LightDB支持dbms_sql包部分功能

背景

在Oracle数据库中，dbms_sql是一个在PL/SQL中使用动态sql解析并执行dml/ddl语句功能的API。它是动态sql的一种补充，详见Oracle官网描述。

为了更好的兼容oracle数据库，从24.1版本开始，LightDB也开始支持在plorasql（LightDB自己的存储过程语言，语法上尽量兼容Oracle的PL/SQL）中支持使用dbms_sql包动态解析并处理dml/ddl语句。由于时间问题，本期仅实现了dbms_sql的部分功能。

DBMS_SQL使用说明

先来一个dbms_sql包的使用案例：

DECLARE
  vc_prosql dbms_sql.varchar2a;
  cursor_sql integer;
  iIndex integer;
BEGIN
  FOR i IN 1..2 LOOP
    --1-打开游标
    cursor_sql := dbms_sql.open_cursor;
    vc_prosql.DELETE;
    iIndex := 1;
    --2-构造待执行的sql语句
    vc_prosql(iIndex) := 'create or replace function f' || i || '(v int) RETURN int AS ';
    iIndex := iIndex + 1;
    vc_prosql(iIndex) := 'begin';
    iIndex := iIndex + 1;
    vc_prosql(iIndex) := '  return v + ' || i || ';';
    iIndex := iIndex + 1;
    vc_prosql(iIndex) := 'end;';
    for j in 1..vc_prosql.last loop
      raise notice '%',vc_prosql(j);
    end loop;
    raise notice '';
    --3-解析sql
    dbms_sql.parse(cursor_sql, vc_prosql, vc_prosql.first, vc_prosql.last, false, dbms_sql.native);
    --4-执行sql
    iIndex := dbms_sql.execute(cursor_sql);
    --5-关闭游标
    dbms_sql.close_cursor(cursor_sql);
  END LOOP;
END;
/

该案例就是通过拼接一个待执行的sql语句，经过解析执行后，数据库中会生成f1和f2这2个函数：

lightdb@oracle=# DECLARE
lightdb@oracle$#   vc_prosql dbms_sql.varchar2a;
lightdb@oracle$#   cursor_sql integer;
lightdb@oracle$#   iIndex integer;
lightdb@oracle$# BEGIN
lightdb@oracle$#   FOR i IN 1..2 LOOP
lightdb@oracle$#     cursor_sql := dbms_sql.open_cursor;
lightdb@oracle$#     vc_prosql.DELETE;
lightdb@oracle$#     iIndex := 1;
lightdb@oracle$#     vc_prosql(iIndex) := 'create or replace function f' || i || '(v int) RETURN int AS ';
lightdb@oracle$#     iIndex := iIndex + 1;
lightdb@oracle$#     vc_prosql(iIndex) := 'begin';
lightdb@oracle$#     iIndex := iIndex + 1;
lightdb@oracle$#     vc_prosql(iIndex) := '  return v + ' || i || ';';
lightdb@oracle$#     iIndex := iIndex + 1;
lightdb@oracle$#     vc_prosql(iIndex) := 'end;';
lightdb@oracle$#     for j in 1..vc_prosql.last loop
lightdb@oracle$#       raise notice '%',vc_prosql(j);
lightdb@oracle$#     end loop;
lightdb@oracle$#     raise notice '';
lightdb@oracle$#     dbms_sql.parse(cursor_sql, vc_prosql, vc_prosql.first, vc_prosql.last, false, dbms_sql.native);
lightdb@oracle$#     iIndex := dbms_sql.execute(cursor_sql);
lightdb@oracle$#     dbms_sql.close_cursor(cursor_sql);
lightdb@oracle$#   END LOOP;
lightdb@oracle$# END;
lightdb@oracle$# /
NOTICE:  create or replace function f1(v int) RETURN int AS 
NOTICE:  begin
NOTICE:    return v + 1;
NOTICE:  end;
NOTICE:  
NOTICE:  _query: 0:create or replace function f1(v int) RETURN int AS begin  return v + 1;end;
NOTICE:  _pg_typeof(_query): varchar2
NOTICE:  create or replace function f2(v int) RETURN int AS 
NOTICE:  begin
NOTICE:    return v + 2;
NOTICE:  end;
NOTICE:  
NOTICE:  _query: 0:create or replace function f2(v int) RETURN int AS begin  return v + 2;end;
NOTICE:  _pg_typeof(_query): varchar2
DO
lightdb@oracle=# select * from pg_proc where proname in('f1','f2');
  oid   | proname | pronamespace | proowner | prolang | procost | prorows | provariadic | prosupport | prokind | prosecdef | proleakproof | proisstrict | proretset | provolatile | proparallel | pronargs | pronargdefaults | prorettype | proargtypes | proallargtypes | pro
argmodes | proargnames | proargdefaults | protrftypes |          prosrc          | probin | proconfig | proacl 
--------+---------+--------------+----------+---------+---------+---------+-------------+------------+---------+-----------+--------------+-------------+-----------+-------------+-------------+----------+-----------------+------------+-------------+----------------+----
---------+-------------+----------------+-------------+--------------------------+--------+-----------+--------
 132125 | f1      |         2200 |       10 |   14193 |     100 |       0 |           0 | -          | f       | f         | f            | f           | f         | v           | u           |        1 |               0 |         23 | 23          |                |    
         | {v}         |                |             |  begin return v + 1;end; |        |           | 
 132126 | f2      |         2200 |       10 |   14193 |     100 |       0 |           0 | -          | f       | f         | f            | f           | f         | v           | u           |        1 |               0 |         23 | 23          |                |    
         | {v}         |                |             |  begin return v + 2;end; |        |           | 
(2 rows)

现在对dbms_sql包的用法有了一个大致了解之后，我们来详细拆解一下内部各接口的用法

dbms_sql包内变量

先上一张oracle中dbms_sql包内常量的说明，但是在LightDB中，它们是变量，但也只是为了兼容oracle语法而已，这些变量并没有实际含义

可以看到在Oracle中，这些V6、NATIVE或V7是用来选择使用某个Oracle版本的功能的，也就是一个兼容参数。在LightDB中，也支持使用这种变量作为某个接口参数，但只是为了语法兼容，并无实际含义。

dbms_sql.varchar2a

dbms_sql.varchar2a是一个基于varchar2的嵌套表，可以理解为是多个varchar2对象组成的一个数组，可以使用下标访问数组元素，数组下标从1开始。上例中可以使用vc_prosql(1)或vc_prosql .first表示该数组的第一个元素，用vc_prosql.last表示该数组的最后一个元素。vc_prosql.delete表示清空整个数组。

dbms_sql.open_cursor

接口声明：CREATE FUNCTION dbms_sql.open_cursor() RETURNS int

dbms_sql.open_cursor表示打开一个游标，可理解为从系统申请一份资源，该接口调用时无需任何参数，返回系统分配的游标句柄。

dbms_sql.parse

dbms_sql.parse表示解析拼接而成的sql。该接口有2种不同的重载方式：

1、PROCEDURE parse(c int, stmt oracle.varchar2) ;

2、PROCEDURE parse(c int, stmt dbms_sql.varchar2a, lb int, ub int, lfflg bool, language_flag int) ;

第一个参数c表示已经打开的游标句柄，也就是dbms_sql.open_cursor接口的返回值。

第二个参数stmt表示自定义的动态sql，第一种是直接用varchar2类型来表示的，第二种是通过一个varchar2数组的类型来表示的。而lb和ub分别数组的下标下限和下标上限，通过nestedtab.(lb)和nestedtab.(ub)来确定使用数组的哪些元素来组成动态sql。

最后的ifflg和language_flag只为语法兼容，目前并无实际含义。

dbms_sql.execute

接口声明：CREATE FUNCTION dbms_sql.execute(c int) RETURNS bigint

dbms_sql.execute表示执行拼接出来的动态sql，接收的参数c表示已经打开的游标句柄。返回值为：1-查询或dml执行了多少条元组；2-执行ddl语句时返回0。

dbms_sql.close_cursor

接口声明：CREATE PROCEDURE dbms_sql.close_cursor(c int)

dbms_sql.close_cursor表示关闭游标句柄，释放系统资源，其入参c表示已经打开的游标句柄，无返回值。

使用注意点说明

由于时间原因，本版本(LightDB 24.1版本)并未完整兼容了Oracle的dbms_sql包，而只是兼容了该包的部分最基础常用的功能。同时在plorasql中使用嵌套表函数时，也存在限制：目前对嵌套表的函数，仅支持first和last。其它类似于count、exists、prior和next函数暂未支持。

特此说明。