参数游标是带有参数的游标,在定义参数游标之后,当使用不同参数值多次打开游标时,可以产生不同的结果集,语法如下:
cursor cursor_name(parameter_name datatype) is select_statement;
定义参数游标时,游标参数只能指定数据类型,而不能指定长度。
示例如下:
- declare
- cursor temp_cursor(no number) is select name from cip_temps where id=no;
- v_name cip_temps.name%type;
- begin
- open temp_cursor(1);
- loop
- fetch temp_cursor into v_name;
- exit when temp_cursor%notfound;
- dbms_output.put_line(v_name);
- end loop;
- close temp_cursor;
- end;
declare cursor temp_cursor(no number) is select name from cip_temps where id=no; v_name cip_temps.name%type; begin open temp_cursor(1); loop fetch temp_cursor into v_name; exit when temp_cursor%notfound; dbms_output.put_line(v_name); end loop; close temp_cursor; end;
二、使用游标更新或删除数据
通过使用显示游标,不仅可以一行一行的处理select语句结果,而且也可以更新或删除当前游标的数据,注意,如果要通过游标更新或删除数据,在定义游标时一定要带有for update子句,语法如下:
cursor cursor_name(parameter_name datatype) is select_statement for updae [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 from table_name where current of cursor_name;
1、使用游标更新数据
- declare
- cursor temp_cursor is select name,address,id from cip_temps for update;
- v_name cip_temps.name%type;
- v_address cip_temps.ADDRESS%type;
- v_id cip_temps.id%type;
- begin
- open temp_cursor;
- loop
- fetch temp_cursor into v_name,v_address,v_id;
- exit when temp_cursor%NOTFOUND;
- if(v_id>4) then
- update cip_temps set name='name'||to_char(v_id),address='address'||to_char(v_id) where current of temp_cursor;
- end if;
- end loop;
- close temp_cursor;
- end;
declare cursor temp_cursor is select name,address,id from cip_temps for update; v_name cip_temps.name%type; v_address cip_temps.ADDRESS%type; v_id cip_temps.id%type; begin open temp_cursor; loop fetch temp_cursor into v_name,v_address,v_id; exit when temp_cursor%NOTFOUND; if(v_id>4) then update cip_temps set name='name'||to_char(v_id),address='address'||to_char(v_id) where current of temp_cursor; end if; end loop; close temp_cursor; end;
2、使用游标删除数据
- declare
- cursor temp_cursor is select name,address,id from cip_temps for update;
- v_name cip_temps.name%type;
- v_address cip_temps.ADDRESS%type;
- v_id cip_temps.id%type;
- begin
- open temp_cursor;
- loop
- fetch temp_cursor into v_name,v_address,v_id;
- exit when temp_cursor%NOTFOUND;
- if(v_id>2) then
- delete from cip_temps where current of temp_cursor;
- end if;
- end loop;
- close temp_cursor;
- end;
declare cursor temp_cursor is select name,address,id from cip_temps for update; v_name cip_temps.name%type; v_address cip_temps.ADDRESS%type; v_id cip_temps.id%type; begin open temp_cursor; loop fetch temp_cursor into v_name,v_address,v_id; exit when temp_cursor%NOTFOUND; if(v_id>2) then delete from cip_temps where current of temp_cursor; end if; end loop; close temp_cursor; end;
3、使用of子句在特定表加行共享锁。
如果使用子查询涉及到多张表,那么默认情况下会在所有表上加行共享锁,为了只在特定表上加行共享锁,需要在for update子句后带有of子句,of后面跟字段名,如果跟表名或游标名称,则会报错:标示符无效。示例如下:
- declare
- cursor gData is select name,address,cip_temps.id from cip_temps,cip_t
- where cip_temps.id=cip_t.id for update of address;
- rs gData%rowtype;
- begin
- open gData;
- loop
- fetch gData into rs;
- exit when gData%notfound;
- if rs.id=1 then
- delete from cip_temps where current of gData;
- else
- update cip_temps set name='塞北的雪' where current of gData;
- end if;
- end loop;
- close gData;
- end;
declare cursor gData is select name,address,cip_temps.id from cip_temps,cip_t where cip_temps.id=cip_t.id for update of address; rs gData%rowtype; begin open gData; loop fetch gData into rs; exit when gData%notfound; if rs.id=1 then delete from cip_temps where current of gData; else update cip_temps set name='塞北的雪' where current of gData; end if; end loop; close gData; end;
4、使用nowait子句
使用for update语句对被作用于行加锁,如果其他会话已经在被作用于行上加锁,那么默认情况下当前会话要一直等待对方释放锁,通过在for update子句中指定 nowait语句,可以避免等待锁,当指定了nowait子句之后,如果其他会话已经在被作用行加锁,那么当前会话会显示错误提示信息,并退出PL/SQL,示例如下:
- declare
- cursor gData is select name,address,cip_temps.id from cip_temps,cip_t
- where cip_temps.id=cip_t.id for update nowait;
- rs gData%rowtype;
- begin
- open gData;
- loop
- fetch gData into rs;
- exit when gData%notfound;
- if rs.id=1 then
- delete from cip_temps where current of gData;
- else
- update cip_temps set name='塞北的雪' where current of gData;
- end if;
- end loop;
- close gData;
- end;
declare cursor gData is select name,address,cip_temps.id from cip_temps,cip_t where cip_temps.id=cip_t.id for update nowait; rs gData%rowtype; begin open gData; loop fetch gData into rs; exit when gData%notfound; if rs.id=1 then delete from cip_temps where current of gData; else update cip_temps set name='塞北的雪' where current of gData; end if; end loop; close gData; end;
三、游标for循环
使用游标for循环是循环游标最简单的方法,oracle会隐含打开游标、循环提取数据、关闭游标,语法如下:
for record_name in cursor_name loop
..........
end loop;
如上所示:cursor_name是已经定义的游标名称,record_name是oracle隐含定义的记录变量。
1、使用游标for循环
当使用游标开发程序时,建议使用for循环,从而简化代码程序,示例如下:
- declare
- cursor temp_cursor is select name,age,address,id from cip_temps;
- begin
- for emp_record in temp_cursor loop
- dbms_output.put_line(temp_cursor%rowcount||'第一行数据:'||emp_record.name||':'|| emp_record.age||':'|| emp_record.address||':'|| emp_record.id);
- end loop;
- end;
declare cursor temp_cursor is select name,age,address,id from cip_temps; begin for emp_record in temp_cursor loop dbms_output.put_line(temp_cursor%rowcount||'第一行数据:'||emp_record.name||':'|| emp_record.age||':'|| emp_record.address||':'|| emp_record.id); end loop; end;
2、在游标for循环时直接使用子查询
- declare
- begin
- for emp_record in (select * from cip_temps) loop
- dbms_output.put_line('第一行数据:'||emp_record.name||':'|| emp_record.age||':'|| emp_record.address||':'|| emp_record.id);
- end loop;
- end;
create or replace procedure aaaaa(interfaceid in varchar2,
areacode in varchar2,
aresult out int) is
cursor interfacesa is
select t.interface_id,t.interface_class_name from uip_interface t where t.interface_class_id = interfaceid and t.area_code = areacode;
bresult int :=0;
-- cresult uip_interface.interface_class_id%type;
v_emp interfacesa%rowtype;
begin
open interfacesa;
loop
fetch interfacesa into v_emp;
exit when interfacesa%notfound ;
dbms_output.put_line('employeename is '||v_emp.interface_id);
bresult:=bresult+1;
end loop;
close interfacesa;
aresult:=bresult;
/**for emp_record in interfacesa(interfaceid,areacode) loop
dbms_output.put_line('id is '||emp_record.interface_id||';name:'||emp_record.interface_class_name);
bresult:=bresult+1;
end loop;
aresult:=bresult;**/
end aaaaa;
Oracle 系列:REF Cursor
在上文 Oracle 系列:Cursor (拜见:http://blog.csdn.net/qfs_v/archive/2008/05/06/2404794.aspx)中
提到个思虑:如何让游标作为参数传递? 解决这个题目就须要用到 REF Cursor 。
1,什么是 REF游标 ?
动态接洽关系成果集的姑且对象。即在运行的时辰动态决意履行查询。
2,REF 游标 有什么感化?
实如今法度间传递成果集的功能,哄骗REF CURSOR也可以实现BULK SQL,从而进步SQL机能。
3,静态游标和REF 游标的差别是什么?
①静态游标是静态定义,REF 游标是动态接洽关系;
②应用REF 游标需REF 游标变量。
③REF 游标能做为参数进行传递,而静态游标是不成能的。
4,什么是REF 游标变量?
REF游标变量是一种 引用 REF游标类型 的变量,指向动态接洽关系的成果集。
5,怎么应用 REF游标 ?
①声明REF 游标类型,断定REF 游标类型;
⑴强类型REF游标:指定retrun type,REF 游标变量的类型必须和return type一致。
语法:Type REF游标名 IS Ref Cursor Return 成果集返回记录类型;
⑵弱类型REF游标:不指定return type,能和任何类型的CURSOR变量匹配,用于获取任何成果集。
语法:Type REF游标名 IS Ref Cursor;
②声明Ref 游标类型变量;
语法:变量名 已声明Ref 游标类型;
③打开REF游标,接洽关系成果集 ;
语法:Open Ref 游标类型变量 For 查询语句返回成果集;
④获取记录,操纵记录;
语法:Fatch REF游标名 InTo 姑且记录类型变量或属性类型变量列表;
⑤封闭游标,完全开释资料;
语法:Close REF游标名;
例子:强类型REF游标
/*conn scott/tiger*/
Declare
Type MyRefCurA IS REF CURSOR RETURN emp%RowType;
Type MyRefCurB IS REF CURSOR RETURN emp.ename%Type;
vRefCurA MyRefCurA;
vRefCurB MyRefCurB;
vTempA vRefCurA%RowType;
vTempB vRefCurB.ename%Type;
Begin
Open vRefCurA For Select * emp Where SAL > 2000;
Loop
Fatch vRefCurA InTo vTempA;
Exit When vRefCurA%NotFound;
DBMS_OUTPUT.PUT_LINE(vRefCurA%RowCount||"" ""|| vTempA.eno||"" ""||vTempA.ename ||"" ""||vTempA.sal)
End Loop;
Close vRefCurA;
DBMS_OUTPUT.PUT_LINE(""-------------------------------------------------------------------------------------------------------"");
Open vRefCurB For Select ename emp Where SAL > 2000;
Loop
Fatch vRefCurB InTo vTempB;
Exit When vRefCurB%NotFound;
DBMS_OUTPUT.PUT_LINE(vRefCurB%RowCount||"" ""||vTempB)
End Loop;
Close vRefCurB;
DBMS_OUTPUT.PUT_LINE(""-------------------------------------------------------------------------------------------------------"");
Open vRefCurA For Select * emp Where JOB = ""CLERK"";
Loop
Fatch vRefCurA InTo vTempA;
Exit When vRefCurA%NotFound;
DBMS_OUTPUT.PUT_LINE(vRefCurA%RowCount||"" ""|| vTempA.eno||"" ""||vTempA.ename ||"" ""||vTempA.sal)
End Loop;
Close vRefCurA;
End;
例子:弱类型REF游标
/*conn scott/tiger*/
Declare
Type MyRefCur IS Ref Cursor;
vRefCur MyRefCur;
vtemp vRefCur%RowType;
Begin
Case(&n)
When 1 Then Open vRefCur For Select * emp;
When 2 Then Open vRefCur For Select * dept;
Else
Open vRefCur For Select eno, ename emp Where JOB = ""CLERK"";
End Case;
Close vRefCur;
End;
6,如何让REF游标作为参数传递?
--作为函数返回值
create or replace function returnacursor return sys_refcursor
is
v_csr sys_refcursor;
begin
open v_csr for a1 test3;
return v_csr;
end;
/
declare
c sys_refcursor;
a1 char(2);
begin
c:=returnacursor;
loop
fetch c into a1;
exit when c%notfound;
dbms_output.put_line(a1);
end loop;
close c;
end;
/
--作为参数
create or replace procedure proc_ref_cursor (rc in sys_refcursor) as
v_a number;
v_b varchar2(10);
begin
loop
fetch rc into v_a, v_b;
exit when rc%notfound;
dbms_output.put_line(v_a || "" "" || v_b);
end loop;
end;
/
declare
v_rc sys_refcursor;
begin
open v_rc for
a1,a2 test3;
proc_ref_cursor(v_rc);
close v_rc;
end;
/