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.
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.
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;
/
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.