动态SQL语句

动态SQL语句不仅是指SQL语句是动态拼接而成的,更主要的是SQL语句所使用的对象也是在运行时期才能创建的。


动态SQL语句基础

create or replace function get_tablecount(table_name in varchar2)

return pls_integer

is

sql_query varchar2(32767):='select count(*) from'||table_name;

l_return  pls_integer;

begin

execute immediate sql_query into l_return;

return l_return;

end;


调用

declare

v_count pls_integer;

begin

v_count:=get_tablecount('emp');

dbms_output.put_line('emp表的行数:'||v_count);

v_count:=get_tablecount('dept');

dbms_output.put_line('dept表的行数:'||v_count);

end;


静态SQL优点

1.静态SQL语句在编译或测试时,可以立即知道所需要的数据库对象是否存在,如果依赖的对象不存在,SQL语句将立即失败,而动态语言只是在运行时才会知道这种错误

2.静态SQL语句在编译或测试时,可以立即知道当前用户是否具有了所有的授权,同义词是否已定义,如果以来的对象不存在,SQL语句将执行失败,动态SQL语句只能延迟到运行时才能发现这种错误。

3.在使用静态SQL语句时,可以对要执行的sql语句进行性能优化调整,从而提高应用程序的性能,动态sql不具有这种能力。




动态SQL语句使用时机

使用动态SQL语句执行DDL语句

declare

v_counter number;

begin

select count(*) into v_counter from user_tables where table_name='EMP_TESTING';

if v_counter > 0 then

dbms_output.put_line('表存在不创建');

else

dbms_output.put_line('表不存在');

execute immediate 'create table emp_testing(

emp_name varchar2(18) not null,

hire_date date not null,

status number(2),

constraint PK_ENTRY_MODIFYSTATUS primary key(emp_name,hire_date))';

end if;

v_counter:=0;

end;

1.由于在PL/SQL中只能执行静态的查询和DML语句,因此要执行DDL语句,必须要借助于动态SQL语句,通过前面的示例可以了解到这个过程。

2.在开发报表或一些复杂的应用程序逻辑时,如果要基于参数化的查询方式,比如动态的表字段和动态的表名称,可以使用动态SQL语句

3.基于数据表存储业务规则和软件代码,可以将很多的业务规则的代码写在一个表的记录中,在程序需要时检索不同的业务逻辑代码动态地执行。




本地动态SQL

本地动态SQL缩写为NDS,它的运行速度比DBMS_SQL要快。以后将主要用NDS来执行动态SQL语句。

NDS不支持事先不知道参数的个数,名称或数据类型的动态SQL语句,此时需要使用DBMS_SQL来解决。

1.使用EXECUTE IMMEDIATE语句:DDL  DML DCL 以及单行

但是不能处理多行查询语句,多行查询需要使用open-for语句

2.使用open for,fetch和close语句执行多行查询

3.使用批量SQL的处理语句,通过批量的SQL语句的处理,可以加快SQL语句的处理,提高PL/SQL应用程序的性能。




使用EXECUTE IMMEDIATE

1.执行性能要高于DBMS_SQL

2.语法可以说是标准静态SQL的镜像,比内置的DBMS_SQL要简单

3.可以直接将记录提取到PLSQL记录类型中,而DBMS_SQL没有这个能力

4.支持所有静态SQL语句支持的PL/SQL并不支持用户自定义的类型


使用execute immediate最基本的语法是直接为其传递一个sql字符串,通常用来执行一些DML或DDL操作,或者是一个PLSQL块,在使用execute immediate执行一个sql语句时,不要在语句后面放一个分号,否则PLSQL引擎会提示错误信息,只有在执行PLSQL语句块时才需要添加分号。

declare

sql_statement varchar2(100);

begin

sql_statement:='create table ddl_demo(id number,amt number)';

execute immediate sql_statement;

sql_statement:='insert into ddl_demo VALUES(1,100)';

execute immediate sql_statement;

end;

执行动态PLSQL语句

declare

plsql_block varchar2(500);

begin

plsql_block:='declare

I integer:=10;

      begin

execute immediate ''truncate table ddl_demo'';  --嵌入的sql要用双引号

for j in 1..I loop

insert into ddl_demo values(j,j*100);

end loop;

end;';      --语句结束时添加分号

execute immediate plsql_block;

commit;

end;




使用绑定变量

在执行动态SQL语句时,可以在SQL字符串中使用绑定变量占位符,在程序运行时使用USING语句为占位符赋予不同的绑定变量值来动态地产生SQL语句。在绑定变量中可以使用所有的sql数据类型,预定义变量并且绑定变量参数可以是集合,大型对象,一种对象类型的实例及REF类型,但是不能是PL/SQL定义的类型。

要使用绑定变量,在SQL字符串中使用冒号加占位符名称指定占位符,然后使用USING子句根据占位符的顺序依次指定要进行绑定的变量。

declare

sql_stmt varchar2(200);

type id_table is table of integer;

type name_table is table of varchar2(8);

t_empno id_table:=id_table(9001,9002,9003,9004,9005);

t_empname name table:=name_table('张三','李四','王五','赵六','何七');

v_deptno number(2):=30;

v_loc varchar(20):='南京';

emp_rec emp%ROWTYPE;

begin

--为记录类型赋值,记录类型作为绑定变量将失败;

emp_rec.empno:=9001;

emp_rec.ename:='西蒙';

emp_rec.hiredate:=trunc(sysdate);

emp_rec.sal:=5000;

--使用普通的变量作为绑定变量

sql_stmt:='update dept set loc:=1 where deptno=:2';

execute immediate sql_stmt using v_loc,v_deptno;

--创建一个测试用的数据表

sql_stmt:='create table emp_name_tab(empno number,empname varchar(20))';

execute immediate sql_stmt;

--使用嵌套表变量的值作为绑定变量

sql_stmt:='insert into emp_name_tab values(:1,:2)';

for i in t_empno.first..t_empno.last loop

execute immediate sql_stmt using t_empno(i),t_empname(i);

end loop;

--使用记录类型提示失败

--sql_stmt:='insert into emp values :1';

--execute immediate sql_stmt using emp_rec;

end;


在sql语句中使用绑定变量时,仅能对用于数据值的表达式进行替换,比如静态文字,变量或复杂表达式,而不能对方案元素使用绑定表达式,比如将表名和列名作为绑定表达式,或者是对整个sql语句块使用绑定表达式,比如一个where子句,如果要动态定义方案元素,需要使用字符串拼接的方式对字符串进行拼接。


使用字符串拼接设置方案对象

create or replace procedure trunc_table(table_name in varchar2)

is

sql_stmt varchar2(100);

begin

sql_stmt:='truncate table '||table_name;

execute immediate sql_stmt;

end;




使用returning into 子句

如果使用execute immediate语句执行的DML语句中包含了returning子句,必须要使用returning into子句接收返回的数据,但是returning into 子句只能处理作用在单行上的DML语句,如果DML语句作用在多行上,则必须要使用bulk子句.

declare

v_empno number(4):=7369;

v_percent number(4,2):=0.12;

v_salary number(10,2);

sql_stmt varchar2(500);

begin

sql_stmt:='update emp set sal=sal*(1+:percent)'||'where empno=:empno returning sal into :salary';

execute immediate sql_stmt using v_percent,v_empno returning into v_salary;

dbms_output.put_line('调整后的工资为:'||v_salary);

end;




执行单行查询

declare

sql_stmt varchar2(100);

v_deptno number(4):=20;

v_empno number(4):=7369;

v_dname varchar2(20);

v_loc varchar2(20);

emp_row emp%ROWTYPE;

begin

--查询dept表的动态sql语句

sql_stmt:='select dname,loc from dept where deptno=:deptno';

--执行动态sql语句并记录查询结果

execute immediate sql_stmt into v_dname,v_loc using v_deptno;

--查询emp表的特定员工编号的记录

sql_stmt:='select * from emp where empno:=empno';

--将emp表中的特定行内容写入emp_row记录中

execute immediate sql_stmt into emp_row using v_empno;

dbms_output.put_line('查询的部门名称为:'||v_dname);

dbms_output.put_line('查询的员工编号为:'||emp_row.ename);

end;



指定参数模式

在使用using子句时,默认的参数模式是IN模式,using主要用来从变量中获取值。而returning into子句的参数不用指定输出参数模式,因为定义中它就是out模式.这些模式子程序的形式参数定义一样

IN:只读模式,这是默认的模式

OUT:只写模式

IN OUT:能够读取值然后向变量写入值后进行传出.

多数时候都不用显式地指定这些模式,但是有时需要为绑定变量指定IN OUT模式。

create or replace procedure create_dept(

deptno in out number,

dname in varchar2,

loc in varchar2

)

as

begin

if deptno in null then

select deptno_seq.nextval into deptno from dual;

end if;

insert into dept values(deptno,dname,loc);

end;


如果在动态PL/SQL中调用这个过程,在使用绑定变量时,必须要显式地指定参数的模式

declare

plsql_block varchar2(500);

v_deptno number(2);

v_dname  varchar2(14):='网络部';

v_loc   varchar2(13):='也门';

begin

plsql_block:='begin create_dept(:a,:b,:c);end;';

execute immediate plsql_block using in out v_deptno,v_dname,v_loc;

dbms_output.put_line('新建部门的编号为:'||v_deptno);

end;



多行查询语句

在使用静态sql语句批量多行时,需要使用游标机制来循环遍历,动态sql语句如果返回多行,也需要类似的处理。由于execute immediate语句只能处理单行查询语句,因此为了能动态处理select语句返回的多行数据,需要使用open for,fetch和close语句。


必须先定义一个游标变量,然后使用open for语句打开多行的动态sql语句到游标变量,当游标变量具有一个指向动态sql语句查询返回的多行数据以后,就可以通过循环遍历的方式使用fetch提取多行数据,最后调用close语句关闭游标.




使用open for语句

open for语句的使用方法与普通的游标定义语法非常相似,但是必须首先在声明部分对游标进行声明。


定义并打开动态SQL语句游标

declare

type emp_cur_type is ref cursor;

emp_cur emp_cur_type;

v_deptno number(4):='&deptno';

v_empno number(4);

v_ename varchar2(25);

begin

open emp_cur for

'select empno,ename from emp'||'where deptno=:1'

using v_deptno;

null;

end;




使用fetch语句


declare

type emp_cur_type is ref cursor;

emp_cur emp_cur_type;

v_deptno number(4):='&deptno';

v_empno number(4);

v_ename varchar2(25);

begin

open emp_cur for

'select empno,ename from emp'||'where deptno=:1' using v_deptno;

loop

fetch emp_cur into v_empno,v_ename;

exit when emp_cur%NOTFOUND;

dbms_output.put_line('员工编号:'||v_empno);

dbms_output.put_line('员工名称:'||v_ename);

end loop;

end;



关闭游标变量


多行动态SQL语句执行完整示例

declare

type emp_cur_type is ref cursor;

emp_cur emp_cur_type;

v_deptno number(4):='&deptno';

v_empno number(4);

v_ename varchar2(25);

begin

open emp_cur for

'select empno,ename from emp'||'where deptno=:1' using v_deptno;

loop

fetch emp_cur into v_empno,v_ename;

exit when emp_cur%NOTFOUND;

dbms_output.put_line('员工编号:'||v_empno);

dbms_output.put_line('员工名称:'||v_ename);

end loop;

close emp_cur;

exception

when others then

if emp_cur%FOUND then

close emp_cur;

end if;

dbms_output.put_line('ERROR:'||SUBSTR(SQLERRM,1,200));

end;



使用批量绑定


批量绑定允许对数据库的插入或更新的数据首先放到一个PL/SQL集合中,然后把一个集合里的所有行在一次操作中都传递到SQL引擎中,而不是通过for循环一次一次迭代计算,减少了在PL/SQL引擎和SQL之间传递的数据量,提高了运行的效率。



批量EXECUTE IMMEDIATE语法

EXECUTE IMMEDIATE语句通过使用BULK子句来提供批量绑定的能力。当使用BULK子句时,集合类型可以是PL/SQL所支持的索引表,嵌套表和varray,但是集合的元素必须是SQL类型,比如NUMBER,char或varchar2,而不能是PL/SQL特有的数据类型,比如BINARY_INTEGER或BOOLEAN等.


在oracle中有3种语句支持BULK子句,分别是EXECUTE IMMEDIATE,FETCH和FORALL。

1.多行DML语句

在进行DML处理时,通常一个insert,update或delete语句会影响到多行,为了获取影响多行的返回结果,在DML语句中指定了returning子句后,必须在execute immediate语句后使用bulk子句将结果信息写入到集合类型中。由于集合可以是嵌套表,索引表或变长数组,因此可以根据需要来定义集合类型并声明集合类型的变量,在DML中使用BULK子句的示例


declate

--定义索引表类型,用来保存从DML语句中返回的结果

type ename_table_type is table of varchar2(25) index by binary_integer;

type sal_table_type is table of number(10,2) index by binary_integer;

ename_tab ename_table_type;

sal_tab sal_table_type;

v_deptno number(4):=20;

v_percent number(4,2):=0.12;

sql_stmt varchar2(500);

begin

--定义更新emp表的sal字段值的动态sql语句

sql_stmt:='update emp set sal=sal*(1+:percent)'||'where deptno=:deptno returning ename,sal into :ename,:salary';

exception immediate sql_stmt using v_percent,v_deptno returning bulk collect into ename_tab,sal_tab;

for i in 1..ename_tab.count loop

dbms_output.put_line('员工'||ename_tab(i)||'调薪后的薪资:'||sal_tab(i));

end loop;

end;


2.多行select语句

execute immediate 语句中,由于将单行查询的into子句切换为了bulk collect into子句,使得有机会直接在execute immediate中处理多行查询。


使用bulk子句处理多行查询

declare

type ename_table_type is table of varchar2(20) index by binary_integer;

type empno_table_type is table of number(24) index by binary_integer;

ename_tab ename_table_type;

empno_tab empno_table_type;

v_deptno number(4):='&deptno';

sql_stmt varchar2(500);

begin

--定义多行查询的sql语句

sql_stmt:='select empno,ename from emp'||'where deptno=:1';

execute immediate sql_stmt

bulk collect into empno_tab,ename_tab using v_deptno;

for i in 1..ename_tab.count loop

dbms_output.put_line('员工编号'||empno_tab(i)||'员工名称:'||ename_tab(i));

end loop;

end;


在语句执行部分,定义了一个查询emp表中特定部门编号的多行查询,在execute immediate子句中使用了bulk collect into 子句来批量插入到索引表。




使用批量fetch语句

如果多行查询的结果数量超过了集合中的数量,那么在执行时oracle将产生一个错误


使用批量fetch语句获取多行查询结果

declare

type ename_table_type is table of varchar2(20) index by binary_integer;

type empno_table_type is table of number(24) index by binary_integer;

type emp_cur type is ref cursor;

ename_tab ename_table_type;

empno_tab empno_table_type;

emp_cur emp_cur_type;

v_deptno number(4):='&deptno';

begin

open emp_cur for 'select empno,ename from emp'||'where deptno=:1' using v_deptno;

fetch emp_cur bulk  collect into empno_tab,ename_tab;

close emp_cur;

for i in 1..ename_tab.count loop

dbms_output.put_line('员工编号'||empno_tab(i)||'员工名称:'||ename_tab(i));

end loop;

end;



使用批量forall 语句

forall语句允许在execute immediate中批量绑定输入参数,通过在forall语句中使用execute immediate语句,可以让多个动态SQL的执行变得更具效率.


使用forall语句更新多个员工薪资

declare

--定义索引表类型,用来保存从DML语句中返回的结果

type ename_table_type is table of varchar2(25) index by binary_integer;

type sal_table_type is table of number(10,2) index by binary_integer;

type empno_table_type is table of number(4);

ename_tab ename_table_type;

sal_tab sal_table_type;

empno_tab empno_table_type;

v_percent number(4,2):=0.12;

sql_stmt varchar2(500);

begin

empno_tab:=empno_table_type(7369,7499,7521,7566);

sql_stmt:='update emp set sal=sal*(1+percent)' ||'where empno=:empno returning ename,sal into :ename,:salary';

forall i in 1 .. empno_tab.count

execute immediate sql_stmt using v_percent,empno_tab(i);

returning bulk collect into ename_tab,sal_tab;

for i in 1 .. ename_tab.count loop

dbms_output.put_line('员工'||ename_tab(i)||'调薪后的薪资:'||sal_tab(i));

end loop;

end;

使用forall语句执行动态sql时,动态sql语句必须是insert,update或delete语句,不能为select语句。


动态SQL的使用建议

用绑定变量改善性能

当使用动态sql语句处理非方案对象的DML或查询操作时,可以选择字符串拼接或使用绑定变量。


要删除emp表中特定员工编号的记录,如果使用拼接的语法:

execute immediate 'delete from emp where empno='||to_char(emp_id);

可以使用绑定变量来实现这个删除操作。

excute immediate 'delete from emp where empno=:num' using emp_id;


1.绑定比连接具有更高的性能:由于使用绑定变量,并不会每次都改变SQL语句,因此可以使用SGA中缓存的预备游标来快速处理SQL语句。

2.绑定变量更容易编写和维护:使用绑定变量不用担心数据转换的问题,本地动态SQL引擎可以处理所有关于转换相关的问题,而对于连接字符串来说,必须要经常使用to_date或to_char函数处理数据类型转换.

3.避免隐士类型转换:连接sql语句有可能会导致数据库隐式转换,有可能会导致隐式转换为不想要的结果

4.绑定避免代码注入:使用绑定变量可以避免sql注入式攻击,而连接字符串有可能会导致这种危险的情形。




使用重复占位符

using子句中的绑定变量与动态sql语句中的参数占位符是通过位置关联的,因此即便在sql语句中同样的占位符出现了两次或多次,每次都会与一个占位符关联。

declare

col_in varchar2(10):='sal';

start_in date;

end_in date;

val_in number;

dml_str varchar2(32767)

:='update emp set'||col_in||'=:val where hiredate between :lodate and :hidate and :val is not null';

begin

execute immediate dml_str using val_in,start_in,end_in,val_in;

end;


val_in出现了两次以匹配在动态sql中的位置,但是,动态PL/SQL块中只有唯一的占位符才与using子句中的绑定参数按位置对应。所以,如果一个占位符在PLSQL块中出现两次或多次,那么所有这样的相同的占位符都只与using语句中的一个绑定参数相对象。

declare

col_in varchar2(10):='sal';

start_in date;

end_in date;

val_in number;

plsql_str varchar2(32767)

:='begin

update emp set'

||col_in

||'=:val

where hiredate between :lodate and :hidate and :val is not null;

end;

';

begin

execute immediate dml_str using val_in,start_in,end_in;

end;



使用调用者权限

create or replace procedure drop_obj(kind in varchar2,name in varchar2)

authid current_user    --定义调用者权限

as

begin

execute immediate 'DROP '||kind||' '||name;

exception

when others then

raise;

end;


传递NULL参数

using语句不接收null作为传递的参数。可以直接定义一个未赋值的变量,该变量在未赋值时自动为NULL值


declare

v_null char(1);    --在运行时该变量自动被设置为NULL值

begin

execute immediate 'update emp set comm=:p_null'

using v_null;  --传入null值

end;



动态SQL异常处理

1.总是在调用execute immediate和open for语句的地方包含异常处理块

2.在每一个异常处理块中记录和显示错误和执行的sql语句,以便发现错误

3.可以使用DBMS_OUTPUT包添加一个追踪机制以便能更好地发现错误


create or replace procedure ddl_execution(ddl_string in varchar2)

authid current_user is

begin

execute immediate ddl_string;

exception 

when others

then

dbms_output.put_line('动态SQL语句错误:'||DBMS_UTILITY.FORMAT_ERROR_STACK);

dbms_output.put_line('执行的sql语句为: " '||ddl_string ||' " ');

raise;

end ddl_execution;






      本文转自潘阔 51CTO博客,原文链接:http://blog.51cto.com/pankuo/1630257,如需转载请自行联系原作者


  • 0
    点赞
  • 0
    评论
  • 0
    收藏
  • 一键三连
    一键三连
  • 扫一扫,分享海报

表情包
插入表情
评论将由博主筛选后显示,对所有人可见 | 还能输入1000个字符
©️2021 CSDN 皮肤主题: 大白 设计师:CSDN官方博客 返回首页
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、C币套餐、付费专栏及课程。

余额充值