【MogDB】MogDB5.2.0重磅发布第十篇-支持PLSQL嵌套子程序
一、前言
在ORACLE的PLSQL中,支持在procedure、function及匿名块中,嵌套定义procedure和function,编写这样的代码,算是一种低耦合高内聚的风格。在openGauss 6.0及之前的版本,并不支持嵌套子程序(预计7.0版本会支持),但通过实测,MogDB 5.2.0支持的场景比openGauss 7.0(截止到2024年12月19号的代码)要多。
二、什么是PLSQL嵌套子程序
plsql-subprograms
在ORACLE的官方文档里,将子程序分为三种,我简单描述一下
- 独立子程序
通过create procedure/function创建的程序单元 - 包子程序
存在于package对象内的procedure/function - 嵌套子程序
在plsql块中声明的procedure/function
ORACLE风格的create function/procedure/package在openGauss 2.1版本都是支持的,也就是说,独立子程序以及包子程序早就已经支持,仅缺嵌套子程序。
对于plsql语言的procedure/function,本身就会具有一个plsql块,因此嵌套子程序可能出现在以下几种语法中
- create procedure/function 的声明内,声明一个或多个procedure/function
- package 内的procedure/function的声明内,声明一个或多个procedure/function
- 匿名块的声明内,声明一个或多个procedure/function
- 嵌套子程序内,声明一个或多个procedure/function
从语法上看,大概如下
declare
x int;
--子程序开始
procedure a is
begin
null;
end;
--子程序结束
begin
a;
end;
一般来说,declare…begin…end是一个plsql块的固定结构,只是在oracle语法的function/procedure中,可以省去declare这个关键字。由此我们可以发现,嵌套子程序是位于plsql块中,begin前面的一个声明,而plsql嵌套子程序本身也具有一个plsql块,因此仅从语法上看,嵌套子程序是可以无限套娃的,比如
declare
procedure a is
procedure b is
procedure c is
begin
null;
end;
begin
null;
end;
begin
null;
end;
begin
a;
end;
为什么特意提这一点?如果对语法没有敏感度,稍不注意,就很容易把这个功能做成仅支持一层嵌套。而openGauss到6.0也仍然不支持嵌套子程序,说明这个功能是具有相当的难度的,元数据放哪、怎么放、怎么记录嵌套关系、怎么解决重载问题、怎么解决生效范围的问题、怎么解决并发执行时的编译冲突问题、怎么管理缓存等等。如果没想明白这些问题,仅仅只是做一个形似的功能,很容易在实际的生产使用中出现各种奇奇怪怪的问题。
三、一些典型的使用场景
1、固定组合的长表达式
create procedure test_p is
function n2p(i number) return varchar2
is
begin
return to_char(i*100,'FM99999999999999999990.00')||'%';
end;
begin
insert into t_test1
select a,b,n2p(c/e),n2p((d-e)/e),n2p(e) from t_test;
end;
以上例子常见于报表生成,经常会有需要将一个数值转换成百分比的形式,比如占比、增长率之类的,而数值转百分比的这个表达式有一点点长,如果要使用很多次,就会让代码显得很冗余而且很长。而且这个转换的格式还不一定在所有场景通用,所以可以只放在本procedure中,让这个procedure也不用依赖于其他逻辑对象。
2、框架化流程
CREATE TABLE doc_list (BIZ_TYPE VARCHAR2(3),DOC_NO VARCHAR2(200),STATUS VARCHAR2(1));
CREATE OR REPLACE PACKAGE TEST_SUBPRO AS
PROCEDURE biz_a(I_DOC_NO VARCHAR2); --业务A处理流程
PROCEDURE biz_b(I_DOC_NO VARCHAR2); --业务B处理流程
PROCEDURE main; --入口
END TEST_SUBPRO;
/
CREATE OR REPLACE PACKAGE BODY TEST_SUBPRO AS
PROCEDURE biz_a(I_DOC_NO VARCHAR2) IS
--单据处理的开始过程
PROCEDURE BEGIN_PRO(I_DOC_NO VARCHAR2) IS
BEGIN
NULL;
END;
--单据处理的结束过程
PROCEDURE END_PRO(I_DOC_NO VARCHAR2) IS
BEGIN
NULL;
END;
BEGIN
BEGIN_PRO(I_DOC_NO);
END_PRO(I_DOC_NO);
END;
PROCEDURE biz_b(I_DOC_NO VARCHAR2) IS
--单据处理的开始过程
PROCEDURE BEGIN_PRO(I_DOC_NO VARCHAR2) IS
BEGIN
NULL;
END;
--单据处理的结束过程
PROCEDURE END_PRO(I_DOC_NO VARCHAR2) IS
BEGIN
NULL;
END;
BEGIN
BEGIN_PRO(I_DOC_NO);
END_PRO(I_DOC_NO);
END;
PROCEDURE main IS
BEGIN
FOR rec IN (SELECT * FROM doc_list WHERE status = 'N') LOOP
-- 根据所有未处理的单据,按不同的类型分别处理
IF rec.biz_type = 'A' THEN
biz_a(REC.doc_no);
ELSIF rec.biz_type = 'B' THEN
biz_a(REC.doc_no);
END IF;
END LOOP;
END;
END TEST_SUBPRO;
以上模拟了一种通用业务流程处理框架,对于每一种业务,都有开始过程和结束过程(可能还有"预处理-处理-完成"形式的流程),但是每种业务自己分步的流程可能有些区别,不能共用。此处嵌套子程序的使用,能让每一种业务只需要有一个procedure暴露出来,程序结构清晰。
当然,嵌套子程序远不止这两种使用场景,此处只是列出最常见的两种。
只要嵌套子程序的功能是完整的,那么开发者就可以充分发挥自己的想象力来让自己的plsql代码更整洁易维护。
四、国产数据库比较
目前虽然有很多国产数据库也支持了PLSQL嵌套子程序,但是有些支持得并不完整,下面来做下对比
数据库 | 是否支持在匿名块中使用 | 是否支持在procedure中使用 | 是否支持在function中使用 | 是否支持在PACKAGE内的procedure中使用 | 是否支持在PACKAGE内的function中使用 | 是否支持在嵌套子程序内使用 | 是否支持在PACKAGE内的不同procedure里重名 | 是否支持在同一个procedure内重载 | 是否正确处理内外层的重载关系 | 是否正确处理和系统函数的重载关系 | 是否支持前向声明 | 支持百分比 | 备注 |
---|---|---|---|---|---|---|---|---|---|---|---|---|---|
DM8 | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | 100% | |
崖山 23.2.4 | Y | Y | Y | Y | Y | Y | Y | N | Y | N | N | 80% | |
OCEANBASE 4.1 | ? | ? | ? | ? | ? | ? | ? | ? | ? | ? | ? | ? | 文档有提支持嵌套子程序 |
KINGBASE 9 | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | 100% | |
POLARDB-O 2.0 | ? | ? | ? | ? | ? | ? | ? | ? | ? | ? | ? | ? | 文档有提支持嵌套子程序 |
TDSQL-PG(Oracle兼容版) | ? | ? | ? | ? | ? | ? | ? | ? | ? | ? | ? | ? | 文档无相关描述 |
UXDB | ? | ? | ? | ? | ? | ? | ? | ? | ? | ? | ? | ? | 文档无相关描述 |
神通(openGauss版) | ? | ? | ? | ? | ? | ? | ? | ? | ? | ? | ? | ? | 无公开文档 |
海盒 | ? | ? | ? | ? | ? | ? | ? | ? | ? | ? | ? | ? | 无公开文档 |
瀚高 6.0.4 | ? | ? | ? | ? | ? | ? | ? | ? | ? | ? | ? | ? | 文档无相关描述 |
GAUSSDB 503.1.0.SPC1700 | N | N | N | N | N | N | N | N | N | N | N | 0% | |
OPENGAUSS 6.0 | N | N | N | N | N | N | N | N | N | N | N | 0% | |
OPENGAUSS 7.0(31ae9e8a 20241219) | Y | Y | Y | N | N | N | N | Y | N | Y | Y | 50% | 基于2024年12月19号源码手动编译,非发布版本 |
GBASE 8c | N | N | N | N | N | N | N | N | N | N | N | 0% | |
VASTBASE G100 V2.2 BUILD 16 | Y | Y | Y | Y | Y | N | Y | Y | N | Y | N | 70% | |
MogDB 5.2.0 | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | 100% |
测试用例
--是否支持在匿名块中使用
declare
procedure a is
begin
null;
end;
begin
a();
end;
/
--是否支持在procedure中使用
create procedure test_p is
procedure a is
begin
null;
end;
begin
a();
end;
/
call test_p();
--是否支持在function中使用
create function test_f return int is
procedure a is
begin
null;
end;
begin
a();
return 1;
end;
/
select test_f() from dual;
--是否支持在PACKAGE内的procedure中使用
drop package TEST_PKG;
create package test_pkg is
procedure test_p;
end;
/
create package BODY test_pkg is
procedure test_p is
procedure a is
begin
null;
end;
begin
a();
end;
end;
/
call test_pkg.test_p();
--是否支持在PACKAGE内的function中使用
create package test_pkg2 is
function test_f return int;
end;
/
create package BODY test_pkg2 is
function test_f return int is
procedure a is
begin
null;
end;
begin
a();
return 1;
end;
end;
/
select test_pkg2.test_f() from DUAL;
--是否支持在嵌套子程序内使用
declare
procedure a is
procedure b is
procedure c is
begin
null;
end;
begin
null;
end;
begin
null;
end;
begin
a;
end;
/
--是否支持在PACKAGE内的不同procedure里重名
CREATE OR REPLACE PACKAGE TEST_SUBPRO AS
PROCEDURE biz_a(I_DOC_NO VARCHAR2);
PROCEDURE biz_b(I_DOC_NO VARCHAR2);
PROCEDURE main(i_biz_type varchar2,I_DOC_NO VARCHAR2);
END TEST_SUBPRO;
/
CREATE OR REPLACE PACKAGE BODY TEST_SUBPRO AS
PROCEDURE biz_a(I_DOC_NO VARCHAR2) IS
PROCEDURE BEGIN_PRO(I_DOC_NO VARCHAR2) IS
BEGIN
NULL;
END;
PROCEDURE END_PRO(I_DOC_NO VARCHAR2) IS
BEGIN
NULL;
END;
BEGIN
BEGIN_PRO(I_DOC_NO);
END_PRO(I_DOC_NO);
END;
PROCEDURE biz_b(I_DOC_NO VARCHAR2) IS
--单据处理的开始过程
PROCEDURE BEGIN_PRO(I_DOC_NO VARCHAR2) IS
BEGIN
NULL;
END;
--单据处理的结束过程
PROCEDURE END_PRO(I_DOC_NO VARCHAR2) IS
BEGIN
NULL;
END;
BEGIN
BEGIN_PRO(I_DOC_NO);
END_PRO(I_DOC_NO);
END;
PROCEDURE main(i_biz_type varchar2,I_DOC_NO VARCHAR2) IS
BEGIN
-- 根据所有未处理的单据,按不同的类型分别处理
IF i_biz_type = 'A' THEN
biz_a(I_DOC_NO);
ELSIF i_biz_type = 'B' THEN
biz_a(I_DOC_NO);
END IF;
END;
END TEST_SUBPRO;
/
--是否支持在同一个procedure内重载
create procedure test_p2 is
procedure p1 is
begin
dbms_output.put_line(1);
end;
procedure p1(a int) is
begin
dbms_output.put_line(2);
end;
begin
p1();
p1(1);
dbms_output.put_line(3);
end;
/
call test_p2();
--是否正确处理内外层的重载关系
declare
procedure p1 is
procedure p1 is
begin
dbms_output.put_line(1);
end;
begin
p1;
dbms_output.put_line(2);
end;
begin
p1;
dbms_output.put_line(3);
end;
/
--是否正确处理和系统函数的重载关系
declare
x varchar(20);
function substr(i date,f varchar) return varchar is
begin
return to_char(i,f);
end;
function substr(i varchar,c int) return varchar is
begin
return i||c;
end;
begin
x:=substr(sysdate,'yyyy');
x:=substr(x,1);
dbms_output.put_line(x);
end;
/
--是否支持前向声明
declare
procedure a ;
procedure b is
begin
a();
end;
procedure a is
begin
null;
end;
begin
null;
end;
/
五、总结
MogDB 5.2.0版本正式推出的plsql嵌套子程序功能,是从MogDB3.0版本时期开始立项开发,经历了非常长的时间,因为这个功能对openGauss的现有框架来说,必须要做一些突破,改掉openGauss中一些不合理的代码,并且结合MogDB 5.2.0版本对plsql的各种底层原理的优化,才让PLSQL嵌套子程序功能变得完整且可扩展,不惧未来更多新功能的冲击。
- 本文作者: DarkAthena
- 本文链接: https://www.darkathena.top/archives/mogdb-5.2.0-support-plsql-nested-subprogram
- 版权声明: 本博客所有文章除特别声明外,均采用CC BY-NC-SA 3.0 许可协议。转载请注明出处