首页 > 代码库 > oracle 存储过程小总结
oracle 存储过程小总结
1 创造存储过程
Create or procedure My_procedure( param1,param2) is
Begin
.
.
.
End
2 判断语句
If x>0 then
Begin
.
.
.
End
End if
3 for 循环
For …in… Loop
.
.
.
End Loop
4 循环遍历游标
Create or replace procedure My_procedure() as Cursor cursor is select name from student;name Varchar(20);
Begin
For name in cursor LOOP
Begin
.
.
.
End
End Loop
End
5 循环遍历数组
Create or replace procedure My_procedure(varArray in My_Package.TestArray) as
I number
Begin
For I in varArray.count Loop
Begin
.
.
.
End
End Loop
End
6 while 循环
While(条件语句) Loop
Begin
.
.
.
End
End Loop
Eg:
Create or replace procedure My_procedure() as
I number
Begin
While(i<10) Loop
Begin
I:=i+1;
End;
End Loop
End
7 oracle 数组的使用
(1) 使用oracle 自带的数组类型
X array //使用的时候进行初始化
Eg：
Create or replace procedure My_procedure(y out array) as
X array;
Begin
X:=new array();
y:=x;
end
(2) 使用自定义的数组类型(自定义数组类型的时候，建议通过创建Package的方式实现，以便于管理)
Eg：
Create or replace package My_package is
Public type declarations type info is record( name varchar(20), y number);
Type TestArray is table of info index by binary_integer;
//如果不指定index by binary_integer 就要 varArray My_package.TestArray;
varArray:=new My_package.TestArray();
8 oracle 游标的使用
游标的使用是非常有用的额，用于遍历临时表中的查询结果。其相关的方法和属性也是很多
(1)
Create or replace procedure My_procedure () is cursor_1 Cursor is select stu_name form student ;varName varchar(20);
Cursor_2 cursor;
Begin
Select stu_name into cursor_2 from student;
For varName in cursor_2 Loop
Begin
.
.
.
End
End Loop
End
(3) Sys_refcursor 型游标，该游标是Oracle以预定义的游标，可以作传出参数进行传递
Create or replace procedure My_procedure(rsCursor out Sys_refcursor) is
Cursor_1 Sys_refcursor;
Name varchar(20);
Begin
Open cursor_1 for select stu_name from student //sys_refcursor 只能通过open 方法打开和赋值
Loop
Fetch cursor_1 into name //sys_refcursor 只能通过fetch into 来打开和遍历
Exit when
Cursor_1%notfound //notfound 未找到记录信息 found找到记录信息 rowcount 当前游标所指向的行的位置
.
.
.
End Loop
rsCursor:=cursor_1
End
友情提示
' 单引号
Eg：
实例
下面写一个简单的例子来对oracle 语句的用法做一个简单的应用
现假设有两张表
一张是学生成绩表（student）
字段为：
stu_id ,math, article, language,music, sport, total,average,step
另一张是学生的课外成绩表（out_school）,字段为
Stu_id ,practice,comment
现通过存储过程自动计算出每位学生的总成绩和平均成绩，同时，如果学生在课外课程中获得的评分为“A” ，就在总成绩上面加20分
Create or replace procedure auto_Compute(step in number) as
rsCursor Sys_refcursor;
comentArray myPackage.myArray;
math number;
article number;
language number;
music number;
sport number;
total number;
average number;
stu_id varchar(30);
record myPackage.stdInfo;
I number;
Begin
I;=1;
Get_comment(commentArray);
Open rsCursor for select stu_id ,math, article,language, music,sport from student t where t.step=step;
Loop
Fetch rsCursor into stu_id,math,article,language,music,sport ;//解析rscursor
Exit when rsCursor%notfound;
Total:=math+article+language+music+sport;
For I in commentArray.count Loop
Record:=commentArray(i);
If(stu_id=record.stu.id) then
Begin
If record.comment=’A’ then
Begin
Total:=total+20;
End;
End if
End
Endif
End Loop
Average:total/5;
Update student t set t.total=total and t.average=average where t.stu_id=stu_id;
End Loop;
End;
获得学生的评论信息放入commentArray 数组
Create or replace procedure get_comment(commentArray out myPackage.myArray) as
rsCursor Sys_refCursor;
record myPackage.stdInfo;
std_id varchar(30)
comment varchar(1);
I number;
Begin
Open rscursor for select std_id ,comment from out_school
I:=1;//定义索引
Loop
Fetch rscursor into std_id,comment;
Exit when rscursor%notfound
Record.stu_id=stu_id;
Record.comment=comment;
commentArray(i)=record;
i:=i+1;
end Loop
end;
定义数组类型myArray和评论信息类型stdInfo
Create or replace package myPackage as
Begin
Type stdInfo is record(stu_id varchar(30),comment varchar(1)));
Type myArray is table of stdInfo index by binary_integer;
End ;
oracle 存储过程小总结