CODEWITHSUNDEEP
oracle-databaseplsql
Jaspreet Singh
Jaspreet Singh

Reputation: 195

Calling procedure in function

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

Answers (2)

William Robertson
William Robertson

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

thatjeffsmith
thatjeffsmith

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:

enter image description here

I get the answer back...it just takes 5 seconds longer than it really needed to.

Upvotes: 1

Related Questions