Oracle存储过程总结

个人技术积累Oracle存储过程总结
2012年10月
本文档状态记录
目录
第1章存储过程创建 (4)
1.1. 语法 (4)
1.2. 样例： (5)
第2章变量赋值 (5)
2.1. 语法 (5)
2.2. 样例 (5)
第3章判断语句 (5)
3.1. 语法 (5)
3.2. 样例 (6)
第4章 For循环 (6)
4.1. 语法 (6)
4.2. 样例 (7)
4.2.1. 循环遍历游标 (7)
4.2.2. 循环遍历数组 (8)
第5章 While 循环 (8)
5.1. 语法 (8)
5.2. 样例 (9)
第6章数组 (9)
6.1. 语法 (9)
6.2. 样例 (10)
6.2.1. 使用Oracle自带的数组类型 (10)
6.2.2.自定义的数组类型 (10)
第7章游标 (11)
7.1. 语法 (11)
7.1.1. Cursor型游标(不能用于参数传递) (11)
7.1.2. SYS_REFCURSOR型游标 (11)
第8章附录1-注意事项 (12)
第9章附录2-案例 (13)
9.1. 示例脚本 (13)
第1章存储过程创建1.1.语法
1.2.样例：
第2章变量赋值
2.1.语法
变量名 := 值;
2.2.样例
第3章判断语句
3.1.语法
if 比较式 then begin end; end if;
第4章For循环
4.1.语法
For ... in ... LOOP --执行语句end LOOP;
4.2.1.循环遍历游标
4.2.2.循环遍历数组
第5章While 循环5.1.语法
第6章数组
概念说明：Oracle 中本是没有数组的概念的，数组其实就是一张表(Table),每个数组元素就是表中的一个记录。

使用数组时，用户可以使用Oracle已经定义好的数组类型，或可根据自己的需要定义数组类型。

6.1.语法
x array; --使用时需要进行初始化
6.2.1.使用Oracle自带的数组类型
6.2.2. 自定义的数组类型
第7章游标
7.1.语法
7.1.1.Cursor型游标(不能用于参数传递)
7.1.2.SYS_REFCURSOR型游标
该游标是Oracle以预先定义的游标，可作出参数进行传递。

第8章附录1-注意事项
1.存储过程参数不带取值范围，in表示传入，out表示输出类型可
以使用任意Oracle中的合法类型。

2.变量带取值范围，后面接分号。

3.在判断语句前最好先用count（*）函数判断是否存在该条操作记
录。

4.用select 。

into。

给变量赋值。

5.在代码中抛异常用 raise+异常名。

第9章附录2-案例
现假设存在两张表,一张是学生成绩表(studnet),字段为：stdId、
math、article、language、music、sport、total、average、step 一张是学生课外成绩表(out_school),字段为:stdId、parctice、
comment通过存储过程自动计算出每位学生的总成绩和平均成绩,同
时,如果学生在课外课程中获得的评价为A,就在总成绩上加20分。

9.1.示例脚本
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 mentArray.count LOOP
record := commentArray(i);
if stdId = record.stdId then
begin
if ment = '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;
ment := 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;。