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

友情提示

&apos; 单引号

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 ;