CURSOR式

CURSOR式はネストされたカーソルを返します。この形式の式はPLのREF CURSORと同等であり、REF CURSORパラメータとして関数に渡すことができます。

CURSOR式の構文

CURSOR (subquery)

CURSOR 式を計算する際、ネストされたカーソルが暗黙的に開きます。例えば、カーソル式が選択リスト内に現れる場合、クエリは各行を取得するたびにネストされたカーソルを開きます。

ネストされたカーソルは、次のいずれかの場合にのみ閉じられます：

• ユーザーが明示的にネストされたカーソルを閉じる

• 親カーソルが再実行される

• 親カーソルが閉じられる

• 親カーソルがキャンセルされる

• 特定の親カーソルの取得中にエラーが発生した場合（ネストされたカーソルが閉じられた後にクリーンアップが実行される）

制限事項

CURSOR式には以下の制限事項があります：

• 包囲するステートメントが SELECT ステートメントでない場合、ネストされたカーソルはプロシージャの REF CURSOR パラメータとしてのみ使用できます。

• 包囲するステートメントが SELECT ステートメントの場合、ネストされたカーソルはクエリで定義された最も外側の選択リストまたは別のネストされたカーソルの最も外側の選択リストにも現れることができます。

• ネストされたカーソルはビューには現れません。

• ネストされたカーソルに対して BIND および EXECUTE 操作を実行することはできません。

例

• クエリの選択リストで CURSOR 式を使用します。

  obclient> CREATE TABLE dept(
      deptno           NUMBER(2,0),
      dname            VARCHAR(15),
      location         VARCHAR(20),
     CONSTRAINT pk_dept PRIMARY KEY(deptno)
  );
  Query OK, 0 rows affected
  
  obclient> CREATE TABLE emp(
     empno         NUMBER(4,0),
     empname       VARCHAR(10),
     job           VARCHAR(10),
     mgr           NUMBER(4,0),
     hiredate      DATE,
     sal           NUMBER(7,2),
     comm          NUMBER(7,2),
     deptno        NUMBER(2,0),
     CONSTRAINT PK_emp PRIMARY KEY (empno)
  );
  Query OK, 0 rows affected
  
  obclient> INSERT INTO dept VALUES (10,'ACCOUNTING','Los Angeles')
      ,(30,'OPERATIONS','CHICAGO')
      ,(40,'SALES','NEW YORK');
  Query OK, 3 rows affected
  Records: 3  Duplicates: 0  Warnings: 0
  
  obclient> INSERT INTO emp VALUES (1839,'KING','PRESIDENT',null, '04-JAN-20',5000,3,10)
      ,(1698,'BLAKE','MANAGER',1839,'01-MAY-1981',2850,2,30)
      ,(1782,'CLARK', 'MANAGER',1839, '09-JUN-81', 2450,2,10)
      ,(1566,'JONES','MANAGER',1839, '02-APR-81',2975,2,40)
      ,(1788,'SCOTT','ANALYST',1566, '15-JUL-87',3000,1,20)
      ,(1369,'SMITH','CLERK',1902,'17-OCT-80',800,1,20);
  Query OK, 6 rows affected
  Records: 6  Duplicates: 0  Warnings: 0
  
  obclient> SELECT dname, CURSOR(SELECT sal comm FROM emp e WHERE e.deptno= d.deptno)
     FROM dept d ORDER BY dname;
  +------------+-----------------------------------------------------+
  | DNAME      | CURSOR(SELECTSALCOMMFROMEMPEWHEREE.DEPTNO=D.DEPTNO) |
  +------------+-----------------------------------------------------+
  | ACCOUNTING | -1                                                  |
  | OPERATIONS | -1                                                  |
  | SALES      | -1                                                  |
  +------------+-----------------------------------------------------+
  3 rows in set
  

• 関数のパラメータとして CURSOR 式を使用します。emp テーブルの特定の部門のすべての従業員を削除し、その部門に従業員がいなくなった場合は、dept テーブルからその部門を削除します。

  obclient> DECLARE
           v_dept_id emp.deptno%TYPE := 10;
       BEGIN
           DELETE FROM emp WHERE deptno=v_dept_id;
           IF SQL%NOTFOUND THEN
               DELETE FROM dept WHERE deptno=v_dept_id;
           END IF;
  END;
  /
  Query OK, 0 rows affected
  
  obclient> SELECT * FROM emp;
  +-------+---------+---------+------+-----------+------+------+--------+
  | EMPNO | EMPNAME | JOB     | MGR  | HIREDATE  | SAL  | COMM | DEPTNO |
  +-------+---------+---------+------+-----------+------+------+--------+
  |  1369 | SMITH   | CLERK   | 1902 | 17-OCT-80 |  800 |    1 |     20 |
  |  1566 | JONES   | MANAGER | 1839 | 02-APR-81 | 2975 |    2 |     40 |
  |  1698 | BLAKE   | MANAGER | 1839 | 01-MAY-81 | 2850 |    2 |     30 |
  |  1788 | SCOTT   | ANALYST | 1566 | 15-JUL-87 | 3000 |    1 |     20 |
  +-------+---------+---------+------+-----------+------+------+--------+
  4 rows in set