如何使用存储过程执行表存储查询

Question

我有以下表结构。

CREATE TABLE "DADMIN"."DATATEMPLATES" 
(   
"ID" VARCHAR2(100 BYTE),  
"QUERY" CLOB, 
"ACTIVESTATUS" VARCHAR2(2 BYTE), 
)

我正在存储这样的查询数据 SELECT CD.CIF , CD.CREDIT_ACCOUNT FROM CUTOMERDATA CD WHERE ID = :PARA_ID

目前我正在通过 C# 原始 sql 执行来执行它

，而不是那样，我需要通过存储过程执行表查询。如何使用 SP 执行表存储查询？并使用游标输出返回其数据？

更新：

这是我的示例 SP，

PROCEDURE Get_customer_data (p_Query_id IN VARCHAR2,
                             p_cursor OUT OUTPUTCURSOR)
IS
BEGIN
    DECLARE
        l_query CLOB;
    BEGIN
        SELECT query
        INTO   l_query
        FROM   querytemplates
        WHERE  id = p_Query_id ;

        OPEN p_cursor FOR l_query;
    END;
END; 

但这会产生错误

ORA-06512: at line 1
01008. 00000 -  "not all variables bound"

，而我的另一个问题是，表存储的查询也除了名为 PARA_ID 的参数之外，我如何传递它。

示例表存储查询如下，

SELECT CD.CIF , CD.CREDIT_ACCOUNT FROM CUTOMERDATA CD  WHERE ID = :PARA_ID

Answer 1

一种选择是返回 refcursor。这是一个例子。

示例数据（存储查询）：

SQL> SELECT * FROM datatemplates;

        ID QUERY                                                        A
---------- ------------------------------------------------------------ -
         1 select deptno, dname from dept                               A
         2 select d.dname, e.ename, e.job, e.sal from emp e join dept d A
            on d.deptno = e.deptno


SQL>

过程 - 基于传递的 ID 参数 - 选择适当的查询并根据该 select 语句打开引用游标：

SQL> CREATE OR REPLACE FUNCTION f_test (par_id IN NUMBER)
  2     RETURN SYS_REFCURSOR
  3  IS
  4     l_query  CLOB;
  5     l_rc     SYS_REFCURSOR;
  6  BEGIN
  7     SELECT query
  8       INTO l_query
  9       FROM datatemplates
 10      WHERE id = par_id;
 11
 12     OPEN l_rc FOR l_query;
 13
 14     RETURN l_rc;
 15  END;
 16  /

Function created.

测试：

SQL> SELECT f_test (1) FROM DUAL;

F_TEST(1)
--------------------
CURSOR STATEMENT : 1

CURSOR STATEMENT : 1

    DEPTNO DNAME
---------- --------------
        10 ACCOUNTING
        20 RESEARCH
        30 SALES
        40 OPERATIONS

更多测试：

SQL> SELECT f_test (2) FROM DUAL;

F_TEST(2)
--------------------
CURSOR STATEMENT : 1

CURSOR STATEMENT : 1

DNAME          ENAME      JOB              SAL
-------------- ---------- --------- ----------
ACCOUNTING     CLARK      MANAGER         2450
ACCOUNTING     KING       PRESIDENT       5000
ACCOUNTING     MILLER     CLERK           1300
RESEARCH       JONES      MANAGER         2975
RESEARCH       FORD       ANALYST         3000
RESEARCH       ADAMS      CLERK           1100
RESEARCH       SMITH      CLERK            800
RESEARCH       SCOTT      ANALYST         3000
SALES          WARD       SALESMAN        1250
SALES          TURNER     SALESMAN        1500
SALES          ALLEN      SALESMAN        1600
SALES          JAMES      CLERK            950
SALES          BLAKE      MANAGER         2850
SALES          MARTIN     SALESMAN        1250

14 rows selected.


SQL>