PL/SQL

計算機程序語言

PL/SQL也是一種程序語言，叫做過程化SQL語言（Procedural Language/SQL）。PL/SQL是Oracle資料庫對SQL語句的擴展。在普通SQL語句的使用上增加了編程語言的特點，所以PL/SQL就是把數據操作和查詢語句組織在PL/SQL代碼的過程性單元中，通過邏輯判斷、循環等操作實現複雜的功能或者計算的程序語言。

目錄
1基本概念 2變數 3基本語法
4執行操作 過程 函數
管理子程序 開發包 異常處理
5Example 6類似語言

基本概念

是Oracle對標準資料庫語言SQL的過程化擴充，它將資料庫技術和過程化程序設計語言聯繫起來，是一種應用開發語言，可使用循環，分支處理數據，將SQL的數據操縱功能與過程化語言數據處理功能結合起來.PL/SQL的使用，使SQL成為一種高級程序設計語言，支持高級語言的塊操作，條件判斷，循環語句，嵌套等，與資料庫核心的數據類型集成，使SQL的程序設計效率更高.
1、PL/SQL的作用
使用PL/SQL可以編寫具有很多高級功能的程序，雖然通過多個SQL語句可能也能實現同樣的功能，但是相比而言，PL/SQL具有更為明顯的一些優點：
⒈能夠使一組SQL語句的功能更具模塊化程序特點；
⒉採用了過程性語言控制程序的結構；
⒊可以對程序中的錯誤進行自動處理，使程序能夠在遇到錯誤的時候不會被中斷；
⒋具有較好的可移植性，可以移植到另一個Oracle資料庫中；
⒌集成在資料庫中，調用更快；
⒍減少了網路的交互，有助於提高程序性能。
通過多條SQL語句實現功能時，每條語句都需要在客戶端和服務端傳遞，而且每條語句的執行結果也需要在網路中進行交互，佔用了大量的網路帶寬，消耗了大量網路傳遞的時間，而在網路中傳輸的那些結果，往往都是中間結果，而不是我們所關心的。
而使用PL/SQL程序是因為程序代碼存儲在資料庫中，程序的分析和執行完全在資料庫內部進行，用戶所需要做的就是在客戶端發出調用PL/SQL的執行命令，資料庫接收到執行命令后，在資料庫內部完成整個PL/SQL程序的執行，並將最終的執行結果反饋給用戶。在整個過程中網路里只傳輸了很少的數據，減少了網路傳輸佔用的時間，所以整體程序的執行性能會有明顯的提高。
2、PL/SQL程序的基本結構
PL/SQL塊由四個基本部分組成：塊頭、聲明單元、執行單元、異常處理單元。
下面是四個部分的基本結構：
HEADER——可選部分
只有命名塊才有的單元，塊頭單元就確定了這個命名塊或者程序的調用方式。
DECLARE——可選部分
變數、常量、游標，以及可以被後面執行單元或者異常處理單元引用的子塊。
……
BEGIN——必要部分
SQL語句和PL/SQL語句構成的執行程序
……
EXCEPTION——可選部分
程序出現異常時，捕捉異常並處理異常
……
END；——必須部分
一個完整的塊結構應該是這樣的：
Header
IS
Declaration Section
BEGIN
Execution Section
EXCEPTION
Exception Section
END;
在資料庫執行PL/SQL程序時，PL/SQL語句和SQL語句是分別進行解析和執行的。PL/SQL塊被資料庫內部的PL/SQL引擎提取，將SQL語句取出送給Oracle的SQL引擎處理，兩種語句分別在兩種引擎中分析處理，在資料庫內部完成數據交互、處理過程。

變數

就像其他的程序語言一樣，變數是在程序中出現最頻繁的名詞，在PL/SQL中的學習中首先需要了解變數的一些基本概念和使用方法。
PL/SQL
PL/SQL
PL/SQL程序包括了四個部分，在四個部分中，聲明部分主要用來聲明變數並且初始化變數，在執行部分可以為變數賦新值，或者在表達式中引用變數的值，在異常處理部分同樣可以按執行部分的方法使用變數。另外，在PL/SQL程序使用時可以通過參數變數把值傳遞到PL/SQL塊中，也可以通過輸出變數或者參數變數將值傳出PL/SQL塊。
在定義變數、常量標識符時需要注意下面的一些基本規則：
⒈定義的標識符名稱應該遵循命名規則，在後面將會提到主要的命名規則；
⒉在聲明常量和變數的時候可以為其設置初始化值，也可以強制設置not null；
⒊可以使用賦值運算符（：=）或DEFAULT保留字來初始化標識符，為標識符賦初始值；
⒋在聲明標識符時，每行只能聲明一個標識符。
在PL/SQL中主要使用下面三種類型的變數（或者常量）：
⒈簡單變數；
⒉複合（組合）變數；
⒊外部變數。
三種變數分別用於存放不同特性的數據。

基本語法

在寫PL/SQL語句時，必須遵循一些基本的語法，下面是PL/SQL程序代碼的基本語法要求：
PL/SQL
PL/SQL
⒈語句可以寫在多行，就像SQL語句一樣；
⒉各個關鍵字、欄位名稱等等，通過空格分隔；
⒊每條語句必須以分號結束，包括PL/SQL結束部分的END關鍵字後面也需要分號；
⒋標識符需要遵循相應的命名規定；
⑴名稱最多可以包含30個字元；
⑵不能直接使用保留字，如果需要，需要使用雙引號括起來；
⑶第一個字元必須以字母開始；
⑷不要用資料庫的表或者科學計數法表示；
還有一些語法相關的規則：
⒈在PL/SQL程序中出現的字元值和日期值必須用單引號括起；
⒉數字值可以使用簡單數字或者科學計數法表示；
⒊在程序中最好養成添加註釋的習慣，使用註釋可以使程序更清晰，使開發者或者其他人員能夠很快的理解程序的含義和思路。在程序中添加註釋可以採用：
⑴之間的多行註釋；
⑵以--開始的單行註釋。

執行操作

過程：執行特定操作
函數：用於返回特定數據

過程

語法:create [orreplace] procedure procedure_name(argument1 [model]datatype1,argment2 [mode2],...)
is[as]
pl/sql block;
1.建立過程：不帶任何參數
create or replace procedure out_time
is
begin
dbms_output.put_line(systimestamp);
end;
2.調用過程
set serveroutput on
exec out_time
set serveroutput on
call out_time();
3.建立過程：帶有IN參數
CREATE OR REPLACE PROCEDURE add_employee(eno NUMBER,
NAME VARCHAR2,
sal NUMBER,
job VARCHAR2 DEFAULT 'clerk',
dno NUMBER) IS
e_integrity EXCEPTION;
PRAGMA EXCEPTION_INIT(e_integrity,-2291);
BEGIN
INSERT intoimp
(empno,
ename,
sal,
job,
deptno)
VALUES
(eno,
NAME,
sal,
job,
dno);
EXCEPTION
WHEN dup_val_on_index THEN
raise_application_error(-20000,'僱員號不能重複');
WHEN e_integrity THEN
raise_application_error(-20001,'部門不存在');
END add_employee;
4.建立過程：帶有OUT參數
create or replaceprocedure qry_employee
(eno number,name outvarchar2,salary out number)
is
begin
selectename,sal into name,salary from emp where empno=eno;
exception
whenno_date_found then
raise_application_error(-20000,'該僱員不存在');
end;
當在應用程序中調用該過程時，必須要定義變數接受輸出參數的數據
sql>var name varchar2(10)
var salary number
exec qry_employee(7788,:name,:salary)
print name salary
5.建立過程：帶有INOUT參數(輸入輸出參數)
create or replaceprocedure compute
(num1 in outnumber,num2 in out number)
is
v1number;
v2number;
begin
v1:num1/num2;
v2:mod(num1,num2);
num1:=v1;
num2:=v2;
end;
sql>var n1 number
var n2 number
exec :n1:=100
exec :n2:=30
exec ecmpute(:n1,:n2)
print n1 n2
6.為參數傳遞變數和數據
位置傳遞，名稱傳遞，組合傳遞三種
1.位置傳遞：在調用子程序時按照參數定義的順序為參數指定相應的變數或數值
exec add_dept(40,'sales','new york');
exec add_dept(10);
2.名稱傳遞：在調用子程序時指定參數名，並使用關聯符號=>為其提供相應的數值或變數
execadd_dept(dname=>'sales',dno=>50);
exec add_dept(dno=>30);
3.組合傳遞：同時使用位置傳遞和名稱傳遞
exec add_dept(50,loc=>'new york');
execadd_dept(60,dname=>'sales',loc=>'newyork');
7.查看過程原代碼
oracle會將過程名，源代碼以及其執行代碼存放到數據字典中。執行時直接按照其執行代碼執行
可查詢數據字典(user_source)
select textfrom user_source where name='add_dept';
刪除過程
dropprocedure add_dept;

函數

用於返回特定函數
語法:create [orreplace] function function_name
(argument1 [mode1] datatype1,
argument2 [mode2] datatype2,
.....)
returndatatype --函數頭部必須要帶有RETURN子句，至少要包含一條RETURN語句
is|as pl/sql block;
1.建立函數：不帶任何參數
create or replacefunction get_user
return varchar2
is
v_uservarchar2(100);
begin
selectusername into v_user from user_users;
returnv_user;
end;
2.使用變數接受函數返回值
sql>var v1 varchar2(100)
exec :v1:=get_user
print v1
在SQL語句中直接調用函數
selectget_user from d l;
使用DBMS_OUTPUT調用函數
setserveroutput on
execdbms_output.put_line('當前資料庫用戶:'||ger_user)
3.建立函數：帶有IN參數
create orreplace function get_sal(name in varchar2)
returnnumber
as
v_sal emp.sal%type;
begin
select sal into v_sal from emp where upper(ename)=upper(name);
return v_sal;
exception
when no_data_found then
raise_application_error(-20000,'該僱員不存在');
end;
4.建立函數：帶有out參數
create or replacefunction get_info(name varchar2,title out varchar2)
return varchar2
as
deptnamedept.dname%type;
begin
selecta.job,b.dname into title,deptname from emp a,dept b anda.deptno=b.deptno
andupper(a.ename)=upper(name);
returndeptname
exception
whenno_data_found then
raise_application_error(-20000,'該僱員不存在');
end;
sql>var job varchar2(20)
var dname varchar2(20)
exec :dname:=get_info('scott',:job)
print danme job
5.建立函數：帶有INOUT參數
create or replacefunction result(num1 number,num2 in out number)
return number
as
v_resultnumber(6);
v_remaindernumber;
begin
v_result:=num1/num2;
v_remainder:=mod(num1,num2);
num2:=v_remainder;
returnv_result;
exception
whenzero_divide then
raise_application_error(-20000,'不能除0');
end;
sql>var result1 number
var result2 number
exec :result2:=30
exec :result1:=result(100,:result2)
print result result2
6.函數調用限制
SQL語句中只能調用存儲函數(伺服器端),而不能調用客戶端的函數
SQL只能調用帶有輸入參數，不能帶有輸出，輸入輸出函數
SQL不能使用PL/SQL的特有數據類型(boolean,table,record等)
SQL語句中調用的函數不能包含INSERT,UPDATE和DELETE語句
7.查看函數源代碼
oracle會將函數名及其源代碼信息存放到數據字典中user_source
set pagesize 40
select text fromuser_source where name='result';
8.刪除函數
drop functionresult;

管理子程序

1.列出當前用戶的子程序
數據字典視圖USER_OBJECTS用於顯示當前用戶所包含的所有對象.(表，視圖，索引，過程，函數，包)
sql>col object_name format a20
select object_name,created,status from user_objects whereobject_type in ('procedure','function')
2.列齣子程序源代碼
select text fromuser_source where name='raise_salsry';
3.列齣子程序編譯錯誤
使用SHOWERRORS命令確定錯誤原因和位置
show errorsprocedure raise_salary
使用數據字典視圖USER_ERRORS確定錯誤原因和位置
col text formata50
selectline||'/'||position as "line/col",text error from user_errors wherename='raise_salary';
4.列出對象依賴關係
使用數據字典視圖USER_DEPENDENCIES確定直接依賴關係
select name,typefrom user_dependencies where referenced_name='emp';
使用工具視圖DEPTREE和IDEPTREE確定直接依賴和間接依賴關係
先運行SQL腳本UTLDTREE.SQL來建立這兩個視圖和過程DEPTREE_FILL,然後調用DEPTREE_FILL填充這兩個視圖
sql>@%oracle_home%\rdbms\admin\utldtree
exec deptree_fill('TABLE','scott','emp')
執行後會將直接或間接依賴於SCOTT.EMP表的所有對象填充到視圖DEPTREE和IDEPTREE中.
select nested_level,name,type from deptree;
select * from ideptree
5.重新編譯子程序
當修改了被引用對象的結構時，就會將相關依賴對象轉變為無效(INVALID)狀態。
alter table emp addremark varchar2(10);
selectobject_name,object_type from user_objects wherestatus='invalid';
為了避免子程序的運行錯誤，應該重新編譯這些存儲對象
alter procedureadd_employee compile;
alter view dept10compile;
alter functionget_info compile;

開發包

包用於邏輯組合相關的PL/SQL類型，項和子程序，由包規範和包體組成
1.建立包規範：包規範是包與應用程序之間的介面，用於定義包的公用組件，包括常量，變數，游標，過程，函數等
create [or replace]package package_name
is|as
p lic type and item declarations
s program specificationsend package_name;
create or replacepackage emp_package is
g_deptnonumber(3):=30;
procedureadd_employee(eno number,name varchar2,salary number,dno numberdefault g_deptno);
procedurefire_employee(eno number);
functionget_sal(eno number) return number;
end emp_package;
2.建立包體：用於實現包規範所定義的過程和函數
create [or replace]package body package_name
is|as
private type and item declarations
s program bodies
endpackage_name;
create or repalce package body emp_package is
functionvalidate_deptno(v_deptno number)
return boolean
is
v_temp int;
begin
select 1 into v_temp from dept where deptno=v_deptno;
return tr;
exception
when no_date_found then
return false;
end;
procedure add_employee(eno number,name varchar2,salary number,dnonumber default g_deptno)
is
begin
if validate_deptno(dno) then
insert into emp(empno,ename,sal,deptno)vals(eno,name,salsry,dno);
else
raise_application_error(-20010,'不存在該部門');
end if;
exception
when dup_val_on_index then
raise_application_error(-20012,'該僱員已存在');
end;
procedure fire_employee(eno number) is
begin
delete from emp where empno=eno;
if sql%notfound then
raise_application_error(-20012,'該僱員不存在');
end if;
end;
functionget_sal(eno number) return number
is
v_sal emp.sal%type;
begin
select sal into v_sal from emp where empno=eno;
return v_sal;
exception
whenno_data_found then
raise_application_error(-20012,'該僱員不存在');
end;
end emp_package;
3.調用包組件
3.1在同一個包內調用包組件
create or replace package body emp_package is
procedure add_employee(eno number,name va har2,salary number,dnonumber default g_deptno)
is
begin
ifvalidate_deptno(dno) then
insert into emp(empno,ename,sal,deptno)vals(eno,name,salary,dno);
else
raise_application_error(-20010,'該部門不存在')
end if;
exception
when dup_val_on_index then
raise_application_error(-20011,'該僱員已存在')
end;
.........
3.2調用包公用變數
execemp_package.g_deptno:=20
3.3調用包公用過程
execemp_package.add_employee(1111,'mary',2000)
3.4調用包公用函數
var salarynumber
exec:salary:=emp_package.get_sal(7788)
print salary
3.5以其他用戶身份調用包公用組件
connsystem/manager
execscott.emp_package.add_employee(1115,'scott',1200)
execscott.emp_package.fire_employee(1115)
3.6調用遠程資料庫包的公用組件
execemp_package.add_employee@orasrv(1116,'scott',1200)
4.查看源代碼：存放在數據字典USER_SCOURCE中
select text fromuser_source where name='emp-package' and type='package';
5.刪除包
drop packageemp_package;
6.使用包重載
重載(overload)是指多個具有相同名稱的子程序
1.建立包規範
同名的過程和函數必須具有不同的輸入參數，同名函數返回值的數據類型必須完全相同
create or replacepackage overload is
functionget_sal(eno number) return number;
functionget_sal(name varchar2) return number;
procedurefile_employee(eno number);
procedurefile_employee(name varchar2);
end;
2.建立包體
必須要給不同的重載過程和重載函數提供不同的實現代碼
create or replacepackage body overload is
function get_sal(eno number) return number
is
v_sal emp.sal%type;
begin
select sal into v_sal from emp where empno=eno;
return v_sal;
exception
when no_data_found then
raise_application_error(-20020,'該僱員不存在');
end;
function get_sal(name varchar2) return number
is
v_sal emp.sal%type;
begin
select sal into v_sal from emp where upper(ename)=upper(name);
return v_sal;
exception
when no_data_found then
raise_application_error(-20020,'該僱員不存在');
end;
procedure fire_employee(eno number) is
begin
delete from emp where empno=no;
if sql%notfound then
raise_application_error(-20020,'該僱員不存在');
end if;
end;
procedurefire_employee(name varchar2) is
begin
delete from emp where upper(ename)=upper(name);
if sql%notfound then
raise_application_error(-20020,'該僱員不存在');
end if;
end;
end;
3.調用重載過程和重載函數
var sal1 number
var sal2 number
exec:sal1:=overload.get_sal('scott')
exec:sal2:=overload.get_sal(7685)
execoverload.fire_employee(7369)
execoverload.fire_employee('scott')
7.使用包構造過程
類似於高級語言中的構造函數和構造方法
1.建立包規範
包的構造過程用於初始化包的全局變數.
create or replacepackage emp_package is
minsalnumber(6,2);
maxsalnumber(6,2);
procedure add_employee(eno number,name varchar2,salary number,dnonumber);
procedureupd_sal(eno number,salary number);
procedureupd_sal(name varchar2,salary number);
end;
2.建立包體
包的構造過程沒有任何名稱，它是實現了包的其他過程后，以BEGIN開始,END結束的部分
create or replace package body emp_package is
procedure add_employee(eno number,name varchar2,salary number,dno number)
is
begin
if salarybetween minsal and maxsal then
insert into emp (empno,ename,sal,deptno)vals(eno,name,salary,dno);
else
raise_application_error(-20001,'工資不在範圍內');
end if;
exception
when dup_val_on_index then
raise_application_error(-20002,'該僱員已經存在');
end;
procedureupd_sal(eno number,salary number) is
begin
if salary between minsal and maxsal then
update emp set sal=salary where empno =eno;
if sql%notfound then
raise_application_error(-20003,'不存在僱員號');
end if;
else
raise_application_errpr(-20001,'工資不在範圍內');
end if;
end;
procedure upd_sal(name varchar2,salary number) is
begin
if salary between minsal and maxsal then
update emp set sal=salary where upper(ename)=upper(name);
if sql%notfound then
raise_application_error(-20004,'不存在該僱員名');
end if;
else
raise_application_error(-20001,'工資不在範圍內');
end if;
end;
begin
selectmi(sal),max(sal) into minsal,maxsal from emp ;
end;
調用包公用組件：構造過程只調用一次
execemp_package.add_employee(1111,'mary',3000,20)
execemp_package.upd_sal('mary',2000)
8.使用純度級別
在SQL中引用包的公用函數，該公用函數不能包含DML語句(insert,update,delete),也不能讀寫遠程包的變數
為了對包的公用函數加以限制，在定義包規範時，可以使用純度級別(purity level)限制公用函數
語法:pragmarestrict_references (function_name,wnds[,wnps][,rnds][,rnps]);
wnds:用於限制函數不能修改資料庫數據(禁止DML)
wnps:用於限制函數不能修改包變數(不能給包變數賦值)
rnds:用於限制函數不能讀取資料庫數據(禁止SELECT操作)
rnps:用於限制函數不能讀取包變數(不能將包變數賦值給其他變數)
1.建立包規範
create or replacepackage purity is
minsalnumber(6,2);
maxsalnumber(6,2);
functionmax_sal return number;
functionmin_sal return number;
pragmarestrict_references(max_sal,wnps);--不能修改
pragmarestrict_references(min_sal,wnps);
end;
2.建立包體
create or replacepackage body purity is
function max_sal return number
is
begin
return maxsal;
end;
functionmin_sal return number
is
begin
return minsal;
end;
begin
select min(sal),max(sal) into minsal,maxsal from emp;
end;
3.調用包的公用函數
var minsal number
var maxsal number
exec :minsal:=purity.minsal()
exec :maxsal:=purity.maxsal()
print minsal maxsal

異常處理

PL/SQL處理異常不同於其他程序語言的錯誤管理方法，PL/SQL的異常處理機制與ADA很相似，有一個處理錯誤的全包含方法。當發生錯誤時，程序無條件轉到異常處理部分，這就要求代碼要非常乾淨並把錯誤處理部分和程序的其它部分分開。oracle允許聲明其他異常條件類型以擴展錯誤/異常處理。這種擴展使PL/SQL的異常處理非常靈活。
當一個運行時錯誤發生時，稱為一個異常被拋出。PL/SQL程序編譯時的錯誤不是能被處理得異常，只有在運行時的異常能被處理。在PL/SQL程序設計中異常的拋出和處理是非常重要的內容。

Example

DECLARE
salary emp.sal%TYPE:=0;
mgr_num emp.mgr%TYPE;
last_name emp.ename%TYPE;
starting_empno emp.empno%TYPE:=7499;
BEGIN
SELECT mgr INTO mgr_num FROM emp
WHERE empno=starting_empno;
WHILE salary<=2500 LOOP
SELECT sal,mgr,ename INTO salary,mgr_num,last_name
FROM emp WHERE empno=mgr_num;
END LOOP;
INSERT INTO temp VALUES (NULL,salary,last_name);
COMMIT;
EXCEPTION
WHEN NO_DATA_FOUND THEN
INSERT INTO temp VALUES (NULL,NULL,'Not found');
COMMIT;
END;

類似語言

功能近似PL/SQL的程序語言和其他關係型資料庫：
SybaseASE、Microsoft SQL Server的Transact-SQL，PostgreSQL資料庫的PL/pgSQL（模仿PL/SQL）和IBM DB2的SQL PL，都符合ISOSQL的SQL/PSM標準。

基本信息

中文名
過程化SQL語言
外文名
Procedural Language/SQL
作用
採用了過程性語言控制程序的結構
作用2
集成在資料庫中，調用更快
類別
程序語言
簡稱
PL/SQL