PL/SQL 基礎
一、PL/SQL程式設計
1.1 PL/SQL概述
1.1.1 PL/SQL塊結構
PL/SQL程式都是以塊(BLOCK）為基本單位
語法結構：
[DECLEAR]
--宣告部分，可選
BEGIN
--執行部分，必須
[EXCEPTION]
--異常處理部分，可選
END;
每一條語句必須以分號結束。
SQL> set serveroutput on
SQL> declare
2 a int:=100;
3 b int:=200;
4 c number;
5 begin
6 c:=(a+b)/(a-b);
7 dbms_output.put_line(c);
8 exception
9 when zero_divide then
10 dbms_output.put_line('除數不許為零!');
11 end;
12 /
-3
PL/SQL procedure successfully completed.
1.1.2 程式碼註釋和識別符號
1、單行註釋
由兩個連字元“--”開始
SQL> declare
2 Num_sal number; --宣告一個數值變數
3 Var_ename varchar2(20); --宣告一個字串變數
4 begin
5 select ename,sal into Var_ename,Num_sal from emp
6 where empno=7369; --檢索指定的值並儲存到變數中
7 dbms_output.put_line(Var_ename||'的工資是'||Num_sal);
8 end;
2、多行註釋
由/*開頭，由*/結尾
3、PL/SQL字符集
1.2 資料型別和定義變數和常量
1.2.1 基本資料型別
1、數值型別
number,pls_integer,binary_integer
number(p,s)
Num_Money number(9,2);
2、字元型別
varchar2,char,long,nchar,nvarchar2
varchar2(maxlength)
char(maxlength)
3、日期型別
date
儲存空間是7個位元組，分別使用一個位元組儲存世紀，年，月，天，小時，分鐘和秒。
4、布林型別
boolean
變數值可以是TRUE,FALSE和NULL
1.2.2 特殊資料型別
1、%type型別
宣告一個與指定列名稱相同的資料型別，緊跟在指定列名的後面。
var_job emp.job%type;
SQL> conn scott/tiger
Connected.
SQL> set serveroutput on
SQL> declare
2 var_ename emp.ename%type;
3 var_job emp.job%type;
4 begin
5 select ename,job into var_ename,var_job
6 from emp
7 where empno=7369;
8 dbms_output.put_line(var_ename||'的職務是
9 '||var_job);
10 end;
11 /
SMITH的職務是
CLERK
PL/SQL procedure successfully completed.
由於into子句中的變數只能儲存一個單獨的值，所以要求select子句只能返回一行資料。
2、record型別
定義record型別的語法格式：
type record_type_name is record
(
var_member1 data_type [not null][:=default_value],
...
var_member date_type [not null][:=default_value])
declare
type emp_type is record
(
var_name varchar2(20),
var_job varchar2(20),
var_sal number
);
empinfo emp_type;
begin
select ename,job,sal
info empinfo
from emp
where empno=7369;
dems_output.put_line('僱員'||empinfo.var_ename||'的職務是'||empinfo.var_job||',工資是'||empinfo.var_sal);
end;
SQL> set serveroutput on
SQL> declare
2 type emp_type is record
3 (
4 var_ename varchar2(20),
5 var_job varchar2(20),
6 var_sal number
7 );
8 empinfo emp_type;
9 begin
10 select ename,job,sal
11 into empinfo
12 from emp
13 where empno=7369;
14 dbms_output.put_line('僱員'||empinfo.var_ename||'的職務是'||empinfo.var_job||',工資是'||empinfo.var_sal);
15 end;
16 /
僱員SMITH的職務是CLERK,工資是2758.33
PL/SQL procedure successfully completed.
3、%rowtype型別
儲存從資料表中檢索到的一行資料。
語法結構：
rowVar_name table_name%rowtype;
SQL> set serveroutput on
SQL> declare
2 rowvar_emp emp%rowtype;
3 begin
4 select *
5 into rowvar_emp
6 from emp
7 where empno=7369;
8 dbms_output.put_line('僱員'||rowvar_emp.ename||'的編號是'||rowvar_emp.empno||',職務是'||rowVar_emp.job);
9 end;
10 /
僱員SMITH的編號是7369,職務是CLERK
PL/SQL procedure successfully completed.
1.2.3 定義變數和常量
1、定義變數
語法格式：
<變數名><資料型別>[(長度）:=<初始值>];
var_countryname varchar2(50):='中國'；
2、定義常量
語法格式：
<常量名>constant<資料型別>:=<常量值>;
con_day constant integer:=365;
1.3 流程控制語句
1.3.1 選擇語句
1、if...then語句
語法格式：
ifthen
plsql_sentence
end if;
if last_name is null then
...
end if;
編輯器中編輯如下：
declare
var_name1 varchar2(50);
var_name2 varchar2(50);
begin
var_name1:='east';
var_name2:='xiaoke';
if length(var_name1)<LENGTH(VAR_NAME2) then
dbms_output.put_line('字串"'||var_name1||'"的長度比字串"'||var_name2||'"的長度小');
end if;
end;
/
SQL> set serveroutput on
SQL>
字串"east"的長度比字串"xiaoke"的長度小
PL/SQL procedure successfully completed
2、if...then...else語句
語法格式：
ifthen
plsql_sentence1；
else
plsql_sentence2；
end if;
declare
age int:=55;
begin
if age>=56 then
dbms_output.put_line('您可以申請退休了！');
else
dbms_output.put_line('您小於56歲，不可以申請退休了！');
end if;
end;
/
SQL>
您小於56歲，不可以申請退休了！
PL/SQL procedure successfully completed
3、if...then...elsif語句
語法格式：
ifthen
plsql_sentence1；
elsifthen
plsql_sentence2；
...
else
plsql_sentencen；
end if;
declare
month int:=10;
begin
if month>=0 and month <=3 then
dbms_output.put_line('這是春季');
elsif month>=4 and month <=6 then
dbms_output.put_line('這是夏季');
elsif month>=7 and month <=9 then
dbms_output.put_line('這是秋季');
elsif month>=10 and month <=12 then
dbms_output.put_line('這是冬季');
else
dbms_output.put_line('對不起，月份不合法！');
end if;
end;
/
SQL>
這是冬季
PL/SQL procedure successfully completed
4、case語句
語法格式：
case
whenthen plsql_sentence_1;
whenthen plsql_sentence_2;
...
whenthen plsql_sentence_n;
[else plsql_sentence;]
end case;
declare
season int:=3;
aboutinfo varchar2(50);
begin
case season
when 1 then
aboutinfo:=season||'季度包括1,2,3月份';
when 2 then
aboutinfo:=season||'季度包括4,5,6月份';
when 3 then
aboutinfo:=season||'季度包括7,8,9月份';
when 4 then
aboutinfo:=season||'季度包括10,11,12月份';
else
aboutinfo:=season||'季節不合法';
end case;
dbms_output.put_line(aboutinfo);
end;
/
SQL>
3季度包括7,8,9月份
PL/SQL procedure successfully completed
1.3.2 迴圈語句
1、loop語句
程式至少執行一次迴圈體
語法格式：
loop
plsql_sentence;
exit when end_condition_exp;
end loop;
declare
sum_i int:=0;
i int:=0;
begin
loop
i:=i+1;
sum_i:=sum_i+i;
exit when i=100;
end loop;
dbms_output.put_line('前100個自然數的和是：'||sum_i);
end;
/
SQL>
前100個自然數的和是：5050
PL/SQL procedure successfully completed
2、while語句
語法格式：
while condition_expression loop
plsql_sentence;
end loop;
declare
sum_i int:=0;
i int:=0;
begin
while i<=99 loop
i:=i+1;
sum_i:=sum_i+i;
end loop;
dbms_output.put_line('前100個自然數的和是：'||sum_i);
end;
/
SQL>
前100個自然數的和是：5050
PL/SQL procedure successfully completed
3、for語句
語法格式：
for variable_counter in [reverse] lower_limit..upper_limit loop
plsql_sentence;
end loop;
declare
sum_i int:=0;
begin
for i in 1..100 loop
if mod(i,2)=0 then
sum_i:=sum_i+i;
end if;
end loop;
dbms_output.put_line('前100個自然數中偶數之和是：'||sum_i);
end;
/
SQL>
前100個自然數中偶數之和是：2550
PL/SQL procedure successfully completed
1.4 PL/SQL遊標
遊標的作用相當於指標
顯式遊標和隱式遊標
1.4.1 顯式遊標
由使用者宣告和操作的一種遊標，通常用於操作查詢結果集。
處理資料的步驟：宣告遊標，開啟遊標，讀取遊標和關閉遊標。
遊標每次只能讀取一行資料
1、宣告遊標
語法格式：
cursor cur_name[(input_parameter[,input_parameter2]...)]
[return ret_type]
is select_sentence;
ret_type：執行遊標操作後的返回值型別。
select_sentence：遊標所使用的select語句，為遊標的反覆讀取提供結果集。
input_parameter1：作為遊標的輸入引數，指定使用者在開啟遊標後向遊標中傳遞的值，該引數的定義和初始化格式如下：
para_name [in] datatype[{:=|default}para_value]
定義遊標應該放在PL/SQL塊得declare部分。
declare
cursor cur_emp(var_job in varchar2:='SALESMAN')
is select empno,ename,sal
from emp
where job=var_job;
2、開啟遊標
語法格式：
open cur_name[(para_value1[,para_value2]...)]'
3、讀取遊標
語法格式：
fetch cur_name into {variable};
declare
cursor cur_emp(var_job in varchar2:='SALESMAN')
is select empno,ename,sal
from emp
where job=var_job;
type record_emp is record
(
var_empno emp.empno%type,
var_ename emp.ename%type,
var_sal emp.sal%type
);
emp_row record_emp;
begin
open cur_emp('MANAGER');
fetch cur_emp into emp_row;
while cur_emp%found loop
dbms_output.put_line(emp_row.var_ename||'的編號是'||emp_row.var_empno||'，工資是'||emp_row.var_sal);
fetch cur_emp into emp_row;
end loop;
close cur_emp;
end;
/
SQL>
JONES的編號是7566，工資是2975
BLAKE的編號是7698，工資是2850
CLARK的編號是7782，工資是2450
PL/SQL procedure successfully completed
4、關閉遊標
語法格式：
close cur_name;
1.4.2 遊標的屬性
%found：布林型屬性，如果SQL語句至少影響到一行資料，則該屬性為true，否則為false。
%notfound：布林型屬性，與%fonud相反。
%rowcount：數字型屬性，返回受SQL語句影響的行數。
%isopen：布林型屬性，當遊標已經開啟時返回true，遊標關閉時則為false。
declare
var_ename varchar2(50);
var_job varchar2(50);
cursor cur_emp
is select ename,job
from emp
where empno=7499;
begin
open cur_emp;
fetch cur_emp into var_ename,var_job;
if cur_emp%found then
dbms_output.put_line('編號是7499的僱員名稱為：'||var_ename||'，的職務是：'||var_job);
else
dbms_output.put_line('無資料記錄');
end if;
end;
/
SQL>
編號是7499的僱員名稱為：ALLEN，的職務是：SALESMAN
PL/SQL procedure successfully completed
1.4.3 隱式遊標
begin
update emp
set sal=sal*(1+0.2)
where job='SALESMAN';
if sql%notfound then
dbms_output.put_line('沒有僱員需要上調工資');
else
dbms_output.put_line('有'||sql%rowcount||'個僱員工資上調20%');
end if;
end;
/
SQL>
有4個僱員工資上調20%
PL/SQL procedure successfully completed
1.4.4 透過for語句迴圈遊標
（1）在for語句中遍歷隱式遊標中的資料時，通常在關鍵字“in”的後面提供由select語句檢索的結果集，在檢索結果集的過程中，oracle系統會自動提供一個隱式的遊標sql
begin
for emp_record in (select empno,ename,sal from emp where job='SALESMAN')
loop
dbms_output.put('僱員編號： '||emp_record.empno);
dbms_output.put('；僱員名稱： '||emp_record.ename);
dbms_output.put_line('；僱員工資： '||emp_record.sal);
end loop;
end;
/
SQL>
僱員編號： 7499；僱員名稱： ALLEN；僱員工資： 1920
僱員編號： 7521；僱員名稱： WARD；僱員工資： 1500
僱員編號： 7654；僱員名稱： MARTIN；僱員工資： 1500
僱員編號： 7844；僱員名稱： TURNER；僱員工資： 1800
PL/SQL procedure successfully completed
（2）在for語句中遍歷顯式遊標中的資料時，通常在關鍵字“in”的後面提供遊標的名稱
語法格式：
for var_auto_record in cur_name
loop
plsql sentence;
end loop;
declare
cursor cur_emp is
select * from emp
where deptno=30;
begin
for emp_record in cur_emp
loop
dbms_output.put('僱員編號： '||emp_record.empno);
dbms_output.put('；僱員名稱： '||emp_record.ename);
dbms_output.put_line('；僱員職務： '||emp_record.job);
end loop;
end;
/
SQL>
僱員編號： 7499；僱員名稱： ALLEN；僱員職務： SALESMAN
僱員編號： 7521；僱員名稱： WARD；僱員職務： SALESMAN
僱員編號： 7654；僱員名稱： MARTIN；僱員職務： SALESMAN
僱員編號： 7698；僱員名稱： BLAKE；僱員職務： MANAGER
僱員編號： 7844；僱員名稱： TURNER；僱員職務： SALESMAN
僱員編號： 7900；僱員名稱： JAMES；僱員職務： CLERK
PL/SQL procedure successfully completed
在使用遊標（包括顯式和隱式）的for迴圈中，可以宣告遊標，但不要進行開啟遊標，讀取遊標和關閉遊標等操作，這些由oracle系統內部自動完成。
1.5 PL/SQL異常處理
oracle系統異常分為以下兩大類：
預定義異常
自定義異常
1.5.1 預定義異常
|
命名的系統異常 |
產生原因 |
|
ACCESS_INTO_NULL |
未定義物件 |
|
CASE_NOT_FOUND |
CASE 中若未包含相應的 WHEN ，並且沒有設定 ELSE 時 |
|
COLLECTION_IS_NULL |
集合元素未初始化 |
|
CURSER_ALREADY_OPEN |
遊標已經開啟 |
|
DUP_VAL_ON_INDEX |
唯一索引對應的列上有重複的值 |
|
INVALID_CURSOR |
在不合法的遊標上進行操作 |
|
INVALID_NUMBER |
內嵌的 SQL 語句不能將字元轉換為數字 |
|
NO_DATA_FOUND |
使用 select into 未返回行，或應用索引表未初始化的元素時 |
|
TOO_MANY_ROWS |
執行 select into 時，結果集超過一行 |
|
ZERO_DIVIDE |
除數為 0 |
|
SUBSCRIPT_BEYOND_COUNT |
元素下標超過巢狀表或 VARRAY 的最大值 |
|
SUBSCRIPT_OUTSIDE_LIMIT |
使用巢狀表或 VARRAY 時，將下標指定為負數 |
|
VALUE_ERROR |
賦值時，變數長度不足以容納實際資料 |
|
LOGIN_DENIED |
PL/SQL 應用程式連線到 oracle 資料庫時，提供了不正確的使用者名稱或密碼 |
|
NOT_LOGGED_ON |
PL/SQL 應用程式在沒有連線 oralce 資料庫的情況下訪問資料 |
|
PROGRAM_ERROR |
PL/SQL 內部問題，可能需要重灌資料字典＆ pl./SQL 系統包 |
|
ROWTYPE_MISMATCH |
宿主遊標變數與 PL/SQL 遊標變數的返回型別不相容 |
|
SELF_IS_NULL |
使用物件型別時，在 null 物件上呼叫物件方法 |
|
STORAGE_ERROR |
執行 PL/SQL 時，超出記憶體空間 |
|
SYS_INVALID_ID |
無效的 ROWID 字串 |
|
TIMEOUT_ON_RESOURCE |
Oracle 在等待資源時超時 |
|
錯誤號 |
異常錯誤資訊名稱 |
說明 |
|
ORA-0001 |
Dup_val_on_index |
違反了唯一性限制 |
|
ORA-0051 |
Timeout-on-resource |
在等待資源時發生超時 |
|
ORA-0061 |
Transaction-backed-out |
由於發生死鎖事務被撤消 |
|
ORA-1001 |
Invalid-CURSOR |
試圖使用一個無效的遊標 |
|
ORA-1012 |
Not-logged-on |
沒有連線到ORACLE |
|
ORA-1017 |
Login-denied |
無效的使用者名稱/口令 |
|
ORA-1403 |
No_data_found |
SELECT INTO沒有找到資料 |
|
ORA-1422 |
Too_many_rows |
SELECT INTO 返回多行 |
|
ORA-1476 |
Zero-divide |
試圖被零除 |
|
ORA-1722 |
Invalid-NUMBER |
轉換一個數字失敗 |
|
ORA-6500 |
Storage-error |
記憶體不夠引發的內部錯誤 |
|
ORA-6501 |
Program-error |
內部錯誤 |
|
ORA-6502 |
Value-error |
轉換或截斷錯誤 |
|
ORA-6504 |
Rowtype-mismatch |
宿主遊標變數與 PL/SQL變數有不相容行型別 |
|
ORA-6511 |
CURSOR-already-OPEN |
試圖開啟一個已處於開啟狀態的遊標 |
|
ORA-6530 |
Access-INTO-null |
試圖為null 物件的屬性賦值 |
|
ORA-6531 |
Collection-is-null |
試圖將Exists 以外的集合( collection)方法應用於一個null pl/sql 表上或varray上 |
|
ORA-6532 |
Subscript-outside-limit |
對巢狀或varray索引得引用超出宣告範圍以外 |
|
ORA-6533 |
Subscript-beyond-count |
對巢狀或varray 索引得引用大於集合中元素的個數 |
declare
var_empno number;
var_ename varchar2(50);
begin
select empno,ename into var_empno,var_ename
from emp
where deptno=10;
if sql%found then
dbms_output.put_line('僱員編號： '||var_empno||'；僱員名稱： '||var_ename);
end if;
exception
when too_many_rows then
dbms_output.put_line('返回記錄超過一行');
when no_data_found then
dbms_output.put_line('無資料記錄');
end;
/
SQL>
返回記錄超過一行
PL/SQL procedure successfully completed
1.5.2 自定義異常
1、錯誤編號異常
declare
primary_iterant exception;
pragma exception_init(primary_iterant,-00001);
begin
insert into dept values(10,'軟體開發部','深圳');
exception
when primary_iterant then
dbms_output.put_line('主鍵不允許重複！');
end;
/
SQL>
主鍵不允許重複！
PL/SQL procedure successfully completed
2、業務邏輯異常
declare
null_exception exception;
dept_row dept%rowtype;
begin
dept_row.deptno:=66;
dept_row.dname:='公關部';
insert into dept
values(dept_row.deptno,dept_row.dname,dept_row.loc);
if dept_row.loc is null then
raise null_exception;
end if;
exception
when null_exception then
dbms_output.put_line('loc欄位的值不許為null');
rollback;
end;
/
SQL>
loc欄位的值不許為null
PL/SQL procedure successfully completed
二、過程、函式、觸發器和包
2.1 儲存過程
儲存過程是一種命名的PL/SQL程式塊，既可以沒有引數，也可以有若干個輸入、輸出引數，甚至可以有多個既作輸入又作輸出的引數，但它通常沒有返回值。儲存過程被儲存在資料庫中，他不可以被SQL語句直接執行或呼叫，只能透過EXECUTE命令執行或在PL/SQL程式塊內部呼叫。
語法格式：
create [or replace] procedure pro_name [(parameter1[,parameter2]...)] is|as
begin
plsql_sentences;
[exception]
[dowith_sentences;]
end [pro_name];
create or replace procedure pro_insertDept is
begin
insert into dept values(77,'市場擴充部','JILIN');
commit;
dbms_output.put_line('插入新紀錄成功！');
end;
/
SQL>
Procedure created
SQL> execute pro_insertDept;
插入新紀錄成功！
PL/SQL procedure successfully completed
SQL> select * from dept;
DEPTNO DNAME LOC
------ -------------- -------------
10 ACCOUNTING NEW YORK
20 RESEARCH DALLAS
30 SALES CHICAGO
40 OPERATIONS BOSTON
77 市場擴充部 JILIN
create or replace procedure pro_insertDept is
begin
delete from dept where deptno=77;
insert into dept values(77,'市場擴充部','JILIN');
commit;
dbms_output.put_line('插入新紀錄成功！');
end;
/
SQL>
Procedure created
begin
pro_insertDept;
end;
/
SQL>
插入新紀錄成功！
PL/SQL procedure successfully completed
2.2 儲存過程的引數
1、IN模式引數
預設引數模式，引數的型別不能指定長度
create or replace procedure insert_dept(
num_deptno in number,
var_ename in varchar2,
var_loc in varchar2) is
begin
insert into dept
values(num_deptno,var_ename,var_loc);
commit;
end insert_dept;
（1）指定名稱傳遞
向儲存過程傳遞引數時需要指定引數名稱，即引數名稱在左側，中間是賦值符號“=>”，右側是引數值。
傳遞引數值與引數的定義順序無關，但與引數個數有關。
語法格式：
pro_name(parameter1=>values1[,parameter2=>value2]...)
begin
insert_dept(var_ename=>'採購部',var_loc=>'成都',num_deptno=>15);
end;
/
SQL>
PL/SQL procedure successfully completed
SQL> select * from dept;
DEPTNO DNAME LOC
------ -------------- -------------
10 ACCOUNTING NEW YORK
20 RESEARCH DALLAS
30 SALES CHICAGO
40 OPERATIONS BOSTON
77 市場擴充部 JILIN
15 採購部 成都
6 rows selected
（2）按位置傳遞
begin
insert_dept(28,'工程部','洛陽');
end;
/
SQL>
PL/SQL procedure successfully completed
SQL> select * from dept;
DEPTNO DNAME LOC
------ -------------- -------------
10 ACCOUNTING NEW YORK
20 RESEARCH DALLAS
30 SALES CHICAGO
40 OPERATIONS BOSTON
77 市場擴充部 JILIN
15 採購部 成都
28 工程部 洛陽
7 rows selected
SQL> desc insert_dept;
Parameter Type Mode Default?
---------- -------- ---- --------
NUM_DEPTNO NUMBER IN
VAR_ENAME VARCHAR2 IN
VAR_LOC VARCHAR2 IN
3、混合方式傳遞
SQL> exec insert_dept(38,var_loc=>'濟南',var_ename=>'測試部');
PL/SQL procedure successfully completed
SQL> select * from dept;
DEPTNO DNAME LOC
------ -------------- -------------
10 ACCOUNTING NEW YORK
20 RESEARCH DALLAS
30 SALES CHICAGO
40 OPERATIONS BOSTON
38 測試部 濟南
77 市場擴充部 JILIN
15 採購部 成都
28 工程部 洛陽
8 rows selected
如果某個位置使用了“指定名稱傳遞”方式傳入引數值後，其後面的引數值也要使用“指定名稱傳遞”。
2、out模式引數
表示這個引數在儲存過程中已經被賦值，並且這個引數值可以傳遞到當前儲存過程以外的環境中。
建立儲存過程：
create or replace procedure select_dept(
num_deptno in number,
var_dname out dept.dname%type,
var_loc out dept.loc%type) is
begin
select dname,loc
into var_dname,var_loc
from dept
where deptno=num_deptno;
exception
when no_data_found then
dbms_output.put_line('該部門編號不存在');
end select_dept;
（1）在PL/SQL塊中呼叫OUT模式的儲存過程：這種方式需要在PL/SQL塊得DECLARE部分定義與儲存過程中out引數相容的若干變數。
SQL> update dept
2 set deptno=99
3 where dname='市場擴充部';
1 row updated
SQL> select * from dept;
DEPTNO DNAME LOC
------ -------------- -------------
10 ACCOUNTING NEW YORK
20 RESEARCH DALLAS
30 SALES CHICAGO
40 OPERATIONS BOSTON
38 測試部 濟南
99 市場擴充部 JILIN
15 採購部 成都
28 工程部 洛陽
8 rows selected
呼叫儲存過程：
declare
var_dname dept.dname%type;
var_loc dept.loc%type;
begin
select_dept(99,var_dname,var_loc);
dbms_output.put_line(var_dname||'位於： '||var_loc);
end;
/
執行結果：
SQL>
市場擴充部位於： JILIN
PL/SQL procedure successfully completed
（2）使用exec命令執行out模式的儲存過程：使用exec命令需要在sql*plus環境中使用variable關鍵字宣告兩個變數，用以儲存out引數的返回值。
SQL> variable var_dname varchar2(50);
SQL> variable var_loc varchar2(50);
SQL> exec select_dept(15,:var_dname,:var_loc);
PL/SQL procedure successfully completed
var_dname
---------
採購部
var_loc
---------
成都
3、in out模式引數
可以從外界向該型別的引數傳入值，在執行完儲存過程後，可以將該引數的返回值傳給外界。
建立儲存過程：
create or replace procedure pro_square(
num in out number,
flag in boolean) is
i int:=2;
begin
if flag then
num:=power(num,i);
else
num:=sqrt(num);
end if;
end pro_square;
呼叫儲存過程：
declare
var_number number;
var_temp number;
boo_flag boolean;
begin
var_temp:=3;
var_number:=var_temp;
boo_flag:=true;
pro_square(var_number,boo_flag);
if boo_flag then
dbms_output.put_line(var_temp||'的平方是：'||var_number);
else
dbms_output.put_line(var_temp||'的平方根是：'||var_number);
end if;
end;
/
執行結果：
SQL>
3的平方是：9
PL/SQL procedure successfully completed
4、 in引數的預設值
宣告in引數的同時給其初始化預設值
建立儲存過程：
create or replace procedure insert_dept(
num_deptno in number,
var_ename in varchar2 default '綜合部',
var_loc in varchar2 default '北京') is
begin
insert into dept
values(num_deptno,var_ename,var_loc);
end insert_dept;
呼叫儲存過程：
declare
row_dept dept%rowtype;
begin
insert_dept(57,var_loc=>'太原');
commit;
select * into row_dept from dept where deptno=57;
dbms_output.put_line('部門名稱是：'||row_dept.dname||'，位置是：'||row_dept.loc);
end;
/
執行結果：
SQL>
部門名稱是：綜合部，位置是：太原
PL/SQL procedure successfully completed
2.3 函式
2.3.1 建立函式
函式可以接受零個或者多個輸入引數，並且函式必須有返回值。
語法格式：
create [or replace] function fun_name[(parameter1[,parameter2]...) return data_type is
[inner_variable]
begin
plsql_sentence;
[exception]
[dowith_sentences;]
end [fun_name];
由於函式有返回值，所以在函式主體部分（即begin部分）必須使用return語句返回函式值，並且要求返回值的型別要與函式宣告時的返回值型別（即data_type）相同。
建立函式：
create or replace function get_avg_pay(num_deptno number) return number is
num_avg_pay number;
begin
select avg(sal)
into num_avg_pay
from emp
where deptno=num_deptno;
return(round(num_avg_pay,2));
exception
when no_data_found then
dbms_output.put_line('該部門編號不存在');
return(0);
end get_avg_pay;
2.3.2 呼叫函式
declare
avg_pay number;
begin
avg_pay:=get_avg_pay(10);
dbms_output.put_line('平均工資是：'||avg_pay);
end;
/
SQL>
平均工資是：2916.67
PL/SQL procedure successfully completed
2.3.3 刪除函式
語法格式：
drop function fun_name;
SQL> drop function get_avg_pay;
Function dropped
2.4 觸發器
語法格式：
create [or replace] trigger tri_name
[before|after|instead of] tri_event
on table_name|view_name|user_name|db_name
[for each row][when tri_condition]
begin
plsql_sentences:
end tri_name;
觸發器的組成部分：
1、觸發器名稱
2、觸發語句
3、觸發器限制
4、觸發操作
觸發器型別：
1、 語句觸發器
2、 行觸發器
3、INSTEAD OF 觸發器
4、系統條件觸發器
5、使用者事件觸發器
2.4.1 語句級觸發器
針對 一條DML語句而引起的觸發器執行。無論資料操作影響多少行，觸發器都只會執行一次。
（1）建立日誌表dept_log
在scott模式下建立dept_log資料表，定義兩個欄位，儲存操作種類資訊和操作日期
SQL> create table dept_log
2 (
3 operate_tag varchar2(10),
4 operate_time date);
Table created
（2）建立一個關於emp表的語句級觸發器，將使用者對dept表的操作資訊儲存到dept_log表中。
create or replace trigger tri_dept
before insert or update or delete
on dept
declare
var_tag varchar2(10);
begin
if inserting then
var_tag:='插入';
elsif updating then
var_tag:='修改';
elsif deleting then
var_tag:='刪除';
end if;
insert into dept_log
values(var_tag,sysdate);
end tri_dept;
條件謂詞通用的語法格式：
if inserting then
do something about insert;
elsif updating then
do something about update;
elsif deleting then
do something about delete;
end if;
判斷特定列是否被更新：
if updating(dname) then
do something about update dname
end if;
（3）執行觸發器
在資料表dept中實現插入、修改、刪除3種操作，以便引起觸發器tri_dept的執行。
SQL> insert into dept values(66,'業務諮詢部','長春');
1 row inserted
SQL> update dept set loc='瀋陽' where deptno=66;
1 row updated
SQL> delete from dept where deptno=66;
1 row deleted
（4）檢視dept_log日誌
SQL> select * from dept_log;
OPERATE_TAG OPERATE_TIME
----------- ------------
插入 2013/12/8 17
修改 2013/12/8 17
刪除 2013/12/8 17
2.4.2 行級觸發器
必須在語法中使用for each row，典型應用是給資料表生成主鍵值。
（1）建立一個帶有主鍵列的資料表
SQL> create table goods
2 ( id int primary key,
3 good_name varchar2(50));
Table created
（2）為了給goods表的id列生成不能重複的有序值，需要建立一個序列
SQL> create sequence seq_id;
Sequence created
（3）建立一個觸發器，為goods表的id列賦值
create or replace trigger tri_insert_good
before insert on goods
for each row
begin
select seq_id.nextval
into :new.id
from dual;
end tri_insert_good;
列識別符號：
原值識別符號：用於標識當前行某個列的原始值，記作“:old.column_name”，通常在update和delete語句中使用。
新值識別符號：用於標識當前行某個列的新值，記作“:new.column_name”，通常在insert和update語句中使用。
（4）向goods表中插入資料
SQL> insert into goods(good_name) values('蘋果');
1 row inserted
SQL> insert into goods(id,good_name) values(9,'葡萄');
1 row inserted
（5）驗證觸發器
SQL> select * from goods;
ID GOOD_NAME
--------------------------------------- --------------------------------------------------
1 蘋果
2 葡萄
2.4.3 替換觸發器
關鍵字：instead of
替換觸發器定義在檢視上。
（1）建立一個檢視
SQL> conn sysdba/123456
Connected to Oracle Database 11g Enterprise Edition Release 11.2.0.1.0
Connected as sys@stone AS SYSDBA
SQL> grant create view to scott;
Grant succeeded
SQL> conn SCOTT/TIGER
Connected to Oracle Database 11g Enterprise Edition Release 11.2.0.1.0
Connected as SCOTT
SQL> create view view_emp_dept
2 as select empno,ename,dept.deptno,dname,job,hiredate
3 from emp,dept
4 where emp.deptno=dept.deptno;
在沒有建立替換觸發器前向該檢視插入資料，則報錯。
（2）建立view_emp_dept檢視在insert事件中的替換觸發器。
create or replace trigger tri_insert_view
instead of insert on view_emp_dept
for each row
declare
row_dept dept%rowtype;
begin
select * into row_dept from dept where deptno=:new.deptno;
if sql%notfound then
insert into dept(deptno,dname)
values(:new.deptno,:new.dname);
end if;
insert into emp(empno,ename,deptno,job,hiredate)
values(:new.empno,:new.ename,:new.deptno,:new.job,:new.hiredate);
end tri_insert_view;
（3）當觸發器tri_insert_view成功建立後，再向view_emp_dept檢視中插入資料時，oracle就不會產生錯誤資訊，而是引起觸發器tri_insert_view的執行，從而實現向emp表和dept表中插入兩行資料。
SQL> insert into view_emp_dept(empno,ename,deptno,dname,job,hiredate)
2 values(8888,'東方',10,'ACCOUNTING','CASHIER',sysdate);
1 row inserted
SQL> select * from view_emp_dept where empno=8888;
EMPNO ENAME DEPTNO DNAME JOB HIREDATE
----- ---------- ------ -------------- --------- -----------
8888 東方 10 ACCOUNTING CASHIER 2013/12/8 1
2.4.4 使用者事件觸發器
引起使用者事件觸發器的常見事件DDL操作或者使用者登入，退出等。包括：CREATE,ALTER,DROP,ANALYZE,COMMIT,GRANT,REVOKE,RENAME,TRUNCATE,SUSPEND,LOGON和LOGOFF等
（1）建立一個日誌資訊表，儲存DDL操作的資訊
create table ddl_oper_log
(
db_obj_name varchar2(20),
db_obj_type varchar2(20),
oper_action varchar2(20),
oper_user varchar2(20),
oper_date date
);
SQL>
Table created
（2）建立一個使用者觸發器，將當前模式下的DDL操作資訊儲存到上面建立的ddl_oper_log日誌資訊表中。
create or replace trigger tri_ddl_oper
before create or alter or drop
on scott.schema
begin
insert into ddl_oper_log values(
ora_dict_obj_name,
ora_dict_obj_type,
ora_sysevent,
ora_login_user,
sysdate);
end tri_ddl_oper;
ora_dict_obj_name：獲取DDL操作所對應的資料庫物件
ora_dict_obj_type：獲取DDL操作所對應的資料庫物件的型別
ora_sysevent：獲取觸發器的系統事件名
ora_login_user：獲取登入使用者名稱
（3）進行DDL操作，引發觸發器的執行。
SQL> create table tb_test(id number);
Table created
SQL> create view view_test as select empno,ename from emp;
View created
SQL> alter table tb_test add(name varchar2(10));
Table altered
SQL> drop view view_test;
View dropped
SQL> select * from ddl_oper_log;
DB_OBJ_NAME DB_OBJ_TYPE OPER_ACTION OPER_USER OPER_DATE
-------------------- -------------------- -------------------- -------------------- -----------
TB_TEST TABLE CREATE SCOTT 2013/12/8 1
VIEW_TEST VIEW CREATE SCOTT 2013/12/8 1
TB_TEST TABLE ALTER SCOTT 2013/12/8 1
VIEW_TEST VIEW DROP SCOTT 2013/12/8 1
2.5 程式包
程式包由PL/SQL程式元素（如變數，型別）和匿名PL/SQL塊（如遊標）、命名PL/SQL塊（如儲存過程和函式）組成。
程式包通常由規範和包主體組成。
2.5.1 程式包的規範
程式包規範一定要在包主體之前被建立
語法格式：
create [or replace] package pack_name is
[declare_variable];
[declare_type];
[declare_cursor];
[declare_funciton];
[declare_procedure];
end [pack_name];
create or replace package pack_emp is
function fun_avg_sal(num_deptno number)return number;
procedure pro_regulate_sal(var_job varchar2,num_proportion number);
end pack_emp;
2.5.2 程式包的主體
語法格式：
create [or replace] package body pack_name is
[inner_variable]
[cursor_body]
[function_title]
{begin
fun_plsql;
[exception]
[dowith_sentences;]
end [fun_name]}
[procedure_title]
{begin
pro_plsql;
[exception]
[dowith_sentences;]
end [pro_name]}
...
end [pack_name];
建立程式包pack_emp主體，實現對應規範中宣告的函式和儲存過程
create or replace package body pack_emp is
function fun_avg_sal(num_deptno number) return number is
num_avg_sal number;
begin
select avg(sal)
into num_avg_sal
from emp
where deptno=num_deptno;
return(num_avg_sal);
exception
when no_data_found then
dbms_output.put_line('該部門編號不存在僱員記錄');
return 0;
end fun_avg_sal;
procedure pro_regulate_sal(var_job varchar2,num_proportion number)is
begin
update emp
set sal=sal*(1+num_proportion)
where job=var_job;
end pro_regulate_sal;
end pack_emp;
建立一個匿名的PL/SQL塊，透過程式包pack_emp呼叫其中的函式fun_avg_sal和儲存過程pro_regulate_sal，並輸出函式的返回結果。
declare
num_deptno emp.deptno%type;
var_job emp.job%type;
num_avg_sal emp.sal%type;
num_proportion number;
begin
num_deptno:=10;
num_avg_sal:=pack_emp.fun_avg_sal(num_deptno);
dbms_output.put_line(num_deptno||'號部門的平均工資是：'||num_avg_sal);
var_job:='SALESMAN';
num_proportion:=0.1;
pack_emp.pro_regulate_sal(var_job,num_proportion);
end;
/
使用程式包的過程：
建立程式包規範
建立程式包主體
呼叫程式包中的子程式
來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/28536251/viewspace-2145143/，如需轉載，請註明出處，否則將追究法律責任。
相關文章
- Oracle PL/SQL語言基礎OracleSQL
- pl/sql程式設計基礎SQL程式設計
- PLSQL Language Reference-PL/SQL概覽-PL/SQL語言基礎SQL
- PL/SQL基礎語法入門SQL
- Oracle之PL/SQL基礎學習OracleSQL
- Oracle PL/SQL語言基礎(轉)OracleSQL
- Oracle PL/SQL 程式設計基礎 例項OracleSQL程式設計
- Oracle之PL/SQL基礎學習之二OracleSQL
- PLSQL Language Reference-PL/SQL語言基礎-表示式-PL/SQL表示式中的SQL函式SQL函式
- 原創：oracle PL/SQL程式設計基礎 上OracleSQL程式設計
- 原創：oracle PL/SQL程式設計基礎 下OracleSQL程式設計
- PLSQL Language Reference-PL/SQL語言基礎-詞彙單元-宣告SQL
- PLSQL Language Reference-PL/SQL語言基礎-表示式-BOOLEAN表示式SQLBoolean
- PLSQL Language Reference-PL/SQL語言基礎-表示式-短路計算SQL
- PLSQL Language Reference-PL/SQL語言基礎-詞彙單元-字面量SQL
- PL/SQL解數獨在簡單優化基礎上的改進SQL優化
- SQL基礎SQL
- PL/SQLSQL
- PLSQL Language Reference-PL/SQL語言基礎-表示式-比較操作符SQL
- PLSQL Language Reference-PL/SQL語言基礎-表示式-邏輯操作符SQL
- PLSQL Language Reference-PL/SQL語言基礎-表示式-連線操作符SQL
- PLSQL Language Reference-PL/SQL語言基礎-詞彙單元-識別符號引用SQL符號
- SQL&PL/SQL （轉）SQL
- PL/SQL 宣告SQL
- PL/SQL cursorSQL
- PL/SQL打包SQL
- PL/SQL DEVSQLdev
- PLSQL Language Reference-PL/SQL語言基礎-表示式-操作符優先順序SQL
- PLSQL Language Reference-PL/SQL語言基礎-變數賦值-使用SELECT INTO賦值SQL變數賦值
- 使用PL/Scope分析PL/SQL程式碼SQL
- PLSQL Language Reference-PL/SQL概覽-PL/SQL架構SQL架構
- [PL/SQL]10g PL/SQL學習筆記（一）SQL筆記
- [PL/SQL]10g PL/SQL學習筆記（二）SQL筆記
- [PL/SQL]10g PL/SQL學習筆記（三）SQL筆記
- SQL入門基礎SQL
- SQL基礎入門SQL
- SQL 語句基礎SQL
- SQL優化基礎SQL優化