REF Cursor from Procedure PL / SQL-CodePudding

I am trying to trouble shoot a PL SQL package. I found a similar example to explain my question as I am new to learning the PL SQL and how a cursor works with a procedure.

So for example:

CREATE OR REPLACE PROCEDURE emp_by_job (
    p_job           VARCHAR2,
    p_emp_refcur    IN OUT SYS_REFCURSOR
)
IS
BEGIN
    OPEN p_emp_refcur FOR SELECT empno, ename FROM emp WHERE job = p_job;
END;

In this I see that p_job is defined as VARCHAR2. Then the SQL selects from EMP where job = p_job. All we've defined p_job as is VARCHAR2, not mapped to a field anywhere. How does the cursor know what to do with job = p_job, where does p_job's value come from?

Thank you!

CodePudding user response：

A procedure doesn't do anything when it is compiled, it needs to be called/invoked. The value for p_job will be passed when the procedure is called. In the example below, emp_by_job is invoked from an anonymous pl/sql block with a value for the argument p_job of 'MANAGER'.

koen>CREATE OR REPLACE PROCEDURE emp_by_job (
  2      p_job           VARCHAR2,
  3      p_emp_refcur    IN OUT SYS_REFCURSOR
  4  )
  5  IS
  6  BEGIN
  7      OPEN p_emp_refcur FOR SELECT empno, ename FROM emp WHERE job = p_job;
  8  END;
  9* /

Procedure EMP_BY_JOB compiled

koen>set serveroutput on size 999999
koen>DECLARE
  2    TYPE emp_t IS REF CURSOR;
  3    l_emp emp_t;
  4    l_emprow emp%ROWTYPE;
  5  BEGIN
  6    emp_by_job(p_job => 'MANAGER',p_emp_refcur => l_emp);
  7     LOOP
  8        FETCH
  9           l_emp
 10        INTO
 11           l_emprow.empno,
 12           l_emprow.ename;
 13        EXIT
 14     WHEN l_emp%notfound;
 15        dbms_output.put_line(l_emprow.empno || ' ' || l_emprow.ename);
 16     END LOOP;
 17  END;
 18* /
7698 BLAKE
7782 CLARK
7566 JONES


PL/SQL procedure successfully completed.

koen>