目标:
使用显示游标以及游标属性;
使用参数游标;
使用显示游标更新或删除数据;
使用游标FOR循环;
使用右边变量;
使用FETCH .. BULK COLLECT INTO 语句 CURSOR表达式。
一、显示游标
专门用于处理SELECT 语句返回的多行数据。
1、定义游标:CURSOR cursor_name IS select_statement;
2、打开游标 open cursor_name;
3、提取数据
FETCH cursor_name INTO variable1,variable2,...;
FETCH cursor_name INTO collect1,collect2,...[LIMIT rows];
4、关闭游标 close cursor_name;
游标属性:
%OPEN;
%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
fecth emp_cursor into v_ename,v_sal;
exit when emp_cursor%notfound;
……
end loop;
close emp_cursor;
end;
使用FECTH ... BULK COLLECT INTO 语句提取所有数据
declare
cursor emp_cursor is
select ename from emp where deptno=10;
type ename_type_name is table of varchar2(10);
ename_table ename_type_type;
begin
open emp_cursor;
fecth emp_cursor BULK COLLECT INTO ename_table;
FOR i in i..emp_cursor.count loop
........emp_cursor(i)
end loop;
close emp_cursor;
end;
使用FECTH ... BULK COLLECT INTO..LIMIT 语句提取部分数据
以每次提取5行数据为例
declare
cursor emp_cursor is select ename from emp;
type emp_varray_type is varray(5) of emp.ename%type;
rows int:=5;
v_count:=0;
emp_varray emp_varray_type;
begin
open emp_cursor;
loop
FETCH emp_cursor BULK COLLECT INTO emp_varray LIMIT rows; --此时游标指针已经指向了rows
dbms_output.put('雇员:');
for i in 1..(emp_cursor%rowcount-v_count) loop
dbms_output.put(emp_cursor(i)||' ');
end loop;
v_count:=emp_cursor%rowcount;
dbms_output.new_line;
exit when emp_cursor%notfound;
end loop;
close emp_cursor;
end;
基于游标定义记录变量 建议使用
使用%rowcount属性不仅可以基于表和视图定义记录变量,也可以基于游标定义记录变量。当基于游标定义记录变量时,记录成员名实际就是SELECT语句的列名或列别名。
declare
cursor emp_cursor is select ename,sal from emp;
v_emp emp_cursor%rowtype;
begin
open emp_cursor;
loop
FETCH emp_cursor INTO v_emp;
exit when emp_cursor%notfound;
dbms_output.put('雇员:'||v_emp.ename||'工资是:'||v_emp.sal);
end loop;
close emp_cursor;
end;
参数游标
CURSOR cursor_name(parameter_name datatype) IS select_statement;
注意:游标参数只能指定数据类型,而不能指定长度。
使用游标更新或删除数据
通过使用显示游标,不仅可以一行一行的处理SELECT语句的结果,而且也可以更新或者删除当前游标行的数据。但是在定义游标时必须有FOR UPDATE 字句;
CURSOR cursor_name(param_type datatype) is select_statement FOR UPDATE [OF column_reference] [NOWAIT];
FOR UPDATE 字句用于在游标结果集数据上加行共享锁,以防止其他用户在相应行上执行DML操作;当SELECT语句引用到多张表时,使用OF字句可以确定哪些表要加锁,
如果没有OF字句,则会在SELECT语句所引用的全部表上加锁;NOWAIT 字句用于指定不等待锁。在提取了游标数据之后,为了更新或删除当前游标行数据,必须在UPDATE
或DELETE语句中引用WHERE CURRENT OF字句。
语句如下:
UPDATE table_name SET column=..WHERE CURRENT OF cursor_name;
DELETE table_name WHERE CURRENT OF cursor_name;
使用游标更新数据
declare
cursor emp_cursor is select ename,sal from emp FOR UPDATE;
v_emp emp_cursor%rowtype;
begin
open emp_cursor;
loop
FETCH emp_cursor INTO v_emp;
EXIT WHEN emp_cursor%notfound;
IF v_emp.sal<2000 THEN
update emp set sal=sal+1000 WHERE CURRENT OF emp_cursor;
end if;
end loop;
close emp_cursor;
end;
使用游标删除数据
使用OF字句在特定表伤加行共享锁
CURSOR emp_surspor IS SELECT ename,sal,dname,emp.deptno FORM emp,dept WHERE emp.depno=dept.depno FOR UPDATE OF emp.depno;
使用NOWAIT 字句
使用FOR UPDATE 语句对被作用行加锁,如果其他绘画已经在被作用行上加锁,那么在默认情况下当前绘画会要一直等到对方释放锁。使用NOWAIT 如果其他会话已经在被作用行上加锁,则抛异常。
使用游标变量
1 定义REF CURSOR 类型和游标变量
TYPE ref_type_name IS REF CURSOR [RETURN return_type];--加return 字句,在打开游标时select 语句的返回结果必须与RETURN 字句所指定的记录类型相匹配。
cursor_variable ref_type_name;
2 打开游标
OPEN cursor_variable FOR select_statement;
3 提取游标数据
4 关闭游标变量
close cursor_variable;
DECLARE
TYPE emp_record_type IS RECORD(
name VARCHAR2(1O),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 FORM emp WHERE depno=20;
loop
FECTH emp_cursor INTO emp_record;
EXIT WHEN emp_cursor%NOTFOUND;
......DO SOMETHING
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 FORM 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('部门:');
open empcur;
LOOP
FETCH empcur INTO v_ename,v_sal;
EXIT WHEN empcur%NOTFOUND;
dbms_output.put('雇员名:'||v_ename||'工资:'||v_sal);
END LOOP;
CLOSE empcur;
END LOOP;
CLOSE dept_cursor;
END;
-----------------------------
DECLARE
TYPE order_items_cursor IS REF CURSOR;
CURSOR order_cursor(ID NUMBER) IS SELECT o.id,
CURSOR(SELECT oi.id,oi,total,oi,pro_name FROM order_item oi where oi.orderId=o.id) from orders o ;
order_itens_ref order_items_cursor ;
v_oid orders.id%type;
v_ooid order_item.id%type;
v_total order_item.total%type;
v_name order_item.pro_name%type;
BEGIN
OPEN order_cursor(&orderid);
LOOP
FETCH order_cursor INTO v_oid,order_itens_ref;
EXIT WHEN order_cursor%NOTFOUND;
dbms_output.put_line('订单号:'||v_oid);
OPEN order_itens_ref;
LOOP
FETCH order_itens_ref INTO v_ooid, v_total,v_name;
EXIT WHEN order_itens_ref%NOTFOUND;
dbms_output.put_line('条款号:'||v_ooid||'总价'||v_total||'商品名:'||v_name);
END LOOP;
CLOSE order_itens_ref;
END LOOP;
CLOSE order_cursor;
END;