使用DBMS_SQL实现动态sql游标

最新推荐文章于 2024-01-24 07:11:03 发布

FIGHT_ANGEL

最新推荐文章于 2024-01-24 07:11:03 发布

阅读量851

点赞数

CC 4.0 BY-SA版权

分类专栏： Oracle Basic

本文链接：https://blog.youkuaiyun.com/FIGHT_ANGEL/article/details/12618657

Oracle Basic 专栏收录该内容

23 篇文章

订阅专栏

本文详细介绍了如何使用DBMS_SQL包在Oracle数据库中执行动态SQL语句，包括DDL、DML和SELECT操作，以及如何高效地处理数据。重点阐述了如何创建、解析、绑定变量、执行和关闭游标等关键步骤。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

转自：http://blog.sina.com.cn/s/blog_6bd7d94301014mt2.html

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)

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;

====================================================================================================================

如下实例：

CREATE OR REPLACE PROCEDURE PRO_YU(V_TABLE_NAME IN VARCHAR2)

AS
V_CURSOR    NUMBER;
V_SQL       VARCHAR2(100);
V_ACTUALFEE NUMBER;
V_PROCESS   INTEGER;

BEGIN
V_CURSOR := DBMS_SQL.OPEN_CURSOR;
V_SQL := 'SELECT ACTUALFEE FROM ' || V_TABLE_NAME;

DBMS_SQL.PARSE(V_CURSOR, V_SQL, DBMS_SQL.V7);

---V_ACTUALFEE为NUMBER型，如果为字符则使用DBMS_SQL.DEFINE_COLUMN_CHAR(V_CURSOR,1,V_ACTUALFEE,4000);
DBMS_SQL.DEFINE_COLUMN(V_CURSOR, 1, V_ACTUALFEE);

V_PROCESS := DBMS_SQL.EXECUTE(V_CURSOR);

LOOP
    IF DBMS_SQL.FETCH_ROWS(V_CURSOR) = 0 THEN
      EXIT;
    END IF;

    ---V_ACTUALFEE为NUMBER型，如果为字符则使用DBMS_SQL.COLUMN_VALUE_CHAR(V_CURSOR,1,V_ACTUALFEE);
    DBMS_SQL.COLUMN_VALUE(V_CURSOR, 1, V_ACTUALFEE);

    DBMS_OUTPUT.PUT(TO_CHAR(V_ACTUALFEE));
    INSERT INTO YU_TEMP VALUES (V_ACTUALFEE);

END LOOP;

COMMIT;
DBMS_SQL.CLOSE_CURSOR(V_CURSOR);

END;