on the road

0%

Oracle PL/SQL 游标

发表于

Summary: in this tutorial, you will learn about PL/SQL cursor and its usage.

A cursor is a pointer that points to a result of a query. PL/SQL has two types of cursors: implicit cursors and explicit cursors.

Implicit cursors

Whenever Oracle executes an SQL statement such as SELECT INTO, INSERT, UPDATE, and DELETE, it automatically creates an implicit cursor.

Oracle internally manages the whole execution cycle of implicit cursors and reveals only the cursor’s information and statuses such as SQL%ROWCOUNT, SQL%ISOPEN, SQL%FOUND, and SQL%NOTFOUND.

The implicit cursor is not elegant when the query returns zero or multiple rows which cause NO_DATA_FOUND or TOO_MANY_ROWS exception respectively.

Explicit cursors

An explicit cursor is an SELECT statement declared explicitly in the declaration section of the current block or a package specification. For an explicit cursor, you have control over its execution cycle from OPEN, FETCH, and CLOSE.

Oracle defines an execution cycle to execute an SQL statement and associates a cursor with it. The following illustration shows the execution cycle of an explicit cursor:

PL/SQL Cursor

  1. for in loop形式
1
2
3
4
5
6
7
8
9
10
11
12
DECLARE
  　CURSOR c_sal IS SELECT employee_id, first_name || last_name ename, salary
  　FROM employees ;
BEGIN
  　--隐含打开游标
  　FOR v_sal IN c_sal LOOP
 　　 --隐含执行一个FETCH语句
   　　DBMS_OUTPUT.PUT_LINE(to_char(v_sal.employee_id)||'---'|| v_sal.ename||'---'||to_char(v_sal.salary)) ;
  　--隐含监测c_sal%NOTFOUND
  　END LOOP;
　　--隐含关闭游标
END;

2.普通的游标循环

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
declare
  --定义游标并且赋值(is 不能和cursor分开使用)
  cursor stus_cur is select * from students;
  --定义rowtype
  cur_stu students%rowtype;
  /*开始执行*/
  begin
  open stus_cur;--开启游标
  loop --loop循环
    exit when stus_cur%notfound;--循环条件
   	fetch stus_cur into cur_stu;--游标值赋值到rowtype
    dbms_output.put_line(cur_stu.name);--输出
  end loop;--结束循环
  close stus_cur;--关闭游标
  /*结束执行*/
end;

3.高效的游标循环

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
declare
　　cursor myemp_cur
　　is select * from myemp;
　　type myemp_tab is table of myemp%rowtype;
　　myemp_rd myemp_tab;
　　begin
　　  open myemp_cur;
　　  loop
　　    fetch myemp_cur bulk collect into myemp_rd limit 20;
　　    for i in 1..myemp_rd.count loop
　　      dbms_output.put_line('姓名：'||myemp_rd(i).ename);
　　    end loop;
　　    exit when myemp_cur%notfound;
　　  end loop;
　　end;

BULK COLLECT 子句会批量检索结果，即一次性将结果集绑定到一个集合变量中，并从SQL引擎发送到PL/SQL引擎。通常可以在SELECT INTO、
FETCH INTO以及RETURNING INTO子句中使用BULK COLLECT.

BULK COLLECT的限制
1、不能对使用字符串类型作键的关联数组使用BULK COLLECT 子句。
2、只能在服务器端的程序中使用BULK COLLECT，如果在客户端使用，就会产生一个不支持这个特性的错误。
3、BULK COLLECT INTO 的目标对象必须是集合类型。
4、复合目标(如对象类型)不能在RETURNING INTO 子句中使用。
5、如果有多个隐式的数据类型转换的情况存在，多重复合目标就不能在BULK COLLECT INTO 子句中使用。
6、如果有一个隐式的数据类型转换，复合目标的集合(如对象类型集合)就不能用于BULK COLLECTINTO 子句中。

Links：

https://www.oracletutorial.com/plsql-tutorial/plsql-cursor/

https://www.cnblogs.com/zgz21/p/5864298.html