Oracle动态SQL之本地动态SQL的使用.docx

?

?

Oracle动态SQL之本地动态SQL的使用

?

?

论文导读：本地动态SQL在处理SQL语句时将其分为两类：一类是DDL语句、DCL语句、非查询的DML语句、单行查询的SELECT语句，这一类可使用EXECUTEIMMEIDIATE语句执行。

关键词：Oracle，数据库，PL/SQL开发动态SQL，本地动态SQL

?

引言

本地动态SQL在处理SQL语句时将其分为两类：一类是DDL语句、DCL语句、非查询的DML语句、单行查询的SELECT语句，这一类可使用EXECUTEIMMEIDIATE语句执行。第二类是多行查询的SELECT语句，这类语句需要通过游标来实现。

1、使用EXECUTEIMMEDIATE处理DDL、DCL、非多行查询的DML语句等动态SQL

使用EXECUTEIMMEDIATE来执行动态SQL是非常简单的，只需将要执行的动态SQL放到EXECUTEIMMEDIATE命令后即可。如下例：

declare

v_sqlvarchar2(1000);

begin

v_sql:=createtabletab_test(||namevarchar2(80)notnull,||

addrvarchar2(200),||birthdaydate);--DDL语句

executeimmediatev_sql;

v_sql:=grantselectontab_testtopublic;--DCL语句

executeimmediatev_sql;

v_sql:=insertintotab_test(name,addr)values(test1,test1addr);--DML语句

executeimmediatev_sql;

exceptionwhenothersthen

dbms_output.put_line(出现错误：||sqlerrm);

end;

执行完这段代码后，用SELECT查询一下新建的tab_test表结构及表中数据，结果如下：

SQLselect*fromtab_test;

NAMEADDRBIRTHDAY

test1test1addr

在这个例子中分别将三个简单的SQL语句赋值给一个字符串变量，然后通过EXECUTEIMMEDIATE命令来执行这个字符串中包含的SQL语句。

在实际的使用中，所使用到的DML语句往往不会如此简单，可能会包含入参、返回值等，这种情况就需要通过EXECUTEIMMEDIATE增加相应的子句来完成处理。完整的EXECUTEIMMEDIATE语句的格式如下：

EXECUTEIMMEDIATEsql_statement

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

[USING[IN|OUT|INOUT]bind_argument

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

[{RETURNING|RETURN}INTObind_argument[,bind_argument]...];

在这个完整的EXECUTEIMMEDIATE语句中，INTO子句表示在执行单行查询时将查询结果保存到指定的变量variable中；USING子句表示在执行的SQL语句中含有参数时使用bind_argument来替换参数；RETURNINGINTO子句表示在执行包含RETURNING子句的非查询DML语句时将返回值保存到bind_argument中。

下面通过几个例子来分别说明这几个子句的用法，首先看一个单行查询的例子。

declare

v_emp_tableemp%rowtype;

v_sqlvarchar2(1000);

begin

v_sql:=select*fromempwhereempno=:v_empno;

executeimmediatev_sqlintov_emp_tableusing7369;

dbms_output.put_line(emp_name=||v_emp_table.ename);

exceptionwhenothersthen

dbms_output.put_line(出现错误：||sqlerrm);

end;

在这个例子中使用了INTO子句和USING子句。INTO子句将查询的结果保存到变量v_emp_table中，同时在USING子句中用值7369替换SQL中的参数v_empno。需要注意的是，EXECUTEIMMEDIATE…INTO…只能用于返回单行结果的SELECT语句，如果查询结果有多行，必须使用游标的FETCH语句。

再看一个