oracle之游标_oracle执行存储过程接收返回的游标集合

1、概念性问题

概念：是指向查询结果集的一个指针

说明：Oracle会为查询语句分配一个空间，存储结果集
这块内存空间由游标来命名，通过游标来访问这块内存中存储的信息

类型：隐式游标、显示游标

有多条数据的时候用游标处理结果集
当查询的结果只有一条结果的时候可以不使用游标来处理结果，当然用游标处理也是可以的，但是就没有必要使用游标了。

2、游标属性

%isopen：测试游标是否已经打开，返回boolean值
%found：fetch后，是否提取到了记录，返回boolean值
%notfound：fetch后，是否没有提取到记录，返回boolean
%rowcount：返回游标中已提取的记录行数，返回整数

注意：已提取的记录行数不一定是总的记录行，但一般下我们提取到的行数就是总的记录行。

3、隐式游标

隐式游标由系统自动创建并管理

PL/SQL会为所有的SQL操作声明一个隐式游标

包括只返回一条操作记录的查询操作和所有的ＤＭＬ语句。

DML语句：

例： 在emp表中对某部门的员工薪水增加100元，并显示本次更新操作共涉及了多少员工。

不使用游标的方法：

declare
	v_no emp.deptno%type := &no;
	v_count number(2);
bengin
	update emp set sal=sal+100 where deptno=v_no;
	select count(empno) into v_count from emp where deptno=v_no;
	dbms_output.put_line(v_count);
end;
1
2
3
4
5
6
7
8

使用游标的方法：

declare
	v_no emp.deptno%type := &no;
	v_count number(2);
bengin
	update emp set sal=sal+100 where deptno=v_no;
	dbms_output.put_line(sql%rowcount);
end;
1
2
3
4
5
6
7

说明：所有隐式游标的名字都是sql

4、显示游标

显示游标，暂存查询取出的多行数据，然后一行一行地进行处理(一行一行意思就是使用循环处理)

使用步骤：

声明游标:　cursor 游标名 is select 语句;

打开游标：open 游标名称;–相当于执行查询语句
声明的游标必须打开后才能使用。

使用游标：一般在循环语句中使用fetch语句提取游标中的记录来进行操作(fetch语句就相当于指针)

关闭游标：close 游标名称;
游标使用完毕后，一定要关闭

说明：游标包含的就是查询的结果集

fetch语句的作用：

作用：取出一条记录，指针指向下一条记录

练习： 声明一个游标，用于取得部门10的员工的姓名和薪水信息

方案1:用变量普通接收游标中的数据：

declare
	cursor cur_emp is select ename,sal from emp where deptno=10;
	v_name emp.ename%type;
	v_sal emp.sal%type;
begin
	open cur_emp;
	loop
	fetch cur_emp into v_name,v_sal;
	exit when cur_emp %notfound;
	dbms_output.put_line(v_name || '        ' || v_sal);
	end loop;
	close cur_emp;
end;
1
2
3
4
5
6
7
8
9
10
11
12
13

方案2：用记录接收游标中的数据:

declare
	cursor cur_emp is select ename,sal from emp where deptno=10;
	type myrec is record(v_name emp.ename%type,v_sal emp.sal%type);
	emp_rec myrec;
begin
	open cur_emp;
	loop
	fetch cur_emp into emp_rec;
	exit when cur_emp %notfound;
	dbms_output.put_line(emp_rec.v_name || '        ' || emp_rec.v_sal);
	end loop;
	close cur_emp;
end;
1
2
3
4
5
6
7
8
9
10
11
12
13

方案3：使用复合变量%rowtype接收游标中的数据：

declare
	cursor cur_emp is select ename,sal from emp where deptno=10;
	emp_rec cur_emp%rowtype;
begin
	open cur_emp;
	loop
	fetch cur_emp into emp_rec ;
	exit when cur_emp %notfound;
	dbms_output.put_line(emp_rec.ename || '        ' || emp_rec.sal);
	end loop;
	close cur_emp;
end;
1
2
3
4
5
6
7
8
9
10
11
12

%rowtype该类型返回的是一个记录类型，保存从数据表中查询到的一行记录。

方案4：使用for循环接收游标中的数据：

declare 
	cursor mycur is select ename,sal from emp where deptno=10;
begin
	for my in mycur loop
	dbms_output.put_line(my.ename || '    ' || my.sal);
	end loop;
end;
1
2
3
4
5
6
7

点击我跳转到%rowtype的使用说明页面！

说明：当查询结果集是多条记录的时候只能使用游标————————显示的声明游标。

exit when cur_emp %notfound-------------属性%notfound前面需要加上加游标名

5、参数游标

使用带有参数的游标可能简化游标的管理

语法：
cursor cursor_name(parmeter_name datatype…) is select_ statement

举例：
如果需要对某一字段进行多值查询，如通过键盘录入，查询部门编号为10和部门编号为20的员工信息。

说明：不使用参数游标也是可以实现的，比如刚才上面的代码。（使用游标，可以不使用参数游标）

使用参数游标方式1：

declare
	cursor cur_emp(v_no emp.deptno%type) is select ename,sal from emp where deptno=v_no;
	emp_rec cur_emp%rowtype;
begin
	open cur_emp(10);
	loop
	fetch cur_emp into emp_rec ;
	exit when cur_emp %notfound;
	dbms_output.put_line(emp_rec.ename || '        ' || emp_rec.sal);
	end loop;
	close cur_emp;
end;

1
2
3
4
5
6
7
8
9
10
11
12
13

说明：cur_emp(v_no emp.deptno%type)中，我们设置了游标的参数v_no emp.deptno%type，这个游标参数v_no的类型是通过%type进行赋值的，如果不使用%type进行赋值的话，可以使用number或者varchar2进行赋值，但是number、varchar2都不能有参数，也就是说不能写成nnumber(2)、varchar2(5)这样带参数的形式。

上面的代码是通过open cur_emp(10);将游标参数进行v_no赋值为10的。

使用参数游标方式2：

declare
	cursor cur_emp(v_no emp.deptno%type) is select ename,sal from emp where deptno=v_no;
	emp_rec cur_emp%rowtype;
begin
    open cur_emp(&no);    --也可以这样，这样是进行输入的。
	loop
	fetch cur_emp into emp_rec ;
	exit when cur_emp %notfound;
	dbms_output.put_line(emp_rec.ename || '        ' || emp_rec.sal);
	end loop;
	close cur_emp;
end;

1
2
3
4
5
6
7
8
9
10
11
12
13

上面的代码是通过open cur_emp(&no);读入一个数字来为v_no进行赋值的。

使用参数游标方式3：

declare
	cursor cur_emp(v_no emp.deptno%type default 10)  is select ename,sal from emp where deptno=v_no;
	emp_rec cur_emp%rowtype;
begin
    open cur_emp;    --也可以这样，这样是调用默认值，设置默认值也是可以传参数的。
	loop
	fetch cur_emp into emp_rec ;
	exit when cur_emp %notfound;
	dbms_output.put_line(emp_rec.ename || '        ' || emp_rec.sal);
	end loop;
	close cur_emp;
end;
1
2
3
4
5
6
7
8
9
10
11
12

上面的代码在声明游标参数的时候就已经给参数设置了默认值，所以在open cur_emp;这个语句中可以不写参数使用默认参数值，也可以和前两种传值的方式来赋值而不使用默认值。

6、游标FOR循环

特点：

用FOR循环可大大简化游标的循环操作

在FOR循环中不需要事先定义循环控制变量

在游标的FOR循环之前，系统能自动打开游标；在FOR循环结束后，系统能够自动关闭游标，不需要人为操作。

在游标的FOR循环过程中，系统能够自动执行FETCH语句不需要人为执行FETCH语句。

格式：

Declare
  声明游标
Begin
  For record_name in 游标
  loop
  循环体
  End loop;
End;

循环体中只需要写输出语句就可以了，可以通过游标for循环的特点可知。

record_name是记录类型变量，不需要声明，Oracle会隐式声明，同样，当循环结束，它也会被隐式关闭。

练习： 显示雇员表中某一部门的员工薪水

declare
	cursor cur_emp(v_no emp.deptno%type default 10)  is select ename,sal from emp where deptno=v_no;
	--emp_rec cur_emp%rowtype;
begin
       --open cur_emp;   
	for emp_rec in cur_emp
	loop
	--fetch cur_emp into emp_rec ;
	--exit when cur_emp %notfound;
	dbms_output.put_line(emp_rec.ename || '        ' || emp_rec.sal);
	end loop;
	--close cur_emp;
end;
1
2
3
4
5
6
7
8
9
10
11
12
13

简化上面的代码：

declare
	cursor cur_emp(v_no emp.deptno%type default 10)  is select ename,sal from emp where deptno=v_no;
begin
	for emp_rec in cur_emp
	loop
	dbms_output.put_line(emp_rec.ename || '        ' || emp_rec.sal);
	end loop;
end;
1
2
3
4
5
6
7
8

---

刚才所接触的都是静态游标

---

7、游标变量

动态游标或引用游标

游标变量是指在打开时才指定其所应的SQL语句。

分类：强型游标和弱型游标

引用游标（REF游标）是一种动态游标，它比普通的静态游标更加灵活，因为它不依赖指定的查询语句。换言之，引用游标在运行时可以与不同的查询语句相关联，也可以使用游标变量。
引用游标有两种类型，即强型游标和弱型游标。强型游标返回指定格式的结果集，而弱型游标则没有返回类型。
除非有特殊的需要，建议在声明引用游标时指定返回类型，因为这样减小出现错误的概率。

尽量使用强型游标

a、强型游标

声明一个强型游标的变量

1、 有一个记录类型
2、声明游标返回上面定义的记录类型
3、 声明一个强型游标变量

处理游标变量的关键步骤

四步：声明、打开、提取和关闭

步骤格式：

声明一个记录类型，该记录的数据类型是select语句的结果集
Type emp_record is record(ename emp.ename%type,empjob varchar2(10),sal emp.sal%type);

声明一个ref cursor游标变量
Type ref_cursor_emp is ref cursor return emp_record;

声明一个强类型的游标变量
c1 ref_cursor_emp;

例：显示部门表中的所有记录

静态、显示游标的形式：

declare
	cursor dept_cur is select * from dept;
	dept_rec dept_cur%rowtype;
begin
	open dept_cur;
	loop
	fetch dept_cur into dept_rec;
	exit when dept_cur%notfound;
	dbms_output.put_line(dept_rec.dname || '         ' || dept_rec.loc);
	end loop;
	close dept_cur;
end;

1
2
3
4
5
6
7
8
9
10
11
12
13

强型游标、动态游标的形式：

declare
	--声明一个记录类型
	type dept_rec is rocord(dname dept.dname%type,loc dept.loc%type);
	--声明一个强型游标类型，返回值就是前面声明的记录类型
	type dept_cur is ref cursor return dept_rec;
	--声明强型游标变量
	c1 dept_cur;
	--用于接收游标中的一行数据
	mydept c1%rowtype;
begin
	open c1 for select dname,loc from dept;--后面可以有where子句
	loop
	fetch c1 into mydept;
	exit when c1%notfound;
	dbms_output.put_line(mydept.dname || '         ' || mydept.loc);
	end loop;
	close c1;
end;

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19

b、弱型游标

弱型游标的游标变量没有声明返回类型，当定义游标所对应的SQL语句时可以返回任意结构的数据，更自由和方便。

例：根据输入的字符确定显示哪一张表中的数据

declare
	type mycur is ref cursor;
	c1 mycur;
	emprec emp%rowtype;  --对应游标的记录变量
	deptrec dept%rowtype;   --对应游标的记录变量
	v_input char(1) := '&input';
begin
	if upper(v_input) = 'E' then
	open c1 for select * from emp;
	loop
	fetch c1 into emprec;
	exit when c1%notfound;
	dbms_output.put_line(emprec.empno || '    ' || emprec.ename || '    ' || emprec.job);
	end loop;
	close c1;
	elsif upper(v_input)= 'D' then
	open c1 for select * from dept;
	loop
	fetch c1 into deptrec;
	exit when c1%notfound;
	dbms_output.put_line(deptrec.deptno || '    ' || deptrec.dname);
	end loop;
	close c1;
	else
	dbms_output.put_line('输入错误');
	end if;
end;
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27