--什么是动态SQL？动态PL/SQL
--动态SQL是指在运行时刻才构建执行的SQL语句
--动态PL/SQL是指整个PL/SQL代码块都是动态构建，然后再编译执行
--动态SQL来可以用来干什么？
--执行DDL语句
--支持WEB引用的即席查询和即席更新需求
--软编码的业务规则和公式
--先来看dbms_sql包的使用
DECLARE v_cur number;
v_sql varchar2(1000);
v_result number;
BEGIN
v_cur := dbms_sql.open_cursor;
v_sql := 'update ma_users set user_point = :point where user_name = :name';
dbms_sql.parse(v_cur, v_sql, dbms_sql.native);
dbms_sql.bind_variable(v_cur, ':point', 10000);
dbms_sql.bind_variable(v_cur, ':name', '乱世佳人');
v_result := dbms_sql.execute(v_cur);
dbms_sql.close_cursor(v_cur); END;
/
--NDS：原生动态SQL。相较于DBMS_SQL包执行动态SQL要简单的多
--怎么简单法呢？
--只要一个语句就够了
EXECUTE IMMEDIATE --立即执行
--语法
EXECUTE IMMEDIATE SQL_string
[into {defined_varibale[,defined_varibale2]...|record} ]
[using [in|out|in out]] bind_argument
[, [in|out|in out] bind_argument];
--sql_string :包含了SQL语句或者PL/SQL代码块的字符串表达式
--defined_varibale：用于接收查询中某一列值的变量
--record: 用户自定义类型或者基于%rowtype的记录，可以接收查询返回的一行值
--bind_argument:表达式，表达式的值将传给SQL语句或者PL/SQL块，也可以是一个标识符
--这个标识符作为PL/SQL块中调用的函数或者过程的输入或者输出变量
--into:这个字句用于单行的查询，对于查询结果的每一列的值，必须提供一个单独的变量或者一个兼容的记录类型的一个
--字段
--USING子句：利用这个子句给SQL字符串提供绑定参数，同时用于动态SQL和动态PL/SQL
--使用动态PL/SQL时可以指定一个参数模式，缺省模式是IN
DECLARE v_cur number;
v_sql varchar2(1000);
v_result number;
BEGIN v_sql := 'update ma_users set user_point = :point where user_name = :name';
execute immediate v_sql using 10000, '乱世佳人'; END;
/ --EXECUTE IMMEDIATE 可以用于除多行查询以外的SQL语句或者PL/SQL块 ,bulk collect INTO
--如果sql_string后面带分号，则会按照一个PL/SQL块来处理 否则就是DML或者DDL
--字符串可以带有绑定参数的占位符，但是对象的名字，比如表的名字或者列的名字，不能通过绑定变量传进去
DECLARE v_cur number;
v_sql varchar2(1000);
v_result number;
BEGIN v_sql := 'BEGIN update :TAB_NAME set user_point = :point where user_name = :name; END ;';
execute immediate v_sql
using 'MA_USERS',10000, '乱世佳人'; END;
/
--想一想为什么对象名字不能通过绑定变量进行传递？
--例子,
--最简单的例子，执行建表语句
BEGIN
EXECUTE IMMEDIATE 'CREATE TABLE USER_ORDER(USER_ID VARCHAR2(32),ORDER_ID VARCHAR2(32))';
END;
/
--更简单，可以创建通用的方法，比如
create or replace procedure exec_ddl(ddl_string in varchar2)
authid current_user
is
begin
EXECUTE IMMEDIATE ddl_string;
end ;
/
begin
exec_ddl('create table user_order_product(order_id varchar2(32),product_id varchar2(32))');
end ;
/ --传递表名，返回数量
create or replace function count_tab(i_table_name in varchar2)
return number is
v_count number;
begin EXECUTE IMMEDIATE 'select count(1) from ' || i_table_name
into v_count;
-- EXECUTE IMMEDIATE 'select count(1) from :table_name'
-- into v_count using i_table_name;
return v_count;
end;
/ begin
if count_tab('ma_users') <10 then
dbms_output.put_line('用户量不超过十个，太逊了');
end if;
end ;
/
--动态更新列，传进列的名称，就可以更新相应列的值
create or replace function update_col(i_col in varchar2,
i_val in varchar2,
i_start_date in date,
i_end_date in date) return number is begin
execute immediate 'update ma_users set ' || i_col ||
'=:1 where created_date between :2 and :3'
using i_val, i_start_date, i_end_date;
return sql%rowcount;
end;
/
--上述例子使用了绑定参数，对UPDAE语句分析结束后，引擎就会把几个占位符用USING子句中的值替换
--当一个语句在执行的时候，运行引擎会把SQL语句中的每一个占位符用USING语句中对应的绑定参数替换
--注意不能传进NULL直接量，必须通过一个数据类型正确但恰好是NULL值的变量传入
--using语句不能绑定专属于PL/SQL的数据类型，比如布尔类型，关联数组以及用户自定义的记录类型
--支持所有的SQL数据类型
--现在假设需要设计一个任务调度，可以每隔一段时间跑一次，需要入参可配置，任务也可配置 create table ma_schedue_task(task_id varchar2(50),procedure_name varchar2(100));
create table ma_schedue_param(task_id varchar2(50),param_order number,param_value varchar2(100));
declare
v_task_id varchar(32);
begin
insert into ma_schedue_task
values(sys_guid(), 'test_bind(:1,:2)')
returning task_id into v_task_id;
insert into ma_schedue_param values (v_task_id, 1, 'test1');
insert into ma_schedue_param values (v_task_id, 2, 'test2');
commit;
end;
/
create or replace procedure test_bind(i_test1 in varchar2,
i_test2 in varchar2) is begin
dbms_output.put_line(i_test1 || i_test2); end test_bind;
/
declare
cursor cur_task is
select * from ma_schedue_task;
v_sql varchar2(4000);
begin for v in cur_task loop
v_sql := ' begin execute immediate '' begin ' || v.procedure_name ||
' ; end;'' using ';
for param in (select r.param_value
from ma_schedue_param r
where r.task_id = v.task_id
order by param_order) loop
v_sql := v_sql || ' ''' || param.param_value || ''',';
end loop;
v_sql := rtrim(v_sql, ',') || '; end;'; dbms_output.put_line(v_sql);
execute immediate v_sql;
end loop;
end;
/
---OPEN FOR 语句
--上节课讲到游标变量时，用到这个语句
--这个语句可以用来实现多行的动态查询
--语法
OPEN {cursor_variable|:host_cursor_variable} for sql_string
[using bind_argument[,bind_argument]... ]; --cursor_variable； 弱类型的游标变量
-- :host_cursor_variable ：在PL/SQL宿主环境比如OCI程序中声明的游标变量
-- sql_string: 包含了将要动态执行的SELECT 语句
--Using 字句：跟 execute immediate语句遵守同样的规则 create or replace function show_table(i_table in varchar2,
i_where in varchar2)
return sys_refcursor is
v_cur sys_refcursor;
begin open v_cur for 'select * from ' || i_table || ' where ' || i_where;
return v_cur;
end show_table;
/
declare
v_cur sys_refcursor;
v_user ma_users%rowtype;
begin
v_cur := show_table('ma_users', ' user_name=''乱世佳人''');
loop
fetch v_cur
into v_user;
exit when v_cur%notfound;
dbms_output.put_line(v_user.user_name || '积分' || v_user.user_point); end loop;
close v_cur;
end;
/
--一旦使用OPEN FOR 打开一个查询，接下来获取数据，关闭游标变量，检查游标属性的语法规则和静态游标变量
--以及硬编码的显示游标都是一样的
--执行一个OPEN FOR 语句是，PL/SQL引擎将会做以下事情
--用一个游标变量关联查询字符串中的查询语句
--对绑定参数值，然后用这些值替换查询字符串中的占位符
--执行查询
--识别出结果集
--将游标位置置于结果集的第一行
--把已处理行计数器归零，这个计数器也就是SQL/rowcount返回的值
--值得注意的是，查询语句中的任何绑定参数，都是游标变量在打开时才求值的
--也就是说，如果要把不同的绑定参数值用于同一个动态查询，必须用这些参数再执行一个新的OPEN FOR语句
create or replace procedure show_col(i_table in varchar2,
i_col in varchar2,
i_where in varchar2 := null) is
v_cur sys_refcursor;
v_val varchar2(1000);
begin
open v_cur for 'select ' || i_col || ' from ' || i_table || ' where ' || nvl(i_where,
' 1=1');
loop
fetch v_cur
into v_val;
exit when v_cur%notfound; if v_cur%rowcount = 1 then
dbms_output.put_line(i_col || ' of ' || i_table);
end if;
dbms_output.put_line(v_val);
end loop;
close v_cur;
end show_col;
/
begin
show_col('ma_users','user_name');
end ;
/
--上述例子把数据提取到一个单独的变量中，还可以把数据提取到一系列变量中
--也可以放到一个记录中
--可以为不同的需求创建不同类型的记录类型，这些类型可以放置包中，以便全局可用
--OPEN FOR 中的USING子句
--对于查询语句只能用IN 模式
--通过绑定参数，可以提升SQL语句性能，而且代码更容易编写和维护
--可以显著减少需要缓存在SGA中的编译后不同的语句数量
create or replace procedure show_col1(i_table in varchar2,
i_col in varchar2,
i_dcol in varchar2,
i_bg_date in date,
i_end_date in date) is
v_cur sys_refcursor;
v_val varchar2(1000);
begin
open v_cur for 'select ' || i_col || ' from ' || i_table || ' where ' || i_dcol || ' between :1 and :2'
using i_bg_date, i_end_date;
loop
fetch v_cur
into v_val;
exit when v_cur%notfound; if v_cur%rowcount = 1 then
dbms_output.put_line(i_col || ' of ' || i_table);
end if;
dbms_output.put_line(v_val);
end loop;
close v_cur;
end show_col1;
/ begin
show_col1('ma_users','user_name','REGISTER_DATE',date '2016-01-01',sysdate);
end ;
/
--四种动态SQL方法
-----------------------------------------------------------------------------------------------------------
--类型 说明 使用NDS语句
--第一种方法 非查询；只用于UPATE,INSERT,MERGE,DELETE和DDL语句且不带有绑定变量 不带USING into子句的
-- EXECUTE IMMEDIATE语句
--第二种方法 非查询；只用于UPATE,INSERT,MERGE,DELETE且带有绑定变量个数固定 带有USING子句的
-- EXECUTE IMMEDIATE语句
--第三种方法 带有确定数量的列和绑定变量，返回只有一行数据 带有USING into子句的
--之单行查询 EXECUTE IMMEDIATE语句
--第三种方法之 带有固定数量的列和绑定变量，返回多行数据 带有USING BULK COLLECT INTO 的EXECUTE IMMEDIATE语句
--多行查询 或者动态字符串的OPEN FOR 语句
--第四种方法 语句中选定的列的数量或者绑定数量等到允许时刻才能确定 使用DBMS_SQL包
--
-------------------------------------------------------------------------------------------------------------
--绑定变量
--使用绑定变量的规则和情况
--SQL语句中可以绑定的是，可以把动态字符串中的占位符替换成数值的直接量（文本，变量，复杂的表达式）
--不能绑定模式元素的名字（表或者列）或者SQL语句的一整块，比如where 子句
--对于这部分，必须使用拼接的方式
begin
execute immediate 'update :table set :col = :value where 1=1'
using 'ma_users', 'user_point', 10000;
end;
/
--为什么会有一个限制呢？
--当给EXECUTE IMMEDIATE 传入一个字符串时，运行时引擎首先必须要解析这个语句
--解析的目的是保证SQL语句是定义良好的
begin
execute immediate 'update ma_users set user_point = :value where 1=1'
using 10000;
end;
/
--参数的模式：in,out,in out
--执行动态查询时，所有的绑定参数都必须是IN 模式，除非使用了RETURNING
DECLARE
V_POINT NUMBER := 1000;
V_NAME VARCHAR2(20) := '乱世佳人';
V_PHONE varchar2(50);
begin
execute immediate 'update ma_users set user_point = :value where USER_NAME= :NAME
RETURNING USER_PHONE INTO :PHONE'
using V_POINT, V_NAME, OUT V_PHONE;
dbms_output.put_line(v_phone);
end;
/
--除了能用在RETURNING子句，OUT,IN OUT模式的绑定参数在执行动态PL/SQL时发挥比较大的作用
--在动态PL/SQL中绑定参数的模式必须要和PL/SQL程序中参数模式一致 --重复的占位符
--NDS根据位置而不是名字把USING语句的绑定参数关联到占位符的
--当执行一个动态SQL字符串，必须为每一个占位符提供一个参数，即便这些占位符是重复的
--如果执行的是一个动态PL/SQL块，必须为每一个唯一占位符提供一个参数
DECLARE
V_POINT NUMBER := 1000;
V_NAME VARCHAR2(20) := '乱世佳人';
V_PHONE varchar2(50);
V_email varchar2(50);
begin
execute immediate 'update ma_users set user_point = :value where USER_NAME= :value
RETURNING USER_PHONE,user_email INTO :1,:2'
using V_POINT, V_NAME, OUT V_PHONE, out V_email;
dbms_output.put_line(v_phone || V_email);
end;
/ DECLARE
V_POINT NUMBER := 1000;
V_NAME VARCHAR2(20) := '乱世佳人';
V_PHONE varchar2(50);
V_email varchar2(50);
begin
execute immediate 'begin update ma_users set user_name = :value where USER_NAME= :value
RETURNING USER_PHONE,user_email INTO :1,:2 ; end ;'
using V_NAME, OUT V_PHONE,out V_email;
dbms_output.put_line(v_phone||v_email);
end;
/
--NULL值的传递
--把NULL值隐藏在一个变量后面
--通过转换函数把NULL值显示的转换为一个有类型的值 DECLARE
V_POINT NUMBER := null;
V_NAME VARCHAR2(20) := '乱世佳人';
V_PHONE varchar2(50);
begin /*execute immediate 'update ma_users set user_point = :value where USER_NAME= :NAME
RETURNING USER_PHONE INTO :PHONE'
using null, V_NAME, OUT V_PHONE;
dbms_output.put_line(v_phone);*/ /*execute immediate 'update ma_users set user_point = :value where USER_NAME= :NAME
RETURNING USER_PHONE INTO :PHONE'
using V_POINT, V_NAME, OUT V_PHONE;
dbms_output.put_line(v_phone);
*/ execute immediate 'update ma_users set user_point = :value where USER_NAME= :NAME
RETURNING USER_PHONE INTO :PHONE'
using to_number(null), V_NAME, OUT V_PHONE;
dbms_output.put_line(v_phone);
end;
/
---动态PL/SQL
--NDS可以为我们做以下事情
--创建一个程序，包括带有全局访问的数据结构的包
--通过名字获得或修改全局变量的值
--调用那些在编译时刻还不知道名字的函数或者过程
--使用动态PL/SQL块和NDS的规则和技巧
--动态字符串必须是一个有效的PL/SQL块，这个块必须以DECLARE或者BEGIN关键字开始
--使用END关键字和分号结束。如果字符串不以分号结尾，是不会被识别成PL/SQL块的
--在动态块中，只能访问属于全局范围的PL/SQL代码元素。动态块是在局部包围块的作用范围之外执行的
--在动态PL/SQL块中抛出的错误可以在运行EXECUTE IMMEDIATE语句的局部块中捕获并处理
create or replace procedure do_plsql(i_plsql in varchar2)
is
begin
execute immediate 'begin '|| rtrim(i_plsql,';')||' ; end ;';
end ;
/
--下面这个例子反映了规则二
declare
num number ;
begin
do_plsql('num := 5');
exception
when others then
dbms_output.put_line(sqlerrm);
end ;
/ begin
<<test>>
declare
num number;
begin
do_plsql('test.num := 5');
end;
end;
/
create or replace package pkgvars
is
num number ;
end pkgvars; declare
num number ;
begin
do_plsql('pkgvars.num := 5;');
end ;
/
--用动态块替换重复的代码
--比如有以下方法
procedure do_task(task_name in varchar2) is begin
if task_name = 'test' then
test;
elsif task_name = 'test2' then
test2;
......
end if;
end ;
/
procedure do_task(task_name in varchar2) is begin
execute immediate
'begin '||task_name||' ; end ;';
end ;
/
--NDS的建议
--对于共享的程序使用调用者权限
create or replace procedure exec_ddl(ddl_string in varchar2)
authid current_user
is
begin
EXECUTE IMMEDIATE ddl_string;
end ;
/ --预估并处理动态的错误
--如果使用大量的动态SQL，很容易迷失方向以至于在代码调试上浪费大量时间
--在调用EXECUTE IMMEDIATE和OPEN FOR 时，总是带上一个异常处理单元
--在每一个异常处理句柄，记录下显示错误发生时的错误信息以及SQL语句
--可以考虑在这些语句之前加上一个“跟踪机制”
create or replace procedure exec_ddl(ddl_string in varchar2)
authid current_user
is
begin
EXECUTE IMMEDIATE ddl_string;
EXCEPTION
WHEN OTHERS THEN
DBMS_OUTPUT.put_line('FAILLURE:'||DBMS_UTILITY.format_error_backtrace);
DBMS_OUTPUT.put_line('ON :'||ddl_string);
end ;
/ --使用绑定而不是拼接
DECLARE
V_POINT NUMBER := 1000;
V_NAME VARCHAR2(20) := '乱世佳人';
V_PHONE varchar2(50);
begin
execute immediate 'update ma_users set user_point = :value where USER_NAME= :NAME'
using V_POINT, V_NAME
dbms_output.put_line(v_phone);
end;
/
DECLARE
V_POINT NUMBER := 1000;
V_NAME VARCHAR2(20) := '乱世佳人';
V_PHONE varchar2(50);
begin
execute immediate 'update ma_users set user_point =' || V_POINT ||
'where USER_NAME=''' || V_NAME || '''';
dbms_output.put_line(SQL%ROWCOUNT);
end;
/
--只要有可能使用绑定的方式就不要依赖与拼接
--绑定通常更快速
--绑定的编写和维护都很容易
--绑定有助于避免隐式转换
--绑定避免了发生代码注入的可能性
--对于一些场景，如果使用拼接更有效，那也要毫不犹豫的使用拼接方式
--把代码注入的风险最小化
create or replace procedure show_table1(i_table in varchar2,
i_where in varchar2) is
v_sql varchar2(1000);
begin
v_sql := 'declare v_row ' || i_table || '%rowtype;
begin
select * into v_row from ' || i_table || ' where ' || i_where || '
end ;';
dbms_output.put_line(v_sql);
-- execute immediate v_sql;
end show_table1;
/ declare
begin
show_table1('ma_users',
' user_name=''乱世佳人'';delete from ma_users ; ');
end;
/ --代码注入也叫SQL注入，可以严重的威胁程序的安全，动态PL/SQL块的执行为代码注入开启了最大的可能性
--限制用户权限
--尽可能使用绑定变量，但使用绑定，也丧失了一些灵活性
--检测动态文本中的危险文本
--用DBMS_ASSERT检验输入
DBMS_ASSERT.SIMPLE_SQL_NAME ---什么时候使用DBMS_SQL
---解析非常长的字符串
--EXECUTE IMMEDIATE 执行的字符串大小限制32K，在11g中可以处理一个CLOB，最大长度4GB
dbms_sql.parse --可以解析任意长度的SQL和PLSQL ---得到查询的列的信息
--dbms_sql可以对动态游标中的列进行描述，以记录的关联数组的形式返回每个列的信息
--用这个功能，可以写出非常通用的游标处理代码
--动态SQL的第四种方法
declare
cur pls_integer := dbms_sql.open_cursor;
cols dbms_sql.desc_tab;
ncols pls_integer;
begin
dbms_sql.parse(cur,'select user_name ,user_point from ma_users',dbms_sql.native);
dbms_sql.describe_columns(cur,ncols,cols);
for i in 1..ncols loop
dbms_output.put_line(cols(i).col_name);
end loop ;
dbms_sql.close_cursor(cur);
end ;
/
--实现第四种方法的动态SQL的需求
declare
cursor cur_task is
select * from ma_schedue_task;
v_sql varchar2(4000);
v_cur number;
v_result number;
begin for v in cur_task loop
v_sql := ' begin ' || v.procedure_name || ';end;';
v_cur := dbms_sql.open_cursor;
dbms_sql.parse(v_cur, v_sql, dbms_sql.native);
for param in (select r.param_order, r.param_value
from ma_schedue_param r
where r.task_id = v.task_id
order by param_order) loop
dbms_sql.bind_variable(v_cur,
':' || param.param_order,
param.param_value);
end loop;
v_result := dbms_sql.execute(v_cur);
dbms_sql.close_cursor(v_cur);
end loop;
end;
/
--11g新特性
dbms_sql.to_refcursor --原生态动态sql和DBMS_SQL的交互 declare
type string_t is table of varchar2(100); function get_data(i_where in varchar2, i_value in string_t)
return sys_refcursor is
v_sql varchar2(1000);
v_cur number;
v_result number;
v_result_cur sys_refcursor;
begin
v_sql := 'select * from ma_users where ' || i_where;
v_cur := dbms_sql.open_cursor;
dbms_sql.parse(v_cur, v_sql, dbms_sql.native);
for i in 1 .. i_value.count loop
dbms_sql.bind_variable(v_cur, ':' || i, i_value(i));
end loop;
v_result := dbms_sql.execute(v_cur);
v_result_cur := dbms_sql.to_refcursor(v_cur);
return v_result_cur;
end get_data;
begin
declare
v_cur sys_refcursor;
v_row ma_users%rowtype;
begin
v_cur := get_data(' user_name=:1', string_t('乱世佳人'));
loop
fetch v_cur
into v_row;
exit when v_cur%notfound;
dbms_output.put_line(v_row.user_point);
end loop;
close v_cur;
end;
end;
/