oracle 存储过程 基础

差不多一年没写过存储过程，最近要写，发现基本忘了，google一番之后，觉得很有必要把基础的东西写下来备忘。

• 语句块定义：

• decalre  
  -- 变量声明  
  var1 number(2);                -- 仅声明  
  var2 char(2) := '11';          -- 在声明的同时初始化  
    
  begin  
          -- 语句  
  end; -- 语句块结束

• if 语句

• if a = 1 or b = 2 then  
    
  elsif c = 3 then  
    
  else  
    
  end if;  

• case 语句

　　　case语句如果作为分支控制语句，最后结束语句是end case，如果是作为select语句里的控制语句则只需要end。

• declare  
  num number(10) := 1;  
  begin  
      case   
          when num = 0 then dbms_output.put_line( 'zero');  
          when num = 1 then dbms_output.put_line( 'one');  
          else  dbms_output.put_line( 'default');  
      end case;  
        
      case num  
          when 0 then dbms_output.put_line( 'zero');  
          when 1 then dbms_output.put_line( 'one');  
          else  dbms_output.put_line( 'default');  
      end case;  
  end;  

• for循环

　　  for循环主要有两个用处。

　　  1、 循环一个范围
　　  格式：for i in [start .. end] loop ... end loop;

• for i in 0..9 loop  
      dbms_output.put_line('i:' || i);  
  end loop;  

  2、遍历隐式游标
  隐式游标的好处是不需要手动关闭，方便

• for currow in (  
     select t.col1, t.col2  
     from tableName t  
     where ...  
  ) loop  
      if currow.col1 = 0 then  
         return;    -- 中止sp，返回  
     end if;  
  end loop;  

• while 循环

• isok := 9;  
  while isok >= 0 loop  
        isok := isok - 1;  
         
        if isok = 8 then  
           continue;                -- 与编程语言的 continue 语义一样，跳过当前循环的剩余语句，回到循环开始  
        end if;  
         
        if isok = 4 then  
           exit;                    -- 与编程语言的 break 语义一样，跳出循环  
        end if;  
    
        dbms_output.put_line('isok:' || isok);  
  end loop;  
    
  dbms_output.put_line('outside while loop .');  

• 存储过程定义

• create or replace procedure sp_name (  
          -- 入参、出参列表， 逗号分隔。  
          uid in varchar2,                          -- 不能带长度信息  
          startDate in date,                        -- 第二个输入参数  
          defaultVar in varchar2 default "",        -- 默认参数，如果不传，要注意参数的顺序  
          isok out number,                          -- 输出参数  
          result out varchar2                       -- 第二个输出参数  
  )  
  as  
  -- 变量声明，每个声明用分号结束。可以在声明的同时初始化  
  var1 varchar2(11);  
  var2 number(2) := 123;  
    
  begin  
          -- 字符串拼接用 ||  
          dbms_output.put_line('isok:' || 'abc');  
           
          -- 调用其他存储过程  
          sub_sp_name(param1, prarm2, outParam1, outParam2);  
    
  end;        -- 存储过程结束  

• 函数定义

• create or replace function func  (  
          -- 入参、出参列表， 逗号分隔。  
          uid in varchar2,                          -- 不能带长度信息  
          startDate in date,                        -- 第二个输入参数  
          defaultVar in varchar2 default "",        -- 默认参数，如果不传，要注意参数的顺序  
          isok out number,                          -- 输出参数  
          result out varchar2                       -- 第二个输出参数  
  )  
  return number      -- 定义返回类型  
  as  
  -- 变量声明，每个声明用分号结束。可以在声明的同时初始化  
  var1 varchar2(11);  
  var2 number(2) := 123;  
    
  begin  
          -- 字符串拼接用 ||  
          dbms_output.put_line('isok:' || 'abc');  
           
    
          return ret_val;  
  end;  

• 存储过程与函数异同

　　1、两者定义类似，都可以带输入输出参数。
　　2、函数有返回值，存储过程没有。
　　3、函数的调用要在select语句里；而存储过程不用，可以独立调用。

• 游标

　　隐式游标 

　　隐式游标的好处是不需要手动关闭，方便

• for currow in (  
     select t.col1, t.col2  
     from tableName t  
     where ...  
  ) loop  
      if currow.col1 = 0 then  
         return;    -- 中止sp，返回  
     end if;  
  end loop;

  显式游标

• declare  
  isok integer;  
  v_event_id number(10);  
  v_isagain number(2);  
  v_rate number(2);  
    
  v_sender char(11) := '13800138000';  
    
  cursor cursorVar is select event_id, isagain, rate from call_event where sender = v_sender; -- 声明游标  
    
    
  begin  
      open cursorVar;    -- 打开游标  
      loop  
           fetch cursorVar into v_event_id, v_isagain, v_rate;       -- 取值  
           exit when cursorVar%notfound;                             --当没有记录时退出循环  
           dbms_output.put_line(v_event_id || ', ' || v_isagain || ', ' || v_rate);  
      end loop;  
       
      close cursorVar;   -- 关闭游标  
       
      --游标的属性有：%FOUND,%NOTFOUNRD,%ISOPEN,%ROWCOUNT;   
      --%FOUND:已检索到记录时，返回true   
      --%NOTFOUNRD：检索不到记录时，返回true   
      --%ISOPEN:游标已打开时返回true   
      --%ROWCOUNT:代表检索的记录数，从1开始   
  end;  

  带参数游标

• declare  
  isok integer;  
  v_event_id number(10);  
  v_isagain number(2);  
  v_rate number(2);  
    
  v_sender char(11) := '13800138000';  
    
  cursor cursorVar(p_sender varchar2) is select event_id, isagain, rate from call_event where sender = p_sender; -- 声明游标  
    
  begin  
      open cursorVar(v_sender);    -- 打开游标，在括号里传参。  
      loop  
           fetch cursorVar into v_event_id, v_isagain, v_rate;       -- 取值  
           exit when cursorVar%notfound;                             --当没有记录时退出循环  
           dbms_output.put_line(v_event_id || ', ' || v_isagain || ', ' || v_rate);  
      end loop;  
       
      close cursorVar;   -- 关闭游标  
  end;  

  本文转自：http://wen866595.iteye.com/blog/1733887