PL/SQL程式設計急速上手

　　結構化查詢語言（SQL）是第四代程式語言的典型，這種命令式的語言更像一種指令，使用它，你只需要告訴計算機“做什麼”，而不用告訴計算機“怎麼做”。第四代程式語言普遍具有簡單、易學、能更快的投入生產等優點，但也失去了部分第三代程式語言（C，C++，Java等）的靈活性。PL/SQL 在 SQL 的基礎上，保留了部分第三代程式語言特性，它主要執行在 Oracle 資料庫上，它同時兼備了第四代語言簡單、易用的特點，又保留了高階程式設計語言的靈活性與完整性，這使得開發人員可以只使用 PL/SQL 就能進行復雜業務邏輯的編寫。

 

一　　PL/SQL 簡介

 

　　1，簡介

　　標準 SQL 提供了定義和操縱資料庫物件的能力，但與傳統高階程式語言相比，由於其具有更高的抽象性，所以註定缺乏諸多高階程式語言的特性，比如封裝函式、流程控制、進行錯誤檢測和處理等。

　　PL/SQL 是 Oracle 在標準 SQL 的基礎上進行功能擴充後的一門程式語言，這使它保留了部分第三代程式語言的部分特性，比如變數宣告、流程控制、錯誤處理等。

　　PL/SQL 的全稱是 Procedural Language/SQL，即過程化結構查詢語言，正如其名所示，PL/SQL 增加了過程性語言中的結構，以對標準 SQL 進行擴充。在PL/SQL 中，最基本的程式單元是語句塊（block），所有的程式都應該由各種塊構成，塊與塊之間可以相互巢狀。在塊中，可以定義變數，執行條件判斷，迴圈等。

 

　　2，開發工具

　　Oracle 官方提供了兩款開發工具：SQL*Plus 和 Oracle SQL Developer。前者是一款命令列開發工具，後者則擁有方便的圖形化操作介面（類似SQL Server 的 SSMS）。

　　除了官方提供的兩款工具外，PL/SQL Develpoer 是一款由第三方公司開發的，非常流行的 Oracle 資料庫整合開發環境。除此之外，市面上還有很多其他工具也具備 Oracle 資料庫開發的能力，大家可以根據需要選擇合適的開發工具。

 

二　　PL/SQL基礎

 

　　1，SQL 與 PL/SQL

　　前面提到，PL/SQL 是對標準 SQL 的擴充套件，所以，在 PL/SQL 中不僅可以執行 SQL 語句，還支援很多增強的特性，比如在 SQL 語句中使用變數、使用 PL/SQL 定義的函式等。在 PL/SQL 語句塊中，可以使用 SQL 語句運算元據庫，它支援所有的 SQL 資料操作、遊標和事務處理命令，支援所有的 SQL 函式、操作符，完全支援 SQL 資料型別。

　　需要注意的是：在 PL/SQL 語句塊中，不能直接使用 DDL 語句，這是因為 PL/SQL 引擎在編譯時會檢測語句塊中所涉及的物件，如果其不存在，通常都會引發錯誤，導致 DDL 語句執行失敗。

　　為了解決這類繫結性錯誤，可以使用動態SQL，即把需要執行的 DDL 操作儲存在字串中，並通過 execute immediate 來執行這個字串，從而達到間接執行 DDL 操作的目的。

　　

　　2，資料定義

　　資料管理主要使用 DDL 資料定義語言：create、alter、drop。

　　建立表和約束：

--在列後新增約束
create table table_name
(
    col1 type constraint,
    ...
)
--單獨新增約束
create table table_name
(
    col1 type,
    ....,
    constration cons_name cons_type
)
--在Oracle中建立表和約束與標準SQL相同

　　建立索引和檢視：

--建立索引（非唯一）
--預設系統會在具有unique和primary key的列上建立唯一約束
create index index_name on (col1...);
--當提供多個列時，即建立複合索引
--建立檢視
create or replace view view_name
as
select ...;
--建立，如果已存在則修改檢視
create view ...
as
...
with read only;
--建立只讀的檢視（推薦）

　　修改表或檢視：

--為表增加新的列
alter table table_name
add col_name type constration;
--移除表中已有的列
alter table table_name
drop column col_name;

　　刪除資料庫物件：

--刪除表
drop table table_name;
--刪除檢視
drop view view_name;
...

　　

　　3，資料查詢

　　A：標準查詢

　　　　Oracle 中的資料查詢遵循 SQL 標準，常規查詢請移步我的《SQL入門，就這麼簡單》。

　　B：dual 表

　　　　dual 是 Oracle 系統中對所有使用者可用的一個實際存在的表，它不能用來儲存資訊，在實際開發中只能用來執行 SELECT 語句，我們可以用它來獲取系統資訊，比如獲取當前系統日期，或輸出一些測試資訊。

--獲取系統日期
select sysdate from dual;
--轉換日期格式
select to_char(sysdate,'yyyy-mm-dd');
...

　　C： 偽列

　　　　常用的偽列有兩個：rownum、rowid。

　　　　在 Oracle 中沒有類似 SQL Server 中 TOP 這樣可以提取結果集前幾條記錄的關鍵字，但 Oracle 提供了一個更方便的方法，rownum 偽列。rownum 是一個動態的序號，從 1 開始，為所有查詢到的資料編號。

--查詢員工表中前10位員工相關資訊
select rownum,ename,sal from emp
where rownum<=10;
-- 測試資料庫 Oracle 11g 

　　　　使用 rownum 偽列時需要注意：rownum 是在基礎查詢之後動態新增上去的序號，所以，如果你想通過一條查詢語句實現提取結果集中間的部分記錄是不能成功的，必須使用子查詢，把 rownum 當做普通列才能實現。

select row_num,empno,ename,sal from (
    select rownum as row_num,empno,ename,sal from emp
)a
where row_num >5 and row_num <=10;
-- 別名是為了防止伺服器把外層的rownum再次當做偽列

　　　　同理，提取使用 order by 排序後的記錄，也需要使用子查詢。

　　　　和 rownum 不同，rowid 偽列是和表中的資料一樣實際存在的列，它是一種資料型別，是基於 64 位編碼的 18 個字元，用來唯一的表示一條記錄物理位置的一個id。我們可以通過 rowidtochar 函式把它轉換成字串進行顯示，還可以通過它來刪除表中重複的記錄。

--檢視rowid
select rowidtochar(rowid) ename,sal from emp;
--基於rowid刪除表中形同的記錄
delete from emp
where rowid not in (
    select min(rowid) from emp group by empno
);

　　

　　4，資料操縱

　　資料操縱主要包含以下操作：insert、update、delete、merge。

　　A：insert 插入

--方式一
insert into table_name(column list)--如果不提供欄位列表，下面的值列表需要提供每個欄位的值，即使可以為空或有預設值
values 
(value list),
(value list),
....
--方式二
insert into table_name
select ...
--從其他查詢獲取資料，並插入表，資料必須符合表的約束

　　B：update 更新

--方式一
update table_name
set col=newValue
where ...--如果不提供過濾條件，則更新表中所有的列
--方式二
update table_name
set (column list)=
(select ...)
--通過子查詢更新表，如果只更新一列，則可以省略column list 的括號，需要注意子查詢的欄位順序需要和更新的欄位順序一致

　　C：delete

--方式一
delete from table_name
where ...--如果不提供過濾條件，則會刪除所有記錄

　　

　　5，序列

　　Oracle 中沒有 SQL Server 中 identity() 標識函式，也沒有 MySQL 中 auto_increnent 這樣的選項來實現自增的列。但 Oracle 提供了更有用的“序列”。類似一個封裝好的函式，每次執行會返回一個按指定步長增長或減小的數字。常用來為表設定自增的主鍵。

create sequence seq_name
increment by n --自增的步長，（省略該選項則）預設為1，負數表示遞減
start with n --序列的初始值，預設為1
max value n | nomaxvalue --指定最大值或沒有最大值(無限增長)
min value n  | nominvalue --指定最小值或沒有最小值(無限減小)
cycle | nocycle --規定設定的序列到達最大或最小時是否從開頭迴圈
cache n | nocache --規定是否在記憶體中快取序列值，以改善效能

　　通常情況下，我們只需要指定初始值，最大值和迴圈三項，即可建立一個序列。

create sequence my_seq
start with 1
nomaxvalue
nocycle;

　　序列也是 Oracle 資料庫物件之一，序列有兩個常用的屬性：nextval、currval。

select my_seq.nextval from dual;--獲取下一個序列值
select my_seq.currval from dual;--檢視當前序列值
--在插入資料是使用序列
insert into table_name
values
(my_seq.nextval,...)
--使用迴圈批量插入時非常方便

　　我們可以為每個表建立單獨的序列，從而為每個表提供沒有間隙（無刪除資料或回滾等操作干擾）的自增欄位作為主鍵。

　　修改和刪除序列：

alter sequence seq_name
...
--為了保證主鍵的變化有相同的規律可循，一般不建議修改已建立的序列
drop sequence seq_name

 

三　　Oracle 內建函式

 

　　1，字串函式

--把二進位制轉換成字元
select CHR(0101) from dual;
--連線字串
select concat(111,'aaa') from dual;
select 111 || 'aaa' from dual;
--首字母大寫
select INITCAP('char') from dual;
--全大/小寫轉換
select lower('ABC'),upper('abc') from dual;
--左/右填充
select lpad('aa',5,'*'),rpad('aa',5,'*') from dual;
--刪除字串左/右指定字元(第二個引數中包含的字元都會被刪除)
select ltrim('aaa123aaa','1a'),rtrim('aa123aa','a') from dual;
--刪除左右空格
select trim('  aaa  ') from dual;
--從左邊開始刪除指定字元（單個）,可選引數還包括：trailing（從右邊開始）,both(兩邊一起)
select trim(leading 'a' from 'aa123aa') from dual;
--從指定位置開始擷取指定長度的字串
select substr('abcdefg',2,3) from dual;
--字元替換(第二個引數中包含的字元都會被替換)
select translate('11aa22aa11', 'a2', 'bb') from dual;
--替換 NULL 值
select nvl(NULL,'aha') from dual;

　　

　　2，數學函式

--絕對值
select abs(-123) from dual;
--向上取整
select ceil(1.2),ceil(-1.2) from dual;
--向下取整
select floor(1.8),floor(-1.8) from dual;
--返回自然常數 e 的 n 次方
select exp(5) from dual;
--返回以第一個引數為底的第二個引數的對數
select log(3,10) from dual;
--求模,如果第二個引數為0，則返回第一個引數
select mod(10,3) from dual;
--返回第一個引數的第二個引數次方
select power(2，3) from dual;
--保留指定小數位，最後一位小數四捨五入得來
select round(1.2345,3) from dual;
--保留指定小數位，其餘直接截斷
select trunc(1.2345,3) from dual;

--格式化數字(格式位數應該與數字位數相同)

--用0格式化時，如果數字位數不夠，結果會用0補齊位數
select to_char(123456789000,'000,000,000,000,000') from dual;
--用9格式化時，如果數字位數不夠，結果會用空格補齊位數
select to_char(123456789000,'999,999,999,999,999') from dual;
--使用fm格式化小數
select to_char(123456.258,'fm999,999,999.99') from dual;
--使用 $（美元） 或 L（當地） 新增貨幣符號
select to_char(123.456,'L999.999') from dual;
/* 注意貨幣符號和小數不能一起使用 */

 

　　3，時間和日期函式

--返回作業系統日期
select sysdate from dual;
--返回日期部分
select current_date from dual;
--返回日期+時間
select current_timestamp from dual;
--返回作業系統日期—+時間（包含時區資訊）
select systimestamp from dual;
--按格式化日期為字串
select to_char(sysdate,'YYYY-MM-DD HH:MM:SS') from dual;
--把字串表示的日期轉換成日期型別的值返回（前後格式需保持一致）
select to_date('2020-05-28 17:02:00','YYYY-MM-DD HH24:MI:SS') from dual;
--把字串表示的日期轉換成日期 + 時間型別的值返回（前後格式需保持一致）
select to_timestamp('2020-05-28 17:02:00','YYYY-MM-DD HH24:MI:SS') from dual;
--返回指定日期後幾個月的日期
select add_months(sysdate,1) from dual;
--返回兩個日期間間隔月數(注意正負)
select months_between(sysdate,to_date('2020-07-01','YYYY-MM-DD')) from dual;
--把日期按指定精度截斷,可選引數有yyyy(精確到年，返回當年的第一天的日期),mm(精確到月，返回當月第一天的日期),rr(精確到日，返回當天的日期)
select trunc(sysdate,'mm') from dual;

/* ----------------------日期可選格式--------------------- */
TO_CHAR(sysdate, 'DD-MON-YYYY HH24:MI:SS')
TO_CHAR(sysdate, 'DD-MON-YYYY HH12:MI:SS PM')
TO_CHAR(systimestamp, 'DD-MON-YYYY HH24:MI:SS.FF')
TO_CHAR(sysdate, 'DY, DD-MON-YYYY')
TO_CHAR(sysdate,'Month DDth, YYYY') 
TO_CHAR(systimestamp, 'DD-MON-YYYY HH24:MI:SS TZH:TZM')
TO_CHAR(sysdate, 'MM/DD/YYYY HH24:MI:SS')
TO_CHAR(sysdate, 'MM/DD/YY HH24:MI:SS')
TO_CHAR(sysdate, 'MM/DD/RRRR HH12:MI:SS PM')
TO_CHAR(sysdate, 'MM/DD/RR HH12:MI:SS PM')

 

　　4，聚合函式

--計算行數（不計算空值）
select count(*) from emp;--根據所有列計算
select count(comm) from emp;--根據某一列計算（注意該列是否有空值）
select count(distinct deptno) from emp;--計算deptno中不同值的個數
--計算列的最大/小值
select max(sal),min(sal) from emp;
--返回中間值
select median(sal) from emp;
--返回標準差
select stddev(sal) from emp;
--求和
select sum(sal) from emp;
--計算方差
select variance(sal) from emp;
--偽列 rownum,每條資料的序號
select rownum,empno,ename,sal from emp;

 

四　　變數和型別

 

　　1，PL/SQL 基礎

　　如果想通過 PL/SQL 程式輸出內容，需要先執行以下命令，以開啟輸出功能，否則即使 PL/SQL 程式正常執行，也不會有任何資訊輸出。

set serveroutput on--可以不需要語句結束標記';',這是開發工具的命令
dbms_output.enable;--這是 Pl/SQL 提供的

　　PL/SQL 程式由不同的 block（程式塊）組成，塊是 PL/SQL 程式的基本組成單位，塊又可以分為匿名塊和命名塊。

　　一個完整的 PL/SQL 程式一般包含 3 部分：declare（宣告），execution code（執行程式碼，即業務邏輯程式碼），exception（異常處理），宣告和異常處理不是必須的。

declare
--... 包括變數、遊標等
begin
--... 業務程式碼
exception
--... 異常處理
end;

　　讓我們來看一個最簡單的 PL/SQL 程式：

--注意，PL/SQL業務程式碼必須執行在 begin...end 中
begin
dbms_output.put_line('hello world');
end;
--沒有宣告和異常部分

　　塊與塊之間可以相互巢狀，PL/SQL 中程式塊可以限制變數的作用域（變數的作用域問題稍後的章節將會詳細講解），另外，使用<<name>>為塊命名可以讓整個程式可讀性更好：

<<outer>>--oracle 11g 不允許給最外層塊命名
begin
dbms_output.put_line('outer block');
  <<inner>>
  begin
  dbms_output.put_line('inner block');
  end;
end;

　　

　　2，變數

　　PL/SQL 中的變數在 declare 區域宣告，不需要額外的識別符號，只需要提供變數名和值型別即可。

declare
  v_name emp.ename%type;--通過動態獲取表中列的資料型別，來確定變數的資料型別
  v_job  varchar(50);--直接指定具體的資料型別
begin
  v_name:='&name';--通過:=為變數賦值
end;

　　&name，這種形式是 SQL Developer 工具提供的一種變數形式：替換變數，在執行程式時，你可以手動指定變數的值，提升程式的互動性，測試程式時非常有用。需要注意的是，它並不是 PL/SQL 提供的功能，當使用 & 標識變數時，每次執行該程式都需要提供值，如果使用 && 標識，則只需要在第一次執行時提供，後續執行都預設為第一次提供的值。

　　給變數賦值除了通過 := 的方式，還可以使用 select...into 的方式，直接從查詢中獲取值並賦給變數。

declare
    v_job emp.job%type;
begin
    select job into v_job from emp where ename=v_name;--通過select...into 為變數賦值
    dbms_output.put_line(v_job);--輸出變數值
end;

　　

　　3，記錄型別

　　當有多個邏輯相關的變數需要宣告時，我們可以使用記錄型別來封裝他們，封裝好這個東西就是記錄型別（record）。

declare
  type emp_record is record(--這裡相當於定義了一種新的資料型別，型別名稱是emp_record,和varcahr，int等型別一樣
  v_name emp.ename%type,
  v_job emp.job%type,
  v_sal emp.sal%type
  );
  --記錄型別類似其他程式語言中的類
  v_emp_record emp_record;--宣告一個emp_record型別的變數，相當於建立一個類的例項
begin
  select ename,job,sal into v_emp_record from emp where ename='ALLEN';--注意查詢的順序必須和記錄型別中定義的順序一致
  dbms_output.put_line(v_emp_record.v_name||' '||v_emp_record.v_job||' '||v_emp_record.v_sal);
  --通過例項訪問相關屬性
end;

　　%rowtype：

declare
  v_emp_record emp%rowtype;--宣告一個包含指定表中所有列的rowtype變數，使用上和記錄型別完全一致，但它本質上並不是記錄型別
begin
  select * into v_emp_record from emp where ename='ALLEN';--把所有的列都查詢出來賦值給該變數
  dbms_output.put_line(v_emp_record.ename||' '||v_emp_record.sal);
  --該變數中的屬性和表的列名完全一致，可以根據需要，只使用部分資料
end;

　　

　　4，集合

　　集合類似其他程式語言中的陣列，也可以通過下標來訪問資料。

　　如果把它和記錄型別、變數相比教，你會發現，標量標量是用來處理單行單列資料的，記錄型別適合處理單行多列的資料，而集合則是用來處理單列多行資料的。

　　Oracle 提供了三種型別的集合：索引表（又稱關聯陣列）、巢狀表、可變長度陣列。

　　索引表可以通過數字或字串來作為下標儲存資料，下標可以不連續，索引表的容量即是數字的最大值，但它只能儲存在記憶體中。

declare
  type idx_table is table of varchar(20) index by pls_integer;--建立索引表型別
  -- index by 後可選的引數有pls_integer、binary_integer、varcahr(size)和使用%type 指定的varchar2型別
  v_idx_table idx_table;--宣告索引表型別的變數
begin
  v_idx_table(1):='hello';--插入值
  v_idx_table(2):='world';
  dbms_output.put_line(v_idx_table(1)||' '||v_idx_table(2));
end;

　　巢狀表只能使用數字作為下標，數字必須是有序的，巢狀表的容量沒有限制，可以儲存到資料庫中。

declare 
  type nest_table is table of varchar(20);--建立巢狀表型別
  v_nest_table nest_table:=nest_table('x');--宣告巢狀表型別的變數並初始化
  --未初始化的巢狀表型別實際上是一個null，如果試圖為其賦值會報錯。初始化就是呼叫一個和建立的巢狀表型別同名的函式，
  --函式的引數值型別需要和巢狀表型別定義的儲存值型別（of 後的型別）相同，並且引數的個數預設就是這個巢狀表型別變數的初始容量
begin
  v_nest_table.extend(5);--擴充巢狀表型別變數的容量
  --如果要增加巢狀表的容量，需要呼叫extend方法（該方法將在稍後詳細說明）
  v_nest_table(1):='hello';--插入值
  v_nest_table(2):='world';
  dbms_output.put_line(v_nest_table(1)||' '||v_nest_table(2));
  dbms_output.put_line(nvl(v_nest_table(3),'it is null'));--沒被使用的位置為null
end;

　　可變長度陣列和巢狀表類似，都只能使用有序的數字作為下標，可變陣列在定義時必須指定容量，但在執行時可以手動的擴充其容量，所以，可變陣列的真實容量也可以是無限的，可變陣列也可以儲存到資料庫中。

declare
  type varr is varray(5) of int;--建立可變陣列型別
  v_varr varr:=varr();--宣告可變陣列型別的變數並初始化
  --和巢狀表一樣的原因，必須初始化
begin
  for i in 1..5 loop--迴圈插入值
    v_varr.extend();
    v_varr(i):=i;
    end loop; dbms_output.put_line(v_varr(1)||','||v_varr(2)||','||v_varr(3)||','||v_varr(4)||','||v_varr(5));
end;

　　巢狀表和可變陣列能存入資料庫是指：他們可以和普通資料型別一樣，用來定義表的列。

--第一步，建立一個儲存在資料庫中的巢狀表型別
create or replace type nest is table of varchar(20);
--第二步，建立一個帶有巢狀表型別列的資料表
create table x(
  x_id int, 
  x_nest nest
)nested table x_nest store as y;--使用nest table 指定這是一個包含巢狀表型別值的資料表，並通過 store as 建立一個關聯表來專門儲存巢狀表
--插入一條資料（包含初始化的巢狀表型別值）
insert into x values(1,nest('x','y','z'));
--第三步，在PL/SQL中讀取巢狀表型別的值（多行操作使用遊標）。資料表並沒有直接儲存巢狀表，所以不能直接使用select查詢，而應該在PL/SQL程式塊中查詢
declare
  v_nest nest;
begin
  select x_nest into v_nest from x;
  for i in 1..3 loop
    dbms_output.put_line(v_nest(i));
  end loop;
end;

　　把可變長度陣列存放到資料庫就不需要使用 nested table 和 store as 指定相關資訊，而且可以直接使用 select 查詢儲存了可變長度陣列的資料表。所以，通常的建議是，當只是臨時使用集合，那麼索引表是最好的選擇，如果需要把集合存入資料庫，可變陣列更操作起來更簡單。

　　

　　5，集合常用方法

　　集合的方法通過“.”點的形式呼叫：集合.方法。

集合.exists(n)--判斷是否存在某個集合的值
集合.count--統計集合中值的個數
集合.limit--查詢集合容量（長度）
集合.first/集合.last--集合中第一個/最後一個值的索引
集合.prior(n)/集合.nest(n)--指定索引位置前一個/下一個值的索引（一般用在索引表中，因為其下標可能不連續）
集合.extend/集合.extend(n)--為集合增加1個/n個容量（一般用在巢狀表和可變陣列中）
集合.trim/集合.trim(n)--從集合末尾刪除1個/n個元素（一般用在巢狀表和可變陣列中）
集合.delete/集合.delete(n)--從集合中刪除所有元素/第n個元素（一般用在索引表和巢狀表中）

 　　

　　6，變數的作用域

declare
 v1 int default 1;--外層塊變數v1
begin
 dbms_output.put_line(v1);
 --dbms_output.put_line(v2);error 必須宣告v2
 declare
  v2 int default 2;---內層塊變數
  v1 int default 3;
 begin
  dbms_output.put_line(v1);--返回3
 end;
end;

　　變數只在宣告的塊中起作用，內層塊可以訪問外層塊的變數，但外層塊無法訪問內層塊的變數，如果內外塊宣告的相同的變數，那麼 PL/SQL 採用就近原則。

 

五　　流程控制

　　

　　1，case

　　case 語句有兩種語法，簡單 case 語法只做等值匹配，搜尋 case 語法可以做區間匹配。

　　先來看簡單 case 語法：

declare
  v_sal int;
begin
  select sal into v_sal from emp where empno=&empno;
  case v_sal
    when 800 then dbms_output.put_line('太少了吧');
    when 1600 then dbms_output.put_line('這還差不多');
    when 3000 then dbms_output.put_line('這樣更好');
    when 5000 then dbms_output.put_line('這樣最好');
    else dbms_output.put_line('隨緣吧');
    end case;
end;

　　搜尋 case 語法：

begin
  select sal into v_sal from emp where empno=&empno;
  case 
    when v_sal<=1000 then dbms_output.put_line('太少了吧');
    when v_sal<=1600 then dbms_output.put_line('這還差不多');
    when v_sal<=3000 then dbms_output.put_line('這樣更好');
    when v_sal<=5000 then dbms_output.put_line('這樣最好');
    else dbms_output.put_line('隨緣吧');
    end case;
end;

　　請仔細觀察兩種語法的區別。

　　

　　2，if...elsif...else

declare
  v_sal int;
begin
  select sal into v_sal from emp where empno=&empno;
  if v_sal>=5000
    then dbms_output.put_line('還有頭髮嗎');
  elsif v_sal>=3000
    then dbms_output.put_line('還有一半嗎');
  else
    dbms_output.put_line('好好珍惜頭髮啊，少年');
  end if;
end;

　　請注意，PL/SQL 中的多分支結構 elsif 關鍵字與其他語言相比，少了一個字母 e，且 els 和 if 之間沒有空格。

　　

　　3，迴圈

　　PL/SQL 提供了 3 種迴圈：loop、while、for（集合部分已經見過了）。

　　在正式介紹迴圈之前，首先要介紹 PL/SQL 中的迴圈控制語句：exit，無條件結束整個迴圈（類似其他語言中的 break）。continue，結束本次迴圈，繼續下一次迴圈。接下里讓我們通過例子來詳細說明每個迴圈的使用方法。

　　loop 迴圈：

declare
  i int default 1;--定義，初始化迴圈控制變數
begin
  loop
    if i=5 then
      i:=i+1;
      continue;--當n等於5時，直接結束本次迴圈，不輸出
    end if;
    dbms_output.put_line(i);
    i:=i+1;--修改迴圈控制變數 
    exit when i>10;--根據迴圈控制比變數，判斷是否退出迴圈
  end loop;--結束迴圈
end;

　　while 迴圈：

declare
  i int default 1;--定義，初始化迴圈控制變數
begin
  while i<=10 loop--根據迴圈控制變數，判斷是否進入迴圈體
    dbms_output.put_line(i);--迴圈體
    i:=i+1;--修改迴圈控制變數
  end loop;--結束迴圈
end;

　　for 迴圈：

begin
  --在for迴圈中，初始化迴圈控制變數，只需指明變數名即可，型別系統預設為數字，min..max指明控制變數的變化範圍，從min開始，到max結束
  for i in reverse 1..10 loop--i可以被迴圈體引用，但不能被賦值
  dbms_output.put_line(i);--迴圈體
  --注意，因為初始化迴圈變數時已經指定了變化範圍，這相當於限定了迴圈條件，當變數從min變化到max時將自動結束迴圈
  end loop;  --結束迴圈
  --最後說明，reverse是可選的引數，表示迴圈變數從max開始，到min結束
end;

　　

　　4，雜項

　　這裡要介紹兩個東西，null 語句（不是null 值）和 goto 語句。null 語句表示什麼也不做，goto 可以無條件跳轉到程式指定位置。

begin
  if ... then
    ...
  else
    null;--什麼也不做，但使整個語句塊更豐滿，可讀性更高
  end if;
end;

declare
  i int:=0;
begin
  <<outer>>--定義一個標籤
  i:=i+1;
  dbms_output.put_line(i);
  if i<10 then
    goto outer;--通過goto實現類似迴圈的結構
  else
    null;--通過使用null讓語句塊更易讀
  end if;
end;

　　使用 goto 語句會破壞程式常規的執行流程，它是有別於順序、分支、循壞的另一種執行流程，如無特別需求，建議不要使用。

 

六　　異常處理

　　

　　1，異常簡介

　　無論何時何地何人，在程式設計的領域，總是無法避開異常。為了保證程式的健壯性，多數語言都提供了異常處理機制，PL/SQL 也不例外。

　　在 PL/SQL 中，異常大致可分為兩大類：

　　　　編譯時錯誤：程式在編寫過程中的錯誤，例如語法錯誤，訪問不存在的物件等，這類錯誤在編譯時 PL/SQL 引擎就會發現，並通知使用者。

　　　　執行時錯誤：這類錯誤會順利通過程式的編譯環節，只能等到執行時才能被發現，比如除數是 0 。這類錯誤也是最要命的。

　　

　　2，異常處理語法

　　我們知道，PL/SQL 程式分為三個部分：宣告區，執行區，異常處理區。基本的異常處理也包含此三個步驟：

　　　　A：在定義區，定義異常。

　　　　B：在執行區，觸發異常。

　　　　C：只要執行區觸發了異常，那麼執行區後續的業務程式碼都會立即停止執行，執行流程跳轉至異常處理區。

declare
    異常變數名 exception;
begin
    ...
    raise 異常變數名;
    ...
    exception
      when 異常變數名
        then ...
end;

　　如果有多個異常，可以定義多個變數，並在合適的時候觸發他們，並在異常處理區通過多個 when...then 來捕獲他們，並執行特定操作。

　　

　　3，預定義異常

　　大多數編譯時的異常，Oracle 都在內部隱式的定義好了，並且不需要在執行區手動的觸發，這類異常的處理最為簡單：

declare
    v_tmp varchar(10);
begin
    v_tmp:='超過10位元組的長度了';
    exception
        when value_error
        then
            dbms_output.put_line('出現value_error錯誤！' || '錯誤編號：'|| sqlcode || '錯誤名稱' || sqlerrm);
end;

　　PL/SQL 中出現的錯誤，都一個錯誤號，一個錯誤編碼（sqlcode），一個錯誤名稱（sqlerrm）。在錯誤處理區通過在 when 後面指定錯誤名稱，既可捕獲到指定錯誤了。常見的預定義錯誤如下：

錯誤號	異常編碼	異常名稱	描述
ora-01012	-1017	not_logged_on	在沒有連線資料庫時訪問資料
ora-01403	100	no_date_found	select...into沒有返回值
ora-01422	-1422	too_many_rows	select...into結果集超過一行
ora-01476	-1476	zero_divide	除數為0
ora-01722	-1722	invalid_number	字串和數字相加時，字串轉換失敗
ora-06502	-6502	value-error	賦值時，變數長度不足
ora-06530	-6530	access_into_null	向null值物件賦值
ora-06592	-06592	case_not_found	case語句中沒有任何匹配的值並且沒有else選項

　　更多預定義異常請查詢 Oracle 11g 《Oracle 線上文件》。

　　

　　4，自定義錯誤

declare
  e_nocomm exception;--定義一個異常名稱
  v_comm number(10,2);
begin
  select comm into v_comm from emp where empno=&empno;
  if v_comm is null
    then raise e_nocomm;--觸發自定義異常
  end if;
  exception
    when e_nocomm--捕獲自定義異常
      then dbms_output.put_lne('該員工沒有提成');
    when others--捕獲未定義的錯誤
      then dbms_output.put_line('未知錯誤 !');
end;

　　同一個塊中不能同時宣告一個異常多次，但不同的塊中可以定義相同的異常，在各自的塊中使用不會相互影響。

 

七　　程式設計物件

　　

　　1，事務

　　在 SQL Server 中，每一條 DML 語句都是一個隱式的事務，除非顯示的開始一個事務，否則，這些語句執行完就立即向資料庫提交了這些更改。而在 Oracle 中，每一條 SQL 語句開始都會自動開啟一個事務，除非顯示的使用 commit 提交，或退出某個開發工具而斷開連線，才會提交到資料庫，否則這些操作都只會儲存在記憶體中。

--在Oracle SQL Developer中
begin
  insert into dept values(88,'開發部','cd');
  savepoint a;--設定儲存點a
  insert into dept values(88,'設計部','cd');
  exception
    when dup_val_on_index then
    dbms_output.put_line('插入出錯');
    rollback to a;--回滾到a
end;
--這裡我們人為的製造了一個違反唯一約束的插入操作，在錯誤區捕獲該錯誤，然後回滾到儲存點a
select * from dept;--只能查詢到開發部被插入

/* 在 SQL*Plus 中 */
SQL>select * from dept;
/* 連開發部都沒有被插入 */　

1 -- 在 Oracle SQL Developer中
commit;
3 --現在插入已經被提交到資料庫，在SQL*Plus 中也可以查詢到了

　　在多個事務併發執行時，大概率會發生：一個事務讀取到另一個事務還未提交的資料（髒讀）；一個事務中不同時間點執行的同一個查詢，由於其他事務對涉及的資料進行了修改或刪除（不可重複讀）或插入（幻讀），而導致出現不一樣的結果。

　　為了解決這樣的問題，Oracle 允許對事務設立隔離級別：

begin
  commit;
  set transaction read only;--只讀的事務
  --settransaction read write;--可讀寫的事務
  --set transaction isolation level [serializable | read commited];
  --serializable：整個事務只能讀到當前事務開始前就以提交的資料
  --read commited：當前事務中的查詢，只能讀到該查詢前以提交的資料（不是整個事務，而是該查詢語句。這也是 Oracle 預設的隔離級別）
end;

　　由於一個事務中有且只能存在一條 set transaction 語句，且必須是事務的第一條語句，所以通常先使用 commit 結束前一個事務（理論上rollback也可以），以保證該語句是事務的第一條語句。

　　

　　2，子程式

　　Oracle 中子程式事實上就是 SQL Server 中對儲存過程和使用者自定義函式的總稱。過程和函式本質上是一個命名塊，可以被儲存在資料庫中，並在合適的時候呼叫，這樣可以解決程式碼重用的問題，並且由於它是已編譯好的程式碼，所以執行起來也更快。

　　過程和函式相比，過程不會返回值，常用來做資料的增刪改。而函式必須有返回值，通常用來嚮應用程式返回值。其他方面，過程和函式幾無區別。

　　儲存過程：

--無參過程
create or replace procedure p2 as
begin
  dbms_output.put_line('hello world');
end p2;
--or replace：如果存在則替換儲存過程，建議使用
--p1：過程名
--as：不能省略，也可以用is代替
--end p2：建立完成時也要跟上過程名

--帶引數的過程
create or replace procedure p2(p_deptno in int)--使用括號新增過程需要的形參
as
  v_empcount number;--定義過程中需要使用的變數，只需指定資料型別，不能新增型別所佔位元組長度
begin
  select count(ename) into v_empcount from emp where deptno=p_deptno;
  if v_empcount>0 then
    dbms_output.put_line('有人');
  else
    dbms_output.put_line('沒人');
  end if;
end p2;--不要忘了過程名

--呼叫儲存過程
begin
  p2(20);--通過（）傳遞實參
end;
--call p2(20);

　　函式：

--建立函式
create or replace function f1
return number--需要指定返回值型別，不需要長度
as
begin
  return 1;--需要使用return指定返回值
end f1;
--呼叫函式
declare 
  v_f1 number(10);
begin
  v_f1:=f1();--呼叫函式，並把返回值賦值給變數
  dbms_output.put_line(v_f1);
end;

　　在上面帶引數儲存過程中，指定形參時使用關鍵字 in，該關鍵字表示引數的模式是輸入型，可選的還有 out 輸出型，in out 輸入輸出型。

　　in 模式的引數被用作輸入引數，在過程內部只能被訪問，不能被賦值。

　　out  模式的引數被當做輸出引數使用，在過程內部可以被賦值，不能訪問。使用 out 型別引數時，必須在過程外部定義一個變數，用於接收過程在內部需要輸出的值，然後在呼叫子程式時把該變數當做形參傳入。待過程執行完畢，直接訪問外部定義的這個變數即可得到過程希望輸出的值了。

　　in out 模式的引數既可以被當做輸入引數，也可以被當做輸出引數。使用方式和 out 型引數一致，但可以給這個變數一個初始化值，一併傳入過程內部。out 型引數即使傳入了初始值，也會被過程忽略。

　　過程的引數模式和 MySQL 完全一致，例子可以參考我的《MySQL 程式設計》。函式本身就需要使用 return 返回值，所以不使用 in 或 out 指定引數模式，這樣毫無意義。

　　

　　3，觸發器

　　Oracle 中的觸發器本質上也是一個命名的語句塊，定義的方式和 PL/SQL 語句塊差不多，但它和過程或函式不同，它只能被隱式的呼叫。並且不能接受任何引數。

　　定義觸發器的語法：

create or replace trigger trigger_name--觸發器名稱
[before | after | instead of]--在事件之前還是之後執行觸發器中的程式碼
trigger_event--觸發事件
[referenceing_caluse]--通過新的名稱引用當前正在更新的資料
[when trigger_condition]--指定觸發條件
[for each row]--指定行級觸發器（每一條記錄都觸發一次）
trigger_body--觸發體（程式塊）

　　一個簡單例子：

create test(--建立測試表
    id int primary key,
    name varchar(20)
)
create or replace trigger t_test--建立觸發器
after insert or update or delete--觸發操作（也可以是其中一種）
on test--在表test上
for each row--行級觸發器
begin
    if inserting then--在插入資料時
        dbms_output.put_line('插入了資料，name：'||:new.name);
    end if;
    if updating then--在更新資料時
        dbms_output.put_line('更新了資料，oldname：'||:old.name||',newname:'||:new.name);
    end if;
    if deleting then--在刪除資料時
        dbms_output.put_line('刪除了資料，name：'||:old.name);
    end if;
end;

　　謂詞：new 表示引用新的資料（更新後或插入的資料），：old 引用舊的資料（被刪除的或更新前的資料）。可以在建立觸發器時通過 referencing（操作型別之後，for each row 之前） 指定新的謂詞。

...
referencing old as test_old new as test_new
...
--下面通過:test_old 引用修改前的資料，:test_new引用修改後的資料

　　測試程式碼：

insert into test values(1,'r');
update test set name='e' where id=1;
delete from test where id=1;
--注意觀察輸出結果

　　

　　4，遊標

　　Oracle 中的遊標用來處理多行多列的資料集合，包含四個步驟：定義，開啟，遍歷，關閉。遊標的語法如下：

cursor cursor_name [形參]--形參可以用來在where子句中限定遊標記錄
[return type]--可選的指定遊標返回的值型別
is query--通過is指定查詢（在這裡使用形參）
[for update[of column_list]]--允許在遊標中修改表中的資料，並在遊標開啟期間鎖定選中的記錄

　　下面是一個通過遊標遍歷輸出 dept 部門資訊的例子：

declare
    deptrow dept%rowtype;--定義一個儲存記錄的變數
    cursor dept_cur is--通過cursor定義遊標，is指定需要遍歷的結果集（一個查詢語句）
    select * from dept;
begin
    open dept_cur;--開啟遊標
    loop--通過迴圈遍歷遊標中的記錄
        fetch dept_cur into deptrow;--通過fetch提取遊標中記錄（每次一條）賦值給變數
        dbms_output.put_line(deptrow.deptno||':'||deptrow.dname);
        exit when dept_cur%notfound;--通過%notfound判斷遊標中是否還有記錄
    end loop;
    close dept_cur;--關閉遊標
end;

　　遊標除了 %notfound 還有以下常用的的屬性：

cursor%isopen;--檢測遊標是否已開啟，開啟返回ture，否則返回false
cursor%found;--檢測是否提取到值，提取到返回true，否者返回false
cursor%notfound;--與%found相反
cursor%rowcount;--統計到目前為止已提取的記錄數

　　PL/SQL 中的三種迴圈都可以用來迴圈遍歷遊標中的記錄，while 和 loop 相似，這裡不再舉例，for 迴圈專門對遍歷遊標做了強化，工作中使用最多，也最方便：

delcare
    cursor dept_cur is
    select * from dept;
begin
    for dept_row in dept_cur loop
        dbms_output.put_line(deptrow.deptno||':'||deptrow.dname);
    end loop;
end;

　　dept_row 不需要顯式的宣告為記錄型別，PL/SQL 引擎自動隱式的宣告為 %rowtype。for 迴圈開始，自動開啟遊標，並自動提取記錄，然後賦值給dept_row，不用顯式的使用 fetch 提取記錄，迴圈完畢自動關閉遊標並退出迴圈。

　　

　　5，包

　　Oracle 中包（package）是一個工程化和麵向物件的概念，它就像一個容器或名稱空間，把邏輯相關的變數、型別、子程式或異常等組合起來一起存放，形成一個有序的組織單元或模組，當我們編寫大型的複雜的應用程式時，我們就可以通過包來方便的歸類和管理各個功能模組。

　　完整的包由包規範和包體組成，但 Oracle 分開編譯的儲存包規範和包體，這又使得我們可以脫離包體使用包規範（反向不行）。包規範中主要是一些定義資訊（也可以看成是 PL/SQL 提供的 API），比如記錄型別、變數、遊標、異常和子程式的宣告。包體則負責實現包規範中定義的子程式。

　　包規範簡單應用：

create or replace package pkg1--建立包規範
as
  i int := 1;--標量變數
  dept_record dept%rowtype;--rowtype型別
  type dept_tab is table of varchar(20) index by pls_integer;--集合型別
end pkg1;

declare
  mydept pkg1.dept_tab;--建立一個包中集合型別的變數（通過"包.內容"的方式訪問包中的內容）
begin
  select * into pkg1.dept_record from dept where deptno=10;--給包中定義的rowtype型別變數賦值
  dbms_output.put_line(pkg1.dept_record.dname);--訪問包中的rowtype型別變數
  dbms_output.put_line('-------------------------------------------');--分割線
  for deptrow in (select * from dept) loop--使用遊標給包中的集合賦值
    mydept(pkg1.i) := deptrow.dname;
    pkg1.i := pkg1.i+1;--修改包中的標量變數
  end loop;
  for j in 1..mydept.count loop--使用迴圈訪問集合
    dbms_output.put_line(mydept(j));
  end loop;
  pkg1.i := 1;--初始化包中的標量變數（防止下一次遊標讀取不到資料）
end;

 　　在這個例子中，我們只建立了包規範，沒有包體，並且在包中定義了標量變數，rowtype型別（記錄型別同理），集合這些基本的資料型別，然後在 PL/SQL 程式塊中使用了他們。

　　包規範中只有宣告，沒有具體的實現，事實上，包規範中的宣告的內容是公共的，對於一個方案來說，相當於一個全域性的物件，在包內任何地方都能訪問他們。包規範和包體分別進行獨立的編譯和儲存，所以沒有包體，上訴例子任然能正常執行。

　　另一個例子：

create or replace package pkg2--建立包規範
as
  cursor dept_cur return dept%rowtype;--定義遊標型別
  procedure dept_ins(p_deptno int,p_dname varchar);--定義儲存過程
  function f2 return varchar;--定義函式
end pkg2;

create or replace package body pkg2--建立包體
as
  cursor dept_cur return dept%rowtype--建立遊標
  is
    select * from dept;
  procedure dept_ins(p_deptno in int,p_dname in varchar)--建立儲存過程
  as
  begin
    insert into dept(deptno,dname) values(p_deptno,p_dname);
    dbms_output.put_line('新增了部門：'|| p_deptno||','||p_dname);
  end dept_ins;
  function f2 return varchar--建立函式
  is
  begin
    return '這是個函式';
  end f2;
end pkg2;


begin
  for deptrow in pkg2.dept_cur loop--讀取遊標
    dbms_output.put_line(deptrow.dname);
  end loop;
  pkg2.dept_ins(99,'TI');--執行儲存過程
  dbms_output.put_line(pkg2.f2());--執行函式
end;

　　上面的例子在包體中定義了遊標，儲存過程和函式，並且在包規範中也宣告瞭他們，這時候，儲存過程和函式、遊標都是公開的了，如果在包體中建立的內容並未在包規範中定義，那麼我們說，這些內容是包私有的，不能在其他地方呼叫，而只能在包體內部使用。

　　合理的使用包，有助於我們進行模組化的程式開發；把邏輯相關的東西放在一個包中進行開發和管理，是我們的程式更加規範化；把一些重要的東西定義成包的私有內容，可以大大加強資料的安全性；另外，由於在使用包時， PL/SQL 會把整個包都載入到記憶體中，所以還可以提高程式執行效率。