动态SQL 是指在执行时才构建 SQL 语句, 相对于静态 sql 的编译时就已经构建.
动态PLSQL 是指整个PL/SQL代码块都是动态构建, 然后再编译执行的.
作用:
1. 可以支持 DDL 语句, 静态 sql 只能支持 DML 语句.
2. 支持web 引用程序的查询意愿( 一个网络应用程序的常见需求是用户可以指定他们想看到的列, 以及改变数据的排序方式 )
2. 可以将业务逻辑先放在表中, 然后再动态编译.
NDS 是我们首选的动态解决方案.
NDS 执行动态语句的办法就是 execute immediate 和 open for 语句.
我们可以把 execute immediate 用于除了多行查询以外的任何SQL语句或者PL/SQL块. 如果sql_string 后面带了分号, 这个语句就会按照一个PL/SQL块来处理; 否则, 就会按照DML或者DDL语句来处理, 这个字符串可以带有绑定参数的占位符, 但是对象的名字, 比如表的名字或者列的名字, 不能通过绑定变量传进去.
注意: 当程序在执行一个DDL语句时, 我们同时也执行了一个提交动作, 如果我们不希望这种提交动作影响了之前未决定的修改项, 我们可以把这个DDL放到一个自治事务中.
当一个语句执行时, 运行引擎会把SQL语句中的每一个占位符( 一个带有冒号前缀的标示符, 比如 :salary_value) 用using 子句中对应的绑定参数替换. 注意我们不能直接传入一个NULL直接量, 相反, 我们必须用一个数据类型正确并且恰好是NULL 值得变量来传入.
例如:
begin
execute immediate 'create index emp_u_1 on employees(last_name)';
end; create or replace exec_ddl(ddl_string IN varchar2)
is
begin
execute immediate ddl_string;
end; -- 运行
begin
exec_ddl('create index emp_u_1 on employees(last_name)');
end;
-- 也可以直接用
exec exec_ddl('create index emp_u_1 on emplyees(last_name)');
例2:
create or replace function tabcount(table_in in varchar2)
return pls_integer
is
l_query varchar2(32767) := 'select count(*) from ' || table_in;
l_return pls_integer;
begin
execute immediate l_query into l_return; -- 注意这里的into子句
return l_return;
end;
/ begin
if tabcount('employees') > 100 then
dbms_output.put_line('we are growing fast!');
else
dbms_output.put_line('we are growing slow');
end if;
end;
例3:
create or replace function updNVal( col in varchar2, val in number,
start_in in date, end_in in date)
return pls_integer
is
begin
execute immediate
'update employees set ' || col || ' = :the_value
where hire_date between :lo and :hi'
using val, start_in, end_in; -- 这里的, using 子句传参给上边的dml语句
return sql%rowcount;
end;
/
show errors;
OPEN FOR 语句
create or replace procedure show_parts_inventory(
parts_table in varchar2, where_in varchar2)
is
type query_curtype is ref cursor; -- 游标类型
dyncur query_curtype; -- 游标变量
begin
open dyncur for
'select * from ' || parts_table
'where || where_in';
-- using 这里同样可以使用 using 子句
... --接下来的 fetch 等操作, 跟前边的游标完全相同
4 种 动态 SQL 方法
第4种方法一般不用, 可以忽略
当我们给 execute immediate 传入一个字符串时, 运行时引擎首先必须要解析这个语句, 解析的目的就是保证 SQL 语句定义良好.
例如:
参数模式
IN 只读值, 默认
OUT 只准许写
INOUT 可以读取传入的值, 也可以把值传递出去
当我们执行动态查询语句时, 所有的绑定参数都必须是IN模式的, 除非我们像下面这样使用了returning子句：
create or replace procedure wrong_incentive(
company_in IN varchar2,
new_layoffs in number)
is
sql_string varchar2(32767);
sql_after_layoffs number;
begin
sql_string := -- 注意, 下边的变量居然在单引号内
'update jobs
set min_salary = min_salary + 1 * :layoffs
where job_id = :company
returning min_salary into :newsal'; execute immediate sql_string
using new_layoffs, company_in, OUT sql_after_layoffs; -- 注意顺序 dbms_output.put_line( 'CEO copensation after latest round of layoffs $' || sal_after_layoffs);
end;
下面就是在动态PL/SQL执行过程中使用 USING 子句的一些指导建议
- 可以给一个IN模式的绑定变量提供任何类型正确的表达式, 可以用直接量, 被命名的常量, 变量, 复杂表达式, 这些表达式被求值后再传递给动态的 PL/SQL块.
- 对于OUT或者IN OUT 模式的绑定变量, 我们必须提供一个变量接收传出值.
- 对于动态 PL/SQL 块, 我们只能绑定那些 SQL 类型的变量值, 比如 bolean 类型, 我们不能再USING子句中设置.
Null 值传递
我们不能传 null 值(直接量), 那么解决的办法是:
1. 先定义一个变量, 默认是 null , 然后把这个变量穿进去
2. 通过转换函数, 例如 USING to_number(NULL); -- 不是直接使用 USING NULL
动态PL/SQL 块
技巧和规则:
- 动态字符串必须是一个有效的PL/SQL块, 这个块必须是以 DECLARE 或者 BEGIN 关键字开始, 用 END 关键字和分号结束, 如果字符串不是用分号结尾的, 是不会被识别成PL/SQL块的
- 在动态块中, 我们只能访问属于全局作用范围的PL/SQL代码元素
- 在动态PL/SQL 块中抛出的错误可以在运行EXECUTE IMMEDIATE 语句的局部块中捕获并处理.
NDS 建议
绑定包括使用占位符和USING子句; 拼接通过把值直接添加到SQL字符串而缩短了处理过程.
能使用绑定变量的地方就使用绑定变量
绑定的好处: 速度快, 容易维护, 避免隐式转换, 避免发生代码注入的可能性.( 因为sql 语句是以字符串的形式传递进去的, 如果没有绑定变量, 随便写个SQL的字符串就可以了)
代码注入安全问题, 记得 TOM 大师说过只要给他一个执行存储过程的权限, 就可以做任何事情, 这就是因为他注入了危险代码--修改权限的代码.
如何防止这种代码注入的危险, 1.限制用户权限 2.尽可能使用绑定变量 3.判断是否为危险脚本