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
升级会员 