oracle存储过程
文章转载于: 明将军博客
存储过程语法结构
create or replace procedure test() is begin null; end test;
存储过程创建语法：
create or repalce procedure 存储过程名（param1 in type,param2 out type）
as
变量1 类型（值范围）；
变量2 类型（值范围）；
Begin
select count(*) into 变量1 from 表A where 列名=param1;
if(判断条件) then
select 列名 into 变量2 from 表A where列名=param1;
Dbms_output.Put_line(‘打印信息’);
elsif（判断条件）then
Dbms_output.Put_line(‘打印信息’);
else
raise 异常名‘数据异常’；
endif;
exception
when others then
rollback;
End;
注意事项：
1、存储过程参数不带取值范围，in表示传入，out表示输出，类型可以使用任意oracle中的合法类型。
2、 变量带取值范围，后面接分号
3、在判断语句前最好先用count（*）函数判断是否存在该条操作记录
4、用select...into...给变量赋值
5、 在代码中抛异常用 raise+异常名
create or replace procedure 存储过程名（is_ym in char（5）,the_count out number）
as
--定义变量名
vs_msg varchar2(1000); --错误信息变量
vs_ym_beg char(6); --起始月份
vs_ym_end char(6); --终止月份
vs_ym_sn_beg char(6); --同期起始月份
vs_ym_sn_end char(6); --同期终止月份
--定义游标，一个可以遍历的结果集
cursor cur_1 is
select * from ... where ... group by ... order by ...
begin
--用输入参数给变量赋初始值
vs_ym_beg :substr(is_ym,1,6);
vs_ym_end :substr(is_ym,7,6);
vs_ym_sn_beg :to_char(add_month(to_date(is_ym_sn_beg,'yyyymm'),-12),'yyyymm');
vs_ym_sn_end :to_char(add_month(to_date(is_ym_sn_end,'yyyymm'),-12),'yyyymm');
--先删除表中特定的数据
delete from 表名 where ym=is_ym;
--用dbms_output的putline方法打印出结果，其中用到一个系统变量SQL%rowcount
dbms_output.putline('del上月记录数='||SQL%rowcount||'条');
insert into 表名（area_code,ym,cmcode,rmb_amt,usd_amt）select area_code,is_ym,cmcode,sum(rmb_amt)/10000,sum(usd_amt)/10000
from bgd_area_cm_base_t where ym>=vs_ym_beg and ym<=vs_ym_end group by area_code,cmcode;
dbms_output.put_line('ins当月记录数='||SQL%rowcount||'条');
--遍历游标处理后更新到表，遍历游标有多种方法，用户for语句是比较直观的一种
For rec in cur_1 Loop
update 表名 set rmb_amt_sn=rec.rmb_amt_sn,usd_amt_sn=rec.usd_amt_sn where area_code=rec.area_code and cmcode=rec.cmcode and ym=is_ym
End Loop;
COMMIT;
--错误处理部分，others表示除了声明外的任意错误，sqlerrm是系统内置变量保存了当前错误的详细信息
Exception
when others then
vs_msg :='error in xxxxxxx_p('||is_ym||'):'||substr(sqlerrm,1,500);
rollback;
--把当前错误记录到日志表
insert into log_info(proc_name,error_info,op_date)values(xxxxxxx_p,vs_msg,sysdate);
commit;
return;
end;
oracle存储过程语法
1 、判断语句:
if 比较式 then begin end; end if;
create or replace procedure test(x in number) is
begin
if x>0 then
begin
x :=0-x;
end
if x=0 then
begin
x :=1;
end;
endif;
end test;
2 、For 循环
for ... in ... loop
-- 执行语句
end loop;
1.循环遍历游标
create or replace procedure test() as
cursor cur is selec name from student;
name varchar(20);
begin
for name in cur loop
begin
dbms_output.putline(name);
end;
end loop;
end test;
2.循环遍历数组
create or replace procedure test(array in myPackage.testArray) as
--输入参数array是自定义的数组类型
i number;
begin
i :=1;
--存储过程数组起始位置从1开始，与java、C++等语言不通，在oracle中是没有数组概念的，数组就是一张表
--表，每个数组元素就是表中的一个记录，所以遍历数组时就相当于从表的第一条记录开始遍历
for i in 1..array.count loop
dbms_output.putline('the No.'||i||'record in array is'||'array(i)');
end loop;
end test;
3 、While 循环
while 条件语句 LOOP
create or replace procedure test(i in number) as
begin
while i<10 loop
begin
i :=i+1;
end;
end loop;
end test;
4 、数组
首先明确一个概念：Oracle 中本是没有数组的概念的，数组其实就是一张表， 每个数组元素就是表中的一个记录。
使用数组时，用户可以使用Oracle 已经定义好的数据类型，也可根据自己的需要定义数据类型。
1.使用oracle自带的数组类型
creaate or replace procedure test(y out array) is
x array;--x需要初始化
begin
x :=new array();
y :=x;
end test;
2.自定义数据类型
--自定义数据类型时，一般通过创建package的方式实现，以便于管理
create or replace package myPackage is
public type declarations type info is record(name varchar(20),y number);
type testArray is table of info index by bianry_integer;
--此处声明了一个testArray的数据类型，其为一张存储info数据类型的table而已，即testArray就是一张表，有两个值，一个是name，一个是y
--此处使用了index by binary_integer编制该table的索引项，也可不写；若不写，使用数组时就需要进行初始化：
array myPackage.testArray;
array :=new myPackage.testArray();
end testArray;
5. 游标的使用
Oracle 中Cursor 是非常有用的，用于遍历临时表中的查询结果。其相关方法和属性也很多，现仅就常用的用法做简单介绍：
1.cursor型游标（不能用于参数传递）
create or repalce procedure test() is
cursor cur1 is select std_name from student where ...;
begin
select class_name into cur2 from class where...;
for x in cur1
end test;
2.sys_refcursor型游标，该游标是Oracle预先定义的游标，可作出参数进行传递
create or replace procedure test(reCursor out sys_refcursor) is
cursor sys_refcursor;
name varchar(20);
begin
open cursor for select name from from student where ...
--sys_refcursor只能通过open方法来打开和赋值
loop
fetch cursor into name
--sys_refcursor只能通过fetch into 来打开和遍历
exit when cursor%notfound;
--%rowcount(当前游标所指向的行位置)
dbms_output.putline(name);
end loop;
rsCursor :cursor;
end test;
实例
下面写一个简单的例子来对以上所说的存储过程的用法做一个应用：
现假设存在两张表，一张是学生成绩表(studnet) ，字段为：stdId,math,article,language,music,sport,total,average,step 。一张是学生课外成绩表(out_school), 字段为:stdId,parctice,comment
通过存储过程自动计算出每位学生的总成绩和平均成绩，同时，如果学生在课外课程中获得的评价为A ，就在总成绩上加20 分。
create or replace procedure autocomputer(step in number) is rsCursor SYS_REFCURSOR; commentArray myPackage.myArray; math number; article number; language number; music number; sport number; total number; average number; stdId varchar(30); record myPackage.stdInfo; i number; begin i := 1; get_comment(commentArray); -- 调用名为get_comment() 的存储过程获取学生课外评分信息 OPEN rsCursor for select stdId,math,article,language,music,sport from student t where t.step = step; LOOP fetch rsCursor into stdId,math,article,language,music,sport; exit when rsCursor%NOTFOUND; total := math + article + language + music + sport; for i in 1..commentArray.count LOOP record := commentArray(i); if stdId = record.stdId then begin if record.comment = 'A' then begin total := total + 20; go to next; -- 使用go to 跳出for 循环 end; end if; end; end if; end LOOP; <<continue>> average := total / 5; update student t set t.total=total and t.average = average where t.stdId = stdId; end LOOP; end; end autocomputer; -- 取得学生评论信息的存储过程 create or replace procedure get_comment(commentArray out myPackage.myArray) is rs SYS_REFCURSOR ； record myPackage.stdInfo; stdId varchar(30); comment varchar(1); i number; begin open rs for select stdId,comment from out_school i := 1; LOOP fetch rs into stdId,comment; exit when rs%NOTFOUND; record.stdId := stdId; record.comment := comment; recommentArray(i) := record; i:=i + 1; end LOOP; end get_comment; -- 定义数组类型myArray create or replace package myPackage is begin type stdInfo is record(stdId varchar(30),comment varchar(1)); type myArray is table of stdInfo index by binary_integer; end myPackage;
浙公网安备 33010602011771号