Create Procedure   «Prev 

Using Oracle Function inside a Procedure

CREATE OR REPLACE PROCEDURE DO_ADDRESS
  (I_CUST_ID IN NUMBER,
    ADDRESS_LINE2 OUT VARCHAR) AS
  BEGIN
  DECLARE
    V_CITY VARCHAR2(20);
    V_STATE VARCHAR2(2);
    V_ZIP VARCHAR(9);
  BEGIN
    SELECT CITY, STATE, ZIP
    INTO V_CITY, V_STATE, V_ZIP
    FROM CUSTOMER
    WHERE CUST_ID = I_CUST_ID;
    ADDRESS_LINE2 := V_CITY || '  ' ||
                     V_STATE ||'  '||
                     FORMAT_ZIP(V_ZIP);
  END;
  END;
  /
 

Display Area 1

Here is the function and how it is used in an assignment statement. The parameter passed to the function is a local variable from the procedure. The returned value is used to help build the ADDRESS_LINE2 parameter that is returned from the procedure.

Display Area 2

This is the way you remove a procedure.
To remove a function, use the same syntax, except replace the word PROCEDURE with the word FUNCTION.