PL/SQL中的动态SQL - 道行尚浅的道场

动态SQL的概念

本质上来说动态SQL就是将符合SQL语法的字符串当作SQL语句来运行.

动态SQL相对于静态SQL来说更灵活.并且可以突破一些PL/SQL对静态SQL的限制.

由于在编译时SQL的全文还不确定，所以你可使用动态SQL构建灵活的，多用途的应用程序。

动态SQL的分类

对于DML,DDL,SELECT INTO 语句我们使用EXECUTE IMMEDIATE 语句来执行;

对于多行查询我们使用OPEN cursor FOR 语句来执行

EXECUTE IMMEDIATE 语句

例程:打印表中记录数

DECLARE
  TAB_ROW_CNT NUMBER;
BEGIN
  FOR R IN (SELECT TABLE_NAME FROM USER_TABLES where rownum <= 5)
   LOOP
    execute immediate 'SELECT COUNT(*) FROM ' || R.TABLE_NAME
      INTO TAB_ROW_CNT;
    DBMS_OUTPUT.put_line(R.TABLE_NAME || ':' || TAB_ROW_CNT);
  END LOOP;
END;

执行结果

DOMAIN_NAME:225884
TEST_CONNECT_CYCLE:6
TEST_FOJ_B:2
TEST_FOJ_A:2
TEST_INSERT_DEPT:4
PL/SQL procedure successfully completed

OPEN cursor_name FOR 语句

只能打开REF CURSOR 类型的游标
后面多跟随多行查询语句

declare
  type ref_cursor is ref cursor;
  c1 ref_cursor;
  r1 emp%rowtype;
begin
  open c1 for 'select * from emp ';
  loop
    fetch c1
      into r1;
     dbms_output.put_line(r1.ename||' '||r1.sal);
    exit when c1%notfound;
  end loop;
end;

执行结果

SMITH 800
ALLEN 1600
WARD 1250
JONES 2975
MARTIN 1250
BLAKE 2850
CLARK 2450
SCOTT 3000
KING 5000
TURNER 1500
ADAMS 1100
JAMES 950
FORD 3000
MILLER 1300
MILLER 1300
PL/SQL procedure successfully completed

绑定变量

执行一下语句

declare
  v1 number := 10;
  v2 number := 20;
begin
  execute immediate 'select/* dao */ count(*) from emp where deptno=' || v1;
  execute immediate 'select/* dao */ count(*) from emp where deptno=' || v2;
end;

查询V$SQL


select sql_id,sql_text
 from v$sql
where sql_text like '%dao%'

执行结果:

SQL_ID        SQL_TEXT
------------- --------------------------------------------------------------------------------
8yz8usu65h27s declare   v1 number := 10;   v2 number := 20; begin   execute immediate 'select/
8h22vrrdn546k select/* dao */ count(*) from emp where deptno=10
47tbn5whrjnpp select/* dao */ count(*) from emp where deptno=20
8ztfnk3pf6pp7 select sql_id,sql_text  from v$sql where sql_text like '%dao%'

通过上面的例子我们可以发现,意义类似的语句由于传入的部门编号不同,被Oracle定义为两个不同的SQL语句,拥有不同的SQL_ID.

在SHARED POOL中两个SQL各占一份空间,游标不能被共享,会造成硬解析.

当这种SQL被大量调用,会产生大量的硬解析,产生latch争用,占用大量CPU等一系列问题.

Oracle 建议使用绑定变量来避免上述问题.

 alter system flush shared_pool ;

 declare
  v1 number := 10;
  v2 number := 20;
begin
  execute immediate 'select/* dao */ count(*) from emp where deptno=' || v1;
  execute immediate 'select/* dao */ count(*) from emp where deptno=' || v2;
end;

查询V$SQL


select sql_id,sql_text
 from v$sql
where sql_text like '%dao%'

执行结果:

SQL_ID        SQL_TEXT
------------- --------------------------------------------------------------------------------
5s7cf3yw5kfh7 select/* dao */ count(*) from emp where deptno= :b1
8ztfnk3pf6pp7 select sql_id,sql_text  from v$sql where sql_text like '%dao%'
bsgk84htrg15g declare   v1 number := 10;   v2 number := 20; begin   execute immediate 'select/