Oracle使用行型別變數獲取遊標資訊

(1) 表資料
SQL> select * from scott.dept;

    DEPTNO DNAME      LOC
---------- ---------- ----------
    10 ACCOUNTING NEW YORK
    20 RESEARCH   DALLAS
    30 SALES      CHICAGO
    40 OPERATIONS BOSTON

(2) 程式編寫
set serverout on;

--宣告遊標變數
declare cursor cu_dept is select * from dept;
--宣告行型別變數
dept_new dept%rowtype;

begin
  open cu_dept;
  fetch cu_dept into dept_new;

  while cu_dept%found loop
    dbms_output.put_line(dept_new.deptno || ':' || dept_new.dname || ':' || dept_new.loc);
    fetch cu_dept into dept_new;
  end loop;
  close cu_dept;
end;

/

(3) 執行結果
10:ACCOUNTING:NEW YORK
20:RESEARCH:DALLAS
30:SALES:CHICAGO
40:OPERATIONS:BOSTON

PL/SQL procedure successfully completed.