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

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

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

引言

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

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

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

declare

v_sqlvarchar2(1000);

begin

v_sql:= 'create table tab_test('||'name varchar2(80) not null,'||

'addrvarchar2(200),'||'birthday date)'; --DDL语句

executeimmediate v_sql;

v_sql:= 'grant select on tab_test to public'; --DCL语句

executeimmediate v_sql;

v_sql:= 'insert into tab_test(name, addr) values(''test1'',''test1 addr'')'; --DML语句

executeimmediate v_sql;

exception when others then

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

end;

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

SQL> select * fromtab_test;

NAMEADDR BIRTHDAY

-------- ---------------------------------

test1test1 addr

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

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

EXECUTE IMMEDIATE sql_statement

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

[USING [IN | OUT | IN OUT] bind_argument

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

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

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

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

declare

v_emp_tableemp%rowtype;

v_sqlvarchar2(1000);

begin

v_sql:= 'select * from emp where empno=:v_empno';

executeimmediate v_sql into v_emp_table using '7369';

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

exception when others then

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

end;

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

再看一个使用RETURNING INTO子句的例子。

declare

v_enameemp.ename%type;

v_sqlvarchar2(1000);

begin

v_sql:= 'update emp set ename=''Davis'' where empno=:v_empno returning ename into:in_ename';

executeimmediate v_sql using '7369' returning into v_ename;

dbms_output.put_line('emp_name='||v_ename);

exception when others then

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

end;

在这个例子使用了RETURNINGINTO子句和USING子句。需要注意的是，在这个例子中，EXECUTEIMMEDIATE语句中取返回值使用的是RETURNING INTO子句，而单行查询中使用的是INTO子句，两者在EXECUTE IMMEDIATE语句中的位置也有所不同。

2、使用FETCH游标来处理多行查询语句的动态SQL。

使用FETCH游标处理多行查询语句分为三个步骤：第一步打开游标，第二步循环获取每一行数据，第三步关闭游标。具体使用过程看下面的例子：

set serverout on;

declare

typecursor_type_def is ref cursor;

v_cursor_acursor_type_def;

v_emp_tableemp%rowtype;

v_sqlvarchar2(1000);

v_jobemp.job%type := 'CLERK';

begin

v_sql:= 'select * from emp where job=:in_job ';

openv_cursor_a for v_sql using v_job;

loop

fetchv_cursor_a into v_emp_table ;

exitwhen v_cursor_a%notfound;

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

endloop;

closev_cursor_a;

exception when others then

closev_cursor_a;

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

end;

在这个例子中首先定义了一个游标类型cursor_type_def，然后定义了一个cursor_type_def类型的变量v_cursor_a，在PL/SQL程序块中使用OPEN语句将v_cursor_a游标变量关联到SQL语句，然后在一个循环中使用FETCH语句取出每一行数据，并在每一次提取数据后判断是否取到了数据，如果没有就退出循环，并使用CLOSE语句关闭游标。需要注意：使用OPEN打开游标时，关联的SQL语句必须是SELECT语句，不能是其它DML语句。

在使用FETCH游标查询多行数据时，也可以不使用参数。如果不使用参数，只需要在OPEN语句中将USING子句去掉即可。另外，在使用FETCH获取每一行查询结果时，除了可以将“select * ”的结果赋给一个数据行变量外(如果SELECT语句查询指定列则只能赋给普通SQL变量)，还可以将其结果赋给一个或多个普通SQL变量，但保存查询结果的变量个数不能超过查询结果的列数，并且保存结果的变量的顺序要与表结构顺序一致。

结语

该文通过对本地动态SQL的实现原理、使用方法和一些技巧的介绍，希望能够对正在从事或准备从事这方面开发工作的同行有所帮助。限于知识水平和表达能力，对于文中的一些知识和技巧的理解、说明可能会存在错误和偏颇，希望您能不吝指正并提出宝贵的建议。

本文实验环境：

Windows XP Profession SP3+ Oracle9.2.0.8.0

参考文献：

[1]Scott Urman. Oracle8PL/SQL程序设计. 机械工业出版社

[2]William G.Page.Jr . ORACLE8/8I 开发使用手册. 机械工业出版社