1、PL/SQL語法以及程序編寫介紹,一、PL/SQL介紹 PL/SQL是一種高級數(shù)據(jù)庫程序設(shè)計語言，該語言專門用于在各種環(huán)境下對Oracle數(shù)據(jù)庫進行訪問。 PL/SQL語言實現(xiàn)了將過程結(jié)構(gòu)與Oracle SQL的無縫集成，從而為用戶提供了一種功能強大的結(jié)構(gòu)化程序設(shè)計語言。,二、引入PL/SQL的原因 PL/SQL語言可以將第四代語言的強大功能和靈活性與第三代語言的過程結(jié)構(gòu)的優(yōu)勢融為一體。,三、PL/SQL內(nèi)容 PL/SQL是在SQL語言中擴充了面向過程語言中使用的程序結(jié)構(gòu)，如： 變量和類型（即可以予定義也可以由用戶定義） 控制語句（如IF-THEN-ELSE）和循環(huán) 過程和函數(shù) 對象類型和方法

2、（PL/SQL8.0版本以上）,四、變量的聲明 在PL/SQL中聲明變量與其他語言不太一樣，它采用從右往左的方式聲明 。 C語言聲明變量方式：int v_id; PL/SQL聲明變量方式：v_id number; 五、變量的賦值 不能用”=”應(yīng)該用”:=”, 即形式為 v_id := 5;,六、數(shù)據(jù)類型 PL/SQL主要用于數(shù)據(jù)庫編程，所以其所有數(shù)據(jù)類型跟oracle數(shù)據(jù)庫里的字段類型是一一對應(yīng)的，常用的數(shù)據(jù)類型大體上有數(shù)字型、布爾型、字符型和日期型。 數(shù)字型 用來存儲整型或者浮點型數(shù)值。范圍為E-130 10E125，其聲明方式為：number （precision, scale） 其中（p

3、recision, scale）是可選的，precision表示所有數(shù)字的個數(shù)，scale表示小數(shù)點右邊數(shù)字的個數(shù)。,實際數(shù)據(jù) 定義 在oralce中的數(shù)據(jù),7456123.89 NUMBER 7456123.89 NUMBER(9) 7456123.89 NUMBER(9,2) 7456123.89 NUMBER(9,1) 7456123.89 NUMBER(6) 7456123.89 NUMBER(7,-2) 7456123.89 NUMBER(7,2),7456123.89,7456124,7456123.89,7456123.9,溢出,7456100,溢出,布爾型 布爾型BOOLEAN

4、，用于邏輯判斷，只有TRUE,FALSE兩種值。 字符型 主要說明字符類型的存儲格式。主要包括 char、varchar2和long等幾種類型。 CHAR：用于描述定長的字符型數(shù)據(jù)，長度= 2000 字節(jié)。如果實際值不夠定義的長度，系統(tǒng) 將以空格填充。,v_char char(10):=abcde; Length(v_char)=,10,VARCHAR2：用于描述變長的字符型數(shù)據(jù)，長度= 4000 字節(jié)。它的聲明方式如下VARCHAR2(L)，L為字符串長度，沒有缺省值。,v_char varchar2(10):=abcde; Length(v_char)=,5,NCHAR：來存儲Unicod

5、e字符集的定長字符型數(shù)據(jù)，長度= 1000 字節(jié)。它的聲明方式與CHAR相同。 NVARCHAR2：用來存儲Unicode字符集的變長字符型數(shù)據(jù)，長度= 1000 字節(jié)。它的聲明方式與VARCHAR2相同。 LONG：用來存儲最大長度為2GB的變長字符數(shù)據(jù)。,日期型 DATE：用于存儲日期與時間信息。雖然日期信息也可以用字符型變量或者數(shù)字型變量來表示，但是DATE型變量有它特有的屬性。每一個DATE型的數(shù)據(jù)都存儲了以下信息：年、月、日、小時、分鐘以秒。,TO_DATE函數(shù) TO_DATE函數(shù)可以將一個字符型變量按照程序員的要求轉(zhuǎn)換成日期型變量。,假設(shè)有一DATE類型變量v_date，現(xiàn)在用to

6、_date 函數(shù)將v_date賦值為2006年4月6日,V_date := to_date(2006-04-06,yyyy-mm-dd);,V_date := to_date(2006-04-06 10:10:10,yyyy-mm-dd hh24:mi:ss);,V_date := to_date(20060406,yyyymmdd);,V_date := to_date(2006/04/06,yyyy/mm/dd);,V_date := to_date(20060406101010,yyyymmddhh24miss);,V_date := to_date(2006/04/06 10,10,1

7、0,yyyy/mm/dd hh24,mi,ss);,七、條件邏輯 在PL/SQL中，可以使用命令if,else和elsif來控制可執(zhí)行命令段的命令流。下面列出了可用的條件邏輯命令的格式（循環(huán)條件除外） 格式1： if then elsif then else end if;,格式2： if then if then end if; else end if; 總的說來除了格式上與C略有區(qū)別，其它都 是一樣的,例：判斷通過映射關(guān)系，將郵編信息填入到i_addr_zipcode字段中去 if i_area_id = 1 then i_addr_zipcode := 325200; elsif i_a

8、rea_id =2 then i_addr_zipcode := 325400; elsif i_area_id = 3 then i_addr_zipcode := 325800; elsif i_area_id = 4 then i_addr_zipcode := 325100; elsif i_area_id = 5 then i_addr_zipcode := 325600; elsif i_area_id = 6 then i_addr_zipcode := 325700; elsif i_area_id = 7 then i_addr_zipcode := 325300; elsi

9、f i_area_id = 8 then i_addr_zipcode := 325500; else i_addr_zipcode := 325000; end if;,八、循環(huán) 在一個單獨的PL/SQL代碼塊中可以使用循環(huán)處理多個記錄。PL/SQL支持三種類型的循環(huán)。,簡單循環(huán),一個重復(fù)運動的循環(huán)，直到碰到循環(huán)中的exit或者exit when語句時才結(jié)束循環(huán),WHILE循環(huán),一個重復(fù)運行的循環(huán)，直到滿足條件才退出,FOR循環(huán),一個重復(fù)運行指定遍數(shù)的循環(huán),例：一個簡單循環(huán)舉例 i_variable := 10; i_sum := 0; loop i_sum := i_sum + i_var

10、iable; i_variable := i_variable 1; exit when i_variable0; end loop;,例：用while循環(huán)來完成上例 i_variable := 10; i_sum := 0; while i_variable 0 loop i_sum := i_sum + i_variable; i_variable := i_variable 1; end loop; ,例：用for循環(huán)來完成簡單循環(huán) i_variable := 10; i_sum := 0; for i_variable in 0.10 loop i_sum := i_sum + i_v

11、ariable; i_variable := i_variable 1; end loop; ,九、游標(biāo) 當(dāng)查詢返回結(jié)果超過一行時，就需要一個顯式游標(biāo)，也就是我們常說的游標(biāo)。 常用的游標(biāo) 要在程序中使用游標(biāo)，大致需要以下幾個步驟： 1）定義游標(biāo) 2）打開游標(biāo) 3）從游標(biāo)中提取數(shù)據(jù) 4）關(guān)閉游標(biāo),例： i_cust_name varchar2(20); i_cust_id number(13); cursor cur_cust_info is select name,cust_id from cust where sts=A; -a begin open cur_cust_info; -b lo

12、op fetch cur_cust_info into i_cust_name,i_cust_id;-c exit when cur_cust_info%notfound; end loop; close cur_cust_info; -d end;,帶參數(shù)的游標(biāo) 與存儲過程和函數(shù)相似，可以將參數(shù)傳遞給游標(biāo)并在查詢中使用。這對于處理在某種條件下打開游標(biāo)的情況非常有用,例： i_cust_name varchar2(20); i_cust_id number(13); i_sts varchar2(10):=A; cursor cur_cust_info(p_sts varchar2) is s

13、elect name,cust_id from cust where sts= p_sts; -a begin open cur_cust_info(i_sts); -b loop fetch cur_cust_info into i_cust_name,i_cust_id; -c exit when cur_cust_info%notfound; end loop; close cur_cust_info; -d end;,REF CURSOR i_cust_name varchar2(20); i_cust_id number(13); type cur_ref is ref cursor

14、; cur_cust_info cur_ref; -a begin open cur_cust_info for select name,cust_id from cust where sts = A; -b loop fetch cur_cust_info into i_cust_name,i_cust_id; -c exit when cur_cust_info%notfound; end loop; close cur_cust_info; -d end;,十、PL/SQL程序編寫 塊的介紹 PL/SQL程序基本結(jié)構(gòu)是塊（BLOCK）所有的PL/SQL程序都是由塊組成的，這些塊之間還可以

15、相互嵌套。通常，程序中的每一塊都實現(xiàn)一個邏輯操作，從而把不同的任務(wù)進行分割，由不同的塊來實現(xiàn) 。,塊的結(jié)構(gòu) DECLARE /* Declarative section - PL/SQL variables, types, cursors,and local subprograms go here. */ -聲明部分 BEGIN /* Executable section - procedural and SQL statements go here.This is the main section of the block and the only one that is required.

16、 */ -執(zhí)行部分 EXCEPTION /* Exception-handling section - error-handling statements go here. */ -出錯部分 END;,Declare -聲明部分 l_servid number(12); begin -執(zhí)行部分 for rec in (select * from temp_ctzj_sts_cmp where serv_id_97 is null) loop begin select serv_id from serv_acc_nbr where nxx_nbr =rec.nxx_nbr and line_nb

17、r = rec.line_nbr and sts = A) where rownum = 1; exception when no_data_found then null; end; begin update temp_ctzj_sts_cmp set serv_id_97 = to_char(l_servid) where nxx_nbr = rec.nxx_nbr and line_nbr = rec.line_nbr;,exception when others then dbms_output.put_line(i_servid); end; i_servid := null; en

18、d loop; exception -出錯部分 when others then rollback; dbms_output.put_line(SQLERRM); end;,過程與函數(shù) PL/SQL的過程和函數(shù)的運行方式非常類似于其他第3代程序設(shè)計語言使用的過程和函數(shù)。它們之間具有許多共同的特征屬性?？傮w來說，過程和函數(shù)統(tǒng)稱為子程序。,創(chuàng)建過程語句的語法如下所示： CREATE OR REPLACE PROCEDURE procedure_name ( argumentIN | OUT | IN OUT datatype, ,argumentIN | OUT | IN OUT datatype

19、 ) IS | AS procedure_body,IN/OUT/IN OUT參數(shù),模式說明,IN,OUT,IN OUT,當(dāng)過程被調(diào)用時，實參的值將傳入該過程。在該過程內(nèi)部，形參類似PL/SQL使用的常數(shù)，即該值具有只讀屬性不能對其修改。當(dāng)該過程結(jié)束時，控制將返回到調(diào)用環(huán)境，這時，對應(yīng)的實參沒有改變,當(dāng)過程被調(diào)用時，實參具有的任何值將被忽略不計。在該過程內(nèi)部，形參的作用類似沒有初始化的PL/SQL變量，其值為空（ NULL）。該變量具有讀寫屬性。當(dāng)該過程結(jié)束時，控制將返回調(diào)用環(huán)境，形參的內(nèi)容將賦予對應(yīng)的實參。,該模式是模式IN 和OUT的組合。當(dāng)調(diào)用過程時，實參的值將被傳遞到該過程中。在該過程

20、內(nèi)部，形參相當(dāng)于初始化的變量，并具有讀寫屬性。當(dāng)該過程結(jié)束時，控制將返回到調(diào)用環(huán)境中，形參的內(nèi)容將賦予實參,過程體 過程體是一種帶有聲明部分，可執(zhí)行語句部分和異常部分的PL/SQL塊。該聲明部分是位于關(guān)鍵字IS或AS和關(guān)鍵字BEGIN之間的語句?？蓤?zhí)行部分（該部分是必須要有的）是位于關(guān)鍵字BEGIN和EXCEPTION之間的語句。最后，異常部分位于關(guān)鍵字EXCEPTION和關(guān)鍵字END之間的語句。,綜上所述，過程的結(jié)構(gòu)應(yīng)具有下面所示的特征： CREATE OR REPLACE PROCEDURE procedure_name parameter_list AS /* Declarative s

21、ection is here */ -聲明部分 BEGIN /* Executable section is here */ -執(zhí)行部分 EXCEPTION /* Exception section is here */ -異常部分 END procedure_name;,函數(shù) 函數(shù)的創(chuàng)建與過程的創(chuàng)建十分相似，具體語法如下： CREATE OR REPLACE FUNCTION function_name (argument IN|OUT|IN OUT datatype ,argument IN|OUT|IN OUT datatype) RETURN datatype IS/AS FUCTIO

22、N BODY;,包 包的說明（也叫做包頭）包含了有關(guān)包內(nèi)容的信息。然而，該部分中不包括包的代碼部分。 CREATE OR REPLACE PACKAGE package_nameIS | AS type_definition| -結(jié)構(gòu)定義 procedure_specification| -過程聲明 function_specification| -函數(shù)聲明 variable_declaration| -變量聲明 exception_declaration| -出錯聲明 cursor_declaration| -游標(biāo)聲明 pragma_declaration END package_name;

23、,通俗地講：包頭只是起到聲明作用，其他具體的實現(xiàn)都是在包體中完成的。,包體介紹 包頭和包體中的過程和函數(shù)的說明必須一致，其中包括子程序名和其參數(shù)名，以及參數(shù)的模式,包頭 CREATE OR REPLACE PACKAGE pkg_val IS - Author : MRC - Created : 2005-4-8 14:07:30 - Purpose : VALIDATE THE VALUES OF EACH ENTITY INCLUDE CUST,ACCT,SERV,PROD,ORDER -修改：王興明 2005/10/20 -客戶信息校驗 PROCEDURE prc_val_cust_sr

24、c(i_src_fileset VARCHAR2, i_sib_process_status VARCHAR2, i_batch_no NUMBER); -賬戶信息校驗 PROCEDURE prc_val_acct_src(i_src_fileset VARCHAR2, i_sib_process_status VARCHAR2, i_batch_no NUMBER); -用戶信息校驗 PROCEDURE prc_val_serv_src(i_src_fileset VARCHAR2, i_sib_process_status VARCHAR2, i_batch_no NUMBER); -子產(chǎn)

25、品信息校驗 PROCEDURE prc_val_prod_src(i_src_fileset VARCHAR2, i_sib_process_status VARCHAR2, i_batch_no NUMBER); -優(yōu)惠信息校驗 PROCEDURE prc_val_disc_src(i_src_fileset VARCHAR2, i_sib_process_status VARCHAR2, i_batch_no NUMBER); END;,CREATE OR REPLACE PACKAGE BODY pkg_val AS PROCEDURE prc_val_cust_src(i_src_fi

26、leset varchar2, i_sib_process_status varchar2, i_batch_no number) AS i_process_name VARCHAR2(50) :=; i_error_info VARCHAR2(2000); -錯誤詳細描述 BEGIN END; PROCEDURE prc_val_acct_src(i_src_fileset varchar2, i_sib_process_status varchar2 i_batch_nonumber) AS END;,包的作用域 包頭中聲明的任何對象都是在其作用域中，并且可在其外部使用包名作為前綴對其進行

27、引用。,begin arg1 datatype:=xxx; arg2 datatype:=xxx; package_cedure_name(arg1,arg2); end;,十一、常用工具與調(diào)試方式（SQL Navigator PL/SQL Developer） SQL Navigator調(diào)試 SQL Navigator的調(diào)試很簡單，大致分為以下幾步：,1進入Debug模式,2設(shè)置調(diào)試參數(shù),3正式調(diào)試,4正常結(jié)束調(diào)試或者中斷調(diào)試,Server output 按鈕，可以顯示調(diào)試過程中oracle執(zhí)行的情況,Start debugger按鈕，點擊按鈕進入調(diào)試模式，調(diào)試必須點這個按鈕

28、,進入DEBUG模式,Step over按鈕，點擊進入Console界面（調(diào)試控制臺）,設(shè)置調(diào)試參數(shù) 需要完成兩項工作,1）設(shè)置IN參數(shù),2）設(shè)置提交方式,正式調(diào)試,直接運行,跑到斷點處停下來，按鈕F4。,單步調(diào)試，遇到調(diào)用其他存儲過程或者函數(shù)時跳過，按鈕F8。,單步調(diào)試，遇到調(diào)用其他存儲過程或者函數(shù)時進入該存儲過程或者函數(shù)中，按鈕F7。,當(dāng)調(diào)試時進入了調(diào)用存儲過程或者函數(shù)后，該按鈕可以幫助跳出該調(diào)用存儲過程或者函數(shù)，回到調(diào)試的上層函數(shù)中，按鈕Shift+F7。,斷點設(shè)置 1）點擊需要設(shè)置斷點的代碼行，點擊右鍵，出現(xiàn)菜單，選擇toggle breakpoint選項，該行應(yīng)該成紅色，此時斷點即設(shè)置成功 。 2）點擊需要設(shè)置斷點的代碼行，按按鈕F5也能設(shè)置斷點。 設(shè)置斷點之后，按F4按鈕，程序可以連續(xù)運行，直到碰到設(shè)置的斷點為止。 當(dāng)然，設(shè)置的斷點需要在程序運行能夠執(zhí)行道的代碼段上，否則程序?qū)⒅苯?/p>