动态 SQL、EXECUTE IMMEDIATE、using、into、returning

来源：互联网发布：c语言随机数生成的代码编辑：程序博客网时间：2024/05/18 12:32

很多时候我们需要在存储过程中使用动态的SQL，要怎么用好动态SQL呢，怎样执行效率最高呢。下面来介绍一下，如何使用动态SQL操作数据库。

在存储过程中执行一个简单的SQL语句使用下面的方法即可。

DECLAREBEGIN  /* 使用该方法来创建一个新数据表，但这方法需要当前用户具有创建表的权限。 */  EXECUTE IMMEDIATE 'create table test (data1 number, data2 number)'; END;/

DECLAREBEGIN  /* 使用这个方法删除一个已存在的数据表，需要删除权限。 */  EXECUTE IMMEDIATE 'drop table test'; END;/

SQL文中不能对数据表名使用变量替换方法，则如需动态填入表名，实现方法如下。
create table :tbl1 ... 是不被允许的。

DECLARE  tbl1 VARCHAR2(40);  sql1 VARCHAR2(2000);BEGIN   tbl1 := 'test';  sql1 := 'create table ' || tbl1 || ' (data1 number, data2 number)';  EXECUTE IMMEDIATE sql1; END;/

使用变数替换带入数值的方式实现方法如下列代码。

DECLARE  sql1 VARCHAR2(2000);BEGIN   sql1 := 'insert into test 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 test values (:data1, :data2)';  EXECUTE IMMEDIATE sql1 USING data1, data2; END;/

执行结果可以被取得，但如果不是返回单行记录是，会报出错误信息。

SET serveroutput ON; DECLARE  test_rec test%ROWTYPE;  sql1 VARCHAR2(2000);BEGIN   sql1 := 'select * from test 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 testm 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 テストm%ROWTYPE;  /*行模型*/BEGIN   OPEN cv FOR    'select * from テストm where キー like :data1'    USING 'a%';   LOOP    FETCH cv INTO rec;    EXIT WHEN cv%notfound;    dbms_output.put_line (rec.キー);  END LOOP;   CLOSE cv; END;

更新SQL执行结果也可以被取得。

  codingset serveroutput ON; DECLARE  sql1 VARCHAR2(2000);  ret1 NUMBER;BEGIN   sql1 := 'update test 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;

0 0