ORACLE存储过程

2024-07-15

ORACLE存储过程（精选八篇）

ORACLE存储过程篇1

随着计算机技术和网络技术的发展, 众多大中企业的信息化建设达到了相当完善的程度。在大多数企业的应用系统中, 数据库得到了广泛的应用。IBM公司的INFORMIX数据库和甲骨文公司的ORACLE数据库, 作为数据库中的佼佼者, 在银行、电力、电信等行业的大型应用中扮演着举足轻重的角色, 发挥着巨大的作用。数据库的存储过程具有执行速度更快、允许模块化设计、可提高系统安全性和可减少网络流通量等优点, 在应用系统中越来越多地被采用。而INFORMIX存储过程与ORACLE存储过程之间的相互转换具有很强的实际意义。笔者长期从事信息技术工作, 对INFORMIX存储过程与ORACLE存储过程多有了解, 在此试图通过分析INFORMIX存储过程语言与ORACLE存储过程语言的主要异同, 为两者之间的转换打下一定的基础, 希望对感兴趣的读者有所帮助。

二、INFORMIX与ORACLE存储过程语言的主要异同

INFORMIX存储过程语言与ORACLE存储过程语言的内容十分丰富, 本文仅介绍两者之间的主要异同。

(一) 注释

INFORMIX:

单行注释在注释内容前加双连字符--

多行注释以{开头, 以}结束

ORACLE:

单行注释在注释内容前加双连字符--

多行注释以/*开头, 以*/结束

(二) 常用变量定义

INFORMIX格式:

DEFINE变量类型;

DEFINE变量REFERENCES TEXT;

DEFINE变量LIKE数据库表.字段;

ORACLE格式:

变量类型[CONSTANT][NOT NULL][:=变量值]

变量数据库表.字段%TYPE;

表记录变量数据库表%ROWTYPE;

(三) 变量赋值

INFORMIX格式:

LET变量=表达式;

LET变量=函数;

SELECT字段1[, 字段2[, ...]]INTO变量1[, 变量2[, ...]]FROM表WHERE条件

ORACLE格式:

变量:=表达式;

变量:=函数;

SELECT字段1[, 字段2[, ...]]INTO变量1[, 变量2[, ...]]FROM表WHERE条件;

SELECT*INTO表记录变量FROM表WHERE条件;

(四) 存储过程基本结构

INFORMIX格式:

CREATE PROCEDURE过程名[ (参数1类型[, 参数2类型[, .......]]) ]

[RETURNING类型[, 类型[, ……]]];

变量定义

异常处理语句块

执行语句块

RETURN参数1[, 参数2[, ...];

END PPROCEDURE

ORACLE格式:

CREATE OR REPLACE PROCEDURE procedure_name

[ (参数1[IN|OUT|IN OUT]类型[, 参数2[IN|OUT|IN OUT]类型[,

…]) ]AS

DECLARE

定义变量

BEGIN

执行语句块

EXCEPTION

异常处理语句块

END[procedure_name];

(五) 条件语句

INFORMIX格式:

IF条件1 THEN

语句块

[ELIF条件2 THEN

语句块]

......

[ELSE

语句块]

END IF

ORACLE格式:

1. IF语句

IF条件1 THEN

语句块

[ELSIF条件2 THEN

语句块]

......

[ELSE

语句块]

END IF

2. CASE语句

CASE变量

WHEN表达式1 THEN语句块;

WHEN表达式2 THEN语句块;

………

WHEN表达式N THEN语句块;

[ELSE语句块;]

END CASE

(六) 循环语句

INFORMIX格式:

1. WHILE循环

WHILE条件语句块END WHILE

2. FOR循环

FOR变量IN (表达式1 TO表达式2 STEP步长) 语句块END FOR

FOR变量= (表达式1 TO表达式2 STEP步长) 语句块END FOR

FOR变量IN (表达式1, 表达式2, ...) 语句块END FOR

3. FOREACH循环

FOREACH[WITH HOLD]SELECT INTO语句语句块END FOREACH;

FOREACH EXECUTE PROCEDURE存储过程名[ (参数...参数) ]

INTO变量1[, 变量2[, ...]]语句块END FOREACH;

4. 游标式FOREACH循环

FOREACH游标名[WITH HOLD]FOR SELECT INTO语句语句块END FOREACH;

ORACLE格式:

1.LOOP循环

LOOP语句块END LOOP;

2.WHILE循环

WHILE条件LOOP语句块END LOOP;

3.FOR循环

FOR变量IN[REVERSE]下边界值..上边界值LOOP语句块END LOOP;

4.游标式FOR循环

CURSOR游标名IS SELECT语句;

FOR记录类型变量IN游标名LOOP语句块END LOOP;

(七) 循环的转移INFORMIX格式:

CONTINUE WHILE

EXIT WHILE

CONTINUE FOR

EXIT FOR

CONTINUE FOREACH

EXIT FOREACH

ORACLE:

因在循环语句中无CONTINUE语句, 一般可用goto语句或自定义异常来实现CONTINUE的功能。

用EXIT WHEN条件退出循环。

(八) 跟踪调试

INFORMIX格式:

SET DEBUG FILE TO跟踪调试文件名;

TRACE ON;

TRACE变量||“显示信息”;

TRACE OFF;

ORACLE:

如不使用第三方软件, 可用DBMS_OUTPUT.PUT_LINE () 对程序进行跟踪调试, 例如:

DBMS_OUTPUT.PUT_LINE (‘Today is’||v_day) ;

(九) 异常处理

INFORMIX格式:

1. 异常处理

ON EXCEPTION[IN (错误号, ...) ]

SET SQL错误变量, ISAM错误变量, 错误文本变量

语句块

END EXCEPTION[WITH RESUME];

2. 用户自定义异常

RAISE EXCEPTION自定义SQL错误号, 自定义ISAM错误变量, 自定义错误文本变量

人工触发异常由ON EXCEPTION进行捕捉

ORACLE格式:

1.预定义异常处理

EXCEPTION WHEN异常情况错误名称THEN

WHEN OTHERS THEN异常处理语句块

2.用户自定义异常处理

R A ISE异常处理语句块自定义异常情况变量自定义异常情况变量为一特殊变量类型, 须事先定义:

自定义异常情况变量EXCEPTION;

(十) 常用内置函数

在INFORMIX和ORACEL存储过程中, 有许多内置函数其函数名和功能完全相同, 其用法也基本相同。下面列出部分以供参考。

LOWER, LTRIM, REPLACE, RTRIM, SUBSTR, UPPER, LENGTH, ABS, TRUNC, ROUND, TO_CHAR, TO_DATE, DECODE, NVL。

三、结束语

如何调用Oracle存储过程篇2

创建语句：create or replace procedure 存储过程名

如果没有or replace语句，则仅仅是新建一个存储过程，如果系统存在该存储过程，则会报错。Create or replace procedure 如果系统中没有此存储过程就新建一个，如果系统中有此存储过程则把原来删除掉，重新创建一个存储过程。

存储过程名定义：包括存储过程名和参数列表。参数名和参数类型。参数名不能重复，参数传递方式：IN, OUT, IN OUT

IN 表示输入参数，按值传递方式。

OUT 表示输出参数，可以理解为按引用传递方式。可以作为存储过程的输出结果，供外部调用者使用。

IN OUT 即可作输入参数，也可作输出参数。

参数的数据类型只需要指明类型名即可，不需要指定宽度。

参数的宽度由外部调用者决定。

过程可以有参数，也可以没有参数

变量声明块：紧跟着的as (is )关键字，可以理解为pl/sql的declare关键字，用于声明变量。

变量声明块用于声明该存储过程需要用到的变量，它的作用域为该存储过程，

另外这里声明的变量必须指定宽度。遵循PL/SQL的变量声明规范。

过程语句块：从begin 关键字开始为过程的语句块。存储过程的具体逻辑在这里来实现。

异常处理块：关键字为exception ,为处理语句产生的异常。该部分为可选

结束块：由end关键字结果。

存储过程的参数传递方式

存储过程的参数传递有三种方式：IN,OUT,IN OUT .

IN 按值传递，并且它不允许在存储过程中被重新赋值。如果存储过程的参数没有指定存参数传递类型，默认为IN

例子：

创建：

CREATE OR REPLACE PROCEDURE P_1(S_NO INT)

S_AGE INT;

BEGIN

SELECT SAGE INTO S_AGE FROM STU WHERE SNO=S_NO;

DBMS_OUTPUT.PUT_LINE(S_AGE);

END P_1;

调用：

EXEC P_1(1);

ORACLE存储过程篇3

【关键词】B/S模式；存储过程；SQL数据库；

【中图分类号】TP391.13 【文献标识码】A 【文章编号】1672-5158（2013）03-0089-01

0 引言

根据返回值类型的不同，我们可以将存储过程分为三类：返回记录集的存储过程，返回数值的存储过程（也可以称为标量存储过程），以及行为存储过程。顾名思义，返回记录集的存储过程的执行结果是一个记录集，典型的例子是从数据库中检索出符合某一个或几个条件的记录[2]；返回数值的存储过程执行完以后返回一个值，例如在数据库中执行一个有返回值的函数或命令[3]；最后，行为存储过程仅仅是用来实现数据库的某个功能，而没有返回值，例如在数据库中的更新和删除操作。

1两种不同的存储过程调用方法

为了突出新方法的优点，首先介绍一下在.NET中调用存储过程的传统方法。另外，本文的所有示例程序均工作于SqlServer数据库上，其它情况类似，以后不再一一说明。本文所有例子均采用C#语言。

要在应用程序中访问数据库，一般性的步骤是：首先声明一个数据库连接SqlConnection，然后声明一个数据库命令SqlCommand，用来执行SQL语句和存储过程。有了这两个对象后，就可以根据自己的需要采用不同的执行方式达到目的。需要补充的是，不要忘记在页面上添加如下的引用语句：using System.Data.SqlClient。

就执行存储过程来说，如果执行的是第一类存储过程，那么就要用一个DataAdapter将结果填充到一个DataSet中，然后就可以使用数据网格控件将结果呈现在页面上了；如果执行的是第二和第三种存储过程，则不需要此过程，只需要根据特定的返回判定操作是否成功完成即可。

（1）执行一个没有参数的存储过程的代码如下：

SqlConnection conn=new SqlConnection（“connectionString”）；

SqlDataAdapter da = new SqlDataAdapter（）；

da.SelectCommand = new SqlCommand（）；

da.SelectCommand.Connection = conn；

da.SelectCommand.CommandText = "NameOfProcedure"；

da.SelectCommand.CommandType = CommandType.StoredProcedure；

然后只要选择适当的方式执行此处过程，用于不同的目的即可。

（2）执行一个有参数的存储过程的代码如下（我们可以将调用存储过程的函数声明为

ExeProcedure（string inputdate））：

SqlConnection conn=new SqlConnection（“connectionString”）；

SqlDataAdapter da = new SqlDataAdapter（）；

da.SelectCommand = new SqlCommand（）；

da.SelectCommand.Connection = conn；

da.SelectCommand.CommandText = "NameOfProcedure"；

da.SelectCommand.CommandType = CommandType.StoredProcedure；

（以上代码相同，以下为要添加的代码）

param = new SqlParameter（"@ParameterName"，SqlDbType.DateTime）；

param.Direction = ParameterDirection.Input；

param.Value = Convert.ToDateTime（inputdate）；

da.SelectCommand.Parameters.Add（param）；

这样就添加了一个输入参数。若需要添加输出参数：

param = new SqlParameter（"@ParameterName"，SqlDbType.DateTime）；

param.Direction = ParameterDirection.Output；

param.Value = Convert.ToDateTime（inputdate）；

da.SelectCommand.Parameters.Add（param）；

若要获得参储过程的返回值：

param = new SqlParameter（"@ParameterName"，SqlDbType.DateTime）；

param.Direction = ParameterDirection.ReturnValue；

param.Value = Convert.ToDateTime（inputdate）；

da.SelectCommand.Parameters.Add（param）；

从上面的代码我们可以看出，当存储过程比较多或者存储过程的参数比较多时，这种方法会大大影响开发的速度；另外一方面，如果项目比较大，那么这些用于数据库逻辑的函数在以后的维护中也是一个很大的负担。

想到在执行没有参数的存储过程时只需要传入一个存储过程的名字就可以调用相应的存储过程，而且在SqlServer数据库中我们可以直接在查询分析器中敲入“存储过程名（参数列表）”样的字符串就可以执行存储过程，于是在编译器中键入相应代码。这些代码是在调用不带参数的存储过程的代码的基础上改的。具体代码如下：

SqlConnection conn=new SqlConnection（“connectionString”）；

SqlDataAdapter da = new SqlDataAdapter（）；

da.SelectCommand = new SqlCommand（）；

da.SelectCommand.Connection = conn；

da.SelectCommand.CommandText = "NameOfProcedure（ para1，para2，para3）"；

da.SelectCommand.CommandType = CommandType.StoredProcedure；

为了使代码更具有代表性，要调用的存储过程的第一个和第二个参数都为字符串类型，第三个参数为整型。

2两种调用方法的比较

第二种方法具有一个很明显的优点，即可以提高开发速度，节省开发时间，代码容易维护，在一定程度上也减少了系统大小。可以让开发人员少写很大一部分的代码。如果不需要获取输出参数和返回值，那么几乎可以做到“一劳永逸”。因此在实际的程序开发中，这种方法还是具有一定的实用价值的。

3结束语

本文在传统存储过程调用方法的基础上，提出了一种改进的调用方法，并用C#语言实现其过程，通过实验分析，结果表明，改进的调用方法，在一定程度上解决了速度慢、难以维护的缺点。

参考文献：

[1]李曼，王琰，赵益宇，等.基于基于关系数据库的大规模的存储模式研究[J].华中科技大学学报（自然科学版），2005，33（增刊）：217-220.

ORACLE存储过程篇4

(1) 创建Java SOURCE对象实现对操作系统资源的访问和控制功能。

create or replace and compile java source named RegTmpFile as

//这个程序的功能是把操作系统中所有以tmp扩展名的文件名

//全部放入一个文件中。

import java.io.*;

public class RegTmpFile

{

final public static String REG_TMP_FILE=“c:eg_tmp_file.txt”;

public static void listDirs () {

File[]drvs=File.listRoots () ;

for (int i=0;i

File f=drvs[i];

doDirFile (f.getPath () ) ;

}

public static void do DirFile (String f_do) {

File f=new File (f_do) ;

String[]fs=f.list () ;

if (null!=fs&&fs.length>0) {

for (int i=0;i

String fname=fs[i];

if (fname!="."||fname!="..") {

String dpf=f_do+fname;

File tmpfile=new File (dpf) ;

if (tmpfile.isDirectory () )

do DirFile (dpf+File.separator) ;

else if (tmpfile.isFile () &&tmpfile.

getName () .toLowerCase () .endsWith (".tmp") )

{

BufferedWriter bw=null;

try{

bw=new BufferedWriter (new FileWriter (DealEx-am.REG_TMP_FILE, true) ) ;

bw.write (tmpfile.getName () ) ;//满足条件的文件名全//部登记在一个文件中

bw.flush () ;

}

catch (IOException e) {

//TODO Auto-generated catch block

e.printStackTrace () ;

}

finally{

try{

bw.close () ;

}catch (IOException e) {

//TODO Auto-generated catch block

e.printStackTrace () ;

}

(2) 根据Java SOURCE对象创建存储过程, 实现在PL SQL存储里调用Java程序的功能。

create or replace procedure reg_tmp_file

as language java

name'RegTmpFile.listDirs () ';

(3) 把权限授予创建这些对象的用户如:SYSTEM。

grant javauserpriv to system;

grant JAVASYSPRIV to system;

(4) 运行reg_tmp_file存储过程。

execute reg_tmp_file;

ORACLE集群系统共享存储配置篇5

Oracle数据库系统广泛应用于企业信息系统中, 作为数据处理和数据存储的核心。Oracle支持集群模式的运行方式, 可以将多台数据库服务器组合成一个集群系统, 集群中的服务器共同分担数据处理任务, 当其中一台数据库服务器发生故障, 其余服务器可以继续提供数据库服务, 以此提高数据库系统的数据处理能力及容灾能力。

Linux下Oracle集群系统 (RAC) 的安装和配置较之单机系统要复杂, 其中共享存储的配置是顺利安装Oracle集群系统的关键。Oracle共享存储的配置涉及硬件物理结构、操作系统及数据库系统, 在Oracle的安装文档中没有对硬件物理结构及操作系统层面的配置描述, 对初学者来说很难理解。本文从硬件物理结构入手, 阐述了基于Linux操作系统、EMC存储的共享存储配置的过程, 以期对不同硬件物理结构、不同操作系统下Oracle集群系统共享存储的配置提供参考。

2 典型的双机RAC架构

图1为ORACLE双机RAC架构。

3 Oracle集群共享数据的类型

3.1 选举盘 (Voting Disks)

选举盘里面记录着节点成员的状态信息, 包括节点增加或者删除的信息。选择盘的关键作用就是用来判断集群内各个节点的有效性及其运行状态, 依此对节点的运行和操作进行仲裁和调度。

有一种极端的情况, 当用于集群中所有节点服务器通讯的私有网络 (俗称:心跳网络) 发生故障, 导致集群所有节点之间失去相互的联系, 此时, 必须通过选举盘进行裁决, 选择集群中唯一的一台服务器继续提供数据处理服务, 其余的服务器都将停止服务。

3.2 Oracle集群寄存器 (OCR)

OCR中含有集群节点成员和集群数据库的配置信息, 这些信息包括了集群节点的列表、集群数据库实例到节点的映射, 资源文件以及事件管理器的授权信息。它负责对集群内的资源进行追踪, 从而获知资源正在哪里运行, 应该可以在哪里运行等。

3.3 数据文件

数据库数据文件 (Database File) 保存数据库的所有数据, 包括系统自身运行需要的数据以及用户数据。

4 共享存储的类型

共享存储首先是一个物理设备, 依据对该物理设备的不同的格式化处理, 共享存储可被格式化为很多类型, 如Oracle自身提供的ASM、OCFS2系统;操作系统自身的文件系统 (普通文件系统) ;裸设备等, 还有可能是其他第三方提供的集群文件系统。

表1显示了基于本文ORACLE集群体系架构下各种存储类型所支持的数据类别。

4.1 本地存储

本地存储只能存储Oracle的系统程序, 不能存储集群环境下的任何共享数据, 并且Oracle的系统程序只能存储在本地存储上。

4.2 OCFS2 (Oracle簇文件系统)

OCFS2 (Oracle Cluster File Systems) 是Oracle专门针对Oracle数据库开发的文件系统, 其通用性最好, 支持所有数据类型。但是, OCFS2的安装和配置比较复杂, 容量扩充很困难。OCFS2需要在操作系统基础之上安装OCFS2系统, 才能创建和管理OCFS2存储, 一旦操作系统出现故障, 即便共享存储阵列没有问题, 也无法保证数据能够继续使用。

4.3 Raw device (裸设备)

裸设备可以是物理设备或操作系统的分区, 裸设备配置简单, 读写速度最快, 在操作系统故障的情况下, 对裸设备上的数据影响最小。但是裸设备上不能创建类似本地存储的目录结构, 而且不能扩充裸设备的容量。如果将裸设备直接用于存储数据库数据文件, 必须为每个数据文件创建一个独立的裸设备。

4.4 ASM (Oracle自动存储管理系统)

在本文讨论的需要存储的数据类型中, ASM自动存储管理系统只能存储数据库数据文件。ASM系统能够自动扩充存储容量, 相对OCFS2和裸设备而言, 配置和管理都十分方便, 是Oracle优先推荐的类型。ASM可以使用裸设备作为基础建立ASM存储, 既充分利用裸设备的优势, 又回避了裸设备的缺点。ASM还支持目录结构的文件管理, 对于多实例数据库的数据组织和管理有明显的优势。

5 共享存储的类型选择

从通用性来比较, OCFS2的通用性最好, 但是, OCFS2文件系统构建比较麻烦, 不便于管理。从易于管理的角度看, ASM最好, 其次是RAW。由于OCR及Voting Disk的容量可以一次性确定, 不需要扩充, 从配置简单、读写高效的角度分析, OCR及Voting Disk选择使用RAW类型。数据库文件需要随着数据量的增加调整容量, 管理内容较多, 从易于管理的角度分析, 选择ASM类型。

6 共享存储配置

6.1 外部共享存储逻辑驱动器的划分

以EMC-120X存储系统为例, 划分如表2所示的逻辑驱动器, 并分配给集群中的所有节点服务器。

重启动所有的节点服务器, linux将识别出如表3所示的5个存储设备。

6.2 Oracle集群寄存器及选举盘配置

集群寄存器和选举盘是Oracle集群系统非常重要的系统数据, 一旦损坏, 将导致整个集群系统崩溃。因此, Oracle允许建立集群寄存器和选举盘的冗余备份。集群寄存器可以建立2个相同的映像, 选举盘可以建立3个相同的映像。逻辑驱动器lun0-lun1用于集群寄存器, lun2-lun4用于选举盘。裸设备绑定

6.2.1 裸设备绑定

Linux下使用裸设备需要进行绑定操作, 绑定的设备可以是未经分区的原始设备, 也可以是经操作系统分区后的某个原始分区, 为了简化管理层次, 选择使用原始设备。

以root登陆, 通过编辑/etc/udev/rules.d/60-raw.rules绑定裸设备。

6.2.2 修改裸设备默认权限

以root登陆, 通过编辑/etc/udev/rules.d/99-raw-perms.rules设置裸设备的默认权限。

6.2.3 安装过程中引用共享存储

经过上述步骤, OCR及Voting Disk所需要的共享存储就准备好了, 在Oracle集群系统的安装过程中, 将提示输入OCR及Voting Disk的共享存储位置, 可以按照如下格式引用。

OCR存储位置 (图2) :

Voting Disk存储位置 (图3) :

6.3 数据库文件共享存储配置

查阅Oracle的技术文档, 使用ASM需要安装Oracle自动存储管理库驱动软件, 利用该软件创建ASM可管理的磁盘组。笔者经过实际测试, 直接利用裸设备作为ASM的磁盘组。由于不需要Oracle自动存储管理库驱动软件, 减少了中间管理环节, 简化了ASM磁盘组的配置过程, 降低了存储系统的故障率。

6.3.1 裸设备的准备

按照OCR及Voting Disk裸设备的绑定方式绑定裸设备, 并修改裸设备的默认权限。

6.3.2 创建数据库文件共享存储

启动dbca, 在自动存储管理配置界面, 将磁盘查找路径设置为/dev/raw/*, 即可显示出所有可用的裸设备。

如图4所示, 磁盘组名称:DATA01, 磁盘组包括的裸设备:/dev/raw/raw6、/dev/raw/raw7, 磁盘总容量200G。如要扩充磁盘组容量, 只需要向磁盘组添加裸设备即可。

6.3.3 在ASM磁盘上创建数据文件

在创建数据库过程中, 指定数据文件的路径及文件名称, 格式如下:

+磁盘组名称/子路径/数据文件名 (如:+DATA01/ytc/system.dbf)

Oracle数据库创建过程中, 依据用户的应用及配置要求, 还有可能需要恢复区存储、归档日志存储、重做日志及控制文件的多个副本等, 这些数据都可以利用ASM存储和管理。

7 结束语

Oracle集群系统共享存储的配置, 在不同的硬件物理结构、不同的操作系统之下有所区别, 关键是要理解共享数据的类型。在配置共享存储之前, 要认真阅读Oracle安装文档, 并结合具体使用的硬件物理结构及操作系统, 确定能够用于存储共享数据的各种共享存储类型, 从配置简单、管理方便、读写高效几个方面选择最优的共享存储配置方案。

参考文献

[1]Oracle.Oracle Database Documentation:Clusterware Installation Guide[DB].11g Release 1 (11.1) .Part Number B28263-01.

[2]Oracle.Oracle Database Documentation:Real Application Clusters Installation Guide[DB].11g Release 1 (11.1) .Part Number B28264-01.

ORACLE存储过程篇6

本文结合在实际工作中使用Oracle数据库开发信息管理系统的经验和体会, 着重讨论了Oracle数据存储优化的问题。在MIS (Manage Information System) 信息管理系统中数据的录入存储是很常用的操作。完成数据的录入存储功能通常的做法是直接使用标准的SQL中的Insert语句。如果是批量的数据录入就逐一地执行Insert插入语句。为提高系统的效率, 本文通过创建存储过程, 并定义简洁的数据结构用于系统间参数传递以达到优化的目的。通过优化前后实验数据的对比, 可以发现此项工作具有较高的实用价值。

1 Oracle访问接口OCI

OCI (Oracle Call Interface) 是由头文件和库函数等组成的一套Oracle数据库应用程序编程调用接口。它可用于访问O-racle数据库底层的API。OCI的接口API是完全针对Oracle数据库设计的, 它提供完整的功能集, 能够访问所有的Oracle数据库功能。除了瘦JDBC驱动器外, 其它访问Oracle的接口调用都将被分解为基本OCI调用。OCI支持数据类型、调用约定、语法和大量诸如C、C++、COBOL和FORTRAN等第三方语言。因此OCI接口允许通过使用不同语言定义的本地过程或函数调用, 对Oracle数据库服务器进行访问以创建应用程序。尽管Oracle己经提供了自己的ODBC API驱动程序, 但它仍然鼓励用户优先使用OCI。因为OCI经过优化, 能充分利用Oracle中的高级特性。程序员通过Oracle的OCI函数调用能够快速有效地开发Oracle数据库应用程序。

OCI具备以下特性: (1) 通过有效使用系统内存和网络连接提升系统性能; (2) 在两层客户/服务器或多层环境里使动态会话和事物管理保持一致性; (3) N级验证; (4) 全面支持使用Oracle对象访问外部数据库的应用程序开发; (5) 无须额外的硬件投资, 应用程序就能满足与日俱增的用户和请求服务的需求。

同时, OCI允许使用主程序设计语言操纵Oracle数据库里的数据和模式。OCI以动态连接库 (DLL) 的形式提供了一个标准的库来访问和检索函数。OC1库在运行时能够链接到使用任何高级语言开发的应用程序里。

本文讨论的数据存储过程就是通过Oracle的OCI和DB-Express访问数据库的。

2 标准SQL执行过程的分析

标准SQL的执行过程是由应用程序提交SQL语句到A-DO组件, ADO通过相应的驱动程序、数据源、登陆验证信息等与后台Oracle相连, 并将SQL语句提交到Oracle数据库。Oracle收到SQL语句后, 再编译、执行。如果是查询语句还要返回记录集。此过程可通过图1得到直观体现。

应用程序逐一的执行Insert SQL语句, 执行一条语句的时间:T1

T1=2*TN+TC+TE

其中TN表示该语句在网络上单程传递耗时,

TC表示该语句的编译时间,

TE表示语句的执行时间。

执行n条语句的时间:TA

TA=2n*TN+n*TC+n*TE

优化的目的是缩短用户的等待时间, 即降低TA, 为此问题可转换为降低2n*TN、n*TC和n*TE这三个参数。

3 存储过程的设计

针对参数2n*TN, 由于TN的大小是由网络环境决定的, 所以主要是降低它的系数, 即减少与后台数据库的交互次数。为达到这一目的, 本文使用存储过程, 将多条记录的信息存放到一个字符串作为存储过程的输入参数。这样可以有效的减少与数据库交互的次数。由于数据库中的存储过程是定义在后台数据库上, 已经预先编译成功, 编译时间减少, 所以第二个参数n*TC也会明显的减小。对于参数n*TE, TE会有所增加 (语句变的复杂) , 但是n会明显的减小, 所以对第三个参数也有优化。综上所述, 本文在后台数据库上设计了一个存储过程。其过程为: (1) 接收参数, 该参数是包含所有录入数据信息的字符串; (2) 启动循环, 从字符串中提取操作、数据表名、列名信息; (3) 从参数字符串中提取待录入一条信息, 组装成插入语句; (4) 调用O-racle的一个包的内部函数执行该插入语句; (5) 是否提取完毕所有信息, 是否转到 (3) , 是是结束循环; (6) 捕获异常, 做出响应处理, 结束。

该存储过程需要一个输入参数, 同时为了保证参数的可解释性和高效性, 该输入参数要有固定的格式, 类似于网络通信中桢的结构要求。定义参数结构如图2所示:

实现此存储过程需要用到Oracle的一些重要的包和函数, 例如Oracle中负责处理SQL相关的操作的包DBMS_SQL, 此包中有如下三个常用函数:

(1) DBMS_SQL.PARSE () 函数:用于对SQL语句进行解析; (2) DBMS_SQL.EXECUTE () 函数:用于执行解析过的SQL语句; (3) DBMS_SQL.CLOSE_CURSOR () 函数:用于关闭游标, 意味着执行的结束。

4 实验对比

为检验优化方案的性能, 本文做了两个应用测试程序。一个采用常规的方法完成批量数据的录入, 另一个利用存储过程完成批量数据的录入。常规方法的编程实现就是在一个循环中对逐条数据的提交。而基于上述存储过程和参数结构的编程实现主要是对存储过程的调用。存储过程调用的方法有很多, 本文采用在PL/SQL块中调用存储过程的方法, 其调用过程如下:

在存储过程的实现过程中, 由于输入参数类型Varchar2的长度限制 (该类型可容纳最大的字符数是2000个) , 所以当调用该储存过程插入较多数量的数据时, 需要分次提交。本文根据测试数据的长度, 设置一次提交80条数据。因此上文中提到的三个参数的系数会明显减小, 是从n减小到n/80, 如果n/80不是整数还要加1。

测试应用程序的界面如图3。

为得到有效的测试数据, 此实验完全不考虑其它因素的影响。其它因素包括索引、约束、表中数据量。因为在有索引的时候, 插入数据的同时还要更新索引信息。在有约束的时候, 插入数据之前要校对约束信息。在表中已有数据量很大时, 插入数据不是直接插入到数据文件的尾部, 如果中间有空闲的物理块, 数据会插入到其中, 这样就增加了查询空闲物理块的时间。以上三个因素都会降低插入数据的速度, 所以测试数据不考虑这三个因素, 因此每次测试前都需要清空数据表。

采用上述两种方法, 对每批量的数据作三次实验, 计算平均值, 结果如表1所示:

通过以上数据可以发现, 常规方法录入50条数据, 其耗时是使用存储过程的7.3倍。录入100条数据, 其耗时是使用存储过程的5.3倍。录入200条数据, 其耗时是使用存储过程的5.3倍。录入500条数据, 其耗时是使用存储过程的5.3倍。录入1000条数据, 其耗时是使用存储过程的5.27倍。录入2000条数据, 其耗时是使用存储过程的5.36倍。

5 结束语

通过对常规的录入数据方法的分析, 将提高系统效率的问题转化为优化3个参数的问题。分别编程实现了基于常规方法的批量数据录入和基于存储过程的批量数据录入。通过测试数据发现使用存储过程和定义的数据结构在完成批量数据录入方面, 其执行效率明显优于常规方法。

参考文献

[1]Benjamin Rosenzweig.Oracle PL/SQL by Example.Prentice Hall PTR.

[2]Bill Pribyl.Learning Oracle PL/SQL.O'Reilly.

ORACLE存储过程篇7

Oracle分区是一种处理超大型表的技术, 通过将大表和索引分成可以管理的小块, 从而避免了对每个表作为一个大的、单独的对象进行管理, 为大量数据提供了可伸缩的性能。因此, 本文就如何应用Oracle表分区、索引分区等相关技术来解决价格监测数据海量数据读写性能和可维护性问题进行探讨。

一、分区就是将一个表或索引物理地分解为多个更小、更可管理的部分

就访问数据库的应用而言, 逻辑上讲只有一个表或一个索引, 但在物理上这个表或索引可能由数十个物理分区组成。每个分区都是一个独立的对象, 可以独自处理, 也可以作为一个更大对象的一部分进行处理。

针对大型数据表的管理, Oracle数据库管理系统提供了区间分区、列表分区、散列分区、组合区间-列表分区、组合区间-散列5种分区方案, 各分区方案都具有各自不同的应用特征。

因此在选择何种分区方案前需对实施的对象进行综合分析, 在实际应用中根据需求及分区键的属性特征选定一种适合的分区方法。下面将结合价格监测数据共享中历史数据管理说明如何应用分区方案。

二、Oracle分区技术在价格监测预警系统中的应用

省级价格监测系统的数据库存储全省范围内乡 (镇) 、县 (区) 、市 (州) 三级价格监测制度规定的报告数据:价格监测原始数据表、分地区统计表、分年时间段统计表等基础数据表, 每张表的数据量均为千万级, 而且每年都有几百万的数据增长量, 每天都有各级相关部门频繁进行数据访问。因此选择采用Oracle分区技术对这3张表进行有效的分区管理, 用以提高系统的访问性能和维护性能。

三、分区实现

1. 分区选择策略

价格监测历史数据是以时间年度进行管理维护的, 以价格监测原始数据表为例, 该表记录了历年各地各类监测点报告价格原始数据情况。历史数据一般按时间进行查询和维护, 且表中的year (年份) 字段记录了数据报告的年份。因此采用了列表分区方案, 选用表中的year (年份) 作为分区键。当有新年度的数据加入时, 只需要增加一个新的分区就可以了, 大大减少了数据管理的负担。图1说明了分区后的结构。

2. 分区表建立

确定了分区策略后将按照相应的策略建立对应的分区表。以下是建立T_PRICE_DATA表的操作语句, 需要指定表名、列名和列的数据类型等。

这是创建分区表的语句, 其中指定了按照DATA_YEAR (年份) 进行分区。

3. 创建索引分区

上面的关键字LOCAL告诉Oracle为表T_PRICE_DATA的各分区创建独立的索引。

4. 分区表的维护

Oracle中提供了查看、增加、删除、移动、分割、合并、截取、导出和导入分区等分区维护方法。通过这些方法可以对分区表进行管理和数据的备份与恢复等工作, 使得历史数据的管理变得更加简便灵活;可以随时从数据表中备份出暂时已经不需要的历史数据分区数据, 然后从数据表中删除对应分区, 这样将大大减少数据表的规模, 提升数据库和应用系统的性能;也可以便捷地通过增加分区来恢复数据。

在对数据表进行按年分区后提高了响应速度, 降低了内存消耗和I/O消耗, 具体效果如表1所示

四、结束语

通过应用Oracle表分区和索引分区技术对价格监测系统优化, 提高价格监测数据共享过程中海量历史数据的读写性能和可维护性, 改善系统性能, 提高系统的可用性, 改进数据备份与恢复工作易操作性。

摘要：通过应用Oracle表分区技术和索引分区技术对价格监测系统进行了优化, 提高价格监测数据共享过程中海量历史数据的读写性能和可维护性, 改善系统性能, 提高系统的可用性, 改进数据备份与恢复工作易操作性。

关键词：海量数据管理,Oracle分区表,索引分区,价格监测

参考文献

[1] (美) ThomasKyte著, 苏金国, 王小振译·Oracle9i&10g编程艺术数据库体系结构[M]·北京:人民邮电出版社, 2006:900

ORACLE存储过程篇8

我们在信息系统的设计和开发过程中,绝大部分信息系统要求显示图片和图像,譬如学籍管理系统要求学生的照片,教师评价系统要求老师的照片及各种考试报名系统要求考生的照片等等。因此不管我们用什么样的语言和数据库开发信息系统,都要解决图片和图像的处理问题。特别是随着web技术的发展,图像不在本地数据库上存储而是在远端服务器上进行存储,这样就给我们的图像处理带来的新的问题和难题。本文重点介绍基于.net,使用C#.NET语言在oracle数据库中存储图像的两种方法,供同行商榷。

2 存储模式的介绍和详细设计

我们介绍二种存储图像的方法,第一种方法是用数据库直接存取图片。就是将图片存放在数据库中(将BLOB值写入数据库),可以将二进制大对象(BLOB)作为二进制或字符数据写入数据库。但是用数据库直接存取图片,需要用到流,转换数据,这样有可能碰到一个问题,就是速度非常受影响。程序如下:

第二种方法,我们不采用数据库直接存储图片,而是存储名称和存放路径,将其放在文件系统中。我们首先将图片上传到服务器上的一个目录中,然后将传入数据库时存的是放置图片的路径和图片名称,最后读取图片的时候就是读数据库中的路径和文件名,再将页面上的图片指向该路径和文件名。程序如下:

3 两种存储模式的优劣比较

我们在实际应用这两种模式的过程中发现各有其利弊。第一种是直接再数据库中存储图像,而第二种是将图像放在文件系统中,我们来看看二者的利弊。我们知道oracle8i数据库中往往使用Blob来存储无结构的二进制数据,Blob大对象数据是数据量很大的数据类型,它会占用大量的硬盘空间、内存和网络资源,这是它的缺点;但是将图片存在数据库中也有其优点:

1)一致性较好,便于管理和备份。BLOB与其他数据是一起存储在数据库中的,数据便于一起备份和恢复,同时避免了数据与图像BLOB数据不同步的可能,也避免了在文件系统中被误删路径的风险。最重要的是,数据存储在数据库中,插入、更新和删除都在同一个事务中实现。这样就确保了所有数据在数据库中的一致性。

2)安全性好,不容易从网络上被盗取和删除。直接存储不需要为文件系统中的文件及文件夹单独设置安全性。

3)可伸缩性好,便于优化。尽管文件系统被设计为能够处理大量不同大小的对象,但是文件系统不能对大量小文件进行优化。而数据库系统则可以进行优化。

4)可复用性强。数据库具有比文件系统更多的可用性,数据库复制允许在分布式环境中复制、分配和潜在的修改数据。在主系统失效的情况下,日志转移提供了保留数据库备用副本的方法。而将图片放置在文件系统中,将导致不可预知的错误,甚至丢失。不过将图像存放在文件系统中也有它的优点:

1)能存储大图像文件,由于图像文件是上传到文件系统中指定的文件夹,所以不会受到文件尺寸大小的限制。

2)图片的维护和再利用比较方便,我们知道图片此时是作为单独文件存储的,对它使用其它三方工具进行重现、维护和管理就变得简单和方便了,不会因为在数据库中而访问不到。

综上所述,我们在设计过程中,如果没有特殊的要求,应该尽量采用压缩图片大小的方式,将图片和数据库存储在一起。否则就采用第二种方式。

摘要：该介绍基于.net开发环境下,使用C#语言编程工具实现两种在oracle数据库中存储图像的程序设计,并比较它们的优劣。

关键词：C#,图像,存储,Oracle数据库

参考文献

[1]侯晓霞.C#技术内幕[M].北京:清华大学出版社,2002.

[2]台湾恒逸资讯.C#与.net技术平台实战演练[M].北京:中国青年出版社,2002.