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的作用
支持DDL语句，而静态SQL只能支持DML语句；
支持web引用程序的查询意愿( 一个网络应用程序的常见需求是用户可以指定他们想看到的列, 以及改变数据的排序方式 )；
可以将业务逻辑先放在表中，然后再动态编译
三、动态SQL语句的五种实现方法
使用 execute immediate 语句
可以执行DDL语句、DCL语句、DML语句以及单行select语句。但这个方法不能用于处理多行查询语句。
&参数输入
使用游标（open-for, fetch,close语句）
是对于处理动态多行的查询操作，使用open-for语句打开游标，fetch语句循环获取数据，最后使用close语句关闭游标。
使用批量动态SQL
即在动态SQL中使用BULK子句，或使用游标变量时在fetch中使用BULK ，或在FORALL语句中使用BULK子句来实现。
使用系统提供的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;

————————————————
版权声明：本文为CSDN博主「是小曹同学」的原创文章，遵循CC 4.0 BY-SA版权协议，转载请附上原文出处链接及本声明。
原文链接：https://blog.csdn.net/cwx2352265318/article/details/120995835

Oracle数据库 动态SQL

相关文章

归档