`

Oracle之DBMS_SQL包用法详解

 
阅读更多

通常运用DBMS_SQL包一般分为如下几步:

1. open cursor打开cursor

2. parse cursor:解析你要执行的SQL语句

3. bind variable:如果要执行的SQL语句中包含变量,在此就需要绑定变量

4. execute:执行SQL语句

5. close cursor:在执行后关闭此cursor.

如果你还需要返回执行SQL的结果集,还需要使用define_column,define_array等方法,具体可以看如下流程图: 

  --  The flow of procedure calls will typically look like this:
  --
  --                      -----------
  --                    | open_cursor |
  --                      -----------
  --                           |
  --                           |
  --                           v
  --                         -----
  --          ------------>| parse |
  --         |               -----
  --         |                 |
  --         |                 |---------
  --         |                 v         |
  --         |           --------------  |
  --         |-------->| bind_variable | |
  --         |     ^     -------------   |
  --         |     |           |         |
  --         |      -----------|         |
  --         |                 |<--------
  --         |                 v
  --         |               query?---------- yes ---------
  --         |                 |                           |
  --         |                no                           |
  --         |                 |                           |
  --         |                 v                           v
  --         |              -------                  -------------
  --         |----------->| execute |            ->| define_column |
  --         |              -------             |    -------------
  --         |                 |------------    |          |
  --         |                 |            |    ----------|
  --         |                 v            |              v
  --         |           --------------     |           -------
  --         |       ->| variable_value |   |  ------>| execute |
  --         |      |    --------------     | |         -------
  --         |      |          |            | |            |
  --         |       ----------|            | |            |
  --         |                 |            | |            v
  --         |                 |            | |        ----------
  --         |                 |<-----------  |----->| fetch_rows |
  --         |                 |              |        ----------
  --         |                 |              |            |
  --         |                 |              |            v
  --         |                 |              |    --------------------
  --         |                 |              |  | column_value         |
  --         |                 |              |  | variable_value       |
  --         |                 |              |    ---------------------
  --         |                 |              |            |
  --         |                 |<--------------------------
  --         |                 |
  --          -----------------|
  --                           |
  --                           v
  --                      ------------
  --                    | close_cursor |
  --                      ------------
  --

 

下面根据不同情况用例子详细展示:

在做展示之前,先准备一些基础数据

create table cux_demo (a number,b number,c number); 
begin 
  for i in 1 .. 15 loop 
    insert into cux_demo 
    values 
      (round(dbms_random.value, 2) * 100, 
       round(dbms_random.value, 2) * 100, 
       round(dbms_random.value, 2) * 100); 
  end loop; 
  commit; 
end;

 基础数据完成之后,下面开始对一些具体情况进行分析:

 

1.执行一般的select语句

首先先介绍最常用情况:

DECLARE
  p_no        NUMBER := 80;
  cursor_name INTEGER := dbms_sql.open_cursor; --在初始化参数时,就可以打开cursor; 
  row_process INTEGER;
  v_b         NUMBER;
BEGIN
  --解析要执行的SQL. 
  dbms_sql.parse(cursor_name,
                 'select * from cux_demo where a= :no',
                 dbms_sql.native);
  --如果要执行的SQL中不需要参数,则可以省略掉bind_variable-- 
  dbms_sql.bind_variable(cursor_name,
                         'no',
                         p_no);
  --如果需要返回查询语句的结果,则必须在exec之前使用define_column函数定义返回字段;
  --define_column函数的第一个参数是最初定义的cursor name,
  --第二个参数是指需要返回的字段在查询结果中处于第几列,在此例中返回的字段是查询结果中的第二列,即b列;
  --第三个参数就是接收返回结果需要的变量*/ 
  dbms_sql.define_column(cursor_name,
                         2,
                         v_b);
  --必须定义一个参数接收exec的结果 
  row_process := dbms_sql.execute(cursor_name);
  LOOP
    IF dbms_sql.fetch_rows(cursor_name) > 0 THEN
      --将前面定义的字段返回给变量v_b-- 
      dbms_sql.column_value(cursor_name,
                            2,
                            v_b);
      dbms_output.put_line('B is ' || v_b);
    ELSE
      EXIT;
    END IF;
  END LOOP;
  --数据处理完成后记得要将cursor关闭 
  dbms_sql.close_cursor(cursor_name);
EXCEPTION
  WHEN OTHERS THEN
    dbms_sql.close_cursor(cursor_name);
END;

 

2.使用define_array方法得到查询结果

前面已经分析了如何使用define_column方法得到查询结果,但有时我们想要一次得到多行查询结果,此时我们就需要使用define_array方法,此方法常用于DML操作,稍后会有例子对此介绍,现在先来看一下如果使用define_array.

DECLARE
  c      NUMBER;
  d      NUMBER;
  n_tab  dbms_sql.number_table;
  n_tab1 dbms_sql.number_table;
  indx   NUMBER := 1;
BEGIN
  c := dbms_sql.open_cursor;
  dbms_sql.parse(c,
                 'select * from cux_demo where rownum<13 order by 1',
                 dbms_sql.native);
  --在此需要特别介绍一下define_array函数的第一个参数是已经打开的cursor名称, 
  --第二个参数是指需要返回的字段在查询结果中处于第几列,
  --第三个参数就是接收返回结果需要的变量,与define_column不同的是此变量是table,而不是普通的字段类型;
  --第四个参数表示一次可以返回的行数;
  --第五个参数是指n_tab的index从哪个数值开始,此数值是递增的.在此例中index是从1开始的,一次得到9行结果集,则有n_tab(1)到n_tab(9),如果循环再得到新的结果集,则index继续增长n_tab(10)....
  dbms_sql.define_array(c,
                        1,
                        n_tab,
                        9,
                        indx);
  dbms_sql.define_array(c,
                        2,
                        n_tab1,
                        9,
                        indx);
  d := dbms_sql.execute(c);
  LOOP
    d := dbms_sql.fetch_rows(c);
    dbms_output.put_line('fetch rows is ' || d);
    EXIT WHEN d < 9;
    dbms_sql.column_value(c,
                          1,
                          n_tab);
    dbms_sql.column_value(c,
                          2,
                          n_tab1);
    FOR i IN 1 .. d LOOP
      dbms_output.put_line(n_tab(i) || ',' || n_tab1(i));
    END LOOP;
  END LOOP;
  dbms_sql.close_cursor(c);
EXCEPTION
  WHEN OTHERS THEN
    IF dbms_sql.is_open(c) THEN
      dbms_sql.close_cursor(c);
    END IF;
END;

  

3.使用variable_value显示DML后的返回结果(单条记录)

以上我们介绍了如何使用DBMS_SQL包来处理数据查询,如果我们把查询语句更换成DML语句,则可以完成各种DML操作。在PL/SQL中我们可以使用returning方法返回DML操作结果,在DBMS_SQL包中可不可以实现呢?答案当然是可以,用variable_value方法就可以实现。下面就分别用两个例子来展示如何实现,一个是返回单条记录,另一个是返回多条记录。

1)返回单条记录

DECLARE
  c1 NUMBER := 100;
  c2 NUMBER := 100;
  r NUMBER;
  cursor_name NUMBER := dbms_sql.open_cursor;
  n NUMBER;
BEGIN
  dbms_sql.parse(cursor_name,
                 'insert into cux_demo(a,b) values (:a,:b) returning :a*:b into :r',
                 dbms_sql.native);
  dbms_sql.bind_variable(cursor_name,
                         'a',
                         c1);
  dbms_sql.bind_variable(cursor_name,
                         'b',
                         c2);
  dbms_sql.bind_variable(cursor_name,
                         'r',
                         r);
  n := dbms_sql.execute(cursor_name);
  --使用variable_value函数得到DML操作returning的结果集 
  dbms_sql.variable_value(cursor_name,
                          'r',
                          r);
  dbms_output.put_line(r);
  dbms_sql.close_cursor(cursor_name);
EXCEPTION
  WHEN OTHERS THEN
    dbms_sql.close_cursor(cursor_name);
END;

 2)返回多条记录

结合define_array使用,可以更好的完成DML操作。

PROCEDURE multi_insert_priv(c1 IN dbms_sql.number_table,
                            c2 IN dbms_sql.number_table,
                            r OUT dbms_sql.number_table) IS
  cursor_name NUMBER := dbms_sql.open_cursor;
  n NUMBER;
BEGIN
  dbms_sql.parse(cursor_name,
                 'insert into cux_demo values (:a,:b) returning :a*:b into :r',
                 dbms_sql.native);
  --使用bind_array函数将number_table类型的变量赋值给绑定变量 
  dbms_sql.bind_array(cursor_name,
                      'a',
                      c1);
  dbms_sql.bind_array(cursor_name,
                      'b',
                      c2);
  dbms_sql.bind_array(cursor_name,
                      'r',
                      r);
  n := dbms_sql.execute(cursor_name);
  --使用variable_value函数将returning的结果集赋值给number_table类型的变量 
  dbms_sql.variable_value(cursor_name,
                          'r',
                          r);
  dbms_sql.close_cursor(cursor_name);
EXCEPTION
  WHEN OTHERS THEN
    dbms_sql.close_cursor(cursor_name);
END;

PROCEDURE multi_insert IS
  c1 dbms_sql.number_table;
  c2 dbms_sql.number_table;
  cursor_name NUMBER := dbms_sql.open_cursor;
  n NUMBER;
  r dbms_sql.number_table;
  indx NUMBER := 1;
  d NUMBER;
BEGIN
  dbms_sql.parse(cursor_name,
                 'select * from cux_demo',
                 dbms_sql.native);
  dbms_sql.define_array(cursor_name,
                        1,
                        c1,
                        5,
                        indx);
  dbms_sql.define_array(cursor_name,
                        2,
                        c2,
                        5,
                        indx);
  n := dbms_sql.execute(cursor_name);
  LOOP
    d := dbms_sql.fetch_rows(cursor_name);
    EXIT WHEN d = 0;
    dbms_sql.column_value(cursor_name,
                          1,
                          c1);
    dbms_sql.column_value(cursor_name,
                          2,
                          c2);
    multi_insert_priv(c1,
                      c2,
                      r);
    FOR i IN 1 .. r.count LOOP
      dbms_output.put_line(r(i));
    END LOOP;
  END LOOP;
EXCEPTION
  WHEN OTHERS THEN
    dbms_sql.close_cursor(cursor_name);
END;

 

 

 

1
2
分享到:
评论

相关推荐

    DBMS_SQL的使用

    ### Oracle DBMS_SQL 使用详解 #### 一、概述 在Oracle数据库中,`DBMS_SQL`包是一个功能强大的工具,用于执行动态SQL语句。它提供了处理动态SQL语句的能力,使得开发人员能够灵活地构建和执行SQL语句,而不需要...

    DBMS_STATS.GATHER_TABLE_STATS详解.pdf

    ### DBMS_STATS.GATHER_TABLE_STATS详解 #### 一、概述 `DBMS_STATS.GATHER_TABLE_STATS` 是 Oracle 数据库中的一个重要过程,主要用于收集表、列和索引的统计信息,这些统计信息对于优化器选择合适的执行计划至关...

    oracle数据库定时任务dbms_job的用法详解

    下面将详细介绍DBMS_JOB的使用方法和相关知识点。 1. 创建JOB: 使用DBMS_JOB.SUBMIT函数来创建一个新的作业。这个函数接受四个参数:作业号(jobno)、执行的PL/SQL代码、下次执行时间(next_date)和执行间隔...

    dbms_lock控制串行详解

    这篇博文深入探讨了DBMS_LOCK的功能、使用方法以及在实际应用中的重要性。 首先,我们要理解DBMS_LOCK的作用。在多用户环境下,数据库系统需要确保事务的隔离级别,防止并发操作导致的数据不一致。DBMS_LOCK提供了...

    DBMS_JOB包创建ORACLE定时任务

    ### DBMS_JOB包创建Oracle定时任务详解 在Oracle数据库中,`DBMS_JOB`包提供了一种机制,用于创建和管理后台作业(Job),这些作业可以在特定的时间或按一定的时间间隔执行。这对于需要定期执行的任务非常有用,...

    Oracle中使用DBMS_XPLAN处理执行计划详解

    DBMS_XPLAN是Oracle提供的一个用于查看SQL计划,包括执行计划和解释计划的包;在以前查看SQL执行计划的时候,我都是使用set autotrace命令,不过现在看来,DBMS_XPLAN包给出了更加简化的获取和显示计划的方式。 这5...

    怎样禁用及回收java的授权dbms_java

    ### 如何禁用及回收Java的授权:dbms_java 授权管理详解 #### 一、引言 在Oracle数据库环境中,`dbms_java`包提供了一系列功能强大的工具,用于管理和控制Java应用程序的安全性。这对于那些在Oracle环境中部署了...

    Oracle_SQL注入

    ### Oracle SQL注入技术详解 #### 一、Oracle SQL注入概述 **Oracle SQL注入**是一种针对使用Oracle数据库的应用程序的安全攻击手段。攻击者通过构造特殊的SQL查询来操纵目标应用程序中的SQL语句,以此来获取敏感...

    Oracle之DBMS_RANDOM包详解

    DBMS_RANDOM是Oracle提供的一个PL/SQL包,用于生成随机数据和字符。它具有以下函数。  其中,initialize,random,terminate函数在Oracle11g中已不推荐使用,主要用于向后兼容。下面对各个函数进行举例说明  1. ...

    DBMS_JOB使用方法

    ### DBMS_JOB 使用方法详解 #### 一、概述 `DBMS_JOB` 是 Oracle 数据库提供的用于调度任务的包,可以实现对定时任务的管理,包括任务的创建、修改、删除及执行等操作。该包提供了多种过程和函数,允许用户以灵活...

    ORACLE DBMS STATS ERROR

    在Oracle数据库中,`DBMS_STATS` 是一个重要的包,它用于收集和管理表、索引和其他数据库对象的统计信息,这些信息用于优化器选择执行查询的最佳执行计划。然而,当出现错误“ORA-04063: package body 'SYS.DBMS_...

    Oracle随机函数之dbms_random使用详解

    Oracle的DBMS_RANDOM是一个强大的包,它提供了生成各种随机数据的方法,对于测试、模拟和数据分析等场景非常有用。本文将深入解析DBMS_RANDOM中的几个主要函数:`value`、`normal`和`string`。 1. **DBMS_RANDOM....

    oracle_plsql_编程详解

    ### Oracle PL/SQL 编程详解 #### 一、PL/SQL 概述 ##### 1.1 什么是 PL/SQL? PL/SQL(Procedure Language for SQL)是Oracle数据库的一种专用编程语言,它将SQL语句与过程化的编程语言特性相结合,提供了强大的...

    DBMS_AQ 不能出队

    在某些情况下,使用 Oracle Advanced Queueing (AQ) 的 PL/SQL 通知机制时,会遇到 `register_driver()` 进程无法正常终止的问题,导致消息无法被正确处理。这一问题表现为 `register_driver()` 相关作业在 `db`a_...

    dbms_stats.docx

    DBMS_STATS是Oracle提供的一款功能强大的工具包,主要用于收集表和索引的统计信息,进而帮助Oracle的Cost-Based Optimizer (CBO)生成更为高效的执行计划。通过准确的统计信息,可以有效地减少查询响应时间和提高系统...

    Oracle_PLSQL_存储过程

    ### Oracle PL/SQL 存储过程详解 #### Oracle存储过程基础知识 存储过程是数据库中预编译好的一段SQL或PL/SQL代码,它可以接受输入参数并返回结果,从而实现复杂的功能逻辑。存储过程的主要优势包括提高性能(因为...

    Oracle_11g_面向DBA的新功能学习指南 less04

    ### Oracle 11g 面向DBA的新功能学习指南之SQL计划管理详解 #### 一、引言 随着数据库技术的不断发展,Oracle 11g 的推出为数据库管理员(DBA)带来了诸多新的功能与改进。其中一项重要的增强功能便是SQL计划管理...

    oracle存储过程_函数_语法_大全_详解

    本文将详细介绍Oracle存储过程与函数的创建、使用方法以及常见操作。 #### 二、创建存储过程 存储过程是一组为了完成特定功能的SQL语句集,经编译后存储在数据库中。存储过程的优点包括提高性能、减少网络流量、...

Global site tag (gtag.js) - Google Analytics