How to write a PL/SQL procedure

How to write PL/SQL Procedure

Initialize input and output variables.

input variables are those which dynamic values which needs to be updated whenever Procedure is called

Output variables are those in which you need to store the output of query

For Single Result set, we use Output Variable

But when output is of Many Line it is recommended to use Cursers else you might end up with exception:TOO_MANY_ROWS

The Select Query is modified with little insertions so that result can be stored in an variable which can there is used for further action.

Example :

create or replace PROCEDURE TESTPROC1

(

  U_MATCHSYSTEM IN VARCHAR2  -- Declare Input Variable

  ,R_RESULT_TEXT OUT VARCHAR2  -- Declare Output Variable

  AS

BEGIN

select txn_proc_id,counterparty_id,TXN_ID into R_TXN_PRC_ID,R_CPTYID,R_TXN_ID from table1 where MATCHSYSTEM=U_MATCHSYSTEM;

R_RESULT_TEXT := CONCAT (R_RESULT_TEXT, 'Process Started');  -- You can Store the Results in a variable, so that you can see what's happening in procedure when proc is called through some other program.

Else for Output messages we can use

DBMS_OUTPUT.PUT_LINE('FOR FX module Getting the data for MESSAGE ID '|| U_MSGID);

if U_MSGID in (1,9) then

Select statement1

Elsif U_MSGID in (2,6) then

select statement2

end if;

Multiple Output is single output line is handled:

DBMS_OUTPUT.PUT_LINE('EMAIL Chaser Setting Emails '||U_E_ADDR_1 || ' '|| U_E_ADDR_2 || ' '||U_E_ADDR_3);

commit; -- Don't forget to commit if you are updating or inserting certain things

  EXCEPTION     -- Handle the exceptions

      WHEN NO_DATA_FOUND THEN

  DBMS_OUTPUT.PUT_LINE( 'No trades found for your filters');

    R_RESULT_TEXT := CONCAT (R_RESULT_TEXT, '; No trades found for your filters');

         when others then

     DBMS_OUTPUT.PUT_LINE( 'Exception occurred ');

      R_RESULT_TEXT := CONCAT (R_RESULT_TEXT, ';  Exception occurred ');

    rollback;    -- rollback your changes if any exception occurs

           

END TESTPROC1;  -- End the procedure

When there is Multiple Results and we need to operation on each output row, we need to use cursors:

create or replace PROCEDURE TESTPROC2

AS

caseno NUMBER(10,0);

countmessages NUMBER(10,0);

countstring NUMBER(5,0);

isThere NUMBER(5,0);

ihour Number(2,0);

CURSOR messages1 IS

SELECT message_id, message_handle, message_date

FROM XYZ

WHERE trunc(MESSAGE_DATE) = trunc(sysdate) and message_id > 0 and CASE_NO = 0;

BEGIN

FOR message_row1 IN messages2

LOOP

Select/update Statement for message_row1.message_handle – Cursorname.oneofthecoulmnName

END LOOP;

Commit;

EXCEPTION

WHEN NO_DATA_FOUND THEN

DBMS_OUTPUT.PUT_LINE( 'No case generated with workfolder ''RM CONTROL'' for date: ' || sysdate);

when others then

DBMS_OUTPUT.PUT_LINE( 'Check the values and logs...');

rollback;

END TESTPROC2;