`
hqhe_nj
  • 浏览: 25609 次
  • 性别: Icon_minigender_1
  • 来自: 上海
社区版块
存档分类
最新评论

关于dbms_sql的使用

阅读更多

关于dbms_sql的使用

PL/SQL中使用动态SQL编程

在PL/SQL程序设计过程中,会遇到很多必须使用动态sql的地方,oracle系统所提供的DMBS_SQL包可以帮助你解决问题。
(一)介绍
DBMS_SQL系统包提供了很多函数及过程,现在简要阐述其中使用频率较高的几种:

function open_cursor:打开一个动态游标,并返回一个整型;

procedure close_cursor(c in out integer);关闭一个动态游标,参数为open_cursor所打开的游标;

procedure parse(c in integer, statement in varchar2, language_flag in integer):对动态游标所提供的sql语句进行解析,参数C表示游标,statement为sql语句,language-flag为解析sql语句所用oracle版本,一般有V6,V7跟native(在不明白所连database版本时,使用native);

procedure define_column(c in integer, position in integer, column any datatype, [column_size in integer]):定义动态游标所能得到的对应值,其中c为动态游标,positon为对应动态sql中的位置(从1开始),column为该值所对应的变量,可以为任何类型,column_size只有在column为定义长度的类型中使用如VARCHAR2,CHAR等(该过程有很多种情况,此处只对一般使用到的类型进行表述);

function execute(c in integer):执行游标,并返回处理一个整型,代表处理结果(对insert,delete,update才有意义,而对select语句而言可以忽略);

function fetch_rows(c in integer):对游标进行循环取数据,并返回一个整数,为0时表示已经取到游标末端;

procedure column_value(c in integer, position in integer, value):将所取得的游标数据赋值到相应的变量,c为游标,position为位置,value则为对应的变量;

procedure bind_variable(c in integer, name in varchar2, value):定义动态sql语句(DML)中所对应字段的值,c为游标,name为字段名称,value为字段的值;

以上是在程序中经常使用到的几个函数及过程,其他函数及过程请参照oracle所提供定义语句dbmssql.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;

(三)具体案例
下面就本人所开发系统中某一程序做分析
该过程为一股票技术曲线计算程序,将数据从即时数据表中取出,并按照计算曲线的公式,对这些数据进行计算,并将结果保存到技术曲线表中.
--**********************************
--procedure name:R_Ma_Main
--入口参数:PID股票代码,PEND时间,pinterval时间间隔,totab目标数据表
--调用函数:R_GetSql1,R_GetSql2
--功能:具体计算单支股票ma技术曲线
--时间:2001-06-20
--**********************************
create or replace procedure R_Ma_Main
  (
   pid varchar2,
   pend varchar2,
   pinterval varchar2,
   totab varchar2
  ) is                     
  
--定义数组
type Date_type is table of varchar2(12) index by binary_integer;
type Index_type is table of number index by binary_integer;

TempDate Date_Type;--时间数组
TempIndex Index_Type;--股票收盘价数组
TempMa Index_Type;--ma技术曲线数据

cursor1 integer;--游标
cursor2 integer;--游标
rows_processed integer;--执行游标返回

TempInter integer;--参与计算数值个数
TempVal integer;--计算时间类型
TempSql varchar2(500);--动态sql语句
MyTime varchar2(12);--时间
MyIndex number;--数值
MidIndex number;--中间变量
i integer := 999;
j integer;
begin
  TempInter := to_number(substr(pinterval,1,4));
  TempVal := to_number(substr(pinterval,5,2));
  TempSql := R_GetSql1(pid, pend, TempVal);--得到选择数据的sql语句

  --得到当天的即时数据,并依次保存到数组中
  cursor1 := dbms_sql.open_cursor;  --创建游标
  dbms_sql.parse(cursor1, TempSql, dbms_sql.native);  --解析动态sql语句,取两个字段,时间及价格,其中时间以14位的varchar2表示
  dbms_sql.define_column(cursor1, 1, MyTime, 12);  --分别定义sql语句中各字段所对应变量
  dbms_sql.define_column(cursor1, 2, MyIndex);
  rows_processed := dbms_sql.execute(cursor1);
  loop
    if dbms_sql.fetch_rows(cursor1) > 0 then
      begin
        dbms_sql.column_value(cursor1, 1, MyTime);
        dbms_sql.column_value(cursor1, 2, MyIndex);
        TempDate(i) := MyTime;
        TempIndex(i) := MyIndex;
        i := i - 1;--按倒序的方法填入数组
      end;
    else
      exit;
    end if;
  end loop;
  dbms_sql.close_cursor(cursor1);
  
  --如果取得的数据量不够计算个数,则跳出程序
  if i > 999-TempInter then
    goto JumpLess;
  end if;
  
  --初始化中间变量
  MidIndex := 0;
  TempIndex(i) := 0;
  for j in i..i+TempInter-1 loop
    MidIndex := MidIndex + TempIndex(j);
  end loop;

  --依次对当天数据计算ma值,并保存到ma数组中
  for j in i+TempInter..999 loop
    MidIndex := MidIndex - TempIndex(j-TempInter) + TempIndex(j);
    TempMa(j) := MidIndex/TempInter;
  end loop;   

  if TempVal < 6 then--如果计算的是分钟跟天的ma技术曲线
    begin
    cursor2 := dbms_sql.open_cursor;
    TempSql := 'insert into ' || totab || ' values(:r_no, :i_interval, :i_time, :i_index)';
    dbms_sql.parse(cursor2, TempSql, dbms_sql.native);
    for j in i+TempInter..999 loop
      dbms_sql.bind_variable(cursor2, 'r_no', pid);
      dbms_sql.bind_variable(cursor2, 'i_interval', pinterval);
      dbms_sql.bind_variable(cursor2, 'i_time', TempDate(j));
      dbms_sql.bind_variable(cursor2, 'i_index', TempMa(j));
      rows_processed := dbms_sql.execute(cursor2);--插入数据
    end loop;
    end;
  end if;
  commit;
  dbms_sql.close_cursor(cursor2);
  --数据量不足跳出
  <<JumpLess>>
  null;
  
  --exception处理,无关本话题
end;
/

(四)个人观点
在使用dbms_sql系统包的过程中,其方法简单而又不失灵活,但还是需要注意一些问题:
1,在整个程序的设计过程中,对游标的操作切不可有省略的部分,一旦省略其中某一步骤,则会程序编译过程既告失败,如在程序结尾处未对改游标进行关闭操作,则在再次调用过程时会出现错误.
2,dbms_sql除了可以做一般的select,insert,update,delete等静态的sql做能在过程中所做工作外,还能执行create等DDL操作,不过在执行该类操作时应首先显式赋予执行用户相应的系统权限,比如create table等.该类操作只需open cursor--->prase--->close cursor即能完成.

以上为本人在工作中对dbms_sql的一点点看法,不到之处,请予指正.
对于想更深了解dbms_sql的朋友,请阅读dbmssql.sql文件.
分享到:
评论

相关推荐

    DBMS_SQL的使用

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

    DBMS_SQL.rar_dbms_oracle

    下面是一个简单的示例,展示了如何使用DBMS_SQL执行动态SQL: ```sql DECLARE v_cursor INTEGER; v_sql VARCHAR2(200) := 'SELECT * FROM employees WHERE employee_id = :1'; v_employee_id NUMBER := 101; v_...

    DBMS_XMLDOM DBMS_XMLPARSER DBMS_XMLQUERY 文档

    Oracle数据库系统提供了强大的XML处理能力,这主要体现在其内置的几个PL/SQL包上,如DBMS_XMLDOM、DBMS_XMLPARSER和DBMS_XMLQUERY。这些包为开发者提供了处理XML文档的一整套工具,使得在数据库环境中进行XML数据的...

    ORACLE数据库封装过程DBMS_SQL的应用.pdf

    DBMS_SQL 的应用可以解决在项目开发中对用户管理和 DDL 使用的问题。 静态联接和动态联接是两种不同的联接方式。在静态联接中,PL/SQL 编译器会在编译时查找程序中引用的 ORACLE 对象的定义,然后将它们的存储地址...

    Oracle动态SQL之DBMS_SQL系统包的使用.pdf

    本文将探讨Oracle中的动态SQL实现机制,重点介绍DBMS_SQL系统包的结构与使用方法,通过实例展示动态SQL的基本用法和相关技巧。 动态SQL是指在程序执行时才能确定的SQL语句,它与静态SQL相对。静态SQL的语句在编写...

    PLSQL开发过程中,动态使用DBMS_SQL[借鉴].pdf

    dbms_sql.parse(cur, sql_stmt, dbms_sql.native); dbms_sql.execute(cur); dbms_sql.close_cursor(cur); end; 在这个示例中,我们使用 DBMS_SQL 包来执行一个 SELECT 语句,获取 dinya_test 表中的所有记录。 ...

    使用dbms_stats包手工收集统计信息

    Oracle 数据库中使用 dbms_stats 包手动收集统计信息 在 Oracle 数据库中,dbms_stats 包提供了一种手动收集统计信息的方式,包括基于表、用户和索引的统计信息。通过使用 dbms_stats 包,我们可以手动收集统计信息...

    oracle dbms_lob

    在实际应用中,`DBMS_LOB`包通常与SQL DML语句结合使用,以处理LOB字段。例如,可以使用`DBMS_LOB.WRITE`在INSERT或UPDATE语句中修改LOB值,或者使用`DBMS_LOB.APPEND`在已有的LOB后面添加新内容。 学习`DBMS_LOB`...

    ORACLE SYS.DBMS_REGISTRY_SYS has errors

    标题中的问题“ORACLE SYS.DBMS_REGISTRY_SYS has errors”指的是在Oracle数据库系统中,系统包BODY `SYS.DBMS_REGISTRY_SYS` 出现错误,导致了一系列的PL/SQL调用失败。这种错误通常与数据库的元数据注册功能有关,...

    DBMS_STATS.GATHER_TABLE_STATS详解.pdf

    1. **对于分区表**:建议使用 `DBMS_STATS` 而不是 `ANALYZE` 语句,因为 `DBMS_STATS` 支持并行处理、可以收集整个分区表的数据以及单个分区的数据,并且可以在不同级别上计算统计信息。 2. **对于非分区表**:同样...

    dbms_obfuscation_toolkit加密解密数据

    ### DBMS_OBFUSCATION_TOOLKIT:Oracle 数据库中的加密与解密工具包 DBMS_OBFUSCATION_TOOLKIT是Oracle数据库提供的一种用于数据加密解密的强大工具包,自Oracle 8i版本开始引入。它支持多种加密算法,如DES、...

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

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

    DBMS_RANDOM.VALUE OR DBMS_RANDOM.STRING

    请注意,使用`DBMS_RANDOM`时需要注意其性能影响,因为生成随机数和字符串涉及计算,可能会比直接查询数据库更消耗资源。在处理大量数据或频繁调用时,要特别注意这一点。 在源码层面,`DBMS_RANDOM`的实现可能涉及...

    SQL Tuning Advisor使用总结

    DBMS_OUTPUT.put_line('l_sql_tune_task_id: ' || l_sql_tune_task_id); END; ``` - **从游标缓存中获取SQL**: ```plsql DECLARE l_sql_tune_task_id VARCHAR2(100); BEGIN l_sql_tune_task_id := DBMS_...

    dbms_logmnr使用

    dbms_logmnr 使用 DBMS_LOGMNR 是 Oracle 中的一个日志分析包,用于分析和解释redo日志文件,以便追踪和诊断数据库中的操作。下面是 DBMS_LOGMNR 的使用方法。 首先,需要安装 LogMiner 工具,包括两个脚本 dbmslm...

    dbms_lock控制串行详解

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

    DBMS_JOB使用方法

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

    DBMS(java).zip_DBMS.java_consistog1_dbms_dbms sql解析器_java 实现dbms

    标题中的“DBMS(java).zip”表明这是一个关于Java实现的数据库管理系统(DBMS)的压缩文件,其中可能包含了源代码和其他相关文件。"DBMS.java"可能是主要的数据库管理系统的Java类,而"consistog1_dbms"可能是项目的...

    dbms.rar_DBMS实现_SQL语句_dbms_dbms java_java 实现dbms

    本项目“dbms.rar”提供了一个自定义实现的DBMS系统,它支持SQL语句的编译和解析,这在理解数据库系统的工作原理和进行数据库相关开发时具有很高的学习价值。 SQL(Structured Query Language)是用于处理关系...

Global site tag (gtag.js) - Google Analytics