Create Procedure   «Prev  Next»

Lesson 5 Adding a function to a procedure
Objective Add a function to a procedure.

Adding Oracle Function to Procedure

This lesson demonstrates an example of how to add a function to a procedure. The following MouseOver shows how you can use a function within a procedure. Here we create a new procedure called DO_ADDRESS. The function FORMAT_ZIP, which was demonstrated in an earlier lesson, is used within the procedure. Afterwards, we demonstrate the syntax for removing a procedure.

Using a function inside a procedure
  1. Here is the function, and 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.
  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.
CREATE OR REPLACE PROCEDURE DO_ADDRESS
  (I_CUST_ID IN NUMBER,
   ADDRESS_LINE2 OUT VARCHAR) AS
  BEGIN
  DECLARE
    V_CITY VARCHAR(20);
    V_STATE VARCHAR2(2);
    v_ZIP VARCHAR2(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;
  /

Function in Assignment Statement

Here is the function, and it is used in an assignment statement.
Using Function Inside Procedure
Now that you have seen an example of calling a function from a procedure, you could apply this knowledge to calling just about any kind of sub-program from any other. For example, you could call a procedure from a trigger; you could create a function that calls a procedure or another function; you could call one procedure from another procedure and call a function from the called procedure; and so on.
The next lesson concludes this module.
Click the link below to modify a procedure using an evaluative simulation.
Function Procedure

Question: I am working with Oracle database 10g. Is it possible to create a function inside a procedure because I do not want to use package.
Answer: Yes, it is possible. In the declaration section of the procedure, you can declare and define a function. But this function wil be private to this procedure and can not be called from outside. 

SQL> create or replace procedure test
  2  as
  3    l_dt date;
  4    --
  5    function dt
  6    return date
  7    is 
  8    begin
  9      return sysdate;
 10    end;
 11    --
 12  begin
 13    l_dt := dt;
 14    dbms_output.put_line(to_char(l_dt, 'dd-mm-yyyy'));
 15  end; 
 16  /

Procedure created.

SQL> exec test
11-13-2011
PL/SQL procedure successfully completed.