PostgreSQL Oracle 兼容性之 - DBMS_SQL(存储过程动态SQL中使用绑定变量)

本文涉及的产品
RDS MySQL Serverless 基础系列,0.5-2RCU 50GB
云原生数据库 PolarDB PostgreSQL 版,企业版 4核16GB
推荐场景:
HTAP混合负载
云原生数据库 PolarDB 分布式版,标准版 2核8GB
简介:

标签

PostgreSQL , Oracle , pl/sql , 存储过程 , plpgsql , 绑定变量 , DB端绑定变量 , prepare , DEALLOCATE , EXECUTE


背景

Oracle中有一个dbms_sql的包,可以让你在存储过程的动态SQL中使用prepared statement。如果动态SQL的调用频次较高,硬解析可能导致一些性能问题(Oracle硬解析的CPU消耗还是蛮大的)。

https://docs.oracle.com/cd/B19306_01/appdev.102/b14258/d_sql.htm#i1028953

DMBS_SQL里面包含的一些类型

Bulk SQL Types  
  
BFILE_TABLE  
  
BINARY_DOUBLE_TABLE  
  
BLOB_TABLE  
  
CLOB_TABLE  
  
DATE_TABLE  
  
INTERVAL_DAY_TO_SECOND_TABLE  
  
INTERVAL_YEAR_TO_MONTH_TABLE  
  
NUMBER_TABLE  
  
TIME_TABLE  
  
TIME_WITH_TIME_ZONE_TABLE  
  
TIMESTAMP_TABLE  
  
TIMESTAMP_WITH_LTZ_TABLE  
  
UROWID_TABLE  
  
VARCHAR2_TABLE  

其中本文用到的 NUMBER_TABLE

TYPE number_table IS TABLE OF NUMBER INDEX BY BINARY_INTEGER;  

在PostgreSQL的INLINE CODE或者FUNCTION中如何使用动态SQL,如何使用绑定变量,如何使用BULK类型呢?

PostgreSQL INLINE CODE或函数的动态SQL、绑定变量使用

首先借这个链接,解释一下Oracle dbms_sql的使用

https://stackoverflow.com/questions/21335493/can-somebody-explain-dbms-sql-number-table

Better to understand DBMS_SQL itself to some extent, before understanding NUMBER_TABLE.   
( I do this for My Learning!)  
  
NUMBER_TABLE  
is Actually,  
  
TYPE number_table IS TABLE OF NUMBER INDEX BY BINARY_INTEGER;  
So, only numbers are allowed!  
  
FlowChart on How DBMS_SQL Works! :  
  
Your interested area comes in bind variable box  
                        -----------  
--                    | open_cursor |  
--                      -----------  
--                           |  
--                           |  
--                           v  
--                         -----  
--          ------------>| parse |  
--          |              -----  
--          |                |  
--          |                | ---------  
--          |                v          |  
--          |           --------------  |  
--          |-------->| bind_variable | |  
--          |     ^     -------------   |  
--          |     |           |         |  
--          |      -----------|         |  
--          |                 |<--------  
--          |                 v  
--          |               query?---------- yes ---------  
--          |                 |                           |  
--          |                no                           |  
--          |                 |                           |  
--          |                 v                           v  
--          |              -------                  -------------  
--          |----------->| execute |            ->| define_column |  
--          |              -------             |    -------------  
--          |                 |------------    |          |  
--          |                 |            |    ----------|  
--          |                 v            |              v  
--          |           --------------     |           -------  
--          |       ->| variable_value |   |  ------>| execute |  
--          |      |    --------------     | |         -------  
--          |      |          |            | |            |  
--          |       ----------|            | |            |  
--          |                 |            | |            v  
--          |                 |            | |        ----------  
--          |                 |<-----------  |----->| fetch_rows |  
--          |                 |              |        ----------  
--          |                 |              |            |  
--          |                 |              |            v  
--          |                 |              |  -----------------  
--          |                 |              | | column_value    |  
--          |                 |              | | variable_value  |  
--          |                 |              |  -----------------  
--          |                 |              |            |  
--          |                 |<--------------------------  
--          |                 |  
--           -----------------|  
--                            |  
--                            v  
--                       ------------  
--                     | close_cursor |  
--                       ------------  

例子

Example: In a DELETE statement, for example, you could bind in an array in the WHERE clause and have the statement be run for each element in the array:  
  
DECLARE  
  stmt VARCHAR2(200);  
  dept_no_array DBMS_SQL.NUMBER_TABLE;  
  c NUMBER;  
  dummy NUMBER;  
begin  
  dept_no_array(1) := 10; dept_no_array(2) := 20; /* Put some values into the array */  
  dept_no_array(3) := 30; dept_no_array(4) := 40;  
  dept_no_array(5) := 30; dept_no_array(6) := 40;  
  stmt := 'delete from emp where deptno = :dept_array'; /* A Dynamic SQL String with a bind variable */  
  c := DBMS_SQL.OPEN_CURSOR; /* Open a Cursor! */  
  DBMS_SQL.PARSE(c, stmt, DBMS_SQL.NATIVE); /* Parse the Dynamic SQL , making it happen on the native database to which is connected! */  
  
  DBMS_SQL.BIND_ARRAY(c, ':dept_array', dept_no_array, 1, 4);  
  /* Bind only elements 1 through 4 to the cursor Happens 4 times */  
  
  dummy := DBMS_SQL.EXECUTE(c);  
  /* Execute the Query, and return number of rows deleted! */  
  
  DBMS_SQL.CLOSE_CURSOR(c);   
  
  EXCEPTION WHEN OTHERS THEN  
    IF DBMS_SQL.IS_OPEN(c) THEN  
      DBMS_SQL.CLOSE_CURSOR(c);  
    END IF;  
    RAISE;  
END;  
/   
  
P.S. Pure rip-off, with some more commments ,from Oracle  

PostgreSQL 服务端绑定变量的用法与之类似

PREPARE,准备DB端绑定变量SQL

EXECUTE,绑定并执行

DEALLOCATE,删除绑定变量

详见:

https://www.postgresql.org/docs/devel/static/sql-prepare.html

因此以上Oracle的代码可以改成如下:

do language plpgsql $$  
DECLARE  
  stmt VARCHAR(200);  
  dept_no_array numeric[];  
  c numeric;  
begin  
  dept_no_array[1] := 10; dept_no_array[2] := 20; /* Put some values into the array */  
  dept_no_array[3] := 30; dept_no_array[4] := 40;  
  dept_no_array[5] := 30; dept_no_array[6] := 40;  
  execute format('prepare stmt(numeric) as delete from emp where deptno = $1');    /* A Dynamic SQL String with a bind variable */  
  
  foreach c in array dept_no_array[1:4]  
  loop  
    execute format('execute stmt(%s)', c);   -- 执行绑定SQL  
  end loop;  
  
  DEALLOCATE stmt;  
  
  EXCEPTION WHEN OTHERS THEN  
    DEALLOCATE stmt;  
    RAISE;  
END;  
$$;  

如果不需要用到绑定变量,那就更简单了。

do language plpgsql $$  
DECLARE  
  dept_no_array numeric[];  
  c numeric;  
begin  
  dept_no_array[1] := 10; dept_no_array[2] := 20; /* Put some values into the array */  
  dept_no_array[3] := 30; dept_no_array[4] := 40;  
  dept_no_array[5] := 30; dept_no_array[6] := 40;  
  
  foreach c in array dept_no_array[1:4]  
  loop  
    delete from emp where deptno = c;  
  end loop;  
  
  DEALLOCATE stmt;  
  
  EXCEPTION WHEN OTHERS THEN  
    DEALLOCATE stmt;  
    RAISE;  
END;  
$$;  

例子

建表

do language plpgsql $$
declare
begin
  execute 'drop table if exists test';
  execute 'create table test(id int primary key, info text, crt_time timestamp)';
  
  for i in 0..1023 loop
    execute format('drop table if exists test%s', i);
    execute format('create table test%s (like test including all)', i);
  end loop;
end;
$$;

使用动态SQL,写入目标子表

create or replace function dyn_pre(int) returns void as $$
declare
  suffix int := mod($1,1024);
begin
  execute format('insert into test%s values(%s, md5(random()::text), now()) on conflict(id) do update set info=excluded.info,crt_time=excluded.crt_time', suffix, $1);
end;
$$ language plpgsql strict;

使用绑定变量,写入目标子表

create or replace function dyn_pre(int) returns void as $$
declare
  suffix int := mod($1,1024);
begin
  execute format('execute p%s(%s)', suffix, $1);
  exception when others then
    execute format('prepare p%s(int) as insert into test%s values($1, md5(random()::text), now()) on conflict(id) do update set info=excluded.info,crt_time=excluded.crt_time', suffix, suffix);
    execute format('execute p%s(%s)', suffix, $1);
end;
$$ language plpgsql strict;

性能对比

vi test.sql

\set id random(1,1000000000)
select dyn_pre(:id);

pgbench -M prepared -n -r -P 1 -f ./test.sql -c 56 -j 56 -T 120

1、使用动态SQL,TPS约5.2万。

2、使用函数内绑定变量,TPS约13.4万。

参考

https://www.postgresql.org/docs/devel/static/sql-prepare.html

https://stackoverflow.com/questions/21335493/can-somebody-explain-dbms-sql-number-table

相关实践学习
使用PolarDB和ECS搭建门户网站
本场景主要介绍基于PolarDB和ECS实现搭建门户网站。
阿里云数据库产品家族及特性
阿里云智能数据库产品团队一直致力于不断健全产品体系,提升产品性能,打磨产品功能,从而帮助客户实现更加极致的弹性能力、具备更强的扩展能力、并利用云设施进一步降低企业成本。以云原生+分布式为核心技术抓手,打造以自研的在线事务型(OLTP)数据库Polar DB和在线分析型(OLAP)数据库Analytic DB为代表的新一代企业级云原生数据库产品体系, 结合NoSQL数据库、数据库生态工具、云原生智能化数据库管控平台,为阿里巴巴经济体以及各个行业的企业客户和开发者提供从公共云到混合云再到私有云的完整解决方案,提供基于云基础设施进行数据从处理、到存储、再到计算与分析的一体化解决方案。本节课带你了解阿里云数据库产品家族及特性。
相关文章
|
4天前
|
SQL Oracle 关系型数据库
|
3天前
|
SQL Oracle 关系型数据库
MySQL、SQL Server和Oracle数据库安装部署教程
数据库的安装部署教程因不同的数据库管理系统(DBMS)而异,以下将以MySQL、SQL Server和Oracle为例,分别概述其安装部署的基本步骤。请注意,由于软件版本和操作系统的不同,具体步骤可能会有所变化。
20 3
|
1月前
|
SQL Oracle 关系型数据库
一些非常有用的Oracle SQL
一些非常有用的Oracle SQL
29 4
|
1月前
|
SQL Oracle 关系型数据库
mysql和oracle 命令行执行sql文件 数据库执行sql文件 执行sql语句
mysql和oracle 命令行执行sql文件 数据库执行sql文件 执行sql语句
44 0
|
1月前
|
存储 关系型数据库 MySQL
MySql创建带事务操作的存储过程
MySql创建带事务操作的存储过程
|
1月前
|
存储 SQL 关系型数据库
MySQL 进阶使用【函数、索引、视图、存储过程、存储函数、触发器】(2)
MySQL 进阶使用【函数、索引、视图、存储过程、存储函数、触发器】
|
1月前
|
存储 SQL 关系型数据库
MySQL 进阶使用【函数、索引、视图、存储过程、存储函数、触发器】(1)
MySQL 进阶使用【函数、索引、视图、存储过程、存储函数、触发器】
|
1月前
|
存储 SQL 关系型数据库
MySQL数据库进阶第四篇(视图/存储过程/触发器)
MySQL数据库进阶第四篇(视图/存储过程/触发器)
|
1月前
|
存储 SQL 关系型数据库
MySQL存储过程和存储函数的使用
MySQL的存储过程和存储函数在功能和用法上有明显的区别。存储过程是一组为了完成特定功能的SQL语句集,经编译后存储在数据库中,通过指定名称和参数(如果有)来调用执行,可以返回多个值或结果集,但不直接返回值。而存储函数则是一个有返回值的特殊存储过程,它返回一个值或表对象,可以直接嵌入SQL语句中使用,如SELECT语句中。两者都是为了提高SQL代码的重用性和性能,但使用场景和方式有所不同。
166 4
|
1月前
|
存储 关系型数据库 MySQL
MySQL周内训参照5、存储过程创建
MySQL周内训参照5、存储过程创建
31 1

相关产品

  • 云原生数据库 PolarDB
  • 推荐镜像

    更多