使用显式游标以及游标属性;显式游标更新或删除数据;参数游标;游标FOR循环;游标变量;FETCH ...BULK COLLECT INTO语句和CURSOR表达式。
为了处理SELECT语句返回的多行数据,可以使用显式游标,使用显示游标包括定义游标、打开游标、提取数据和关闭游标四个阶段。
1)定义游标。在使用显式游标之前,必须首先在定义部分定义游标。定义游标用于指定游标所对应的SELECT语句。
CURSOR cursor_name IS select_statement;
2)打开游标。当打开游标时,Oracle会执行游标所对应的SELECT语句,并且将SELECT语句的结果暂时存放到结果集中。
OPEN cursor_name
3)提取数据。为了处理结果集中的数据,需要使用FETCH语句提取游标数据。使用FETCH语句每次只能提取一行数据,通过使用FETCH...BULK COLLECT INTO语句,每次可以提取多行数据。
语法一:FETCH cursor_name INTO variable1,variable2...
语法二:FETCH cursor_name BULK COLLECT INTO collect1,collect2,...[LIMIT rows];
variable用于指定接收游标数据的变量;collect用于指定接收游标结果的集合变量。当使用语法一时,必须要使用循环语句处理结果集的所有数据。
4)关闭游标。
CLOSE cursor_name;
显示游标属性用于返回显式游标的执行信息,这些属性包括%ISOPEN、%FOUND、%NOTFOUND和%ROWCOUNT。
--在显式游标中使用FETCH...INTO语句
DECLARE
CURSOR emp_cursor IS
SELECT ename,sal FROM emp WHERE deptno=10;
v_ename emp.ename%TYPE;
v_sal emp.sal%TYPE;
BEGIN
OPEN emp_cursor;
LOOP
FETCH emp_cursor INTO v_ename,v_sal;
EXIT WHEN emp_cursor%NOTFOUND;
dbms_output.put_line(v_ename||': '||v_sal);
END LOOP;
CLOSE emp_cursor;
END;
--在显示游标中,使用FETCH...BULK COLLECT INTO语句提取所有数据
DECLARE
CURSOR emp_cursor IS
SELECT ename FROM emp WHERE deptno=10;
TYPE ename_table_type IS TABLE OF VARCHAR2(10);
ename_table ename_table_type;
BEGIN
OPEN emp_cursor;
FETCH emp_cursor BULK COLLECT INTO ename_table;
FOR i IN 1..ename_table.COUNT LOOP
dbms_output.put_line(ename_table(i));
END LOOP;
CLOSE emp_cursor;
END;
--在显示游标中,使用FETCH...BULK COLLECT INTO...LIMIT语句提取部分数据
DECLARE
TYPE name_array_type IS VARRAY(5) OF VARCHAR2(10);
name_array name_array_type;
CURSOR emp_cursor IS SELECT ename FROM emp;
rows INT:=5;
v_count INT:=0;
BEGIN
OPEN emp_cursor;
LOOP
FETCH emp_cursor BULK COLLECT INTO name_array LIMIT rows;
dbms_output.put('雇员名:');
FOR i IN 1..(emp_cursor%ROWCOUNT-v_count) LOOP
dbms_output.put(name_array(i)||' ');
END LOOP;
dbms_output.new_line;
v_count:=emp_cursor%ROWCOUNT;
EXIT WHEN emp_cursor%NOTFOUND;
END LOOP;
CLOSE emp_cursor;
END;
--使用游标属性
DECLARE
CURSOR emp_cursor IS
SELECT ename FROM emp WHERE deptno=10;
TYPE ename_table_type IS TABLE OF VARCHAR2(10);
ename_table ename_table_type;
BEGIN
IF NOT emp_cursor%ISOPEN THEN
OPEN emp_cursor;
END IF;
FETCH emp_cursor BULK COLLECT INTO ename_table;
dbms_output.put_line(emp_cursor%ROWCOUNT);
CLOSE emp_cursor;
END;
--基于游标定义记录变量
DECLARE
CURSOR emp_cursor IS
SELECT ename, sal FROM emp;
emp_record emp_cursor%ROWTYPE;
BEGIN
IF NOT emp_cursor%ISOPEN THEN
OPEN emp_cursor;
END IF;
LOOP
FETCH emp_cursor
INTO emp_record;
EXIT WHEN emp_cursor%NOTFOUND;
dbms_output.put_line('雇员名:' || emp_record.ename || ' ,雇员工资:' ||
emp_record.sal);
END LOOP;
END;
--参数游标
DECLARE
CURSOR emp_cursor(no NUMBER) IS
SELECT ename FROM emp WHERE deptno=no;
v_ename emp.ename%TYPE;
BEGIN
OPEN emp_cursor(10);
LOOP
FETCH emp_cursor INTO v_ename;
EXIT WHEN emp_cursor%NOTFOUND;
dbms_output.put_line(v_ename);
END LOOP;
CLOSE emp_cursor;
END;
使用游标更新或删除数据
CURSOR cursor_name(parameter_name datatype) IS select_statement FOR UPDATE [OF column_reference][NOWAIT];
FOR UPDATE子句用于在游标结果集数据上加行共享锁,以防止其他用户在相应行上执行DML操作,当SELECT语句引用到多张表时,使用OF子句可以确定哪些表要加锁,如果没有OF子句,则会在SELECT语句所引用的全部表上加锁;NOWAIT子句用于指定不等待锁。在提取了游标数据之后,为了更新或删除当前游标行数据,必须在UPDATE或DELETE语句中引用WHERE CURRENT OF子句。
--使用游标更新数据
DECLARE
CURSOR emp_cursor IS
SELECT ename,sal FROM emp FOR UPDATE;
v_ename emp.ename%TYPE;
v_oldsal emp.sal%TYPE;
BEGIN
OPEN emp_cursor;
LOOP
FETCH emp_cursor INTO v_ename,v_oldsal;
EXIT WHEN emp_cursor%NOTFOUND;
IF v_oldsal<2000 THEN
UPDATE emp set sal=sal+100 WHERE CURRENT OF emp_cursor;
END IF;
dbms_output.put_line(v_ename);
END LOOP;
CLOSE emp_cursor;
END;
--使用OF子句在特定表上加行共享锁
DECLARE
CURSOR emp_cursor IS
SELECT ename, sal, dname, emp.deptno
FROM emp, dept
WHERE emp.deptno = dept.deptno
FOR UPDATE OF emp.deptno;
emp_record emp_cursor%ROWTYPE;
BEGIN
OPEN emp_cursor;
LOOP
FETCH emp_cursor
INTO emp_record;
EXIT WHEN emp_cursor%NOTFOUND;
IF emp_record.deptno = 30 THEN
UPDATE emp set sal = sal + 100 WHERE CURRENT OF emp_cursor;
END IF;
dbms_output.put_line('雇员名:' || emp_record.ename || ' ,工资:'
||emp_record.sal);
END LOOP;
CLOSE emp_cursor;
END;
--使用NOWAIT子句
DECLARE
CURSOR emp_cursor IS
SELECT ename,sal FROM emp FOR UPDATE NOWAIT;
v_ename emp.ename%TYPE;
v_oldsal emp.sal%TYPE;
BEGIN
OPEN emp_cursor;
LOOP
FETCH emp_cursor INTO v_ename,v_oldsal;
EXIT WHEN emp_cursor%NOTFOUND;
IF v_oldsal<2000 THEN
UPDATE emp set sal=sal+100 WHERE CURRENT OF emp_cursor;
END IF;
dbms_output.put_line(v_ename);
END LOOP;
CLOSE emp_cursor;
END;
游标FOR循环。Oracle会隐含地打开游标、提取游标数据并关闭游标。
DECLARE
CURSOR emp_cursor IS
SELECT ename, sal FROM emp;
BEGIN
FOR emp_record IN emp_cursor LOOP
dbms_output.put_line('第' || emp_cursor%ROWCOUNT || '个雇员:' ||
emp_record.ename);
END LOOP;
END;
--在游标FOR循环中直接使用子查询
BEGIN
FOR emp_record IN (SELECT ename, sal FROM emp) LOOP
dbms_output.put_line(emp_record.ename);
END LOOP;
END;
使用游标变量。其使用步骤:定义游标变量、打开游标、提取游标数据、关闭游标。
1)首先必须定义REF CURSOR类型,然后才能定义游标变量。
TYPE ref_type_name IS REF CURSOR[RETURN return_type];
cursor_variable ref_type_name;
下面的步骤同显式游标...
--在定义REF CURSOR类型时不指定RETURN子句
DECLARE
TYPE emp_cursor_type IS REF CURSOR;
emp_cursor emp_cursor_type;
emp_record emp%ROWTYPE;
BEGIN
OPEN emp_cursor FOR SELECT * FROM emp where deptno=10;
LOOP
FETCH emp_cursor INTO emp_record;
EXIT WHEN emp_cursor%NOTFOUND;
dbms_output.put_line(emp_record.ename);
END LOOP;
CLOSE emp_cursor;
END;
--在定义REF CURSOR类型时指定RETURN子句
DECLARE
TYPE emp_record_type IS RECORD(name VARCHAR(10),salary NUMBER(6,2));
TYPE emp_cursor_type IS REF CURSOR RETURN emp_record_type;
emp_cursor emp_cursor_type;
emp_record emp_record_type;
BEGIN
OPEN emp_cursor FOR SELECT ename,sal FROM emp where deptno=20;
LOOP
FETCH emp_cursor INTO emp_record;
EXIT WHEN emp_cursor%NOTFOUND;
dbms_output.put_line(emp_record.name||' '||emp_record.salary);
END LOOP;
CLOSE emp_cursor;
END;
使用CURSOR表达式,用于返回嵌套游标
DECLARE
TYPE refcursor IS REF CURSOR;
CURSOR dept_cursor(no NUMBER) IS
SELECT a.dname,
CURSOR (SELECT ename, sal FROM emp WHERE deptno = a.deptno)
FROM dept a
WHERE a.deptno = no;
empcur refcursor;
v_dname dept.dname%TYPE;
v_ename emp.ename%TYPE;
v_sal emp.sal%TYPE;
BEGIN
OPEN dept_cursor(&no);
LOOP
FETCH dept_cursor INTO v_dname,empcur;
EXIT WHEN dept_cursor%NOTFOUND;
dbms_output.put_line('部门名:'||v_dname);
LOOP
FETCH empcur INTO v_ename,v_sal;
EXIT WHEN empcur%NOTFOUND;
dbms_output.put_line('雇员名:'||v_ename||', 工资:'||v_sal);
END LOOP;
END LOOP;
CLOSE dept_cursor;
END;