Oracle中执行动态SQL

一、概述

在一般的sql操作中,sql语句基本上都是固定的,如: 
SELECT t.empno,t.ename FROM scott.emp t WHERE t.deptno = 20; 
但有的时候,从应用的需要或程序的编写出发,都可能需要用到动态SQl,如: 
当 from 后的表 不确定时,或者where 后的条件不确定时,都需要用到动态SQL。

使用execute immediate语句可以处理包括ddl(create、alter和drop)、DCL(grant、revoke)、DML(insert、update、delete)以及单行select语句。

execute immediate语句:

execute immediate dynamic_string
      [into {define_variable[,define_variable]…|record}]
      [using [in|out|in out] bind_argument[,[in|out|in out]bind_argument]…]
      [{returning|return} into bind_argument[, bind_argument]…]
  • define_variable用于指定存放单行查询结果的变量;
  • using in bind_argument用于指定存放传递给动态sql值的变量,即在dynamic中存在占位符时使用;
  • using out bind_argument用于指定存放动态sql返回值的变量。

二、执行ddl、dcl语句

不能使用into和using子句。

begin

execute immediate 'create table ma_org(org_codevarchar2(20),org_name varchar2(254))';
execute immediate 'drop table ma_org';
end;

语句

begin
       execute immediate 'grant insert on ma_org to scott'
end;

三、处理dml语句

1、给动态语句传值(USING 子句)

如果dml语句包含占位符,那么在execute immediate语句之后必须要带有using子句;

declare
         orgcode varchar2(10);
         orgname varchar2(254);
    begin
         orgcode := 1200;
         execute immediate 'select org_name fromma_org
         where org_code = :X'
         into orgname
         using orgcode;
         dbms_output.put_line(orgname);
    end;

2、从动态语句检索值(INTO子句)

3、动态调用存储过程

declare
  l_routin   varchar2(100) := 'gen2161.get_rowcnt';
  l_tblnam   varchar2(20) := 'emp';
  l_cnt      number;
  l_status   varchar2(200);
begin
  execute immediate 'begin ' || l_routin || '(:2, :3, :4); end;'
  using in l_tblnam, out l_cnt, in out l_status;

  if l_status != 'OK' then
     dbms_output.put_line('error');
  end if;
end;

4、处理包含returing子句的DML语句

如果dml语句带有returning子句,那么在execute immediate语句之后必须带有returning into子句,并且此时只能处理作用的单行上的dml语句,如果dml语句作用在多行上,则必须使用bulk子句。

declare
         orgcode varchar2(10);
         orgname varchar2(254);
         rname varchar2(254);
    begin
         orgcode := '1200';
         orgname := '天津市分行';
         execute immediate 'update ma_org set org_name=:X
         where org_code = :Y returning org_name into :rname'
         using orgname, orgcode
         returning into rname;
         dbms_output.put_line(orgname);
    end;

5、在retuing into中使用bulk collect into

四、处理多行查询

oracle通过使用bulk collect into子句处理动态sql中的多行查询可以加快处理速度,从而提高应用程序的性能。当使用bulk子句时,集合类型可以是plsql所支持的索引表、嵌套表和varray,但集合元素必须使用sql数据类型。在oracle9i以后,有三种语句支持bulk子句,execute immediate,fetch和forall。

1、使用动态游标(游标变量)处理多行查询类动态sql语句。

DECLARE
    TYPE ref_cur IS REF CURSOR;
    rc ref_cur;
    emprow emp%ROWTYPE;
    v_sql VARCHAR2(100):= 'select * from emp where deptno = :x';   --动态执行的SQL语句
BEGIN
    OPEN rc FOR v_sql USING 30;   --打开游标,绑定执行的SQL语句,并传递参数
  LOOP
        FETCH rc INTO emprow;
        EXIT WHEN rc%NOTFOUND;
        dbms_output.put_line('name:'||emprow.ename||'  sal:'||emprow.sal);
    END LOOP;
    CLOSE rc;
END;

2、在execute immediate中使用bulk collect into

示例:

declare
         type org_table_type is table of ma_org%rowtype;
         org_table org_table_type;
         v_orgcode varchar2(20);
    begin
         v_orgcode := '%00%';
         execute immediate 'select * from ma_org where org_code like:v_orgcode' bulk collect into org_table
         using v_orgcode;
         for i in 1..org_table.count
             loop
                dbms_output.put_line(org_table(i).org_code||','||org_table(i).org_name);
             end loop;
    end;

3、在forall语句中使用bulk collect into语句

示例:

declare
         type type_org_code is table of ma_org.org_code%type;
         type type_org_name is table of ma_org.org_name%type;
         v_orgcode type_org_code;
         v_orgname type_org_name;
    begin
         v_orgcode := type_org_code('1100','1200');
         forall i in 1..v_orgcode.count
            execute immediate 'update ma_org set org_name = org_code||org_namewhere org_code = :p1 returning org_name into :p2'
            using v_orgcode(i)
            returning bulk collect into v_orgname;
         for i in v_orgname.first..v_orgname.last

            loop
               dbms_output.put_line(v_orgname(i));
         end loop;
    end;

到此这篇关于Oracle中执行动态SQL的文章就介绍到这了。希望对大家的学习有所帮助,也希望大家多多支持我们。

(0)

相关推荐

  • 基于Oracle的高性能动态SQL程序开发

    正在看的ORACLE教程是:基于Oracle的高性能动态SQL程序开发. 摘要:对动态SQL的程序开发进行了总结,并结合笔者实际开发经验给出若干开发技巧. 关键词:动态SQL,PL/SQL,高性能 1. 静态SQLSQL与动态SQL Oracle编译PL/SQL程序块分为两个种:其一为前期联编(early binding),即SQL语句在程序编译期间就已经确定,大多数的编译情况属于这种类型:另外一种是后期联编(late binding),即SQL语句只有在运行阶段才能建立,例如当查询条件为用户输

  • Oracle实现动态SQL的拼装要领

    虽说Oracle的动态SQL语句使用起来确实很方便,但是其拼装过程却太麻烦.尤其在拼装语句中涉及到date类型字段时,拼装时要加to_char先转换成字符,到了sql中又要使用to_date转成date类型和原字段再比较. 例如有这样一个SQL语句: select '========= and (t.created>=to_date('''||to_char(sysdate,'yyyy-mm-dd')||''',''yyyy-mm-dd'') AND t.created< to_date(''

  • oracle中动态SQL使用详细介绍

    1.静态SQLSQL与动态SQL Oracle编译PL/SQL程序块分为两个种:其一为前期联编(early binding),即SQL语句在程序编译期间就已经确定,大多数的编译情况属于这种类型:另外一种是后期联编(late binding),即SQL语句只有在运行阶段才能建立,例如当查询条件为用户输入时,那么Oracle的SQL引擎就无法在编译期对该程序语句进行确定,只能在用户输入一定的查询条件后才能提交给SQL引擎进行处理.通常,静态SQL采用前一种编译方式,而动态SQL采用后一种编译方式.

  • oracle+mybatis 使用动态Sql当插入字段不确定的情况下实现批量insert

    最近做项目遇到一个挺纠结的问题,由于业务的关系,DB的数据表无法确定,在使用过程中字段可能会增加,这样在insert时给我造成了很大的困扰. 先来看一下最终我是怎么实现的: <insert id="batchInsertLine" parameterType="HashMap"> <![CDATA[ INSERT INTO tg_fcst_lines(${lineColumn}) select result.*,sq_fcst_lines.next

  • 在Oracle PL/SQL中游标声明中表名动态变化的方法

    /*     小弟刚刚接触ORACLE存储过程,有一个问题向各位同行求教,小弟写了一个存储过程,其目的是接收一个参数作为表名,然后查询该表中的全部记录的某一个字段的内容导入到另一个表中.     (     tabname in varchar     )     is     v_servicesname tabname.服务类型%type; --这个变量就是用来存放所要取得的字段内容,但不知该如何定义     cursor curSort1 is select 服务类型 from tabna

  • Oracle中执行动态SQL

    一.概述 在一般的sql操作中,sql语句基本上都是固定的,如: SELECT t.empno,t.ename FROM scott.emp t WHERE t.deptno = 20; 但有的时候,从应用的需要或程序的编写出发,都可能需要用到动态SQl,如: 当 from 后的表 不确定时,或者where 后的条件不确定时,都需要用到动态SQL. 使用execute immediate语句可以处理包括ddl(create.alter和drop).DCL(grant.revoke).DML(in

  • 为什么ASP中执行动态SQL总报错误信息?提示语句语法错误

    问:为什么ASP中执行动态SQL总报错误信息?提示语句语法错误 答: 有时候写ASP用conn.execute(sql)查询.更新.插入Access数据库数据时,明明正确的语句却往往会显示sql语句错误,相当恼火,特进行了归纳,可适当为字段添加"["."]"解决: 复制代码 代码如下: 例1:select * from a  如出现错误,可改为:select * from [a]  例2:UPDATE [user] SET password = '"&am

  • SQL Server中执行动态SQL

    在Sql Server查询语句中使用变量表示表名.字段名等动态查询方式叫动态查询. 当需要根据外部输入的参数来决定要执行的SQL语句时,常常需要动态来构造SQL查询语句,用得比较多的地方就是分页存储过程和执行搜索查询的SQL语句. 一个比较通用的分页存储过程,可能需要传入表名,字段,过滤条件,排序等参数,而对于搜索的话,可能要根据搜索条件判断来动态执行SQL语句. 在SQL Server中有两种方式来执行动态SQL语句,分别是sp_executesql和exec. sp_executesql相对

  • MySQL 存储过程中执行动态SQL语句的方法

    drop PROCEDURE if exists my_procedure; create PROCEDURE my_procedure() BEGIN declare my_sqll varchar(500); set my_sqll='select * from aa_list'; set @ms=my_sqll; PREPARE s1 from @ms; EXECUTE s1; deallocate prepare s1; end;

  • MySQL存储过程中实现执行动态SQL语句的方法

    本文实例讲述了MySQL存储过程中实现执行动态SQL语句的方法.分享给大家供大家参考.具体实现方法如下: mysql> mysql> delimiter $$ mysql> mysql> CREATE PROCEDURE set_col_value -> (in_table VARCHAR(128), -> in_column VARCHAR(128), -> in_new_value VARCHAR(1000), -> in_where VARCHAR(4

  • Mybatis中的动态SQL语句解析

    这篇文章主要介绍了Mybatis中的动态SQL语句解析,文中通过示例代码介绍的非常详细,对大家的学习或者工作具有一定的参考学习价值,需要的朋友可以参考下 Mybatis中配置SQL有两种方式,一种是利用xml 方式进行配置,一种是利用注解进行配置. Mybatis使用注解配置SQL,但是由于配置功能受限,而且对于复杂的SQL而言可读性很差,所以很少使用. Mybatis常用xml配置的方式,使用xml的几个简单的元素,便能完成动态SQL的功能,大量的判断都可以在mybaties的映射xml里面配

  • 分享一下SQL Server执行动态SQL的正确方式

    SQL Server执行动态SQL的话,应该如何实现呢?下面就为您介绍SQL Server执行动态SQL两种正确方式,希望可以让您对SQL Server执行动态SQL有更深的了解 动态SQL:code that is executed dynamically.它一般是根据用户输入或外部条件动态组合的SQL语句块.动态SQL能灵活的发挥SQL强大的功能.方便的解决一些其它方法难以解决的问题.相信使用过动态SQL的人都能体会到它带来的便利,然而动态SQL有时候在执行性能(效率)上面不如静态SQL,而

  • MyBatis 执行动态 SQL语句详解

    大家基本上都知道如何使用 MyBatis 执行任意 SQL,使用方法很简单,例如在一个 XXMapper.xml 中: <select id="executeSql" resultType="map"> ${_parameter} </select> 你可以如下调用: sqlSession.selectList("executeSql", "select * from sysuser where enabled

随机推荐