目录
- 阅读准备事项
- 概述
- 显式游标
- 使用显式游标步骤
- 显式游标属性
- 游标for循环
- 参数游标
- 更新或删除游标行
- 游标变量
- 使用批量提取
- 使用cursor表达式
- 练习试题
阅读准备事项
请先阅读本学习系列之“ORACLE学习系列注意事项”。
CREATE TABLE emp_learn AS SELECT * FROM employees; CREATE TABLE dept_learn AS SELECT * FROM departments;
概述
当在PL/SQL块中执行DML和单行SELECT INTO语句时,oracle会分配隐含游标。为了处理SELECT语句返回的多行数据,需要使用显式游标。从oracle database 9i 开始,也可以使用select ...bulk collect into语句将多行数据存放到PL/SQL集合变量。
这一节主要学习在PL/SQL块中使用显示游标的方法,学习目标为:
- 学会使用显式游标、游标for循环和参数游标。
- 学会使用游标更新或删除表行。
- 学会使用游标变量。
- 学会使用批量提取和cursor表达式。
显式游标
PL/SQL包含隐含游标和显式游标两种游标类型,其中隐含游标用于处理单行select into和DML语句,而显示游标则用于处理select语句返回的多行数据。
- 使用显示游标步骤
a)定义游标:用于指定游标对应的select语句。语法:
CURSOR cursor_name IS select_statement;
b)打开游标:用于执行所对应的select语句,并将行数据存放到游标结果集。
OPEN cursor_name;
c)提取数据:将结果集中的行数据存放到PL/SQL变量。
FETCH cursor_name INTO variable1[variable2,...];
d)关闭游标:用于释放游标结果集的数据。
CLOSE cursor_name;
- 显式游标属性
包含%ISOPEN、%FOUND、%NOTFOUND和&ROWCOUNT四种。结合示例解释含义:
a)%ISOPEN:该属性用于检测游标是否已经打开。如果已经打开,则返回true,否则返回false。
b)%FOUND:检测游标结果集是否存在数据。如果存在,则返回true,否则返回false。
c)%NOTFOUND:检测结果集是否未提取到数据。如果不存在数据,则返回true,否则返回false。
d)%ROWCOUNT:返回已提取的实际行数。
示例:
1 DECLARE 2 CURSOR emp_learn_cursor01 IS 3 SELECT first_name,salary,department_id FROM emp_learn WHERE department_id=&dno; 4 TYPE emp_table_type IS TABLE OF emp_learn_cursor01%ROWTYPE INDEX BY Binary_Integer; 5 emp_table emp_table_type; 6 i INT; 7 BEGIN 8 --OPEN emp_learn_cursor01; 9 IF NOT emp_learn_cursor01%ISOPEN THEN 10 OPEN emp_learn_cursor01; 11 dbms_output.put_line('检测到游标未打开,现已打开'); 12 END IF; 13 LOOP 14 dbms_output.put_line('ROWCOUNT的值:'||emp_learn_cursor01%ROWCOUNT); 15 i:=emp_learn_cursor01%ROWCOUNT+1;--让集合下标从1开始 16 FETCH emp_learn_cursor01 INTO emp_table(i); 17 --EXIT WHEN emp_learn_cursor01%NOTFOUND; 18 IF emp_learn_cursor01%NOTFOUND THEN 19 dbms_output.put_line('未提取到行'); 20 EXIT; 21 ELSIF emp_learn_cursor01%FOUND THEN 22 dbms_output.put_line('提取到'||emp_learn_cursor01%ROWCOUNT||'行'); 23 END IF; 24 dbms_output.put_line('姓名:'||emp_table(i).first_name||',部门:'||emp_table(i).department_id||',工资:'||emp_table(i).salary); 25 END LOOP; 26 CLOSE emp_learn_cursor01;
- 游标for循环
此功能为简化处理显式游标数据处理,当使用游标for循环时,oracle会隐含的打开游标、提取数据并关闭游标。语法:
FOR recorde_name IN cursor_name LOOP
statements;
END LOOP;
cursor_name用于指定已定义的游标名,而recorde_name是基于游标隐含定义的记录变量。在执行循环之前,oracle会隐含的打开游标,并且每循环一次自动提取一行数据,在提取了所有数据之后自动退出循环并隐含的关闭游标。
示例:
DECLARE CURSOR emp_learn_cursor01 IS SELECT first_name,salary,department_id FROM emp_learn WHERE department_id=&dno; BEGIN FOR emp_recorde IN emp_learn_cursor01 LOOP dbms_output.put_line('提取到'||emp_learn_cursor01%ROWCOUNT||'行'); dbms_output.put_line('姓名:'||emp_recorde.first_name||',部门:'||emp_recorde.department_id||',工资:'||emp_recorde.salary); END LOOP; END;
- 参数游标
参数游标是指带有参数的游标。通过使用参数游标,使用不同的参数值可以生成不同的游标结果集。定义参数游标的语法:
CURSOR cursor_name(parameter_name data_type) IS select_statement;
OPEN cursor_name(parameter_value);(当不以游标for循环方式使用时,需要以此方式打开)
- 更行或删除游标行
通过使用显式游标,不仅可以取得游标结果集的数据,而且可以更新或删除游标结果集的当前行。需要注意的是:当使游标更新或者删除数据时,定义游标必须带有for update子句以加行共享锁,并且在更新或者删除游标行时必须带有where current of子句。语法:
CURSOR cursor_name IS select_statement FOR UPDATE [OF column_reference] [NOWAIT];
UPDATE table_name SET column=... WHERE CURRENT OF cursor_name;
DELETE FROM table_name WHERE CURRENT OF cursor_name;
示例:
1 DECLARE 2 --定义游标,寻找出名字相同,且相同数为输入的特定参数的雇员的名字 3 CURSOR name_cursor(v_same_count NUMBER) IS 4 SELECT first_name FROM emp_learn GROUP BY first_name HAVING COUNT(*)=v_same_count; 5 --定义游标,提取特定名字的雇员信息,以便执行UD操作 6 CURSOR emp_info_cursor(v_name VARCHAR2) IS 7 SELECT employee_id,first_name,salary FROM emp_learn WHERE first_name=v_name ORDER BY employee_id DESC FOR UPDATE; 8 v_temp_name emp_learn.first_name%TYPE; 9 v_cnt INT:=&cnt; 10 v_num INT; 11 BEGIN 12 SELECT COUNT(*) INTO v_num FROM(SELECT first_name FROM emp_learn GROUP BY first_name HAVING COUNT(*)=v_cnt); 13 dbms_output.put_line('操作前,名字相同,且相同数为'||v_cnt||'的分组数为'||v_num); 14 15 SAVEPOINT s1; 16 FOR emp_name_record IN name_cursor(v_cnt) LOOP 17 FOR emp_info_record IN emp_info_cursor(emp_name_record.first_name) LOOP 18 dbms_output.put_line('开始-雇员:'||emp_info_record.employee_id||',姓名'||emp_info_record.first_name||'工资'||emp_info_record.salary); 19 CASE emp_info_cursor%ROWCOUNT 20 WHEN 1 THEN 21 UPDATE emp_learn SET salary=salary*0.9 WHERE CURRENT OF emp_info_cursor; 22 WHEN 2 THEN 23 DELETE FROM emp_learn WHERE CURRENT OF emp_info_cursor; 24 WHEN 3 THEN 25 UPDATE emp_learn SET salary=salary*1.1 WHERE CURRENT OF emp_info_cursor; 26 ELSE 27 NULL; 28 END CASE; 29 END LOOP; 30 FOR emp_info_record IN emp_info_cursor(emp_name_record.first_name) LOOP 31 dbms_output.put_line('结束-雇员:'||emp_info_record.employee_id||',姓名'||emp_info_record.first_name||'工资'||emp_info_record.salary); 32 END LOOP; 33 END LOOP; 34 dbms_output.put_line('操作结束'); 35 36 SELECT COUNT(*) INTO v_num FROM(SELECT first_name FROM emp_learn GROUP BY first_name HAVING COUNT(*)=v_cnt); 37 dbms_output.put_line('操作完成后,名字相同,且相同数为'||v_cnt||'的分组数为'||v_num); 38 ROLLBACK TO s1; 39 END;
游标变量
游标变量是基于REF CURSOR类型所定义的变量,它实际是指向内存地址指针。使用显式游标只能定义静态游标,而通过使用游标变量可以在打开游标时指定其所对应的select语句,从而实现动态游标。注意:不能在远程子程序中使用游标变量。当指定return子句时,那么在打开游标时select子句的返回结果必须与return子句指定的记录类型匹配。使用步骤:
a)定义REF CURSOR类型和游标变量
TYPE ref_type_name IS REF CURSOR [RETURN return_type];
cursor_variable ref_type_name;
b)打开游标变量,指定游标变量所对应的select语句。select语句被执行,数据存放到了游标结果集
OPEN cursor_variable FOR select_statement;
c)提取数据
FETCH cursor_variable INTO variable1,variable2,...;
d)关闭游标变量
CLOSE cursor_variable;
示例:
1 DECLARE 2 TYPE ref_cursor_type IS REF CURSOR RETURN emp_learn%ROWTYPE; 3 emp_cursor ref_cursor_type; 4 emp_record emp_cursor%ROWTYPE; 5 BEGIN 6 OPEN emp_cursor FOR 7 SELECT * FROM emp_learn WHERE department_id=&dept; 8 LOOP 9 FETCH emp_cursor INTO emp_record; 10 EXIT WHEN emp_cursor%NOTFOUND; 11 dbms_output.put_line(emp_record.first_name||'-'||emp_record.department_id); 12 END LOOP; 13 CLOSE emp_cursor; 14 END;
使用批量提取
此功能为oracle 9i的新功能,通过使用fetch ... bulk collect into可以从游标结果集中一次提取所有数据。也可以使用limit子句限制每次提取的行数。
示例:
1 DECLARE 2 TYPE ref_cursor_type IS REF CURSOR; 3 emp_ref_cursor ref_cursor_type; 4 TYPE emp_table_type IS TABLE OF emp_learn%ROWTYPE; 5 emp_table emp_table_type; 6 TYPE emp_arrary_type IS VARRAY(10) OF emp_learn.first_name%TYPE; 7 emp_name_array emp_arrary_type; 8 BEGIN 9 OPEN emp_ref_cursor FOR 10 SELECT * FROM emp_learn WHERE department_id=&dept; 11 FETCH emp_ref_cursor BULK COLLECT INTO emp_table; 12 FOR i IN 1..emp_table.count LOOP 13 dbms_output.put_line(emp_table(i).first_name||'-'||emp_table(i).department_id); 14 END LOOP; 15 CLOSE emp_ref_cursor; 16 17 --使用limit 18 OPEN emp_ref_cursor FOR 19 SELECT first_name FROM emp_learn; 20 FETCH emp_ref_cursor BULK COLLECT INTO emp_name_array LIMIT 5; 21 CLOSE emp_ref_cursor; 22 dbms_output.put_line('被限制后数组的长度为:'||emp_name_array.count); 23 END;
使用CURSOR表达式
cursor表达式用于实现嵌套游标,也是oracle 9i的新特性。通过使用cursor表达式,可以在PL/SQL块中处理基于多张表的复杂关联数据。为了可以在PL/SQL块中取得嵌套游标的数据,需要定义游标变量和嵌套循环。语法:
CURSOR(subquery)
示例:
1 DECLARE 2 CURSOR dept_cursor(deptno NUMBER) IS 3 SELECT department_name,CURSOR(SELECT first_name,salary FROM employees WHERE department_id=d.department_id) 4 FROM dept_learn d WHERE d.department_id=deptno; 5 TYPE ref_cursor_type IS REF CURSOR; 6 emp_cursor ref_cursor_type; 7 v_dname dept_learn.department_name%TYPE; 8 TYPE emp_record_type IS RECORD( 9 first_name VARCHAR2(30), 10 salary NUMBER 11 ); 12 emp_record emp_record_type; 13 BEGIN 14 OPEN dept_cursor(&dno); 15 LOOP 16 FETCH dept_cursor INTO v_dname,emp_cursor; 17 EXIT WHEN dept_cursor%NOTFOUND; 18 LOOP 19 FETCH emp_cursor INTO emp_record; 20 EXIT WHEN emp_cursor%NOTFOUND; 21 dbms_output.put_line('部门名:'||v_dname||',雇员姓名:'||emp_record.first_name||',工资:'||emp_record.salary); 22 END LOOP; 23 END LOOP; 24 CLOSE dept_cursor; 25 END;