技术分享 | 独立思考

0%

oracle游标

发表于 更新于 分类于 ，
本文字数： 2.3k 阅读时长 ≈ 2 分钟

定义

  • 逐行处理查询结果，以编程的方式访问数据

游标分类

隐式游标：在PL/SQL中执行DML SQL语句时自动创建隐式游标

  • 隐式游标自动生命、打开和关闭，名字为SQL
  • 隐式游标的属性：
    • %FOUND:SQL语句影响一行或多行时为TRUE
    • %NOTFOUND:SQL语句没有影响任何行时为TRUE
    • %ROWCOUNT:语句影响的行数
    • %ISOPEN:是否打开状态，始终为FALSE
1
2
3
4
BEGIN
 UPDATE student SET sage = sage + 10;
 dbms_output.put_line('更新了' || SQL%rowcount || '行');
END

显式游标：现式游标用于处理返回多行的查询

1
2
3
4
5
6
7
8
9
10
11
12
DECLARE
 my_toy_price toys.toyprice%TYPE;
 CURSOR toy_cur IS SELECT toyprice FROM toys WHERE toyprice<250;--声明游标
BEGIN
 OPEN toy_cur;--打开游标
    LOOP
     FETCH toy_cur INTO my_toy_price;--提取行
     EXIT WHEN toy_cur%NOTFOUND;
     DBMS_OUTPUT.PUT_LINE('玩具单价'||my_toy_price);
    END LOOP;
 CLOSE toy_cur;--关闭游标
END;
  • 可以给游标加参数，提高灵活性
1
2
3
4
5
6
7
8
9
10
11
12
13
14
DECLARE
 sno1 student.sno%TYPE;
 stu1 student%rowtype;
 CURSOR mycursor(input_no number) IS SELECT * FROM student WHERE sno > input_no;--声明带参游标
BEGIN
 sno1 := &学生学号;
 OPEN mycursor(sno1);
 FETCH mycursor INTO stu1;
    while mycursor%FOUND loop
     dbms_output.put_line('学号'|| stu1.sno || '姓名' || stu1.sname)
     FETCH mycursor INTO stu1;
    END loop;
    CLOSE toy_cur;
END;
  • 可以用游标删除和更新活动集中的行，必须使用select … for update语句和where current of 
1
2
3
CURSOR <cursor_name> IS SELECT statement FOR UPDATE;
UPDATE <table_name> SET <set_clause> WHERE CURRENT OF <cursor_name>--更新语句
DELETE FROM <table_name> WHERE CURRENT OF <cursor_name>--删除语句
  • 循环游标用于简化游标处理代码，用于从游标中提出所有记录时使用
1
2
3
4
5
6
7
DECLARE
 CURSOR toy_cur IS SELECT * FROM toys;--声明游标
BEGIN
 FOR cur_2 IN toy_cur loop
     DBMS_OUTPUT.PUT_LINE('玩具单价'||cur_2.toyprice);
 END LOOP;
END;

fetch … bulk collect into

1
2
3
4
5
6
7
8
9
10
11
12
DECLARE
 CURSOR my_cursor IS SELECT ename FROM emp WHERE deptno=10;
 TYPE ename_table_type IS TABLE OF varchar2(10);
 ename_table enname_table_type;
BEGIN
 OPEN MY_CURSOR;
 FETCH MY_CURSOR BULK COLLECT INTO ename_table;
 FOR i IN 1..ename_table.count LOOP
  dbms_output.put_line(ename_table(i));
 END LOOP;
 CLOSE MY_CURSOR ;
END;

REF游标：REF游标用于处理运行时才能确定的动态SQL查询的结果

  • 创建游标变量需要两个步骤：

    • 声明REF游标类型
    • 声明REF游标类型的变量

  • 声明REF游标类型的语法：

1
2
TYPE <ref_cursor_name> IS REF CURSOR
[RETURN <return_type>]
  • 打开游标变量的语法：
1
OPEN cursor_name FOR select_statement;
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
DECLARE
 TYPE refcur IS REF CURSOR;
 cursor2 REFCUR;
 tab varchar2(50);
 no2 student.sno%TYPE;
 name2 student.sname%TYPE;
BEGIN
 tb_name:='&tab';
 IF tb_name='student' THEN
  OPEN cursor2 FOR SELECT sno,sname FROM student;
  FETCH cursor2 INTO no2,name2;
   WHILE cursor2%FOUND LOOP
    dbms_output.put_line(cursor2%rowcount || no2 || name2);
    FETCH cursor2 INTO no2,name2;
   END LOOP;
  CLOSE cursor2;
 ELSE 
  dbms_output.put_line('不是正确的表的名字');
 END IF; 
END