存储过程基础

觉得很有必要把基础的东西写下来备忘。

 

  • 语句块定义：

Sql代码  

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

 

  • if 语句

1
2
3
4
5
Sql代码  
if a = 1 or b = 2 then
elsif c = 3 then
else
end if;

 

  • case 语句

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

Sql代码  

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
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
2
3
4
5
6
7
8
9
循环一个范围
格式：for i in [start .. end] loop ... end loop;
Sql代码  
for i in 0..9 loop   
    dbms_output.put_line('i:' || i);   
end loop;  
  for i in 0..9 loop
      dbms_output.put_line('i:' || i);
  end loop;

 


2、遍历隐式游标

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

Sql代码  

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
for currow in (   
   select t.col1, t.col2   
   from tableName t   
   where ...   
) loop   
    if currow.col1 = 0 then  
       return;    -- 中止sp，返回   
   end if;   
end loop;  
for currow in (
   select t.col1, t.col2
   from tableName t
   where ...
) loop
    if currow.col1 = 0 then
       return;    -- 中止sp，返回
   end if;
end loop;
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
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 .');  
    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 .');

 

  • 存储过程定义

Sql代码    

oracle 存储过程 基础
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
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;        -- 存储过程结束

 

  • 函数定义

Sql代码    

oracle 存储过程 基础
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
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语句里；而存储过程不用，可以独立调用。

  • 游标

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

Sql代码   

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

 

显式游标

Sql代码    

oracle 存储过程 基础
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
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;

 

带参数游标

Sql代码  

 

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
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;
posted @ 2015-06-10 15:27  calvin_Dai  阅读(193)  评论(0)  编辑  收藏  举报