oracle存储过程--游标

素颜马尾好姑娘i 2022-01-11 05:39 806阅读 0赞

## 存储过程，学会了游标的使用就学会了数据的处理。 ##

## 隐式游标select  into  from 获取值  ##

所有的隐式游标都被假设为只返回一条记录。

使用[隐式游标][Link 1]时，用户无需进行声明、打开及关闭。PL/SQL隐含地打开、处理，然后关掉[游标][Link 2]。

create or replace procedure pro_two(p_id in integer) is
      --declare 
      v_description varchar2(132) := '';
      v_code        lsy_codebook.codecode%type;
      v_codeorder   number := 0;
      v_temp        varchar2(132) := '';
    begin
      -- select  into  from 获取值
      select codecode, description
        into v_code, v_description
        from lsy_codebook
       where id = p_id;
      if v_code is null then
        raise no_data_found;
      elsif v_code = 'qw' then
        begin
          update lsy_codebook
             set description = v_description || ' changed'
           where id = p_id;
          commit;
          dbms_output.put_line(p_id || ':更新成功');
        end;
      else
        begin
          --  execute immediate类似于js中的eval方法。在此单独做个例子。
          -- 它把字符串当做sql命令执行。其中注意的是，关于单引号的转义。
          -- 在字符串中，单引号连续写两个就会转义成字符单引号，例如 '' 就是 '。
          -- 类似java中使用斜杠转义
          v_description := v_description || ' else changed';
          v_temp        := ' begin update lsy_codebook set description='' ' ||
                           v_description || '  '' where id=' || p_id ||
                           '; commit;end;';
          dbms_output.put_line(v_temp);
          execute immediate ' begin update lsy_codebook set description='' ' ||
                            v_description || '  '' where id=' || p_id ||
                            '; commit;end;';
          dbms_output.put_line(p_id || ':更新成功');
        end;
      end if;
    
      --异常捕获
    exception
      when no_data_found then
        begin
          dbms_output.put_line('no_data_found .');
        end;
      when too_many_rows then
        begin
          dbms_output.put_line('too_many_rows .');
        end;
      when others then
        begin
          rollback;
        end;
    end pro_two;

上面语句中，注意execute immediate的用法。它的作用类似js中的eval。好处不言而喻，可以动态在java程序中传入plsql命令。

需要注意的是单引号的转义，2个紧邻的单引号在字符串中就转义成了一个单引号字符。

## 游标循环loop EXIT WHEN  ##

create or replace procedure pro_three(p_code in varchar2) is
      ccode lsy_codebook.codecode%type;
      cname lsy_codebook.codename%type;
      cnum number :=0;
      CURSOR mycur IS
        select codecode, codename from lsy_codebook where codecode like '%'||p_code||'%';
    
    begin
      select count(*) into cnum from lsy_codebook where codecode like '%'||p_code||'%';
      dbms_output.put_line('结果集：  ' || cnum||'条');
      OPEN mycur;--打开
      loop
        FETCH mycur
          INTO ccode, cname;
        dbms_output.put_line('字段信息：  ' || ccode || '  ' || cname);
        EXIT WHEN mycur%NOTFOUND;
      end loop;
      CLOSE mycur;--关闭
    
      dbms_output.put_line('over ');
        --异常捕获
    exception
      when no_data_found then dbms_output.put_line('no_data_found .');
      when too_many_rows then dbms_output.put_line('too_many_rows .');
      when others then rollback;
    end pro_three;

![Center][]

![Center 1][]

## 游标for循环   for v\_temp in mycur loop ##

create or replace procedure pro_four(p_code in varchar2) is
      cnum number :=0;
      CURSOR mycur IS
        select codecode, codename from lsy_codebook where codecode like '%'||p_code||'%';
    
    begin
      select count(*) into cnum from lsy_codebook where codecode like '%'||p_code||'%';
      dbms_output.put_line('结果集：  ' || cnum||'条');
      --open mycur;--注意这里不能打开游标，否则会直接跳过
      --临时变量v_temp不用声明
      for v_temp in mycur loop
        dbms_output.put_line('字段信息：  ' || v_temp.codecode || '  ' || v_temp.codename);
      end loop;
      --close mycur;-- 不能写关闭
      
      --测试for循环
      for v_temp2 in 1..5 loop
        dbms_output.put_line('测试for循环：  ' || v_temp2);
      end loop;
    
      dbms_output.put_line('over ');
        --异常捕获
    exception
      when no_data_found then dbms_output.put_line('no_data_found .');
      when too_many_rows then dbms_output.put_line('too_many_rows .');
      when others then rollback;
    end pro_four;

打印：

结果集：  3条

字段信息：  gd  广东

字段信息：  gx  广西

字段信息：  gz  贵州

测试for循环：  1

测试for循环：  2

测试for循环：  3

测试for循环：  4

测试for循环：  5

over

## 游标while循环   while mycur%found loop ##

create or replace procedure pro_five(p_code in varchar2) is
      ccode lsy_codebook.codecode%type;
      cname lsy_codebook.codename%type;
      cnum number :=0;
      cint number :=0;
      CURSOR mycur IS
        select codecode, codename from lsy_codebook where codecode like '%'||p_code||'%';
    
    begin
      select count(*) into cnum from lsy_codebook where codecode like '%'||p_code||'%';
      dbms_output.put_line('结果集：  ' || cnum||'条');
      open mycur;--游标
      
      /* while的在游标中写法要注意，在while外，要先执行一句 fetch mycur into
      。因为判断条件是mycur%found，如果不先执行这一句，这个条件必然不成立，循环
      就直接跳过了。 */
      fetch mycur into ccode,cname;
      while mycur%found loop
        fetch mycur into ccode,cname;
        dbms_output.put_line('字段信息：  ' || ccode || '  ' || cname);
      end loop;
      close mycur;-- 关闭
      
      --测试while循环
      while cint<5 loop
        cint:=cint+1;
        dbms_output.put_line('测试while循环：  '||cint);
      end loop;
    
      dbms_output.put_line('over ');
        --异常捕获
    exception
      when no_data_found then dbms_output.put_line('no_data_found .');
      when too_many_rows then dbms_output.put_line('too_many_rows .');
      when others then rollback;
    end pro_five;

打印：

结果集：  3条

字段信息：  gx  广西

字段信息：  gz  贵州

测试while循环：  1

测试while循环：  2

测试while循环：  3

测试while循环：  4

测试while循环：  5

over

## 游标带参数  CURSOR mycur(c\_param varchar2) IS ##

create or replace procedure pro_six(p_code in varchar2) is
      --ccode lsy_codebook.codecode%type;
      --cname lsy_codebook.codename%type;
      cnum number :=0;
      cint number :=0;
      --  带参数的游标
      CURSOR mycur(c_param varchar2) IS
        select codecode, codename from lsy_codebook where codecode like '%'||c_param||'%';
    
    begin
      select count(*) into cnum from lsy_codebook where codecode like '%'||p_code||'%';
      dbms_output.put_line('结果集：  ' || cnum||'条');
      --open mycur(p_code);--游标，传入参数
      dbms_output.put_line('游标参数：  ' || p_code);
      for v_temp in mycur(p_code) loop
        dbms_output.put_line('字段信息：  ' || v_temp.codecode || '  ' || v_temp.codename);
      end loop;
      --close mycur;-- 关闭
    
      dbms_output.put_line('over ');
        --异常捕获
    exception
      when no_data_found then dbms_output.put_line('no_data_found .');
      when too_many_rows then dbms_output.put_line('too_many_rows .');
      when others then rollback;
    end pro_six;

打印：

结果集：  3条

游标参数：  g

字段信息：  gd  广东

字段信息：  gx  广西

字段信息：  gz  贵州

over

## ref cursor游标 ##

create or replace procedure pro_seven(p_code in varchar2, p_method number) is
      ccode lsy_codebook.codecode%type;
      cname lsy_codebook.codename%type;
      cdescription lsy_codebook.description%type;
      type type_mycursor is ref cursor;
      cnum number := 0;
      --  ref cursor游标
      mycur type_mycursor;
    begin
      if p_method = 1 then
        begin
          ---
          select count(*)  into cnum  from lsy_codebook
            where codecode like '%' || p_code || '%';
          dbms_output.put_line('p_method：'||p_method||'结果集：  ' || cnum || '条');
          open mycur for
            select codecode, codename
              from lsy_codebook
             where codecode like '%' || p_code || '%';
             if mycur%isopen then 
               begin
                   loop 
                     fetch mycur into ccode,cname;
                     exit when mycur%notfound;
                  -- open mycur for打开的游标不能用for循环语句了
                      dbms_output.put_line('字段信息：  ' || ccode || '  ' || cname);
                   end loop;
               end;
             end if;
          ---
        end;
      else  
        begin
              -----
              select count(*)  into cnum  from lsy_codebook
           where codecode like '%' || p_code || '%';
          dbms_output.put_line('p_method：'||p_method||'结果集：  ' || cnum || '条');
          open mycur for
            select codecode, description
              from lsy_codebook
             where codecode like '%' || p_code || '%';
             if mycur%isopen then 
               begin
                   loop 
                     fetch mycur into ccode,cdescription;
                     exit when mycur%notfound;
                  -- open mycur for打开的游标不能用for循环语句了
                      dbms_output.put_line('字段信息：  ' || ccode || '  ' || cdescription);
                   end loop;
               end;
             end if;
              
              -----
        end;
      end if;
    
      dbms_output.put_line('over ');
      --异常捕获
    exception
      when no_data_found then
        dbms_output.put_line('no_data_found .');
      when too_many_rows then
        dbms_output.put_line('too_many_rows .');
      when others then
        rollback;
    end pro_seven;

p\_method：1结果集：  3条

字段信息：  gd  广东

字段信息：  gx  广西

字段信息：  gz  贵州

over

p\_method：2结果集：  3条

字段信息：  gd   数据库无此记录！changed else changed

字段信息：  gx  测试描述22

字段信息：  gz  测试描述11

over

附测试表数据：

\-- Create table  
create table LSY\_CODEBOOK  
(  
  id           NUMBER(19) not null,  
  codetypecode VARCHAR2(20),  
  codeorder    NUMBER(10),  
  codecode     VARCHAR2(60),  
  codename     VARCHAR2(120),  
  builddate    DATE,  
  description  VARCHAR2(100)  
)  
tablespace USERS  
  pctfree 10  
  initrans 1  
  maxtrans 255  
  storage  
  (  
    initial 64K  
    next 1M  
    minextents 1  
    maxextents unlimited  
  );

<table> 
 <tbody> 
  <tr> 
   <th>ID</th> 
   <th>CODETYPECODE</th> 
   <th>CODEORDER</th> 
   <th>CODECODE</th> 
   <th>CODENAME</th> 
   <th>BUILDDATE</th> 
   <th>DESCRIPTION</th> 
  </tr> 
  <tr> 
   <td>500</td> 
   <td>sq</td> 
   <td>1</td> 
   <td>qw</td> 
   <td>全网</td> 
   <td>2013/7/1 0:08:05</td> 
   <td>&nbsp;changed</td> 
  </tr> 
  <tr> 
   <td>501</td> 
   <td>sq</td> 
   <td>2</td> 
   <td>gd</td> 
   <td>广东</td> 
   <td>2013/6/19 9:52:10</td> 
   <td>&nbsp;数据库无此记录！changed else changed&nbsp;&nbsp;</td> 
  </tr> 
  <tr> 
   <td>502</td> 
   <td>sq</td> 
   <td>3</td> 
   <td>gx</td> 
   <td>广西</td> 
   <td>2013/5/14 17:54:03</td> 
   <td>测试描述22</td> 
  </tr> 
  <tr> 
   <td>503</td> 
   <td>sq</td> 
   <td>4</td> 
   <td>yn</td> 
   <td>云南</td> 
   <td>2013/6/19 8:03:02</td> 
   <td>测试描述33</td> 
  </tr> 
  <tr> 
   <td>504</td> 
   <td>sq</td> 
   <td>5</td> 
   <td>gz</td> 
   <td>贵州</td> 
   <td>2013/6/19 9:52:10</td> 
   <td>测试描述11</td> 
  </tr> 
  <tr> 
   <td>505</td> 
   <td>sq</td> 
   <td>6</td> 
   <td>hn</td> 
   <td>海南</td> 
   <td>2013/6/19 9:52:10</td> 
   <td>测试描述5555</td> 
  </tr> 
  <tr> 
   <td>505</td> 
   <td>sq</td> 
   <td>7</td> 
   <td>hn</td> 
   <td>海南</td> 
   <td>2013/6/19 9:52:10</td> 
   <td>测试描述6666666</td> 
  </tr> 
 </tbody> 
</table>

[Link 1]: http://baike.baidu.com/view/1236003.htm
[Link 2]: http://baike.baidu.com/view/176618.htm
[Center]: /images/20220111/0d6fa15ff6e544fab611c703a1583be4.png
[Center 1]: /images/20220111/d8db8ea24e914c15a4c8393c1038d38f.png