背景
在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函数暂未支持。
特此说明。