ORALCE DBMS_SQL的使用

  DBMS_SQL包提供一个接口，用于执行动态SQL（包括DDL 和DML）。

  DBMS_SQL定义了一个实体叫游标ID，游标ID 是一个PL/SQL整型数，通过游标ID，可以对游标进行操作。

DBMS_SQL包和本地动态SQL在功能上有许多重叠的地方，但是有的功能只能通过本地动态SQL实现，而有些功能只能通过DBMS_SQL实现。

 

对于一般的select操作,如果使用动态的sql语句则需要进行以下几个步骤:

open   cursor---> parse---> define   column---> excute---> fetch   rows---> close   cursor;

而对于 dml操作(insert,update) 则需要进行以下几个步骤:

open   cursor---> parse---> bind   variable---> execute---> close   cursor;

对于 delete 操作只需要进行以下几个步骤:

open   cursor---> parse---> execute---> close   cursor;

利用DBMS_SQL执行DDL语句：

  CREATE OR REPLACE PROCEDURE CreateTable2 (tablename VARCHAR2)

IS

SQL_string VARCHAR2(1000);--存放SQL语句

V_cur integer;--定义整形变量，用于存放游标

BEGIN

SQL_string := 'CREATE TABLE ' || tablename || '(name VARCHAR(20))';

V_cur := dbms_sql.open_cursor;--打开游标

dbms_sql.parse(V_cur,SQL_string,DBMS_SQL.NATIVE);--解析并执行SQL语句

dbms_sql.close_cursor(V_cur);--关闭游标

END;

利用DBMS_SQL执行SELECT语句：

open   cursor---> parse---> define   column---> excute---> fetch   rows---> close   cursor;

    DECLARE

  v_cursor NUMBER;--游标ID

  sqlstring VARCHAR2(200);--用于存放SQL语句

  v_phone_name   VARCHAR2(20);--手机名字

  v_producer   VARCHAR2(20);--手机生产商

  v_price   NUMBER := 500;--手机价钱

  v_count   INT;--在这里无意义，只是存放函数返回值

BEGIN

  --:p是占位符

  --SELECT 语句中的第1列是phone_name,第2列是producer ,第3列是price

  sqlstring :='SELECT phone_name,producer,price FROM phone_infor WHERE price > :p';

  v_cursor := dbms_sql.open_cursor;--打开游标；

  dbms_sql.parse(v_cursor ,sqlstring ,dbms_sql.native);--解析动态SQL语句；

 

  --绑定输入参数，v_price的值传给 :p

  dbms_sql.bind_variable(v_cursor ,':p',v_price);

 

        --定义列，v_phone_name对应SELECT 语句中的第1列

  dbms_sql.define_column(v_cursor,1,v_phone_name,20);

  --定义列，v_producer对应SELECT语句中的第2列

  dbms_sql.define_column(v_cursor,2,v_producer,20);

  --定义列，v_price对应SELECT语句中的第3列

  dbms_sql.define_column(v_cursor,3,v_price);

   

  v_count := dbms_sql.EXECUTE(v_cursor); --执行动态SQL语句。

   

  LOOP

  --从游标中把数据检索到缓存区（BUFFER）中，缓冲区的值只能被函数COULUMN_VALUE()所读取

  EXIT WHEN dbms_sql.fetch_rows(v_cursor)<=0;

  --函数 column_value()把缓冲区的列的值读入相应变量中。

  --第1列的值被读入v_phone_name中

  dbms_sql.column_value(v_cursor,1,v_phone_name);

  --第2列的值被读入v_producer中

  dbms_sql.column_value(v_cursor,2,v_producer);

  --第2列的值被读入v_price中

  dbms_sql.column_value(v_cursor,3,v_price);

  --打印变量的值

  dbms_output.put_line(v_phone_name || ' '|| v_producer|| ' '||v_price);

 

 END LOOP;

  dbms_sql.close_cursor(v_cursor);--关闭游标

  END;

利用DBMS_SQL执行DML语句：

open   cursor---> parse---> bind   variable---> execute---> close   cursor;

  DECLARE

  v_cursor NUMBER;--游标ID

  sqlstring VARCHAR2(200);--用于存放SQL语句

  v_phone_name   VARCHAR2(20);--手机名字

  v_producer   VARCHAR2(20);--手机生产商

  v_price   NUMBER := 500;--手机价钱

  v_count   INT;--被DML语句影响的行数

BEGIN

 

  sqlstring :=' INSERT INTO phone_infor values (:a,:b,:c)';-- :a,:b,:c 是占位符

  v_phone_name   := 'S123';

  v_producer   := '索尼AA';

  v_price   := 999;

  v_cursor := dbms_sql.open_cursor;--打开游标；

 dbms_sql.parse(v_cursor ,sqlstring ,dbms_sql.native);--解析动态SQL语句；

 

  --绑定输入参数，v_price的值传给 :p

 dbms_sql.bind_variable(v_cursor ,':a',v_phone_name);

  dbms_sql.bind_variable(v_cursor ,':b',v_producer);

  dbms_sql.bind_variable(v_cursor ,':c',v_price);

  v_count := dbms_sql.EXECUTE(v_cursor); --执行动态SQL语句。

   

  dbms_sql.close_cursor(v_cursor);--关闭游标

  dbms_output.put_line(' INSERT ' || to_char( v_count) ||' row ');--打印有多少行被插入

  COMMIT;

  END;

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

Execution Flow

1. OPEN_CURSOR

2. PARSE

3. BIND_VARIABLE or BIND_ARRAY

4. DEFINE_COLUMN, DEFINE_COLUMN_LONG, or DEFINE_ARRAY

5. EXECUTE

6. FETCH_ROWS or EXECUTE_AND_FETCH

7. VARIABLE_VALUE, COLUMN_VALUE, or COLUMN_VALUE_LONG

8. CLOSE_CURSOR

minutes.

The execution flow of DBMS_SQL is shown in Figure 100-1.

Figure 100-1 DBMS_SQL Execution Flow

Description of "Figure 100-1 DBMS_SQL Execution Flow"

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

在某些场合下，存储过程或触发器里的SQL语句需要动态生成。Oracle的DBMS_SQL包可以用来执行动态SQL语句。本文通过一个简单的例子来展示如何利用DBMS_SQL包执行动态SQL语句：

 

DECLARE        v_cursor NUMBER;        v_stat NUMBER;        v_row NUMBER;        v_id NUMBER;        v_no VARCHAR(100);        v_date DATE;        v_sql VARCHAR(200);        s_id NUMBER;        s_date DATE; BEGIN      s_id := 3000;      s_date := SYSDATE;      v_sql := 'SELECT id,qan_no,sample_date FROM "tblno" WHERE id > :sid and sample_date <<span class="Apple-converted-space"> :sdate';      v_cursor := dbms_sql.open_cursor; --打开游标；      dbms_sql.parse(v_cursor, v_sql, dbms_sql.native); --解析动态SQL语句；      dbms_sql.bind_variable(v_cursor, ':sid', s_id); --绑定输入参数；      dbms_sql.bind_variable(v_cursor, ':sdate', s_date);            dbms_sql.define_column(v_cursor, 1, v_id); --定义列      dbms_sql.define_column(v_cursor, 2, v_no, 100);      dbms_sql.define_column(v_cursor, 3, v_date);      v_stat := dbms_sql.execute(v_cursor); --执行动态SQL语句。      LOOP          EXIT WHEN dbms_sql.fetch_rows(v_cursor)<=0; --fetch_rows在结果集中移动游标，如果未抵达末尾，返回1。                  dbms_sql.column_value(v_cursor, 1, v_id); --将当前行的查询结果写入上面定义的列中。          dbms_sql.column_value(v_cursor, 2, v_no);          dbms_sql.column_value(v_cursor, 3, v_date);          dbms_output.put_line(v_id || ';' || v_no || ';' || v_date);      END LOOP;      dbms_sql.close_cursor(v_cursor); --关闭游标。 END;

 

结果：

 

3095;S051013XW00010;15-10月-05 3112;A051013XW00027;10-10月-05 3113;A051013XW00028;13-10月-05 3116;S051013XW00031;13-10月-05