oracle pl/sql 存储过程（抄）

正雅1997

　　游标


环境区域是用来处理SQL
语句的一个oracle存储区域。游标是指向它的指针或句
柄。通过游标，PL
/SQL
程
序可以控制这个环境区域中被处理的语句。


Oracle中的游标有两种：显式游标、隐式游标。


显示游标是用cursor...is命令定义的游标，它可以对查询语句(select)返回的多条记录进行处理，而隐式游标是在执行插入
(insert)、删除(delete)、修改(update)和返回单条记录的查询(select)语句时由PL
/SQL
自动定义的。



1、显式游标操作


显式游标在块定义部分、包或子程序中声明。当声明了显式游标后，可以通过以下三条命令控制显式游标的操作：打开游标、推进游标、关闭游标。


(1)声明显式游标


--例1

Java
代码

• 
  declare  
  
  
• 
    v_auths auths%rowtype;  
  
• 
    v_code auths.author_code%type;  
  
• 
    cursor c_auths is   
  
• 
      select * from auths where author_code=v_code;  
  

declare
v_auths auths%rowtype;
v_code auths.author_code%type;
cursor c_auths is
select * from auths where author_code=v_code;
　　上例是将PL
/SQL
变量绑定在WHERE子句中，
下面将游标参数绑定在游标的WHERE子句中：


--例2

Java
代码

• 
  delcare  
  
  
• 
    cursor c_auths(p_code auths.author_code%type) is  
  
• 
      select * from auths where author_code=p_code;  
  

delcare
cursor c_auths(p_code auths.author_code%type) is
select * from auths where author_code=p_code;
　　


(2)打开显式游标


游标操作的第一步是打开游标。


例1，下面的语句是打开上节例1中声明的显式游标c_auths;

Java
代码

• 
  begin  
  
  
• 
    --
  在打开游标前为绑定变量赋值。  
  
• 
    v_code:='A00001'
  ;  
  
  
• 
    --
  打开游标。  
  
• 
    open c_auths;  
  

begin
--在打开游标前为绑定变量赋值。
v_code:='A00001';
--打开游标。
open c_auths;
　　例2，如果对于一个带参数的游标

Java
代码

• 
  begin  
  
  
• 
    --
  打开游标时将参数传入。  
  
• 
    open c_auths('A00001'
  );  
  
  

begin
--打开游标时将参数传入。
open c_auths('A00001');
　　打开一个已打开的游标也是合法的。当第二次打开游标时，PL
/SQL
先自动关闭游标，然后再打开。
一次打开多个游标也是PL
/SQL
所
允许的。


(3)推进显式游标


当打开显式游标后，就可以使用FETCH语句来推进游标，返回查询结果集中的一行。每执行完一条FETCH语句后，显式游标会自动指向查询结果集
的下一行。


(4)关闭显式游标


当整个结果集都检索完以后，应当关闭游标。关闭游标用来通知PL
/SQL
游标操作已经结束，并且释放游标所占用的资源(结果集所使用的资源空间)。



2、游标的属性


游标有四个属性：%found、%notfound、%isopen和%rowcount。要注意这些属性只能使用在过程性语句中，而不能使用在SQL
语句中。


表tableattribute，表中有两列column1(number类型)和column2(varchar2类型)，现在向表中插入两条
记录：


insert into tableattribute values(10,'first');


insert into tableattribute values(20,'second');


...



3、显式游标的推进循环

Java
代码

• 
  delcare  
  
  
• 
    --
  声明一个变量，这个变量用来接收游标返回的结果集。  
  
• 
    v_salary auths.salary%type;  
  
• 
    v_code auths.author_code%type;  
  
• 
    /*声明游标，该游标的查询结果集是作家代码为"A00001"到"A00006"的工资值。*/
    
  
  
• 
    cursor c_salary is select salary,author_code from auths where author_code<='A00006'
  ;  
  
  
• 
  begin  
  
• 
    --
  打开游标，并初始化结果集  
  
• 
    open c_salary;  
  
• 
    loop  
  
• 
      --
  推进游标，将游标的查询结果集中的一行存到变量v_salary中。  
  
• 
      fetch c_salary into v_salary,v_code;  
  
• 
      --
  当结果集中没有行时退出循环。  
  
• 
      exit when c_salary%notfound;  
  
• 
      --
  如果查询到的作家工资小于或等于200
  ，则增加该作家的工资值。  
  
  
• 
      if
   v_salary<=
  200
   then  
  
  
• 
        update auths set salary=salary+50
   where author_code=v_code;  
  
  
• 
      end if
  ;  
  
  
• 
    end loop;  
  
• 
    --
  关闭游标，释放游标占用资源。  
  
• 
    close c_salary;  
  
• 
    --
  提交所做的修改。  
  
• 
    commit;  
  
• 
  end;  
  

delcare
--声明一个变量，这个变量用来接收游标返回的结果集。
v_salary auths.salary%type;
v_code auths.author_code%type;
/*声明游标，该游标的查询结果集是作家代码为"A00001"到"A00006"的工资值。*/
cursor c_salary is select salary,author_code from auths where author_code<='A00006';
begin
--打开游标，并初始化结果集
open c_salary;
loop
--推进游标，将游标的查询结果集中的一行存到变量v_salary中。
fetch c_salary into v_salary,v_code;
--当结果集中没有行时退出循环。
exit when c_salary%notfound;
--如果查询到的作家工资小于或等于200，则增加该作家的工资值。
if v_salary<=200 then
update auths set salary=salary+50 where author_code=v_code;
end if;
end loop;
--关闭游标，释放游标占用资源。
close c_salary;
--提交所做的修改。
commit;
end;
　　PL
/SQL
还
提供了一种简单类型的循环，可以自动控制游标的打开、推进和关闭，叫做游标的FOR循环。

Java
代码

• 
  delcare  
  
  
• 
    cursor c_salary is  
  
• 
      select salary form auths where author_code<='A00006'
  ;  
  
  
• 
  begin  
  
• 
    --
  开始游标FOR循环，隐含地打开c_salary游标。  
  
• 
    for
   v_salary in c_salary loop  
  
  
• 
      --
  一个隐含的fetch语句在这里被执行。  
  
• 
  　　if
   v_salary.salary<=
  200
   then  
  
  
• 
        update auths set salary=salary+50
   where salary=v_salary.salary;  
  
  
• 
      end if
  ;  
  
  
• 
      --在循
  环继续前，一个隐含的c_auths%notfound被检测。  
  
• 
  　end loop;  
  
• 
    --
  现在循环已经结束，c_auths游标的一个隐含的close操作被执行。  
  
• 
    commit;  
  
• 
  end;  
  

delcare
cursor c_salary is
select salary form auths where author_code<='A00006';
begin
--开始游标FOR循环，隐含地打开c_salary游标。
for v_salary in c_salary loop
--一个隐含的fetch语句在这里被执行。
　　if v_salary.salary<=200 then
update auths set salary=salary+50 where salary=v_salary.salary;
end if;
--在循环继续前，一个隐含的c_auths%notfound被检测。
　end loop;
--现在循环已经结束，c_auths游标的一个隐含的close操作被执行。
commit;
end;
　　使用current of cursor子句作为条件

Java
代码

• 
  delcare  
  
  
• 
    --
  声明游标时在select语句中必须加for
   update of子句。  
  
  
• 
    cursor c_salary is  
  
• 
      select salary form auths where author_code<'A00006'
   
  for
   update of salary;  
  
  
• 
  begin  
  
• 
    for
   v_salary in c_salary loop  
  
  
• 
      if
   v_salary.salary<=
  200
   then  
  
  
• 
        --
  下面的update语句中的current of子句用来表明结果集的当前行。  
  
• 
      end if
  ;  
  
  
• 
    end loop;  
  
• 
    commit;  
  
• 
  end;  
  

delcare
--声明游标时在select语句中必须加for update of子句。
cursor c_salary is
select salary form auths where author_code<'A00006' for update of salary;
begin
for v_salary in c_salary loop
if v_salary.salary<=200 then
--下面的update语句中的current of子句用来表明结果集的当前行。
end if;
end loop;
commit;
end;
　　如果在游标的FOR循环中使用子查询，则不用在块定义部分声明显式游标，在FOR循环中子查询隐含声明了一个显式游标。

Java
代码

• 
  begin  
  
  
• 
    --
  在下面的FOR循环中隐含地声明了一个游标c_salary。  
  
• 
  　for
   c_salary in  
  
  
• 
    (select salary form auths where author_code<='A00006'
  ) loop  
  
  
• 
      if
   c_salary.salary<=
  200
   then  
  
  
• 
        update auths set salary=salary+50
   where salary=c_salary.salary;  
  
  
• 
      end if
  ;  
  
  
• 
    end loop;  
  
• 
    commit;  
  
• 
  end;  
  

begin
--在下面的FOR循环中隐含地声明了一个游标c_salary。
　for c_salary in
(select salary form auths where author_code<='A00006') loop
if c_salary.salary<=200 then
update auths set salary=salary+50 where salary=c_salary.salary;
end if;
end loop;
commit;
end;
　　



4、隐式游标处理

PL
/SQL
隐式地打
开SQL
游标，并在它内部处理SQL
语
句，然后关闭它。SQL
游标用来处理insert、update、delete以及返回一行
的select...into语句。


一个SQL
游标不管是打开还是关闭，open、fetch和close命令都不能操
作它。SQL
游标与显式游标类似，也有四个一样的属性。当打开SQL
游标之前，SQL
游标的属性都为
NULL。

Java
代码

• 
  begin  
  
  
• 
    update auths set entry_date_time=sysdate where author_code='A00017'
  ;  
  
  
• 
    --
  如果update语句中修改的行不存在(SQL
  %notfound返回值为true
  ),则向auths表中插入一行。  
  
  
• 
    if
   sql
  %nofound then  
  
  
• 
      insert into auths(author_code,name,sex,birthdate,salary,entry_date_time)  
  
• 
        values('A000017'
  ,
  'qiuys'
  ,
  1
  ,
  '30-apr-40'
  ,
  88.5
  ,sysdate);  
  
  
• 
    end if
  ;  
  
  
• 
  end;  
  

begin
update auths set entry_date_time=sysdate where author_code='A00017';
--如果update语句中修改的行不存在(SQL
%notfound返回值为true),则向auths表中插入一行。
if sql
%nofound then
insert into auths(author_code,name,sex,birthdate,salary,entry_date_time)
values('A000017','qiuys',1,'30-apr-40',88.5,sysdate);
end if;
end;
　　--如果update语句中修改的行不存在(sql
%rowcount=0)

Java
代码

• 
  declare  
  
  
• 
    v_birthdate date;  
  
• 
  begin  
  
• 
    select birthdate into v_birthdate from auths where name='qiuys'
  ;  
  
  
• 
    --
  如果查询到一条记录，则删除该记录。  
  
• 
    if
   sql
  %found then  
  
  
• 
      delete from auths where name='qiuys'
  ;  
  
  
• 
    end if
  ;  
  
  
• 
  exception  
  
• 
    when no_data_found then  
  
• 
      dbms_output.put_line('该记录不存在'
  );  
  
  
• 
    when too_many_rows then  
  
• 
      dbms_output_line('存在同名的作家'
  );  
  
  
• 
  end;  
  

declare
v_birthdate date;
begin
select birthdate into v_birthdate from auths where name='qiuys';
--如果查询到一条记录，则删除该记录。
if sql
%found then
delete from auths where name='qiuys';
end if;
exception
when no_data_found then
dbms_output.put_line('该记录不存在');
when too_many_rows then
dbms_output_line('存在同名的作家');
end;
　　




5、游标变量


到目前为止前面所有显式游标的例子都是静态游标－即游标与一个SQL
语句关联，并且
该SQL
语句在编译时已经确定。


而游标变量是一个引用类型(REF)的变量。


(1)游标变量的声明

Java
代码

• 
  declare  
  
  
• 
    --
  使用%rowtype定义一个游标变量类型。  
  
• 
    type t_authsref is ref cursor return
   auths%rowtype;  
  
  
• 
    --
  定义一个记录类型。  
  
• 
    type t_coderecord is record(  
  
• 
      author_code article.author_code%type,  
  
• 
      article_code article.article_code%type);  
  
• 
    --
  声明一个记录类型的变量。  
  
• 
    v_code t_coderecord;  
  
• 
    --
  使用t_coderecord作为一个游标变量类型的结果集类型。  
  
• 
    type t_coderef is ref cursor return
   t_codeRecord;  
  
  
• 
    --
  使用v_code作为一个游标变量类型的结果集类型。  
  
• 
    type t_coderef2 is ref cursor return
   v_code%type;  
  
  
• 
    --
  使用上面的类型声明的两个游标变量。  
  
• 
    v_authcv t_authsref;  
  
• 
    v_codecv t_coderef;  
  

declare
--使用%rowtype定义一个游标变量类型。
type t_authsref is ref cursor return auths%rowtype;
--定义一个记录类型。
type t_coderecord is record(
author_code article.author_code%type,
article_code article.article_code%type);
--声明一个记录类型的变量。
v_code t_coderecord;
--使用t_coderecord作为一个游标变量类型的结果集类型。
type t_coderef is ref cursor return t_codeRecord;
--使用v_code作为一个游标变量类型的结果集类型。
type t_coderef2 is ref cursor return v_code%type;
--使用上面的类型声明的两个游标变量。
v_authcv t_authsref;
v_codecv t_coderef;
　　PL
/SQL2.8以上版本中，可
以使用一个没有指定结果集类型的游标变量来指定多个不同类型的查询。


type t_authsref if ref cursor;


v_cursorvar t_authsref;--声明一个该类型的变量。


(2)打开游标变量


为了将一个游标变更与一个具体的select语句联系起来，open的语法中增加了一个select语句。

Java
代码

• 
  open cursor_variable 
  for
   select_statement;  
  
  
• 
  declare  
  
• 
    type t_authorsref is ref cursor return
   auths%rowtype;  
  
  
• 
    v_authscv t_authorsref;  
  
• 
    --
  然后打开  
  
• 
    open v_authscv for
   select * from auths;  
  
  

open cursor_variable for select_statement;
declare
type t_authorsref is ref cursor return auths%rowtype;
v_authscv t_authorsref;
--然后打开
open v_authscv for select * from auths;
　　(3)推进游标变更


(4)关闭游标变更


该操作用来释放查询所占用的资源。但没有释放游标变量占用的存储空间。当变量超出作用域时，它所占用的空间才被释放掉。


下面的块中定义了一个没有指定结果集的游标变量，这样我们就可以使用这个游标变量指向不同的查询，并能够返回不同的记录类型：

Java
代码

• 
  set serveroutput on size 
  100000
   --设置存储缓冲区大小。  
  
  
• 
  declare  
  
• 
    /*定义游标变更类型t_curref，该游标变量类型没有指定结果集类型，所以该游标变量类型的变量可以返回不同的PL
  /SQL
  记录类型。*/
    
  
  
• 
    type t_curref is ref cursor;  
  
• 
    --
  声明一个游标变量类型的变量  
  
• 
    c_cursorref t_curref;  
  
• 
    --
  定义PL
  /SQL
  记录类型
  t_authorrec，该类型的变量用来接收游标变量的返回值。  
  
• 
    type t_authorrec is record(  
  
• 
      authorcode auths.author_code%type,  
  
• 
      name auths.name%type);  
  
• 
    --
  定义PL
  /SQL
  记录类型
  t_articlerec,该类型的变量也用来接收游标变量的返回值。  
  
• 
    type t_articlerec is record(  
  
• 
      authorcode article.author_code%type,  
  
• 
      title artitle.title%type);  
  
• 
    --
  声明两个记录类型变量。  
  
• 
    v_author t_authorrec;  
  
• 
    v_article t_articlerec;  
  
• 
  begin  
  
• 
    --
  打开游标变量c_cursorref，返回t_authorrec类型的记录。  
  
• 
    open c_cursorref for
     
  
  
• 
      select author_code,name from auths where author_code in('A00001'
  ,
  'A00002'
  ,
  'A00003'
  ,
  'A00004'
  ,
  'A00005'
  );  
  
  
• 
    --
  推进游标变量  
  
• 
    fetch c_cursorref into v_author;  
  
• 
    --
  游标变量的推进循环。  
  
• 
    while
   c_cursorref%found loop  
  
  
• 
      --
  将作家代码和相应的作家名字输出到屏幕上。  
  
• 
      dbms_output.put(v_author.authorcode||':'
  ||v_author.name||
  ' '
  );  
  
  
• 
      fetch c_cursorref into v_author;  
  
• 
    end loop;  
  
• 
    dbms_output.new_line;--
  向屏幕上输出一个回车行。  
  
• 
    --关闭游标变量，仅仅将游标变量指定的资源释放掉，游标变量本身的存储
  空间没有释放掉。  
  
• 
    close c_cursorref;  
  
• 
    --
  再次打开游标变量，返回t_articlerec类型的记录。  
  
• 
    open c_cursorref for
     
  
  
• 
      select author_code,title from article  
  
• 
      where author_code in('A00001'
  ,
  'A00002'
  ,
  'A00003'
  ,
  'A00004'
  ,
  'A00005'
  );  
  
  
• 
    fetch c_cursorref into v_article;  
  
• 
    while
   c_cursorref%found loop  
  
  
• 
      ...  
  
• 
    end loop;  
  
• 
    close c_cursorref;  
  
• 
  end;  
  

set serveroutput on size 100000 --设置存储缓冲区大小。
declare
/*定义游标变更类型t_curref，该游标变量类型没有指定结果集类型，所以该游标变量类型的变量可以返回不同的PL
/SQL
记录类型。*/
type t_curref is ref cursor;
--声明一个游标变量类型的变量
c_cursorref t_curref;
--定义PL
/SQL
记录类型t_authorrec，该类型的变量用来接收游标变量的返回值。
type t_authorrec is record(
authorcode auths.author_code%type,
name auths.name%type);
--定义PL
/SQL
记录类型t_articlerec,该类型的变量也用来接收游标变量的返回值。
type t_articlerec is record(
authorcode article.author_code%type,
title artitle.title%type);
--声明两个记录类型变量。
v_author t_authorrec;
v_article t_articlerec;
begin
--打开游标变量c_cursorref，返回t_authorrec类型的记录。
open c_cursorref for
select author_code,name from auths where author_code in('A00001','A00002','A00003','A00004','A00005');
--推进游标变量
fetch c_cursorref into v_author;
--游标变量的推进循环。
while c_cursorref%found loop
--将作家代码和相应的作家名字输出到屏幕上。
dbms_output.put(v_author.authorcode||':'||v_author.name||' ');
fetch c_cursorref into v_author;
end loop;
dbms_output.new_line;--向屏幕上输出一个回车行。
--关闭游标变量，仅仅将游标变量指定的资源释放掉，游标变量本身的存储空间没有释放掉。
close c_cursorref;
--再次打开游标变量，返回t_articlerec类型的记录。
open c_cursorref for
select author_code,title from article
where author_code in('A00001','A00002','A00003','A00004','A00005');
fetch c_cursorref into v_article;
while c_cursorref%found loop
...
end loop;
close c_cursorref;
end;
　　注意，在上例中，第一次关闭游标变量是可省略的，因为在第二次打开游标变量时，就将第一次的查询丢失掉了。而且游标变量也有游标属
性，通常在推进游标变量时使用这些游标属性，例如上例使用了%found属性。

　　
　　动态执行
　　

create or replace  procedure proc_insert
(
id in number, --输入序号
name in varchar2 --输入姓名
) as
str_sql varchar2(500);
begin
str_sql:=’insert into dinya_test values(:1,:2)’;
execute immediate str_sql using id,name; --动态执行插入操作
exception
when others then
null;
end ;
 　　
　　tab tablename%rowtype

create or replace procedure fangCreateUserAccount(newLibId number,
oldLibId number) is
--type myCursor is ref cursor;
userAccountT user_account%rowtype;
--rcp_cur myCursor;
userId number;
oldUserId number;
cursor rcpD is
select distinct ua.product_id
from user_account ua
inner join lib_user lu on ua.user_id = lu.lib_user_id
where lu.lib_id = oldLibId
and ua.status = 0
and lu.type = 1;
begin
select lib_user_id into userId from lib_user where lib_id=newLibId and type=1;
delete from user_account where user_id=userId;
for uaObjD in rcpD loop
select * into userAccountT from user_account where product_id=uaObjD.product_id ;
insert into user_account
values
(userId,
seq_user_account.nextval,
userAccountT.Product_Id,
userAccountT.Version_Id,);
end loop;
commit;
end fangCreateUserAccount;