ORACLE之 动态 SQL 语句

动态SQL语句

静态 SQL

静态 SQL 是指直接嵌入在 PL/SQL 块中的 SQL 语句。

 

动态 SQL

动态 SQL 是指在运行 PL/SQL 块时动态输入的 SQL 语句并将 SQL 语句存放在字符串变量中，而且 SQL 居于可以包含占位符（以冒号开始）。

 

静态 SQL 和 动态 SQL 的比较

静态 SQL 是在编写 PL/SQL 块时直接嵌入的 SQL 语句；而动态 SQL 是在运行 PL/SQL 块时动态输入的 SQL 语句。
静态 SQL 性能要优于动态 SQL，因此当编写 PL/SQL 块时，如果功能完全确定，则使用静态 SQL；如果不能够确定要执行的 SQL 语句，则使用动态 SQL。

 

动态 SQL 的处理方法

1、使用 EXECUTE IMMEDIATE 语句
execute immediate 语句可以处理多数动态 SQL 操作，包括 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 ]... ];

处理无占位符和 returning 子句的 DML 语句（不需要带有 using 和 into 子句）

declare
  sql_str varchar2(100);
begin
  sql_str := 'update emp set sal=sal*1.1 where deptno=30';
  execute immediate sql_str;
end;

处理包含 returning 子句的 DML 语句

declare
  salary number(6,2);
  sql_str varchar2(100);
begin
  sql_str := 'update emp set sal=sal*(1+:percent/100)' || ' where empno=:eno returning sal into :salary';
  execute immediate sql_str using &1,&2 returning into salary;
  dbms_output.put_line('新工资：'||salary);
end;

 

2、使用 OPEN-FOR、FETCH 和 CLOSE 语句
为了处理动态的多行查询操作，必须要使用 open-for 语句打开游标，使用 fetch 语句循环提取数据，最终使用 close 语句关闭游标。

3、使用批量动态 SQL
通过使用批量动态 SQL，可以加快 SQL 语句处理，进而提高 PL/SQL 程序的性能。

 

动态 SQL 中使用 BULK 子句

通过使用 BULK 子句，可以增加批量数据的处理速度，从而提高应用程序的性能。当使用 BULK 子句时，实际是动态 SQL 语句将变量绑定为集合元素。
当使用 BULK 子句时，集合类型必须使用 SQL 数据类型（例如： number、char 等等），而不能使用 PL/SQL 数据类型（例如：binary_integer、boolean 等）。
支持三种 BULK 子句：
1、在 EXECUTE IMMEDIATE 中使用动态 BULK 子句
语法：
execute immediate dynamic_string
[ bulk collect into define_variable [, define_variable ]...  ]
[ using bind_argument [, bind_argument... ] ]
[ { returning | return }
bulk collect into return_variable [, return_variable... ] ];

使用 BULK 子句处理多行查询

declare
  type ename_table_type is table of emp.ename%type index by binary_integer;
  ename_table ename_table_type;
  sql_str varchar2(100);
begin
  sql_str:='select ename from emp where deptno=:depno';
  execute immediate sql_str bulk collect into ename_table using &dno;
  for i in 1..ename_table.count loop
    dbms_output.put_line(ename_table(i));
  end loop;
end;

 

2、在 FETCH 中使用 BULK 子句
语法：
fetch dynamic_cursor
bulk collect into define_variable[, define_variable ... ];

示例：

declare
  type empcurtype is ref cursor;
  emp_cv empcurtype;
  type ename_table_type is table of emp.ename%type index by binary_integer;
  ename_table ename_table_type;
  sql_str varchar2(100);
begin
  sql_str := 'select ename from emp where job=:title';
  open emp_cv for sql_str using '&job';
  fetch emp_cv bulk collect into ename_table;
  for i in 1..ename_table.count loop
    dbms_output.put_line(ename_table(i));
  end loop;
  close emp_cv;
end;

 

3、在 FORALL 中使用 BUKL 子句
使用 FORALL 语句可允许在动态 SQL 语句中输入变量同时提供多个数据，但 FORALL 语句只适用于动态的 INSERT、UPDATE 和 DELETE 语句而不适用于动态的 SELECT 语句；并且 FORALL 语句和 EXECUTE IMMEDIATE 语句是结合使用的。
语法：
forall index in lower bound..upper bound
execute immediate dynamic_string
using bind_agrument | bind_agrument( index ) [, bind_agrument | bind_agrument( index ) ]...
[ { returning | return } bulk collect into bind_agrument [, bind_agrument... ] ];

修改多个雇员工资并返回新工资

declare
  type ename_table_type is table of emp.ename%type ;
  type sal_table_type is table of emp.sal%type;  
  ename_table ename_table_type;
  sal_table sal_table_type;
  sql_str varchar2(100);
begin
  ename_table := ename_table_type('SCOTT', 'SMITH', 'CLARK');
  sql_str := 'update emp set sal=sal*1.1 where ename=:1 returning sal into :2';
  forall i in 1..ename_table.count execute immediate sql_str using ename_table(i) returning bulk collect into sal_table;
  for j in 1..ename_table.count loop
    dbms_output.put_line('Empolyeer'||ename_table(j)||'''s new salary is: '||sal_table(j));
  end loop;
end;