Oracle—PL/SQL 基礎知識

什麼是PL/SQL

•PL/SQL是由Oracle開發，專用於Oracle的一種程式設計語言•PL代表 ProceduralLanguage•SQL代表StructuredQuery Language•PL/SQL是Oracle資料庫對SQL語句的擴充，增加了程式設計語言的特點•PL/SQL 包含過程化語句和SQL語句•資料操作和查詢語句被包含在PL/SQL代碼的程式單元中（PL/SQL塊）,經過邏輯判斷、迴圈等操作完成複雜的功能或者計算.
PL/SQL 塊

•PL/SQL是塊結構化語言(程式能被分成若干個邏輯塊)•PL/SQL塊的組成–PL/SQL語言以塊為單位，塊中可以嵌套子塊。–一個基本的PL/SQL塊由3部分組成：

  定義部分（DECLARE）

  可執行部分（BEGIN）

  異常處理部分（EXCEPTION）

PL/SQL塊的定義部分

  與其它語言一樣，PL/SQL中使用的變數、常量、遊標和異常處理的名字都必須先定義後使用。並且必須定義在以DECLARE關鍵字開頭的定義部分。

PL/SQL塊的可執行部分

  該部分是PL/SQL塊的主體，包含該塊的可執行語句。該部分定義了塊的功能，是必須的。由關鍵字BEGIN開始，以END結束。

PL/SQL塊的異常處理部分

  該部分包含塊的例外處理常式（錯誤處理程式）。當該塊程式體中的某個語句出現異常（檢測到一個錯誤）時，oracle將程式控制轉到異常部分的相應的例外處理常式中進行進一步的處理。該部分由關鍵字EXCEPTION開始，END關鍵字結束。

•DECLARE– 可選–變數、常量、遊標、使用者自訂的特殊類型•BEGIN –必須–SQL 陳述式–PL/SQL 陳述式•EXCEPTION– 可選–錯誤發生時的處理動作•END; – 必須

DECLARE<br /> v_variable VARCHAR2(5);<br />BEGIN<br /> SELECTcolumn_name<br /> INTOv_variable<br /> FROMtable_name;<br />EXCEPTION<br /> WHEN exception_name THEN<br /> ...<br />END;<br />

PL/SQL的優點

•改善了效能

PL/SQL以整個語句塊發送給伺服器，這個過程在單次調用

中完成，降低了網路擁擠。而如果不使用PL/SQL，每條

SQL語句都有單獨的傳輸互動，在網路環境下佔用大量的

伺服器時間，同時導致網路擁擠。

•可重用性

PL/SQL能運行在任何ORACLE環境中（不論它的作業系統

和平台），在其他ORACLE能夠啟動並執行作業系統上無需修改

代碼。

•模組化

  每個PL/SQL單元可以包含一個或多個程式塊，程式中的每一塊都實現一個邏輯操作，從而把不同的任務進行分割，由不同的塊來實現，塊之間可以是獨立的或是嵌套的。

PL/SQL塊的類型

–匿名塊：一般在要啟動並執行應用中說明，運行時傳遞給PL/SQL引擎處理，只能執行一次，不能被儲存在資料庫中。–過程，函數和包（Procedure，Function& Package）：是命名的PL/SQL塊，被儲存在資料庫中，能夠被多次執行，可以用外部程式來顯示執行。–觸發器（Trigger）：是命名的PL/SQL塊，被儲存在資料庫中，能夠被多次執行，當相應的觸發事件發生時自動被執行。

PL/SQL中變數

•PL/SQL中可使用標識符來聲明變數、常量、遊標、使用者定義的異常等，並在SQL語句或過程化的語句中使用。•標識符的命名和Oracle對資料庫物件的命名原則相同。–至多有30個字元–不能是保留字–必須以字母開頭–不允許與資料庫中表的列名相同–包含字母、$、_、數字記號•對標識符的命名最好遵循相關命名規範

PL/SQL中變數

純量資料型別:

•容納單個值•內部沒有分量•分為四個類別：–數字型

  number、binary_integer等

–字元型

  char、varchar2、long、RAW等

–日期型

  date、timestamp等

–布爾型

  true、false、null

VARCHAR2(最大長度）:變長字元的基本類型，最大的儲存資料是32767位元組。VARCHAR2型的變數和常量沒有預設的長度。

CHAR[(最大長度)]:  固定長度字元資料的基本類型，最大有32767位元組。如果沒有指定的最大值，預設值為1。

LONG  變長字串的基本類型，最大長度是32k位元組。

LONGRAW 變長字串，最多可以儲存32k位元組的位元據。

NCHAR  使用由國家字元集指定的字元集

BINARY_INTEGER:介於-2147483647和2147483647之間整數的基本類型。

PLS_INTEGER:介於-2147483647和2147483647之間有符號的整數的基本類型。與NUMBER 和 BINARY_INTEGER相比，PLS_INTEGER佔有較少的儲存空間且運行效率高。

RAW儲存定長的位元據，最大長度儲存32k位元組

LONG型和 VARCHAR2非常相似，不過LONG型的最大長度是32760位元組，比VARCHAR2少了7個位元組。

LONGRAW:位元據和位元組字串的基本類型，最大位元組為32760。PL/SQL不能解釋 LONGRAW 型的資料。

聲明 PL/SQL變數

舉例

Declare<br /> v_hiredateDATE;<br /> v_deptnoNUMBER(2) NOT NULL := 10;<br /> v_locationVARCHAR2(13) := ‘Shenyang';<br /> c_tax_rateCONSTANT NUMBER(3,2) := 8.25;<br /> v_validBOOLEAN NOT NULL := TRUE;<br />

•變數的命名規則與SQL的規則基本相同，即每個標識符必須以字母開頭，而且不分大小寫。•使用NOT NULL約束條件定義變數時，必須為變數賦予一個值•在每行上聲明一個變數，使代碼更易於閱讀和維護•在常量聲明中，關鍵字CONSTANT必須位於類型指定符之前，必須被初始化•使用賦值運算子(:=)或DEFAULT保留字將變數初始化•在PL/SQL中使用的變數、常量、遊標和異常處理的名字都必須先聲明後使用。•聲明部分是包括在關鍵字DECLARE和BEGIN之間的部分，每條語句之後用‘；’結束。

–不同塊的變數可以同名–資料庫中列名應與變數名有截然不同的名字

DECLARE<br /> v_jobemp.job%TYPE;<br /> empno emp.empno%TYPE:=7369;<br />BEGIN<br /> SELECT job<br /> INTO v_job<br /> FROM emp<br /> WHERE empno = empno;<br />END;<br />
變數的賦值

文法

identifier := expr;<br />

另外一種為變數賦值的方式是從資料庫中選取值賦給變數。文法：

SELECT  column INTO  variable

FROM  table 

WHERE  condition;

SELECT 語句執行後，將出現下列情況之一：

• 只檢索了一行• 檢索了多行• 不檢索任何行

只有當檢索一行時，SELECT才成功操作。

%TYPE 的屬性

•通過%TYPE屬性聲明一個變數，實際上就是參照變數或者表中欄位的類型作為變數的類型，並且保持同步。變數將遵循下面的型別宣告：–已經聲明過的變數類型  –資料庫中表的欄位類型•可以作為%TYPE首碼的可以是–資料庫表和列–前面聲明的變數名稱•PL/SQL在運行程式時確定變數的資料類型和大小

...<br /> v_enameemp.ename%TYPE;<br /> v_balanceNUMBER(7,2);<br /> v_min_balancev_balance%TYPE := 10;<br />...<br />

使用%TYPE 屬性的好處：

–在編程時，可以不去查詢資料庫中欄位的資料類型–資料庫中欄位的資料類型可能被改變–為了和前面的變數類型始終保持一致•要在PL/SQL中引用綁定變數，需在該變數名前加上一個冒號 (:)。•舉例

VARIABLE g_salary NUMBER</p><p>DECLARE<br /> v_salemp.sal%TYPE;<br />BEGIN<br /> SELECTsal<br /> INTOv_sal<br /> FROMemp<br /> WHEREempno = 7369;<br /> :g_salary := v_sal;<br />END;<br />/<br />
DBMS_OUTPUT.PUT_LINE

•使用setserveroutput on 命令設定環境變數serveroutput為開啟狀態，從而使得PL/SQL程式能夠在SQL*plus中輸出結果。

DECLARE<br />v_var varchar2(10);<br />BEGIN<br />SELECT ename into v_var<br />FROM emp<br />WHERE empno=7369;<br />DBMS_OUTPUT.PUT_LINE('僱員名='||v_var);<br />END;<br />

•注釋可以是–/* 和*/之間的多行注釋–單行注釋，以 －－ 開始

PL/SQL中的SQL語句

•使用SELECT命令從資料庫中選取資料•使用DML命令更新資料庫中行•使用COMMIT,ROLLBACK或SAVEPOINT 命令控制事務。•使用隱式遊標屬性確定DML的輸出•關鍵字END標記著PL/SQL塊的結束，不代表事務的結束，一個塊可以跨越多個事務，一個事務能夠跨越多個塊•PL/SQL不直接支援DDL

SELECT:

SELECT select_list<br />INTO {variable_name[, variable_name]...<br /> | record_name}<br />FROM table<br />WHERE condition;<br />

•必須使用INTO子句•查詢必須並且只能返回一行

INSERTBEGIN<br />INSERT INTO emp<br />(empno,ename,job,mgr,hiredate,sal)<br />VALUES(<br />1000,'ljs','manager',1100,sysdate,4000);<br />END;<br />/<br />

UPDATE:DECLARE<br />v_salemp.sal%type := 800;<br />BEGIN<br />UPDATE emp<br />SET sal = sal + v_sal<br />WHEREjob = 'ANALYST';<br />END;<br />/
DELETE:DECLARE<br />v_deptnoemp.deptno%type := 10;<br />BEGIN<br />DELETE FROM emp<br />WHERE deptno = v_deptno;<br />END;<br />/<br />