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。

　　創建表和約束：

 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