将 %ROWTYPE 与游标结合使用

使用 %ROWTYPE 属性，可以定义一条记录，该记录包含与从游标或游标变量获取的所有列相对应的字段。每个字段都采用其对应列的数据类型。

%ROWTYPE 属性以游标名称或游标变量名称为前缀。

record cursor%ROWTYPE;

record 是分配给记录的标识符。cursor 是当前作用域内显式声明的游标。

以下示例显示了如何将游标与 %ROWTYPE 结合使用来获取有关哪个员工在哪个部门中工作的信息。

CREATE OR REPLACE PROCEDURE emp_info
IS
    CURSOR empcur IS SELECT ename, deptno FROM emp;
    myvar           empcur%ROWTYPE;
BEGIN
    OPEN empcur;
    LOOP
        FETCH empcur INTO myvar;
        EXIT WHEN empcur%NOTFOUND;
        DBMS_OUTPUT.PUT_LINE( myvar.ename || ' works in department '
            || myvar.deptno );
    END LOOP;
    CLOSE empcur;
END;

以下是此存储过程的输出。

EXEC emp_info;

SMITH works in department 20
ALLEN works in department 30
WARD works in department 30
JONES works in department 20
MARTIN works in department 30
BLAKE works in department 30
CLARK works in department 10
SCOTT works in department 20
KING works in department 10
TURNER works in department 30
ADAMS works in department 20
JAMES works in department 30
FORD works in department 20
MILLER works in department 10