如何使用动态SQL语句?

标签: sql 语句 | 发表时间:2013-12-04 06:21 | 作者:chiclewu
出处:http://blog.csdn.net

这里只介绍动态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;

 

 

作者:chiclewu 发表于2013-12-3 22:21:11 原文链接
阅读:110 评论:0 查看评论

相关 [sql 语句] 推荐:

sql语句总结

- zhaoloving - 博客园-首页原创精华区
1、创建数据库语句(以部门表(department)和员工表(employee)位例). --员工表 employee.   alter table [表名] add .     constraint [键名] Primary key [主键名称].   alter table [表名] add.      constraint [键名] unique(唯一键名称).

SQL语句大全

- - SQL - 编程语言 - ITeye博客
原文地址:http://www.cnblogs.com/yubinfeng/archive/2010/11/02/1867386.html. 3、说明:备份sql server. --- 创建 备份数据的 device. A:create table tab_new like tab_old (使用旧表创建新表).

sql经典语句

- - 数据库 - ITeye博客
3、说明:备份sql server. --- 创建 备份数据的 device. table tab_new like tab_old (使用旧表创建新表). DB2中列加上后数据类型也不能改变,唯一能改变的是增加. 注:索引是不可更改的,想更改必须删除重新建. 10、说明:几个简单的基本的sql语句.

sql语句优化

- - 数据库 - ITeye博客
性能不理想的系统中除了一部分是因为应用程序的负载确实超过了服务器的实际处理能力外,更多的是因为系统存在大量的SQL语句需要优化. 为了获得稳定的执行性能,SQL语句越简单越好. 对复杂的SQL语句,要设法对之进行简化. 1)不要有超过5个以上的表连接(JOIN). 2)考虑使用临时表或表变量存放中间结果.

SQL查询语句(oralce)(1)

- - ITeye博客
  主要用于创建删除数据库对象和维护数据对象的属性. 主要有三个主命令:CREATE .DROP. SQL>CREATE TABLE 表1 AS SELECT * FROM 表2;. SQL>DROP TABLE 表名;. SQL>--添加.修改.删除字段. SQL> ALTER TABLE 表名 ADD/MODIFY(字段 类型);.

(转)经典sql查询语句大全

- - SQL - 编程语言 - ITeye博客
3、说明:备份sql server. --- 创建 备份数据的 device. A:create table tab_new like tab_old (使用旧表创建新表). DB2中列加上后数据类型也不能改变,唯一能改变的是增加varchar类型的长度. 7、说明:添加主键: Alter table tabname add primary key(col).

P6SPY 2.0 发布,SQL 语句拦截

- - 开源中国社区最新新闻
8年过去了,P6SPY 发布了全新稳定版本 2.0 ,P6Spy是一个可以用来在应用程序中拦截和修改数据操作语句的开源框架. 通过P6Spy我们可以对SQL语句进行拦截,相当于一个SQL语句的记录器,这样我们可以用它来作相关的分析,比如性能分析. 下载地址: https://github.com/p6spy/p6spy/wiki/Download.

如何写出高性能SQL语句

- - Oracle - 数据库 - ITeye博客
优化SQL查询:如何写出高性能SQL语句. 1、首先要搞明白什么叫执行计划. 执行计划是数据库根据SQL语句和相关表的统计信息作出的一个查询方案,这个方案是由查询优化器自动分析产生欀如一条SQL语句如果用来从一个10万条记录的表中查1条记录,那查询优化器会选择“索引查找”方式,如果该表进行了归档,当前只剩下5000条记录了,那查询优化器就会改变方案,采用“全表扫描”方式.

如何使用动态SQL语句?

- - CSDN博客数据库推荐文章
这里只介绍动态SQL的使用. 关于动态SQL语句的语法,参见: http://blog.csdn.net/chiclewu/article/details/16097133. 1.什么是时候需要使用动态SQL. 例如,SELECT语句包含的标识符(如表名)在编译时是未知的,或者WHERE子句的条件数量在编译时是未知.

Oracle sql语句执行顺序

- - 数据库 - ITeye博客
sql语法的分析是从右到左. 一、sql语句的执行步骤:. 1)语法分析,分析语句的语法是否符合规范,衡量语句中各表达式的意义. 2)语义分析,检查语句中涉及的所有数据库对象是否存在,且用户有相应的权限. 3)视图转换,将涉及视图的查询语句转换为相应的对基表查询语句. 4)表达式转换, 将复杂的 SQL 表达式转换为较简单的等效连接表达式.