很多时候我们需要在存储过程中使用动态的SQL，要怎么用好动态SQL呢，怎样执行效率最高呢。下面来介绍一下，如何使用动态SQL操作数据库。
在存储过程中执行一个简单的SQL语句使用下面的方法即可。
DECLARE
BEGIN
/* 使用该方法来创建一个新数据表，但这方法需要当前用户具有创建表的权限。 */
EXECUTE IMMEDIATE ‘create table testSQLU (data1 number, data2 number)’;
END;
/
DECLARE
BEGIN
/* 使用这个方法删除一个已存在的数据表，需要删除权限。 */
EXECUTE IMMEDIATE ‘drop table testSQLU’;
END;
/
SQL文中不能对数据表名使用变量替换方法，则如需动态填入表名，实现方法如下。
create table : testSQLU … 是不被允许的。
DECLARE
tbl1 VARCHAR2(40);
sql1 VARCHAR2(2000);
BEGIN
tbl1 := ‘testSQLU’;
sql1 := ‘create table ‘ || tbl1 || ‘ (data1 number, data2 number)’;
EXECUTE IMMEDIATE sql1;
END;
/
使用变数替换带入数值的方式实现方法如下列代码。
DECLARE
sql1 VARCHAR2(2000);
BEGIN
sql1 := ‘insert into testSQLU values (:data1, :data2)’;
EXECUTE IMMEDIATE sql1 USING 100, 200;
END;
/
对于带入的数值，可以使用其他的变量代替传入。
如下代码使用变量带入参数。
DECLARE
data1 NUMBER;
data2 NUMBER;
sql1 VARCHAR2(2000);
BEGIN
data1 := 100;
data2 := 200;
sql1 := ‘insert into testSQLU values (:data1, :data2)’;
EXECUTE IMMEDIATE sql1 USING data1, data2;
END;
/
执行结果可以被取得，但如果不是返回单行记录时，会报出错误信息。
SET serveroutput ON;
DECLARE
test_rec testSQLU%ROWTYPE;
sql1 VARCHAR2(2000);
BEGIN
sql1 := ‘select * from testSQLU where field1 = :data1’;
EXECUTE IMMEDIATE sql1 INTO test_rec USING 100;
dbms_output.put_line (‘– 取得数据 –‘);
dbms_output.put_line (test_rec.field1);
dbms_output.put_line (test_rec.field2);
END;
/
如果返回结果为多行数据，则应使用open for 和 fetch方法。
SET serveroutput ON;
/* 抽出一列数据的时候 */
DECLARE
TYPE cutype IS REF CURSOR;
cv cutype;
item1 testm.key1%TYPE;
BEGIN
OPEN cv FOR
‘select key1 from testSQLU where key1 like :data1’
USING ‘a%’;
LOOP
FETCH cv INTO item1;
EXIT WHEN cv%notfound;
dbms_output.put_line (item1);
END LOOP;
CLOSE cv;
END;
/
SET serveroutput ON;
/* 整行数据抽出时 */
DECLARE
TYPE cutype IS REF CURSOR;
cv cutype;
rec testm%ROWTYPE; /*行模型*/
BEGIN
OPEN cv FOR
‘select * from testSQLU where rec like :data1’
USING ‘a%’;
LOOP
FETCH cv INTO rec;
EXIT WHEN cv%notfound;
dbms_output.put_line (rec.field1);
END LOOP;
CLOSE cv;
END;
更新SQL执行结果也可以被取得。
codingset serveroutput ON;
DECLARE
sql1 VARCHAR2(2000);
ret1 NUMBER;
BEGIN
sql1 := ‘update testSQLU set data2 = :data2 where data1 = :data1
returning data2 into :ret1’;
EXECUTE IMMEDIATE sql1 USING 11, 100 RETURNING INTO ret1;
dbms_output.put_line (‘– 取得数据 –‘);
dbms_output.put_line (ret1);
END;
