存儲過程類似于面向過程語言當中的函數,可以實現面向過程語言當中的聲明變量、邏輯判斷、條件循環等操作,是一組完成特定功能的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 --包頭,在包頭內聲明的變量存儲過程等都為公有的,可以被外部訪問ISpublic_var1 int:=1; --在包頭聲明的公有變量public_var1,可以被外部訪問procedure public_proc1(col1 int,col2 int); --只在包頭內聲明的存儲過程,因此為公有的,可以被外部訪問。END PCK1;/CREATE OR REPLACE PACKAGE BODY PCK1 --指定了PACKAGE BODY關鍵字ISprivate_var1 int:=1; --在包體內聲明的私有變量private_var1,不能被外部訪問procedure private_proc1(col1 int,col2 int) --只在包體內定義的存儲過程為私有存儲過程isbeginraise notice 'col1 + col2 = %',col1+col2;insert into tab1 values(col1+col2);end;procedure public_proc1(col1 int,col2 int)--只在包體內定義的存儲過程為私有存儲過程isbeginprivate_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) ?ASDECLARE? ?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) ?ASDECLAREc 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 | ?211 | 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 PROCEDURESELECT 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;
歡迎訪問openGauss官方網站

openGauss開源社區官方網站:
https://opengauss.org
openGauss組織倉庫:
https://gitee.com/opengauss
openGauss鏡像倉庫:
https://github.com/opengauss-mirror

掃碼關注我們
微信公眾號|openGauss
微信社群小助手|openGauss-bot




