Oracle数据库之动态SQL

时间:2022-09-14 07:39:52

Oracle数据库之动态SQL

1. 静态SQLSQL与动态SQL

Oracle编译PL/SQL程序块分为两个种:一种为前期联编(early binding),即SQL语句在程序编译期间就已经确定,大多数的编译情况属于这种类型;另外一种是后期联编(late binding),即SQL语句只有在运行阶段才能建立,例如当查询条件为用户输入时,那么Oracle的SQL引擎就无法在编译期对该程序语句进行确定,只能在用户输入一定的查询条件后才能提交给SQL引擎进行处理。通常,静态SQL采用前一种编译方式,而动态SQL采用后一种编译方式。

所谓静态SQL指在PL/SQL块中使用的SQL语句在编译时是明确的,执行的是确定对象。而动态SQL是指在PL/SQL块编译时SQL语句是不确定的,如根据用户输入的参数的不同而执行不同的操作。编译程序对动态语句部分不进行处理,只是在程序运行时动态地创建语句、对语句进行语法分析并执行该语句。

2. 动态SQL介绍

Oracle数据库有两种动态SQL技术:使用DBMS_SQL包和本地动态SQL。本地动态SQL是在Oracle 8i之后引入的一种新的执行动态SQL的机制,与使用DBMS_SQL包相比,它更简单、运行速度更快、性能更高,所以DBMS_SQL包的方法已经很少用了。下面主要介绍的是本地动态SQL。

动态SQL语句是在运行时由程序创建的字符串,它们必须是有效的SQL语句或PL/SQL块,也可以包含用于数据绑定的占位符。占位符是未声明的标识符,名称并不重要,只需以冒号开头。如:

'DELETE FROM dept WHERE id = :1 RETURNING loc INTO :2'

'SELECT name, salary FROM employee WHERE salary > :s'

一般在下列的情况下我们才需要使用动态SQL:

  1. 在PL/SQL块中执行数据定义语句,数据控制语句或会话控制语句(如ALTER SESSION),因为在PL/SQL中,这样的语句是不允许静态执行的。
  2. 为了获取更多的灵活性。例如,想在运行时根据实际需求来为SELECT语句的WHERE子句选择不同的schema对象。
  3. 动态地使用包DBMS_SQL执行SQL语句,但是为了获得更好的性能,或是DBMS_SQL不支持的功能。

通常有三种执行不同类型的动态SQL方法:

  1. 使用EXECUTE IMMEDIATE语句。 
    除不能处理多行查询语句,其他的动态SQL包括DDL语句,DCL语句以及单行的SELECT查询都可以。
  2. REF CURSOR动态游标,使用OPEN-FOR,FETCH,CLOSE。 
    能处理动态的多行查询操作,必须要使用OPEN-FOR语句打开游标,使用FETCH语句循环提取数据,最终使用CLOSE语句关闭游标。
  3. 使用批量BULK COLLECT执行动态SQL。 
    通过使用批量动态SQL语句,可以加快SQL语句处理,进而提高PL/SQL的性能。

3. 使用EXECUTE IMMEDIATE语句

语法:

EXECUTE IMMEDIATE dynamic_sql_stmt
[ { into_clause | bulk_collect_into_clause } [ using_clause ]
| using_clause [ dynamic_returning_clause ]
| dynamic_returning_clause
] ;

说明:

dynamic_sql_stmt:是代表一条SQL语句或一个PL/SQL块的字符串表达式。

into_clause:用于存放被选出的字段值的变量或被选出的行记录。格式如:

INTO { variable [, variable ]... | record )

using_clause:SQL或PL/SQL字符串中包括用于参数绑定的占位符时,该子句为占位符绑定值,也可用于返回值。输入bind_argument参数是一个表达式,它的值将被输入(IN模式)或输出(OUT模式)或输入输出(IN OUT模式)到动态SQL语句或是PL/SQL块中。一个输出bind_argument参数就是一个能保存动态SQL返回值的变量。格式如:

USING [ IN | OUT | IN OUT ] bind_argument
[ [,] [ [ IN | OUT | IN OUT ] bind_argument ]...

dynamic_returning_clause:指明用于存放返回值的变量或记录。格式如:

{ RETURNING | RETURN } { into_clause | bulk_collect_into_clause }

注意:

  1. 可以把所有的绑定参数放到USING子句中,默认的参数模式是IN。对于含有RETURNING子句的DML语句来说,我们可以把OUT参数放到RETURNING INTO之后,并且不用指定它们的参数模式,因为默认就是OUT。如果我们既使用了USING又使用RETURNING INTO,那么,USING子句中就只能包含IN模式的参数了。
  2. 每个占位符必须与USING子句和/或RETURNING INTO子句中的一个绑定参数对应。
  3. 可以使用数字、字符和字符串作为绑定参数,但不能使用布尔类型(TRUE,FALSE和NULL)。
  4. 动态SQL是不支持PL/SQL特有的类型,所以不能使用布尔型或索引表。

示例1:

CREATE OR REPLACE PROCEDURE proc_test
(
table_name IN VARCHAR2, -- 表名
field1 IN VARCHAR2, -- 字段名1
datatype1 IN VARCHAR2, -- 字段类型1
field2 IN VARCHAR2, -- 字段名2
datatype2 IN VARCHAR2 -- 字段类型2
) AS
str_sql VARCHAR2(200);
BEGIN
str_sql := 'CREATE TABLE '||table_name||'('||field1||' '||datatype1||','||field2||' '||datatype2||')';
EXECUTE IMMEDIATE str_sql;
EXCEPTION
WHEN others THEN
DBMS_OUTPUT.PUT_LINE(SQLCODE || ' - ' || SQLERRM);
END proc_test;

示例2,使用USING子句:

CREATE OR REPLACE PROCEDURE proc_insert
(
id IN NUMBER,
name IN VARCHAR2
) AS
str_sql VARCHAR2(200);
BEGIN
str_sql := 'INSERT INTO dinya_test VALUES (:1,:2)';
EXECUTE IMMEDIATE str_sql USING id, name;
EXCEPTION
WHEN others THEN
DBMS_OUTPUT.PUT_LINE(SQLCODE || ' - ' || SQLERRM);
END proc_insert;

示例3,USING向后兼容:

DECLARE
str_sql VARCHAR2(200);
v_eid NUMBER(4) := 1;
v_ename VARCHAR2(20);
v_address VARCHAR2(100);
v_salary NUMBER(10, 4) := 5500.0000;
BEGIN
str_sql := 'UPDATE emp SET salary = :1 WHERE id = :2 RETURNING name, address INTO :3, :4'; EXECUTE IMMEDIATE str_sql
USING v_salary, v_eid, OUT v_ename, OUT v_address; EXECUTE IMMEDIATE str_sql
USING v_salary, v_eid
RETURNING INTO v_ename, v_address;
...
END;

当动态INSERT、UPDATE或DELETE语句有一个RETURNING子句时,输出绑定参数可以放到RETURNING INTO或USING子句的后面。

示例4,使用RETURNING INTO子句:

DECLARE
p_id NUMBER := 1;
v_count NUMBER;
v_string VARCHAR2(200);
BEGIN
v_string := 'SELECT COUNT(*) FROM table_name t WHERE t.id=:id';
EXECUTE IMMEDIATE v_string USING p_id RETURNING INTO v_count;
END;

4. REF CURSOR动态游标,使用OPEN-FOR,FETCH,CLOSE

语法结构如下:

Oracle数据库之动态SQL

using_clause ::=

Oracle数据库之动态SQL

说明:

cursor_variable是一个弱类型(没有返回类型)的游标变量。

dynamic_string是字符串表达式,代表一个多行查询。

在运行时,USING子句中的绑定变量可以替换动态SELECT语句中相对应的占位符。

示例:

DECLARE
TYPE empcurtyp IS REF CURSOR; emp_cv empcurtyp;
v_ename VARCHAR2(15);
v_sal NUMBER := 1000;
BEGIN
OPEN emp_cv FOR
'SELECT name, salary FROM employee WHERE salary > :s'
USING v_sal; LOOP
FETCH emp_cv INTO v_ename, v_sal;
EXIT WHEN emp_cv%NOTFOUND;
DBMS_OUTPUT.PUT_LINE('姓名:' || v_ename || ',工资:' || v_sal);
END LOOP; CLOSE emp_cv;
END;

5. 批量动态SQL

批量绑定能减少PL/SQL和SQL引擎之间的切换,改善性能。批量绑定能让Oracle把SQL语句中的一个变量与一个集合相绑定。集合类型可以是任何PL/SQL集合类型(索引表、嵌套表或变长数组)。但是,集合元素必须是SQL数据类型,如VARCHAR2、DATE或NUMBER。有三种语句支持动态批量绑定:EXECUTE IMMEDIATE、FETCH和FORALL。

5.1 EXECUTE IMMEDIATE批量

示例1:

DECLARE
TYPE ename_table_type IS TABLE OF employee.name%TYPE INDEX BY BINARY_INTEGER;
ename_table ENAME_TABLE_TYPE;
v_sql VARCHAR2(100);
BEGIN
v_sql := 'SELECT name FROM employee WHERE did = :v_deptno';
EXECUTE IMMEDIATE v_sql BULK COLLECT INTO ename_table USING &v_deptno; FOR i IN ename_table.FIRST .. ename_table.LAST LOOP
dbms_output.put_line(ename_table(i));
END LOOP;
END;

示例2:

DECLARE
TYPE ename_table_type IS TABLE OF employee.name%TYPE INDEX BY BINARY_INTEGER;
TYPE sal_table_type IS TABLE OF employee.salary%TYPE INDEX BY BINARY_INTEGER;
ename_table ENAME_TABLE_TYPE;
sal_table SAL_TABLE_TYPE;
v_sql VARCHAR2(200);
BEGIN
v_sql := 'UPDATE employee SET salary = salary*(1 + :percent / 100)' ||
' where did=:dno' ||
' RETURING ename,sal INTO :name,:salary';
EXECUTE IMMEDIATE v_sql
USING &percent,&dno
RETURNING BULK COLLECT INTO ename_table, sal_table; FOR i IN ename_table.FIRST .. ename_table.LAST LOOP
DBMS_OUTPUT.PUT_LINE('姓名:' || ename_table(i) || ',新工资:' || sal_table(i));
END LOOP;
END;

5.2 FETCH批量

示例:

DECLARE
TYPE emp_cur_type IS REF CURSOR;
TYPE num_list IS TABLE OF NUMBER;
TYPE name_list IS TABLE OF VARCHAR2(50); emp_cur emp_cur_type;
emp_nums num_list;
enames name_list;
sals num_list;
BEGIN
OPEN emp_cur FOR 'SELECT id, name FROM employee';
FETCH emp_cur
BULK COLLECT INTO emp_nums, enames;
CLOSE emp_cur; EXECUTE IMMEDIATE 'SELECT salary FROM employee'
BULK COLLECT INTO sals;
END;

5.3 FORALL批量

示例:

DECLARE
TYPE num_list IS TABLE OF NUMBER;
TYPE name_list IS TABLE OF VARCHAR2(50);
emp_nums num_list;
enames name_list;
BEGIN
emp_nums := num_list(1, 2, 3, 4, 5);
FORALL i IN emp_nums.FIRST .. emp_nums.LAST
EXECUTE IMMEDIATE 'UPDATE employee SET salary = salary * 1.1 WHERE id = :1 ' ||
'RETURNING name INTO :2'
USING emp_nums(i)
RETURNING BULK COLLECT INTO enames;
...
END;

Oracle数据库之动态SQL的更多相关文章

  1. oracle 存储过程 调用动态sql

      oracle 存储过程 调用动态sql CreationTime--2018年8月16日11点25分 Author:Marydon 1.错误实现方式 --开始时间拼接' 00:00:00' V_S ...

  2. 【PL/SQL系列】Oracle存储过程使用动态SQL

    Oracle存储过程相信大家都比较了解,下面就为您介绍Oracle存储过程使用动态SQL的方法,希望对您能够有所帮助. CREATE OR REPLACE PROCEDURE P_STAT_SCORE ...

  3. Oracle数据库之PL/SQL触发器

    Oracle数据库之PL/SQL触发器 1. 介绍 触发器(trigger)是数据库提供给程序员和数据分析员来保证数据完整性的一种方法,它是与表事件相关的特殊的存储过程,它的执行不是由程序调用,也不是 ...

  4. Oracle数据库之PL/SQL包

    Oracle数据库之PL/SQL包 1. 简介 包(PACKAGE)是一种数据对象,它是一组相关过程.函数.变量.常量和游标等PL/SQL程序设计元素的组合,作为一个完整的单元存储在数据库中,用名称来 ...

  5. Oracle数据库之PL/SQL过程与函数

    Oracle数据库之PL/SQL过程与函数 PL/SQL块分为匿名块与命名块,命名块又包含子程序.包和触发器. 过程和函数统称为PL/SQL子程序,我们可以将商业逻辑.企业规则写成过程或函数保存到数据 ...

  6. Oracle数据库之PL/SQL异常处理

    Oracle数据库之PL/SQL异常处理 异常指的是在程序运行过程中发生的异常事件,通常是由硬件问题或者程序设计问题所导致的. PL/SQL程序设计过程中,即使是写得最好的程序也可能会遇到错误或未预料 ...

  7. Oracle数据库之PL/SQL流程控制语句

    Oracle数据库之PL/SQL流程控制语句 在任何计算机编程语言(如C,Java,C#等)都有各种流程控制语句,同样,在PL/SQL中也存在这样的流程控制结构. 几种常见的流程控制结构: 一.条件结 ...

  8. 在Oracle中执行动态SQL的几种方法

    转载:在Oracle中执行动态SQL的几种方法 以下为内容留存: 在Oracle中执行动态SQL的几种方法 在一般的sql操作中,sql语句基本上都是固定的,如:SELECT t.empno,t.en ...

  9. oracle数据库查询日期sql语句(范例)、向已经建好的表格中添加一列属性并向该列添加数值、删除某一列的数据(一整列)

    先列上我的数据库表格: c_date(Date格式)     date_type(String格式) 2011-01-01                   0 2012-03-07         ...

随机推荐

  1. MySQL中的外键是什么、有什么作用

    本文参加博文大赛,如果您满意的话麻烦点击这里给我投票原,查看原文点击这里.最近自学数据库MySQL,然后有个疑问,一直不得其解,查询了相关资料,最后还是没有解决. 我的疑问是 "使用外键约束 ...

  2. 百度Couldn't load BaiduMapSDK_v2_4_1 from loader dalvik

    原文链接:http://liuzhichao.com/p/1463.html 在使用百度定位SDK的时候,明明已经加入了liblocSDK3.so,Manifest中也添加了相应权限,注册了com.b ...

  3. android 自定义按钮实现 home键 和返回键

    由于在自己做的东西中用到了就总结一下,自己做了测试 在一个程序运行中如果按 返回键  分别执行了 : onpause()     onStop()   onDestory()方法 如果点击 home键 ...

  4. Unity 技能按钮触发特效

    unity 版本:4.5.1 NGUI版本:3.6.5 首先,要导入特效资源包,导入应该是基本中的基础,但是对于初学者来说好像很少有这方面的介绍,也许是我现学现用书看的不够认真,因为导入这个问题卡了好 ...

  5. Django 执行单独脚本及SyntaxError缩进报错解决

    有时候会碰到这样的场景,对于一些业务升级,我需要把数据库数据做些处理,同时又想以 Django 项目的环境变量执行脚本,这个时候使用 python 脚本是再适合不过的手段了. 注意:在pycharm里 ...

  6. alibaba dubbo admin的安装

    一.下载地址 https://github.com/apache/incubator-dubbo-admin 然后把项目作为maven项目 前端部分 使用Vue.js作为javascript框架,Vu ...

  7. CentOS 7 配置HTTPS加密访问SVN

    上一篇文章已经介绍了如何在CentOS7环境下安装SVN并整合HTTP访问 http://www.cnblogs.com/fjping0606/p/7581093.html 那么本文则介绍如何添加HT ...

  8. linux下安装mysql等信息

    1.安装 apt-get update;// 第一次的时候,你更新一下你的软件包的源地址数据; apt-get install mysql-server 2.账号登陆 mysql -h localho ...

  9. Servlet_Struts2

    百度云链接:https://pan.baidu.com/s/1TNkQ8KN2t1xJFcf_CnTXDQ 密码:i3w8 修改中...

  10. Thinkphp5 多图上传

    html代码 <div class="content" id="content_list"> <!-- 上传部分 --> <for ...