很多时候我们需要在存储过程中使用动态的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;

发表回复

您的电子邮箱地址不会被公开。