Oracle数据库 动态SQL

一、什么是动态SQL

一般的PL/SQL程序设计中，在DML和事务控制的语句中可以直接使用SQL，但是DDL语句及系统控制语句却不能在PL/SQL中直接使用，要想实现在PL/SQL中使用DDL语句及系统控制语句，可以通过使用动态SQL来实现。
在Oracle数据库开发PL/SQL块中我们使用SQL分为静态SQL和动态SQL。
静态SQL指在PL/SQL块中使用的SQL语句在编译时是明确的，执行的是确定对象。
例：

select * from emp where emp=7788;

动态SQL是指在PL/SQL块编译时SQL语句是不确定的，比如根据用户输入参数的不同而执行不同的操作。
例：

select * from emp where empno='&员工编号';

动态SQL就是将SQL语句写在一个字符串中，在存储过程中解析字符串执行SQL。

二、动态SQL的作用

1. 支持DDL语句，而静态SQL只能支持DML语句；
2. 支持web引用程序的查询意愿( 一个网络应用程序的常见需求是用户可以指定他们想看到的列, 以及改变数据的排序方式 )；
3. 可以将业务逻辑先放在表中，然后再动态编译

三、动态SQL语句的五种实现方法

1. 使用 execute immediate 语句
   可以执行DDL语句、DCL语句、DML语句以及单行select语句。但这个方法不能用于处理多行查询语句。
2. &参数输入
3. 使用游标（open-for, fetch,close语句）
   是对于处理动态多行的查询操作，使用open-for语句打开游标，fetch语句循环获取数据，最后使用close语句关闭游标。
4. 使用批量动态SQL
   即在动态SQL中使用BULK子句，或使用游标变量时在fetch中使用BULK ，或在FORALL语句中使用BULK子句来实现。
5. 使用系统提供的PL/SQL包 DBMS_SQL 实现
   此处重点讲述第一种，其它三种带过

四、动态SQL的语法结构

execute immediate 动态语句字符串
[into 变量列表]
[using 参数列表]

解释：
动态语句字符串：存储指定的SQL语句或者PL/SQL块的字符串变量
into：用于存储查询结果
using：参数传递值
动态SQL传参数的格式：[:参数名]，参数在运行时需要使用using传值

五、动态SQL的写法

写法1：不传参不赋值

--拷贝emp表
begin
  execute immediate 'create table test1 as select * from emp';	
  --字符串语句最后不需要加分号
end;
--创建表
begin
  execute immediate 'create table temp_table2 ' || '( id integer ,name varchar2(20))';
end;

写法2：将结果集存在变量中动态运行

--拷贝emp表
declare
  sqls varchar2(100) := 'create table test1_emp as select * from emp';
begin
  execute immediate sqls;
end;

写法3：动态SQL传参和赋值

using 传参
into 赋值
参数格式 [:参数]

--案例：根据员工编号查询员工薪资
declare
  v_sal emp.sal%type;
begin
  --执行动态SQL
  execute immediate 'select sal from emp where empno=:员工编号'
  --变量赋值
  into v_sal
  --接收参数
  using &请输入员工编号;
  dbms_output.put_line('工资：' || v_sal);
end;

写法4：动态SQL只赋值不传参

--案例：根据员工编号查询员工薪资
declare
  v_sal emp.sal%type;
begin
  --执行动态SQL
  execute immediate 'select sal from emp where empno=7788'
  --变量赋值
  into v_sal;
  dbms_output.put_line('工资：' || v_sal);
end;

六、动态SQL与存储过程的运用

案例1：删除表（通过传递表名对相应的表进行删除）

-- 创建一个存储过程，通过传递表名对相应的表进行删除
create or replace procedure truncate_table(table_name in varchar2)
is
sqls varchar2(100);
begin
  sqls := 'truncate table ' || table_name; --为变量赋值，用于生成动态的SQL语句
  execute immediate sqls;
end;
-- 调用存储过程删除test111表
begin
  truncate_table('test111');
end;

案例2：创建表（根据用户输入的表名及字段名等参数创建表）

--创建表:根据用户输入的表名及字段名等参数创建表
create or replace procedure create_table(
table_name in varchar2,----表名
field1 in varchar2,---字段1
field1type in varchar2,---字段1的数据类型
field2 in varchar2,---字段2
field2type in varchar2,---字段2的数据类型
field3 in varchar2,---字段3
field3type in varchar2---字段3的数据类型 最后一个参数后面不加逗号
)
is 
sqls varchar2(500);
begin
  sqls := 'create table' || ' ' || table_name || '(' || field1 || ' ' || field1type || ',' || field2 || ' ' || field2type ||',' || field3 ||' ' || field3type || ')';
  -- 即：create table table_name (field1 field1type,field2 field2type,field3 fieldtype)
  execute immediate sqls;
end;
--调用存储过程创建表
begin
create_table('test_table','id','varchar2(100)','sno','varchar2(100)','sname','varchar2(100)');
end;

案例3：插入数据（根据用户输入的字段数据向指定表插入数据）

--插入数据
create or replace procedure insert_table(
id in varchar2,
name in varchar2,
age in varchar2
)
is
sqls varchar2(500);
begin
  sqls:='insert into test_table values(:1,:2,:3)';
  execute immediate sqls using id,name,age;
end;
--调用存储过程insert_table插入数据
begin
  insert_table('1','小红','18');
end;