PL/SQL 游标表达式

来源：互联网发布：nosql数据库编辑：程序博客网时间：2024/06/11 01:52

-- Start

利用游标表达式，我们可以返回嵌套游标。

CREATE TABLE Class (  ClassName   VARCHAR2(10));INSERT INTO Class VALUES ('116班');CREATE TABLE Student(      Id          INT             NOT NULL,      Name        VARCHAR2(30)    NOT NULL,      ClassName   VARCHAR2(10)  );  INSERT INTO Student VALUES (1, '张三', '116班');  INSERT INTO Student VALUES (2, '李四', '116班');  INSERT INTO Student VALUES (3, '王五', '117班');DECLARE  ClassName        VARCHAR2(10);  StudentName      VARCHAR2(10);  StudentCurVar    SYS_REFCURSOR;  CURSOR c1 IS  SELECT     ClassName,     CURSOR (SELECT s.Name FROM Student s WHERE c.ClassName = s.ClassName) Student -- 游标表达式  FROM Class c;BEGIN  OPEN c1;    LOOP    FETCH c1 INTO ClassName, StudentCurVar;    EXIT WHEN c1%NOTFOUND;    DBMS_OUTPUT.PUT_LINE('ClassName: ' || ClassName);        LOOP       FETCH StudentCurVar INTO StudentName;      EXIT WHEN StudentCurVar%NOTFOUND;      DBMS_OUTPUT.PUT_LINE('-- StudentName: ' || StudentName);    END LOOP;  END LOOP;    CLOSE c1;END;/

--更多参见：Oracle PL/SQL 精萃

-- 声明：转载请注明出处

-- Last Edited on 2015-01-14

-- Created by ShangBo on 2015-01-14

-- End

0 0