游标的两种概念
共享游标
:
是用户提交
SQL
或
PL/SQL
程序块到
Oracle
的
share pool
之后，在
library cache
中生成的一个可执行对象，这个对象我们称之为游标(
cursor
)。是
SQL
语句在进行硬解析时生成的，其元数据被在视图
V$sqlarea
与
v$sql
中具体化。
PL/SQL
游标
:
则是用于存放
SQL
语句的执行结果
,
用户可以通过这个中间缓冲区
逐条
取出游标中的记录并对其处理，直到所有的游标记录被逐一处理完毕。需要声明、打开、提取、关闭。
共享游标包括父游标和子游标。
父游标：
是在进行硬解析时产生的。将
SQL
语句的文本进行哈希得到哈希值并在
library cache
寻找相同的哈希值(
SQL
语句必须完全一致包括大小写、空格回车等才能共享
)，如不存在则生存父游标且保存在
library cache
中，按顺序完成后续步骤。如果此时存在父游标，则进一步判断是否存在子游标。若存在相同的子游标，则直接调用其子游标的执行计划执行该
SQL
语句，否则转到下一步进行逻辑优化。
子游标：
在发生硬解析时，在产生父游标的同时，则跟随父游标会产生相应的子游标，此时
V$SQL.CHILD_NUMBER
的值为
。如果存在父游标，由于不同的运行环境，此时同样会产生新的子游标，新子游标的
CHILD_NUMBER
在已有子游标基础上以
1
为单位累计。
v$sql
中的每一行表示了一个
child cursor
子游标
，根据
sql_id
与父
cursor
关联。
child cursor
有自己的
address
，即
v$sql.child_address
。
如果你想确定是由那种原因造成的子游标，需要查看
v$sql_shared_cursor
。
1.
父游标的关键信息是
sql
文本，子游标的关键信息是执行计划和执行环境。
2.
硬解析通常是由于不可共享的父游标造成的，如经常变动的
SQL
语句，或动态
SQL
或未使用绑定变量等。解决硬解析的办法则通常是使用绑定变量来解决。
3.
与父游标
SQL
文本完全一致的情形下，多个相同的
SQL
语句可以共享一个父游标。
4.SQL
文本、执行环境完全一致的情形下，子游标能够被共享，否则如果执行环境不一致则生成新的子游标。如果
SQL
文本相同，但是可能提交
SQL
语句的用户不同，或者用户提交的
SQL
语句所涉及到的对象为同名词等，都有可能生成不同的子游标。因为这些
SQL
语句的文本虽然完全一样，但是上下文环境却不一样，因此这样的
SQL
语句不是一个可执行的对象，必须细化为多个子游标后才能够执行。
5.
游标是可以被所有进程共享的，也就是说如果
100
个进程都执行相同的
SQL
语句，那么这
100
个进程都可以同时使用该
SQL
语句所产生的游标，从而节省了内存。
这里讨论的游标是指
pl/sql
语句中定义的游标(
session cursor
)
。
Oracle
在
PL
/
SQL
中当查询返回结果超过一行时，从中每次指向一条记录进行交互的话，就需要用到游标。
例如：
SELECT
INTO
查询语句，一次只能从数据库中
select
一行数据来进行
insert
，但是如果要处理多行数据，就要由程序员定义一个显式游标，并通过与游标有关的语句进行处理。
说明：
select
* into new_table from table;
在
sqlserver
中正常，但
oracle
会报错，
在
oracle
里面
select...into...
是
pl/sql
的变量赋值语句
例如：
select
count(*) into v_count from table_name where id=1;
意思就是把
id=1
的数量放到一变量
v_count
中
当然
insert into new_table select * from table
都是在
sqlserver
或
oracle
中都是正常的，都可以一条
insert
语句插入多行数据
个人一般使用
for
m in (select * from emp)loop
来替代显式游标的功能
显式游标
(需要明确定义
cursor
，即有定义
cursor
的就是显示游标)
fetch
游标则必须用到显式游标，需要
open
和
close
显式游标语法如下
declare
cursor mycursor1 is select * from emp;
--
声明游标
table_row mycursor1%rowtype;
--mycursor1%rowtype
表示数据类型是一行数据
xx number;
begin
open mycursor1;
--
打开游标
loop
fetch mycursor1 into table_row;
--
取出游标结果放入变量
table_row
exit when mycursor1%notfound;
xx:=table_row.empno;
insert into emp_2 (empno,ename,job ) values (xx,table_row.ename,table_row.job);
end loop;
commit;
close mycursor1;
--
关闭游标
end;
如下显式游标
fetch
游标名
into
变量
,
变量
,
变量，且变量不需要再定义为游标名
%rowtype,
但是
sql
出现
游标名
.
字段
则会报错
PLS-00225:
subprogram or cursor
'MYCURSOR111' reference is out of scope
declare
cursor mycursor111 is select empno,ename,job from emp;
--
声明游标
xx number;
yy varchar2(10);
zz varchar2(10);
begin
open mycursor111;
--
打开游标
loop
fetch mycursor111 into xx,yy,zz;
--
取出游标结果放入变量
xx,yy,zz
exit when mycursor111%notfound;
insert into emp_2 (empno,ename,job ) values (xx,yy,zz);
end loop;
commit;
close mycursor111;
--
关闭游标
end;
显示游标
fetch
的注意事项
1.
游标对应的不管是一个还是多个字段，
fetch
游标名
.
字段
into
变量，会报错
PLS-00225: subprogram or cursor
'MYCURSOR111' reference is out of scope
，但是可以出现
fetch
游标名
into
字段
1
对应的变量
1,
字段
2
对应的变量
2,
字段
3
对应的变量
3
，这里这些变量可以不需要定义为
%rowtype
，如果定义为
%rowtype
，则只能
into
一个
%rowtype
的变量，
fetch
游标名
into
游标名
%rowtype
2.
游标对应多个字段时，
Fetch
游标名
into
变量时，其实是按顺序把字段一个个
into
到变量的，如果游标对应的字段有
3
个，但是
into
的变量只有
2
个，则报错
PLS-00394:
wrong number of values in the INTO list of a FETCH statement
3.
游标必须
open
，否则会报错
ORA-01001:
无效的游标
，当然如果没有
close
的话执行过程中不会报错，但是游标会一直存储在
PAG
中，直到
session
关闭才会释放，所以如果很多显式都不关闭会导致
PGA
很大。
游标
FOR
循环确实很好的简化了游标的开发，我们不在需要
open
、
fetch
和
close
语句，不在需要用
%FOUND
属性检测是否到最后一条记录，这一切
Oracle
隐式的帮我们完成了。
(虽然是显式游标，不过个人觉得更像隐式游标)
declare cursor mycursor1 is select * from emp;
--
声明
cursor
vv varchar2(200);
begin
--open mycursor1;
--
这里
open
游标反倒会报错
for m in mycursor1 loop
--m
不需要声明
vv:=m.empno;
insert into emp_2 (empno,ename,job ) values (vv,m.ename,m.job);
end loop;
commit;
--close mycursor1;
--
这里
close
游标反倒会报错
end;
下面语句只是比上面语句多了一行
xx
mycursor11%rowtype
，发现不需要这一行也一样。
declare cursor mycursor11 is select * from emp;
--
声明
cursor
xx mycursor11%rowtype;
yy varchar2(100);
begin
--open mycursor11; --
这里
open
游标反倒会报错
for xx in mycursor11 loop
--m
不需要声明
yy:=xx.empno;
insert into emp_2 (empno,ename,job ) values (yy,xx.ename,xx.job);
end loop;
commit;
--close mycursor11; --
这里
close
游标反倒会报错
end;
简单的
for
循环连
cursor
都不用声明了，此时
m
不需要声明类型，这是隐式游标
create table emp_2 as select * from emp where 1=2
declare vv varchar2(200);
begin
for m in (select * from emp)loop
vv:=m.empno;
insert into emp_2 (empno,ename,job ) values (vv,m.ename,m.job);
end loop;
commit;
end;
相比如上，以下加了
sql%found
、
sql%rowcount
可以说明是隐式游标
declare vv varchar2(200);
begin
for m in (select * from emp) loop
if
sql%found
then
dbms_output.put_line(
sql%rowcount
);
end if;
vv:=m.empno;
insert into emp_2 (empno,ename,job ) values (vv,m.ename,m.job);
end loop;
commit;
end;
隐式游标
DECLARE
hid
VARCHAR2
(
10
);
hname
varchar
(
20
);
BEGIN
SELECT
empno
,
ename
INTO
hid
,
hname
FROM
emp_2
;
IF
SQL
%
FOUND
then
dbms_output.put_line
(
hid
||
'
的名字是
'
||
hname
);
end
if;
END;
则如下报错，
select
into
只能有一行结果，加上
where
条件限定在一行后就不会报错了
ORA-01422:
实际返回的行数超出请求的行数
ORA-06512:
在
line 5
显式游标的个属性
cursor_name%ISOPEN
游标是否打开
cursor_name%FOUND
最近的
FETCH
是否提取到数据
cursor_name%NOTFOUND
最近的
FETCH
是否没有提取到数据
cursor_name%ROWCOUNT
返回到目前为止，已经从游标缓冲区中提取到数据的行数
隐式游标的属性
SQL%ROWCOUNT
代表
DML
语句成功执行的数据行数
SQL%FOUND
值为
TRUE
代表插入、删除、更新或单行查询操作成功
SQL%NOTFOUND
与
SQL%FOUND
属性返回值相反
SQL%ISOPEN DML
执行过程中为真，结束后为假
begin
for m in (select sign_id,SIGN_INFO_ID from MDS_SIGN_LIST)
loop
insert into MDS_SIGN_LIST_v2 (SIGN_ID,SIGN_INFO_ID )
values (m.sign_id,m.SIGN_INFO_ID);
end loop;
end;
5018
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
