189 8069 5689
For 循环游标
创新互联-专业网站定制、快速模板网站建设、高性价比满洲网站开发、企业建站全套包干低至880元,成熟完善的模板库,直接使用。一站式满洲网站制作公司更省心,省钱,快速模板网站建设找我们，业务覆盖满洲地区。费用合理售后完善，十年实体公司更值得信赖。
（1）定义游标
（2）定义游标变量
（3）使用for循环来使用这个游标
declare
--类型定义
cursor c_job
is
select empno,ename,job,sal
from emp
where job='MANAGER';
--定义一个游标变量v_cinfo c_emp%ROWTYPE ，该类型为游标c_emp中的一行数据类型
c_row c_job%rowtype;
begin
for c_row in c_job loop
dbms_output.put_line(c_row.empno||'-'||c_row.ename||'-'||c_row.job||'-'||c_row.sal);
end loop;
end;
Fetch游标
使用的时候必须要明确的打开和关闭
declare
--类型定义
cursor c_job
is
select empno,ename,job,sal
from emp
where job='MANAGER';
--定义一个游标变量
c_row c_job%rowtype;
begin
open c_job;
loop
--提取一行数据到c_row
fetch c_job into c_row;
--判读是否提取到值，没取到值就退出
--取到值c_job%notfound 是false
--取不到值c_job%notfound 是true
exit when c_job%notfound;
dbms_output.put_line(c_row.empno||'-'||c_row.ename||'-'||c_row.job||'-'||c_row.sal);
end loop;
--关闭游标
close c_job;
end;
1.游标定义：
cursor XXXA is
SELECT 语句;
XXXB cursorName%rowtype;
XXXA: 游标名
XXXB: 游标行数据定义
2. 打开游标：
-- 打开之前最好先关一下，防止上次发生异常没有关掉而引发不必要的异常
IF XXXA%ISOPEN THEN
CLOSE XXXA;
END IF;
Open XXXA ;
Loop
Fetch XXXA into XXXB;
exit when XXXA%NOTFOUND;
... ... 处理逻辑
end loop;
close XXXA;
你尝试一下， 使用 函数 来处理， 应该就可以避免掉 存储过程参数没法写的问题。
创建返回结果集的函数
SQL create or replace package pkg_HelloWorld as
2 -- 定义ref cursor类型
3 type myrctype is ref cursor;
4 --函数申明
5 function getHelloWorld return myrctype;
6 end pkg_HelloWorld;
7 /
程序包已创建。
SQL CREATE OR REPLACE package body pkg_HelloWorld as
2 function getHelloWorld return myrctype
3 IS
4 return_cursor myrctype;
5 BEGIN
6 OPEN return_cursor FOR
7 SELECT 'Hello 1' AS a, 'World 1' AS B FROM dual
8 UNION ALL
9 SELECT 'Hello 2' AS a, 'World 2' AS B FROM dual;
10 return return_cursor;
11 END getHelloWorld;
12 end pkg_HelloWorld;
13 /
程序包体已创建。
注：Oracle 这里的函数，是一个返回游标类型的函数， 不是像 SQL Server 的那种叫 “表值函数” 的东西。
因此下面的写法会报错。
SQL SELECT * FROM pkg_HelloWorld.getHelloWorld();
SELECT * FROM pkg_HelloWorld.getHelloWorld()
*
第 1 行出现错误:
ORA-00933: SQL 命令未正确结束
SQL SELECT pkg_HelloWorld.getHelloWorld() FROM dual;
PKG_HELLOWORLD.GETHE
--------------------
CURSOR STATEMENT : 1
CURSOR STATEMENT : 1
A B
------- -------
Hello 1 World 1
Hello 2 World 2
C# 如何调用上面的 返回结果集的例子：
/// summary
/// 测试 调用 Oracle 返回结果集的函数.
/// /summary
private void CallFuncWithTable(OracleConnection conn)
{
// 创建一个 Command.
OracleCommand testCommand = conn.CreateCommand();
// 定义需要执行的SQL语句. testCommand.CommandText = "pkg_HelloWorld.getHelloWorld";
// 定义好，本次执行的类型，是存储过程. testCommand.CommandType = CommandType.StoredProcedure;
// 定义好，我这个参数，是 游标 + 返回值.
OracleParameter para = new OracleParameter("c", OracleType.Cursor);
para.Direction = ParameterDirection.ReturnValue;
testCommand.Parameters.Add(para);
// 执行SQL命令，结果存储到Reader中.
OracleDataReader testReader = testCommand.ExecuteReader();
// 处理检索出来的每一条数据.
while (testReader.Read())
{
// 将检索出来的数据，输出到屏幕上.
Console.WriteLine("调用函数:{0}; 返回:{1} - {2}",
testCommand.CommandText, testReader[0], testReader[1]
);
}
// 关闭Reader.
testReader.Close();
}
游标操作的优化：
-- 有一个表格，存储的是用户的名字，将 emp 表中所有的用户名转换成小写，再写入这个表格
create table ename_emp(
ename varchar2(50)
);
-- 下面这个游标的操作，需要 43s时间才能完成
declare
begin
for i in (select ename from emp_liebiao) loop
insert into ename_emp values(lower(i.ename));
commit;
end loop;
end;
-- 因为游标是以行为单位进行数据的操作的，所有游标的效率是比较慢的，而且游标需要消耗的内存也是比较多的，我们需要将游标以行进行操作的方式，修改成一次性操作所有数据的批量的方式。
批量操作在游标里面 叫做 bulk collect
第一步，创建一个表类型
type 表类型的名字 is table of 表名.列名 %type;
变量名 表类型的名字;
第三步，创建一个游标，读取某个查询的结果
cursor 游标名字 is select 查询语句 ;
第四步，打开游标
open 游标名字;
第五步，捕获游标的数据，将内容给到表类型的变量进行保存
fetch 游标名字 bulk collect into 变量名字 ;
第六步，使用 forall 语句，对数据进行批量的操作
forall i in 变量 .first .. 变量 .last DML 语句操作 ;
第七步，关闭游标
close 游标名字 ;
declare
-- 创建表类型
type biao is table of emp_liebiao.ename%type;
b biao;
-- 创建游标
cursor m is select ename from emp_liebiao;
begin
-- 打开游标
open m;
-- 将游标的内容批量的给到变量
fetch m bulk collect into b;
-- 使用forall批量修改数据
forall i in b.first .. b.last insert into ename_emp values(lower(b(i)));
commit;
-- 关闭游标
close m;
end;
练习：批量修改用户的编号，让编号+工资等级+部门，形成一个新的编号，存入下面的表格中，使用 bulk collect 来实现。
2000以下是 C ，2000-3000是 B ，3000以上是 A ，
例如 SMITH ， 新编号应该是 7369_C_20
create table empno_emp(
empno varchar2(50)
);
declare
type biao is table of emp_liebiao%rowtype;
b biao;
cursor m is select * from emp_liebiao;
begin
open m;
fetch m bulk collect into b;
forall i in b.first..b.last
insert into empno_emp values(
b(i).empno||'_'||decode(sign(b(i).sal-2000)+sign(b(i).sal-3000),-2,'C',2,'A','B')||'_'||b(i).deptno
);
commit;
close m;
end;