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

1、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处

2、理DDL、DCL、非多行查询的DML语句等动态SQL使用EXECUTE IMMEDIATE来执行动态SQL是非常简单的，只需将要执行的动态SQL放到EXECUTE IMMEDIATE命令后即可。如下例：declarev_sqlvarchar2(1000);beginv_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;

3、-DCL语句executeimmediate v_sql;v_sql:= insert into tab_test(name, addr) values(test1,test1 addr); -DML语句executeimmediate v_sql;exception when others thendbms_output.put_line(出现错误：|sqlerrm);end;执行完这段代码后，用SELECT查询一下新建的tab_test表结构及表中数据，结果如下：SQL select * fromtab_test;NAMEADDR BIRTHDAY- -test1test1 addr在这个

4、例子中分别将三个简单的SQL语句赋值给一个字符串变量，然后通过EXECUTE IMMEDIATE命令来执行这个字符串中包含的SQL语句。在实际的使用中，所使用到的DML语句往往不会如此简单，可能会包含入参、返回值等，这种情况就需要通过EXECUTE IMMEDIATE增加相应的子句来完成处理。完整的EXECUTEIMMEDIATE语句的格式如下：EXECUTE IMMEDIATE sql_statementINTO variable ,variable . |recordUSING IN | OUT | IN OUT bind_argument,IN | OUT | IN OUT bind_a

5、rgument. RETURNING |RETURN INTO bind_argument ,bind_argument.;在这个完整的EXECUTE IMMEDIATE语句中，INTO子句表示在执行单行查询时将查询结果保存到指定的变量variable中；USING子句表示在执行的SQL语句中含有参数时使用bind_argument来替换参数；RETURNING INTO子句表示在执行包含RETURNING子句的非查询DML语句时将返回值保存到bind_argument中。下面通过几个例子来分别说明这几个子句的用法，首先看一个单行查询的例子。declarev_emp_tableemp%rowt

6、ype;v_sqlvarchar2(1000);beginv_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 thendbms_output.put_line(出现错误：|sqlerrm);end;在这个例子中使用了INTO子句和USING子句。INTO子句将查询的结果保存到变量v_emp_table中，同时在

7、USING子句中用值 7369替换SQL中的参数v_empno。需要注意的是，EXECUTE IMMEDIATEINTO只能用于返回单行结果的SELECT语句，如果查询结果有多行，必须使用游标的FETCH语句。再看一个使用RETURNING INTO子句的例子。declarev_enameemp.ename%type;v_sqlvarchar2(1000);beginv_sql:= update emp set ename=Davis where empno=:v_empno returning ename into:in_ename;executeimmediate v_sql using

8、7369 returning into v_ename;dbms_output.put_line(emp_name=|v_ename);exception when others thendbms_output.put_line(出现错误：|sqlerrm);end;在这个例子使用了RETURNINGINTO子句和USING子句。需要注意的是，在这个例子中，EXECUTEIMMEDIATE语句中取返回值使用的是RETURNING INTO子句，而单行查询中使用的是INTO子句，两者在EXECUTE IMMEDIATE语句中的位置也有所不同。2、使用FETCH游标来处理多行查询语句的动态SQL。

9、使用FETCH游标处理多行查询语句分为三个步骤：第一步打开游标，第二步循环获取每一行数据，第三步关闭游标。具体使用过程看下面的例子：set serverout on;declaretypecursor_type_def is ref cursor;v_cursor_acursor_type_def;v_emp_tableemp%rowtype;v_sqlvarchar2(1000);v_jobemp.job%type := CLERK;beginv_sql:= select * from emp where job=:in_job ;openv_cursor_a for v_sql using

10、 v_job;loopfetchv_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 thenclosev_cursor_a;dbms_output.put_line(出现错误：|sqlerrm);end;在这个例子中首先定义了一个游标类型cursor_type_def，然后定义了一个cursor_type_def类型的变量v_cursor_a

11、，在PL/SQL程序块中使用OPEN语句将v_cursor_a游标变量关联到SQL语句，然后在一个循环中使用FETCH语句取出每一行数据，并在每一次提取数据后判断是否取到了数据，如果没有就退出循环，并使用CLOSE语句关闭游标。需要注意：使用OPEN打开游标时，关联的SQL语句必须是SELECT语句，不能是其它DML语句。在使用FETCH游标查询多行数据时，也可以不使用参数。如果不使用参数，只需要在OPEN语句中将USING子句去掉即可。另外，在使用FETCH获取每一行查询结果时，除了可以将“select * ”的结果赋给一个数据行变量外(如果SELECT语句查询指定列则只能赋给普通SQL变量)，还可以将其结果赋给一个或多个普通SQL变量，但保存查询结果的变量个数不能超过查询结果的列数，并且保存结果的变量的顺序要与表结构顺序一致。结语该文通过对本地动态SQL的实现原理、使用方法和一些技巧的介绍，希望能够对正在从事或准备从事这方面开发工作的同行有所帮助。限于知识水平和表达能力，对于文中的一