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子句)

image

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

image

 

四、处理多行查询

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的文章就介绍到这了。希望对大家的学习有所帮助，也希望大家多多支持编程宝库。

 一、概述OLAP的系统(即Online Aanalyse Process)一般用于系统决策使用。通常和数据仓库、数据分析、数据挖掘等概念联系在一起。这些系统的特点是数据量大，对实时响应的要求不高或者 ...