Oracle 动态SQL

最新推荐文章于 2024-05-02 01:04:40 发布

知乐康康桑

最新推荐文章于 2024-05-02 01:04:40 发布

阅读量1k

点赞数 16

文章标签： oracle sql 数据库

本文链接：https://blog.csdn.net/weixin_46268997/article/details/135711035

版权

2.1 给动态语句传值(USING 子句)

2.2 从动态语句检索值(INTO子句)

2.3 传递并检索值 (INTO用在USING子句前)

2.4 获取返回结果（returning/return 子句）

2.4 动态调用例程

2.5 动态SQL操控游标

2.6 获得修改前的的数据

动态SQL是在运行时动态构建和执行的SQL语句。与静态SQL相比，动态SQL的主要优势是可以根据不同条件生成不同的查询语句，从而提供更灵活的查询能力。动态SQL在处理复杂查询逻辑、动态过滤条件和动态列选择等方面非常有用。Oracle 动态SQL有两种写法：用 DBMS_SQL 或 execute immediate。

一、动态SQL和静态SQL

1.1. 静态SQL

静态SQL通常用于完成可以确定的任务。在第一次运行时进行编译，而后续再次调用，则不再编译该过程。即一次编译，多次调用，使用的相同的执行计划。此种方式被称之为使用的是静态的SQL。

1.2. 动态SQL

动态SQL通常是用来根据不同的需求完成不同的任务。对于输入不同的参数，SQL在每次运行时需要事先对其编译。即多次调用则需要多次编译，此称之为动态SQL。

动态SQL语句通常存放在字符串变量中，且SQL语句可以包含占位符(使用冒号开头)。也可以直接将动态SQL紧跟在EXECUTE IMMEDIATE语句之后，如EXECUTE IMMEDIATE 'alter table emp enable row movement'

1.3. 两者的异同

静态SQL为直接嵌入到PL/SQL中的代码，而动态SQL在运行时，根据不同的情况产生不同的SQL语句。
静态SQL为在执行前编译，一次编译，多次运行。动态SQL同样在执行前编译，但每次执行需要重新编译。
静态SQL可以使用相同的执行计划，对于确定的任务而言，静态SQL更具有高效性。但缺乏灵活性
动态SQL使用了不同的执行计划，效率不如静态SQL，但能够解决复杂的问题。
动态SQL容易产生SQL注入，为数据库安全带来隐患。

1.4. 动态SQL语句的几种方法

a.使用EXECUTE IMMEDIATE语句

包括DDL语句，DCL语句，DML语句以及单行的SELECT 语句。该方法不能用于处理多行查询语句。

b.使用OPEN-FOR，FETCH和CLOSE语句

对于处理动态多行的查询操作，可以使用OPEN-FOR语句打开游标，使用FETCH语句循环提取数据，最终使用CLOSE语句关闭游标。

c.使用批量动态SQL

即在动态SQL中使用BULK子句，或使用游标变量时在fetch中使用BULK ，或在FORALL语句中使用BULK子句来实现。

d.使用系统提供的PL/SQL包DBMS_SQL来实现动态SQL。

二、动态SQL的语法

2.1 语法描述

into 保存SQL的执行结果,返回多个则使用bulk collect设置保存变量
using 为动态SQL的占位符设置内容,默认模式为IN模式
returning|return 使用效果相同,获得被影响的行数.通过bulk collect实现批量绑定,默认OUT模式

2.2 注意事项

a. EXECUTE IMMEDIATE执行DML时，不会提交该DML事务，需要使用显示提交(COMMIT)或作为EXECUTE IMMEDIATE自身的一部分。

b. EXECUTE IMMEDIATE执行DDL,DCL时会自动提交其执行的事务。

c. 对于多行结果集的查询，需要使用游标变量或批量动态SQL，或者使用临时表来实现。

d. 当执行SQL时，其尾部不需要使用分号，当执行PL/SQL 代码时，其尾部需要使用分号。

e. 动态SQL中的占位符以冒号开头，紧跟任意字母或数字表示。

三、举例演示

3.1 给动态语句传值(USING 子句)

使用冒号加占位符,如:1进行占位,并在随后使用using关键字替换占位符,注意,占位符的出现顺序与替换顺序保持一致.

declare
  l_depnam varchar2(20) := 'testing';
  l_loc    varchar2(10) := 'Dubai';
begin
  execute immediate 'insert into dept values  (:1, :2, :3)'
  using 50, l_depnam, l_loc;
  commit;
end;
/

3.2 从动态语句检索值(INTO子句)

使用into关键字获得SQL执行的返回结果字段,注意类型匹配

--将返回结果传递给变量
declare
  l_cnt    varchar2(20);
begin
  execute immediate 'select count(1) from emp'
  into l_cnt;
  dbms_output.put_line(l_cnt);
end;
/

--将返回结果传递给记录类型
declare
  type empdtlrec is record (empno  number(4),ename  varchar2(20),deptno  number(2));
  empdtl empdtlrec;
begin
  execute immediate 'select empno, ename, deptno '||'from emp where empno = 7934'
  into empdtl;
end;
/

3.3 传递并检索值 (INTO用在USING子句前)

--case1:
declare
  l_dept    pls_integer := 20;
  l_nam     varchar2(20);
  l_loc     varchar2(20);
begin
  execute immediate 'select dname, loc from dept where deptno = :1'
  into l_nam, l_loc
  using l_dept ;
end;
/

--case2: 创建一个包含多个部门ID的集合
DECLARE
  TYPE dept_id_list IS TABLE OF NUMBER;
  l_dept_ids dept_id_list := dept_id_list(10, 20, 30);
  l_sql VARCHAR2(200);
  l_result NUMBER;
BEGIN
  -- 动态生成查询语句
  l_sql := 'SELECT COUNT(*) FROM employees WHERE department_id IN (:1)';

  -- 执行动态SQL并获取结果
  EXECUTE IMMEDIATE l_sql INTO l_result USING l_dept_ids;

  -- 输出结果
  DBMS_OUTPUT.PUT_LINE('总共有 ' || l_result || ' 个员工在这些部门。');
END;
/

3.4 获取返回结果（returning/return 子句）

-- 批量绑定,设定输入参数
declare
  -- 嵌套表
  type empno_nested is table of emp.empno%type;
  -- 索引表
  type ename_index is table of emp.ename%type index by pls_integer;
  type job_index   is table of emp.job%type   index by pls_integer;
  type sal_index   is table of emp.sal%type   index by pls_integer;
  t_ename          ename_index;
  t_job            job_index;
  t_sal            sal_index;  
  v_sql            varchar2(2000);
  t_empno          empno_nested := empno_nested(7369,7566,7788); -- 要删除的员工编号
begin
  -- SQL,返回删除前的数据
  v_sql := 'delete from emp
             where empno = :eno 
         returning ename, job, sal into :ea, :ej, :es';
  -- 使用forall批量执行
  forall i in 1 .. t_empno.count
    execute immediate v_sql 
      using t_empno(i)
  returning bulk collect into t_ename,t_job, t_sal;
  
  -- 循环获得修改后的数据对象
  for i in 1 .. t_ename.count loop
    dbms_output.put_line('员工' || t_ename(i) || '职位' || t_job(i) || '删除前工资' || t_sal(i));
  end loop;
end;
/

3.5 动态调用例程

例程中用到的绑定变量参数必须指定参数类型.黓认为IN类型,其它类型必须显式指定(in out/out)

declare
  l_routin   varchar2(100) := 'gen2161.get_rowcnt';
  l_tblnam   varchar2(20) := 'emp';
  l_cnt      number;
  l_status   varchar2(200);
begin
  execute immediate 'begin ' || l_routin || '(:2, :3, :4); end;'
  using in l_tblnam, out l_cnt, in out l_status;

  if l_status != 'OK' then
     dbms_output.put_line('error');
  end if;
end;
/

3.6 动态SQL操控游标

语法：open 游标名称 for 动态SQL语句 [using 绑定变量,绑定变量,...]

-- 动态SQL操控游标
declare
  cur_emp    sys_refcursor;     -- 弱类型的游标
  r_emp      emp%rowtype;
  v_deptno   emp.deptno%type;   -- 查询部门编号
begin
  v_deptno := 10;
  -- 动态SQL打开游标
  open cur_emp for 'select * from emp where deptno = :dno'
  using v_deptno;
  -- 使用loop循环遍历游标数据
  loop
    fetch cur_emp into r_emp;
    exit when cur_emp%notfound;
    dbms_output.put_line('员工' || r_emp.ename || '的薪资为' || r_emp.sal);
  end loop;
  -- 关闭游标
  close cur_emp;
end;
/

3.7 获得修改前的的数据

在动态SQL中使用returning 字段 into :占位符方式将修改前的字段信息返回,随后执行动态SQL,并使用returning into 变量形式获得修改前的字段信息

-- 获得删除前的数据
declare
  v_sql          varchar2(2000);
  v_dname        dept.dname%type;
begin
  v_sql := 'delete from dept where rownum <= 1 
            returning dname into :1';
  execute immediate v_sql
  returning into v_dname;
  dbms_output.put_line('删除部门' || v_dname);
end;
/

参考链接：

https://blog.csdn.net/paul50060049/article/details/78434170

https://blog.csdn.net/leshami/article/details/6118010

知乐康康桑

关注

16
点赞
踩
19

收藏

觉得还不错? 一键收藏
0
评论
Oracle 动态SQL

动态SQL是在运行时动态构建和执行的SQL语句。与静态SQL相比，动态SQL的主要优势是可以根据不同条件生成不同的查询语句，从而提供更灵活的查询能力。动态SQL在处理复杂查询逻辑、动态过滤条件和动态列选择等方面非常有用。Oracle 动态SQL有两种写法：用 DBMS_SQL 或 execute immediate。
复制链接

扫一扫