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;

  • 7
    点赞
  • 108
    收藏
    觉得还不错? 一键收藏
  • 0
    评论

“相关推荐”对你有帮助么?

  • 非常没帮助
  • 没帮助
  • 一般
  • 有帮助
  • 非常有帮助
提交
评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值