oracle 存储过程 基础

语句块定义：

create OR REPLACE PROCEDURE 存储过程名 (
	-- 变量声明   
	var1 number(2),                -- 仅声明   
	var2 char(2) := '11'          -- 在声明的同时初始化   
) 
begin  
        -- 语句   
end;    -- 语句块结束  

 

if 语句：

if a = 1 or b = 2 then  
  
els if 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循环

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;  

 --------------------------------------------------------函数案例----------------------------------------------------------------------------------------------------------------------------------------------------------

create or replace function func_is_first_day   
------------------------------------------------------------------------------   
---功能描述：判断今天是否是该月的第一天（1号）                             ---   
---返回参数：1代表是，0代表不是                                            ---                                        ---   
------------------------------------------------------------------------------   
return number    
is  
  v_result number := 0;   
     
  v_day varchar2(2);   
begin  
  select to_char(sysdate,'dd') into v_day from dual;   
  if v_day = '01' then  
    v_result := 1;   
  else    
    v_result := 0;   
  end if;   
     
  return(v_result);   
end func_is_first_day; 

 

--------------------------------------------------------存储过程案例------------------------------------------------------------------------------------------------------------------------------------------------------------ 

create or replace procedure proc_delete_day_fee_info   
------------------------------------------------------------------------------   
---功能描述： 只保留一个月得数据（8月的日账单10月份删除）                                                         ---   
------------------------------------------------------------------------------   
is
type base_cursor is ref cursor;   
cf base_cursor;   
v_fee_id number(18);   
begin  
  begin 
  --打开cf游标      
  open cf for select a.fee_id from user_fee_info a ,day_fee_info b where a.fee_id = b.fee_id and to_char(b.start_time,'yyyy-mm') = to_char(add_months(sysdate,0),'yyyy-mm');       
	fetch cf into v_fee_id;--遍历游标
	--开始遍历该游标
	while cf%found loop   
	   delete from user_fee_info where fee_id = v_fee_id;   
	   delete from day_fee_info where fee_id = v_fee_id;   
	   commit;          
	   fetch cf into v_fee_id;   
	end loop;        
  close cf;--关闭游标          
  exception when others then  
    rollback; --回滚数据 
    dbms_output.put_line('出现了异常!'); --
    return;  
  end;         
end proc_delete_day_fee_info;