oracle 存储过程

create or replace procedure PRO_ME_TEST(parm_name string) as
  parm_unit_start string(2) := '09';
begin
  if parm_name = 900 then
    begin
      parm_unit_start := '90';
    end;
  end if ;
  insert into jhvgate_parm_dict(parameter_name, unit) values(parm_name, parm_unit_start);

end PRO_ME_TEST;

 

• 语句块定义：
  decalre   

1. -- 变量声明   
2. var1 number(2);                -- 仅声明   
3. var2 char(2) := '11';          -- 在声明的同时初始化   
4.   
5. begin  
6.         -- 语句   
7. end; -- 语句块结束  

 if 语句

• Sql代码  

1. if a = 1 or b = 2 then  
2.   
3. elsif c = 3 then  
4.   
5. else  
6.   
7. end if;  

case 语句

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

Sql代码   

1. declare  
2. num number(10) := 1;   
3. begin  
4.     case    
5.         when num = 0 then dbms_output.put_line( 'zero');   
6.         when num = 1 then dbms_output.put_line( 'one');   
7.         else  dbms_output.put_line( 'default');   
8.     end case;   
9.        
10.     case num   
11.         when 0 then dbms_output.put_line( 'zero');   
12.         when 1 then dbms_output.put_line( 'one');   
13.         else  dbms_output.put_line( 'default');   
14.     end case;   
15. end;  

 for 循环

for循环主要有两个用处。

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

Sql代码   

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

2、遍历隐式游标

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

Sql代码   

1. for currow in (   
2.    select t.col1, t.col2   
3.    from tableName t   
4.    where ...   
5. ) loop   
6.     if currow.col1 = 0 then  
7.        return;    -- 中止sp，返回   
8.    end if;   
9. end loop;  

while 循环

Sql代码   

1. isok := 9;   
2. while isok >= 0 loop   
3.       isok := isok - 1;   
4.         
5.       if isok = 8 then  
6.          continue;                -- 与编程语言的 continue 语义一样，跳过当前循环的剩余语句，回到循环开始   
7.       end if;   
8.         
9.       if isok = 4 then  
10.          exit;                    -- 与编程语言的 break 语义一样，跳出循环   
11.       end if;   
12.   
13.       dbms_output.put_line('isok:' || isok);   
14. end loop;   
15.   
16. dbms_output.put_line('outside while loop .');  

• 存储过程定义

Sql代码   

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

函数定义

Sql代码   

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

 存储过程与函数异同

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

• 游标

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

Sql代码   

1. for currow in (   
2.    select t.col1, t.col2   
3.    from tableName t   
4.    where ...   
5. ) loop   
6.     if currow.col1 = 0 then  
7.        return;    -- 中止sp，返回   
8.    end if;   
9. end loop;  

 

显式游标

Sql代码   

1. declare  
2. isok integer;   
3. v_event_id number(10);   
4. v_isagain number(2);   
5. v_rate number(2);   
6.   
7. v_sender char(11) := '13800138000';   
8.   
9. cursor cursorVar is select event_id, isagain, rate from call_event where sender = v_sender; -- 声明游标   
10.   
11.   
12. begin  
13.     open cursorVar;    -- 打开游标   
14.     loop   
15.          fetch cursorVar into v_event_id, v_isagain, v_rate;       -- 取值   
16.          exit when cursorVar%notfound;                             --当没有记录时退出循环   
17.          dbms_output.put_line(v_event_id || ', ' || v_isagain || ', ' || v_rate);   
18.     end loop;   
19.       
20.     close cursorVar;   -- 关闭游标   
21.       
22.     --游标的属性有：%FOUND,%NOTFOUNRD,%ISOPEN,%ROWCOUNT;    
23.     --%FOUND:已检索到记录时，返回true    
24.     --%NOTFOUNRD：检索不到记录时，返回true    
25.     --%ISOPEN:游标已打开时返回true    
26.     --%ROWCOUNT:代表检索的记录数，从1开始    
27. end;  

 带参数游标

Sql代码

 

declare  

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