如何使用动态SQL语句?

原创 2013年12月03日 22:21:11

这里只介绍动态SQL的使用。关于动态SQL语句的语法,参见:http://blog.csdn.net/chiclewu/article/details/16097133

1.什么是时候需要使用动态SQL?

  • SQL文本在编译时是未知的。

例如,SELECT语句包含的标识符(如表名)在编译时是未知的,或者WHERE子句的条件数量在编译时是未知。

  • 静态SQL不支持

例如,在PL/SQL中用静态SQL只能执行查询以及DML语句。如果想要执行DDL语句,只能使用动态SQL。

当让使用静态SQL,也有它的好处:

  • 编译成功验证了静态SQL语句引用有效的数据库对象和访问这些对象的权限

  • 编译成功创建了模式对象的依赖关系

2.EXECUTE IMMEDIATE语句

EXECUTE IMMEDIATE语句的意思是使用本地动态SQL处理大多数动态SQL语句。

如果动态SQL语句是自包含的(也就是说,它的绑定参数没有占位符,并且结果不可能返回错误),则EXECUTE IMMEDIATE语句不需要子句。

如果动态SQL语句包行占位符绑定参数,每个占位符在EXECUTE IMMEDIATE语句的子句中必须有一个相应的绑定参数,具体如下:

  • 如果动态SQL语句是一个最多只能返回一行的SELECT语句,OUT绑定参数放置在INTO子句,IN绑定参数放置在USING子句。
  • 如果动态SQL语句是一个可以返回多行的SELECT语句,OUT绑定参数放置在BULK COLLECT INTO子句,IN绑定参数放置在USING子句。
  • 如果动态SQL语句是一个除了SELECT以外的其他DML语句,且没有RETURNING INTO子句,所有的绑定参数放置在USING子句中。
  • 如果动态SQL还语句一个匿名PL/SQL块或CALL语句,把所有的绑定参数放置在USING子句中。

如果动态SQL语句调用一个子程序,请确保:

  • 每个对应子程序参数占位符的绑定参数与子程序参数具有相同的参数模式和兼容的数据类型。

  • 绑定参数不要有SQL不支持的数据类型(例如,布尔类型,关联数组,以及用户自定的记录类型)

     

USING子句不能包含NULL字面量。如果想要在USING子句中使用NULL值,可以使用位初始化的变量或者函数显示将NULL转换成一个有类型的值。

 

2.1动态SQL语句是一个最多只能返回一行的SELECT语句

使用动态SQL语句返回单列,查询SCOTT的薪水:

declare
  v_sql_text varchar2(1000);
  v_sal      number;
  v_ename    emp.ename%type := 'SCOTT';
begin
  v_sql_text := 'select e.sal from emp e where e.ename = :ename';

  execute immediate v_sql_text
    into v_sal
    using v_ename;

  dbms_output.put_line(v_ename || ':' || v_sal);

end;

 

使用动态SQL返回一条记录,查询SCOTT的基本信息:

declare
  v_sql_text varchar2(1000);
  v_ename    emp.ename%type := 'SCOTT';
  vrt_emp    emp%rowtype;
begin
  v_sql_text := 'select * from emp e where e.ename = :ename';
  execute immediate v_sql_text
    into vrt_emp
    using v_ename;
  dbms_output.put_line(v_ename || '的基本信息:');
  dbms_output.put_line('工号:' || vrt_emp.empno);
  dbms_output.put_line('工资:' || vrt_emp.sal);
  dbms_output.put_line('入职日期:' || vrt_emp.hiredate);

end;

 

2.2动态SQL语句是一个可以返回多行的SELECT语句

2.2.1只有一个占位符

使用动态SQL语句返回多行记录,查询30部门的员工基本信息:

declare
  v_sql_text varchar2(1000);
  v_deptno   emp.deptno%type := 30;

  type nt_emp is table of emp%rowtype;
  vnt_emp nt_emp;
begin
  v_sql_text := 'select * from emp e where e.deptno = :deptno';
  execute immediate v_sql_text bulk collect
    into vnt_emp
    using v_deptno;

  for i in 1 .. vnt_emp.count loop
    dbms_output.put_line(vnt_emp(i).ename || '的基本信息:');
    dbms_output.put_line('工号:' || vnt_emp(i).empno);
    dbms_output.put_line('工资:' || vnt_emp(i).sal);
    dbms_output.put_line('入职日期:' || vnt_emp(i).hiredate);
    dbms_output.put_line('');
  end loop;

end

 

2.2.2多个占位符

查询20部门工资大于2000的员工基本信息:

declare
  v_sql_text varchar2(1000);
  v_deptno   emp.deptno%type := 20;
  v_sal      number := 2000;

  type nt_emp is table of emp%rowtype;
  vnt_emp nt_emp;
begin
  v_sql_text := 'select * from emp e where e.sal>:sal and e.deptno = :deptno';
  execute immediate v_sql_text bulk collect
    into vnt_emp
    using v_sal, v_deptno; --注意绑定多个变量时,绑定变量只与占位符位置有关,与占位符名称无关,

  for i in 1 .. vnt_emp.count loop
    dbms_output.put_line(vnt_emp(i).ename || '的基本信息:');
    dbms_output.put_line('工号:' || vnt_emp(i).empno);
    dbms_output.put_line('工资:' || vnt_emp(i).sal);
    dbms_output.put_line('入职日期:' || vnt_emp(i).hiredate);
    dbms_output.put_line('');
  end loop;

注意:对于SQL文本,占位符名称是没有意义的,绑定变量与占位符名称无关,只与占位符的配置有关。即使有多个相同名称占位符,也需要每个占位符对应一个绑定变量。对于PL/SQL块,占位符名称是有意义的,相同名称的占位符,只需要第一个占位符绑定变量。

 

2.3动态SQL语句是一个带有RETURNING子句的DML语句

KING的工资增长20%,返回增长后的工资:

eclare
  v_sql_text varchar2(1000);
  v_sal      number;
  v_ename    emp.ename%type := 'KING';
begin

  v_sql_text := 'update emp e  set e.sal= e.sal*1.2 where e.ename = :ename returning e.sal into :sal';

  execute immediate v_sql_text
    using v_ename
    returning into v_sal;

  dbms_output.put_line(v_ename || ':' || v_sal);

end;

 

注意:只有当v_sql_text语句有returning into子句时,动态SQL语句才能使用returning into子句。

 

2.4给占位符传递NULL值

2.4.1通过未初始化变量传递NULL值

declare
  v_sql_text varchar2(1000);
  v_deptno   emp.ename%type := 'ALLEN';
  v_comm     emp.comm%type;

begin
  v_sql_text := 'update emp e set e.comm = :comm where e.ename =:ename';
  execute immediate v_sql_text
    using v_comm, v_deptno;
end;

2.4.2通过函数将NULL值显式的转换成一个有类型的值

declare
  v_sql_text varchar2(1000);
  v_deptno   emp.ename%type := 'ALLEN';
begin
  v_sql_text := 'update emp e set e.comm = :comm where e.ename =:ename';
  execute immediate v_sql_text
    using to_number(null), v_deptno;
end;

 

3.OPEN FOR语句

PL/SQL引入OPEN FOR语句实际上并不是为了支持本地动态SQL,而是为了支持游标变量。现在它以一种极其优雅的方式实现了多行的动态查询。

  1. 使用OPEN FOR语句来关联动态SQL语句的游标变量,在OPEN FOR语句的USING子句中,指定动态SQL语句每个占位符的绑定参数。

  2. 使用FETCH语句获取运行时结果集。
  3. 使用CLOSE语句关闭游标变量

使用OPEN FOR语句查询出10部门的员工的基本信息:

declare
  type rc_emp is ref cursor;
  vrc_emp rc_emp;

  v_sql_text varchar2(1000);
  v_deptno   emp.deptno%type := 10;
  vrt_emp    emp%rowtype;

begin
  v_sql_text := 'select * from emp e where e.deptno=:deptno';

  open vrc_emp for v_sql_text
    using v_deptno;
  loop
    exit when vrc_emp%notfound;
    fetch vrc_emp
      into vrt_emp;
 
    dbms_output.put_line(vrt_emp.ename || '的基本信息:');
    dbms_output.put_line('工号:' || vrt_emp.empno);
    dbms_output.put_line('工资:' || vrt_emp.sal);
    dbms_output.put_line('入职日期:' || vrt_emp.hiredate);
    dbms_output.put_line('');
 
  end loop;
  close vrc_emp;

end;

 

4.重复的占位符名称

如果在动态SQL语句重复占位符名称,要知道占位符关联绑定参数的方式依赖于动态语句的类型。

  • 如果执行的是一个动态SQL字符串,则必须为每一个占位符提供一个绑定参数,即使这些占位符是重复的。
  • 如果执行的是一个动态PL/SQL块,则必须为每一个唯一占位符提供一个绑定参数,即重复的占位符只需要提供一个绑定参数。

4.1重复占位符的动态SQL字符串

declare
  v_sql_text varchar2(1000);
  v_sal      emp.sal%type := 4000;
  v_comm     emp.comm%type;
  v_ename    emp.ename%type := 'SCOTT';
begin

  v_sql_text := 'update emp e set e.sal=:sal , e.comm = :sal*0.1  where e.ename =:ename returning e.comm into :comm ';

  execute immediate v_sql_text
    using v_sal, v_sal, in v_ename
    returning into v_comm;
  dbms_output.put_line(v_ename || '分红:' || v_comm);

end;

 

4.2重复占位符的动态PL/SQL块

declare
  v_sql_text varchar2(1000);
  v_sal      number;
  v_ename    emp.ename%type := 'KING';
begin

  v_sql_text := ' begin select e.sal,e.ename into :sal,:ename from emp e where e.ename =:ename; end;';

  execute immediate v_sql_text
    using out v_sal, in out v_ename;
  dbms_output.put_line(v_ename || ':' || v_sal);

end;

 

 

MyBatis注解应用之动态SQL语句

有时候,我们需要在输入的标准下,创建动态的查的语言。MyBatis提供了多个注解如:@InsertProvider,@UpdateProvider,@DeleteProvider和@SelectPro...
  • owen_william
  • owen_william
  • 2016年07月03日 16:01
  • 34907

动态SQL语句的语法

动态SQL是在运行时生成和执行SQL语句的编程方法。动态是和静态相对而言的。静态SQL指的是在代码编译时刻就已经包含在代码中的那些已经充分明确的固定的SQL语句。 PL/ SQL提供了两种方式来编写动...
  • chiclewu
  • chiclewu
  • 2013年11月14日 22:16
  • 5268

sql语句 静态sql和动态sql

使用动态SQL是在编写PL/SQL过程时经常使用的方法之一。很多情况下,比如根据业务的需要,如果输入不同查询条件,则生成不同的执行 SQL查询语句,对于这种情况需要使用动态SQL来完成。再比如,对于...
  • zy_281870667
  • zy_281870667
  • 2016年06月15日 16:45
  • 2700

SAL在OpenOffice中是什么意思

SAL stands for System Abstraction Layer. SAL provides a C API for standard OS functionality, e.g. as...
  • lantianjialiang
  • lantianjialiang
  • 2012年09月26日 17:22
  • 503

MySQL基础----动态SQL语句

动态sql语句基本语法  1 :普通SQL语句可以用Exec执行  eg:   Select * from tableName           Exec('select * from t...
  • abc19900828
  • abc19900828
  • 2014年09月23日 17:49
  • 8857

ProC动态SQL示例(第1,2,3种方法)

ProC动态SQL示例(第1,2,3种方法)草木瓜 2006-2-1下面是ProC前三种动态SQL的完整示例。(1)动态SQL1: 不能是查询(SELECT)语句,并且没有宿主变量.  用法:拼一句动...
  • liwei_cmg
  • liwei_cmg
  • 2006年05月29日 09:42
  • 18815

MyBatis中动态SQL语句完成多条件查询

一看这标题,我都感觉到是mybatis在动态SQL语句中的多条件查询是多么的强大,不仅让我们用SQL语句完成了对数据库的操作;还通过一些条件选择语句让我们SQL的多条件、动态查询更加容易、简洁、直观。...
  • yanggaosheng
  • yanggaosheng
  • 2015年06月29日 19:00
  • 30461

ORACLE动态SQL语句

问题的提出我们经常需要运行可变化的SQL语句,这种通常称为动态SQL,在ORACLE中执行动态的SQL语句,需要了解ORACLE的动态SQL语句的相关规定。 SQL动态语句是由程序或者存储过程生成的...
  • paul50060049
  • paul50060049
  • 2017年11月03日 12:06
  • 82

动态sql编程语句的四种格式 [

http://bbs.chinaunix.net/thread-507191-1-1.html 1,动态sql语句 格式1    直接执行sql声明(sqlstatement)   ...
  • mydriverc2
  • mydriverc2
  • 2013年12月26日 10:35
  • 567

sql server的动态语句用法

--建立测试环境IF OBJECT_ID('tb','U') IS NOT NULL  DROP TABLE tbGOCREATE TABLE tb( id int identity,  code v...
  • xys_777
  • xys_777
  • 2010年06月24日 10:28
  • 5206
内容举报
返回顶部
收藏助手
不良信息举报
您举报文章:如何使用动态SQL语句?
举报原因:
原因补充:

(最多只允许输入30个字)