oracle 預存程序小總結

標籤：io   ar   os   使用   sp   for   on   art   bs   

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 ;

 

oracle 預存程序小總結