CH6游标管理.docx
《CH6游标管理.docx》由会员分享,可在线阅读,更多相关《CH6游标管理.docx(9页珍藏版)》请在冰豆网上搜索。
CH6游标管理
◆游标
在PL/SQL中内存中记录集合的指针,指向一行,提供逐行访问记录
在初始状态下指向的是首记录
游标的属性:
%found
%notfound
%isopen
%rowcount
Oracle游标分为显式游标和隐式游标
--隐式游标
PL/SQL中用户在使用数据操作语言(DML)时,预先定义一个名SQL的隐式游标
用户不能直接控制此游标,但可以通过检查隐式游标的属性获取信息
执行DML语句后,Oracle自动关闭
begin
updatescott.empsetsal=sal+1wheredeptno=50;
ifSQL%foundthen
dbms_output.put_line('表已更新...'||sql%rowcount||'行记录');
else
dbms_output.put_line('没有找到记录....');
endif;
ifnotsql%isopenthen
dbms_output.put_line('sql游标已经关闭');
endif;
commit;
end;
/*
显式游标用户自定义的游标
一般使用步骤:
声明游标:
cursor游标名is查询语句;
打开游标:
open游标名;
提取游标:
fetch游标名into变量;
关闭游标:
close游标名
*/
declare
cursorv_csisselect*fromscott.emp;--声明游标
v_tempv_cs%rowtype;
begin
openv_cs;--打开游标
loop
fetchv_csintov_temp;--提取游标
dbms_output.put_line(v_temp.ename||''||v_temp.hiredate);
exitwhenv_cs%notfound;--如果游标没有记录退出循环
endloop;
closev_cs;--关闭游标
end;
--游标属性说明
declare
v_deptnumber;
cursorv_curisselect*fromscott.empwheredeptno=v_dept;
c_tempv_cur%rowtype;
begin
v_dept:
=30;
openv_cur;
dbms_output.put_line('fetch前游标总记录数:
'||v_cur%rowcount);
if(v_cur%isopen)then
dbms_output.put_line('游标已经打开');
endif;
loop
fetchv_curintoc_temp;
dbms_output.put('游标总记录数:
'||v_cur%rowcount||'');
if(v_cur%found)then
dbms_output.put_line(c_temp.ename||''||c_temp.sal);
else
dbms_output.put_line('游标已经没有记录');
exit;
endif;
endloop;
closev_cur;
end;
/*带参数的游标
声明:
cursor游标名(形参数据类型...)isselect....where...=形参;
使用:
open游标名(实参);
*/
declare
v_inputscott.emp.deptno%type;
v_namescott.emp.ename%type;
v_salscott.emp.sal%type;
cursorc_cur(v_paramnumber)isselectename,salfromscott.empwheredeptno=v_param;
begin
v_input:
='&部门编号';
openc_cur(v_input);--传入实参
loop
fetchc_curintov_name,v_sal;
exitwhenc_cur%notfound;
dbms_output.put_line(v_name||''||v_sal);
endloop;
closec_cur;
end;
/*
用于delete/update的游标
声明时指定forupdate[of列][nowait]加行级锁
[of列]如果涉及到多张表,默认会在所有表上加行共享锁,
为了只在特定表上加行共享锁,需要在forupdate子句后带有of子句指定字段名
更新时update......wherecurrentof游标名
*/
declare
cursorc_curisselect*fromscott.empforupdate;
v_tempemp%rowtype;
begin
openc_cur;
loop
fetchc_curintov_temp;
exitwhenc_cur%notfound;
if(v_temp.sal<1000)then
updatescott.empsetsal=sal+1wherecurrentofc_cur;
endif;
endloop;
closec_cur;
commit;
end;
/*
for循环游标
自动打开,关闭,自动获取记录
声明游标:
cursorcursor_nameisselect......
使用:
forv_nameincursor_name(或select语句)
loop.....endloop;
*/
例1
declare
cursorc_curisselect*fromscott.emp;
begin
forv_tempinc_curloop--v_temp局部变量,无需声明
dbms_output.put_line(v_temp.ename||''||v_temp.sal);
endloop;
end;
例2
declare
v_inputvarchar2
(1);
begin
v_input:
='&请选择';
ifv_input='a'then
forv_recin(select*fromemp)
loop
dbms_output.put_line(v_rec.empno||':
'||v_rec.ename||''||
v_rec.hiredate||''||v_rec.sal);
endloop;
elsifv_input='b'then
forv_recin(select*fromdept)
loop
dbms_output.put_line(v_rec.deptno||':
'||v_rec.dname);
endloop;
else
dbms_output.put_line('pleaseinput...');
return;
endif;
end;
/*********for循环游标(带参数)*************
声明游标:
cursorcursor_name(形参列表)isselect......
使用:
forv_nameincursor_name(实参)
loop.....endloop;
****/
declare
v_inputemp.deptno%type;
cursorc(deptnumber)isselect*fromempwheredeptno=dept;
begin
v_input:
='&部门编号';
forv_recinc(v_input)loop
dbms_output.put_line(v_rec.ename||':
'||v_rec.sal);
endloop;
end;
/**************refcursor游标*******************
用于处理运行时动态执行的SQL查询即在open游标时动态指定sql语句
声明refcursor类型:
TYPEISREFCURSOR[RETURN];
声明ref游标类型的游标变量:
变量名游标名;
打开方式:
opencursor_namefor查询语句
***/
声明强类型的REF游标:
TYPEt_refcurISREFCURSORRETURNemp%ROWTYPE;
V_curt_refcur;
声明弱类型的REF游标(用于动态SQL)
TYPEt_refcurISREFCURSOR;
V_curt_refcur;
declare
typev_refcurisrefcursor;---声明refcursor类型
refcurv_refcur;--声明refcursor类型的游标变量
v_idnumber;
v_namevarchar2(20);
v_selectionvarchar2
(1):
=upper(substr('&请输入员工(E)或部门编号(D)',1,1));
begin
ifv_selection='E'then
openrefcurforselectempno,enamefromemp;--打开游标
dbms_output.put_line('====员工信息====');
elsifv_selection='D'then
openrefcurforselectdeptno,dnamefromdept;--打开游标
dbms_output.put_line('====部门信息====');
else
dbms_output.put_line('请输入员工信息(E)或部门信息(D)');
return;--返回
endif;
fetchrefcurintov_id,v_name;--提取游标
whilerefcur%foundloop
dbms_output.put_line('#'||v_id||':
'||v_name);
fetchrefcurintov_id,v_name;
endloop;
closerefcur;--关闭游标
end;
/*******动态SQL(使用refcursor返回多行记录)***********
使用占位符
Opencursor_nameforv_sql[using占位符对应的变量]
*****/
declare
typev_refcurisrefcursor;
v_curv_refcur;
v_empemp%rowtype;
v_salarynumber;
v_sqlvarchar2(100);
v_dnonumber;
begin
v_salary:
='&请输入薪水标准';
v_dno:
='&请输入部门编号';
v_sql:
='select*fromempwheresal>:
1anddeptno=:
2orderbysaldesc';
openv_curforv_sqlusingv_salary,v_dno;
dbms_output.put_line('薪水大于'||v_salary||'的员工:
');
loop
fetchv_curintov_emp;
exitwhenv_cur%notfound;
dbms_output.put_line('姓名:
'||v_emp.ename||'薪水:
'||v_emp.sal);
endloop;
closev_cur;
end;