Cursor语法及理解

游标的两种概念

共享游标 : 是用户提交 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;

 

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/30126024/viewspace-2117550/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/30126024/viewspace-2117550/