如何从 shell 脚本中从引用游标（作为 INOUT 参数传递）检索数据到存储过程？

Question

我有一个从 shell 脚本调用的存储过程。传递给存储过程的参数包括作为 INOUT 参数传递的引用游标。执行存储过程后，我必须检索引用游标返回的数据。如何访问参考游标中的数据？

提前致谢

Answer 1

shell 脚本正在连接数据库以运行存储过程，对吧？我猜测它正在使用 SQL*Plus 进行连接。假设游标变量是在 SQL*Plus 中声明的，您应该能够简单地打印游标，即

SQL> create procedure return_rc( p_rc in out sys_refcursor )
  2  is
  3  begin
  4    open p_rc
  5     for
  6     select * from emp;
  7  end;
  8  /

Procedure created.

SQL> variable rc refcursor;
SQL> exec return_rc( :rc );

PL/SQL procedure successfully completed.

SQL> print rc;

     EMPNO ENAME      JOB              MGR HIREDATE         SAL       COMM
---------- ---------- --------- ---------- --------- ---------- ----------
    DEPTNO   FAKE_COL        FOO
---------- ---------- ----------
      7369 smith      CLERK           7902 17-DEC-80        800
        20          1

      7499 ALLEN      SALESMAN        7698 20-FEB-81       1600        300
        30          1

      7521 WARD       SALESMAN        7698 22-FEB-81       1250        500
        30          1


     EMPNO ENAME      JOB              MGR HIREDATE         SAL       COMM
---------- ---------- --------- ---------- --------- ---------- ----------
    DEPTNO   FAKE_COL        FOO
---------- ---------- ----------
      7566 JONES      MANAGER         7839 02-APR-81       2975
        20          1

      7654 MARTIN     SALESMAN        7698 28-SEP-81       1250       1400
        30          1

      7698 BLAKE      MANAGER         7839 01-MAY-81       2850
        30          1


     EMPNO ENAME      JOB              MGR HIREDATE         SAL       COMM
---------- ---------- --------- ---------- --------- ---------- ----------
    DEPTNO   FAKE_COL        FOO
---------- ---------- ----------
      7782 CLARK      MANAGER         7839 09-JUN-81       2450
        10          1

      7788 SCOTT      ANALYST         7566 19-APR-87       3000
        20          1

      7839 KING       PRESIDENT            17-NOV-81       5000
        10          1


     EMPNO ENAME      JOB              MGR HIREDATE         SAL       COMM
---------- ---------- --------- ---------- --------- ---------- ----------
    DEPTNO   FAKE_COL        FOO
---------- ---------- ----------
      7844 TURNER     SALESMAN        7698 08-SEP-81       1500          0
        30          1

      7876 ADAMS      CLERK           7788 23-MAY-87       1110
        20          1

      7900 SM2        CLERK           7698 03-DEC-81        950
        30          1


     EMPNO ENAME      JOB              MGR HIREDATE         SAL       COMM
---------- ---------- --------- ---------- --------- ---------- ----------
    DEPTNO   FAKE_COL        FOO
---------- ---------- ----------
      7902 FORD       ANALYST         7566 03-DEC-81       3000
        20          1

      7934 MILLER     CLERK           7782 23-JAN-82       1300
        10          1


14 rows selected.

如果您尝试获取 PL/SQL 块中的数据

SQL> ed
Wrote file afiedt.buf

  1  declare
  2    l_rc     sys_refcursor;
  3    l_emprec emp%rowtype;
  4  begin
  5    return_rc( l_rc );
  6    loop
  7      fetch l_rc into l_emprec;
  8      exit when l_rc%notfound;
  9      dbms_output.put_line( l_emprec.ename );
 10    end loop;
 11    close l_rc;
 12* end;
SQL> /
smith
ALLEN
WARD
JONES
MARTIN
BLAKE
CLARK
SCOTT
KING
TURNER
ADAMS
SM2
FORD
MILLER

PL/SQL procedure successfully completed.

Answer 2

你必须编写类似这样的代码：-

首先像这样创建 SP：-

create or replace PROCEDURE impact_type_test
(
  v_impact_type_id IN NUMBER,
  cv_1 OUT SYS_REFCURSOR
)
AS

BEGIN

   OPEN  cv_1 FOR
      SELECT impact_type_id 
        FROM impact_type
       WHERE  impact_type_id = v_impact_type_id ;
END;

然后在 shell 脚本中像这样调用 SP：-

sqlplus -S /nolog  <<-! >/dev/null 2>&1
connect ${ORACLE_UID}/${ORACLE_PWD}@${ORACLE_DB};
whenever sqlerror exit sql.sqlcode;
set heading off
set verify off
set feedback off
set pagesize 0
set linesize 150
spool ${WORKDIR}/ouput_data.dat

var vc refcursor;
execute impact_type_test (1,:vc);
print vc;

spool off;
exit
!

while read line
do 
echo $line
done <${WORKDIR}/ouput_data.dat

输出将是这样的：-

113

---

如果你想从 SP 输出中读取特定字段，请参阅此代码下面：-

create or replace PROCEDURE impact_type_test
(
  v_impact_type_id IN NUMBER,
  cv_1 OUT SYS_REFCURSOR
)
AS

BEGIN

   OPEN  cv_1 FOR
      SELECT impact_type_id as impact_type_id,IMPACT_TYPE_NM as IMPACT_TYPE_NM,SEVERITY_ORDER as SEVERITY_ORDER
        FROM impact_type
       WHERE  impact_type_id = v_impact_type_id ;
END;

然后在 shell 脚本中像这样调用 SP：-

sqlplus -S /nolog  <<-! >/dev/null 2>&1
connect ${ORACLE_UID}/${ORACLE_PWD}@${ORACLE_DB};
whenever sqlerror exit sql.sqlcode;
set heading off
set verify off
set feedback off
set pagesize 0
set linesize 150
spool ${WORKDIR}/ouput_data.dat

var vc refcursor;
execute impact_type_test (1,:vc);
print vc;

spool off;
exit
!

while read -r impact_type_id IMPACT_TYPE_NM 
do 
echo $impact_type_id
echo $IMPACT_TYPE_NM

done <${WORKDIR}/ouput_data.dat

如果你仔细观察，SP 返回 3 个字段（impact_type_id，SEVERITY_ORDER） Impact_type_nm，但在 shell 脚本中我只读取 2 个字段（impact_type_id，Impact_type_nm）。

输出将是这样的：-

113
High