Oracle--plsql游标创建和使用_数据库技术_Linux公社-Linux系统门户网站
你好，游客
Oracle--plsql游标创建和使用
来源：Linux社区&
作者：shaojie519
为什么要游标：
先看这个：
DECLARE&&&
&v_empno&emp.empno%&&&
&v_ename&emp.ename%&&&
&SELECT&empno,ename&&&
&into&v_ename,v_empno&&&
&FROM&&&&&&
&dbms_output.put_line(v_empno||'&'||v_ename);&&
这个SELECT语句可能返回多条记录，所以这个赋值语句可能是错误的。所以游标可以发挥作用了。
什么是游标？
游标（cursor）是Oracle系统在内存中开辟的一个工作区，在其中存放SELECT语句返回的查询结果。
游标的分类：
隐式游标：PL/SQL隐式建立并自动管理这一游标。隐式游标也叫做SQL游标。 对于SQL游标，不能对其显式地执行OPEN、CLOSE和FETCH语句，但是可以使用游标属性从最近执行的SQL语句中获取信息。
显式游标:由程序员显式说明及控制，用于从表中取出多行数据，并将多行数据一行一行单独处理。
隐式游标：
由Oracle在内部声明
由Oracle自行管理游标
可以使用游标属性从最近执行的SQL语句中获取信息
用于处理DML语句以及返回单行的查询
隐式游标属性：
隐式游标举例：
VARIABLE&rows_deleted&VARCHAR2(30)&&
&&v_deptno&&NUMBER&:=&20;&&
&&DELETE&FROM&&emp&&
&&WHERE&&deptno&=&v_&&
&&:rows_deleted&:=&(SQL%ROWCOUNT&||'&rows&deleted.');&&
返回所删除行的数量
显式游标:由用户显式声明,查询返回多行记录
使用游标时，select语句查询的结果可以是单条记录，多条记录，也可以是零条记录。
游标工作区中，存在着一个指针（POINTER）,在初始状态它指向查询结果的首记录。
要访问查询结果的所有记录，可以通过FETCH语句，进行指针的移动来实现。
使用游标进行操作，包括定义游标、打开游标、提取数据以及关闭游标几步。
更多Oracle相关信息见 专题页面
相关资讯 & & &
　　　同意评论声明
　　　发表
尊重网上道德，遵守中华人民共和国的各项有关法律法规
承担一切因您的行为而直接或间接导致的民事或刑事法律责任
本站管理人员有权保留或删除其管辖留言中的任意内容
本站有权在网站内转载或引用您的评论
参与本评论即表明您已经阅读并接受上述条款博客分类：
--=============cursor:在pl/sql中用来 处理多行多列(包括0行) 的记录数据=====================
-- Cursor: oracle使用专有的sql 工作区(private sql workparea)来执行sql语句，存储信息.这个工作区称之为cursor
--oracle每执行每一个sql语句都有唯一的cursor与之对应.
--2.游标的分类
----显示游标:即用户自定义游标，专门用于处理select语句返回的多行数据
----隐式游标:系统自动定义的游标，记录集只有单行数据，用于处理select into 和DML语句
--3.游标使用的一般过程：
----显式游标：声明(declare), 打开(open,执行sql语句,产生结果集), 读取(fetch,提取结果集中记录), 关闭(close)
------显示cursor的属性：
------属性
------%isopen
open--& true
------%notfound
前一个fetch没有返回一行记录--&true
------%found
前一个fetch返回一行记录--&true,同%notfound相反.
------%rowcount
到目前为止 cursor已提取的总行数
----隐式游标：直接使用读取，声明、打开、关闭都是系统自动进行的
------隐式cursor的属性:
------ 属性
------SQL%isopen
dml执行为true,结束后
------SQL%notfound
sql%found属性返回值相反
------SQL%found
dml操作成功 true
------SQL%rowcount
dml执行成功的数据行数
--======显式游标============================================
---===============================================================================
--声明游标: Cu 在没有open之前 不会执行该select语句
Cursor c_emp is
select * from emp where deptno = 10 or deptno = 20;
v_emp emp% --声明变量,用来接收从cursor中的取出的每一条数据
c_ --打开cursor ,开始执行select 语句
--循环读取cursor结果
fetch c_emp into v_ --从结果集中取数据到v_emp变量，并移动指向数据的指针
if c_emp%FOUND
--如果上一个fetch找到记录 就返回true .
dbms_output.put_line(v_emp.ename);
close c_ --关闭close,释放资源
--2.record类型 + while处理cursor
-- 定义record类型
type r_emp_rec
is record(
eid emp.empno%type,
name emp.ename%type
Cursor c_emp_cursor
select empno , ename
from emp where empno = 7900;
r_emp_--声明 v_emp 记录类型的变量
open c_emp_
--必须先fetch，c_emp_cursor%found的值才会是true或false，
--若不fetch, c_emp_cursor%found的值为null
fetch c_emp_cursor into v_
--通过循环处理结果集中的数据，若c_emp_cursor%Found的值为false，循环结束
while c_emp_cursor%FOUND
dbms_output.put_line(v_emp.name);
fetch c_emp_cursor into v_ --再次fetch
close c_emp_
-- 3 . record类型 + for处理cursor +游标参数
-- ----For循环对cursor的处理进行了集成，
------ 不需要open、循环处理fetch、close。cursor中的数据通过for循环中的记录类型的变量emp执行引用。
type t_emp_rec is record(
salary emp.sal%type, name emp.ename%type
v_emp t_emp_
Cursor c_emp(emp_id number) is
-- emp_id是参数
select sal , ename from emp where empno = emp_
for emp in c_emp(7900)
dbms_output.put_line(emp.ename);
--===========总结==============
--cursor的声明
----1.在游标声明中使用标准的select 语句
----2.查询中可以用order by来处理次序问题。
----3.可以在查询中引用变量，但必须在cursor语句之前声明这些变量.
Cursor c_emp_id(emp_id number) is
select id from service where cost_id = emp_
--open cursor
----通过open cursor来执行select语句 并标识结果集.
----select语句没有记录返回 不会出现异常.
----open cursor emp_id(5);
--fech cursor.
----1.检查当前行 ,把值赋给变量.变量可以是record类型或者标量类型
----2. fetch
cursorName into 变量1,变量2 | record_
--cursor结果集处理
----1.使用loop处理cursor.
----2.每次fetch一行,反复进行.
----3.使用%notfound属性检测一次不成功的提取操作
----4.使用显示cursor的属性检测提取是否成功，避免死循环.
--关闭cursor
----1.最后一条记录，应该关闭cursor.(有需要，可以再次open)
----2,cursor一旦关闭，所有和当前cursor相关的资源都会释放，不能从关闭的 cursor中fetch数据.
----3.对任何关闭的cursor的操作都会invalid_cursor错误。
----4.每个session打开的cursor数量由open_cursor参数决定
--===========练习=================
-- 打印每个员工的ename , id ,不存在则打印
--select * from emp where 1 = 2; -- 模拟没有查询到记录
* from emp where 1 = 1;
v_emp emp%
fetch c_emp into v_
if c_emp%FOUND
while c_emp%FOUND LOOP
dbms_output.put_line(v_emp.ename ||'--------------'|| v_emp.deptno);
fetch c_emp into v_
exit when c_emp%NOTFOUND;
dbms_output.put_line('not exists');
whatisjavabean
浏览: 7571 次
来自: 广州
(window.slotbydup=window.slotbydup || []).push({
id: '4773203',
container: s,
size: '200,200',
display: 'inlay-fix' 上传我的文档
 下载
 收藏
该文档贡献者很忙，什么也没留下。
 下载此文档
正在努力加载中...
Oracle--plsql游标创建和使用
下载积分：840
内容提示：Oracle--plsql游标创建和使用
文档格式：DOCX|
浏览次数：70|
上传日期： 14:56:08|
文档星级：
全文阅读已结束，如果下载本文需要使用
 840 积分
下载此文档
该用户还上传了这些文档
Oracle--plsql游标创建和使用
关注微信公众号博客分类：
&&& 这是第五章的学习笔记，学习完第四章的数据库操作和事务之后，开始要学习游标的使用了……，希望大家能多给俺一些支持啊！
&&& 编程时使用的工具是PLSQL Developer 7.1.4
隐式游标
隐式游标的属性等在第四章笔记中已记录了一部分
如果要确保属性指向想要的SQL语句，那么就在SQL语句执行后，立即将属性值保存到一个本地变量中
用存储过程来实现这种效果：
先定义一个存储过程remove_from_emp ，用于从雇员中删除一个员工
在存储过程的参数中 in 表示输入，out 表示输出
create or replace procedure remove_from_emp(empno_in in employee.empno%type)
delete from employee where empno = empno_
dbms_output.put_line('删除了' || sql%rowcount || '条记录！');
-- 定义一个存储过程来调用存储过程remove_from_emp，并保存隐式游标的属性值
create or replace procedure show_emp_count is
i_numfound pls_
select count(*) into i_
-- 将属性值做一个快照
i_numfound := sql%
-- 注意employee表中没有编号为99的员工
-- 调用存储过程remove_from_emp
remove_from_emp(1);
-- 现在可以用前一条语句的属性值输出
dbms_output.put_line(i_numfound);
显式游标(PL/SQL块中使用显示游标的本个步骤：声明、打开、提取记录、关闭)
在程序包规范中声明一个游标类型(我们可以在PL/SQL块或者包的定义部分声明游标类型)
create or replace package types
type emp_cur5
-- 定义一个不带参数的游标emp_cur1，游标的结果是employee表中员工的集合
cursor emp_cur1 is select *
-- 定义一个带参数的游标emp_cur2，游标的结果集是匹配由游标传递过来的员工编号的员工姓名和加入公司的时间
cursor emp_cur2 (empno_in in number) is select name,hiredate from employee where empno = empno_
-- 定义一个带有return 子句的游标，游标的结果集是员工编号为1的employee表中所有的列
cursor emp_cur3 return employee%rowtype is select * from employee where empno = 1;
如果想通过游标更新数据，必须在select 语句后加上for update 子句，
该select 语句涉及的所有行都会被锁住
如果是加上了for update of 列名，那么只有在for update 子句中引用了某个表的列时，
该表中的行才会被锁住，of 列表并不限制只能更改列给出的列，它只让我们知道要更改什么
可以在for update 子句后添加一个nowait 关键字，用于告诉Oracle如果表已经被其他用户
锁住，就不需要等待了，这样控制权会立即返回给我们的程序，如果没有nowait子句进程就
会阻塞，直到表可用(commit或rollback)为止
可以在Select into、Fetch into、Returning into子句中使用Bulk Collect将数据输出到集合中
cursor emp_cur4 (empno_in in number) is select name,salary from employee where empno = empno_
-- 应该总是将游标行提取到用%rowtype定义的记录中，这样更灵活(表字段改变了不需要更改fetch语句)
emp_row1 employee%
-- 基于游标定义一个记录
emp_row2 emp_cur2%
emp_row3 emp_cur3%
emp_row4 emp_cur4%
n_salary number(10,2);
n_empno employee.empno%type := &员工编号：;
-- 游标变量和ref cursor
-- 定义ref cursor 类型的游标变量
-- 创建一个强类型的引用游标类型
type emp_ref_type1 is ref cursor return employee%
-- 创建一个弱类型的引用游标类型(弱类型的游标变量比强类型的游标变量更灵活)
type emp_ref_type2
-- 定义实际的游标变量
emp_ref1 emp_ref_type1;
emp_ref2 emp_ref_type2;
-- 从Oracle9i 开始提供了一个名为sys_refcursor的预定义的Oracle系统游标，
-- 它相当于弱类型游标，使用它不需要定义游标变量
sys_cursor sys_
-- 定义一个行类型的集合
type emp_table_type is table of employee%rowtype index by binary_
emp_table emp_table_
type emp_info_type is record(name employee.name%type,job employee.job%type);
emp_info emp_info_
打开游标:open 显式游标名 (参数列表)
一旦打开了显式游标，就可以从游标中提取记录，直到没有记录留在游标中
打开游标时，PL/SQL就开始执行该游标的Select 查询，但是实际上并不返回任何行，
返回行的任务是由后面的Fetch(读取)…… into(赋值给)……& 语句完成的：
fetch 游标名 into 记录或变量列表
open emp_cur1;
-- 提取记录
-- 如果游标只返回一行可以用if、loop或for来判断获得数据，如果游标返回多行可以用loop或for来循环获得数据
fetch emp_cur1 into emp_row1;
exit when emp_cur1%
dbms_output.put_line('员工' || emp_row1.name || '的工资是：' || emp_row1.salary);
-- 关闭游标并释放资源
close emp_cur1;
-- 打开带参数的游标
-- 游标for 循环能很好的简化游标的开发，我们不再需要声明记录，不再需要Open、Fetch和Close语句
-- 也不再需要%found属性检测记录，一切Oracle隐式的帮我们完成了
for emp_row2 in emp_cur2(n_empno) loop
dbms_output.put_line('员工' || emp_row2.name || '加入公司的时间是 ' || emp_row2.hiredate);
-- 打开带return 子句的游标
open emp_cur3;
fetch emp_cur3 into emp_row3;
if emp_cur3%found then
dbms_output.put_line('员工' || emp_row3.name || '其职位是' || emp_row3.job || '，加入公司的时间是 ' || emp_row3.hiredate);
close emp_cur3;
-- 打开带for update 子句的游标，将指定编号的员工工资增加500元
open emp_cur4(n_empno);
fetch emp_cur4 into emp_row4;
where current of 游标名 子句能很容易的修改最近提取的数据行(也就是当前游标指向的位置)，
这样的好处是，如果表表结构发生了改变，我们只需要更改Select语句的Where子句即可，而不
需要更新每个SQL语句
if emp_cur4%found then
update employee set salary = salary + 500 where current of emp_cur4; --returning salary into n_
n_salary := emp_row4.salary + 500;
dbms_output.put_line('员工' || emp_row4.name || '原来的工资是' || emp_row4.salary || '元，增加工资后现在的工资是' || n_salary || '元');
close emp_cur4;
-- 打开强类型游标变量
open emp_ref1 for select * from empl
-- 在游标变量中提取数据
fetch emp_ref1 bulk collect into emp_
for i in 1..emp_table.count loop
dbms_output.put_line(emp_table(i).name || '
本月工资 ' || emp_table(i).salary);
-- 关闭游标变量
close emp_ref1;
-- 打开弱类型游标变量
open emp_ref2 for select name,
fetch emp_ref2 into emp_
exit when emp_ref2%
dbms_output.put_line(emp_info.name || '的工作是 ' || emp_info.job);
close emp_ref2;
-- 打开Oracle系统游标
open sys_cursor for select name,hiredate from employee or
fetch sys_cursor into emp_
exit when sys_cursor%
dbms_output.put_line(emp_info.name || '加入公司的时间是 ' || emp_info.job);
close sys_
when NO_DATA_FOUND then dbms_output.put_line('查询不到员工编号为' || n_empno || '的员工！');
when TOO_MANY_ROWS then dbms_output.put_line('数据完整性错误，员工编号' || n_empno || '重复！');
when OTHERS then dbms_output.put_line('PL/SQL执行错误！' || sqlerrm);
论坛回复 /
(1 / 10394)
浏览: 71350 次
来自: 长沙
Rac_javac 写道必须给楼主
CSDN下了半天下不了 ...
huangxinyu_it 写道文件已损坏应该是网络问题，那么 ...
文件已损坏
必须给楼主
CSDN下了半天下不了，还是楼主贴心啊，谢谢了 ...
yxsylyh 写道能不能生成C++的类代码？可以生成，你是要 ...
(window.slotbydup=window.slotbydup || []).push({
id: '4773203',
container: s,
size: '200,200',
display: 'inlay-fix'打开游标后的循环 &
& i_total integer:=1000;
& cursor emp_cur is select * from emp
& i_emp emp%
& & open emp_
& & & fetch emp_cur into i_
& & & exit when emp_cur%
& & & i_total:=i_total+i_emp.
& & &close emp_
& & & dbms_output.put_line(i_total);
& & & select into&
create or replace procedure t_p(emp_id number) is
&v_name varchar2(1000);
& select first_name into v_name from emp where employee_id=emp_
& dbms_output.put_line(v_name);
& & & 动态查询
create or replace procedure p1(employee_id number, department_id number) is
& & & i_name varchar2(100);
& & & begin
& & & & execute immediate 'select first_name from emp where employee_id='||employee_id ||' and department_id='||department_id into i_
& & & & dbms_output.put_line(i_name);
动态查询，结果放到集合中
create or replace procedure p2(department_id number) is
& type names is table of varchar2(100);
& execute immediate 'select first_name from emp where department_id=' ||
& & & & & & & & & & department_id bulk collect
& & into i_
& for idx in 1 .. i_name.count loop
& & dbms_output.put_line(i_name(idx));
游标变量的使用范围
Pass a cursor variable back to the host environment that called the program unit—the result set can be “consumed” for display or other processing.
Construct a result set inside a function, and return a cursor variable to that set. This is especially handy when you need to use PL/SQL, in addition to SQL, to build the result set.
Pass a cursor variable to a pipelined table function—a powerful but quite advanced optimization technique. A full explanation of cursor variables, including the differences between strong and weak REF CURSOR types, is beyond the scope of this article.
Instead, I will show the basic syntax for working with cursor variables and identify situations in which you might consider using this feature
& CREATE OR REPLACE FUNCTION names_for(name_type_in IN VARCHAR2)
& & RETURN SYS_REFCURSOR IS
& & l_return SYS_REFCURSOR;
& & CASE name_type_in
& & & WHEN 'EMP' THEN
& & & & OPEN l_return FOR
& & & & & SELECT last_name FROM emp ORDER BY employee_
& & & WHEN 'DEPT' THEN
& & & & OPEN l_return FOR
& & & & & SELECT name FROM departments ORDER BY
& & END CASE;
& & RETURN l_
& END names_
使用游标变量
& &l_names & SYS_REFCURSOR;
& &l_name & &VARCHAR2 (32767);
& &l_names := names_for ('DEPT');
& & & FETCH l_names INTO l_
& & & EXIT WHEN l_names%NOTFOUND;
& & & DBMS_OUTPUT.put_line (l_name);
& &END LOOP;
& &CLOSE l_
没有显示声明的游标是隐式游标，隐式游标有被称为sql游标，可以使用游标属性查看游标的信息。
updateset'test'where123
sqlrowcount'rows been updated'
ifsqlnotfoundthen
'no record'
whenothersthen
显示游标提供了比隐式游标更多的功能，通常使用显示游标处理select语句返回的多行数据。
在游标中指定参数
cursorinnumberis
selectfromwhere
指定游标的返回类型
cursorinnumberreturnrowtypeis
selectfrom
使用bulk collect语句批量提取游标数据
typeistableofrowtype
selectfrom
fetchbulkcollect
forin1loop
使用bulk collect limit语句批量提取游标数据，用于游标中的数据量很大的情况下
typeisvarray4ofrowtype
selectfrom
fetchbulkcollect
exitwhennotfound
forin1rowcountloop
cursorinnumberis
selectfromwhereforupdate
forin20loop
updateset'test'wherecurrentof
delete也是一样的
delete from t9 where current of t9_
之前声明的游标，在定义的时候就绑定了一个查询，这种的游标称为静态游标，游标变量是在定义的时候不绑定到具体的查询，可以打开任何类型兼容的查询。
typeisrefcursorreturnrowtype
selectfrom
exitwhennotfound
可以使用sys_refcursor类型的游标来定义游标变量下面的2个语句是等效的
Emp_cur emp_
与下面的语句是等效的
emp_cursor sys_
本文已收录于以下专栏：
相关文章推荐
varchar2(50);
cursor cap is
select id,name from user_infor order by...
游标是什么：游标字面理解就是游动的光标。用数据库语言来描述：游标是映射在结果集中一行数据上的位置实体，有了游标用户就可以访问结果集中的任意一行数据了，将游标放置到某行后，即可对该行数据进行操作，例如提...
PLSQL中显示游标使用的4个步骤
本例简单介绍一下游标处理的4个过程中涉及到的加锁,一致性读的问题.
显式游标处理需四个 PL/SQL步骤:
#静态游标指的是程序执行的时候不需要再去解析sql语言，对于sql语句的解析在编译的时候就可以完成的。
动态游标由于含有参数，对于sql语句的解析必须要等到参数确定的时候才能完成。
#静态游标指的是程序执行的时候不需要再去解析sql语言，对于sql语句的解析在编译的时候就可以完成的。
动态游标由于含有参数，对于sql语句的解析必须要等到参数确定的时候才能完成。
--PL/SQL supports three kinds of records: table-based, cursor-based,and programmer-defined.
游标（cursor）是Oracle系统在内存中开辟的一个工作区，在其中存放SELECT语句返回的查询结果。
游标的分类
PL/SQL隐式建立并自动管理这一游标。隐式游...
他的最新文章
讲师：李江龙
讲师：司徒正美
您举报文章：
举报原因：
原文地址：
原因补充：
(最多只允许输入30个字)}