zz Oracle預存程序總結

1、建立預存程序

create or replace procedure test(var_name_1 in type,var_name_2 out type) as

--聲明變數(變數名 變數類型)

begin

--預存程序的執行體

end test;

列印出輸入的時間資訊

E.g:

create or replace procedure test(workDate in Date) is

begin

dbms_output.putline('The input date is:'||to_date(workDate,'yyyy-mm-dd'));

end test;

2、變數賦值

變數名 := 值;

E.g：

create or replace procedure test(workDate in Date) is

x number(4,2);

begin

x := 1;

end test;

3、判斷語句:

if 比較式 then begin end; end if;

E.g

create or replace procedure test(x in number) is

begin

        if x >0 then

         begin

        x := 0 - x;

        end;

    end if;

    if x = 0 then

       begin

        x: = 1;

    end;

    end if;

end test;

4、For 迴圈

For ... in ... LOOP

--執行語句

end LOOP;

(1)迴圈遍曆遊標

create or replace procedure test() as

Cursor cursor is select name from student; name varchar(20);

begin

for name in cursor LOOP

begin

dbms_output.putline(name); 

end;

end LOOP;

end test;

(2)迴圈遍曆數組

create or replace procedure test(varArray in myPackage.TestArray) as

--(輸入參數varArray 是自訂的數群組類型，定義方式見標題6)

i number;

begin

i := 1;  --預存程序數組是起始位置是從1開始的，與java、C、C++等語言不同。因為在Oracle中本是沒有數組的概念的，數組其實就是一張

--表(Table),每個數組元素就是表中的一個記錄，所以遍曆數組時就相當於從表中的第一條記錄開始遍曆

for i in 1..varArray.count LOOP     

dbms_output.putline('The No.'|| i || 'record in varArray is:'||varArray(i));  

end LOOP;

end test;

5、While 迴圈

while 條件陳述式 LOOP

begin

end;

end LOOP;

E.g

create or replace procedure test(i in number) as

begin

while i < 10 LOOP

begin   

i:= i + 1;

end;

end LOOP;

end test;

6、數組

首先明確一個概念：Oracle中本是沒有數組的概念的，數組其實就是一張表(Table),每個數組元素就是表中的一個記錄。

使用數組時，使用者可以使用Oracle已經定義好的數群組類型，或可根據自己的需要定義數群組類型。

(1)使用Oracle內建的數群組類型

x array; --使用時需要需要進行初始化

e.g:

create or replace procedure test(y out array) is

x array; 

begin

x := new array();

y := x;

end test;

(2)自訂的數群組類型 (自訂資料類型時，建議通過建立Package的方式實現，以便於管理)

E.g (自訂使用參見標題4.2) 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 binary_integer;   --此處聲明了一個TestArray的類型資料，其實其為一張儲存Info資料類型的Table而已，及TestArray 就是一張表，有兩個欄位，一個是

name，一個是y。需要注意的是此處使用了Index by binary_integer 編製該Table的索引項目，也可以不寫，直接寫成：type TestArray is

table of info，如果不寫的話使用數組時就需要進行初始化：varArray myPackage.TestArray; varArray := new myPackage.TestArray();

end TestArray;

7.遊標的使用

Oracle中Cursor是非常有用的，用於遍曆暫存資料表中的查詢結果。其相關方法和屬性也很多，現僅就常用的用法做一二介紹：

(1)Cursor型遊標(不能用於參數傳遞)

create or replace procedure test() is  

cusor_1 Cursor is select std_name from student where  ...;  --Cursor的使用方式1   cursor_2 Cursor;

begin

select class_name into cursor_2 from class where ...;  --Cursor的使用方式2

可使用For x in cursor LOOP .... end LOOP; 來實現對Cursor的遍曆

end test;

(2)SYS_REFCURSOR型遊標，該遊標是Oracle以預先定義的遊標，可作出參數進行傳遞

create or replace procedure test(rsCursor out SYS_REFCURSOR) is

cursor SYS_REFCURSOR; name varhcar(20);

begin

OPEN cursor FOR select name from student where ... --SYS_REFCURSOR只能通過OPEN方法來開啟和賦值

LOOP

fetch cursor into name   --SYS_REFCURSOR只能通過fetch into來開啟和遍曆 exit when cursor%NOTFOUND;              --SYS_REFCURSOR中可使用三個狀態屬性：                                         ---%NOTFOUND(未找到記錄資訊) %FOUND(找到記錄資訊)                                         ---%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 = &apos;A&apos; 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;

 

7.字串處理

 

字元函數——返回字元值

這些函數全都接收的是字元族類型的參數(CHR除外)並且返回字元值.

除了特別說明的之外,這些函數大部分返回VARCHAR2類型的數值.

字元函數的傳回型別所受的限制和基本資料庫類型所受的限制是相同的。

字元型變數儲存的最大值：

VARCHAR2數值被限制為2000字元(ORACLE 8中為4000字元)

CHAR數值被限制為255字元(在ORACLE8中是2000)

long類型為2GB

Clob類型為4GB

1、CHR

文法:  chr(x)

功能：返回在資料庫字元集中與X擁有等價數值的字元。CHR和ASCII是一對反函數。經過CHR轉換後的字元再經過ASCII轉換又得到了原來的字

符。

使用位置：過程性語句和SQL語句。

2、CONCAT

文法： CONCAT（string1,string2）

功能：返回string1，並且在後面串連string2。

使用位置：過程性語句和SQL語句。

3、INITCAP

文法：INITCAP（string）

功能：返回字串的每個單詞的第一個字母大寫而單詞中的其他字母小寫string。單詞是用.空格或給字母數字字元進行分隔。不是字母的

字元不變動。

使用位置：過程性語句和SQL語句。

4、LTRIM

文法：LTRIM（string1,string2）

功能：返回刪除從左邊算起出現在string2中的字元的string1。String2被預設設定為單個的空格。資料庫將掃描string1，從最左邊開始。當

遇到不在string2中的第一個字元，結果就被返回了。LTRIM的行為方式與RTRIM很相似。

使用位置：過程性語句和SQL語句。

5、NLS_INITCAP

文法：NLS_INITCAP（string[,nlsparams]）

功能：返回字串每個單詞第一個字母大寫而單詞中的其他字母小寫string，nlsparams

指定了不同於該會話預設值的不同排序序列。如果不指定參數，則功能和INITCAP相同。Nlsparams可以使用的形式是：

‘NLS_SORT=sort’

這裡sort制訂了一個語言排序序列。

使用位置：過程性語句和SQL語句。

6、NLS_LOWER

文法：NLS_LOWER（string[,nlsparams]）

功能：返回字串中的所有字母都是小寫形式的string。不是字母的字元不變。

      Nlsparams參數的形式與用途和NLS_INITCAP中的nlsparams參數是相同的。如果nlsparams沒有被包含，那麼NLS_LOWER所作的處理和

LOWER相同。

使用位置；過程性語句和SQL語句。

7、NLS_UPPER

文法：nls_upper（string[,nlsparams]）

功能：返回字串中的所有字母都是大寫的形式的string。不是字母的字元不變。nlsparams參數的形式與用途和NLS_INITCAP中的相同。如果

沒有設定參數，則NLS_UPPER功能和UPPER相同。

使用位置：過程性語句和SQL語句。

8、REPLACE

文法：REPLACE（string，search_str[,replace_str]）

功能：把string中的所有的子字串search_str用可選的replace_str替換，如果沒有指定replace_str，所有的string中的子字串

search_str都將被刪除。REPLACE是TRANSLATE所提供的功能的一個子集。

使用位置：過程性語句和SQL語句。

9、RPAD

文法：RPAD（string1,x[,string2]）

功能：返回在X字元長度的位置上插入一個string2中的字元的string1。如果string2的長度要比X字元少，就按照需要進行複製。如果string2

多於X字元，則僅string1前面的X各字元被使用。如果沒有指定string2，那麼使用空格進行填充。X是使用顯示長度可以比字串的實際長度

要長。RPAD的行為方式與LPAD很相似，除了它是在右邊而不是在左邊進行填充。

使用位置：過程性語句和SQL語句。

10、RTRIM
文法:  RTRIM（string1,[,string2]）

功能:  返回刪除從右邊算起出現在string1中出現的字元string2. string2被預設設定為單個的空格.資料庫將掃描string1,從右邊開始.當遇

到不在string2中的第一個字元,結果就被返回了RTRIM的行為方式與LTRIM很相似.

使用位置：過程性語句和SQL語句。

11、SOUNDEX

文法:  SOUNDEX（string）

功能:  返回string的聲音表示形式.這對於比較兩個拼字不同但是發音類似的單詞而言很有協助.

使用位置：過程性語句和SQL語句。

12、SUBSTR

文法:  SUBSTR（string,a[,b]）

功能:  返回從字母為值a開始b個字元長的string的一個子字串.如果a是0,那麼它就被認為從第一個字元開始.如果是正數,返回字元是從左

邊向右邊進行計算的.如果b是負數,那麼返回的字元是從string的末尾開始從右向左進行計算的.如果b不存在,那麼它將預設的設定為整個字元

串.如果b小於1,那麼將返回NULL.如果a或b使用了浮點數,那麼該數值將在處理進行以前首先被卻為一個整數.

使用位置：過程性語句和SQL語句。

13、TRANSLATE

   文法:  TRANSLATE(string,from_str,to_str)

   功能:  返回將所出現的from_str中的每個字元替換為to_str中的相應字元以後的string. TRANSLATE是REPLACE所提供的功能的一個超集.

如果from_str比to_str長,那麼在from_str中而不在to_str中而外的字元將從string中被刪除,因為它們沒有相應的替換字元. to_str不可為空

.Oracle把Null 字元串認為是NULL,並且如果TRANSLATE中的任何參數為NULL,那麼結果也是NULL.

使用位置：過程性語句和SQL語句。

14、UPPER

文法: UPPER（string）

功能: 返回大寫的string.不是字母的字元不變.如果string是CHAR資料類型的,那麼結果也是CHAR類型的.如果string是VARCHAR2類型的,那麼

結果也是VARCHAR2類型的.

使用位置: 過程性語句和SQL語句。

字元函數——返回數字

這些函數接受字元參數回數字結果.參數可以是CHAR或者是VARCHAR2類型的.儘管實際下許多結果都是整數值,但是返回結果都是簡單的NUMBER

類型的,沒有定義任何的精度或刻度範圍.

16、ASCII

文法:  ASCII（string）

功能: 資料庫字元集返回string的第一個位元組的十進位表示.請注意該函數仍然稱作為ASCII.儘管許多字元集不是7位ASCII.CHR和ASCII是互為

相反的函數.CHR得到給定字元編碼的響應字元. ASCII得到給定字元的字元編碼.

使用位置: 過程性語句和SQL語句。

17、INSTR

文法:  INSTR（string1, string2[a,b]）

功能:  得到在string1中包含string2的位置. string1時從左邊開始檢查的,開始的位置為a,如果a是一個負數,那麼string1是從右邊開始進行

掃描的.第b次出現的位置將被返回. a和b都預設設定為1,這將會返回在string1中第一次出現string2的位置.如果string2在a和b的規定下沒有

找到,那麼返回0.位置的計算是相對於string1的開始位置的,不管a和b的取值是多少.

使用位置: 過程性語句和SQL語句。

18、INSTRB

文法:  INSTRB（string1, string2[a,[b]]）

功能:  和INSTR相同,只是操作的對參數字元使用的位置的是位元組.

使用位置: 過程性語句和SQL語句。

19、LENGTH

文法:  LENGTH（string）

功能:  返回string的位元組單位的長度.CHAR數值是填充空格類型的,如果string由資料類型CHAR,它的結尾的空格都被計算到字串長度中間.

如果string是NULL,返回結果是NULL,而不是0.

使用位置: 過程性語句和SQL語句。

20、LENGTHB

文法:  LENGTHB（string）

功能:  返回以位元組為單位的string的長度.對於單一位元組字元集LENGTHB和LENGTH是一樣的.

使用位置: 過程性語句和SQL語句。

21、NLSSORT

文法: NLSSORT（string[,nlsparams]）

功能: 得到用於排序string的字串位元組.所有的數值都被轉換為位元組字串,這樣在不同資料庫之間就保持了一致性. Nlsparams的作用和

NLS_INITCAP中的相同.如果忽略參數,會話使用預設排序.

使用位置: 過程性語句和SQL語句。

 

 

zz from：http://blog.csdn.net/squirrelRao/archive/2008/07/15/2654748.aspx