PL/SQL：游标

一、定义

①定义一个静态游标

   1:   CURSOR csr_s IS

   2:   SELECT sname,cno

   3:   FROM student,sc

   4:   WHERE student.sno=sc.sno

   5:   ORDER by 1;

②带参数的静态游标

   1: CURSOR c1(v_sno varchar2) IS

   2: SELECT sname,cno

   3: FROM student,sc

   4: WHERE student.sno=sc.sno

   5: AND trim(sc.sno)=v_sno

   6: ORDER by 1;

使用：open c1(‘oo1’)

③隐式游标
        sql%found
        sql%isopen
        sql%rowcount

④动态弱游标(可以 open for 多个sleect 的结果集)

   1: Type rc is ref curcor;

   2: V_rc rc;

⑤动态强游标

       Type rc is ref cursor return sc%rowtype;
        xxxx rc;

⑥系统弱游标

        v_rc SYS_REFCURSOR;

 

二、例子

①关键字： isopen   while…loop   %rowtype   fetch

   1: DECLARE

   2:   CURSOR c1 IS

   3:     SELECT sname,cno

   4:     FROM student,sc

   5:     WHERE student.sno=sc.sno

   6:     ORDER by 1;

   7:   v_s c1%ROWTYPE;

   8: BEGIN 

   9:   IF (NOT c1%ISOPEN) THEN

  10:     OPEN c1;

  11:   END IF;

  12:   dbms_output.put_line('学生选课信息');

  13:   dbms_output.put_line('------------------------');

  14:   dbms_output.put_line(rpad('学生姓名',20,' ')||' '||rpad('所选课程号',30,' '));

  15:   dbms_output.put_line(rpad('-',20,'-')||' '||rpad('-',30,'-'));

  16:   FETCH c1 INTO v_s;

  17:   WHILE (c1%FOUND)LOOP

  18:     dbms_output.put_line(rpad(v_s.sname,20,' ')||' '||

  19:     rpad(v_s.cno,30,' '));

  20:     FETCH c1 INTO v_s;

  21:   END LOOP;

  22:   IF (c1%ISOPEN)THEN

  23:     CLOSE c1;

  24:   END IF;

  25: END;

②关键字：loop    fetch

   1: DECLARE

   2:   CURSOR c1 IS

   3:     SELECT sname,cno

   4:     FROM student,sc

   5:     WHERE student.sno=sc.sno

   6:     ORDER by 1;

   7:   v_s c1%ROWTYPE;

   8: BEGIN

   9:   OPEN c1;

  10:   LOOP

  11:     FETCH c1 INTO v_s;

  12:     EXIT WHEN c1%NOTFOUND;

  13:     dbms_output.put_line(rpad(v_s.sname,20,' ')||' '||

  14:     rpad(v_s.cno,30,' '));

  15:   END LOOP;

  16:   CLOSE c1;

  17: END;

③关键字：for…loop   fetch   带参数

   1: DECLARE

   2:   CURSOR c1(v_sno varchar2) IS

   3:     SELECT sname,cno

   4:     FROM student,sc

   5:     WHERE student.sno=sc.sno

   6:     AND trim(sc.sno)=v_sno

   7:     ORDER by 1;

   8:   v_s c1%ROWTYPE;

   9: BEGIN

  10:   dbms_output.put_line('学生选课信息 001');

  11:   dbms_output.put_line('------------------------');

  12:   dbms_output.put_line(rpad('学生姓名',20,' ')||

  13:   ''||rpad('所选课程号',30,' '));

  14:   dbms_output.put_line(rpad('-',20,'-')||' '||rpad('-',30,'-'));

  15:   FOR idx in c1('001') LOOP

  16:     dbms_output.put_line(rpad(idx.sname,20,' ')||' '||

  17:     rpad(idx.cno,30,' '));

  18:   END LOOP;

  19:   dbms_output.put_line('学生选课信息 2');

  20:   dbms_output.put_line('------------------------');

  21:   dbms_output.put_line(rpad('学生姓名',20,' ')||' '||

  22:   rpad('所选课程号',30,' '));

  23:   dbms_output.put_line(rpad('-',20,'-')||' '||rpad('-',30,'-'));

  24:   FOR idx in c1('002') LOOP

  25:     dbms_output.put_line(rpad(idx.sname,20,' ')||' '||

  26:     rpad(idx.cno,30,' '));

  27:   END LOOP;

  28: END;

④关键字：sys_refcursor

   1: DECLARE

   2:   v_rc SYS_REFCURSOR;

   3:   v_sc sc%ROWTYPE;

   4: BEGIN

   5:   OPEN v_rc FOR SELECT *from sc;

   6:   dbms_output.put_line('选课信息');

   7:   dbms_output.put_line('------------------------');

   8:   dbms_output.put_line('学号'||' '||rpad('课程号',20,' '));

   9:   dbms_output.put_line(rpad('-',4,'-')||' '||rpad('-',20,'-'));

  10:   LOOP

  11:     FETCH v_rc INTO v_sc;

  12:     EXIT WHEN v_rc%NOTFOUND;

  13:     dbms_output.put_line(to_char(v_sc.sno)||' '||rpad(v_sc.cno,20,' '));

  14:   END LOOP;

  15:   CLOSE v_rc;

  16: END;

 

⑤关键字： 使用同一个游标打开多次查询

   1: DECLARE

   2:   TYPE rc is REF CURSOR;

   3:   v_rc rc;

   4:   v_sc sc%ROWTYPE;

   5:   v_sname VARCHAR2(20);

   6:   v_cno VARCHAR2(30);

   7: BEGIN

   8:   OPEN v_rc FOR SELECT *from sc;

   9:   dbms_output.put_line('选课信息');

  10:   dbms_output.put_line('------------------------');

  11:   dbms_output.put_line('学号'||' '||rpad('课程号 ',20,' '));

  12:   dbms_output.put_line(rpad('-',4,'-')||' '||rpad('-',20,'-'));

  13:   LOOP

  14:     FETCH v_rc INTO v_sc;

  15:     EXIT WHEN v_rc%NOTFOUND;

  16:     dbms_output.put_line(to_char(v_sc.sno)||' '||rpad(v_sc.cno,20,' '));

  17:   END LOOP;

  18:   OPEN v_rc FOR SELECT sname,cno

  19:                 FROM student,sc

  20:                 WHERE student.sno=sc.sno;

  21:   dbms_output.put_line('学生选课信息');

  22:   dbms_output.put_line('------------------------');

  23:   dbms_output.put_line(rpad('学生姓名',20,' ')||' '||rpad('课程号',30,' '));

  24:   dbms_output.put_line(rpad('-',20,'-')||' '||rpad('-',30,'-'));

  25:   LOOP

  26:     FETCH v_rc INTO v_sname,v_cno;

  27:     EXIT WHEN v_rc%NOTFOUND;

  28:     dbms_output.put_line(rpad(v_sname,20,' ')||' '||rpad(v_cno,30,' '));

  29:   END LOOP;

  30:   CLOSE v_rc;

  31: END;