月份: 2021 年 1 月

　　結構化查詢語言（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 數據庫開發的能力，大家可以根據需要選擇合適的開發工具。

　　3，基本概念

　　如果你對 SQL Server 有一定了解，你應該知道，它裏面可以創建很多的數據庫，用於存儲不同業務或系統的數據，每個數據庫都有單獨的數據文件（物理磁盤存儲的）。Oracle 中有點不同，嚴格來說 Oracle 通常只有一個數據庫，然後通過表空間和方案來實現多業務的數據分離。

　　Oracle 通過表空間來存儲不同的內容，表空間是數據庫的邏輯劃分，每個數據庫至少有一個表空間(SYSTEM 表空間)。為了便於管理和提高運行效率，可以使用一些附加表空間來劃分用戶和應用程序。例如：USER 表空間供一般用戶使用，RBS 表空間供回滾段使用，一個表空間只能屬於一個數據庫。

　　方案是另一個 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。

　　創建表和約束：

 1 --在列后添加約束
 2 create table table_name  3 (  4     col1 type constraint,  5  ...  6 )  7 --單獨添加約束
 8 create table table_name  9 ( 10  col1 type, 11  ...., 12  constration cons_name cons_type 13 ) 14 --在Oracle中創建表和約束與標準SQL相同

　　創建索引和視圖：

 1 --創建索引（非唯一）
 2 --默認系統會在具有unique和primary key的列上創建唯一約束
 3 create index index_name on (col1...);  4 --當提供多個列時，即創建複合索引
 5 --創建視圖
 6 create or replace view view_name  7 as
 8 select ...;  9 --創建，如果已存在則修改視圖
10 create view ...
11 as
12 ... 13 with read only; 14 --創建只讀的視圖（推薦）

　　修改表或視圖：

1 --為表增加新的列
2 alter table table_name 3 add col_name type constration; 4 --移除表中已有的列
5 alter table table_name 6 drop column col_name;

　　刪除數據庫對象：

1 --刪除表
2 drop table table_name; 3 --刪除視圖
4 drop view view_name; 5 ...

　　

　　3，數據查詢

　　A：標準查詢

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

　　B：dual 表

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

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

　　C： 偽列

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

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

1 --查詢員工表中前10位員工相關信息
2 select rownum,ename,sal from emp 3 where rownum<=10; 4 -- 測試數據庫 Oracle 11g 

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

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

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

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

1 --查看rowid
2 select rowidtochar(rowid) ename,sal from emp; 3 --基於rowid刪除表中形同的記錄
4 delete from emp 5 where rowid not in ( 6     select min(rowid) from emp group by empno 7 );

　　

　　4，數據操縱

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

　　A：insert 插入

 1 --方式一
 2 insert into table_name(column list)--如果不提供字段列表，下面的值列表需要提供每個字段的值，即使可以為空或有默認值
 3 values 
 4 (value list),  5 (value list),  6 ....  7 --方式二
 8 insert into table_name  9 select ... 10 --從其他查詢獲取數據，並插入表，數據必須符合表的約束

　　B：update 更新

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

　　C：delete

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

　　

　　5，序列

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

1 create sequence seq_name 2 increment by n --自增的步長，（省略該選項則）默認為1，負數表示遞減
3 start with n --序列的初始值，默認為1
4 max value n | nomaxvalue --指定最大值或沒有最大值(無限增長)
5 min value n  | nominvalue --指定最小值或沒有最小值(無限減小)
6 cycle | nocycle --規定設置的序列到達最大或最小時是否從開頭循環
7 cache n | nocache --規定是否在內存中緩存序列值，以改善性能

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

1 create sequence my_seq 2 start with 1
3 nomaxvalue 4 nocycle;

　　序列也是 Oracle 數據庫對象之一，序列有兩個常用的屬性：nextval、currval。

1 select my_seq.nextval from dual;--獲取下一個序列值
2 select my_seq.currval from dual;--查看當前序列值
3 --在插入數據是使用序列
4 insert into table_name 5 values
6 (my_seq.nextval,...) 7 --使用循環批量插入時非常方便

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

　　修改和刪除序列：

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

 

三　　Oracle 內置函數

 

　　1，字符串函數

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

　　

　　2，數學函數

 1 --絕對值
 2 select abs(-123) from dual;  3 --向上取整
 4 select ceil(1.2),ceil(-1.2) from dual;  5 --向下取整
 6 select floor(1.8),floor(-1.8) from dual;  7 --返回自然常數 e 的 n 次方
 8 select exp(5) from dual;  9 --返回以第一個參數為底的第二個參數的對數
10 select log(3,10) from dual; 11 --求模,如果第二個參數為0，則返回第一個參數
12 select mod(10,3) from dual; 13 --返回第一個參數的第二個參數次方
14 select power(2，3) from dual; 15 --保留指定小數位，最後一位小數四舍五入得來
16 select round(1.2345,3) from dual; 17 --保留指定小數位，其餘直接截斷
18 select trunc(1.2345,3) from dual; 19 
20 --格式化数字(格式位數應該與数字位數相同)
21 
22 --用0格式化時，如果数字位數不夠，結果會用0補齊位數
23 select to_char(123456789000,'000,000,000,000,000') from dual; 24 --用9格式化時，如果数字位數不夠，結果會用空格補齊位數
25 select to_char(123456789000,'999,999,999,999,999') from dual; 26 --使用fm格式化小數
27 select to_char(123456.258,'fm999,999,999.99') from dual; 28 --使用 $（美元） 或 L（當地） 添加貨幣符號
29 select to_char(123.456,'L999.999') from dual; 30 /* 注意貨幣符號和小數不能一起使用 */

 

　　3，時間和日期函數

 1 --返回操作系統日期
 2 select sysdate from dual;  3 --返回日期部分
 4 select current_date from dual;  5 --返回日期+時間
 6 select current_timestamp from dual;  7 --返回操作系統日期—+時間（包含時區信息）
 8 select systimestamp from dual;  9 --按格式化日期為字符串
10 select to_char(sysdate,'YYYY-MM-DD HH:MM:SS') from dual; 11 --把字符串表示的日期轉換成日期類型的值返回（前後格式需保持一致）
12 select to_date('2020-05-28 17:02:00','YYYY-MM-DD HH24:MI:SS') from dual; 13 --把字符串表示的日期轉換成日期 + 時間類型的值返回（前後格式需保持一致）
14 select to_timestamp('2020-05-28 17:02:00','YYYY-MM-DD HH24:MI:SS') from dual; 15 --返回指定日期後幾個月的日期
16 select add_months(sysdate,1) from dual; 17 --返回兩個日期間間隔月數(注意正負)
18 select months_between(sysdate,to_date('2020-07-01','YYYY-MM-DD')) from dual; 19 --把日期按指定精度截斷,可選參數有yyyy(精確到年，返回當年的第一天的日期),mm(精確到月，返回當月第一天的日期),rr(精確到日，返回當天的日期)
20 select trunc(sysdate,'mm') from dual; 21 
22 /* ----------------------日期可選格式--------------------- */
23 TO_CHAR(sysdate, 'DD-MON-YYYY HH24:MI:SS') 24 TO_CHAR(sysdate, 'DD-MON-YYYY HH12:MI:SS PM') 25 TO_CHAR(systimestamp, 'DD-MON-YYYY HH24:MI:SS.FF') 26 TO_CHAR(sysdate, 'DY, DD-MON-YYYY') 27 TO_CHAR(sysdate,'Month DDth, YYYY') 28 TO_CHAR(systimestamp, 'DD-MON-YYYY HH24:MI:SS TZH:TZM') 29 TO_CHAR(sysdate, 'MM/DD/YYYY HH24:MI:SS') 30 TO_CHAR(sysdate, 'MM/DD/YY HH24:MI:SS') 31 TO_CHAR(sysdate, 'MM/DD/RRRR HH12:MI:SS PM') 32 TO_CHAR(sysdate, 'MM/DD/RR HH12:MI:SS PM')

 

　　4，聚合函數

 1 --計算行數（不計算空值）
 2 select count(*) from emp;--根據所有列計算
 3 select count(comm) from emp;--根據某一列計算（注意該列是否有空值）
 4 select count(distinct deptno) from emp;--計算deptno中不同值的個數
 5 --計算列的最大/小值
 6 select max(sal),min(sal) from emp;  7 --返回中間值
 8 select median(sal) from emp;  9 --返回標準差
10 select stddev(sal) from emp; 11 --求和
12 select sum(sal) from emp; 13 --計算方差
14 select variance(sal) from emp; 15 --偽列 rownum,每條數據的序號
16 select rownum,empno,ename,sal from emp;

 

四　　變量和類型

 

　　1，PL/SQL 基礎

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

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

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

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

1 declare
2 --... 包括變量、游標等
3 begin
4 --... 業務代碼
5 exception 6 --... 異常處理
7 end;

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

1 --注意，PL/SQL業務代碼必須運行在 begin...end 中
2 begin
3 dbms_output.put_line('hello world'); 4 end; 5 --沒有聲明和異常部分

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

1 <<outer>>--oracle 11g 不允許給最外層塊命名
2 begin
3 dbms_output.put_line('outer block'); 4   <<inner>>
5   begin
6   dbms_output.put_line('inner block'); 7   end; 8 end;

　　

　　2，變量

　　PL/SQL 中的變量在 declare 區域聲明，不需要額外的標識符，只需要提供變量名和值類型即可。

1 declare
2   v_name emp.ename%type;--通過動態獲取表中列的數據類型，來確定變量的數據類型
3   v_job  varchar(50);--直接指定具體的數據類型
4 begin
5   v_name:='&name';--通過:=為變量賦值
6 end;

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

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

1 declare
2     v_job emp.job%type; 3 begin
4     select job into v_job from emp where ename=v_name;--通過select...into 為變量賦值
5     dbms_output.put_line(v_job);--輸出變量值
6 end;

　　

　　3，記錄類型

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

 1 declare
 2   type emp_record is record(--這裏相當於定義了一種新的數據類型，類型名稱是emp_record,和varcahr，int等類型一樣
 3   v_name emp.ename%type,  4   v_job emp.job%type,  5   v_sal emp.sal%type  6  );  7   --記錄類型類似其他編程語言中的類
 8   v_emp_record emp_record;--聲明一個emp_record類型的變量，相當於創建一個類的實例
 9 begin
10   select ename,job,sal into v_emp_record from emp where ename='ALLEN';--注意查詢的順序必須和記錄類型中定義的順序一致
11   dbms_output.put_line(v_emp_record.v_name||' '||v_emp_record.v_job||' '||v_emp_record.v_sal); 12   --通過實例訪問相關屬性
13 end;

　　%rowtype：

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

　　

　　4，集合

　　集合類似其他編程語言中的數組，也可以通過下標來訪問數據。

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

　　Oracle 提供了三種類型的集合：索引表（又稱關聯數組）、嵌套表、可變長度數組。

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

1 declare
2   type idx_table is table of varchar(20) index by pls_integer;--創建索引表類型
3   -- index by 后可選的參數有pls_integer、binary_integer、varcahr(size)和使用%type 指定的varchar2類型
4   v_idx_table idx_table;--聲明索引表類型的變量
5 begin
6   v_idx_table(1):='hello';--插入值
7   v_idx_table(2):='world'; 8   dbms_output.put_line(v_idx_table(1)||' '||v_idx_table(2)); 9 end;

　　嵌套表只能使用数字作為下標，数字必須是有序的，嵌套表的容量沒有限制，可以保存到數據庫中。

 1 declare 
 2   type nest_table is table of varchar(20);--創建嵌套表類型
 3   v_nest_table nest_table:=nest_table('x');--聲明嵌套表類型的變量並初始化
 4   --未初始化的嵌套表類型實際上是一個null，如果試圖為其賦值會報錯。初始化就是調用一個和創建的嵌套表類型同名的函數，
 5   --函數的參數值類型需要和嵌套表類型定義的存儲值類型（of 后的類型）相同，並且參數的個數默認就是這個嵌套表類型變量的初始容量
 6 begin
 7   v_nest_table.extend(5);--擴充嵌套表類型變量的容量
 8   --如果要增加嵌套表的容量，需要調用extend方法（該方法將在稍後詳細說明）
 9   v_nest_table(1):='hello';--插入值
10   v_nest_table(2):='world'; 11   dbms_output.put_line(v_nest_table(1)||' '||v_nest_table(2)); 12   dbms_output.put_line(nvl(v_nest_table(3),'it is null'));--沒被使用的位置為null
13 end;

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

 1 declare
 2   type varr is varray(5) of int;--創建可變數組類型
 3   v_varr varr:=varr();--聲明可變數組類型的變量並初始化
 4   --和嵌套表一樣的原因，必須初始化
 5 begin
 6   for i in 1..5 loop--循環插入值
 7  v_varr.extend();  8     v_varr(i):=i;  9     end loop; dbms_output.put_line(v_varr(1)||','||v_varr(2)||','||v_varr(3)||','||v_varr(4)||','||v_varr(5)); 10 end;

　　嵌套表和可變數組能存入數據庫是指：他們可以和普通數據類型一樣，用來定義表的列。

 1 --第一步，創建一個保存在數據庫中的嵌套表類型
 2 create or replace type nest is table of varchar(20);  3 --第二步，創建一個帶有嵌套表類型列的數據表
 4 create table x(  5   x_id int,  6  x_nest nest  7 )nested table x_nest store as y;--使用nest table 指定這是一個包含嵌套表類型值的數據表，並通過 store as 創建一個關聯表來專門存儲嵌套表  8 --插入一條數據（包含初始化的嵌套表類型值）
 9 insert into x values(1,nest('x','y','z')); 10 --第三步，在PL/SQL中讀取嵌套表類型的值（多行操作使用游標）。數據表並沒有直接存儲嵌套表，所以不能直接使用select查詢，而應該在PL/SQL程序塊中查詢
11 declare
12  v_nest nest; 13 begin
14   select x_nest into v_nest from x; 15   for i in 1..3 loop 16  dbms_output.put_line(v_nest(i)); 17   end loop; 18 end;

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

　　

　　5，集合常用方法

　　集合的方法通過“.”點的形式調用：集合.方法。

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

 　　

　　6，變量的作用域

 1 declare
 2  v1 int default 1;--外層塊變量v1
 3 begin
 4  dbms_output.put_line(v1);  5  --dbms_output.put_line(v2);error 必須聲明v2
 6  declare
 7   v2 int default 2;---內層塊變量
 8   v1 int default 3;  9  begin
10   dbms_output.put_line(v1);--返回3
11  end; 12 end;

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

 

五　　流程控制

　　

　　1，case

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

　　先來看簡單 case 語法：

 1 declare
 2   v_sal int;  3 begin
 4   select sal into v_sal from emp where empno=&empno;  5   case v_sal  6     when 800 then dbms_output.put_line('太少了吧');  7     when 1600 then dbms_output.put_line('這還差不多');  8     when 3000 then dbms_output.put_line('這樣更好');  9     when 5000 then dbms_output.put_line('這樣最好'); 10     else dbms_output.put_line('隨緣吧'); 11     end case; 12 end;

　　搜索 case 語法：

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

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

　　

　　2，if…elsif…else

 1 declare
 2   v_sal int;  3 begin
 4   select sal into v_sal from emp where empno=&empno;  5   if v_sal>=5000
 6     then dbms_output.put_line('還有頭髮嗎');  7   elsif v_sal>=3000
 8     then dbms_output.put_line('還有一半嗎');  9   else
10     dbms_output.put_line('好好珍惜頭髮啊，少年'); 11   end if; 12 end;

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

　　

　　3，循環

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

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

　　loop 循環：

 1 declare
 2   i int default 1;--定義，初始化循環控制變量
 3 begin
 4  loop  5     if i=5 then
 6       i:=i+1;  7       continue;--當n等於5時，直接結束本次循環，不輸出
 8     end if;  9  dbms_output.put_line(i); 10     i:=i+1;--修改循環控制變量 
11     exit when i>10;--根據循環控制比變量，判斷是否退出循環
12   end loop;--結束循環
13 end;

　　while 循環：

1 declare
2   i int default 1;--定義，初始化循環控制變量
3 begin
4   while i<=10 loop--根據循環控制變量，判斷是否進入循環體
5     dbms_output.put_line(i);--循環體
6     i:=i+1;--修改循環控制變量
7   end loop;--結束循環
8 end;

　　for 循環：

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

　　

　　4，雜項

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

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

 1 declare
 2   i int:=0;  3 begin
 4   <<outer>>--定義一個標籤
 5   i:=i+1;  6  dbms_output.put_line(i);  7   if i<10 then
 8     goto outer;--通過goto實現類似循環的結構
 9   else
10     null;--通過使用null讓語句塊更易讀
11   end if; 12 end;

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

 

六　　異常處理

　　

　　1，異常簡介

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

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

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

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

　　

　　2，異常處理語法

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

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

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

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

 1 declare
 2  異常變量名 exception;  3 begin
 4  ...  5  raise 異常變量名;  6  ...  7  exception  8       when 異常變量名  9         then ... 10 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，自定義錯誤

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

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

 

七　　編程對象

　　

　　1，事務

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

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

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

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

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

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

1 begin
2   commit; 3   set transaction read only;--只讀的事務
4   --settransaction read write;--可讀寫的事務
5   --set transaction isolation level [serializable | read commited];
6   --serializable：整個事務只能讀到當前事務開始前就以提交的數據
7   --read commited：當前事務中的查詢，只能讀到該查詢前以提交的數據（不是整個事務，而是該查詢語句。這也是 Oracle 默認的隔離級別）
8 end;

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

　　

　　2，子程序

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

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

　　存儲過程：

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

 1 --帶參數的過程
 2 create or replace procedure p2(p_deptno in int)--使用括號添加過程需要的形參
 3 as
 4   v_empcount number;--定義過程中需要使用的變量，只需指定數據類型，不能添加類型所佔字節長度
 5 begin
 6   select count(ename) into v_empcount from emp where deptno=p_deptno;  7   if v_empcount>0 then
 8     dbms_output.put_line('有人');  9   else
10     dbms_output.put_line('沒人'); 11   end if; 12 end p2;--不要忘了過程名

1 --調用存儲過程
2 begin
3   p2(20);--通過（）傳遞實參
4 end; 5 --call p2(20);

　　函數：

 1 --創建函數
 2 create or replace function f1  3 return number--需要指定返回值類型，不需要長度
 4 as
 5 begin
 6   return 1;--需要使用return指定返回值
 7 end f1;  8 --調用函數
 9 declare 
10   v_f1 number(10); 11 begin
12   v_f1:=f1();--調用函數，並把返回值賦值給變量
13  dbms_output.put_line(v_f1); 14 end;

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

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

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

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

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

　　

　　3，觸發器

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

　　定義觸發器的語法：

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

　　一個簡單例子：

 1 create test(--創建測試表
 2     id int primary key,  3     name varchar(20)  4 )  5 create or replace trigger t_test--創建觸發器
 6 after insert or update or delete--觸發操作（也可以是其中一種）
 7 on test--在表test上
 8 for each row--行級觸發器
 9 begin
10     if inserting then--在插入數據時
11         dbms_output.put_line('插入了數據，name：'||:new.name); 12     end if; 13     if updating then--在更新數據時
14         dbms_output.put_line('更新了數據，oldname：'||:old.name||',newname:'||:new.name); 15     end if; 16     if deleting then--在刪除數據時
17         dbms_output.put_line('刪除了數據，name：'||:old.name); 18     end if; 19 end;

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

1 ... 2 referencing old as test_old new as test_new 3 ... 4 --下面通過:test_old 引用修改前的數據，:test_new引用修改后的數據

　　測試代碼：

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

　　

　　4，游標

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

1 cursor cursor_name [形參]--形參可以用來在where子句中限定游標記錄
2 [return type]--可選的指定游標返回的值類型
3 is query--通過is指定查詢（在這裏使用形參）
4 [for update[of column_list]]--允許在游標中修改表中的數據，並在游標打開期間鎖定選中的記錄

　　下面是一個通過游標遍歷輸出 dept 部門信息的例子：

 1 declare
 2     deptrow dept%rowtype;--定義一個存儲記錄的變量
 3     cursor dept_cur is--通過cursor定義游標，is指定需要遍歷的結果集（一個查詢語句）
 4     select * from dept;  5 begin
 6     open dept_cur;--打開游標
 7     loop--通過循環遍歷游標中的記錄
 8         fetch dept_cur into deptrow;--通過fetch提取游標中記錄（每次一條）賦值給變量
 9         dbms_output.put_line(deptrow.deptno||':'||deptrow.dname); 10         exit when dept_cur%notfound;--通過%notfound判斷游標中是否還有記錄
11     end loop; 12     close dept_cur;--關閉游標
13 end;

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

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

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

1 delcare 2     cursor dept_cur is
3     select * from dept; 4 begin
5     for dept_row in dept_cur loop 6         dbms_output.put_line(deptrow.deptno||':'||deptrow.dname); 7     end loop; 8 end;

　　dept_row 不需要顯式的聲明為記錄類型，PL/SQL 引擎自動隱式的聲明為 %rowtype。for 循環開始，自動打開游標，並自動提取記錄，然後賦值給dept_row，不用顯式的使用 fetch 提取記錄，循環完畢自動關閉游標並退出循環。

　　

　　5，包

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

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

　　包規範簡單應用：

 1 create or replace package pkg1--創建包規範  2 as
 3   i int := 1;--標量變量
 4   dept_record dept%rowtype;--rowtype類型
 5   type dept_tab is table of varchar(20) index by pls_integer;--集合類型  6 end pkg1;  7 
 8 declare
 9  mydept pkg1.dept_tab;--創建一個包中集合類型的變量（通過"包.內容"的方式訪問包中的內容） 10 begin
11   select * into pkg1.dept_record from dept where deptno=10;--給包中定義的rowtype類型變量賦值 12  dbms_output.put_line(pkg1.dept_record.dname);--訪問包中的rowtype類型變量 13   dbms_output.put_line('-------------------------------------------');--分割線 14   for deptrow in (select * from dept) loop--使用游標給包中的集合賦值 15     mydept(pkg1.i) := deptrow.dname; 16     pkg1.i := pkg1.i+1;--修改包中的標量變量 17   end loop; 18   for j in 1..mydept.count loop--使用循環訪問集合 19  dbms_output.put_line(mydept(j)); 20   end loop; 21   pkg1.i := 1;--初始化包中的標量變量（防止下一次游標讀取不到數據） 22 end;

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

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

　　另一個例子：

 1 create or replace package pkg2--創建包規範  2 as
 3   cursor dept_cur return dept%rowtype;--定義游標類型  4   procedure dept_ins(p_deptno int,p_dname varchar);--定義存儲過程  5   function f2 return varchar;--定義函數  6 end pkg2;  7 
 8 create or replace package body pkg2--創建包體  9 as
10   cursor dept_cur return dept%rowtype--創建游標 11   is
12     select * from dept; 13   procedure dept_ins(p_deptno in int,p_dname in varchar)--創建存儲過程 14   as
15   begin
16     insert into dept(deptno,dname) values(p_deptno,p_dname); 17     dbms_output.put_line('新增了部門：'|| p_deptno||','||p_dname); 18   end dept_ins; 19   function f2 return varchar--創建函數
20   is
21   begin
22     return '這是個函數'; 23   end f2; 24 end pkg2; 25 
26 
27 begin
28   for deptrow in pkg2.dept_cur loop--讀取游標 29  dbms_output.put_line(deptrow.dname); 30   end loop; 31   pkg2.dept_ins(99,'TI');--執行存儲過程 32  dbms_output.put_line(pkg2.f2());--執行函數 33 end;

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

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

本站聲明:網站內容來源於博客園,如有侵權,請聯繫我們,我們將及時處理

【其他文章推薦】

※帶您來了解什麼是 USB CONNECTOR  ?

※自行創業缺乏曝光? 網頁設計幫您第一時間規劃公司的形象門面

※如何讓商品強力曝光呢? 網頁設計公司幫您建置最吸引人的網站，提高曝光率!

※綠能、環保無空污,成為電動車最新代名詞，目前市場使用率逐漸普及化

※廣告預算用在刀口上，台北網頁設計公司幫您達到更多曝光效益

※教你寫出一流的銷售文案?

※別再煩惱如何寫文案,掌握八大原則!