[轉(zhuǎn)貼]Oracle PL/SQL語言基礎(chǔ)Oracle PL/SQL語言基礎(chǔ) PL/SQL是ORACLE對(duì)標(biāo)準(zhǔn)數(shù)據(jù)庫語言的擴(kuò)展，ORACLE公司已經(jīng)將PL/SQL整合到ORACLE 服務(wù)器和其他工具中了，近幾年中更多的開發(fā)人員和DBA開始使用PL/SQL，本文將講述PL/SQL基礎(chǔ)語法，結(jié)構(gòu)和組件、以及如何設(shè)計(jì)并執(zhí)行一個(gè)PL/SQL程序。 PL/SQL的優(yōu)點(diǎn) 從版本6開始PL/SQL就被可靠的整合到ORACLE中了，一旦把握PL/SQL的優(yōu)點(diǎn)以及其獨(dú)有的數(shù)據(jù)治理的便利性，那么你很難想象ORACLE缺了PL/SQL的情形。PL/SQL 不是一個(gè)獨(dú)立的產(chǎn)品，他是一個(gè)整合到ORACLE服務(wù)器和ORACLE工具中的技術(shù)，可以把PL/SQL看作ORACLE服務(wù)器內(nèi)的一個(gè)引擎，sql語句執(zhí)行者處理單個(gè)的sql語句，PL/SQL引擎處理PL/SQL程序塊。當(dāng)PL/SQL程序塊在PL/SQL引擎處理時(shí)，ORACLE服務(wù)器中的SQL語句執(zhí)行器處理pl/sql程序塊中的SQL語句。 PL/SQL的優(yōu)點(diǎn)如下： . PL/SQL是一種高性能的基于事務(wù)處理的語言，能運(yùn)行在任何ORACLE環(huán)境中，支持所有數(shù)據(jù)處理命令。通過使用PL/SQL程序單元處理SQL的數(shù)據(jù)定義和數(shù)據(jù)控制元素。 . PL/SQL支持所有SQL數(shù)據(jù)類型和所有SQL函數(shù)，同時(shí)支持所有ORACLE對(duì)象類型 . PL/SQL塊可以被命名和存儲(chǔ)在ORACLE服務(wù)器中，同時(shí)也能被其他的PL/SQL程序或SQL命令調(diào)用，任何客戶/服務(wù)器工具都能訪問PL/SQL程序，具有很好的可重用性。 . 可以使用ORACLE數(shù)據(jù)工具治理存儲(chǔ)在服務(wù)器中的PL/SQL程序的安全性。可以授權(quán)或撤銷數(shù)據(jù)庫其他用戶訪問PL/SQL程序的能力。 . PL/SQL代碼可以使用任何ASCII文本編輯器編寫，所以對(duì)任何ORACLE能夠運(yùn)行的操作系統(tǒng)都是非常便利的 . 對(duì)于SQL，ORACLE必須在同一時(shí)間處理每一條SQL語句，在網(wǎng)絡(luò)環(huán)境下這就意味作每一個(gè)獨(dú)立的調(diào)用都必須被oracle服務(wù)器處理，這就占用大量的服務(wù)器時(shí)間，同時(shí)導(dǎo)致網(wǎng)絡(luò)擁擠。而PL/SQL是以整個(gè)語句塊發(fā)給服務(wù)器，這就降低了網(wǎng)絡(luò)擁擠。 PL/SQL塊結(jié)構(gòu) PL/SQL是一種塊結(jié)構(gòu)的語言，組成PL/SQL程序的單元是邏輯塊，一個(gè)PL/SQL 程序包含了一個(gè)或多個(gè)邏輯塊，每個(gè)塊都可以劃分為三個(gè)部分。與其他語言相同，變量在使用之前必須聲明，PL/SQL提供了獨(dú)立的專門用于處理異常的部分，下面描述了PL/SQL塊的不同部分： 聲明部分(Declaration section) 聲明部分包含了變量和常量的數(shù)據(jù)類型和初始值。這個(gè)部分是由要害字DECLARE開始，假如不需要聲明變量或常量，那么可以忽略這一部分；需要說明的是游標(biāo)的聲明也在這一部分。 執(zhí)行部分(Executable section) 執(zhí)行部分是PL/SQL塊中的指令部分，由要害字BEGIN開始，所有的可執(zhí)行語句都放在這一部分，其他的PL/SQL塊也可以放在這一部分。 異常處理部分(Exception section) 這一部分是可選的，在這一部分中處理異常或錯(cuò)誤，對(duì)異常處理的具體討論我們?cè)诤竺孢M(jìn)行。 PL/SQL塊語法 [DECLARE] ---declaration statements BEGIN ---executable statements [EXCEPTION] ---exception statements END ;PL/SQL塊中的每一條語句都必須以分號(hào)結(jié)束，SQL語句可以使多行的，但分號(hào)表示該語句的結(jié)束。一行中可以有多條SQL語句，他們之間以分號(hào)分隔。每一個(gè)PL/SQL塊由BEGIN或DECLARE開始，以END結(jié)束。注釋由--標(biāo)示。 PL/SQL塊的命名和匿名 PL/SQL程序塊可以是一個(gè)命名的程序塊也可以是一個(gè)匿名程序塊。匿名程序塊可以用在服務(wù)器端也可以用在客戶端。 命名程序塊可以出現(xiàn)在其他PL/SQL程序塊的聲明部分，這方面比較明顯的是子程序，子程序可以在執(zhí)行部分引用，也可以在異常處理部分引用。 PL/SQL程序塊可背獨(dú)立編譯并存儲(chǔ)在數(shù)據(jù)庫中，任何與數(shù)據(jù)庫相連接的應(yīng)用程序都可以訪問這些存儲(chǔ)的PL/SQL程序塊。ORACLE提供了四種類型的可存儲(chǔ)的程序： 　. 函數(shù) 　. 過程 　. 包 　. 觸發(fā)器 函數(shù) 函數(shù)是命名了的、存儲(chǔ)在數(shù)據(jù)庫中的PL/SQL程序塊。函數(shù)接受零個(gè)或多個(gè)輸入?yún)?shù)，有一個(gè)返回值，返回值的數(shù)據(jù)類型在創(chuàng)建函數(shù)時(shí)定義。定義函數(shù)的語法如下： FUNCTION name [{parameter[,parameter,...])] RETURN datatypes IS [local declarations] BEGIN execute statements [EXCEPTION exception handlers] END [name] ;過程 存儲(chǔ)過程是一個(gè)PL/SQL程序塊，接受零個(gè)或多個(gè)參數(shù)作為輸入(INPUT)或輸出(OUTPUT)、或既作輸入又作輸出(INOUT)，與函數(shù)不同，存儲(chǔ)過程沒有返回值，存儲(chǔ)過程不能由SQL語句直接使用，只能通過EXECUT命令或PL/SQL程序塊內(nèi)部調(diào)用，定義存儲(chǔ)過程的語法如下： PROCEDURE name [(parameter[,parameter,...])] IS [local declarations] BEGIN execute statements [EXCEPTION exception handlers ] END [name] ;包(package) 包其實(shí)就是被組合在一起的相關(guān)對(duì)象的集合，當(dāng)包中任何函數(shù)或存儲(chǔ)過程被調(diào)用，包就被加載入內(nèi)存中，包中的任何函數(shù)或存儲(chǔ)過程的子程序訪問速度將大大加快。 包由兩個(gè)部分組成：規(guī)范和包主體(body),規(guī)范描述變量、常量、游標(biāo)、和子程序，包體完全定義子程序和游標(biāo)。 觸發(fā)器(trigger) 觸發(fā)器與一個(gè)表或數(shù)據(jù)庫事件聯(lián)系在一起的，當(dāng)一個(gè)觸發(fā)器事件發(fā)生時(shí)，定義在表上的觸發(fā)器被觸發(fā)。 變量和常量 變量存放在內(nèi)存中以獲得值，能被PL/SQL塊引用。你可以把變量想象成一個(gè)可儲(chǔ)藏東西的容器，容器內(nèi)的東西是可以改變的。 聲明變量 變量一般都在PL/SQL塊的聲明部分聲明，PL/SQL是一種強(qiáng)壯的類型語言，這就是說在引用變量前必須首先聲明，要在執(zhí)行或異常處理部分使用變量，那么變量必須首先在聲明部分進(jìn)行聲明。 聲明變量的語法如下： Variable_name [CONSTANT] databyte [NOT NULL][:=DEFAULT eXPression] ;注重:可以在聲明變量的同時(shí)給變量強(qiáng)制性的加上NOT NULL約束條件，此時(shí)變量在初始化時(shí)必須賦值。 給變量賦值 給變量賦值有兩種方式： . 直接給變量賦值 　X:=200; 　Y=Y+(X*20); . 通過SQL SELECT INTO 或FETCH INTO給變量賦值 SELECT SUM(SALARY),SUM(SALARY*0.1) INTO TOTAL_SALARY,TATAL_COMMISSION FROM EMPLOYEE WHERE DEPT=10; 常量 常量與變量相似，但常量的值在程序內(nèi)部不能改變，常量的值在定義時(shí)賦予，，他的聲明方式與變量相似，但必須包括要害字CONSTANT。常量和變量都可被定義為SQL和用戶定義的數(shù)據(jù)類型。 ZERO_VALUE CONSTANT NUMBER:=0; ;這個(gè)語句定了一個(gè)名叫ZERO_VALUE、數(shù)據(jù)類型是NUMBER、值為0的常量。 標(biāo)量(scalar)數(shù)據(jù)類型 標(biāo)量(scalar)數(shù)據(jù)類型沒有內(nèi)部組件，他們大致可分為以下四類： 　. number 　. character 　. date/time 　. boolean 表1顯示了數(shù)字?jǐn)?shù)據(jù)類型；表2顯示了字符數(shù)據(jù)類型；表3顯示了日期和布爾數(shù)據(jù)類型。 表1 Scalar Types:Numeric Datatype Range SuBTypes description ;BINARY_INTEGER -214748-2147483647 NATURAL NATURAL NPOSITIVE POSITIVEN SIGNTYPE;;用于存儲(chǔ)單字節(jié)整數(shù)。 要求存儲(chǔ)長度低于NUMBER值。 用于限制范圍的子類型(SUBTYPE): 　NATURAL:用于非負(fù)數(shù) 　POSITIVE:只用于正數(shù) 　NATURALN:只用于非負(fù)數(shù)和非NULL值 　POSITIVEN:只用于正數(shù)，不能用于NULL值 　SIGNTYPE:只有值:-1、0或1. ;NUMBER 1.0E-130-9.99E125 DEC DECIMAL DOUBLE;;PRECISION FLOAT;;INTEGERIC INT NUMERIC REAL SMALLINT 存儲(chǔ)數(shù)字值，包括整數(shù)和浮點(diǎn)數(shù)。可以選擇精度和刻度方式，語法： number[（[,]）]。 缺省的精度是38,scale是0. ;PLS_INTEGER -2147483647-2147483647 ; 與BINARY_INTEGER基本相同，但采用機(jī)器運(yùn)算時(shí)，PLS_INTEGER提供更好的性能 。 ;表2 字符數(shù)據(jù)類型;;datatype rang subtype description ;CHAR 最大長度32767字節(jié);;CHARACTER 存儲(chǔ)定長字符串，假如長度沒有確定，缺省是1 ;LONG 最大長度2147483647字節(jié) ; 存儲(chǔ)可變長度字符串 ;RAW;;最大長度32767字節(jié) ; 用于存儲(chǔ)二進(jìn)制數(shù)據(jù)和字節(jié)字符串，當(dāng)在兩個(gè)數(shù)據(jù)庫之間進(jìn)行傳遞時(shí)，RAW數(shù)據(jù)不在字符集之間進(jìn)行轉(zhuǎn)換。 ;LONGRAW 最大長度2147483647 ; 與LONG數(shù)據(jù)類型相似，同樣他也不能在字符集之間進(jìn)行轉(zhuǎn)換。 ;ROWID 18個(gè)字節(jié) ; 與數(shù)據(jù)庫ROWID偽列類型相同，能夠存儲(chǔ)一個(gè)行標(biāo)示符，可以將行標(biāo)示符看作數(shù)據(jù)庫中每一行的唯一鍵值。 ;VARCHAR2 最大長度32767字節(jié) STRINGVARCHAR 與VARCHAR數(shù)據(jù)類型相似，存儲(chǔ)可變長度的字符串。聲明方法與VARCHAR相同;; ;表3 DATE和BOOLEAN datatype range;;description ;BOOLEAN TRUE/FALSE 存儲(chǔ)邏輯值TRUE或FALSE,無參數(shù) ;DATE 01/01/4712 BC;;存儲(chǔ)固定長的日期和時(shí)間值，日期值中包含時(shí)間 ;LOB數(shù)據(jù)類型 LOB(大對(duì)象,Large object) 數(shù)據(jù)類型用于存儲(chǔ)類似圖像，聲音這樣的大型數(shù)據(jù)對(duì)象，LOB數(shù)據(jù)對(duì)象可以是二進(jìn)制數(shù)據(jù)也可以是字符數(shù)據(jù)，其最大長度不超過4G。LOB數(shù)據(jù)類型支持任意訪問方式，LONG只支持順序訪問方式。LOB存儲(chǔ)在一個(gè)單獨(dú)的位置上，同時(shí)一個(gè)'LOB定位符'(LOB locator)存儲(chǔ)在原始的表中，該定位符是一個(gè)指向?qū)嶋H數(shù)據(jù)的指針。在PL/SQL中操作LOB數(shù)據(jù)對(duì)象使用ORACLE提供的包DBMS_LOB.LOB數(shù)據(jù)類型可分為以下四類： . BFILE . BLOB . CLOB . NCLOB 操作符 與其他程序設(shè)計(jì)語言相同，PL/SQL有一系列操作符。操作符分為下面幾類： . 算術(shù)操作符 . 關(guān)系操作符 . 比較操作符 . 邏輯操作符 算術(shù)操作符如表4所示 operator operation ;+;;加 -;;減 /;;除 ;*;;乘;;** 乘方 ;關(guān)系操作符主要用于條件判定語句或用于where子串中，關(guān)系操作符檢查條件和結(jié)果是否為true或false,表5是PL/SQL中的關(guān)系操作符 operator operation ;<;;小于操作符 ;<=;;小于或等于操作符 ;>;;大于操作符 ;>= 大于或等于操作符 ;=;;等于操作符;; ;!=;;不等于操作符 ;<>;;不等于操作符 ;:=;;賦值操作符 ;表6 顯示的是比較操作符 operator;;operation ;IS NULL 假如操作數(shù)為NULL返回TRUE ;LIKE 比較字符串值 ;BETWEEN 驗(yàn)證值是否在范圍之內(nèi) ;IN 驗(yàn)證操作數(shù)在設(shè)定的一系列值中 ;表7.8顯示的是邏輯操作符 operator operation ;AND;;兩個(gè)條件都必須滿足 ;OR 只要滿足兩個(gè)條件中的一個(gè) ;NOT 取反 ;執(zhí)行部分 執(zhí)行部分包含了所有的語句和表達(dá)式,執(zhí)行部分以要害字BEGIN開始，以要害字EXCEPTION結(jié)束，假如EXCEPTION不存在，那么將以要害字END結(jié)束。 分號(hào)分隔每一條語句，使用賦值操作符:=或SELECT INTO或FETCH INTO給每個(gè)變量賦值，執(zhí)行部分的錯(cuò)誤將在異常處理部分解決，在執(zhí)行部分中可以使用另一個(gè)PL/SQL程序塊，這種程序塊被稱為嵌套塊 所有的SQL數(shù)據(jù)操作語句都可以用于執(zhí)行部分，PL/SQL塊不能再屏幕上顯示SELECT語句的輸出。SELECT語句必須包括一個(gè)INTO子串或者是游標(biāo)的一部分，執(zhí)行部分使用的變量和常量必須首先在聲明部分聲明，執(zhí)行部分必須至少包括一條可執(zhí)行語句，NULL是一條合法的可執(zhí)行語句，事物控制語句COMMIT和ROLLBACK可以在執(zhí)行部分使用，數(shù)據(jù)定義語言(Data Definition language)不能在執(zhí)行部分中使用，DDL語句與EXECUTE IMMEDIATE一起使用或者是DBMS_SQL調(diào)用。 執(zhí)行一個(gè)PL/SQL塊 SQL*PLUS中匿名的PL/SQL塊的執(zhí)行是在PL/SQL塊后輸入/來執(zhí)行，如下面的例子所示： declare;;　v_comm_percent constant number:=10; begin 　update emp 　set comm=sal*v_comm_percent 　where deptno=10; 　end SQL> / PL/SQL procedure sUCcessfully completed. SQL> ;命名的程序與匿名程序的執(zhí)行不同，執(zhí)行命名的程序塊必須使用execute要害字： create or replace procedure update_commission 　(v_dept in number,v_pervent in number default 10) is;;begin 　update emp 　set comm=sal*v_percent 　where deptno=v_dept; end SQL>/ Procedure created SQL>execute update_commission(10,15); PL/SQL procedure successfully completed. SQL>;; ;假如在另一個(gè)命名程序塊或匿名程序塊中執(zhí)行這個(gè)程序，那么就不需要EXECUTE關(guān)進(jìn)字。 declare 　v_dept number; begin 　select a.deptno 　into v_dept 　from emp a 　where job='PRESIDENT' 　update_commission(v_dept); end SQL>/ 　PL/SQL procedure successfully completed SQL> ;　控制結(jié)構(gòu) 控制結(jié)構(gòu)控制PL/SQL程序流程的代碼行,PL/SQL支持條件控制和循環(huán)控制結(jié)構(gòu)。 語法和用途 　IF..THEN 語法： IF condition THEN 　Statements 1; 　Statements 2; 　.... END IF;; ;IF語句判定條件condition是否為TRUE，假如是，則執(zhí)行THEN后面的語句，假如condition為false或NULL則跳過THEN到END IF之間的語句，執(zhí)行END IF后面的語句。 IF..THEN...ELSE 語法: IF condition THEN 　Statements 1; 　Statements 2; 　.... ELSE 　Statements 1; 　Statements 2; 　.... END IF;; ;假如條件condition為TRUE,則執(zhí)行THEN到ELSE之間的語句，否則執(zhí)行ELSE到END IF之間的語句。 IF 可以嵌套，可以在IF 或IF ..ELSE語句中使用IF或IF..ELSE語句。 if (a>b) and (a>c) then g:=a; else g:=b; if c>g then 　g:=c; end if end if ;IF..THEN..ELSIF 語法： IF condition1 THEN 　statement1; ELSIF condition2 THEN 　statement2; ELSIF condition3 THEN 　statement3; ELSE 　statement4; END IF; 　statement5; ;假如條件condition1為TRUE則執(zhí)行statement1,然后執(zhí)行statement5,否則判定condition2是否為TRUE,若為TRUE則執(zhí)行statement2,然后執(zhí)行statement5,對(duì)于condition3也是相同的，假如condition1，condition2，condition3都不成立，那么將執(zhí)行statement4,然后執(zhí)行statement5。 循環(huán)控制 循環(huán)控制的基本形式是LOOP語句，LOOP和END LOOP之間的語句將無限次的執(zhí)行。LOOP語句的語法如下： LOOP;;　statements; END LOOP LOOP和END LOOP之間的語句無限次的執(zhí)行顯然是不行的，那么在使用LOOP語句時(shí)必須使用EXIT語句，強(qiáng)制循環(huán)結(jié)束，例如： X:=100; LOOP 　X:=X+10; 　IF X>1000 THEN EXIT; 　END IF END LOOP; Y:=X; ;此時(shí)Y的值是1010. EXIT WHEN語句將結(jié)束循環(huán)，假如條件為TRUE，則結(jié)束循環(huán)。 X:=100; LOOP X:=X+10; EXIT WHEN X>1000; X:=X+10; END LOOP; Y:=X; ;WHILE..LOOP WHILE..LOOP有一個(gè)條件與循環(huán)相聯(lián)系，假如條件為TRUE,則執(zhí)行循環(huán)體內(nèi)的語句，假如結(jié)果為FALSE,則結(jié)束循環(huán)。 X:=100; WHILE X<=1000 LOOP 　X:=X+10; END LOOP; Y=X;;; ;FOR...LOOP 語法： FOR counter IN [REVERSE] start_range....end_range LOOP statements; END LOOP; ;LOOP和WHILE循環(huán)的循環(huán)次數(shù)都是不確定的，F(xiàn)OR循環(huán)的循環(huán)次數(shù)是固定的，counter是一個(gè)隱式聲明的變量，他的初始值是start_range,第二個(gè)值是start_range+1,直到end_range,假如start_range等于end _range,那么循環(huán)將執(zhí)行一次。假如使用了REVERSE要害字，那么范圍將是一個(gè)降序。 X:=100; FOR v_counter in 1..10 loop x:=x+10; end loop y:=x; ;假如要退出for循環(huán)可以使用EXIT語句。 標(biāo)簽 用戶可以使用標(biāo)簽使程序獲得更好的可讀性。程序塊或循環(huán)都可以被標(biāo)記。標(biāo)簽的形式是<>。 標(biāo)記程序塊 <> [DECLARE] ... ... ... BEGIN ........ [EXCEPTION] ....... END label_name ;標(biāo)記循環(huán) <> LOOP ......... <> loop .......... <> loop .... EXIT outer_loop WHEN v_condition=0; end loop innermost_loop; .......... END LOOP inner_loop; END LOOP outer_loop;;; ;GOTO語句 語法： GOTO LABEL; 執(zhí)行GOTO語句時(shí)，控制會(huì)立即轉(zhuǎn)到由標(biāo)簽標(biāo)記的語句。PL/SQL中對(duì)GOTO語句有一些限制，對(duì)于塊、循環(huán)、IF語句而言，從外層跳轉(zhuǎn)到內(nèi)層是非法的。 X ：=100; FOR V_COUNTER IN 1..10 LOOP 　IF V_COUNTER =4 THEN GOTO end_of_loop 　END IF 　X:=X+10; 　<> 　NULL END LOOP Y:=X;;; ;注重：NULL是一個(gè)合法的可執(zhí)行語句。 嵌套 程序塊的內(nèi)部可以有另一個(gè)程序塊這種情況稱為嵌套。嵌套要注重的是變量，定義在最外部程序塊中的變量可以在所有子塊中使用，假如在子塊中定義了與外部程序塊變量相同的變量名，在執(zhí)行子塊時(shí)將使用子塊中定義的變量。子塊中定義的變量不能被父塊引用。同樣GOTO語句不能由父塊跳轉(zhuǎn)道子塊中，反之則是合法的。 《OUTER BLOCK》 DECLARE 　A_NUMBER INTEGER； 　B_NUMBER INTEGER； BEGIN 　--A_NUMBER and B_NUMBER are available here 　<> 　DECLARE 　C_NUMBER INTEGER 　B_NUMBER NUMBER(20) BEGIN 　C_NUMBER:=A_NUMBER; 　C_NUMBER=OUTER_BLOCK.B_NUMBER; END SUB_BLOCK; END OUT_BLOCK; ;小結(jié) 　 我們?cè)谶@篇文章中介紹了PL/SQL的基礎(chǔ)語法以及如何使用PL/SQL語言設(shè)計(jì)和運(yùn)行PL/SQL程序塊，并將PL/SQL程序整合到Oracle服務(wù)器中,雖然PL/SQL程序作為功能塊嵌入Oracle數(shù)據(jù)庫中，但PL/SQL與ORACLE數(shù)據(jù)庫的緊密結(jié)合使得越來越多的Oracle數(shù)據(jù)庫治理員和開發(fā)人員開始使用PL/SQL