How to return a set of results from one Oracle subprogram to another?

Question

This is a simplified version of what I'm trying to do, but let's say I want to select all employees whose manager is in a given department. I could have a SQL query like the following:

SELECT employees.id
  FROM employees
 WHERE employees.manager IN (SELECT managers.id
                               FROM managers
                              WHERE managers.dept = 12)
;

But let's say I want to abstract the manager subquery into a PL/SQL subprogram. How do I do that?
The stored procedures I've worked with (which are mostly written by other developers) tend to have out parameters that get mapped by PHP calling code into a PHP array. I don't really have any experience of calling one stored procedure from another.

What I'd like to do is to have something like this:

SELECT employees.id
  FROM employees
 WHERE employees.manager IN my_stored_procedure(12)
;

and then my_stored_procedure would output the set of manager IDs for the input parameter (which is 12 in this example).

is that a procedure or function (I think it is function) – Rams Sep 01 '17 at 16:59 — Rams, Sep 01 '17 at 16:59

score 2 · Accepted Answer · answered Sep 02 '17 at 16:22

It is not possible to do exactly as you have posted, but if the selection of managers are not straightforward, you could abstract it through a view or make use of a function that returns a table, like this:

SELECT employees.id
  FROM employees
 WHERE employees.manager IN (SELECT * from TABLE(get_managers_from_dept(12)));

In this link there is an example of that approach: Function or Procedure for an IN clause

score 0 · Answer 2 · answered Sep 01 '17 at 17:57

0

To Call A stored Proc from another Stored Proc you just need to call it from the Main Proc as mentioned below. This Main Proc can be called /initiated by a PHP Code.

PROCEDURE some_sp
       AS
       BEGIN
          some_other_sp('parm1');
       END;

answered Sep 01 '17 at 17:57

nitin.sharma0180

471
3
6

Yes, but what I'm unsure of is the structure of the proc that is called from the first one. I.e. how to output a set of results for the calling proc – osullic Sep 05 '17 at 08:13

Kaushik Nayak · Answer 3 · 2017-09-01T18:14:40.353

Although less straight-forward, You can do accomplish it by using dynamic sql. This is the structure of your stored procedure. It returns a comma separated list of manager_ids for a given department.

CREATE OR REPLACE FUNCTION my_stored_procedure(
    p_dept NUMBER)
  RETURN VARCHAR2
IS
  v_manager_list VARCHAR2(1000);
BEGIN
  SELECT m.id INTO v_manager_list FROM managers m WHERE m.dept = p_dept;
  RETURN '('||v_manager_list||')';
EXCEPTION
WHEN NO_DATA_FOUND THEN
  RETURN '(NULL)';
END;
/

Now you cannot use this to directly query as ...IN my_stored_procedure(12), rather you must use a dynamic fetch into a collection.

SET SERVEROUTPUT ON;
DECLARE
TYPE v_emp_type
IS
  TABLE OF employees.id%TYPE;
  v_emp v_emp_type;
BEGIN
  EXECUTE IMMEDIATE 'SELECT employees.id 
FROM employees 
WHERE employees.id IN '|| my_stored_procedure(100) BULK COLLECT INTO v_emp ;
  FOR i IN v_emp.FIRST..v_emp.LAST
  LOOP
    DBMS_OUTPUT.PUT_LINE(v_emp(i));
  END LOOP;
END;
/

This solution seems a bit hacky. I'm looking for an extensible solution. Your `v_manager_list` VARCHAR2 has a length limit, and generally just feels a bit convoluted. I'm trying to keep things simple and elegant. — osullic, Sep 05 '17 at 08:22

How to return a set of results from one Oracle subprogram to another?

3 Answers3