05、动态执行DDL语句

PL/SQL动态执行DDL语句

PL/SQL程序中可以执行DML语句和事物控制等语句，如经常用到select into进行但赋值语句，但是直接执行DDL语句（create table 等操作）是不可以的，但是可以通过动态SQL语句执行，间接到达执行DDL操作的目的。

把SQL语句当做字符串的形式传给动态SQL执行语句执行。
语句结构：

EXECUTE IMMEDIATE 动态SQL语句
[ into 变量列表 ]
[ using 参数列表]

语法解析：

如果动态SQL语句是 SELECT 语句，可以把查询的结果保存到 INTO 后面的变量中。如果动态语句中存在参数， USING 为SQL语句中的参数传值。动态 SQL 中的参数格式是： [:参数名]，参数在运行时需要使用 USING 传值。下面我们通过案例代码分析动态SQL语句的写法。

例1、利用动态语句创建学生信息表的备份表（stuinfo_201812）:

declare
sql_yj varchar(500); --动态sql执行的语句
begin
sql_yj:='create table STUINFO_202010
                (
              stuid      VARCHAR2(11) ,
              stuname    VARCHAR2(50) ,
              sex        CHAR(1) ,
              age        NUMBER(2) ,
              classno    VARCHAR2(7) ,
              stuaddress VARCHAR2(100),
              grade      CHAR(4) ,
              enroldate  DATE,
              idnumber   VARCHAR2(18) 
                )';
                 --利用动态语句创建学生备份表（stuinfo_201812）
execute immediate sql_yj;
end;

例2、给备份表插入一个学生信息，代码如下：

declare
sql_yj varchar(500);--动态SQL执行的语句
ls_stuid VARCHAR2(11);
ls_stuname VARCHAR2(50);
ls_sex CHAR(1);
ls_age NUMBER(2);
begin
 --查询出学生信息表中学生"张三丰"的基本信息
 select t.stuid,t.stuname,t.sex,t.age 
 into ls_stuid,ls_stuname,ls_sex,ls_age 
 from stuinfo t where t.stuid='SC201801006';
 --利用动态语句执行插入操作，插入“张三丰”的信息
 sql_yj:='insert into stuinfo_201812 values(:1,:2,:3,:4,null,null,null,null,null)';
  execute immediate sql_yj using ls_stuid,ls_stuname,ls_sex,ls_age;
end;

例3、利用动态SQL语句查询出刚刚插入的学生信息：

declare
sql_yj varchar(500);--动态SQL执行的语句
ls_stuinfo stuinfo%rowtype;
ls_stuid VARCHAR2(11);
ls_stuname VARCHAR2(50);
ls_sex CHAR(1);
ls_age NUMBER(2);
begin
 --查询出学生信息表中学生"张三丰"的基本信息
 select t.stuid,t.stuname,t.sex,t.age 
 into ls_stuid,ls_stuname,ls_sex,ls_age 
 from stuinfo t 
 where t.stuid='SC201801006';
  --利用动态语句查询获取"张三丰"的信息
 sql_yj:='select * from stuinfo_201812 where stuid=:1';
  execute immediate sql_yj into ls_stuinfo using ls_stuid;
  dbms_output.put_line('学号：'||ls_stuinfo.stuid||'姓名：'
  ||ls_stuinfo.stuname||'性别：'||ls_stuinfo.sex||'年龄：'||ls_stuinfo.age);
end;

PL/SQL动态执行SQL语句，是先进行解析编译后，执行后再传入参数进行执行，因此，动态SQL有一个优势就是绑定变量，只需一次解析，假如是一个SQL操作where条件后的值经常变换，而且经常用到，可以考虑使用Oracle动态执行SQL。因为，Oracle中SQL语句是通过SGA共享池进行缓存的，下次再次执行该SQL语句，直接从缓存当中取出，提高执行效率，减少Oracle数据库负担，不然，随着每次变量值的不同，Oracle把他当作不同的SQL语句，进行再次预解析，会大大加大数据库负担。