【參賽作品99】MogDB/openGauss關于PL/SQL匿名塊呼叫測驗

作者：lmj

一、原理介紹

PL/SQL(Procedure Language/Structure Query Language)是標準SQL語言添加了程序化功能的一門程式設計語言，

單一的SQL陳述句只能進行資料操作，沒有流程控制，無法開發復雜的應用，PL/SQL語言是結合了結構化查詢與資料庫自身程序控制為一體的強大語言，

1.PL/SQL原理

PL/SQL是一種塊結構的語言，它將一組陳述句放在一個塊中，一次性發送給服務器，

PL/SQL引擎分析收到PL/SQL陳述句塊中的內容，把其中的程序控制陳述句由PL/SQL引擎自身去執行,把PL/SQL塊中的SQL陳述句交給服務器的SQL陳述句執行器執行，

PL/SQL塊發送給服務器后，先被編譯然后執行，對于有名稱的PL/SQL塊（如子程式）可以單獨編譯，永久的存盤在資料庫中，隨時準備執行，

PL/SQL是一種塊結構的語言，一個PL/SQL程式包含了一個或者多個邏輯塊，邏輯塊中可以宣告變數，變數在使用之前必須先宣告，

2.PL/SQL特點

–與SQL緊密結合
–支持面向物件編程
–更好的性能
–可移植性
–安全性

3.語法結構

除了正常的執行程式外，PL/SQL還提供了專門的例外處理部分進行例外處理

[DECLARE
     --declaration statements]  ①
BEGIN
     --executable statements   ②
[EXCEPTION
     --exception statements]   ③
END;

語法決議
①宣告部分：宣告部分包含了變數和常量的定義，在此宣告PL/SQL用到的變數,型別及游標，以及區域的存盤程序和函式，
這個部分由關鍵字DECLARE開始，如果不宣告變數或者常量，可以省略這部分，
②執行部分：執行部分是 PL/SQL塊的指令部分，由關鍵字BEGIN開始，關鍵字END結尾，
所有的可執行PL/SQL陳述句都放在這一部分，該部分執行命令并操作變數，其他的PL/SQL塊可以作為子塊嵌套在該部分，
PL/SQL塊的執行部分是必選的，注意END關鍵字后面用分號結尾，
③例外處理部分：該部分是可選的，該部分用EXCEPTION關鍵字把可執行部分分成兩個小部分，之前的程式是正常運行的程式，
一旦出現例外就跳轉到例外部分執行，

4.PL/SQL陳述句塊的型別

1、匿名塊
2、命名塊
–①procedure 存盤程序
–②function 函式
–③package 包
–④trigger 觸發器

原本大家可能一提到PL/SQL就會想到ORACLE，ORACLE的PL/SQL很強大，它的匿名塊呼叫以及有名塊呼叫可以解決很多問題，在MOGDB/openGauss中，其實也有這樣的功能，如下，是我針對MOGDB/openGauss匿名塊的一些測驗，

二、匿名塊測驗

1.普通匿名塊呼叫

openGauss=# create table t1(a int ,b text);
CREATE TABLE

openGauss=# DECLARE
openGauss-# PRAGMA AUTONOMOUS_TRANSACTION;
openGauss-# BEGIN
openGaussKaTeX parse error: Expected 'EOF', got '#' at position 1: #? raise notice '…# insert into t1 values(1,‘I am lmj!’);
openGaussKaTeX parse error: Expected 'EOF', got '#' at position 1: #? END; openGauss# /
NOTICE: Normal anonymous block printing.

ANONYMOUS BLOCK EXECUTE
openGauss=# select * from t1;
a | b
—±----------
1 | I am lmj!
(1 row)

2.匿名塊和事務影響

啟動一個事務后，執行一個自治事務匿名塊，如果事務回滾，則匿名塊不回滾，

openGauss=# truncate table t1;
TRUNCATE TABLE

openGauss=# START TRANSACTION;
START TRANSACTION
openGauss=# DECLARE
openGauss-# PRAGMA AUTONOMOUS_TRANSACTION;
openGauss-# BEGIN
openGaussKaTeX parse error: Expected 'EOF', got '#' at position 1: #? raise notice '…# insert into t1 values(1,‘it will commit!’);
openGaussKaTeX parse error: Expected 'EOF', got '#' at position 1: #? END; openGauss# /
NOTICE: an autonomous transaction anonymous block.

ANONYMOUS BLOCK EXECUTE
openGauss=# insert into t1 values(1,‘you will rollback!’);
INSERT 0 1
openGauss=# rollback;
ROLLBACK
openGauss=# select * from t1;
a | b
—±----------------
1 | it will commit!
(1 row)

3.外部匿名塊和內部匿名塊

其中外部匿名塊是一個公共匿名塊，而內部匿名塊是一個自治事務匿名塊，可以根據如下例子和第二個例子對比事務回滾和匿名塊回滾

openGauss=# truncate table t1;
TRUNCATE TABLE

openGauss=# DECLARE
openGauss-# BEGIN
openGaussKaTeX parse error: Expected 'EOF', got '#' at position 1: #? DECLARE openGa…# PRAGMA AUTONOMOUS_TRANSACTION;
openGaussKaTeX parse error: Expected 'EOF', got '#' at position 1: #? BEGIN openGaus…# raise notice ‘just use call.’;
openGaussKaTeX parse error: Expected 'EOF', got '#' at position 1: #? insert into t1…# END;
openGaussKaTeX parse error: Expected 'EOF', got '#' at position 1: #? insert into t1…# rollback;
openGaussKaTeX parse error: Expected 'EOF', got '#' at position 1: #? END; openGauss# /
NOTICE: just use call.
ANONYMOUS BLOCK EXECUTE
openGauss=# select * from t1;
a | b
—±–
(0 rows)

4.匿名塊直接執行自治事務匿名塊并引發例外

openGauss=# DECLARE
openGauss-# PRAGMA AUTONOMOUS_TRANSACTION;
openGauss-# res int := 0;
openGauss-# res2 int := 1;
openGauss-# BEGIN
openGauss$# raise notice 'just use call.';
openGauss$# res2 = res2/res;
openGauss$# END;
openGauss$# /
NOTICE:  just use call.

ERROR: ERROR: division by zero
CONTEXT: PL/pgSQL function inline_code_block line 7 at assignment

匿名塊執行錯誤，會報出例外

5.例外捕獲

在執行期間引發例外后，將捕獲匿名塊，如下所示，在執行錯誤后，拋出autonomous throw exception提示

openGauss=# DECLARE
openGauss-# PRAGMA AUTONOMOUS_TRANSACTION;
openGauss-# res int := 0;
openGauss-# res2 int := 1;
openGauss-# BEGIN
openGauss$# raise notice 'error catch.';
openGauss$# res2 = res2/res;
openGauss$# EXCEPTION
openGauss$# WHEN division_by_zero THEN
openGauss$#     raise notice 'autonomous throw exception.';
openGauss$# END;
openGauss$# /
NOTICE:  error catch.

NOTICE: autonomous throw exception.

ANONYMOUS BLOCK EXECUTE