ORACLE中一般游标和ref游标Word格式文档下载.docx
- 文档编号:22679476
- 上传时间:2023-02-05
- 格式:DOCX
- 页数:59
- 大小:36.03KB
ORACLE中一般游标和ref游标Word格式文档下载.docx
《ORACLE中一般游标和ref游标Word格式文档下载.docx》由会员分享,可在线阅读,更多相关《ORACLE中一般游标和ref游标Word格式文档下载.docx(59页珍藏版)》请在冰豆网上搜索。
如果DML语句没有影响任何行数,则%notfound属性返回true.
返回值为布尔型,判断游标所在的行是否有效,如果有效,则%FOUNDD等于true,否则等于false,即与%FOUND属性返回值相反。
declarebegindeletefromempwhereempno=111;
ifsql%notfoundthendbms_output.put_line('
删除失败'
3%rowcount%rowcount属性返回DML语句影响的行数。
如果DML语句没有影响任何行数,则%rowcount属性将返回0。
返回值类型为整型,返回当前位置为止游标读取的记录行数,即成功执行的数据行数
begin
updateempsetempno=123whereempno=111;
ifsql%rowcount=0then
dbms_output.put_line('
elsedbms_output.put_line('
endif;
end;
4%isopen%isopen属性判断SQL游标是否已经打开。
在执行SQL语句之后,oracle自动关闭SQL游标,所以隐式游标的%isopen属性始终为false.
返回的类型为布尔型,判断游标是否被打开,如果打开%ISOPEN等于true,否则等于false,即执行过程中为真,结束后为假。
在PL/SQL中向标准的select语句增加单独的into子句,就可以将从表或视图中查询记录赋予变量或行变量。
需要注意的是select..into语句结果必须有且只能有一行。
如果查询没有返回行,PL/SQL将抛出no_data_found异常。
如果查询返回多行,则抛出too_many_rows异常。
如果抛出异常,则停止执行,控制权转移到异常处理部分(没有异常处理,则程序中断)。
在引发异常时,将不使用属性%found,%notfound,%rowcount来查明DML语句是否已影响了行数。
declarenumnumber;
beginselectempnointonumfromempwhereempno=111;
ifsql%rowcount=0orsql%notfoundthendbms_output.put_line('
else
显示游标显示游标是由用户显示声明的游标。
根据在游标中定义的查询,查询返回的行集合可以包含零行或多行,这些行称为活动集。
游标将指向活动集中的当前行。
显示游标的操作过程。
使用显示游标的4个步骤:
(1)声明游标
(2)打开游标
(3)从游标中获取结果集
(4)关闭游标
cursorcursor_name[(parameter[,parameter])][returnreturn_type]isselect_statement;
cursor_name指游标的名称。
parameter为游标指定输入参数。
return_type定义游标提取行的行类型。
select_statement为游标定义查询语句。
open游标名称fetch从游标中提取行close关闭游标
1打开游标,执行游标中定义的查询语句,绑定输入参数,将游标指针指向结果集的BOF位置。
opencursor_name[parameters]
2fetch在打开游标之后,可以从游标中提取记录fetchcursor_nameintovariable_name;
fetch是提取结果集中一行记录存储在变量中。
每次提取之后,结果集指针就向前移动一行。
3close在处理游标中的所有行之后,必须关闭游标,以释放分配给游标的所有资源。
closecursor_name用户可以通过检查游标属性来确定游标的当前状态。
显示游标的属性如下:
%found:
如果执行最后一条fetch语句,成功返回行,则%found属性为true。
%notfound:
如果执行最后一条fetch语句,未能提取行,则%notfound属性为true。
%isopen:
如果游标已经打开,则返回true,否则返回false。
%rowcount:
返回到目前为止游标提取的行数。
%rowcount为数字类型属性。
在第一次获取之前,%rowcount为零。
当fetch语句返回一行时,则该数加1。
declareinfoemp%rowtype;
cursormy_curisselect*fromempwhereempno=111;
beginopenmy_cur;
dbms_output.put_line(my_cur%rowcount);
loop
ifmy_cur%isopenthenfetchmy_curintoinfo;
exit
whenmy_cur%notfound;
dbms_output.put_line(info.empno);
dbms_output.put_line(my_cur%rowcount);
endloop;
closemy_cur;
使用显示游标删除或更新使用游标时,如果处理过程中需要删除或更新。
在定义游标查询语句时必须使用select..forupdate语句,而在执行delete或update时使用wherecurrentof子句指定游标当前行。
cursorcursor_nameisselect_statementforupdate[ofcolumn]wait/nowait在使用forupdate子句声明游标之后,可以使用以下语法更新行updatetable_namesetcolumn_name=column_valuewherecurrentofcursor_name;
update命令中使用的列必须出现在forupdateof子句中select语句必须只包括一个表,而且delete和update语句只有在打开游标并且提取特定行之后才能使用。
declarecursorcur_empisselect*fromempwheresal<
2000forupdateofsal;
numemp%rowtype;
beginopencur_emp;
loopfetchcur_empintonum;
exitwhencur_emp%notfound;
updateempsetsal=2000wherecurrentofcur_emp;
closecur_emp;
带参数的显示游标PL/SQL中允许显示游标接受输入参数。
用于声明带参数的显示游标语法
cursorcursor_name[<
param_name>
data_type][return<
returntype>
]isselect_statementdeclaredept_numemp.deptno%type;
emp_numemp.empno%type;
emp_namemp.ename%type;
cursoremp_cur(deptparamnumber)isselectempno,enamefromempwheredeptno=deptparam;
begindept_num:
=&
部门编号;
openemp_cur(dept_num);
loopfetchemp_curintoemp_num,emp_nam;
exitwhenemp_cur%notfound;
dbms_output.put_line(emp_num||'
'
||emp_nam);
endloop;
closeemp_cur;
可以使用循环游标来简化显示游标。
循环游标隐式打开显示游标(不需要open)自动从结果集提取记录,然后处理完所有记录自动关闭游标。
循环游标自动创建%rowtype类型的变量并将此变量用做记录的索引。
循环游标语法如下:
forrecord_indexincursor_namerecord_index是PL/SQL自动创建的变量,此变量的属性声明为%rowtype类型。
作用域for循环之内。
循环游标的特性有:
从游标中提取所有记录之后自动关闭游标。
提取和处理游标中每一条记录提取记录之后%notfound属性为true则退出循环。
如果未有结果集,则不进入循环。
declarecursoremp_curisselect*fromemp;
beginfortempinemp_curloopdbms_output.put_line(temp.ename);
循环游标自动打开,提取,关闭。
只适用于静态游标
ref游标
隐式游标和显示游标都是静态定义的。
它们在编译的时候结果集就已经被确定。
如果想在运行的时候动态确定结果集,就要使用ref游标和游标变量。
创建ref游标需要两个步骤:
1声明refcursor类型
2声明refcursor类型变量。
语法如下:
typeref_cursor_nameisrefcursor[returnrecord_type]其中,return用于指定游标提取结果集的返回类型。
有return表示是强类型ref游标,没有return表示是弱类型的游标。
弱类型游标可以提取任何类型的结果集。
定义游标变量之后,就可以在PL/SQL执行部门打开游标变量
opencursor_nameforselect_statement;
declaretypeemp_curisrefcursor;
my_curemp_cur;
numnumber;
selectionvarchar
(2):
='
&
请输入编号'
;
begin
ifselection='
1'
then
员工信息'
openmy_curforselectdeptnofromemp;
elsifselection='
2'
部门信息'
openmy_curforselectdeptnofromdept;
请输入员工信息
(1)或门部信息
(2)'
fetchmy_curintonum;
whilemy_cur%foundloop
dbms_output.put_line(num);
在PL/SQL中可以执行动态SQL语句,executeimmediate语句只能语句处理返回单行或没有返回的SQL语句,ref游标则可以处理返回结果集的动态SQL。
ref游标的声明方法与普通ref游标相同,只是在open时指定了动态SQL字符串。
opencursor_namefordynamic_select_string[usingbind_argument_list]declaretypesql_curisrefcursor;
my_cursql_cur;
emp_infoemp%rowtype;
sql_stringvarchar2(100):
请输入查询字符串'
beginopenmy_curforsql_string;
loopfetchmy_curintoemp_info;
exitwhenmy_cur%notfound;
dbms_output.put_line(emp_info.ename);
游标变量的特点:
(1)游标变量可以从不同的结果集中提取记录
(2)游标变量可以做为存储过程参数进行传递
(3)游标变量可以引用游标的所有属性
(4)游标变量可以进行赋值运算使用游标变量也有一定的限制
(1)forupdate子句不能与游标变量一起使用
(2)不允许在程序包使用游标变量(可以声明游标类型)
(3)另一台服务器上的子过程不能接受游标变量参数
(4)不能将NULL值赋给游标变量。
(5)游标变量不能使用比较运算符。
(6)数据库中的列不能存储游标变量。
总结:
(1)游标是使用在PL/SQL中,是用来查询数据,获取结果集的指针
(2)游标类型包括隐式游标,显示游标和ref游标。
(3)游标属性包括%found,%notfound,%rowcount,%isopen
(4)PL/SQL自动定义隐式游标,以获取最近执行SQL语句信息。
(5)循环游标简化处理游标中所有行的查询。
(6)在声明ref游标时,不要将它与select语句相关联。
oracle隐式游标,显示游标,游标循环,动态SELECT语句和动态游标,异常处理,自定义异常,
关键字:
oracle隐式游标显示循环动态select语句游标异常处理自定义
游标的概念:
游标是SQL的一个内存工作区,由系统或用户以变量的形式定义。
游标的作用就是用于临时存储从数据库中提取的数据块。
在某些情况下,需要把数据从存放在磁盘的表中调到计算机内存中进行处理,最后将处理结果显示出来或最终写回数据库。
这样数据处理的速度才会提高,否则频繁的磁盘数据交换会降低效率。
游标有两种类型:
显式游标和隐式游标。
在前述程序中用到的SELECT...INTO...查询语句,一次只能从数据库中提取一行数据,对于这种形式的查询和DML操作,系统都会使用一个隐式游标。
但是如果要提取多行数据,就要由程序员定义一个显式游标,并通过与游标有关的语句进行处理。
显式游标对应一个返回结果为多行多列的SELECT语句。
游标一旦打开,数据就从数据库中传送到游标变量中,然后应用程序再从游标变量中分解出需要的数据,并进行处理。
隐式游标
如前所述,DML操作和单行SELECT语句会使用隐式游标,它们是:
*插入操作:
INSERT。
*更新操作:
UPDATE。
*删除操作:
DELETE。
*单行查询操作:
SELECT...INTO...。
当系统使用一个隐式游标时,可以通过隐式游标的属性来了解操作的状态和结果,进而控制程序的流程。
隐式游标可以使用名字SQL来访问,但要注意,通过SQL游标名总是只能访问前一个DML操作或单行SELECT操作的游标属性。
所以通常在刚刚执行完操作之后,立即使用SQL游标名来访问属性。
游标的属性有四种,如下所示。
Sql代码
1.隐式游标的属性返回值类型意义
2.SQL%ROWCOUNT整型代表DML语句成功执行的数据行数
3.SQL%FOUND布尔型值为TRUE代表插入、删除、更新或单行查询操作成功
4.SQL%NOTFOUND布尔型与SQL%FOUND属性返回值相反
5.SQL%ISOPEN布尔型DML执行过程中为真,结束后为假
隐式游标的属性返回值类型意义
SQL%ROWCOUNT整型代表DML语句成功执行的数据行数
SQL%FOUND布尔型值为TRUE代表插入、删除、更新或单行查询操作成功
SQL%NOTFOUND布尔型与SQL%FOUND属性返回值相反
SQL%ISOPEN布尔型DML执行过程中为真,结束后为假
【训练1】 使用隐式游标的属性,判断对雇员工资的修改是否成功。
步骤1:
输入和运行以下程序:
1.SETSERVEROUTPUTON
2.BEGIN
3.UPDATEempSETsal=sal+100WHEREempno=1234;
4.IFSQL%FOUNDTHEN
5.DBMS_OUTPUT.PUT_LINE('
成功修改雇员工资!
'
6.COMMIT;
7.ELSE
8.DBMS_OUTPUT.PUT_LINE('
修改雇员工资失败!
9.ENDIF;
10.END;
SETSERVEROUTPUTON
BEGIN
UPDATEempSETsal=sal+100WHEREempno=1234;
IFSQL%FOUNDTHEN
DBMS_OUTPUT.PUT_LINE('
COMMIT;
ELSE
DBMS_OUTPUT.PUT_LINE('
ENDIF;
END;
运行结果为:
1.修改雇员工资失败!
2.PL/SQL过程已成功完成。
PL/SQL过程已成功完成。
步骤2:
将雇员编号1234改为7788,重新执行以上程序:
1.成功修改雇员工资!
说明:
本例中,通过SQL%FOUND属性判断修改是否成功,并给出相应信息。
显式游标
游标的定义和操作
游标的使用分成以下4个步骤。
1.声明游标
在DECLEAR部分按以下格式声明游标:
CURSOR游标名[(参数1数据类型[,参数2数据类型...])]
ISSELECT语句;
参数是可选部分,所定义的参数可以出现在SELECT语句的WHERE子句中。
如果定义了参数,则必须在打开游标时传递相应的实际参数。
SELECT语句是对表或视图的查询语句,甚至也可以是联合查询。
可以带WHERE条件、ORDERBY或GROUPBY等子句,但不能使用INTO子句。
在SELECT语句中可以使用在定义游标之前定义的变量。
2.打开游标
在可执行部分,按以下格式打开游标:
OPEN游标名[(实际参数1[,实际参数2...])];
打开游标时,SELECT语句的查询结果就被传送到了游标工作区。
3.提取数据
在可执行部分,按以下格式将游标工作区中的数据取到变量中。
提取操作必须在打开游标之后进行。
FETCH游标名INTO变量名1[,变量名2...];
或
FETCH游标名INTO记录变量;
游标打开后有一个指针指向数据区,FETCH语句一次返回指针所指的一行数据,要返回多行需重复执行,可以使用循环语句来实现。
控制循环可以通过判断游标的属性来进行。
下面对这两种格式进行说明:
第一种格式中的变量名是用来从游标中接收数据的变量,需要事先定义。
变量的个数和类型应与SELECT语句中的字段变量的个数和类型一致。
第二种格式一次将一行数据取到记录变量中,需要使用%ROWTYPE事先定义记录变量,这种形式使用起来比较方便,不必分别定义和使用多个变量。
定义记录变量的方法如下:
变量名表名|游标名%ROWTYPE;
其中的表必须存在,游标名也必须先定义。
4.关闭游标
CLOSE游标名;
显式游标打开后,必须显式地关闭。
游标一旦关闭,游标占用的资源就被释放,游标变成无效,必须重新打开才能使用。
以下是使用显式游标的一个简单练习。
【训练1】用游标提取emp表中7788雇员的名称和职务。
2.DECLARE
3.v_enameVARCHAR2(10);
4.v_jobVARCHAR2(10);
5.CURSORemp_cursorIS
6.SELECTename,jobFROMempWHEREempno=7788;
7.BEGIN
8.OPENemp_cursor;
9.FETCHemp_cursorINTOv_ename,v_job;
10.DBMS_OUTPUT.PUT_LINE(v_ename||'
'
||v_job);
11.CLOSEemp_cursor;
12.END;
SETSERVEROUTPUTON
DECLARE
v_enameVARCHAR2(10);
v_jobVARCHAR2(10);
CURSORemp_cursorIS
SELECTename,jobFROMempWHEREempno=7788;
OPENemp_cursor;
FETCHemp_cursorINTOv_ename,v_job;
DBMS_OUTPUT.PUT_LINE(v_ename||'
CLOSEemp_cursor;
EN
- 配套讲稿:
如PPT文件的首页显示word图标,表示该PPT已包含配套word讲稿。双击word图标可打开word文档。
- 特殊限制:
部分文档作品中含有的国旗、国徽等图片,仅作为作品整体效果示例展示,禁止商用。设计者仅对作品中独创性部分享有著作权。
- 关 键 词:
- ORACLE 一般 游标 ref
![提示](https://static.bdocx.com/images/bang_tan.gif)