转自:http://wen866595.iteye.com/blog/1733887
差不多一年没写过存储过程,最近要写,发现基本忘了,google一番之后,觉得很有必要把基础的东西写下来备忘。
语句块定义:
- decalre
- -- 变量声明
- var1 number(2); -- 仅声明
- var2 char(2) := '11'; -- 在声明的同时初始化
- begin
- -- 语句
- end; -- 语句块结束
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;
if a = 1 or b = 2 then
elsif c = 3 then
else
end if;
- 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;
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;
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 .');
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 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;
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语句里;而存储过程不用,可以独立调用。
- 游标
隐式游标
隐式游标的好处是不需要手动关闭,方便
- 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;
显式游标
- 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 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;