Oracle 存储过程 PROCEDURE

147次阅读

共计 2000 个字符，预计需要花费 5 分钟才能阅读完成。

Oracle 存储过程

一组用于完成特定数据库功能的 SQL 语句集，该 SQL 语句集经过编译后存储在数据库系统中。在使用时候，用户通过指定已经定义的存储过程名字并给出相应的存储过程参数来调用并执行它，从而完成一个或一系列的数据库操作。

包含三部分：过程声明，执行过程部分，存储过程异常（可选）。

示例

create or replace procedure sp_
(p_sResult out integer,– 返回值
p_ID in number –ID
)
as
v_id number(16);
cursor v_chac_cr is
select t.money from T001 t where t.sid=p_ID;– 游标
begin
open v_chac_cr();
loop
fetch v_chac_cr into v_id;
exit when v_chac_cr%notfound;
insert into F002(cnt) values (v_id);
end loop;
close v_chac_cr;
commit;
p_sResult:=1;
exception– 例外处理
when others then
p_sResult:=0;
rollback;
end sp_;

项目运用

1. 获取表下一个 ID 值

CREATE OR REPLACE PROCEDURE PNEXTID
(
tablename IN VARCHAR2 ,
idno OUT NUMBER
)
IS
sqlstring VARCHAR2 (500);
BEGIN
sqlstring := ‘SELECT nvl(max(id),0)+1 FROM ‘ ||tablename;
execute immediate sqlstring into idno; — 动态执行
merge into tSequence a using (select idno as ID, tablename as Name from dual) b – 组合成表 merge into 确定表
on (upper(a.Name)=upper(b.Name)) – 表名大写化
when matched then update set a.ID= case when b.ID>a.ID then b.ID else a.ID+ 1 end — update; case when then else end;
when not matched then insert (ID, Name) values (b.ID,b.Name); –insert;
select ID into idno from tSEQUENCE WHERE upper(name) = upper(tablename);
EXCEPTION
WHEN OTHERS THEN
idno := – 1 ;
END pNextID;

2. 返回列表数据 (游标)

CREATE OR REPLACE PROCEDURE SP_CONTENT (
o_result out types.cursorType, – 结果集
ip_pwf in varchar2) – 关联流程
as
/* – 类型声明是游标变量
create or replace package TestPackage is type outlist is ref cursor;
*/
v_sql varchar2(1000);
vn_count numeric(12,0);
vn_num numeric(12,0);
vn_id numeric(12,0);
vs_table varchar2(30);
vn_inst numeric(12,0);
begin
vn_count:=length(ip_pwf);
vn_num:=instr(ip_pwf,’.’);
vn_id:=to_number(substr(ip_pwf,1,vn_num-1));
vs_table:=substr(ip_pwf,vn_num+1,vn_count-vn_num);
begin
v_sql:=’select nvl(max(job_code),0) from ‘||vs_table||’ where company = ‘||vn_id||”;
execute immediate v_sql into vn_inst;
exception
when others then
vn_inst:=0;
end;
open o_result for
select caller,enddate,content from tuser t where class_code < vn_inst;
end SP_CONTENT;