將 %ROWTYPE 與遊標結合使用 - PolarDB

使用 %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