时间戳

首页 > 解决方案 > Trying to create SELECT Stored Procedure in Oracle PL SQL and getting error in VARIABLE CURSOR_OUTPUT REFCURSOR;

oracle - Trying to create SELECT Stored Procedure in Oracle PL SQL and getting error in VARIABLE CURSOR_OUTPUT REFCURSOR;

问题描述

I have created procedure for simple select statement output.

CREATE OR REPLACE PROCEDURE marcydashboard (p_recordset IN OUT SYS_REFCURSOR)
IS
BEGIN
OPEN p_recordset FOR select employee_id,first_name,last_name from employees;
END;

VARIABLE CURSOR_OUTPUT REFCURSOR;
EXECUTE marcydashboard(:CURSOR_OUTPUT);

I need result same like select statement result but am getting error like:

ERROR at line 6: PLS-00103: Encountered the symbol "VARIABLE" 
4. END;
5. VARIABLE CURSOR_OUTPUT REFCURSOR;
6. EXECUTE marcydashboard(:CURSOR_OUTPUT );

please help me to fix the error

标签： oracleplsqloracle11g

解决方案

好吧，它可以在 SQL*Plus 中运行：

SQL> CREATE OR REPLACE PROCEDURE marcydashboard (p_recordset OUT SYS_REFCURSOR)
  2  IS
  3  BEGIN
  4     OPEN p_recordset FOR
  5        SELECT employee_id, first_name, last_name FROM employees;
  6  END;
  7  /

Procedure created.

SQL>
SQL> VARIABLE cursor_output REFCURSOR;
SQL> EXECUTE marcydashboard(:cursor_output);

PL/SQL procedure successfully completed.

SQL> PRINT :cursor_output

EMPLOYEE_ID FIRST_NAME LAST_NAME
----------- ---------- ---------
       7369 SMITH      CLERK
       7499 ALLEN      SALESMAN
       7521 WARD       SALESMAN
       7566 JONES      MANAGER

SQL>

看起来您没有使用该工具，而是使用了其他工具。在这种情况下，它不起作用，你必须以不同的方式做，例如

SQL> SET SERVEROUTPUT ON
SQL>
SQL> DECLARE
  2     l_rc           SYS_REFCURSOR;
  3     --
  4     l_employee_id  employees.employee_id%TYPE;
  5     l_first_name   employees.first_name%TYPE;
  6     l_last_name    employees.last_name%TYPE;
  7  BEGIN
  8     marcydashboard (l_rc);
  9
 10     LOOP
 11        FETCH l_rc INTO l_employee_id, l_first_name, l_last_name;
 12
 13        EXIT WHEN l_rc%NOTFOUND;
 14
 15        DBMS_OUTPUT.put_line (
 16           l_employee_id || ' - ' || l_first_name || ' - ' || l_last_name);
 17     END LOOP;
 18
 19     CLOSE l_rc;
 20  END;
 21  /
7369 - SMITH - CLERK
7499 - ALLEN - SALESMAN
7521 - WARD - SALESMAN
7566 - JONES - MANAGER

PL/SQL procedure successfully completed.

SQL>

推荐阅读