PL/SQL动态执行DDL语句

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

PL/SQL程序是通过PL/SQL执行时,把SQL语句当做字符串的形式传给动态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_201812
            (
              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;

结果如下:

PL/SQL动态执行DDL语句


案例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;

结果如下:

PL/SQL动态执行DDL语句


案例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动态执行DDL语句

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