SQLSERVER游标.docx
- 文档编号:16696444
- 上传时间:2023-07-16
- 格式:DOCX
- 页数:16
- 大小:96.01KB
SQLSERVER游标.docx
《SQLSERVER游标.docx》由会员分享,可在线阅读,更多相关《SQLSERVER游标.docx(16页珍藏版)》请在冰点文库上搜索。
SQLSERVER游标
24.1.游标
PL/SQL的游标指把从数据库中查询出来的数据以临时表的形式存放在内存中。
游标可以对存储在内存中的数据进行操作,返回一条或一组记录,或者一条记录也不返回。
PL/SQL中的记录和表类型虽然也可以用来存放数据,但对一组存放在内存中的数据进行操作,还是不太方便,游标恰好是实现这一功能的有力工具。
PL/SQL包含隐含游标和显示游标等两种游标类型,其中隐含游标用于处理SELECTINTO和DML语句,而显示游标则专门用于处理SELECT语句返回的多行数据。
游标的基本操作有:
声明游标、打开游标、提取游标和关闭游标。
24.15.1.游标的类型
24.15.2.隐式游标
Ø在PL/SQL中使用DML语句时自动创建隐式游标
Ø隐式游标自动声明、打开和关闭,其名为SQL
Ø通过检查隐式游标的属性可以获得最近执行的DML语句的信息
隐式游标的属性有:
Ø%FOUND–SQL语句影响了一行或多行时为TRUE
Ø%NOTFOUND–SQL语句没有影响任何行时为TRUE
Ø%ROWCOUNT–SQL语句影响的行数
Ø%ISOPEN-游标是否打开,始终为FALSE
示例1:
SQL>SETSERVEROUTPUTON
SQL>BEGIN
UPDATEtoysSETtoyprice=270
WHEREtoyid='P005';
IFSQL%FOUNDTHEN--只有在DML语句影响一行或多行时,才返回True
DBMS_OUTPUT.PUT_LINE(‘表已更新');
ENDIF;
END;
/
示例2:
SQL>SETSERVEROUTPUTON
SQL>DECLARE
v_TOYIDTOYS.ID%type:
='&TOYID';
v_TOYNAMETOYS.NAME%Type:
='&TOYNAME';
BEGIN
UPDATETOYSSETNAME=v_TOYNAME
WHEREtoyid=v_TOYID;
IFSQL%NOTFOUNDTHEN--如果DML语句不影响任何行,则返回True
DBMS_OUTPUT.PUT_LINE('编号未找到。
');
ELSE
DBMS_OUTPUT.PUT_LINE(‘表已更新');
ENDIF;
END;
/
示例3:
SQL>SETSERVEROUTPUTON
SQL>BEGIN
UPDATEvendor_master
SETvenname='RobMathew'
WHEREvencode='V004';
DBMS_OUTPUT.PUT_LINE(SQL%ROWCOUNT);--返回DML语句影响的行数
END;
/
示例4:
SQL>SETSERVEROUTPUTON
SQL>DECLARE
empidVARCHAR2(10);
desigVARCHAR2(10);
BEGIN
empid:
='&Employeeid';
SELECTdesignationINTOdesig
FROMemployeeWHEREempno=empid;
EXCEPTION
WHENNO_DATA_FOUNDTHEN
DBMS_OUTPUT.PUT_LINE('职员未找到');
END;
/
如果没有与SELECTINTO语句中的条件匹配的行,将引发NO_DATA_FOUND异常
示例5:
SQL>SETSERVEROUTPUTON
SQL>DECLARE
empidVARCHAR2(10);
BEGIN
SELECTempnoINTOempidFROMemployee;
EXCEPTION
WHENTOO_MANY_ROWSTHEN
DBMS_OUTPUT.PUT_LINE('该查询提取多行');
END;
/
如果SELECTINTO语句返回多个值,将引发TOO_MANY_ROWS异常
24.15.3.显式游标
显式游标在PL/SQL块的声明部分定义查询,该查询可以返回多行。
显式游标的操作过程:
1.声明游标
声明游标,主要是定义一个游标名称来对应一条查询语句,从而可以利用该游标对此查询语句返回的结果集进行单行操作。
声明游标的语法如下:
CURSORcursor_name
[(parameter_name[IN]data_type[{:
=|DEFAULT}value][,…])]
ISselect_statement
[FORUPDATE[OFcolumn[,…]][NOWAIT]];
语法说明如下:
Øcursor_name新游标名称。
Øparameter_name[IN]为游标定义输入参数,IN关键字可以省略。
使用输入参数可以使游标的应用变得更灵活。
用户需要在打开游标时为输入参数赋值,也可使用参数的默认值。
输入参数可以有多个,多个参数的设置之间使用逗号(,)隔开。
Ødata_type为输入参数指定数据类型,但不能指定精度或长度。
例如字符串类型可以使用VARCHAR2,而不能使用VARCHAR2(10)之类的精确类型。
Øselect_statement查询语句。
ØFORUPDATE用于在使用游标中的数据时,锁定游标结果集与表中对应数据行的所有或部分列。
ØOF如果不使用OF子句,则表示锁定游标结果集与表中对应数据行的所有列。
如果指定了OF子句,则只锁定指定的列。
ØNOWAIT如果表中的数据行被某用户锁定,那么其他用户的FORUPDATE操作将会一直等到该用户释放这些数据行的锁定后才会执行。
而如果使用了NOWAIT关键字,则其他用户在使用OPEN命令打开游标时会立即返回错误信息。
2.打开游标
在声明游标时为游标指定了查询语句,但此时该查询语句并不会被Oracle执行。
只有打开游标后,Oracle才会执行查询语句。
在打开游标时,如果游标有输入参数,用户还需要为这些参数赋值,否则将会报错(除非参数设置了默认值)。
打开游标需要使用OPEN语句,其语法如下:
OPENcursor_name[(value[,…])];
【例】在PL/SQL中,声明一个游标emp_cursor对应emp表中的查询操作,此查询操作检索emp表中指定部门的员工的部分信息,语句如下:
SQL>DECLARE
2CURSORemp_cursor(dept_numNUMBER:
=20)
3IS
4SELECTempno,ename,job,sal
5FROMempWHEREdeptno=dept_num;
6BEGIN
7…;
8END;
【例】在前面BEGIN…END块中,使用OPEN语句打开游标emp_cursor,
并且为输入参数赋值30,指定检索部门编号为30的员工的信息,语句如下:
OPENemp_cursor(30);
3.检索游标
打开游标后,游标所对应的SELECT语句也就被执行了,如果想要获取结果集中的数据,就需要检索游标。
检索游标,实际上就是从结果集中获取单行数据并保存到定义的变量中,这需要使用FETCH语句,其语法如下:
FETCHcursor_nameINTOvariable[,…];
其中,variable是用来存储结果集中单行数据的变量,可以选择使用多个普通类型的变量,一对一地接受数据行中的列值;也可以使用一个%ROWTYPE类型的变量,或自定义的记录类型变量,接受数据行中所有的列值。
变量需要事先定义。
【例】创建自定义记录类型emp_type,然后使用该类型定义变量one_emp,语句如下:
TYPEemp_typeISRECORD(
empnoNUMBER(4),enameVARCHAR2(10),
jobVARCHAR2(9),salNUMBER(7,2));
one_empemp_type;
在BEGIN…END块中的打开游标的语句后面,使用FETCH语句检索游标emp_cursor,将游标中的单行数据赋值给one_emp变量,语句如下:
FETCHemp_cursorINTOone_emp;
如果是使用普通类型变量,例如事先创建NUMBER(4)类型的变量emp_num等,则可以使用如下形式获取游标中的记录值:
FETCHemp_cursorINTOemp_num,emp_name,emp_job,emp_sal;
4.关闭游标
关闭游标需要使用CLOSE语句。
游标被关闭后,Oracle将释放游标中SELECT语句的查询结果所占用的系统资源。
其语法如下:
CLOSEcursor_name;
5.示例
SQL>SETSERVEROUTPUTON
SQL>DECLARE
my_toy_pricetoys.toyprice%TYPE;
CURSORtoy_curIS--声明游标
SELECTtoypriceFROMtoys
WHEREtoyprice<250;
BEGIN
OPENtoy_cur;--打开游标
LOOP
FETCHtoy_curINTOmy_toy_price;--提取行
EXITWHENtoy_cur%NOTFOUND;
DBMS_OUTPUT.PUT_LINE
('TOYPRICE=:
玩具单价=:
'||my_toy_price);
ENDLOOP;
CLOSEtoy_cur;--关闭游标
END;
24.15.4.带参数的显式游标
声明显式游标时可以带参数以提高灵活性
声明带参数的显式游标的语法如下:
CURSOR
ISselect_statement;
示例:
SQL>SETSERVEROUTPUTON
SQL>DECLARE
desigVARCHAR2(20);
emp_codeVARCHAR2(5);
empnmVARCHAR2(20);
CURSORemp_cur(desigparamVARCHAR2)IS
SELECTempno,enameFROMemployee
WHEREdesignation=desig;
BEGIN
desig:
='&desig';
OPENemp_cur(desig);
LOOP
FETCHemp_curINTOemp_code,empnm;
EXITWHENemp_cur%NOTFOUND;
DBMS_OUTPUT.PUT_LINE(emp_code||''||empnm);
ENDLOOP;
CLOSEemp_cur;
END;
24.15.5.使用显式游标更新行
允许使用游标删除或更新活动集中的行
声明游标时必须使用SELECT…FORUPDATE语句
语法:
CURSOR
SELECTstatementFORUPDATE;
更新的语法:
UPDATE
SET
WHERECURRENTOF
删除的语法:
DELETEFROM
WHERECURRENTOF
示例:
SQL>SETSERVEROUTPUTON
SQL>DECLARE
new_priceNUMBER;
CURSORcur_toyIS
SELECTtoypriceFROMtoysWHEREtoyprice<100
FORUPDATEOFtoyprice;
BEGIN
OPENcur_toy;
LOOP
FETCHcur_toyINTOnew_price;
EXITWHENcur_toy%NOTFOUND;
UPDATEtoys
SETtoyprice=1.1*new_price
WHERECURRENTOFcur_toy;
ENDLOOP;
CLOSEcur_toy;
COMMIT;
END;
24.15.6.循环游标
Ø循环游标用于简化游标处理代码
Ø当用户需要从游标中提取所有记录时使用
Ø循环游标的语法如下:
FOR
LOOP
ENDLOOP;
示例:
SQL>SETSERVEROUTPUTON
SQL>DECLARE
CURSORmytoy_curIS
SELECTtoyid,toyname,toyprice
FROMtoys;
BEGIN
FORtoy_recINmytoy_cur
LOOP
DBMS_OUTPUT.PUT_LINE(
‘玩具编号:
'||''||toy_rec.toyid||''
||‘玩具名称:
'||''||toy_rec.toyname||''
||‘玩具单价:
'||''||toy_rec.toyprice);
ENDLOOP;
END;
24.15.7.REF游标和游标变量
ØREF游标和游标变量用于处理运行时动态执行的SQL查询
Ø创建游标变量需要两个步骤:
1.声明REF游标类型
2.声明REF游标类型的变量
Ø用于声明REF游标类型的语法为:
TYPE
[RETURN
Ø打开游标变量的语法如下:
OPENcursor_nameFORselect_statement;
Ø游标变量:
a.一种引用类型
b.可以再运行时指向不同的存储位置
c.Close语句关闭游标并释放用于查询的资源
Ø游标变量的类型
A.具有约束的游标变量
-也就是具有返回类型的游标变量也称为”强游标"
B.无约束的游标变量
-也就是没有返回类型的游标变量也称为“弱游标”
示例1:
弱型游标示例
声明时不需指定查询语句,即不指向任何区域
declare
typeRefEmpCur isrefcursor; --声明引用游标类型游标返回的类型没有限制
EmpCurRefEmpCur; --游标变量
EmpRowemp%rowtype;--存储游标查询到得结果
flagint:
=0;
begin
flag:
=&flag;
ifflag=0then
openEmpCur forselect*fromempwheresal>500andsal<2000;
elsif flag=1then
openEmpCurforselect*from empwheresal>=2000;
elsif flag=2then
openEmpCurforselect*from dept; --弱类型游标对目标表没有限制,数据可以使来自任何表
else
openEmpCurforselect*fromemp;
endif;
/*for循环不能用于Ref游标,因为它是自动打开游标
forEmpRowinEmpCur loop
DBMS_output.put_line('empno='||EmpRow.empno);
endloop;
*/
loop
exitwhenEmpCur%notfound; --如果没有查询到数据就退出
fetchEmpCurintoEmpRow;
DBMS_output.put_line('empno='||EmpRow.empno);
endloop;
closeEmpCur;
end;
/
示例1:
强型游标示例
declare
typeRefEmpCur isrefcursorreturnemp%rowtype; --游标仅能打开emp表的数据
EmpCurRefEmpCur; --游标变量
EmpRowemp%rowtype;--存储游标查询到得结果
flagint:
=0;
begin
flag:
=&flag;
ifflag=0then
openEmpCur forselect*fromempwheresal>500andsal<2000;
elsif flag=1then
openEmpCurforselect*fromempwheresal>=2000;
else
openEmpCurforselect*fromemp;
endif;
loop
exitwhenEmpCur%notfound; --如果没有查询到数据就退出
fetchEmpCurintoEmpRow;
DBMS_output.put_line('empno='||EmpRow.empno);
endloop;
closeEmpCur;
end;
/
24.15.8.游标变量的优点和限制
游标变量的功能强大,可以简化数据处理。
Ø游标变量的优点有:
1.可从不同的SELECT语句中提取结果集
2.可以作为过程的参数进行传递
3.可以引用游标的所有属性
4.可以进行赋值运算
Ø使用游标变量的限制:
1.不能在程序包中声明游标变量
2.FORUPDATE子句不能与游标变量一起使用
3.不能使用比较运算符
24.15.9.使用游标变量执行动态SQL
可以使用游标变量执行动态构造的SQL语句
打开执行动态SQL的游标变量的语如下:
OPENcursor_nameFORdynamic_sqlstring
[USINGbind_argument_list];
示例:
DECLARE
r_empemp%ROWTYPE;
TYPEc_typeISREFCURSOR;
curc_type;
p_salaryNUMBER;
BEGIN
p_salary:
=2500;
OPENcurFOR'select*fromempwheresal>:
1
orderbysaldesc'
USINGp_salary;
DBMS_OUTPUT.PUT_LINE('薪水大于'||p_salary||'的员工有:
');
LOOP
FETCHcurINTOr_emp;
EXITWHENcur%NOTFOUND;
DBMS_OUTPUT.PUT_LINE('编号:
'||r_emp.empno
||'姓名:
'||r_emp.ename||'薪水:
'||r_emp.sal);
ENDLOOP;
CLOSEcur;
END;
- 配套讲稿:
如PPT文件的首页显示word图标,表示该PPT已包含配套word讲稿。双击word图标可打开word文档。
- 特殊限制:
部分文档作品中含有的国旗、国徽等图片,仅作为作品整体效果示例展示,禁止商用。设计者仅对作品中独创性部分享有著作权。
- 关 键 词:
- SQLSERVER 游标