前言
程序亦是在資料庫內的具名區塊，可以在資料庫中直接運行，因此可以撰寫SQL敘述，將邏輯封裝至程序中，減少Client端應用程式與資料庫的連線次數，待資料庫將資料處理後再返回至Client端應用程式，透過減少資料往返的時間，亦可提升執行效能。不過大量的處理接交給資料庫時，資料庫的負擔亦會相對提升。
實作
使用語法如下
1
2
3
4
5
6
| CREATE [OR REPLACE] PROCEDURE 程序名稱
[(參數名稱 [IN | OUT | IN OUT] 型態 [, ...])]
{IS | AS}
BEGIN
.....
END [程序名稱];
|
程序不像函數一般有回傳值，但可透過參數類型IN、OUT以及IN OUT進行值的傳遞，其實在函數建立語法亦有此種用法，不過於函數設計多以IN參數為主，因此將此類主題放置程序這邊介紹，以下進行相關說明。
- IN：由外部提供的參數，供程序進行運算。此參數類型 IN 可省略。
- OUT：由程式運算後，傳出給外部變數的參數。此參數類型 OUT 不可省略。
- IN OUT：由外部提供的參數，供程式運算後，再透過相同參數傳出。此類型參數 IN OUT 不可省略。
以下進行相關範例
資料儲存範例
儲存資料於楊藝撰寫Java時經常會進行資料是否已存在的判斷，若不存在才進行資料寫入動作，楊藝是偏向盡可能不要以Exception來進行這些本應檢查的邏輯，否則若KEY重複，資料庫亦會拋出Exception，因此楊藝將原本撰寫於應用程式中的邏輯轉換為PL/SQL撰寫成程序。
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
| -- 建立程序
CREATE PROCEDURE SAVE_ARTICLE (IN_TITLE IN VARCHAR2, OUT_RESULT OUT VARCHAR2)
IS
V_COUNT INT;
BEGIN
-- 進行資料是否已存在查詢
SELECT COUNT(*) INTO V_COUNT FROM ARTICLE WHERE TITLE = IN_TITLE;
-- 判斷資料是否已存在，若以存在將回傳結果設成 FAILED 並終止程序
IF V_COUNT > 0 THEN
OUT_RESULT := 'FAILED';
RETURN;
END IF;
-- 新增資料
INSERT INTO ARTICLE (ID, TITLE, CREATE_DATE) VALUES (SYS_GUID(), IN_TITLE, SYSDATE);
-- 確認交易
COMMIT;
-- 將回傳結果設成 SUCCESS
OUT_RESULT := 'SUCCESS';
END;
-- 進行測試 (資料寫入失敗範例)
VAR SAVE_RESULT VARCHAR2(10);
EXECUTE SAVE_ARTICLE('楊藝的洋溢生活 - 我的第一篇 PL/SQL 筆記', :SAVE_RESULT);
PRINT SAVE_RESULT;
-- 進行測試 (資料寫入成功範例)
VAR SAVE_RESULT VARCHAR2(10);
EXECUTE SAVE_ARTICLE('PHP 使用 PDO 建立資料庫連線 (使用 MariaDB/MySQL 進行查詢)', :SAVE_RESULT);
PRINT SAVE_RESULT;
|
執行結果
1
2
3
4
5
6
7
8
9
| -- 進行測試 (資料寫入失敗範例)
SAVE_RESULT
--------------------------------------------------------------------------------
FAILED
-- 進行測試 (資料寫入成功範例)
SAVE_RESULT
--------------------------------------------------------------------------------
SUCCESS
|
資料撈取範例
於Oracle當中使用程序無法直接透過SELECT語法將撈取的資料表資料直接回傳，因此需要透過SYS_REFCURSOR資料類型的幫忙，透過此方式可讓應用程式取得程序撈取資料表的結果集，以下先進行於Oracle資料庫內的使用範例，往後在整理如後透過Java取得程序回傳的結果集。
1
2
3
4
5
6
7
8
9
10
11
| -- 建立程序
CREATE PROCEDURE ARTICLE_LIST(OUT_RESULT OUT SYS_REFCURSOR)
AS
BEGIN
OPEN OUT_RESULT FOR SELECT * FROM ARTICLE;
END;
-- 取得資料集
VAR DATA_RESULT REFCURSOR;
EXECUTE ARTICLE_LIST(:DATA_RESULT);
PRINT DATA_RESULT;
|
執行結果
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
| ID TITLE CREATE_DATE MODIFY_DATE
-------------------------------- ---------------------------------------------------------------------------------------------------- ------------------- -------------------
7C5E3A5AD847F0D3E050A8C0FD894690 楊藝的洋溢生活 - 我的第一篇 PL/SQL 筆記 2018-12-07 10:59:37 2018-12-08 12:39:53
7C5E3A5AD851F0D3E050A8C0FD894690 楊藝的洋溢生活 - Oracle PL/SQL 游標鎖定 (FOR UPDATE) 2018-12-10 00:54:25
7C5E3A5AD848F0D3E050A8C0FD894690 楊藝的洋溢生活 - 我的 JAVA 學習歷程 2018-12-07 12:13:23 2018-12-08 12:39:53
7C5E3A5AD849F0D3E050A8C0FD894690 楊藝的洋溢生活 - C# 程式架構 2018-12-07 12:16:20 2018-12-08 12:39:53
7C5E3A5AD84AF0D3E050A8C0FD894690 楊藝的洋溢生活 - Eclipse 程式碼提示設定 2018-12-07 12:16:20 2018-12-08 12:39:53
7C5E3A5AD84BF0D3E050A8C0FD894690 楊藝的洋溢生活 - TightVNC 檔案傳輸 2018-12-07 12:16:20 2018-12-08 12:39:53
7C5E3A5AD84CF0D3E050A8C0FD894690 楊藝的洋溢生活 - Apache Struts 2 檔案上傳 2018-12-07 12:16:20 2018-12-08 12:39:53
7C5E3A5AD84DF0D3E050A8C0FD894690 楊藝的洋溢生活 - Apache Struts 2 表單驗證 (驗證框架) 2018-12-07 12:16:20 2018-12-08 12:39:53
7C5E3A5AD84EF0D3E050A8C0FD894690 楊藝的洋溢生活 - Apache Struts 2 攔截器設定與使用 2018-12-07 12:16:20 2018-12-08 12:39:53
7C5E3A5AD84FF0D3E050A8C0FD894690 楊藝的洋溢生活 - MySQL 外部 IP 連線 (Ubuntu Server) 2018-12-07 12:16:20 2018-12-08 12:39:53
7C5E3A5AD850F0D3E050A8C0FD894690 楊藝的洋溢生活 - CentOS 切換 root 免密碼設定 2018-12-07 12:16:20 2018-12-08 12:39:53
ID TITLE CREATE_DATE MODIFY_DATE
-------------------------------- ---------------------------------------------------------------------------------------------------- ------------------- -------------------
7CA41C50E916E0AAE050A8C0FD890E9D PHP 使用 PDO 建立資料庫連線 (使用 MariaDB/MySQL 進行查詢) 2018-12-10 12:08:00
|