oracle 存储过程 基础

xxtgzznigg

　　差不多一年没写过存储过程，最近要写，发现基本忘了，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;