oracle - 在过程错误中创建 oracle sql 游标

Question

我通过考虑类次表数据创建了一个计算员工工资的程序
我的轮类表是，

CREATE TABLE SHIFT
        (SHIFT_ID NUMBER(4),
        SHIFT_DATE DATE,
        CUSTOMER_ID NUMBER(4),
        SERVICE_ID NUMBER(4),
        EMPLOYEE_ID  NUMBER(4),
        SHIFT_CHARGE FLOAT(10), 
        PRIMARY KEY(SHIFT_ID)
        );

我的程序是，

CREATE OR REPLACE PROCEDURE CAL_SALLARY
AS
CURSOR SHIFT_CURSOR IS SELECT EMPLOYEE_ID,SUM(SHIFT_CHARGE) AS SALARY FROM SHIFT GROUP BY EMPLOYEE_ID;
BEGIN
    OPEN SHIFT_CURSOR;
    LOOP
        FETCH SHIFT_CURSOR INTO SHIFT_REC;
        EXIT WHEN SHIFT_CURSOR%NOTFOUND;
        DBMS_OUTPUT.PUT_LINE('EMPLOYYE ID : '||SHIFT_REC.EMPLOYEE_ID||' SALARY : '||SHIFT_REC.SALARY);
    END LOOP;
    CLOSE SHIFT_CURSOR; 
END;
/

但是当我尝试在 SQL plus 中运行这个过程时，它说编译失败。我数了一下，发现是什么问题。
请任何人帮助我。

Answer 1

您忘记声明游标变量(请参阅第 7 行):

SQL> CREATE OR REPLACE PROCEDURE CAL_SALLARY
  2  AS
  3  CURSOR SHIFT_CURSOR IS
  4    SELECT EMPLOYEE_ID, SUM(SHIFT_CHARGE) AS SALARY
  5    FROM SHIFT GROUP BY EMPLOYEE_ID;
  6
  7    shift_Rec shift_cursor%rowtype;
  8  BEGIN
  9      OPEN SHIFT_CURSOR;
 10      LOOP
 11          FETCH SHIFT_CURSOR INTO SHIFT_REC;
 12          EXIT WHEN SHIFT_CURSOR%NOTFOUND;
 13          DBMS_OUTPUT.PUT_LINE('EMPLOYYE ID : '||SHIFT_REC.EMPLOYEE_ID
 14                                ||' SALARY : '||SHIFT_REC.SALARY);
 15      END LOOP;
 16      CLOSE SHIFT_CURSOR;
 17  END;
 18  /

Procedure created.

SQL>

一个更简单的选项是游标 FOR 循环； Oracle 为您完成了大部分脏活(例如，声明游标变量、打开和关闭游标、退出循环):

SQL> create or replace procedure cal_sallary as
  2  begin
  3    for shift_rec in (select employee_id, sum(shift_charge) as salary
  4                      from shift
  5                      group by employee_id
  6                     )
  7    loop
  8      dbms_output.put_line('EMPLOYYE ID : '||shift_rec.employee_id
  9                           ||' SALARY : '||shift_rec.salary);
 10    end loop;
 11  end;
 12  /

Procedure created.

SQL>