ORACLE存储过程（精选7篇）
ORACLE存储过程 第1篇
文中采用JDK版本即JDK1.6版, JDK1.6官方下载地址:http://www.java.net/download/jdk6/6u10/promoted/b32/binaries/jdk-6u10-rc2-bin-b32-windows-i586-p-12_sep_2008.exe, 下载好后可以按默认路径安装, 然后进行Jdk环境变量的配置, 可以按以下步骤进行:首先找到jdk的bin目录, 例如默认路径安装为:C:Program FilesJavajdk1.6.0_18, 将其路径名复制, 然后打开“我的电脑”的“属性”窗口, 找到“高级”, 点击“环境变量”按钮, 找到系统变量中的“PATH”, 双击“PATH”记录, 弹出“编辑系统变量”窗口, 在变量值的最后加上分号和jdk的bin目录, 如:;C:Program FilesJavajdk1.6.0_18bin。然后新建变量CLASSPATH, 变量值为C:ProgramFilesJavajdk1.6.0_18jrelibrt.jar。最后新建变量JA-VA_HOME, 变量值为C:Program FilesJavajdk1.6.0_18。
(2) Oracle的简单介绍和oracle的jdbc驱动包 (classes12.jar) 的配置
Oracle的关系数据库, 目前是大客户首选的数据库, 所以在校计算机专业的大学生、社会在职计算机人员学习好掌握好Oracle数据库对以后就业、高薪是一个很好的敲门砖。文中采用的是Oracle10g, Oracle10g下载安装好后, 点击“开始”“运行”输入cmd后, 按“确定”。弹出MS-DOS窗口后, 输入:sqlplus scott/tiger, 出现“SQL>”提示符后输入:SQL>SE-LECT*FROM emp;看有没有记录, 有就代表Oracle安装成功了。Java程序要调用Oracle数据库还必须配置jdbc驱动包 (classes12.jar) , Oracle10g安装好后, jdbc驱动包 (classes12.jar一般在..jdbclib目录下, 如本文oracle10g的安装目录为E:oracleproduct10.2.0db_1jdbclib, 所以jdbc驱动包 (classes12jar) 的配置为双击前面所建的的变量CLASSPATH, 增加变量值:;E:oracleproduct10.2.0db_1jdbclibclasses12.jar。如果没配置好jdbc驱动包就会出现java.lang.ClassNotFoundException oracle.jdbc.driver.OracleDriver异常。
(3) 编写Oracle存储过程
首先建立一个返回游标, 用于返回记录集到Java程序中, 语句如下:
如图1所示。
然后创建一个存储过程, 主要功能是取出部门号小于某个值的员工信息, 当然在具体编程的时候用到的SQL语句要复杂得多, 但原理还是相同的。语句如下:
如图2所示。
(4) 编写一个Java程序并调用Oracle存储过程
下面是完整的Java程序代码, 文件名为JdbcOracleTest.java, 通过Javac JdbcOracleTest.java后, 再Java JdbcOracleTes可以输出相应的值。如图3所示。
ORACLE存储过程 第2篇
分类： PL/SQL系列 2009-09-24 15:08 253人阅读 评论(0)收藏 举报
声明：
以下的例子不一定正确，只是为了演示大概的流程。
一：无返回值的存储过程 存储过程为：
CREATE OR REPLACE PROCEDURE TESTA(PARA1 IN VARCHAR2,PARA2 IN VARCHAR2)AS BEGIN
INSERT INTO HYQ.B_ID(I_ID,I_NAME)VALUES(PARA1, PARA2);END TESTA;
然后呢，在java里调用时就用下面的代码： package com.hyq.src;
import java.sql.*;import java.sql.ResultSet;
public class TestProcedureOne {
public TestProcedureOne(){
}
public static void main(String[] args){
String driver = “oracle.jdbc.driver.OracleDriver”;
String strUrl = “jdbc:oracle:thin:@127.0.0.1:1521: hyq ”;
Statement stmt = null;
ResultSet rs = null;
Connection conn = null;
CallableStatement cstmt = null;
try {
Class.forName(driver);
conn = DriverManager.getConnection(strUrl, “ hyq ”, “ hyq ”);
CallableStatement proc = null;
proc = conn.prepareCall(“{ call HYQ.TESTA(?,?)}”);
proc.setString(1, “100”);
proc.setString(2, “TestOne”);
proc.execute();
}
catch(SQLException ex2){
ex2.printStackTrace();
}
catch(Exception ex2){
ex2.printStackTrace();
}
finally{
try {
if(rs!= null){
rs.close();
if(stmt!=null){
stmt.close();
}
if(conn!=null){
conn.close();
}
}
}
catch(SQLException ex1){
}
}
} }
二：有返回值的存储过程（非列表）
当然了，这就先要求要建张表TESTTB,里面两个字段（I_ID，I_NAME）。
存储过程为： CREATE OR REPLACE PROCEDURE TESTB(PARA1 IN VARCHAR2,PARA2 OUT VARCHAR2)AS BEGIN
SELECT INTO PARA2 FROM TESTTB WHERE I_ID= PARA1;END TESTB;
在java里调用时就用下面的代码： package com.hyq.src;
public class TestProcedureTWO {
public TestProcedureTWO(){
}
public static void main(String[] args){
String driver = “oracle.jdbc.driver.OracleDriver”;
String strUrl = “jdbc:oracle:thin:@127.0.0.1:1521:hyq”;
Statement stmt = null;
ResultSet rs = null;
Connection conn = null;
try {
Class.forName(driver);
conn = DriverManager.getConnection(strUrl, “ hyq ”, “ hyq ”);
CallableStatement proc = null;
proc = conn.prepareCall(“{ call HYQ.TESTB(?,?)}”);
proc.setString(1, “100”);
proc.registerOutParameter(2, Types.VARCHAR);
proc.execute();
String testPrint = proc.getString(2);
System.out.println(“=testPrint=is=”+testPrint);
}
catch(SQLException ex2){
ex2.printStackTrace();
}
catch(Exception ex2){
ex2.printStackTrace();
}
finally{
try {
if(rs!= null){
rs.close();
if(stmt!=null){
stmt.close();
}
if(conn!=null){
conn.close();
}
}
}
catch(SQLException ex1){
}
}
} }
}
注意，这里的proc.getString(2)中的数值2并非任意的，而是和存储过程中的out列对应的，如果out是在第一个位置，那就是proc.getString(1)，如果是第三个位置，就是proc.getString(3)，当然也可以同时有多个返回值，那就是再多加几个out参数了。
三：返回列表
由于oracle存储过程没有返回值，它的所有返回值都是通过out参数来替代的，列表同样也不例外，但由于是集合，所以不能用一般的参数，必须要用pagkage了.所以要分两部分，1，建一个程序包。如下：
CREATE OR REPLACE PACKAGE TESTPACKAGE AS
TYPE Test_CURSOR IS REF CURSOR;end TESTPACKAGE;
2，建立存储过程，存储过程为：
CREATE OR REPLACE PROCEDURE TESTC(p_CURSOR out TESTPACKAGE.Test_CURSOR)IS BEGIN
OPEN p_CURSOR FOR SELECT * FROM HYQ.TESTTB;END TESTC;
可以看到，它是把游标（可以理解为一个指针），作为一个out 参数来返回值的。在java里调用时就用下面的代码： package com.hyq.src;import java.sql.*;
import java.io.OutputStream;import java.io.Writer;
import java.sql.PreparedStatement;import java.sql.ResultSet;import oracle.jdbc.driver.*;
public class TestProcedureTHREE {
public TestProcedureTHREE(){
}
public static void main(String[] args){
String driver = “oracle.jdbc.driver.OracleDriver”;
String strUrl = “jdbc:oracle:thin:@127.0.0.1:1521:hyq”;
Statement stmt = null;
ResultSet rs = null;
Connection conn = null;
try {
Class.forName(driver);
conn = DriverManager.getConnection(strUrl, “hyq”, “hyq”);
CallableStatement proc = null;
proc = conn.prepareCall(“{ call hyq.testc(?)}”);
proc.registerOutParameter(1,oracle.jdbc.OracleTypes.CURSOR);
proc.execute();
rs =(ResultSet)proc.getObject(1);
while(rs.next())
{
System.out.println(“
}
}
catch(SQLException ex2){
ex2.printStackTrace();
}
catch(Exception ex2){
ex2.printStackTrace();
}
finally{
try {
if(rs!= null){
rs.close();
if(stmt!=null){
stmt.close();
}
if(conn!=null){
conn.close();
}
}
}
catch(SQLException ex1){
}
}
} }
四。Hibernate调用存储过程
Connection con = session.connect();
CallableStatement proc = null;
con = connectionPool.getConnection();
proc = con.prepareCall(“{ call set_death_age(?, ?)}”);proc.setString(1, XXX);
proc.setInt(2, XXx);...proc.execute();
session.close();
在Hibernate中调用存储过程的示范代码--
如果底层数据库（如Oracle）支持存储过程，也可以通过存储过程来执行批量更新。存储过程直接在数据库中运行，速度更加快。在Oracle数据库中可以定义一个名为batchUpdateStudent()的存储过程，代码如下：
create or replace procedure batchUpdateStudent(p_age in number)as begin update STUDENT set AGE=AGE+1 where AGE>p_age;end;以上存储过程有一个参数p_age，代表学生的年龄，应用程序可按照以下方式调用存储过程： tx = session.beginTransaction();Connection con=session.connection();String procedure = “{call batchUpdateStudent(?)}”;CallableStatement cstmt = con.prepareCall(procedure);cstmt.setInt(1,0);//把年龄参数设为0 cstmt.executeUpdate();tx.commit();在以上代码中，我用的是Hibernate的 Transaction接口来声明事务，而不是采用JDBC API来声明事务。
存储过程中有一个参数p_age，代表客户的年龄，应用程序可按照以下方式调用存储过程：
代码内容
tx = session.beginTransaction();Connection con=session.connection();
String procedure = “{call batchUpdateCustomer(?)}”;
CallableStatement cstmt = con.prepareCall(procedure);
cstmt.setInt(1,0);//把年龄参数设为0
cstmt.executeUpdate();
tx.commit();
CREATE procedure selectAllUsers DYNAMIC RESULT SETS 1 BEGIN
DECLARE temp_cursor1 CURSOR WITH RETURN TO CLIENT FOR
SELECT * FROM test;
OPEN temp_cursor1;END;
映射文件中关于存储过程内容如下
............
{ ? = call selectAllUsers()}
{ ? = call selectAllUsers()} 也可以写成{ call selectAllUsers()}，如果有参数就写成
{ ? = call selectAllUsers(?,?,?)}
代码中对query设置相应位置上的值就OK Java调用关键代码如下
Session session = HibernateUtil.currentSession();
Query query = session.getNamedQuery(“selectAllUsers”);
List list = query.list();
System.out.println(list);
要求你的存储过程必须能返回记录集，否则要出错
如果你的存储过程是完成非查询任务就应该在配置文件用以下三个标签
ORACLE存储过程 第3篇
1 存储过程
1.1 存储过程的概念
存储过程的本质是数据库中的一个对象,是SQL语言的集合。当编译好的代码在执行一次之后就会高速缓冲保存在oracle数据库能够将里,再次使用时不需要进行二次编译就能直接调用。因此存储过程能帮助数据库提高查询和存储的有效速度,并能有效降低数据库系统较复杂这个性能,提升整个系统的可维护性、可伸缩性、安全性以及重用性,从而达到提升整个应用系统性能的目的。此外,他也属于封装重复操作的一种方法,因此不管是编程能力还是提供参数变量能力都是极强的。
1.2 存储过程的分类
我们可以将oracle数据库的存储过程分为以下四种 :分别是触发器、存储函数、存储过程、对象类型。(1)触发器 :触发器是以数据表为基础而创建的,是一种能够为程序员提供完整数据信息的方法,它可以和一个或者多个数据修改操作进行相应的联系,如 :删除、更新、插入等。触发器与普通存储过程有一定的不同性,主要表现在执行过程,它的执行是在条件设定的基础上自行完成的。(2)存储函数 :从实质上看,存储函数和存储过程具有很大程度的相似性,但是两者的不同之处在于 :存储函数可以向调用者返回值,相同之处在于 :用户也可以借助于名称来对存储函数在“顶级”位置、PL/SQL包以及SQL上进行调用。(3)存储过程 :顶级的存储过程可以给客户提供不一样的指令,如使用任何的商业逻辑扩展SQL语句,然后借助于名称来对存储过程在“顶级”位置、PL/SQL包以及SQL上进行调用。(4)对象类型 :从9i版本开始,oracle数据库就已经开始在存储过程的开发工作中引入面向对象编程思想,在对象类型中,对变量操作状态进行维护,对存储的这些信息数据以及相关操作进行针对性的定义,并且具有较好自身数据保护性,及时在函数和过程中也不会被外来程序锁修改。
1.3 存储过程的优点
存储过程的优势可以从以下五个方面进行概述 :(1)可移植性 :完全不需处理编译版本或者操作系统中兼容性问题的出现,因为在oracle数据库中不需要考虑到平台等方面的问题,任何情况下都可以使用存储过程,而且只要确保该操作平台支持运行oracle数据库,就可以不需要任何改变地调用存储过程。(2)安全性 :存储过程的安全性主要表现在它能够阻止客户对oracle数据库进行的不恰当操作,系统管理员还可以使用权限来科学管理存储过程的进行,尽可能避免存储过程或者数据库中的数据出现被非授权用户访问的情况。(3)执行效率高 :随着用户数量的不断增加,存储过程对有关于SQL的需求也在不断增加,这种情况很快就会导致在运行过程中网络出现“瓶颈”,影响网络的正常运行。在对服务器中所有执行的SQL语句进行调用时也可以调用存储过程,这样在很大程度上可以减少网络出现拥挤,腾出更大的空间。(4)可维护性 :一般情况下,若任务的逻辑发生改变,本身一对一的存储过程指定任务的改变只会表现在存储过程上,不需要去改变客户端的代码,降低了软件的维护成本。(5)可重用性 :一个存储过程只需要编写一次程序,并且还可以在很多地方进行使用,如 :客户机应用程序、数据库触发器、SQL脚本等。
2 oracle 数据库中存储过程的开发实例
2.1 复杂业务逻辑的封装
通过实现PL/SQL的存储过程,就可以实现某学校教务系统学生选课的逻辑指令,同时存储过程还能够对学生的选修课程数量进行判断,如果超过规定值,则不能够继续选课。如果不适用这个代码,那就需要数据库终端拥有两个数据集,首先判断学生是否选课成功,然后在计算选课数量,如果在同一时刻选课的学生较多,就会在很大程度上给网络增加较大的符合,从而影响网络的顺利运行。但是目前使用PL/SQL可以有效解决这个问题,但是也需要服务器的帮助,这个服务器要具备强大的处理能力,从而确保如果网络的负荷非常高,学生的选课工作也可以顺利完成。
2.2 动态多表关联查询
开发应用程序的过程中,并不能通过处理数据表直接得到相关数据,而是需要以此为基础建立其他的过度临时表或者直接实施比较复杂的逻辑操作处理,用户才可以得到需要的数据,但是这种过程只能在存储过程中实现。在实际应用过程中,可以通过包或者包体来对某学校某学年或者某学期学生的补考名单进行针对性的查询。补考名单是一个较为内容多且杂的数据库,其中涉及到很多表格,同时还存在一定的传递性参数,使用动态多表开关联查询有利于动态查询操作的实现,最后还能够返回到记录集。
2.3 跨平台的开发
由于考虑到跨平台的开发以及数据库无关性等方面的因素,所以PL/SQL要被作为开放式语言的Java所取代,再加上Java语言的平台具有一定程度的开放性,所以可以沟通并且集成Web服务、J2WW、XML、SQL等多个领域。通过一系列的代码可以显示Java存储过程在异构数据库访问过程中的应用,在oracle数据库中可以直接在本地系统用户认证过程中结合使用fn Login函数,使之组成系统认证功能,由于fn Login函数和其他oracle函数没有大的差异,所以无需关心fn Login函数的实现细节以及核心功能的位置,从而可以轻易的实现多个数据库系统统一进行用户认证的目的。
3 存储过程的创建和调用
3.1 存储过程的创建
3.1.1 PL/SQL 语言
过去,oracle的开发人员都在使用PL/SQL语言来管理和构建大量数据阵列复杂的系统,而PL/SQL语言属于一种高级的数据库程序设计语言,可以在各种环境下访问oracle数据库,同时也是SQL的补充,可以在SQL中引入其他语言的过程性特征,包括整套的数据类型、异常处理结构、循环结构以及条件结构等,如 :变量、函数、过程、控制结构等。SQL和过程性特征相结合,可以最大限度的强化oracle的PL/SQL功能。由于其集成了结构化程序设计中的语言特征以及SQL查询语言的特征,所以可以集成数据库服务器端,以最佳性能完成一些比较复杂的逻辑程序和计算。此外,还吸收了编程语言的很多设计方面的优势,如 :例外处理、重载处理、信息隐蔽性以及数据封装性等。
3.1.2 Java 语言
Oracle 9i数据库集 成了一个 称为oracle JVM的Java虚拟机,而oracle中JVM是在集成数据库中,和SUN Java JDK Specification互相兼容的Java执行环节,在oracle JVM中允许数据库运行Java程序,这些程序也可以称为Java存储过程。在数据库中存放的Java程序可以借助于JVM直接运行,还可以在内置JDBC驱动器的辅助下,对PL/SQL函数和SQL函数进行调用。此外,oracle还为布置、调试、测试、开发Java存储过程 提供了oracleJDeveloper。Java存储过程借助于oracle服务器端内部驱动程序或者服务器层胖驱动程序,这是在oracle数据库中将Java程序创建为存储对象唯一的有效方法。
3.2存储过程的调用
在oracle数据库中,存储过程、存储函数、触发器、对象类型四种不同类型的存储过程都可以在不同的调用环境中进行调用,这些调用环境分别是 :(1)SQL语句,在任何SQL语句的内部,都可以对函数进行调用。(2)顶级CALL语法 :在使用CALL语句的环境下,可以使用顶级调用过程和顶级函数,而CALL语法是随着oracle 9i的发展而引进、使用的新型语法。(3)PL/SQL包、子程序、块 :可以在PL/SQL包、子程序、块对其进行调用,但是要注意Java存储程序自己可以在匿名的BEGIN……END块内进行调用。(4)触发器的隐式调用 :Java存储过程或者PL/SQL存储过程都可以在触发性执行的过程中进行隐式调用。
4存储过程的开发策略
根据多次存储过程的应用经验,主要将oracle数据库应用系统使用存储过程的一些开发策略总结为以下几点 :(1)对于需要自动完成预先执行任务的应用程序,可以通过存储过程来完成,由于存储过程在启动以后就会自动执行,所以应用程序系统启动之后,不需要再进行手工操作,从而大大节约了人物、物力,也在很大程度上方便用户的使用 ;(2)对于需要对基本数据表进行较复杂中间过程逻辑处理的数据,通过存储过程可以返回用户需要的结果数据集 ;(3)用户和数据库如果数据的操作比较频繁时,可以通过存储过程来最大限度的减少网络负荷,同时,由于在调用服务器端的存储过程之前,该存储过程已经经过了解析,所以还可以在一定程度上提高脚本的运行性能 ;(4)对于需要集中进行控制和管理的逻辑和运算进行相应的处理时,要尽量在存储过程中进行,但是要在数据库服务器中保存一个存储过程的备份,以后对客户端进行调用时,就可以直接对存储过程进行便捷的执行和调用,可以起到便于应用程序维护以及版本管理的作用 ;(5)对于容易发生变化的业务规则,尽量通过存储过程来实现 ;(6)对于可以重复调用、运行效率较高的逻辑和运算处理,尽量通过存储过程来实现。
5结束语
ORACLE存储过程 第4篇
关键词：VFP数据,ORACLE数据库,程序设计
ORACLE是以高级结构化查询语言(SQL)为基础的大型关系数据库,因其可以支持多用户和在数据库管理功能、完整性检查、安全性、一致性、稳定性方面的良好性能,使其成为目前最流行的客户服务器(CLIENTSERVER)体系结构的数据库之一。
在企业日常生产中,有时需要调用每天存放在ORACLE服务器上的生产数据,采用VFP软件实现方便的生产查询或汇总,因为VFP(VisualFoxPro)是一个功能强大的小型数据库管理系统,它能迅速而又简单地建立用户的数据库,从而方便地使用和管理数据;利用其提供的对象和事件处理模式和面向对象的编程方法,使用户能够快速地建立应用程序。同时,VisualFoxPro是MicrosoftOffice的兼容产品,用VFP开发的出的应用软件很受大型企业低端用户的欢迎,所以,VFP是大型企业解决数据处理问题时,作为应用程序前端开发工具的理所当然的选择。首先,VFP系统小巧玲珑,不占太多的存储空间,数据建立表容易,操作简单,用它存储中间数据,成果数据,图表信息非常合适,1992年6月Microsoft购买Fox公司以来,对VFP作了控制操作Oracle的接口,用户很容易的在VFP平台下,通过ADO,ODBC等方式完成这项工作。
为此设计了VFP数据库与ORACLE数据库接口程序,以实现ORACLE数据向VFP数据库的自动转换,以下是程序设计思路:
1 总体设计思想
为使用户能够调用服务器上的数据体,首先在ORACLE服务器上根据用户需要建立了可以调用的ORACLE存储过程,用户在远端的客户机上只要调用相应的存储过程就能够获得想要的数据,而不会影响服务器本身的数据库运行。在客户机上编写数据库接口程序,数据库接口程序的主要内容为调用存储过程和将调用的信息转入DBF数据库表,程序编写完成后,利用windows操作系统里的计划任务,按时自动执行数据库接口程序,实现调用ORACLE存储过程,并通过执行VFP的execute和Fields("XX")value、INSERT等函数,将获得数据信息逐条送入VFP数据库已存在的DBF表中,实现ORACLE数据库内容向VFP数据表的自动转换。
2 程序设计
程序是在Windows操作平台上采用VisualFoxPro9.0开发完成。
3 实现
程序通过系统A D O配置和登录ORACLE信息设置、程序参数设置、数据监测四个阶段实现其数据转换功能。
3.1 ADO配置
为实现V F P数据与O R A C L E数据库之间的通讯,要在数据转换服务器上安装o r a c l e 9.0或以上版本的MicrosoftODBCDriverforOracle(假设安装了oracle9.0),以取得ORACLE的SQL*NET软件层支持,并通过ADO配置使VisualFoxPro能够与ORACLE数据库相连,访问库中的数据。在oracle9.0安装目录中找到tnsnames.ora文件,将文件内容中的IP地址和SERVICE_NAME改为所要调用的ORACLE数据库的IP地址和服务器名称。
3.2 登录ORACLE信息设置界面
在WINDOWS操作系统的控制面板里,选择管理工具,并在其中选择ODBC数据源管理器,在其界面菜单里选文件DSN,点击添加,填写一个名称,如:yc,该界面用于设置登录ORACLE数据库所必需的用户名、口令和数据库别名三个参数据,以供程序调用。界面如图示。
3.3 程序参数设置
关键语句格式:首先建立一个远程连接,连接的名称就是上述ADO配置中填写的名称,如yc:
这里“jh”为ORACLE存储过程里的字段。再应用INSERT into VALUES语句向已经建好的DBF表写入jh等字段的数据。
3.4 数据监测
在程序运行的最后阶段执行数据监测模块,设计思路类似学校班级点名册,通过在建立的文件记录统计表(该表的字段设定为日期和所要调用的表名)中做相应记录来区别数据的调用成功与否:若某日没有从ORACLE数据库调用到相关数据,在该日该数据所对应的表名记录号写上F,若某天DBF表已经建立,则在当天该记录号写上T。下次运行调用存储过程时首先检查该表,如果该表上有F标志,则从F标志对应的那天开始调用数据,表的格式如下图所示:
4 结语
该接口程序实现了定时从ORACLE数据库向VFP数据自动转换功能,满足了企业数据库建设和自动化管理要求,极大地提高了数据库利用效率。为技术、决策人员及时掌握企业各类数据,创造了条件。
参考文献
[1]张洪举.Visual Fox Pro程序设计参考手册.人民邮电出版社,2004
ORACLE存储过程 第5篇
1 优点和特性
存储过程是存储在数据库中的一段Java或PL/SQL程序。当创建存储过程时, 系统会对其进行编译, 并将执行代码存储到数据库中。
触发器是一种特殊的存储过程, 它在插入, 删除或修改特定表中的数据时触发执行, 它比数据库本身标准的功能有更精细和更复杂的数据控制能力。在大型数据库设计中, 会经常用到触发器。
2 实例探讨
2.1 举例介绍存储过程和触发器的写法和用法:
下面建立一个简单的数据库录入存储过程 (PL/SQL) , 每当用户修改数据库的重要数据时, 即把用户的用户名、修改日期、操作记录到一个表中。
上面的存储过程建立好后, 可以在通过以下触发器调用这个存储过程来记录用户对表的修改。每当用户修改此表后, 用户的名称、修改时间和操作即被记录在表update_log_tab中:
create trigger audit_update
after update on xxx
for each row begin
begin
update_log
end
2.2 JAVA存储过程
在Oracle8i之后, 不仅可以使用原有的PL/SQL开发存储过程, 而且也可以使用Java语言来开发存储过程。JAVA存储过程开发步骤如下:
1) 编写Java源代码
当以public方式声明类时, 类名必须与其文件名完全一致。
只有public static方法可以作为Java存储过程。
过程用于执行某种操作。需要注意的是, 过程所对应的Java方法返回值必须为空 (void) 。
2) 装载Java代码及类到Oracle数据库中
在编写了Java源代码之后, 接下来应该将Java代码及相应的Java类装载到Oracle数据库中。装载Java代码及类到RDBMS有以下两种方法:
使用loadjava工具, 通过该工具可以快速装载Java源代码 (.java) 、Java二进制代码 (.class) 以及Java打包文件 (.jar) 。
使用CREATE Java、ALTER Java装载Java代码。
3) 生成调用说明
在装载了Java类之后, 接下来应该生成对public static方法的调用说明, 最终完成Java存储过程的开发工作。
经上述步骤后, 完成了Java存储过程的开发工作, 最后调用并执行该Java存储过程。
2.3 触发器典型应用示例
在实际开发过程中, 经常遇到这样的数据维护要求:采油厂由多个矿、大队组成, 要求各矿、大队只能维护本单位的数据, 但另一方面, 又需要将分散到各单位的数据集中起来进行汇总, 得到全厂的汇总数据。
如果为每个矿、大队都建立一个表, 显然难以得到汇总的数据 (在这种情况下, 无法利用视图机制) ;如果所有的单位都共享一个表的话 (这时, 这张表中的数据实际就是汇总的数据) , 因为每个单位需要维护数据, 所以都对这个表有修改权, 因此在数据安全上难以控制。
使用触发器的话, 上述问题便可迎刃而解:为每个单位建立一个表 (该单位的所有权限只限于对此表有修改权) , 再为汇总数据也建立一个表, 然后在每个单位表上建立触发器, 使得单位表上有数据更新时, 便会对应地更改汇总表中的相关数据。
2.4 JSP环境下实现存储过程的调用
J S P是一种服务端页面技术, 它的嵌入脚本语言是JAVA, 因此使它的功能极为强大, 具有JAVA的一切优秀品质, 执行效率和处理能力也较其他语言更高。它具有一次编写, 处处运行的能力, 完全与平台无关, 可以在任何平台下编写JSP网页并且在任何支持JSP的系统上执行。也可以建立自己的组件, 并在JSP中使用。目前主要是JavaBeans和Java servlet, 而它们都是跨平台的。
优秀的特性和强大的功能, 使得JSP技术能够更容易建立动态页面。较之ASP, 它具有更好的安全性, 健壮性和可维护性。因此JSP技术拥有广阔美好的前景。必将成为服务端动态页面技术的主流。
下面, 我们着重介绍JSP脚本是如何利用javabean组件的Command对象访问数据库的存储过程, JSP在服务器端执行, 可以调用ORACLE数据库中的存储过程, 快速完成复杂的数据库操作, 把结果回传给客户端的浏览器, 举例介绍在jsp中同时应用“存储过程”和“javabean”, 假定已建立存储过程xxx和javabean组件aaaa, 组件在sjk包内, 代码如下:
ResultSet RS=aaaa.executeQuery (“{call xxx (`12`, ``) }”) ;
while (RS.next () ) {
out.println (RS.getString (1) ) ;
out.println (RS.getString (2) ) ;}
RS.close () ;
3 结论
以上通过对ORACLE数据库网络开发中常用的存储过程 (Java、PL/SQL) 及触发器的介绍, 以及如何在JSP网络开发环境下调用它们, 探讨了结合运用这些技术的实例, 实践证明, 在油田数据库开发建设过程中, 有效应用存储过程和触发器对数据库开发过程中遇到的问题, 往往会有独到的解决方法。能使数据库的设计变得简洁和高效。
摘要：通过对ORACLE数据库网络开发中常用到的存储过程 (Java、PL/SQL) 及触发器使用技术的介绍, 阐述了存储过程和触发器的特点、作用及使用方法, 最后通过实例说明在jsp网络开发环境下如何完成它们的调用, 应用到实际工作当中。
关键词：Oracle,存储过程,触发器,应用实例
参考文献
[1] (美) Scott Urman著.《ORACLE8PL/SQL程序设计》
[2] (杨华中贾耀炜编著) .《Java语言与程序设计》
Oracle中索引的存储原理浅析 第6篇
关键词：索引块,索引段,平衡树
1. 前言
在Oracle数据库中,当用户创建索引时,Oracle会自动地在表空间中创建索引段来存储索引的数据。用户可以通过以下方式控制索引段的空间分配和使用:
◆设置索引段的存储参数来控制如何为此索引段分配数据扩展
◆为索引段设置PCTFREE参数,来控制组成数据扩展的各个数据块的可用空间情况。
索引段使用的表空间既可以是索引所有者的默认表空间,也可以是在CREATE INDEX语句中指定的表空间。索引无需和其相关的表位于同一表空间中。相反,如果将索引与其相关表存储在不同磁盘上能够提升使用此索引的查询性能,因为此时Oracle能够并行地访问索引及表数据。
2. PCTFREE和PCTUSED参数
2.1 PCTFREE参数
PCTFREE参数用来设置一个数据块中至少需要保留多少可用空间(百分比值),为数据块中已有数据更新时可能发生的数据量增长做准备。例如,当用户用CREATE TABLE语句创建表时指定了以下参数:
PCTFREE 20
这个参数设定了此表对应的数据段中的每个数据块至少保留20%的可用空间,以备块中已有数据更新时使用。只要数据块中行数据区与数据块头的容量之和不超过数据块总容量的80%,用户就可以向其中插入新数据,数据行被放入行数据区,相关信息被写入数据块头。
2.2 PCTUSED参数
PCTUSED参数用于决定一个数据块是否可被用于插入新数据,她的依据是数据区与数据块头的容量之和占数据块全部容量的最大百分比。当一个数据块中的可用空间比例小于PCTFREE参数的规定时,Oracle就认为此数据块无法被用于插入新数据,直到数据块中的占用容量比例小于PCTUSED参数的限定。在占用容量比例大于PCTUSED参数的限定之前,Oracle只在更新数据块内已有数据时才会使用此数据块的可用空间。例如,当用户用CREATE TABLE语句创建表时指定了以下参数:
PCTUSED 40
在例子中,当此表的某数据块占用容量比例高于40%时,Oracle不会将此数据块用于插入新数据行。
3. 索引如何存储
3.1 索引块的格式
一个数据块内可用于存储索引数据的空间等于数据块容量减去数据块管理开销,索引条目管理开销,rowid,及记录每个索引值长度的1字节。
当用户创建索引时,Oracle取得所有被索引列的数据并进行排序,之后将排序后索引值和与此值相对应的rowid按照从下到上的顺序加载到索引中。例如,以下语句:
Oracle先将employees表按last_name列排序,再将排序后的列及相应的rowid按从下到上的顺序加载到索引中。使用此索引时,Oracle可以快速地搜索已排序的last_name值,并使用相应的rowid去定位包含用户所查找的last_name值的数据行。
3.2 索引的内部结构
Oracle使用平衡树存储索引以便提升数据访问速度。当不使用索引时,用户必须对数据进行顺序扫描来查找指定的值。如果有n行数据,那么平均需要扫描的行为n/2。因此当数据量增长时,这种方法的开销将显著增长。
如果将一个已排序的值列划分为多个区间,每个区间的末尾包含指向下个区间的指针,而搜索树中则保存指向每个区间的指针。此时在n行数据中查询一个值所需的时间为log(n)。这就是Oracle索引的基本原理。
在一个平衡树索引中,最底层的索引块(叶块)存储了被索引的数据值,以及对应的rowid。叶块之间以双向链表的形式相互连接。位于叶块之上的索引块被称为分支块,分枝块中包含了指向下层索引块的指针。如果被索引的列存储的是字符数据,那么索引值为这些字符数据在当前数据库字符集中的二进制值。
对于唯一索引,每个索引值对应着唯一的一个rowid。对于非唯一索引,每个索引值对应着多个已排序的rowid。因此在非唯一索引中,索引数据是按照索引键及rowid共同排序的。键值全部为NULL的行不会被索引,只有簇索引例外。在数据表中,如果两个数据行的全部键值都为NULL,也不会与唯一索引相冲突。
3.3 索引的属性
有两种类型的索引块:◆用于搜索的分支块
◆用于存储索引数据的叶块
(1)分支块
分支块中存储以下信息:
◆最小的键值前缀,用于在本块的两个键值之间做出分支选择
◆指向包含所查找键值的子块的指针
包含n个键值的分支块含有n+1个指针。键值及指针的数量同时还受索引块容量的限制。
(2)叶块
所有叶块相对于其根分支块的深度是相同的。叶块用于存储以下信息:
◆数据行的键值
◆键值对应数据行的ROWID
所有的键值-ROWID对都与其左右的兄弟节点向链接,并按照(key,ROWID)的顺序排序。
3.4平衡树结构的优势
平衡树数据结构具有以下优势:
◆平衡树内所有叶块的深度相同,因此获取索引内任何位置的数据所需的时间大致相同。
◆平衡树索引能够自动保持平。
◆平衡树内的所有块容量平均在总容量的3/4左右。
◆在大区间范围内进行查询时,无论匹配个别值还是搜索一个区间,平衡树都能提供较好的查询性能。
◆数据插入,更新,及删除的效率较高,且易于维护键值的顺序。
◆大型表,小型表利用平衡树进行搜索的效率都较好,且搜索效率不会因数据增长而降低。
参考文献
[1]赵伯山Oracle 9i中文版实用培训教程[M].清华大学出版社2002
ORACLE存储过程 第7篇
Oracle是当今最大数据库厂商Oracle(甲骨文)公司的数据库产品。它是世界上第一个商品化的关系型数据库管理系统，也是第一个推出与数据库结合的第四代语言开发工具的数据库产品。它采用标准的SQL(结构化查询语言)，支持多种数据类型，提供面向对象操作的数据支持。
本文所论大型数据对象其实也是Oracle所支持存储的数据类型之一，它是指多媒体数据(图片、视频、音频等)以及文档、表格、邮件等工作文件，把这些大型数据对象存储在Oracle数据表中，成为数据表中字段的某个数据项。该数据类型在其它数据库系统中要么不支持，要么支持有限，支持功能远没有Oracle强大。支持大型数据库，大型数据对象恰恰是Oracle数据库的突出特点之一。
目前多媒体等大型数据进入数据库的应用越来越广泛，而介绍大型数据对象存储在Oracle数据库中的内容少之有少。所以，本文结合笔者实践经验，通过举例Oracle存储图片文件和Word文档，简要剖析大型数据对象在Oracle数据库中的存储方法。
1 大型数据对象在Oracle中的存储
1.1 大型数据对象及Oracle中对应的数据类型
在数据库中常用的数据项的数据量都比较小，一般只有几个字节到几千个字节不等，如学生成绩表中某个数据记录的数据项：英语成绩86，存储数字86，不过几个字节(大小由数据表中该字段类型自定义)。但是目前多媒体数据等大型数据存储在数据库中，并通过数据库来管理及应用越来越广泛。而那些数据之所以被称为大型数据对象是因为其自身数据量大，一般由几十KB至几百MB不等。如一段视频文件两三百MB，一张图片文件几MB，甚至一个Word文档也有二、三十KB。将这样大的数据存储在数据库中进行管理很多数据库系统难以做到，但Oracle支持并有相对应的数据类型。Oracle用LOB类型来存储数据量大的数据库字段，最大可以存储4GB数据量。LOB类型具体又可分为以下几种：
(1)CLOB：用来存储单字节字符数据;
(2)NCLOB：用来存储定宽多字节字符数据;
(3)BLOB：用来存储无结构的二进制数据;
(4)BFILE：在数据库外部保存的大型二进制对象文件。
前面三种数据通常在数据库内部存放，可执行读取、存储、写入等操作，而BFILE存储的数据通常放在数据库外部，可读取、查询BFILE数据，但不能写入，由操作系统来决定。
1.2 如何存储
存储大型数据对象，要做一些准备工作。首先要定义并授权可进行存储操作的用户USER，其次定义一张专用来存储大型数据对象的数据表TABLE，最后确定要存储的大型数据对象。
做好上述准备工作后，接下来创建目录DIRECTORY，该目录下存放大型数据对象。使用Oracle编程语言PL/SQL编写一段代码，也可以是一个过程或函数，该代码实现大对象存入数据库操作。在该代码中主要是采用Oracle自带的DBMS_LOB包中的方法来实现大对象的存储。DBMS_LOB包,主要的方法有:
(1)读:read(lob_1,num_bytes,offset,buffer);
(2)写:write(lob_write,num_bytes,Offset,buffer);
(3)改:Update;
(4)添加:append(dest_lob,Src_lob);
(5)截取:substr(lob_1,num_bytes,offset);
(6)删除:Erase(lob_1,num_bytes,lob_offset);
(7)置空:empty_xlob()[/*x为C、B*/]and Empty_Bfile();
(8)载入:loadFromFile(dest_lob,src_lob,num_bytes,dest_offset,scr_offset);
BFILE在操作上有一些特别:
(1)BFILE只读。
(2)所有对BFILE的操作都要先打开,后关闭。
DBMS_LOB.fileopen(BFILE_LOB);
DBMS_LOB.fileclose(BFILE_LOB);
(3)指向:bfilename(目录',文件名’)。
1.3 举例(存储图片和Word文档)
1.3.1 存储pic01.bmp图片到Oracle数据库Pic＿lob表中
(1)假定位图文件pic01.bmp存放在D:picture目录下，且进行存储操作的用户为已经创建好的数据库管理员用户STU。将位图文件存储在数据库表Pic_lob中的主要代码如下：
1.3.2 存储pw.doc文档到Oracle数据库word＿table表中
假定Word文件pw.doc存放在D:word目录下，且进行存储操作的用户为已经创建好的数据库管理员用户STU。将word文档存储在数据库表word_table中的主要代码如下：
2 结束语
通过以上两个实例，将大型数据对象存储进入Oracle数据库中，首先把已定义好目录及文件名的外部大型数据通过DBMS_LOB包中的bfilename方法指向BFILE类型的对象，然后以只读方式打开BFILE对象，最后把BFILE对象通过dbms_lob.loadfromfile方法载入到内部的BLOB对象中，从而实现了存储过程。当然也可以通过select length(存储大型对象的字段)from表名where限定条件来查询文件是否存入到数据库中，如果得到的字节数非零就说明已经存储进入。
摘要：本文介绍了多媒体及其它工作文件等大型数据对象如何存储于Oracle数据库的方法,并举例说明具体如何进行存储操作。
关键词：大型数据对象,Oracle数据库,存储
参考文献
[1]飞思科技产品研发中心.Oracle基础与提高[M].北京:电子工业出版社,2003.
[2]赵松涛.Oracle9i中文版入门与提高[M].北京:人民邮电出版社,2002,7.
