DBMS_SQL系统包

📄字数 6.6K

👁️阅读量加载中...

一、概述

DBMS_SQL系统包是一个动态SQL执行工具包，它允许在PL/SQL中动态构建、解析、绑定变量、执行和获取结果集。相比EXECUTE IMMEDIATE，DBMS_SQL更灵活，适合复杂的动态场景。以下场景往往会选择DBMS_SQL：

SQL结构完全不确定，需要在运行时动态生成；
列数不固定。查询列数根据参数决定，需动态定义列和读取结果；
动态绑定参数执行SQL。多个占位符参数类型不同，需要逐个绑定变量；
需要执行DDL语句或无法使用EXECUTE IMMEDIATE的情况；
需要逐行读取返回结果集；

在该系统包中提供了以下接口：

包体函数名	简要描述
OPEN_CURSOR	打开一个动态的SQL游标
CLOSE_CURSOR	关闭游标，释放资源
IS_OPEN	检查游标是否处于开启状态
PARSE	解析SQL字符串
BIND_VARIABLE	绑定参数值
DEFINE_COLUMN	定义要读取的列，指定变量类型
EXECUTE	执行已解析的语句
FETCH_ROWS	获取结果集中一行
COLUMN_VALUE	取当前行某列的值
STROF_CURSOR	输出指定游标编号的游标信息

使用流程简要说明：

打开游标

sql

v_cursor := DBMS_SQL.OPEN_CURSOR;

解析语句

sql

DBMS_SQL.PARSE(v_cursor, v_sql, DBMS_SQL.NATIVE);

绑定变量（可选）

sql

DBMS_SQL.BIND_VARIABLE(v_cursor, ':param1', v_value);

执行语句

sql

rows_processed := DBMS_SQL.EXECUTE(v_cursor);

获取结果（如果是查询）

sql

DBMS_SQL.DEFINE_COLUMN(v_cursor, 1, v_result);
LOOP
  IF DBMS_SQL.FETCH_ROWS(v_cursor) = 0 THEN EXIT;
  DBMS_SQL.COLUMN_VALUE(v_cursor, 1, v_result);
END LOOP;

关闭游标

sql

DBMS_SQL.CLOSE_CURSOR(v_cursor);

二、OPEN_CURSOR

2.1 功能描述

该接口用于打开一个游标，分配一个游标对象，并返回游标编号

2.2 方法声明

sql

FUNCTION OPEN_CURSOR() RETURN INTEGER;

2.3 参数说明

无

2.4 示例

sql

SQL> declare
         c integer;
     begin
         -- 打开游标
         c := DBMS_SQL.OPEN_CURSOR;
     end;
     /

三、CLOSE_CURSOR

3.1 功能描述

该接口用于关闭一个游标，通常与OPEN_CURSOR搭配使用

3.2 方法声明

sql

FUNCTION CLOSE_CURSOR(
    CurNo       INTEGER
    ) RETURN    INTEGER;

3.3 参数说明

参数名	类型	取值范围	是否可选	描述
CurNo	INTEGER	无	否	游标编号

3.4 示例

sql

SQL> declare
         c integer;
     begin
         -- 打开游标
         c := DBMS_SQL.OPEN_CURSOR;

         -- 关闭游标
         DBMS_SQL.CLOSE_CURSOR(c);
     end;
     /

四、IS_OPEN

4.1 功能描述

该接口用于检查一个游标是否处于开启状态

4.2 方法声明

sql

FUNCTION IS_OPEN(
    c           IN INTEGER
    ) RETURN    BOOLEAN;

4.3 参数说明

参数名	类型	取值范围	是否可选	描述
c	INTEGER	无	否	游标编号

4.4 示例

sql

SQL> declare
         c integer;
         b boolean;
     begin
         -- 打开游标
         c := DBMS_SQL.OPEN_CURSOR;

         -- 检查游标状态
         b := DBMS_SQL.IS_OPEN(c);
     end;
     /

五、PARSE

5.1 功能描述

该接口用于为游标设置一个SQL语句，并解析规划此语句。

5.2 方法声明

sql

FUNCTION PARSE(
    CurNo    INTEGER, 
    StmtSQL  VARCHAR, 
    SqlType  INTEGER
    ) RETURN INTEGER;

5.3 参数说明

参数名	类型	取值范围	是否可选	描述
CurNo	INTEGER	无	否	游标编号
StmtSQL	VARCHAR	无	否	SQL语句
SqlType	INTEGER	无	否	SQL类型，通常写DBMS_SQL.NATIVE，表示本地SQL语法

5.4 示例

sql

-- 创建employees表
SQL> CREATE TABLE employees (
       id             NUMBER PRIMARY KEY,
       name           VARCHAR2(100),
       department_id  NUMBER
     );

-- 插入数据
SQL> INSERT INTO employees VALUES (1, 'Alice', 10);
SQL> INSERT INTO employees VALUES (2, 'Bob', 20);
SQL> INSERT INTO employees VALUES (3, 'Charlie', 10);
SQL> INSERT INTO employees VALUES (4, 'Diana', 30);
SQL> INSERT INTO employees VALUES (5, 'Evan', 10);

SQL> DECLARE
       c       INTEGER;
     BEGIN
       -- 打开游标
       c := DBMS_SQL.OPEN_CURSOR;

       -- 解析SQL
       DBMS_SQL.PARSE(c, 'SELECT id, name FROM employees WHERE department_id = :1', DBMS_SQL.NATIVE);
     END;
     /

六、BIND_VARIABLE

6.1 功能描述

该接口用于对游标中的指定参数绑定值

6.2 方法声明

该接口有多种重载方式，其对应不同的参数值类型。此处只列举了2种

重载形式一：Val数据类型为BOOLEAN

sql

FUNCTION BIND_VARIABLE(
    CurNo       INTEGER, 
    ColName     VARCHAR, 
    Val         BOOLEAN
    ) RETURN INTEGER;

重载形式二：val数据类型为TINYINT

sql

FUNCTION BIND_VARIABLE(
    CurNo       INTEGER, 
    ColName     VARCHAR, 
    Val         TINYINT
    ) RETURN INTEGER;

6.3 参数说明

参数名	类型	取值范围	是否可选	描述
CurNo	INTEGER	无	否	游标编号
ColName	VARCHAR	无	否	参数名
Val	支持多种数据类型	无	否	绑定的参数值

Val支持的数据类型除BOOLEAN、TINYINT以外，还包括：
SMALLINT、INTEGER、BIGINT、FLOAT、DOUBLE、NUMERIC、VARCHAR、CLOB、BLOB、TIME、TIME WITH TIME ZONE、DATE、DATETIME、DATETIME WITH TIME ZONE、INTERVAL YEAR、INTERVAL YEAR TO MONTH、INTERVAL MONTH、INTERVAL DAY、INTERVAL DAY TO HOUR、INTERVAL HOUR、INTERVAL DAY TO MINUTE、INTERVAL HOUR TO MINUTE、INTERVAL MINUTE、INTERVAL DAY TO SECOND、INTERVAL HOUR TO SECOND、INTERVAL MINUTE TO SECOND、INTERVAL SECOND、GUID、BINARY、ROWID、INTEGER[]

6.4 示例

sql

SQL> DECLARE
      c       INTEGER;
    BEGIN
      -- 打开游标
      c := DBMS_SQL.OPEN_CURSOR;

      -- 解析SQL
      DBMS_SQL.PARSE(c, 'SELECT id, name FROM employees WHERE department_id = :1', DBMS_SQL.NATIVE);

      -- 绑定变量 :1
      DBMS_SQL.BIND_VARIABLE(c, ':1', 10);
    END;
    /

七、DEFINE_COLUMN

7.1 功能描述

该接口用于定义要读取的列，指定变量类型

7.2 方法声明

该接口有多种重载方式，其对应OUT型参数Var的不同数据类型。此处只列举了2种

重载形式一：Var数据类型为BOOLEAN

sql

FUNCTION DEFINE_COLUMNS(
    CurNo       INTEGER, 
    ColNo       INTEGER, 
    Var         OUT BOOLEAN
    ) RETURN INTEGER;

重载形式二：var数据类型为TINYINT

sql

FUNCTION DEFINE_COLUMNS(
    CurNo       INTEGER, 
    ColNo       INTEGER, 
    Var         OUT TINYINT
    ) RETURN INTEGER;

7.3 参数说明

参数名	类型	取值范围	是否可选	描述
CurNo	INTEGER	无	否	游标编号
ColNo	INTEGER	无	否	参数编号
Var	支持多种数据类型	无	否	OUT型参数，保存输出的参数值

Val支持的数据类型除BOOLEAN、TINYINT以外，还包括：
SMALLINT、INTEGER、BIGINT、FLOAT、DOUBLE、NUMERIC、VARCHAR、CLOB、BLOB、TIME、TIME WITH TIME ZONE、DATE、DATETIME、DATETIME WITH TIME ZONE、INTERVAL YEAR、INTERVAL YEAR TO MONTH、INTERVAL MONTH、INTERVAL DAY、INTERVAL DAY TO HOUR、INTERVAL HOUR、INTERVAL DAY TO MINUTE、INTERVAL HOUR TO MINUTE、INTERVAL MINUTE、INTERVAL DAY TO SECOND、INTERVAL HOUR TO SECOND、INTERVAL MINUTE TO SECOND、INTERVAL SECOND、GUID、BINARY、ROWID、INTEGER[]

7.4 示例

sql

SQL> DECLARE
      c       INTEGER;
      v_name  VARCHAR2(100);
      v_id    NUMBER;
    BEGIN
      -- 打开游标
      c := DBMS_SQL.OPEN_CURSOR;

      -- 解析SQL
      DBMS_SQL.PARSE(c, 'SELECT id, name FROM employees WHERE department_id = :1', DBMS_SQL.NATIVE);

      -- 定义列
      DBMS_SQL.DEFINE_COLUMN(c, 1, v_id);
      DBMS_SQL.DEFINE_COLUMN(c, 2, v_name);
    END;
    /

八、EXECUTE

8.1 功能描述

该接口用于执行已解析的SQL语句

8.2 方法声明

sql

FUNCTION EXECUTE(
    CurNo       INTEGER
    ) RETURN    INTEGER;

8.3 参数说明

参数名	类型	取值范围	是否可选	描述
CurNo	INTEGER	无	否	游标编号

8.4 示例

sql

SQL> DECLARE
       c       INTEGER;
       rows_fetched INTEGER;
     BEGIN
       -- 打开游标
       c := DBMS_SQL.OPEN_CURSOR;

       -- 解析SQL
       DBMS_SQL.PARSE(c, 'SELECT id, name FROM employees WHERE department_id = :1', DBMS_SQL.NATIVE);

       -- 绑定变量 :1
       DBMS_SQL.BIND_VARIABLE(c, ':1', 10);

       -- 执行SQL
       rows_fetched := DBMS_SQL.EXECUTE(c);
     END;
     /

九、FETCH_ROWS

9.1 功能描述

该接口用于使用游标向前抓取一行

9.2 方法声明

sql

FUNCTION FETCH_ROWS(
    CurNo       INTEGER
    ) RETURN    INTEGER;

9.3 参数说明

参数名	类型	取值范围	是否可选	描述
CurNo	INTEGER	无	否	游标编号

9.4 示例

sql

SQL> DECLARE
      c       INTEGER;
      v_name  VARCHAR2(100);
      v_id    NUMBER;
      rows_fetched INTEGER;
      str  varchar;
    BEGIN
      -- 打开游标
      c := DBMS_SQL.OPEN_CURSOR;

      -- 解析SQL
      DBMS_SQL.PARSE(c, 'SELECT id, name FROM employees WHERE department_id = :1', DBMS_SQL.NATIVE);

      -- 绑定变量 :1
      DBMS_SQL.BIND_VARIABLE(c, ':1', 10);

      -- 定义列
      DBMS_SQL.DEFINE_COLUMN(c, 1, v_id);
      DBMS_SQL.DEFINE_COLUMN(c, 2, v_name);

      -- 执行
      rows_fetched := DBMS_SQL.EXECUTE(c);

      -- 取结果
      WHILE DBMS_SQL.FETCH_ROWS(c) > 0 LOOP
        DBMS_SQL.COLUMN_VALUE(c, 1, v_id);
        DBMS_SQL.COLUMN_VALUE(c, 2, v_name);
        DBMS_OUTPUT.PUT_LINE('ID: ' || v_id || ', Name: ' || v_name);
      END LOOP;

      -- 关闭游标
      DBMS_SQL.CLOSE_CURSOR(c);
    END;
    /
-- 输出结果
ID: 1, Name: Alice
ID: 3, Name: Charlie
ID: 5, Name: Evan

十、COLUMN_VALUE

10.1 功能描述

该接口用于获取当前行指定列的值

10.2 方法声明

该接口有多种重载方式，其对应不同的参数值类型。此处只列举了2种

重载形式一：Var数据类型为BOOLEAN

sql

FUNCTION COLUMN_VALUE(
    CurNo       INTEGER, 
    ColNo       INTEGER, 
    Var         OUT BOOLEAN
    ) RETURN INTEGER;

重载形式二：var数据类型为TINYINT

sql

FUNCTION COLUMN_VALUE(
    CurNo       INTEGER, 
    ColNo       INTEGER, 
    Var         OUT TINYINT
    ) RETURN INTEGER;

10.3 参数说明

参数名	类型	取值范围	是否可选	描述
CurNo	INTEGER	无	否	游标编号
ColNo	INTEGER	无	否	列号
Var	支持多种数据类型	无	否	OUT型参数，保存输出的参数值

Val支持的数据类型除BOOLEAN、TINYINT以外，还包括：
SMALLINT、INTEGER、BIGINT、FLOAT、DOUBLE、NUMERIC、VARCHAR、CLOB、BLOB、TIME、TIME WITH TIME ZONE、DATE、DATETIME、DATETIME WITH TIME ZONE、INTERVAL YEAR、INTERVAL YEAR TO MONTH、INTERVAL MONTH、INTERVAL DAY、INTERVAL DAY TO HOUR、INTERVAL HOUR、INTERVAL DAY TO MINUTE、INTERVAL HOUR TO MINUTE、INTERVAL MINUTE、INTERVAL DAY TO SECOND、INTERVAL HOUR TO SECOND、INTERVAL MINUTE TO SECOND、INTERVAL SECOND、GUID、BINARY、ROWID、INTEGER[]

10.4 示例

sql

SQL> DECLARE
      c       INTEGER;
      v_name  VARCHAR2(100);
      v_id    NUMBER;
      rows_fetched INTEGER;
      str  varchar;
    BEGIN
      -- 打开游标
      c := DBMS_SQL.OPEN_CURSOR;

      -- 解析SQL
      DBMS_SQL.PARSE(c, 'SELECT id, name FROM employees WHERE department_id = :1', DBMS_SQL.NATIVE);

      -- 绑定变量 :1
      DBMS_SQL.BIND_VARIABLE(c, ':1', 10);

      -- 定义列
      DBMS_SQL.DEFINE_COLUMN(c, 1, v_id);
      DBMS_SQL.DEFINE_COLUMN(c, 2, v_name);

      -- 执行
      rows_fetched := DBMS_SQL.EXECUTE(c);

      -- 取结果
      WHILE DBMS_SQL.FETCH_ROWS(c) > 0 LOOP
        DBMS_SQL.COLUMN_VALUE(c, 1, v_id);
        DBMS_SQL.COLUMN_VALUE(c, 2, v_name);
        DBMS_OUTPUT.PUT_LINE('ID: ' || v_id || ', Name: ' || v_name);
      END LOOP;

      -- 关闭游标
      DBMS_SQL.CLOSE_CURSOR(c);
    END;
    /
-- 输出结果
ID: 1, Name: Alice
ID: 3, Name: Charlie
ID: 5, Name: Evan

十一、STROF_CURSOR

11.1 功能描述

该接口根据游标编号输出游标信息

11.2 方法声明

sql

FUNCTION STROF_CURSOR(
    CurNo       INTEGER
    ) RETURN    VARCHAR(5000);

11.3 参数说明

参数名	类型	取值范围	是否可选	描述
CurNo	INTEGER	无	否	游标编号

11.4 示例

sql

SQL> DECLARE
       c       INTEGER;
       v_name  VARCHAR2(100);
       v_id    NUMBER;
       str  varchar;
     BEGIN
       -- 打开游标
       c := DBMS_SQL.OPEN_CURSOR;

       -- 解析SQL
       DBMS_SQL.PARSE(c, 'SELECT id, name FROM employees WHERE department_id = :1', DBMS_SQL.NATIVE);

       -- 绑定变量 :1
       DBMS_SQL.BIND_VARIABLE(c, ':1', 10);

       -- 定义列
       DBMS_SQL.DEFINE_COLUMN(c, 1, v_id);
       DBMS_SQL.DEFINE_COLUMN(c, 2, v_name);

       -- 获取游标编号为c的游标信息
       str := dbms_sql.strof_cursor(c);
       dbms_output.put_line('info: '||str);

       -- 关闭游标
       DBMS_SQL.CLOSE_CURSOR(c);
     END;
     /

-- 输出结果
info: Cursor{
       state=2  ObjPtr=000002097DBA5F18
       SQL=SELECT id, name FROM employees WHERE department_id = :1
       Paras[
             (:1 1 5 -1)
            ]
       Targets[
              (EMPLOYEES.ID 7 786432 7)
              (EMPLOYEES.NAME 30 100 1025)
              ]

11.5 场景应用

场景一：输出游标的文本信息，用于支持调试。如捕捉当前绑定变量和执行语句结构

DBMS_SQL系统包 ​

一、概述 ​

二、OPEN_CURSOR ​

2.1 功能描述 ​

2.2 方法声明 ​

2.3 参数说明 ​

2.4 示例 ​

三、CLOSE_CURSOR ​

3.1 功能描述 ​

3.2 方法声明 ​

3.3 参数说明 ​

3.4 示例 ​

四、IS_OPEN ​

4.1 功能描述 ​

4.2 方法声明 ​

4.3 参数说明 ​

4.4 示例 ​

五、PARSE ​

5.1 功能描述 ​

5.2 方法声明 ​

5.3 参数说明 ​

5.4 示例 ​

六、BIND_VARIABLE ​

6.1 功能描述 ​

6.2 方法声明 ​

6.3 参数说明 ​

6.4 示例 ​

七、DEFINE_COLUMN ​

7.1 功能描述 ​

7.2 方法声明 ​

7.3 参数说明 ​

7.4 示例 ​

八、EXECUTE ​

8.1 功能描述 ​

8.2 方法声明 ​

8.3 参数说明 ​

8.4 示例 ​

九、FETCH_ROWS ​

9.1 功能描述 ​

9.2 方法声明 ​

9.3 参数说明 ​

9.4 示例 ​

十、COLUMN_VALUE ​

10.1 功能描述 ​

10.2 方法声明 ​

10.3 参数说明 ​

10.4 示例 ​

十一、STROF_CURSOR ​

11.1 功能描述 ​

11.2 方法声明 ​

11.3 参数说明 ​

11.4 示例 ​

11.5 场景应用 ​