當前位置：首頁 > 运维知识 > 数据库 >内容正文

数据库

PL/SQL教程

發布時間：2024/9/5 数据库 34 豆豆

生活随笔收集整理的這篇文章主要介紹了 PL/SQL教程小編覺得挺不錯的,現在分享給大家,幫大家做個參考.

PL/SQL教程

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程序塊可以是一個命名的程序塊也可以是一個匿名程序塊。匿名程序塊可以用在服務器端也可以用在客戶端。

　　命名程序塊可以出現在其他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
<	小于操作符
<=	小于或等于操作符
>	大于操作符
>=	大于或等于操作符
=	等于操作符
!=	不等于操作符
<>	不等于操作符
:=	賦值操作符

　　表6 顯示的是比較操作符

operator	operation
IS NULL	如果操作數為NULL返回TRUE
LIKE	比較字符串值
BETWEEN	驗證值是否在范圍之內
IN	驗證操作數在設定的一系列值中

　　表7.8顯示的是邏輯操作符

operator	operation
AND	兩個條件都必須滿足
OR	只要滿足兩個條件中的一個
NOT	取反

　　執行部分

　　執行部分包含了所有的語句和表達式,執行部分以關鍵字BEGIN開始，以關鍵字EXCEPTION結束，如果EXCEPTION不存在，那么將以關鍵字END結束。分號分隔每一條語句，使用賦值操作符:=或SELECT INTO或FETCH INTO給每個變量賦值，執行部分的錯誤將在異常處理部分解決，在執行部分中可以使用另一個PL/SQL程序塊，這種程序塊被稱為嵌套塊

　　所有的SQL數據操作語句都可以用于執行部分，PL/SQL塊不能再屏幕上顯示SELECT語句的輸出。SELECT語句必須包括一個INTO子串或者是游標的一部分，執行部分使用的變量和常量必須首先在聲明部分聲明，執行部分必須至少包括一條可執行語句，NULL是一條合法的可執行語句，事物控制語句COMMIT和ROLLBACK可以在執行部分使用，數據定義語言(Data Definition language)不能在執行部分中使用，DDL語句與EXECUTE IMMEDIATE一起使用或者是DBMS_SQL調用。

　　執行一個PL/SQL塊

　　SQL*PLUS中匿名的PL/SQL塊的執行是在PL/SQL塊后輸入/來執行，如下面的例子所示：

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>

　　命名的程序與匿名程序的執行不同，執行命名的程序塊必須使用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>

　　如果在另一個命名程序塊或匿名程序塊中執行這個程序，那么就不需要EXECUTE關進字。

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>

控制結構

　　控制結構控制PL/SQL程序流程的代碼行,PL/SQL支持條件控制和循環控制結構。

　　語法和用途

　　　IF..THEN

　　語法：

IF condition THEN
　Statements 1;
　Statements 2;
　....
END IF

　　IF語句判斷條件condition是否為TRUE，如果是，則執行THEN后面的語句，如果condition為false或NULL則跳過THEN到END IF之間的語句，執行END IF后面的語句。

　　IF..THEN...ELSE

　　語法:

IF condition THEN
　Statements 1;
　Statements 2;
　....
ELSE
　Statements 1;
　Statements 2;
　....
END IF

　　如果條件condition為TRUE,則執行THEN到ELSE之間的語句，否則執行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則執行statement1,然后執行statement5,否則判斷condition2是否為TRUE,若為TRUE則執行statement2,然后執行statement5,對于condition3也是相同的，如果condition1，condition2，condition3都不成立，那么將執行statement4,然后執行statement5。

　　循環控制

　　循環控制的基本形式是LOOP語句，LOOP和END LOOP之間的語句將無限次的執行。LOOP語句的語法如下：

　　LOOP
　　　statements;
　　END LOOP

　　LOOP和END LOOP之間的語句無限次的執行顯然是不行的，那么在使用LOOP語句時必須使用EXIT語句，強制循環結束，例如：

X:=100;
LOOP
　X:=X+10;
　IF X>1000 THEN
　　EXIT;
　END IF
END LOOP;
Y:=X;

　　此時Y的值是1010.

　　EXIT WHEN語句將結束循環，如果條件為TRUE，則結束循環。

X:=100;
LOOP
X:=X+10;
EXIT WHEN X>1000;
X:=X+10;
END LOOP;
Y:=X;

　　WHILE..LOOP

　　WHILE..LOOP有一個條件與循環相聯系，如果條件為TRUE,則執行循環體內的語句，如果結果為FALSE,則結束循環。

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循環的循環次數都是不確定的，FOR循環的循環次數是固定的，counter是一個隱式聲明的變量，他的初始值是start_range,第二個值是start_range+1,直到end_range,如果start_range等于end _range,那么循環將執行一次。如果使用了REVERSE關鍵字，那么范圍將是一個降序。

X:=100;
FOR v_counter in 1..10 loop
x:=x+10;

end loop
y:=x;

　　如果要退出for循環可以使用EXIT語句。

　　標簽

　　用戶可以使用標簽使程序獲得更好的可讀性。程序塊或循環都可以被標記。標簽的形式是<>。

　　標記程序塊

<>
[DECLARE]
... ... ...
BEGIN
........
[EXCEPTION]
.......
END label_name

　　標記循環

<>
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;

　　執行GOTO語句時，控制會立即轉到由標簽標記的語句。PL/SQL中對GOTO語句有一些限制，對于塊、循環、IF語句而言，從外層跳轉到內層是非法的。

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是一個合法的可執行語句。

　　嵌套

　　程序塊的內部可以有另一個程序塊這種情況稱為嵌套。嵌套要注意的是變量，定義在最外部程序塊中的變量可以在所有子塊中使用，如果在子塊中定義了與外部程序塊變量相同的變量名，在執行子塊時將使用子塊中定義的變量。子塊中定義的變量不能被父塊引用。同樣GOTO語句不能由父塊跳轉道子塊中，反之則是合法的。

《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;

　　小結

　我們在這篇文章中介紹了PL/SQL的基礎語法以及如何使用PL/SQL語言設計和運行PL/SQL程序塊，并將PL/SQL程序整合到Oracle服務器中,雖然PL/SQL程序作為功能塊嵌入Oracle數據庫中，但PL/SQL與ORACLE數據庫的緊密結合使得越來越多的Oracle數據庫管理員和開發人員開始使用PL/SQL。

轉載于:https://www.cnblogs.com/builderman/articles/851765.html

總結

以上是生活随笔為你收集整理的PL/SQL教程的全部內容，希望文章能夠幫你解決所遇到的問題。

如果覺得生活随笔網站內容還不錯，歡迎將生活随笔推薦給好友。

上一篇： [转]GridView 72般绝技
下一篇：进站公交车碾起积水溅上轿车两男子驾车撞伤