1. 动态SQL
动态SQL在程序启动时会根据输入参数替换相应变量。使用动态SQL可以创建更强大和灵活的应用程序,但在编译时SQL语句的全文不确定,因此运行时编译会牺牲一些性能。动态SQL可以是代码或SQL语句的一部分,动态部分要么由开发人员输入,要么由程序本身创建。
1.1 动态SQL使用场景
在PL/SQL函数或过程中有时需要生成动态命令,因为命令涉及不同表或数据类型,仅在运行时才能确定具体对象或值。这时比较适合使用动态SQL。
另外,在特定情况下,如果静态SQL语句无法执行,或者您真的不知道函数或过程要执行的确切SQL语句,那么您必须使用动态SQL。
1.2 动态SQL VS 静态SQL
静态SQL | 动态SQL |
---|---|
SQL语句在编译时编译 | SQL语句在运行时编译 |
SQL语句在编译时进行解析,有效性检查表、视图和过程。编译时优化并生成应用程序执行计划 | SQL语句在运行时进行编译解析SQL语句,有效性检查表、视图和过程,优化并生成应用程序执行计划 |
性能好,速度快 | 与静态SQL相比较性能不好 |
不够灵活 | 非常灵活 |
2. 生成动态SQL
共有三种方式实现动态SQL。使用format函数,使用quote_ident 和 quote_literal函数,使用连接操作符||。
注意:构建查询时插入的动态值需要仔细处理,因为可能需要包含引号。
2.1 使用format函数
首先我们介绍下format函数的形式参数:
%s s格式化参数值作为简单字符串. %I I 处理参数值作为SQL 标识符,有必要增加双引号. %L L 引用参数作为SQL字面值.
这里先要区分两个概念:SQL标识符和SQL字面值。
SQL标识符表示数据库名称、表名、索引名、schema名、约束名、游标名、触发器、列、视图名称。在动态SQL中,I%会按照SQL标识符进行解析。
SQL字面值表示显示值,数值、字符、字符串、布尔值,不代表SQL标识符。有不同类型的字面值:
类型 | 举例 |
---|---|
字符串 String | ‘Hello! everyone’ |
整数 Integer | 45, 78, +89 , -465,6E5 |
数值 Decimal | 45.56 |
日期 DateTime | ‘5/20/2020’ , TIMESTAMP ‘2020-05-20 12:01:01’; |
字符 Character | A’ ‘%’ ‘9’ ’ ’ ‘z’ ‘(’ |
布尔 Boolean | true, false, null |
下面举例说明:
test=# SELECT format('Hello! Welcome to my %s!', 'Blog') as msg;
msg
----------------------------
Hello! Welcome to my Blog!
(1 行记录)
test=# SELECT format('%s! Welcome to my %s! - %s','Hi','Blog','Ourtechroom') as msg;
msg
---------------------------------------
Hi! Welcome to my Blog! - Ourtechroom
(1 行记录)
test=# SELECT format('INSERT INTO %I VALUES(%L)', 'tbl_test', 'test');
format
-------------------------------------
INSERT INTO tbl_test VALUES('test')
(1 行记录)
2.2 使用quote_indent 函数
select quote_ident('Hello World');
// "Hello World" 字面量增加引号
select quote_ident('mytable');
// mytable 表名称自动去掉引号
除此之外还有几个类似函数。 QUOTE_LITERAL(string text), QUOTE_LITERAL(value anyelement), QUOTE_NULLABLE(value anyelement);
QUOTE_LITERAL函数返回值自动增加引号。QUOTE_NULLABLE对于非空参数增加引号,否则返回null。
2.3 使用连接操作符||
query := 'SELECT score FROM stu_temp WHERE name='''
|| name
|| '''';
这种方式对于需要转义单引号比较麻烦,且容易造成SQL注入。
3. 总结
本文介绍三种方式实现动态SQL语句,并通过示例对比不同方式的差异。相比使用format方式更简单高效。
标签:语句,format,生成,编译,SQL,test,动态,KingbaseES From: https://www.cnblogs.com/kingbase/p/17931080.html