openGauss存儲過程功能全面升級，支持PACKAGE,PLDEBUGGER等諸多新特性

openGauss 2021-12-18

2480

存儲過程類似于面向過程語言當中的函數，可以實現面向過程語言當中的聲明變量、邏輯判斷、條件循環等操作，是一組完成特定功能的SQL語句集合。

在openGauss 2.1之前的版本中，存儲過程支持了定義變量、條件循環、邏輯判斷等基本功能，但是沒有面向對象語言中類的形式，因此無法對存儲過程中的變量以及函數進行封裝，也無法在存儲過程內使用全局變量。在以前的版本中沒有支持類似gdb的功能，用戶調試存儲過程只能夠使用raise info等打印變量的方式。并且在之前的版本當中，存儲過程發生異常后，沒有自治事務，記錄錯誤日志不便。

而在新的版本當中，通過支持PACKAGE、存儲過程調試/PLDEBUGGER、自治事務等新功能，解決了上述問題。下面將介紹新特性的應用場景以及使用方法。

特性一、PACKAGE

package是一組相關存儲過程、函數、變量、常量、游標等PL/SQL程序的組合，具有面向對象的特點，可以對PL/SQL程序設計元素進行封裝。package中的函數具有統一性，創建、刪除、修改都統一進行。

package包含包頭（Package Specification）和Package Body兩個部分，其中包頭所包含的聲明可以被外部函數、匿名塊等訪問，而在包體中包含的聲明不能被外部函數、匿名塊等訪問，只能被包體內函數和存儲過程等訪問。

下面可以看一組簡單的例子理解一下：

CREATE TABLE tab1(col1 int);
CREATE OR REPLACE PACKAGE PCK1 --包頭，在包頭內聲明的變量存儲過程等都為公有的，可以被外部訪問
IS
public_var1 int:=1; --在包頭聲明的公有變量public_var1，可以被外部訪問
procedure public_proc1(col1 int,col2 int); --只在包頭內聲明的存儲過程，因此為公有的，可以被外部訪問。
END PCK1;
/


CREATE OR REPLACE PACKAGE BODY PCK1 --指定了PACKAGE BODY關鍵字
IS
private_var1 int:=1; --在包體內聲明的私有變量private_var1,不能被外部訪問
procedure private_proc1(col1 int,col2 int) --只在包體內定義的存儲過程為私有存儲過程
is
begin
raise notice 'col1 + col2 = %',col1+col2;
insert into tab1 values(col1+col2);
end;
procedure public_proc1(col1 int,col2 int)--只在包體內定義的存儲過程為私有存儲過程
is
begin
private_proc1(1,2);
end;
--需與包頭保持一致
END PCK1;
/
> --package中的函數調用方式與存儲過程調用方式一樣
> call pck1.public_proc1(1,2);

特性二、PLDEBUGGER

DBE_PLDEBUGGER用于調試存儲過程，類似于GDB的功能，可以使用單步調試，設置斷點、打印調用堆棧等功能，方便了存儲過程的調試，減小了存儲過程的開發難度。詳細的使用方法以及說明可以參考在本文末提供的 [PLDEBUGGER接口及示例]

特性三、自治事務

自治事務（Autonomous Transaction），在主事務執行過程中新啟的獨立的事務。自治事務的提交和回滾不會影響主事務已提交的數據，同時自治事務也不受主事務影響。

自治事務在存儲過程、函數和匿名塊中定義，用PRAGMA AUTONOMOUS_TRANSACTION關鍵字來聲明。

自治事務一般用于存儲過程發生異常后，處理日志的時候。

下面可以看一個簡單的示例：

create table t2(a int, b int);
insert into t2 values(1,2);
select * from t2;
 
--創建包含自治事務的存儲過程
CREATE OR REPLACE PROCEDURE autonomous_proc1(a int, b int) ?AS 
DECLARE 
 ? ?num3 int := a;
 ? ?num4 int := b;
 ? ?PRAGMA AUTONOMOUS_TRANSACTION; --聲明此存儲過程為一個自治事務的存儲過程
BEGIN
 ? ?insert into t2 values(num3, num4); 
 ? ?dbe_output.print_line('just use call.');
END;
/
 --創建調用自治事務存儲過程的普通存儲過程
CREATE OR REPLACE PROCEDURE proc1(a int, b int) ?AS 
DECLARE 
c int:=0;
BEGIN
 ? ?dbe_output.print_line('just no use call.');
 ? ?insert into t2 values(5, 6);--異常后回滾
 ? ?c:=c/0;
 ? ?exception when others then
 ? ?autonomous_proc1(a,b);
END;
/
--調用普通存儲過程
select proc1(11,22);
select * from t2 order by a;
輸出結果：
a ?| b ?
----+----
1 | ?2
11 | 22
(2 rows)

pldebugger 接口及示例

接口名稱	描述
DBE_PLDEBUGGER.turn_on	server端調用，標記存儲過程可以調試，調用后執行該存儲過程時會hang住等待調試信息。
DBE_PLDEBUGGER.turn_off?	server端調用，標記存儲過程關閉調試。
DBE_PLDEBUGGER.local_ debug_server_info	server端調用，打印本session內所有已turn_on的存儲過程。?
DBE_PLDEBUGGER.attach	debug端調用，關聯到正在調試存儲過程。??
DBE_PLDEBUGGER.info_locals?	debug端調用，打印正在調試的存儲過程中的變量當前值。
DBE_PLDEBUGGER.next	debug端調用，單步執行。
DBE_PLDEBUGGER.continue?	debug端調用，繼續執行，直到斷點或存儲過程結束。
DBE_PLDEBUGGER.abort	debug端調用，停止調試，server端報錯長跳轉。??
DBE_PLDEBUGGER.print_var?	debug端調用，打印正在調試的存儲過程中指定的變量當前值。
DBE_PLDEBUGGER.info_code	debug和server端都可以調用，打印指定存儲過程的源語句和各行對應的行號。
DBE_PLDEBUGGER.step	debug端調用，單步進入執行。
DBE_PLDEBUGGER.add_ breakpoint	debug端調用，新增斷點。
DBE_PLDEBUGGER.delete_ breakpoint	debug端調用，刪除斷點。
DBE_PLDEBUGGER.info_ breakpoint?	debug端調用，查看當前的所有斷點。?
DBE_PLDEBUGGER.backtrace	debug端調用，查看當前的調用棧。?
DBE_PLDEBUGGER.enable_ breakpoint	debug端調用，激活被禁用的斷點。
DBE_PLDEBUGGER.disable_ breakpoint?	debug端調用，禁用已激活的斷點
DBE_PLDEBUGGER.finish	debug端調用，繼續調試，直到斷點或返回上一層調用棧。
DBE_PLDEBUGGER.set_var	debug端調用，為變量進行賦值操作。

示例

準備調試

通過PG_PROC，查找到待調試存儲過程的oid，并執行DBE_PLDEBUGGER.turn_on(oid)。本客戶端就會作為server端使用

CREATE OR REPLACE PROCEDURE test_debug ( IN ?x INT) 
AS ?
BEGIN
 ? ?INSERT INTO t1 (a) VALUES (x);
 ? ?DELETE FROM t1 WHERE a = x;
END;
/
輸出結果:
CREATE PROCEDURE
SELECT OID FROM PG_PROC WHERE PRONAME='test_debug';
輸出結果：
 ?oid
-------
 16389
(1 row)
 
SELECT * FROM DBE_PLDEBUGGER.turn_on(16389);
輸出結果：
 nodename | port
----------+------
 datanode | ? ?0
(1 row)

開始調試

server端執行存儲過程，會在存儲過程內第一條SQL語句前hang住，等待debug端發送的調試消息。僅支持直接執行存儲過程的調試，不支持通過trigger調用執行的存儲過程調試。

call test_debug(1);

再起一個客戶端，作為debug端，通過turn_on返回的數據，調用DBE_PLDEBUGGER.attach關聯到該存儲過程上進行調試。

SELECT * FROM DBE_PLDEBUGGER.attach('datanode',0);
輸出結果：
 funcoid | ?funcname ?| lineno | ? ? ? ? ? ? ?query
---------+------------+--------+----------------------------------
 ? 16389 | test_debug | ? ? ?3 | ? INSERT INTO t1 (a) VALUES (x);
(1 row)

在執行attach的客戶端調試，執行下一條statement。

SELECT * FROM DBE_PLDEBUGGER.next();
輸出結果：
 funcoid | ?funcname ?| lineno | ? ? ? ?query
---------+------------+--------+----------------------
 ? 16389 | test_debug | ? ? ?0 | [EXECUTION FINISHED]
(1 row)

在執行attach的客戶端調試，可以執行以下變量操作

SELECT * FROM DBE_PLDEBUGGER.info_locals(); --打印全部變量
輸出結果：
 varname | vartype | value | package_name | isconst
---------+---------+-------+--------------+---------
 x ? ? ? | int4 ? ?| 1 ? ? | ? ? ? ? ? ? ?| f
(1 row)


SELECT * FROM DBE_PLDEBUGGER.set_var('x', 2); --變量賦值
輸出結果：
 set_var
---------
 t
(1 row)


SELECT * FROM DBE_PLDEBUGGER.print_var('x'); --打印單個變量
輸出結果：
 varname | vartype | value | package_name | isconst
---------+---------+-------+--------------+---------
 x ? ? ? | int4 ? ?| 2 ? ? | ? ? ? ? ? ? ?| f
(1 row)

直接執行完成當前正在調試的存儲過程。

SELECT * FROM DBE_PLDEBUGGER.continue();
輸出結果：
 funcoid | ?funcname ?| lineno | ? ? ? ?query
---------+------------+--------+----------------------
 ? 16389 | test_debug | ? ? ?0 | [EXECUTION FINISHED]
(1 row)

直接退出當前正在調試的存儲過程，不執行尚未執行的語句。

SELECT * FROM DBE_PLDEBUGGER.abort();
輸出結果：
 abort
-------
 t
(1 row)

client端查看代碼信息并識別可以設置斷點行號。

SELECT * FROM DBE_PLDEBUGGER.info_code(16389);
輸出結果：
 lineno | ? ? ? ? ? ? ? ? ? ? ? ? ? query ? ? ? ? ? ? ? ? ? ? ? ? ? | canbreak
--------+-----------------------------------------------------------+----------
 ? ? ? ?| CREATE OR REPLACE PROCEDURE public.test_debug( IN ?x INT) | f
 ? ? ?1 | AS ?DECLARE ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? | f
 ? ? ?2 | BEGIN ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? | f
 ? ? ?3 | ? ? INSERT INTO t1 (a) VALUES (x); ? ? ? ? ? ? ? ? ? ? ? ?| t
 ? ? ?4 | ? ? DELETE FROM t1 WHERE a = x; ? ? ? ? ? ? ? ? ? ? ? ? ? | t
 ? ? ?5 | END; ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ?| f
 ? ? ?6 | / ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? | f
(7 rows)

設置斷點

SELECT * FROM DBE_PLDEBUGGER.info_breakpoints();
輸出結果：
 breakpointno | funcoid | lineno | ? ? ? ? ? ? ?query ? ? ? ? ? ? ?| enable
--------------+---------+--------+---------------------------------+--------
 ? ? ? ? ? ?0 | ? 16389 | ? ? ?4 | ? ? DELETE FROM t1 WHERE a = x; | t

查看斷點信息

SELECT * FROM DBE_PLDEBUGGER.info_breakpoints();
輸出結果：
 breakpointno | funcoid | lineno | ? ? ? ? ? ? ?query ? ? ? ? ? ? ?| enable
--------------+---------+--------+---------------------------------+--------
 ? ? ? ? ? ?0 | ? 16389 | ? ? ?4 | ? ? DELETE FROM t1 WHERE a = x; | t
(1 row)

執行至斷點

SELECT * FROM DBE_PLDEBUGGER.continue();
輸出結果：
 funcoid | ?funcname ?| lineno | ? ? ? ? ? ? ?query
---------+------------+--------+---------------------------------
 ? 16389 | test_debug | ? ? ?4 | ? ? DELETE FROM t1 WHERE a = x;