Reputation: 195
Can you call a PL/SQL procedure from inside a function?
I haven't come across with the practical example.So if anyone has come across with this please share.
Upvotes: 0
Views: 149
Reputation: 16001
Here you go:
create or replace function demo
return varchar2
as
begin
dbms_output.put_line('Hello');
return 1;
end demo;
Upvotes: 0
Reputation: 22467
Yes. You can call any pl/sql program from inside any other pl/sql program. A function can call a function, a procedure can call a procedure which calls a function, a function can invoke a TYPE BODY...do an INSERT..which causes a TRIGGER to fire.
A simple (not really practical) example.
Using the HR schema where you have an EMPLOYEES table which includes columns EMPLOYEE_ID, FIRST_NAME, and LAST_NAME.
I have a function that takes in an INTEGER which we use to look up an EMPLOYEE record. We take their first and last names, and concat them together, and return the value back in UPPERCASE text.
Before we do that however, we call a procedure which does nothing but take a 5 second nap using the DBMS_LOCK package.
The code:
create or replace procedure do_nothing_comments (x in integer, y in integer)
is
begin
null;
-- yeah, this is a dumb demo
dbms_lock.sleep(5);
end;
/
create or replace FUNCTION upper_name (
x IN INTEGER
) RETURN VARCHAR2 IS
upper_first_and_last VARCHAR2 (256);
BEGIN
SELECT upper (first_name)
|| ' '
|| upper (last_name)
INTO upper_first_and_last
FROM employees
WHERE employee_id = x;
do_nothing_comments (1, 2); -- here we are calling the procedure
RETURN upper_first_and_last;
END;
/
Now let's invoke the function.
DECLARE
X NUMBER;
v_Return VARCHAR2(200);
BEGIN
X := 101;
v_Return := UPPER_NAME(
X => X
);
:v_Return := v_Return;
END;
/
I'm going to do this in SQL Developer using the Execute feature with the function open:
I get the answer back...it just takes 5 seconds longer than it really needed to.
Upvotes: 1