PL/SQL

這是對Oracle-SQL知識點(diǎn)詳細(xì)介紹的文章系列阻逮，其他文章如下：

• Oracle-SQL系列知識點(diǎn)(一)
• Oracle-SQL系列知識點(diǎn)(二)
• Oracle-SQL系列知識點(diǎn)(三)

PL/SQL基礎(chǔ)知識

• 什么是PL/SQL檀轨？

PL/SQL是由Oracle開發(fā)恳不，專用于Oracle的一種程序設(shè)計語言受扳；

PL代表 Procedural Language萧诫；

SQL代表Structured Query Language；

PL/SQL是Oracle數(shù)據(jù)庫對SQL語句的擴(kuò)展，增加了編程語言的特點(diǎn)锄列；

PL/SQL 包含過程化語句和SQL語句數(shù)據(jù)操作和查詢語句被包含在PL/SQL代碼的程序單元中（PL/SQL塊）,經(jīng)過邏輯判斷、循環(huán)等操作完成復(fù)雜的功能或者計算.

• PL/SQL的優(yōu)點(diǎn)

改善了性能

PL/SQL以整個語句塊發(fā)送給服務(wù)器惯悠，這個過程在單次調(diào)用中完成，降低了網(wǎng)絡(luò)擁擠竣况。而如果不使PL/SQL克婶，每條SQL語句都有單獨(dú)的傳輸交互，在網(wǎng)絡(luò)環(huán)境下占用大量的服務(wù)器時間丹泉，同時導(dǎo)致網(wǎng)絡(luò)擁擠情萤。

可重用性

PL/SQL能運(yùn)行在任何ORACLE環(huán)境中（不論它的操作系統(tǒng)和平臺）何乎，在其他ORACLE能夠運(yùn)行的操作系統(tǒng)上無需修改代碼忙厌。

模塊化

每個PL/SQL單元可以包含一個或多個程序塊，程序中的每一塊都實(shí)現(xiàn)一個邏輯操作隘蝎，從而把不同的任務(wù)進(jìn)行分割晒哄，由不同的塊來實(shí)現(xiàn)睁宰，塊之間可以是獨(dú)立的或是嵌套的。

1477182319341.png

• PL/SQL塊 的類型

匿名塊：一般在要運(yùn)行的應(yīng)用中說明寝凌，運(yùn)行時傳遞給PL/SQL引擎處理柒傻，只能執(zhí)行一次，不能被存儲在數(shù)據(jù)庫中较木。

過程红符，函數(shù)和包（Procedure，F(xiàn)unction & Package）：是命名的PL/SQL塊伐债，被存儲在數(shù)據(jù)庫中预侯，能夠被多次執(zhí)行，可以用外部程序來顯示執(zhí)行峰锁。

觸發(fā)器（Trigger）：是命名的PL/SQL塊萎馅，被存儲在數(shù)據(jù)庫中，能夠被多次執(zhí)行虹蒋，當(dāng)相應(yīng)的觸發(fā)事件發(fā)生時自動被執(zhí)行校坑；

• PL、SQL塊的組成

PL/SQL語言以塊為單位千诬，塊中可以嵌套子塊耍目。

一個基本的PL/SQL塊由3部分組成：

定義部分（DECLARE)

PL/SQL中使用的變量、常量徐绑、游標(biāo)和異常處理的名字都必須先定義后使用邪驮。并且必須定義在以DECLARE關(guān)鍵字開頭的定義部分。

可執(zhí)行部分（BEGIN）

PL/SQL塊的主體傲茄，包含該塊的可執(zhí)行語句毅访。該部分定義了塊的功能沮榜，是必須的。 由關(guān)鍵字BEGIN開始喻粹，以END結(jié)束蟆融。

異常處理部分（EXCEPTION）

該部分包含塊的異常處理程序（錯誤處理程序）。當(dāng)該塊程序體中的某個語句出現(xiàn)異常（檢測到一個錯誤）時守呜，oracle將程序控制轉(zhuǎn)到異常部分的相應(yīng)的異常處理程序中進(jìn)行進(jìn)一步的處理型酥。該部分由關(guān)鍵字EXCEPTION開始，END關(guān)鍵字結(jié)束

1477182602911.png

• PL/SQL語句的處理

1477182651274.png

• PL/SQL中變量

PL/SQL中可使用標(biāo)識符來聲明變量查乒、常量弥喉、游標(biāo)、用戶定義的異常等玛迄，并在SQL語句或過程化的語句中使用由境。

標(biāo)識符的命名和Oracle對數(shù)據(jù)庫對象的命名原則相同。

至多有30個字符蓖议；

不能是保留字虏杰；

必須以字母開頭；

包含字母勒虾、$嘹屯、_、# 从撼、數(shù)字符號州弟；

對標(biāo)識符的命名最好遵循相關(guān)命名規(guī)范,不建議與數(shù)據(jù)庫中；

表的列名相同低零；

1477182743997.png

• 變量的類型

1婆翔、PL/SQL變量

標(biāo)量型(只能存儲單值、內(nèi)部沒有分量）

標(biāo)量數(shù)據(jù)類型:

容納單個值,內(nèi)部沒有分量
CHAR [(maximum_length)]掏婶，
VARCHAR2 (maximum_length)（在程序中的大小32767）啃奴，
DATE，
NUMBER [(precision, scale)]雄妥，
BINARY_INTEGER最蕾，
PLS_INTEGER，
BOOLEAN老厌，
BINARY_FLOAT瘟则，
BINARY_DOUBLE

復(fù)合型；
引用型枝秤；
LOB型 (大型的對象)醋拧；

2、外部變量(非PL/SQL變量)

表單應(yīng)用程序中的屏幕域；
SQL*Plus 主機(jī)變量丹壕；

• 聲明變量

1477182932062.png

1庆械、變量的命名規(guī)則與SQL的規(guī)則基本相同，即每個標(biāo)識符必須以字母開頭菌赖，而且不分大小寫缭乘。

2、使用NOT NULL約束條件定義變量時琉用，必須為變量賦予一個值

3堕绩、在每行上聲明一個變量，使代碼更易于閱讀和維護(hù)
在常量聲明中辕羽，關(guān)鍵字CONSTANT必須位于類型指定符之前，必須被初始化

4垄惧、使用賦值運(yùn)算符(:=)或DEFAULT保留字將變量初始化

5刁愿、在PL/SQL中使用的變量、常量到逊、游標(biāo)和異常處理的名字都必須先聲明后使用铣口。

6、聲明部分是包括在關(guān)鍵字DECLARE和BEGIN之間的部分觉壶，每條語句之后用‘脑题；’結(jié)束

7、數(shù)據(jù)庫中列名與變量名不建議相同

• 變量賦值

方式一：

1477183058963.png

方式二：

另外一種為變量賦值的方式是從數(shù)據(jù)庫中選取值賦給變量铜靶。語法：

SELECT  column  INTO  variable
FROM  table  
WHERE  condition;

• %TYPE 的屬性

通過%TYPE屬性聲明一個變量叔遂，實(shí)際上就是參照變量或者表中字段的類型作為變量的類型，并且保持同步争剿。變量將遵循下面的類型聲明：

• 已經(jīng)聲明過的變量類型
• 數(shù)據(jù)庫中表的字段類型

可以作為%TYPE前綴的可以是

• 表名.列名
• 前面聲明的變量名稱

PL/SQL在運(yùn)行程序時確定變量的數(shù)據(jù)類型和大小

• PL/SQL 塊的代碼注釋

/* 和*/之間的多行注釋已艰；

單行注釋，以 －－ 開始蚕苇；

• 事務(wù)控制語句

事務(wù)開始于COMMIT或ROLLBACK后的第一個DML語句哩掺；

使用 COMMIT 和 ROLLBACK 語句來終止一個事務(wù)；

在事務(wù)處理過程中使用SAVEPOINT 來標(biāo)記中間點(diǎn)涩笤；

編寫控制結(jié)構(gòu)

• IF語句

1477183403324.png

任何包含空值的算術(shù)表達(dá)式結(jié)果均為空值

• case語句

CASE語句可以根據(jù)條件從多個執(zhí)行分支中選擇相應(yīng)的執(zhí)行動作嚼吞，并能返回一個值；

CASE selector
    WHEN expression1 THEN result1
    WHEN expression2 THEN result2...
    WHEN expressionN THEN resultN
    [ELSE resultN+1]
END;

• 循環(huán)控制: LOOP 語句

LOOP 語句多次執(zhí)行語句序列

LOOP 語句有三種形式:

Basic loop 無條件的循環(huán)

1477183570534.png

FOR loop 有計數(shù)的循環(huán)

1477183591633.png

WHILE loop 有條件的循環(huán)

1477183613350.png

EXIT 是強(qiáng)制結(jié)束循環(huán)的語句

游標(biāo)

• 游標(biāo)的定義

游標(biāo)（cursor）是Oracle系統(tǒng)在內(nèi)存中開辟的一個工作區(qū)蹬碧，在其中存放SELECT語句返回的查詢結(jié)果

• 游標(biāo)的分類

隱式游標(biāo)：PL/SQL隱式建立并自動管理這一游標(biāo)

顯式游標(biāo):由程序員顯式說明及控制舱禽，用于從表中取出多行數(shù)據(jù)，并將多行數(shù)據(jù)一行一行單獨(dú)處理

• 隱式游標(biāo)

由Oracle在內(nèi)部聲明
由Oracle自行管理游標(biāo)
可以使用游標(biāo)屬性從最近執(zhí)行的SQL語句中獲取信息
用于處理DML語句以及返回單行的查詢

屬性：

1477183763601.png

• 顯式游標(biāo)

由用戶顯式聲明恩沽，查詢返回多行記錄

1呢蔫、使用游標(biāo)時，select語句查詢的結(jié)果可以是單條記錄，多條記錄片吊，也可以是零條記錄绽昏。
2、游標(biāo)工作區(qū)中俏脊，存在著一個指針（POINTER）,在初始狀態(tài)它指向查詢結(jié)果的首記錄全谤。
3、要訪問查詢結(jié)果的所有記錄爷贫，可以通過FETCH語句认然，進(jìn)行指針的移動來實(shí)現(xiàn)。
4漫萄、使用游標(biāo)進(jìn)行操作卷员，包括定義游標(biāo)、打開游標(biāo)腾务、提取數(shù)據(jù)以及關(guān)閉游標(biāo)幾步毕骡。

1477183897298.png

1477183919880.png

屬性：

1477183949276.png

• 游標(biāo)的聲明

1477183986474.png

在游標(biāo)聲明中， SELECT子查詢不能使用INTO子句岩瘦。
如果需要按指定的次序處理行未巫，可在查詢中使用ORDER子句。

• 打開游標(biāo)

1477184040480.png

使用游標(biāo)之前應(yīng)首先打開游標(biāo)启昧；

打開游標(biāo)叙凡，實(shí)際上是執(zhí)行游標(biāo)定義時對應(yīng)的SELECT語句，將查詢結(jié)果檢索到工作區(qū)中密末。

如果沒有要返回的行握爷，不會出現(xiàn)異常；

當(dāng)執(zhí)行 OPEN 語句時严里，并不將活動集中的行賦
給變量饼拍，而是在執(zhí)行 FETCH 語句時才從活動集中提取一行；

• 從游標(biāo)中提取數(shù)據(jù)

1477184109474.png

1田炭、在使用FETCH語句之前必須先打開游標(biāo)师抄，這樣才能保證工作區(qū)中有數(shù)據(jù)。

2教硫、對游標(biāo)第一次使用FETCH語句時叨吮，游標(biāo)指針指向第一條記錄，因此操作的對象是第一條記錄瞬矩，使用后茶鉴，游標(biāo)指針指向下一條記錄。

3景用、游標(biāo)指針只能向下移動涵叮，不能回退惭蹂。如果想查完第二條記錄后又回到第一條記錄，則必須關(guān)閉游標(biāo)割粮，然后重新打開游標(biāo)盾碗。

4、INTO子句中的變量個數(shù)舀瓢、順序廷雅、數(shù)據(jù)類型必須與工作區(qū)中每行記錄的字段數(shù)、順序以及數(shù)據(jù)類型一一對應(yīng)京髓。

• 關(guān)閉游標(biāo)

1477184157817.png

游標(biāo)一旦關(guān)閉航缀，不可再從游標(biāo)中提取數(shù)據(jù)
當(dāng)關(guān)閉了游標(biāo)后，所有和該游標(biāo)相關(guān)的資源都會被釋放堰怨；

• 游標(biāo)和記錄

將提取的行值存入一個PL/SQL RECORD 中能方便地處理活動集中的行芥玉。

1477184193739.png

• 游標(biāo)處理中的FOR循環(huán)

1477184226658.png

游標(biāo)式的 FOR 循環(huán)可以更方便地處理顯式游標(biāo)。
隱式地打開备图、提取和關(guān)閉游標(biāo)灿巧。
隱式聲明記錄類型變量。

• 不需聲明的游標(biāo)

1477184268376.png

• 帶有參數(shù)的游標(biāo)

1477184294444.png

• FOR UPDATE 子句

1477184391684.png

在事務(wù)執(zhí)行期間可以顯式鎖定以拒絕訪問诬烹。
在更新或刪除行時要鎖定該行砸烦。

• WHERE CURRENT OF 子句

1477184413309.png

異常處理

什么是異常?

Oracle中出現(xiàn)錯誤的情形通常分為編譯時錯誤（compile-time error）和運(yùn)行時錯誤(run-time error)弃鸦，在PL/SQL運(yùn)行過程中出現(xiàn)的警告或錯誤绞吁，當(dāng)發(fā)生異常時，塊就會停止執(zhí)行唬格，但是可以轉(zhuǎn)到指定異常處理機(jī)部分繼續(xù)執(zhí)行家破。

異常是如何觸發(fā)的?

隱式觸發(fā)：發(fā)生了一個 Oracle 錯誤時，oracle自動觸發(fā)一個異常购岗。
顯示觸發(fā)：程序員可以使用RAISE語句顯式觸發(fā)異常汰聋。

如何處理異常?

捕捉異常：用處理機(jī)截獲
傳遞異常：在調(diào)用環(huán)境中傳播異常

• 異常的類型

1477184579001.png

• 捕獲異常

語法：

1477184597613.png

1、在異常部分WHEN 子句沒有數(shù)量限制
2喊积、WHEN OTHERS 是最后一個子句
3烹困、異常處理部分從關(guān)鍵字EXCEPTION開始
4、當(dāng)異常拋出后乾吻，控制無條件轉(zhuǎn)到異常處理部分
5髓梅、在離開塊之前只能執(zhí)行一種異常處理

• 預(yù)定義異常

1477184675282.png

• 非預(yù)定義異常

1477184729795.png

例子：

1477184740699.png

• 捕獲異常的函數(shù)

1477184791399.png

• 用戶定義異常

1477184838710.png

例子：

1477184855280.png

Raise_Application_Error過程

1、用于創(chuàng)建用戶定義的錯誤消息的過程
2绎签、向用戶返回錯誤枯饿，并且其返回格式和其它Oracle錯誤的格式相同
3、既可以在可執(zhí)行部分中使用诡必，也可以在異常部分中使用

Raise_Application_Error(error_number,message);

4奢方、錯誤編號必須介于 –20000 和 –20999 之間
5、錯誤消息的長度可長達(dá) 2048 個字節(jié)

例子：

1477184947648.png

存儲過程

命名的PL/SQL塊
能夠接受參數(shù)
能夠被重復(fù)調(diào)用
用于執(zhí)行某項(xiàng)操作
存儲在數(shù)據(jù)庫中

• 創(chuàng)建過程

1477185032850.png

1、REPLACE選項(xiàng)指示如果過程存在蟋字，它將被刪除并且用語句創(chuàng)建的新版本代替
2稿蹲、在IS之后，聲明本地變量愉老，不需要使用DECLARE開始聲明
3场绿、PL/SQL 塊，既可以用BEGIN開始也可以用局部變量的聲明開始嫉入，既可以用END結(jié)束也可以用END procedure_name 結(jié)束焰盗；

• 過程的參數(shù)

1477185109292.png

1477185132766.png

• IN參數(shù)示例

1477185158041.png

• OUT參數(shù)示例

1477185174128.png

• IN OUT參數(shù)示例

1477185187503.png

• 傳遞參數(shù)的方法

位置：實(shí)際參數(shù)與形式參數(shù)排列的順序相同
指定：實(shí)際參數(shù)聯(lián)合其相應(yīng)的形式參數(shù)以任意順序排列
組合：實(shí)際參數(shù)的排列一些用位置，一些用指定

• 處理異常

1477185291915.png

• 刪除過程

1477185322554.png

• Oracle-SQL系列知識點(diǎn)(一)
• Oracle-SQL系列知識點(diǎn)(二)
• Oracle-SQL系列知識點(diǎn)(三)