Oracle PL/SQL語言基礎 (1)

PL/SQL是ORACLE對標準資料庫語言的擴充，ORACLE公司已經將PL/SQL整合到ORACLE 伺服器和其他工具中了，近幾年中更多的開發人員和DBA開始使用PL/SQL，本文將講述PL/SQL基礎文法，結構和組件、以及如何設計並執行一個PL/SQL程式。
  
　　PL/SQL的優點
  
　　從版本6開始PL/SQL就被可靠的整合到ORACLE中了，一旦掌握PL/SQL的優點以及其專屬的資料管理的便利性，那麼你很難想象ORACLE缺了PL/SQL的情形。PL/SQL 不是一個獨立的產品，他是一個整合到ORACLE伺服器和ORACLE工具中的技術，可以把PL/SQL看作ORACLE伺服器內的一個引擎，sql語句執行者處理單個的sql語句，PL/SQL引擎處理PL/SQL程式塊。當PL/SQL程式塊在PL/SQL引擎處理時，ORACLE伺服器中的SQL語句執行器處理pl/sql程式塊中的SQL語句。
  
　　PL/SQL的優點如下：
  
　　. PL/SQL是一種高效能的基於交易處理的語言，能運行在任何ORACLE環境中，支援所有資料處理命令。通過使用PL/SQL程式單元處理SQL的資料定義和資料控制元素。
  
　　. PL/SQL支援所有SQL資料類型和所有SQL函數，同時支援所有ORACLE物件類型
  
　　. PL/SQL塊可以被命名和儲存在ORACLE伺服器中，同時也能被其他的PL/SQL程式或SQL命令調用，任何客戶/伺服器工具都能訪問PL/SQL程式，具有很好的可重用性。
  
　　. 可以使用ORACLE資料工具管理儲存在伺服器中的PL/SQL程式的安全性。可以授權或撤銷資料庫其他使用者訪問PL/SQL程式的能力。
  
　　. PL/SQL代碼可以使用任何ASCII文字編輯器編寫，所以對任何ORACLE能夠啟動並執行作業系統都是非常便利的
  
　　. 對於SQL，ORACLE必須在同一時間處理每一條SQL語句，在網路環境下這就意味作每一個獨立的調用都必須被oracle伺服器處理，這就佔用大量的伺服器時間，同時導致網路擁擠。而PL/SQL是以整個語句塊發給伺服器，這就降低了網路擁擠。
  
　　PL/SQL塊結構
  
　　PL/SQL是一種塊結構的語言，組成PL/SQL程式的單元是邏輯塊，一個PL/SQL 程式包含了一個或多個邏輯塊，每個塊都可以劃分為三個部分。與其他語言相同，變數在使用之前必須聲明，PL/SQL提供了獨立的專門用於處理異常的部分，下面描述了PL/SQL塊的不同部分：
  
　　聲明部分(Declaration section)
  
　　聲明部分包含了變數和常量的資料類型和初始值。這個部分是由關鍵字DECLARE開始，如果不需要聲明變數或常量，那麼可以忽略這一部分；需要說明的是遊標的聲明也在這一部分。
  
　　執行部分(Executable section)
  
　　執行部分是PL/SQL塊中的指令部分，由關鍵字BEGIN開始，所有的可執行語句都放在這一部分，其他的PL/SQL塊也可以放在這一部分。
  
　　異常處理部分(Exception section)
  
　　這一部分是可選的，在這一部分中處理異常或錯誤，對異常處理的詳細討論我們在後面進行。
  
　　PL/SQL塊文法
  
[DECLARE]
---declaration statements
BEGIN
---executable statements
[EXCEPTION]
---exception statements
END
   
  
　　PL/SQL塊中的每一條語句都必須以分號結束，SQL語句可以使多行的，但分號表示該語句的結束。一行中可以有多條SQL語句，他們之間以分號分隔。每一個PL/SQL塊由BEGIN或DECLARE開始，以END結束。注釋由--標示。
  
　　PL/SQL塊的命名和匿名
  
　　PL/SQL程式塊可以是一個命名的程式塊也可以是一個Anonymizer塊。Anonymizer塊可以用在伺服器端也可以用在用戶端。
  
　　命名程式塊可以出現在其他PL/SQL程式塊的聲明部分，這方面比較明顯的是子程式，子程式可以在執行部分引用，也可以在異常處理部分引用。
  
　　PL/SQL程式塊可背獨立編譯並儲存在資料庫中，任何與資料庫相連線應用程式程式都可以訪問這些儲存的PL/SQL程式塊。ORACLE提供了四種類型的可儲存的程式：
  
　　　. 函數
  
　　　. 過程
  
　　　. 包
  
　　　. 觸發器
  
　　        函數
  
　　函數是命名了的、儲存在資料庫中的PL/SQL程式塊。函數接受零個或多個輸入參數，有一個傳回值，傳回值的資料類型在建立函數時定義。定義函數的文法如下：
  
FUNCTION name [{parameter[,parameter,...])] RETURN datatypes IS
[local declarations]
BEGIN
execute statements
[EXCEPTION
exception handlers]
END [name]
   
  
　　過程
  
　　預存程序是一個PL/SQL程式塊，接受零個或多個參數作為輸入(INPUT)或輸出(OUTPUT)、或既作輸入又作輸出(INOUT)，與函數不同，預存程序沒有傳回值，預存程序不能由SQL語句直接使用，只能通過EXECUT命令或PL/SQL程式塊內部調用，定義預存程序的文法如下：
  
PROCEDURE name [(parameter[,parameter,...])] IS
[local declarations]
BEGIN
execute statements
[EXCEPTION
exception handlers ]
END [name]
   
  
　　包(package)
  
　　包其實就是被組合在一起的相關對象的集合，當包中任何函數或預存程序被調用，包就被載入入記憶體中，包中的任何函數或預存程序的子程式訪問速度將大大加快。
包由兩個部分組成：規範和包主體(body),規範描述變數、常量、遊標、和子程式，包體完全定義子程式和遊標。
  
　　觸發器(trigger)
  
　　觸發器與一個表或資料庫事件聯絡在一起的，當一個觸發事件發生時，定義在表上的觸發器被觸發。
  
變數和常量
  
　　變數存放在記憶體中以獲得值，能被PL/SQL區塊引述。你可以把變數想象成一個可儲藏東西的容器，容器內的東西是可以改變的。
  
　　聲明變數
  
　　變數一般都在PL/SQL塊的聲明部分聲明，PL/SQL是一種強壯的類型語言，這就是說在引用變數前必須首先聲明，要在執行或異常處理部分使用變數，那麼變數必須首先在聲明部分進行聲明。
  
　　聲明變數的文法如下：
  
Variable_name [CONSTANT] databyte [NOT NULL][:=|DEFAULT expression]
   
  
　　注意:可以在聲明變數的同時給變數強制性的加上NOT NULL約束條件，此時變數在初始化時必須賦值。
  
　　給變數賦值
  
　　給變數賦值有兩種方式：
  
　　. 直接給變數賦值
  
　　　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;
  
　　常量
  
　　常量與變數相似，但常量的值在程式內部不能改變，常量的值在定義時賦予，，他的聲明方式與變數相似，但必須包括關鍵字CONSTANT。常量和變數都可被定義為SQL和使用者定義的資料類型。
  
ZERO_VALUE CONSTANT NUMBER:=0;
   
  
　　這個語句定了一個名叫ZERO_VALUE、資料類型是NUMBER、值為0的常量。
  
　　標量(scalar)資料類型
  
　　標量(scalar)資料類型沒有內部組件，他們大致可分為以下四類：
  
　　　. number
　　　. character
　　　. date/time
　　　. boolean
  
　　表1顯示了數字資料類型；表2顯示了字元資料類型；表3顯示了日期和布林值資料型別。
  
　　表1 Scalar Types:Numeric
  
Datatype
   Range
   Subtypes
   description
   
BINARY_INTEGER
   -214748-2147483647
   NATURAL
NATURAL
NPOSITIVE
POSITIVEN
SIGNTYPE  
   用於儲存單位元組整數。
要求儲存長度低於NUMBER值。
用於限制範圍的子類型(SUBTYPE):
　NATURAL:用於非負數
　POSITIVE:只用於正數
　NATURALN:只用於非負數和非NULL值
　POSITIVEN:只用於正數，不能用於NULL值
　SIGNTYPE:只有值:-1、0或1.
   
NUMBER
   1.0E-130-9.99E125
   DEC
DECIMAL
DOUBLE  
PRECISION
FLOAT  
INTEGERIC
INT
NUMERIC
REAL
SMALLINT
   儲存數字值，包括整數和浮點數。可以選擇精度和刻度方式，文法：
number[（[,]）]。
預設的精度是38,scale是0.
   
PLS_INTEGER
   -2147483647-2147483647
    
   與BINARY_INTEGER基本相同，但採用機器運算時，PLS_INTEGER提供更好的效能 。
   
  
　　表2 字元資料類型  
  
datatype
   rang
   subtype
   description
   
CHAR
   最大長度32767位元組  
   CHARACTER
   儲存定長字串，如果長度沒有確定，預設是1
   
LONG
   最大長度2147483647位元組
    
   儲存可變長度字串
   
RAW  
   最大長度32767位元組
    
   用於儲存位元據和位元組字串，當在兩個資料庫之間進行傳遞時，RAW資料不在字元集之間進行轉換。
   
LONGRAW
   最大長度2147483647
    
   與LONG資料類型相似，同樣他也不能在字元集之間進行轉換。
   
ROWID
   18個位元組
    
   與資料庫ROWID偽列類型相同，能夠儲存一個行標示符，可以將行標示符看作資料庫中每一行的唯一索引值。
   
VARCHAR2
   最大長度32767位元組
   STRINGVARCHAR
   與VARCHAR資料類型相似，儲存可變長度的字串。聲明方法與VARCHAR相同  
   
  
　　表3 DATE和BOOLEAN
  
datatype
   range  
   description
   
BOOLEAN
   TRUE/FALSE
   儲存邏輯值TRUE或FALSE,無參數
   
DATE
   01/01/4712 BC  
   儲存固定長的日期和時間值，日期值中包含時間
LOB資料類型
  
　　LOB(大對象,Large object) 資料類型用於儲存類似映像，聲音這樣的大型資料對象，LOB資料對象可以是位元據也可以是字元資料，其最大長度不超過4G。LOB資料類型支援任意訪問方式，LONG只支援順序訪問方式。LOB儲存在一個單獨的位置上，同時一個"LOB定位器"(LOB locator)儲存在原始的表中，該定位器是一個指向實際資料的指標。在PL/SQL中操作LOB資料對象使用ORACLE提供的包DBMS_LOB.LOB資料類型可分為以下四類：
  
　　. BFILE
　　. BLOB
　　. CLOB
　　. NCLOB
  
　　操作符
  
　　與其他程式設計語言相同，PL/SQL有一系列操作符。操作符分為下面幾類：
  
　　. 算術操作符
  
　　. 關係操作符
  
　　. 比較操作符
  
　　. 邏輯操作符
  
　　算術操作符如表4所示
  
operator
   operation
   
+   加
   -   減
   /   除
   
*   乘  
** 乘方
   
  
　　關係操作符主要用於條件判斷語句或用於where子串中，關係操作符檢查條件和結果是否為true或false,表5是PL/SQL中的關係操作符
  
operator
   operation
   
<   <