CODEWITHSUNDEEP
sqloracle-databasestored-proceduresoracle-sqldeveloper
user473104
user473104

Reputation: 311

Stored Procedure in Oracle SQL Developer

I'm using SQL Oracle to build a stored procedure. I'm trying to build a stored procedure of the SQL-query below

SELECT "Merch"."Id", "Order"."Id", "Order"."OrderExt"
FROM "Order"
Join "Merch" ON "Order"."MerchId" = "Merch"."Id"
WHERE "Merch"."Id" = 25 AND "Order"."OrderExt" = 'TC10045604'

I want to use 2 inparameters to compare "Merch.Id" and "Order.OrderExt". Im really new at SQL-Oracle and have a real hard time figuring out how to write this procedure.

One of the problems I have is how do a return a table with "Merch"."Id", "Order"."Id", "Order"."OrderExt" ?

Do I use a cursor in some way?

Can anybody help me out with this problem?

Upvotes: 0

Views: 21571

Answers (2)

Ulises
Ulises

Reputation: 13419

Like you said you need to use 2 parameters so you can filter your data by Merch.Id and Order.OrderExt.

You could return the results using a SYS_REFCURSOR, for example:

 PROCEDURE MY_PROC 
                ( pOrderExt "Order"."OrderExt"%type, 
                  pMerchId "Merch"."Id"%type,               
                  recordSet OUT SYS_REFCURSOR )
        AS    
        BEGIN    
       OPEN recordSet FOR 
          SELECT "Merch"."Id", "Order"."Id", "Order"."OrderExt"
          FROM "Order"
          INNER JOIN "Merch" ON "Order"."MerchId" = "Merch"."Id"
          WHERE "Merch"."Id" = pMerchId AND "Order"."OrderExt" = pOrderExt;    
   END MY_PROC;

This is how you see results (make sure you look at the Out Variables tab in SQL Developer):

DECLARE
  pOrderExt "Order"."OrderExt"%type;
  pMerchId "Merch"."Id"%type;        
  recordSet OUT SYS_REFCURSOR;
BEGIN
  pMerchId := 25 ;
  pOrderExt := 'TC10045604';  

  MY_PROC (
    pMerchId => pMerchId,
    pOrderExt => pOrderExt,    
    recordSet => recordSet
  );
  :recordSet := recordSet; --<-- Cursor
END;

Edited: Added example for execution, made improvements as Justin Cave pointed out

Upvotes: 2

Justin Cave
Justin Cave

Reputation: 231651

You could write a stored procedure that had an OUT parameter that was a SYS_REFCURSOR

CREATE OR REPLACE PROCEDURE return_cursor( p_merch_id  IN "Merch"."Id"%type,
                                           p_order_ext IN "Order"."OrderExt"%type,
                                           p_rc       OUT sys_refcursor )
AS
BEGIN
  OPEN p_rc 
   FOR SELECT "Merch"."Id", "Order"."Id", "Order"."OrderExt"
         FROM "Order"
              Join "Merch" ON "Order"."MerchId" = "Merch"."Id"
        WHERE "Merch"."Id" = p_merch_id
          AND "Order"."OrderExt" = p_order_ext;
END;

It would be more natural, however, to have a stored function that returned a SYS_REFCURSOR

CREATE OR REPLACE FUNCTION return_cursor( p_merch_id  IN "Merch"."Id"%type,
                                          p_order_ext IN "Order"."OrderExt"%type )
  RETURN sys_refcursor
AS
  l_rc sys_refcursor;
BEGIN
  OPEN l_rc 
   FOR SELECT "Merch"."Id", "Order"."Id", "Order"."OrderExt"
         FROM "Order"
              Join "Merch" ON "Order"."MerchId" = "Merch"."Id"
        WHERE "Merch"."Id" = p_merch_id
          AND "Order"."OrderExt" = p_order_ext;
  RETURN l_rc;
END;

As a general matter of style, having case-sensitive table and column names is very, very rarely a good idea. And having table names that match Oracle reserved words like Order is even less likely to be a good idea. Forcing every developer to always use double quotes around every identifier and to always specify them in the proper case is going to lead to substantially more mistakes than if you simply used the default case-insensitive convention and avoided reserved words.

Upvotes: 4

Related Questions