游标 环境区域是用来处理SQL语句的一个oracle存储区域。游标是指向它的指针或句柄。通过游标,PL/SQL程序可以控制这个环境区域中被处理的语句。 Oracle中的游标有两种:显式游标、隐式游标。 显示游标是用cursor...is命令定义的游标,它可以对查询语句(select)返回的多条记录进行处理,而隐式游标是在执行插入(insert)、删除(delete)、修改(update)和返回单条记录的查询(select)语句时由PL/SQL自动定义的。
1、显式游标操作 显式游标在块定义部分、包或子程序中声明。当声明了显式游标后,可以通过以下三条命令控制显式游标的操作:打开游标、推进游标、关闭游标。 (1)声明显式游标 --例1
代码
- declare
- v_authsauths%rowtype;
- v_codeauths.author_code%type;
- cursorc_authsis
- select*fromauthswhereauthor_code=v_code;
render_code();
上例是将PL/SQL变量绑定在WHERE子句中,下面将游标参数绑定在游标的WHERE子句中: --例2
代码
- delcare
- cursorc_auths(p_codeauths.author_code%type)is
- select*fromauthswhereauthor_code=p_code;
render_code();
(2)打开显式游标 游标操作的第一步是打开游标。 例1,下面的语句是打开上节例1中声明的显式游标c_auths;
代码
- begin
- --在打开游标前为绑定变量赋值。
- v_code:='A00001';
- --打开游标。
- openc_auths;
render_code();
例2,如果对于一个带参数的游标
代码
- begin
- --打开游标时将参数传入。
- openc_auths('A00001');
render_code();
打开一个已打开的游标也是合法的。当第二次打开游标时,PL/SQL先自动关闭游标,然后再打开。一次打开多个游标也是PL/SQL所允许的。 (3)推进显式游标 当打开显式游标后,就可以使用FETCH语句来推进游标,返回查询结果集中的一行。每执行完一条FETCH语句后,显式游标会自动指向查询结果集的下一行。 (4)关闭显式游标 当整个结果集都检索完以后,应当关闭游标。关闭游标用来通知PL/SQL游标操作已经结束,并且释放游标所占用的资源(结果集所使用的资源空间)。
2、游标的属性 游标有四个属性:%found、%notfound、%isopen和%rowcount。要注意这些属性只能使用在过程性语句中,而不能使用在SQL语句中。 表tableattribute,表中有两列column1(number类型)和column2(varchar2类型),现在向表中插入两条记录: insert into tableattribute values(10,'first'); insert into tableattribute values(20,'second'); ...
3、显式游标的推进循环
代码
- delcare
- --声明一个变量,这个变量用来接收游标返回的结果集。
- v_salaryauths.salary%type;
- v_codeauths.author_code%type;
-
- cursorc_salaryisselectsalary,author_codefromauthswhereauthor_code<='A00006';
- begin
- --打开游标,并初始化结果集
- openc_salary;
- loop
- --推进游标,将游标的查询结果集中的一行存到变量v_salary中。
- fetchc_salaryintov_salary,v_code;
- --当结果集中没有行时退出循环。
- exitwhenc_salary%notfound;
-
--如果查询到的作家工资小于或等于200,则增加该作家的工资值。
-
ifv_salary<=200then
-
updateauthssetsalary=salary+50whereauthor_code=v_code;
-
endif;
- endloop;
- --关闭游标,释放游标占用资源。
- closec_salary;
- --提交所做的修改。
- commit;
- end;
render_code();
PL/SQL还提供了一种简单类型的循环,可以自动控制游标的打开、推进和关闭,叫做游标的FOR循环。
代码
- delcare
- cursorc_salaryis
- selectsalaryformauthswhereauthor_code<='A00006';
- begin
- --开始游标FOR循环,隐含地打开c_salary游标。
-
forv_salaryinc_salaryloop
- --一个隐含的fetch语句在这里被执行。
-
ifv_salary.salary<=200then
-
updateauthssetsalary=salary+50wheresalary=v_salary.salary;
-
endif;
- --在循环继续前,一个隐含的c_auths%notfound被检测。
- endloop;
- --现在循环已经结束,c_auths游标的一个隐含的close操作被执行。
- commit;
- end;
render_code();
使用current of cursor子句作为条件
代码
- delcare
-
--声明游标时在select语句中必须加forupdateof子句。
- cursorc_salaryis
-
selectsalaryformauthswhereauthor_code<'A00006'forupdateofsalary;
- begin
-
forv_salaryinc_salaryloop
-
ifv_salary.salary<=200then
- --下面的update语句中的currentof子句用来表明结果集的当前行。
-
endif;
- endloop;
- commit;
- end;
render_code();
如果在游标的FOR循环中使用子查询,则不用在块定义部分声明显式游标,在FOR循环中子查询隐含声明了一个显式游标。
代码
- begin
- --在下面的FOR循环中隐含地声明了一个游标c_salary。
-
forc_salaryin
- (selectsalaryformauthswhereauthor_code<='A00006')loop
-
ifc_salary.salary<=200then
-
updateauthssetsalary=salary+50wheresalary=c_salary.salary;
-
endif;
- endloop;
- commit;
- end;
render_code();
4、隐式游标处理 PL/SQL隐式地打开SQL游标,并在它内部处理SQL语句,然后关闭它。SQL游标用来处理insert、update、delete以及返回一行的select...into语句。 一个SQL游标不管是打开还是关闭,open、fetch和close命令都不能操作它。SQL游标与显式游标类似,也有四个一样的属性。当打开SQL游标之前,SQL游标的属性都为NULL。
代码
- begin
- updateauthssetentry_date_time=sysdatewhereauthor_code='A00017';
-
--如果update语句中修改的行不存在(SQL%notfound返回值为true),则向auths表中插入一行。
-
ifsql%nofoundthen
- insertintoauths(author_code,name,sex,birthdate,salary,entry_date_time)
-
values('A000017','qiuys',1,'30-apr-40',88.5,sysdate);
-
endif;
- end;
render_code();
--如果update语句中修改的行不存在(sql%rowcount=0)
代码
- declare
- v_birthdatedate;
- begin
- selectbirthdateintov_birthdatefromauthswherename='qiuys';
- --如果查询到一条记录,则删除该记录。
-
ifsql%foundthen
- deletefromauthswherename='qiuys';
-
endif;
- exception
- whenno_data_foundthen
- dbms_output.put_line('该记录不存在');
- whentoo_many_rowsthen
- dbms_output_line('存在同名的作家');
- end;
render_code();
5、游标变量 到目前为止前面所有显式游标的例子都是静态游标-即游标与一个SQL语句关联,并且该SQL语句在编译时已经确定。 而游标变量是一个引用类型(REF)的变量。 (1)游标变量的声明
代码
- declare
- --使用%rowtype定义一个游标变量类型。
-
typet_authsrefisrefcursorreturnauths%rowtype;
- --定义一个记录类型。
- typet_coderecordisrecord(
- author_codearticle.author_code%type,
- article_codearticle.article_code%type);
- --声明一个记录类型的变量。
- v_codet_coderecord;
- --使用t_coderecord作为一个游标变量类型的结果集类型。
-
typet_coderefisrefcursorreturnt_codeRecord;
- --使用v_code作为一个游标变量类型的结果集类型。
-
typet_coderef2isrefcursorreturnv_code%type;
- --使用上面的类型声明的两个游标变量。
- v_authcvt_authsref;
- v_codecvt_coderef;
render_code();
PL/SQL2.8以上版本中,可以使用一个没有指定结果集类型的游标变量来指定多个不同类型的查询。 type t_authsref if ref cursor; v_cursorvar t_authsref;--声明一个该类型的变量。 (2)打开游标变量 为了将一个游标变更与一个具体的select语句联系起来,open的语法中增加了一个select语句。
代码
- opencursor_variableforselect_statement;
- declare
-
typet_authorsrefisrefcursorreturnauths%rowtype;
- v_authscvt_authorsref;
- --然后打开
-
openv_authscvforselect*fromauths;
render_code();
(3)推进游标变更 (4)关闭游标变更 该操作用来释放查询所占用的资源。但没有释放游标变量占用的存储空间。当变量超出作用域时,它所占用的空间才被释放掉。 下面的块中定义了一个没有指定结果集的游标变量,这样我们就可以使用这个游标变量指向不同的查询,并能够返回不同的记录类型:
代码
- setserveroutputonsize100000--设置存储缓冲区大小。
- declare
-
- typet_currefisrefcursor;
- --声明一个游标变量类型的变量
- c_cursorreft_curref;
- --定义PL/SQL记录类型t_authorrec,该类型的变量用来接收游标变量的返回值。
- typet_authorrecisrecord(
- authorcodeauths.author_code%type,
- nameauths.name%type);
- --定义PL/SQL记录类型t_articlerec,该类型的变量也用来接收游标变量的返回值。
- typet_articlerecisrecord(
- authorcodearticle.author_code%type,
- titleartitle.title%type);
- --声明两个记录类型变量。
- v_authort_authorrec;
- v_articlet_articlerec;
- begin
- --打开游标变量c_cursorref,返回t_authorrec类型的记录。
-
openc_cursorreffor
- selectauthor_code,namefromauthswhereauthor_codein('A00001','A00002','A00003','A00004','A00005');
- --推进游标变量
- fetchc_cursorrefintov_author;
- --游标变量的推进循环。
-
whilec_cursorref%foundloop
- --将作家代码和相应的作家名字输出到屏幕上。
- dbms_output.put(v_author.authorcode||':'||v_author.name||'');
- fetchc_cursorrefintov_author;
- endloop;
- dbms_output.new_line;--向屏幕上输出一个回车行。
- --关闭游标变量,仅仅将游标变量指定的资源释放掉,游标变量本身的存储空间没有释放掉。
- closec_cursorref;
- --再次打开游标变量,返回t_articlerec类型的记录。
-
openc_cursorreffor
- selectauthor_code,titlefromarticle
- whereauthor_codein('A00001','A00002','A00003','A00004','A00005');
- fetchc_cursorrefintov_article;
-
whilec_cursorref%foundloop
- ...
- endloop;
- closec_cursorref;
- end;
render_code();
注意,在上例中,第一次关闭游标变量是可省略的,因为在第二次打开游标变量时,就将第一次的查询丢失掉了。而且游标变量也有游标属性,通常在推进游标变量时使用这些游标属性,例如上例使用了%found属性。 |
相关推荐
1、PL/SQL简介 2、PL/SQL基础 3、记录和表 4、在PL/SQL中使用SQL 5、内置SQL函数 6、游标 7、过程和函数 ...
PL/SQL 程序设计 本章主要重点: PL/SQL概述 PL/SQL块结构 PL/SQL流程 运算符和表达式 游标 异常处理 数据库存储过程和函数 包 触发器
pl/sql存储过程,函数,游标,以及存储过程中的基础知识,绝对值得你收藏的经典题目,让你的pl/sql得到最大的锻炼。让你的数据库逻辑更加灵活。
PL/SQL简介(第1~2章) PL/SQL中的SQL语句(第3章) 控制结构(第4~7章) 异常处理(第8~10章) 游标(第11~12章) 触发器(第13~14章) 复合数据类型(第15~16章) 动态SQL(第17章) 批量SQL(第18章) 子程序和包...
PL/SQL是ORACLE对标准数据库语言的扩展,ORACLE公司已经将PL/SQL整合到ORACLE 服务器和其他工具中了,近几年中更多的开发人员和DBA开始使用PL/SQL,本文将讲述PL/SQL基础语法,结构和组件、以及如何设计并执行一个PL...
PL /SQL是一种高级数据库程序设计语言,该语言专门用于在各种环境下对ORACLE数据库进行访问。由于该语言集成于数据库服务器中,所以PL/SQL代码可以对数据进行快速高效的处理。除此之外,可以在ORACLE数据库的某些...
NULL 博文链接:https://sytcun.iteye.com/blog/1408182
本书是专门为oracle应用开发人员提供的sql和pl/sql编程指南。通过学习本书,读者不仅可以掌握oracle常用工具oracle universal installer、net comfiguration assistant、sql developer、sql*plus的作用及使用方法...
内容包括(1)PL/SQL简介(2)PL/SQL语法(3)存储过程(4)触发器(5)游标(6)包(7)函数七部分。如果你是初学者,很快时间就可以掌握PL/SQL了。相信我,没错的!
在PL/SQL中处理显示游标所必需的四个步骤: 1)声明游标;CURSOR cursor_name IS select_statement 2)为查询打开游标;OPEN cursor_name 3)取得结果放入PL/SQL变量中; FETCH cursor_name INTO list_of_...
pl/sql中的一些基本概念,讲解了pl/sql语句的编写方法。其中包含了pl/sql的简单语句、循环语句、条件语句等的相关知识,重点介绍了存储过程、函数、游标、触发器等的编写和运用。
本书是专门为Oracle应用开发人员提供的SQL和PL/SQL编程指南。通过学习本书,读者不仅可以掌握Oracle常用工具Oracle Universal Installer、Net Comfiguration Assistant、SQL Developer、SQL*Plus的作用及使用方法,...
包含oracle存储过程的增、删、查、改 %type、%rowtype的使用 自定义函数 PL/SQL块
PL/SQL 基础,一个不错的 PL/SQL 参考手册。内容预览: ---- 第一章 PL/SQL 简介 ---- 1. Oracle应用编辑方法概览 1) Pro*C/C++/... : C语言和数据库打交道的方法,比OCI更常用; 2) ODBC 3) OCI: C语言和...
Oracle PL/SQL语言初级教程 1 目录 1 1.PL/SQL语言基础 3 过程 5 包(package) 6 变量和常量 6 执行部分 10 控制结构 12 小结 17 2.PL/SQL的复合数据类型 17 使用记录 19 PL/SQL集合 23 嵌套表 24 使用集合 25 集合的...
Pl/SQL简介 控制结构 复合数据类型 游标 异常 创建存储过程 函数的概念 包的概念 触发器概念
除了为读者提供编写sql语句和开发pl/sql块的方法外,本书还为应用开发人员提供了一些常用的pl/sql系统包。通过使用这些pl/sql系统包,应用开发人员可以开发出功能更强大的数据库应用程序。本书不仅适合sql和pl/sql...
7. 把所有 PL/SQL 游标改为非游标 SELECT 语句或 Transact-SQL 游标。 8. 用 Transact-SQL 过程代替 PL/SQL 过程、函数和包。 9. 把 PL/SQL 触发器转换为 Transact-SQL 触发器。 10. 使用 SET SHOWPLAN 语句,优化...
【实验目的】 1. 熟悉PL/SQL的数据类型和书写规则 2. 熟悉控制结构和游标的使用 3. 编写和运行函数、过程和触发器