动态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 值得变量来传入.

例如:

 1 begin  2 execute immediate 'create index emp_u_1 on employees(last_name)';  3 end;  4  5 create or replace exec_ddl(ddl_string IN varchar2)  6 is  7 begin  8 execute immediate ddl_string;  9 end; 10 11 -- 运行 12 begin 13 exec_ddl('create index emp_u_1 on employees(last_name)'); 14 end; 15 -- 也可以直接用 16 exec exec_ddl('create index emp_u_1 on emplyees(last_name)');

例2:

 1 create or replace function tabcount(table_in in varchar2)  2 return pls_integer  3 is  4 l_query varchar2(32767) := 'select count(*) from ' || table_in;  5  l_return pls_integer;  6 begin  7 execute immediate l_query into l_return; -- 注意这里的into子句  8 return l_return;  9 end; 10 / 11 12 begin 13 if tabcount('employees') > 100 then 14 dbms_output.put_line('we are growing fast!'); 15 else 16 dbms_output.put_line('we are growing slow'); 17 end if; 18 end;

例3:

 1 create or replace function updNVal( col in varchar2, val in number,  2 start_in in date, end_in in date)  3 return pls_integer  4 is  5 begin  6 execute immediate  7 'update employees set ' || col || ' = :the_value  8  where hire_date between :lo and :hi'  9 using val, start_in, end_in; -- 这里的, using 子句传参给上边的dml语句 10 return sql%rowcount; 11 end; 12 / 13 show errors;

OPEN FOR 语句

 1 create or replace procedure show_parts_inventory(  2 parts_table in varchar2, where_in varchar2)  3 is  4 type query_curtype is ref cursor; -- 游标类型  5 dyncur query_curtype; -- 游标变量  6 begin  7 open dyncur for  8 'select * from ' || parts_table  9 'where  || where_in'; 10 -- using 这里同样可以使用 using 子句 11 ... --接下来的 fetch 等操作, 跟前边的游标完全相同

4 种动态 SQL 方法

第4种方法一般不用, 可以忽略

当我们给 execute immediate 传入一个字符串时, 运行时引擎首先必须要解析这个语句, 解析的目的就是保证 SQL 语句定义良好.

例如:

参数模式

IN 只读值, 默认

OUT 只准许写

INOUT 可以读取传入的值, 也可以把值传递出去

当我们执行动态查询语句时, 所有的绑定参数都必须是IN模式的, 除非我们像下面这样使用了returning子句：

 1 create or replace procedure wrong_incentive(  2 company_in IN varchar2,  3 new_layoffs in number)  4 is  5 sql_string varchar2(32767);  6 sql_after_layoffs number;  7 begin  8 sql_string := -- 注意, 下边的变量居然在单引号内  9 'update jobs 10  set min_salary = min_salary + 1 * :layoffs 11  where job_id = :company 12  returning min_salary into :newsal'; 13 14 execute immediate sql_string 15 using new_layoffs, company_in, OUT sql_after_layoffs; -- 注意顺序 16 17 dbms_output.put_line( 'CEO copensation after latest round of layoffs $' || sal_after_layoffs); 18 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 语句的局部块中捕获并处理.