ORACLE数据库使用动态SQL的方法

格式：doc
大小：32.50 KB
文档页数：5

ORACLE数据库使用动态SQL的方法

在以数据库为基础的应用程序开发中，有时不得不把SQL语句直接嵌入到某种高级语言中使用，利用高级语言的过程性结构来弥补SQL语言实现复杂应用方面的不足。动态SQL方法允许在程序运行过程中临时“组装”SQL语句，为嵌入式SQL提供了更大的灵活性和适用性。本文以业界广为使用的ORACLE数据库为基础，讨论了ORACLE数据库使用动态SQL的主要方法。

标签：ORCALE 嵌入式SQL 动态SQL

1 嵌入式SQL

结构化查询语言（SQL）是访问关系型数据库的通用语言，其执行特点是非过程化，即不用指明执行的具体方法和途径，而是简单地调用相应语句来直接取得结果即可。SQL语言既是一种用于查询和更新的交互式数据库语言，又是一种应用程序进行数据库访问时所采取的编程式数据库语言。

在编写访问数据库的程序时，必须从普通的编程语言开始（如C语言），再把SQL加入到程序中。所以，嵌入式SQL语言就是将SQL语句直接嵌入到程序的源代码中，与其他高级程序设计语言混合。专用的SQL预编译程序将嵌入的SQL语句转换为能被程序设计语言（如C语言）的编译器识别的函数调用。然后，所选编程语言的编译器编译源代码为可执行程序。各个数据库厂商都采用嵌入SQL语言，并且都符合ANSI/ISO的标准。

嵌入SQL执行的主要部分（以静态SQL为例）：

1.1 在程序中使用include SQLCA语句定义并描述了SQLCA 的结构。SQLCA用于应用程序和数据库之间的通讯，其中的SQLCODE返回SQL语句执行后的结果状态。

1.2 在BEGIN DECLARE SECTION和END DECLARE SECTION之间定义宿主变量。宿主变量可被SQL语句引用，也可以被编程语言语句引用，用于将程序中的数据通过SQL语句传给数据库管理器，或接收查询的结果。在SQL语句中，主变量前有“：”标志以示区别。

1.3 在每次访问数据库前必须做CONNECT操作以连接到某个数据库上。这时应该保证数据库实例已经启动。

1.4 执行数据库操作语句。它将所需数据查出，并将它放在宿主变量中。该语句返回一个结果或通过游标返回多个结果，也可以包含update、insert和delete语句。

1.5 最后断开数据库的连接。

2静态SQL和动态SQL

嵌入式SQL语句为编程提供了一定的灵活性，使用户可以在程序运行过程中根据实际需要输入子句中某些变量的值。这些SQL语句的共同特点是，语句中主变量的个数与数据类型在预编译时都是确定的，只有主变量的值是程序运行过程中动态输入的，这类嵌入式SQL语句为静态SQL语句。静态SQL在PL/SQL块中使用的SQL语句在编译时是明确的，执行的是确定对象。静态SQL语言，在编译时已经确定了引用的表和列。宿主变量不改变表和列信息。可以使用主变量改变查询参数值，但是不能用主变量代替表名或列名。

动态SQL是指在PL/SQL块编译时SQL语句是不确定的，如根据用户输入的参数的不同而执行不同的操作。编译程序对动态语句部分不进行处理，只是在程序运行时动态地创建语句、对语句进行语法分析并执行该语句。动态SQL语言不在编译时确定SQL的表和列，而是让程序在运行时提供，并将SQL语句文本传给DBMS执行，只在执行时才产生执行计划。动态SQL语句首先执行PREPARE语句要求DBMS分析、确认和优化语句，并为其生成执行计划。DBMS还设置SQLCODE以表明语句中发现的错误。当程序执行完“PREPARE”语句后，就可以用EXECUTE语句执行执行计划，并设置SQLCODE，以表明完成状态。

3ORACLE中使用动态SQL的方法

在ORACLE中使用动态SQL，共有四种方法：

3.1 使用EXECUTE IMMEDIATE命令实现

具体语法为：

EXEC SQL EXECUTE IMMEDIATE { :host_string | string_literal }；

其中，host_variable和string是存放完整T-SQL语句。比如下面的程序片段用于执行用户输入的任意合法SQL命令。

char dyn_stmt[132]；

for (；；)

{printf(“Enter SQL statement: “)；

gets(dyn_stmt)；

if (*dyn_stmt == ‘\0’)break；

EXEC SQL EXECUTE IMMEDIATE:dyn_stmt；}

EXECUTE IMMEDIATE命令的作用是：分析该语句的语法，然后执行该语句。适合于仅执行一次的语句。

3.2 支持语句包含输入宿主变量

这种方法支持语句包含输入宿主变量。首先做PREPARE操作，然后通过EXECUTE执行。语法为：

EXEC SQL PREPARE statement_name FROM { :host_string | string_literal }；

该语句接收含有SQL语句的宿主变量，并送到ORACLE，ORACLE编译语句并生成执行计划。在语句串中包含一个”？”表明参数，当执行语句时，ORACLE需要参数来替代这些“？”。PREPRARE执行的结果是DBMS用语句名标志准备后的语句。在执行SQL语句时，EXECUTE语句后面是这个语句名。EXECUTE语句的語法为：

EXECUTE语句名USING宿主变量| DESCRIPTOR描述符名

其作用是，请求ORACLE执行PREPARE语句准备好的语句。当要执行的动态语句中包含一或多个参数标志时，在EXECUTE语句必须为每一个参数提供值。这样的话，EXECUTE语句用宿主变量值逐一代替准备语句中的参数标志（“？”或其他占位符），从而，为动态执行语句提供了输入值。

使用主变量提供值，USING子句中的主变量数必须同动态语句中的参数标志数一致，而且每一个主变量的数据类型必须同相应参数所需的数据类型相一致。各主变量也可以有一个伴随主变量的指示符变量。当处理EXECUTE语句时，如果指示符变量包含一个负值，就把NULL值赋予相应的参数标志。除了使用主变量为参数提供值，也可以通过SQLDA提供值。

3.3查询的列数或宿主变量数在预编译时已确定，但数据库中的对象未定

此方法适用于查询的列数或输入宿主变量数在预编译时已确定，但是数据库中的对象，如表、列名等信息未确定的情况。这些对象名不能是宿主变量。因此，必须通过以下语句来完成：

PREPARE statement_name FROM { :host_string | string_literal}；

DECLARE cursor_name CURSOR FOR statement_name；

OPEN cursor_name [USING host_variable_list]；

FETCH cursor_name INTO host_variable_list；

CLOSE cursor_name；

如下面程序用此方法完成动态查询：

char select_stmt[132] =

“SELECT MGR，JOB FROM EMP WHERE SAL < :salary”；

EXEC SQL PREPARE sql_stmt FROM :select_stmt；

EXEC SQL DECLARE emp_cursor CURSOR FOR sql_stmt；

EXEC SQL OPEN emp_cursor USING :salary；

EXEC SQL FETCH emp_cursor INTO:mgr_number，:job_title；

EXEC SQL CLOSE emp_cursor；

3.4 查询的列数或者宿主变量的个数未确定

在预编译时，查询的列数或者宿主变量的个数还不能确定，因为不知道具体的返回个数，所以不能使用输出宿主变量。这是因为还不知道应该定义多少个宿主变量。这时就需要SQLDA 结构和DESCRIBE命令。SQLDA包含了动态查询的列描述信息。对于输入宿主变量，也可以使用SQLDA来完成不确定的参数说明。可以通过以下语句来完成：

EXEC SQL PREPARE statement_name FROM { :host_string | string_literal }；

EXEC SQL DECLARE cursor_name CURSOR FOR statement_name；

EXEC SQL DESCRIBE BIND VARIABLES FOR statement_name

INTO bind_descriptor_name；

EXEC SQL OPEN cursor_name

[USING DESCRIPTOR bind_descriptor_name]；

EXEC SQL DESCRIBE [SELECT LIST FOR] statement_name

INTO select_descriptor_name；

EXEC SQL FETCH cursor_name USING DESCRIPTOR

select_descriptor_name；

EXEC SQL CLOSE cursor_name；

在上述语句中，DESCRIBE SELECT LIST的作用是将PREPARE后的动态查询语句的列名、数据类型、长度等信息保存在SQLDA中。DESCRIBE BIND

VARIABLES检查PREPARE后的动态查询语句的每个占位符的名字、数据类型、长度等信息。并将它存放在SQLDA中，然后，使用SQLDA提示用户数据参数值。

3.5 ORACLE中动态SQL的混合使用方法

ORACLE中动态SQL的不同使用方法之间可以混用。如：在一个查询中，列的个数确定，但是查询中的占位符不确定，这时，可以结合方法3和方法4，即使用方法3的FETCH语句和方法4的OPEN语句，如：

EXEC SQL FETCH emp_cursor INTO host_variable_list；

反之，如果查询中占位符的个数确定，而列数不确定，则可以使用方法3的OPEN语句，如：

EXEC SQL OPEN cursor_name [USING host_variable_list]；

參考文献：

[1]《基于Oracle的高性能动态SQL程序开发》作者：冯杰.

[2]《在Oracle中执行动态SQL》作者:佚名.

MyBatis动态SQL,MyBatis批量插入(Oracle数据库)

动态 SQL

MyBatis 的一个强大的特性之一通常是它的动态 SQL 能力。如果你有使用 JDBC 或其他相似框架的经验,你就明白条件地串联 SQL 字符串在一起是多么的痛苦,确保不能忘了空格或在列表的最后省略逗号。动态 SQL 可以彻底处理这种痛苦。

通常使用动态 SQL 不可能是独立的一部分,MyBatis 当然使用一种强大的动态 SQL 语言来改进这种情形,这种语言可以被用在任意映射的 SQL 语句中。

动态 SQL 元素和使用 JSTL 或其他相似的基于 XML 的文本处理器相似。在 MyBatis 之前的版本中,有很多的元素需要来了解。MyBatis 3 大大提升了它们,现在用不到原先一半的元素就能工作了。MyBatis 采用功能强大的基于 OGNL 的表达式来消除其他元素。

choose (when, otherwise)

trim (where, set)

foreach

在动态 SQL 中所做的最通用的事情是包含部分 where 字句的条件。比如:

[html] view plaincopy

parameterType="Blog" resultType="Blog">

SELECT * FROM BLOG

WHERE state = ‘ACTIVE’

AND title like #{title}

这条语句会提供一个可选的文本查找功能。如果你没有传递 title,那么所有激活的博客都会被返回。但是如果你传递了 title,那么就会查找相近的 title(对于敏锐的检索,这中情况下你的参数值需要包含任意的遮掩或通配符)的博客。

假若我们想可选地搜索 title 和 author 呢?首先,要改变语句的名称让它有意义。然后简单加入另外的一个条件。

Oracle中动态SQL详解（EXECUTEIMMEDIATE）

Oracle中动态SQL详解(EXECUTE IMMEDIATE)

2017年05⽉02⽇ 18:35:48 阅读数：744 标签：更多

个⼈分类：

Oracle中动态SQL详解

1．静态SQLSQL与动态SQL

Oracle编译PL/SQL程序块分为两个种：其⼀为前期联编（early binding），即SQL语句在程序编译期间就已经确定，⼤多数的编译情况

属于这种类型；另外⼀种是后期联编（late binding），即SQL语句只有在运⾏阶段才能建⽴，例如当查询条件为⽤户输⼊时，那么Oracle的

SQL引擎就⽆法在编译期对该程序语句进⾏确定，只能在⽤户输⼊⼀定的查询条件后才能提交给SQL引擎进⾏处理。通常，静态SQL采⽤前

⼀种编译⽅式，⽽动态SQL采⽤后⼀种编译⽅式。

本⽂主要就动态SQL的开发进⾏讨论，并在最后给出⼀些实际开发的技巧。

2．动态SQL程序开发

理解了动态SQL编译的原理，也就掌握了其基本的开发思想。动态SQL既然是⼀种”不确定”的SQL，那其执⾏就有其相应的特点。

Oracle中提供了Execute immediate语句来执⾏动态SQL，语法如下：

Excute immediate 动态SQL语句 using 绑定参数列表 returning into 输出参数列表;

对这⼀语句作如下说明：

1)动态SQL是指DDL和不确定的DML（即带参数的DML）

2)绑定参数列表为输⼊参数列表，即其类型为in类型，在运⾏时刻与动态SQL语句中的参数（实际上占位符，可以理解为函数⾥⾯的形

式参数）进⾏绑定。

3)输出参数列表为动态SQL语句执⾏后返回的参数列表。

4)由于动态SQL是在运⾏时刻进⾏确定的，所以相对于静态⽽⾔，其更多的会损失⼀些系统性能来换取其灵活性。

为了更好的说明其开发的过程，下⾯列举⼀个实例：

设数据库的emp表，其数据为如下：

oracle中executeimmediate的使用

EXECUTE IMMEDIATE 是 Oracle 数据库的一个动态 SQL 语句执行命令。它允许在运行时动态地构建和执行 SQL 语句的字符串，而不需要预编译。

EXECUTEIMMEDIATE执行的SQL语句可以是任何有效的SQL语句，包括SELECT、INSERT、UPDATE、DELETE等语句。它可以使用变量和参数进行动态的值替换。使用EXECUTEIMMEDIATE可以在运行时根据不同的条件和需求构建不同的SQL语句，从而实现更大的灵活性和可重用性。以下是EXECUTEIMMEDIATE的一些常见用法：

1.动态执行SELECT语句

可以使用EXECUTEIMMEDIATE动态执行SELECT语句，并将结果存储在变量中。例如：

DECLARE

l_emp_id NUMBER := 100;

l_emp_name VARCHAR2(100);

BEGIN

EXECUTE IMMEDIATE 'SELECT emp_name INTO :1 FROM employees

WHERE emp_id = :2' INTO l_emp_name USING OUT l_emp_name,

l_emp_id;

DBMS_OUTPUT.PUT_LINE('Employee Name: ' ， l_emp_name);

END; 在上面的示例中，通过使用 USING OUT 子句将变量 l_emp_name 传递给 EXECUTE IMMEDIATE，并在动态 SQL 语句中使用该变量。使用 INTO

子句将结果存储在 l_emp_name 变量中。

2.动态执行DDL语句

EXECUTEIMMEDIATE还可以用于动态执行DDL（数据定义语言）语句，例如创建表、修改表结构等。例如：

DECLARE

l_table_name VARCHAR2(100);

ORACLE动态执行SQL语句

ORACLE动态执⾏SQL语句

Oracle 动态SQL有两种写法：⽤ DBMS_SQL 或 execute immediate，建议使⽤后者。试验步骤如下：

/*** DDL ***/

begin

EXECUTE IMMEDIATE 'drop table temp_1';

EXECUTE IMMEDIATE 'create table temp_1(name varchar2(8))';

end;

/*** DML ***/

declare

v_1 varchar2(8);

v_2 varchar2(10);

str varchar2(50);

begin

v_1:='测试⼈员';

v_2:='北京';

str := 'INSERT INTO test (name ,address) VALUES (:1, :2)';

EXECUTE IMMEDIATE str USING v_1, v_2;

commit;

end;

2.通过字符串拼接

str_sql:='sal';

str_sql:=' select '|| 'str_sql' ||' from emp ' ;

execute immediate str_sql ;

动态返回sal字段，注意字段必须加单引号，如果不加单引号则进⾏⽂本值处理。

在存储过程中使⽤动态查询使⽤into时⽤法。

str_column:='sal';--查询的列

str_Total:=0;--总和

str_sql:=' select '|| 'str_column' ||' from emp ' ;

execute immediate str_sql into str_Total;

2. 返回单条结果

declare

str varchar2(500);

c_1 varchar2(10);

下载提示

1、下载文档前请自行甄别文档内容的完整性，平台不提供额外的编辑、内容补充、找答案等附加服务。
2、"仅部分预览"的文档,不可在线预览部分如存在完整性等问题,可反馈申请退款(可完整预览的文档不适用该条件!)。
3、如文档侵犯您的权益，请联系客服反馈,我们会尽快为您处理(人工客服工作时间：9:00-18:30)。

ORACLE数据库使用动态SQL的方法

合集下载

MyBatis动态SQL,MyBatis批量插入(Oracle数据库)

Oracle中动态SQL详解（EXECUTEIMMEDIATE）

oracle中executeimmediate的使用

ORACLE动态执行SQL语句

文档推荐

最新文档