oracle存储过程学习（精选8篇）
oracle存储过程学习 第1篇
-09-09ORACLE 常用函数总结(80个)
-11-11oracle 视图权限 oracle 创建视图权限不足
2012-11-11oracle 声明游标(不具备字段)规则应用
-03-03Oracle数据库索引的维护
-04-04oracle监控某表变动触发器例子(监控增,删,改)
-07-07[Oracle] 分析AWR报告的方法介绍
2014-06-06Oracle 11g自动诊断信息库(Automatic Diagnostic Repository，AD
2007-03-03Oracle 9i轻松取得建表和索引的DDL语句
2007-04-04Oracle轻松取得建表和索引的DDL语句
2012-11-11oracle使用order by排序null值如何处理
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篇
文中采用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存储过程学习 第4篇
摘 要：基于存储过程的执行速度快、安全性高等优势，利用存储过程分析设计一种简易教学系统。在分布式应用环境下将对数据库的访问做在业务逻辑层上，通过存储过程实现对数据的所有操作处理，这样既可以降低整个应用程序的复杂性，也可以提高数据处理速度，方便用户操作。针对存储过程和MVC三层架构模式的优点，设计实现了简易教学系统，并给出了设计原理及实现方案。
关键词：存储过程；MVC三层架构模式；简易教学系统
中圖分类号：TP311 文献标志码：A 文章编号：1673-8454（2016）23-0086-04
一、引言
随着社会的发展，学校中教师和学生的规模不断扩大，使教师和学生信息数据量急剧增长，从而导致管理教师和学生信息的工作变得越来越繁杂，如何利用计算机技术帮助教师更快、更好地完成此类工作是非常有必要的。[1]通常情况下，对于基本信息的添加、删除、修改和查询等基本操作，使用Excel软件来完成也是可行的，但安全性不能保证，共享性体现不了，并且查询很不方便。尤其当涉及多个数据表格联合查询时，使用Excel软件进行数据处理会增加很大的工作量，在对其中一个数据表格进行处理时，也不能确保其它相关联的数据表格中数据的一致性。[2]因此，需要借助软件开发技术，编写小的应用程序，利用数据库技术对大量的数据进行保存、添加、删除、修改等操作，以便能够更好地利用数据资源。
Microsoft SQL Server是Microsoft研制开发的一款数据库管理系统，具有操作简单易学的特点，用户可以以很高的安全性、可靠性和可扩展性运行关键任务，具有很强的安全保密性。作为数据存储，SQL Server性能可靠，数据管理功能强大。[3]以其低价位、功能多、用户界面友好而被广泛地应用在大多数分布式Web应用程序中，是目前最常用的数据库系统之一。[4]
二、SQL应用与设计环境选择
SQL（Structured Query Language）语言是一个通用的、功能很强的关系数据库的标准语言，是一种介于关系代数与关系演算之间的结构化查询语言，其功能不仅仅是查询，用于存取数据以及更新、统计和管理关系数据库系统。
1.SQL语言对数据操作具有的优点[5]
（1）非过程化语言；
（2）统一语言，是所有关系数据库的公共语言。
SQL Server主要功能及其命令动词如表1所示：
数据定义是通过CREATE、DROP、ALTER命令，实现对基本表、视图以及索引文件的定义、修改和删除操作，还可以定义存储过程、触发器等模块。数据操纵是通过SELETE、INSERT、UPDATE、DELETE命令，实现数据查询和数据更新两种操作语句。其中，数据查询是指对数据库中的数据查询、统计、排序、检索、分组等操作，数据更新是指数据的插入、删除、修改等操作。数据控制是指对数据的操作权限控制。通过GRANT、REVOKE命令实现对数据库用户的授权和收权的存取控制，以保证数据的安全性。
对数据库的操作主要是通过以上SQL命令动词实现，但编写实现系统的程序代码时，考虑应用在分布式环境中，使用独立的SQL命令实现对数据库的访问，不利于数据的安全保护，因此会采用MVC框架结构和存储过程实现数据库访问。
MVC框架结构由模型（Model）、视图（View）和控制器（Controller）三部分组成。MVC结构将传统的输入、处理和输出功能映射到一个逻辑的图形化用户界面结构中。[6-9]MVC框架结构如图1所示。
模型（Model）表示业务逻辑操作等，在MVC中拥有最多的处理业务，负责在数据库中存取数据，能为多个视图提供数据操作。视图（View）是用于屏幕上的显示。模型进行操作之后，其结果通过视图来显示。控制器（Controller）用于管理用户与视图发生的交互。用户发出请求，控制器将相应的信息传递给模型进行处理，模型与视图匹配在一起，共同完成用户的请求。[6]他们各自处理任务。使应用程序的输入、处理和输出分开。
当前台页面发送数据请求时，控制层接受请求并调用相应模型去处理请求，由模型调用存储过程，在服务器端得到处理结果后，再将返回的数据在前台显示，模型调用的是存储过程，而不是SQL基本语句，屏蔽了数据库中表的结构、数据关联、用户使用权限等重要信息，提高了系统安全性。将业务逻辑封装在数据库系统的存储过程中，可以大大提高整个软件系统的可维护性，提高系统的共享性。合理编写存储过程，可以最大限度地利用SQL Server的各种资源。
2.SQL存储过程具有的优点
（1）执行速度快，改善系统性能
存储过程在服务器端运行，可以利用服务器强大的计算能力和速度提高执行速度。而且存储过程是预编译的，第一次执行后的存储过程会驻留在高速缓存中，以后直接调用，执行速度很快，如果某个操作需要大量的T-SQL语句或重复执行，那么使用存储过程比直接使用T-SQL语句执行得更快。
（2）减少网络流量
用户可以通过发送一条执行存储过程的语句实现一个复杂的操作，而不需要写上百条T-SQL语句，这样可以减少在服务器和客户端之间传递语句的数量，减轻了服务器的负担。
（3）增强代码的重用性和共享性
存储过程在被创建后，可以在程序中被多次调用，而不必重新编写。所有的客户端都可以使用相同的存储过程来确保数据访问和修改的一致性。而且存储过程可以独立于应用程序而进行修改，大大提高了程序的可移植性。
（4）提供了安全机制
如果存储过程支持用户需要执行的所有业务功能，SQL Server可以不授予用户直接访问表、视图的权限，而是授权用户执行该存储过程，这样可以防止把数据库中表的细节暴露给用户，保证表中数据的安全性。
鉴于SQL存储过程的以上优点，本文设计系统应用在网络环境中，采用存储过程处理数据，存储过程封装数据库操作，可以有效提高程序的性能。[8]调用存储过程时，通過指定存储过程的名字并给出参数（如果存储过程带有参数）来执行，避免了代码冗余，从而提高系统开发效率。
三、简易教学系统的设计
针对中学生的教学管理工作，在数据量比较大的情况下，使用存储过程将简单的查询组合在一起，将数据的处理转移到后台数据库中，基于存储过程和MVC三层架构模式设计实现简易教学系统，实现了学生、教师和课程信息的管理。本系统采用了Microsoft Windows 7操作系统、SQL Server 2008作为后台数据库，采用MyEclipse10开发环境，设计并实现了一套完善的简易教学系统。
1.系统功能分析
简易教学系统针对的是学校里的教师、学生以及课程模块的管理。[10-17]主要功能包括：学生基本信息管理、教师基本信息管理、课程信息管理、学生成绩管理、授课信息管理。其功能结构如图2所示：
学生基本信息管理：该模块实现了增加学生基本信息、修改学生基本信息、删除学生基本信息以及查询学生基本信息等。
教师基本信息管理：该模块实现了增加教师信息、删除教师信息、修改教师信息以及查询教师信息等。可以统计某一地区教师信息，教师性别统计及某年龄段的统计。可以根据身份证的前四位查询该地区教师的信息等。
课程信息管理：该模块实现了增加课程信息、修改课程信息、删除课程信息以及查询课程信息。
学生成绩管理：该模块实现了增加学生成绩、修改学生成绩、删除学生成绩、查询学生成绩、查询学生排名、统计选修科目等。学生也可以查询某门课程的成绩，不及格门数统计以及总评成绩等。
授课课程信息管理：该模块实现了增加授课信息、修改授课信息、删除授课信息以及查询授课信息等。
2.系统数据库设计
（1）数据库表设计
本研究中，系统后台数据库主要设计了5个数据表——学生信息表、教师信息表、课程信息表、学生成绩表和授课信息表。各数据表及其主要字段如下所示（下画线代表主键）：
学生信息表（学号、姓名、性别、身份证号、民族、班级号、入学时间、家庭地址、政治面貌）
教师信息表（工号、身份证、姓名、性别、民族、年龄、出生地址、专业、职称）
课程信息表（课程号、课程名、开课学期、学分）
学生成绩表（学号、课程号、成绩、学期、学年）
授课信息表（工号、课程号、班级号、课程名、授课地点、授课学期、周学时）
（2）存储过程设计
存储过程是一组为了完成特定功能的SQL语句集，[9-11]存储在服务器上的一组预编译的Transact-SQL语句，是一种封装重复任务操作的方法。存储过程因其执行效率高、与事务处理结合、运行更安全等优点，在数据库应用程序中被广泛采用。创建存储过程的语法为：[12]
CREATE PROCEDURE <过程名> [；<版本号>]
[@<参数名><参数类型>[=<默认值>][OUTPUT]…]
[WITH RECOMPILE | ENCRYPTION | RECOMPILE，ENCRYPTION]
AS
在上面的CREATE PROCEDURE语句中，尖括号“< >”里的内容是必须要有的。方括号“[ ]”里的内容是可选的，参数的命名必须加“@”符号。方括号里符号“|”是或的关系，两边的选项可以任选一项。
存储过程创建后，以编译的形式存储于数据库服务器端，供应用程序调用。如果不调用，存储过程是不会执行的。通过调用存储过程时，实参的数量、顺序、类型要与定义存储过程时的形参的数量、顺序、类型相匹配。
存储过程按返回的数据类型，可以分为两类：[13-14]一类简单存储过程相当于SELECT语句，用于查询数据，查询到的数据以结果集的形式返回；另一类存储过程是通过输出参数返回信息，或不返回信息只执行一个动作。
根据不同的功能需求，本研究系统设计了如下存储过程。
简单存储过程主要包括：
> 查询所有学生的信息。
> 统计不同性别的学生人数。
> 统计所有课程都合格的学生信息。
> 查询所有教师的基本信息。
带参数的存储过程，向存储过程指定输入、输出参数的主要目的是通过参数向存储过程输入和输出信息来扩展存储过程的功能。通过使用参数，可以多次使用同一存储过程并按用户要求查找所需要的结果。
学生基本信息管理：
> 查询某学生的基本信息。
> 根据身份证，计算某学生的年龄。
> 修改某学生的家庭住址。
> 删除某学生的信息。
教师基本信息管理：
> 统计某教师的基本信息。
> 统计教师信息表中某地区的教师信息和人数。
> 统计某年龄段的教师人数。
对于课程信息管理：
> 查询某课程的基本信息。
> 修改某课程的学分。
> 删除某课程的基本信息。
对学生成绩表：
> 查询某学生的所有成绩以及该学生所选修课程门数。
> 查询某学生所有不及格成绩，并统计不及格课程门数。
> 查询某学生所学课程的总评成绩及班级排名。
> 查询某学生某门课程的成绩。
> 查询某门课程的统计信息（最大值、最小值、平均值、总人数）。
> 查询某课程所有比平均成绩高的学生姓名及成绩。
> 统计某门课程不及格学生人数。
> 统计某课程在某分数区间的学生成绩。
四、系统实现
通过以上分析设计，完成了简易教学系统。系统界面主要由3部分组成：一是显示系统的名称界面，功能是显示系统的标题；二是功能的导航，目的是链接到各个具体功能界面、对系统的各个功能进行的管理操作；三是结果显示，目的是能够显示所有功能的内容，是对教学信息系统模块进行具体操作。
系统存储过程示例如下：
（1）查询某学生某门课程的成绩。功能是根据学生姓名和课程名查询学生这门课程的成绩。
（2）查询某学生所有不及格的成绩，并统计不及格课程门数。功能是根据学生姓名查询不及格的科目成绩，并统计不及格科目。
系统界面如图3所示：
五、总结与展望
本文讨论了SQL基本数据处理与存储过程数据处理的特点，并在介绍存储过程优点的基础上，结合MVC三层架构模式，分析、设计并实现了基于存储过程的简易教学系统，可以方便管理教师和学生信息，为类似教学信息系统的设计与实现提供借鉴。但是，该系统在界面优化、细节设计等方面还存在一些不足，比如增加管理员和学生登录界面，而且可以增加考勤模块，通过管理学生的日常考勤，可以统计学生的最终成绩。下一步将在本文研究的基础上，展开考勤方面的研究，以进一步优化和完善系统功能。
参考文献：
[1]杨开城.教学系统分析技术的初步研究[J].中国电化教育，2007（8）：1-5.
[2]郑少兰.电子表格与SQL Server的结合应用[J].电脑知识与技术，2006（35）：17-18.
[3]石道生，张士军，孙山.SQL Server数据库触发器与存储过程技术在自定义数据表中的应用[J].计算机与现代化，2004（3）：22-24，27.
[4]陈一明.SQL Server数据库应用技巧探讨[J].科学技术与工程，2008（12）：3334-3337.
[5]苗雪兰，刘瑞新，宋歌.数据库系统原理及应用教程（第3版）[M].北京：机械工业出版社，2011：1-325.
[6]康建萍.基于MVC模式的Struts框架研究与应用[J].电脑知识与技术（学术交流），2007（2）：49-51.
[7]张计龙，张成洪，张凯，宓詠，郭超，郭家银.基于改进MVC的高校人事管理系统[J].计算机工程，2004（8）：191-194.
[8]白杨.探究SQL SERVER存储过程[J].电脑知识与技术，2011（34）：8769-8770，8776.
[9]王鹏，王晓亮，张喆，吴仁彪.基于存储过程的通航飞行数据处理系统[J].计算机工程与设计，2015（4）：1084-1089.
[10]王翠娥.基于WEB的教学系统的设计与实现[D].电子科技大学，2013.
[11]常红梅.基于存储过程的数据库安全性实践初论[J].网络安全技术与应用，2015（4）：112-114.
[12]季刚.基于存储过程的主从服务器数据库同步的实现[J].计算技术与自动化，2012（3）：114-116.
[13]陈潇.SQL Server2008数据库存储过程的应用[J].软件工程师，2015（6）：18-19.
[14]张申勇，蔡培茂，廖继勇，乐小燕，孙细斌.浅析SQL Server中视图、存储过程和函数的异与同[J].电脑知识与技术，2011（23）：5570-5573，5577.
[15]李衛华，颜建军.基于MVC模式的人事管理系统的设计与实现[J].三明学院学报，2009（4）：416-419.
[16]徐红，李睿，李进，曾珂.基于存储过程的计费查询统计系统设计与实现[J].现代电子技术，2005（13）：76-78.
[17]沈黎.基于网络数据库的存储过程和触发器应用研究[J].西南师范大学学报（自然科学版），2016（3）：51-55.
oracle存储过程学习 第5篇
今天继续我们的SQL菜鸟教程，上篇博客说了说触发器（SQL菜鸟入门级教程之触发器），今天我们来说说存储过程，其实，触发器也属于存储过程，只不过它比较特殊。下面切入正题，让我带领众菜鸟们一起学习一下存储过程。
存储过程简介
存储过程（Stored Procedure）是数据库系统中，一组为了完成特定功能的SQL 语句集，经编译后存储在数据库中，用户通过指定存储过程的名字并给出参数（如果该存储过程带有参数）来执行它。
在数据库系统中，存储过程和触发器具有很重要的作用。无论是存储过程还是触发器，都是SQL 语句和流程控制语句的集合。
存储过程的分类
1系统存储过程
以sp_开头,用来进行系统的各项设定.取得信息.相关管理工作。
2本地存储过程
用户创建的存储过程是由用户创建并完成某一特定功能的存储过程，这跟各种编程语言里用户自己写的函数非常类似。我们一般所说的存储过程就是指本地存储过程。今天我们重点介绍本地存储过程，其他存储过程了解即可。
3临时存储过程
分为两种存储过程：
一是本地临时存储过程，以“#”开头，这样的存储过程就是存放在tempdb数据库中的本地临时存储过程，且只有创建它的用户才能执行它;
二是全局临时存储过程，以“##”开头，这样的存储过程就是存储在tempdb数据库中的全局临时存储过程，全局临时存储过程一旦创建，以后连接到服务器的任意用户都可以执行它，而且不需要特定的权限。
4远程存储过程
在SQL Server中，远程存储过程(Remote Stored Procedures)是位于远程服务器上的存储过程，通常可以使用分布式查询和EXECUTE命令执行一个远程存储过程。
5扩展存储过程
扩展存储过程(Extended Stored Procedures)是用户可以使用外部程序语言编写的存储过程，而且扩展存储过程的名称通常以xp_开头。
创建存储过程的基本代码结构：
[sql]
CREATE PROCEDURE Procedure_Name
--Procedure_Name为存储过程名(不能以阿拉伯数字开头)，在一个数据库中触发器名是唯一的。名字的长度不能超过个字。PROCEDURE可以简写为PROC。
@Param1 Datatype,@Param2 Datatype
--@Param1和@Param2为存储过程的参数，Datatype为参数类型,多个参数用逗号隔开,最多允许个参数，
AS --存储过程要执行的操作
BEGIN
--BEGIN跟END组成一个代码块，可以写也可以不写，如果存储过程中执行的SQL语句比较复杂，用BEGIN和END会让代码更加整齐，更容易理解。
END
GO --GO就代表结操作完毕
exec Procedure_Name [参数名] --调用存储过程Procedure_Name。
drop procedure Procedure_Name --删除存储过程Procedure_Name，不能在一个存储过程中删除另一个存储过程，只能调用另一个存储过程
show procedure status --显示数据库中所有存储的存储过程基本信息，包括所属数据库，存储过程名称，创建时间等
show create procedure Procedure_Name --显示存储过程Procedure_Name的详细信息
exec sp_helptext Procedure_Name --显示你这个Procedure_Name这个对象创建文本
优点
1.存储过程只在创造时进行编译，以后每次执行存储过程都不需再重新编译，而一般SQL语句每执行一次就编译一次,所以使用存储过程可提高数据库执行速度。
2.当对数据库进行复杂操作时(如对多个表进行Update,Insert,Query,Delete时)，可将此复杂操作用存储过程封装起来与数据库提供的事务处理结合一起使用。
3.存储过程可以重复使用,可减少数据库开发人员的工作量(复用性高，面向对象的编程思想)
4.安全性高,可设定只有某些用户才具有对指定存储过程的使用权
缺点
1.调试麻烦，但是用 PL/SQL Developer 调试很方便！弥补这个缺点。
2.移植问题，数据库端代码当然是与数据库相关的。但是如果是做工程型项目，基本不存在移植问题。
3.重新编译问题，因为后端代码是运行前编译的，如果带有引用关系的对象发生改变时，受影响的存储过程、包将需要重新编译（不过也可以设置成运行时刻自动编译）。
4.如果在一个程序系统中大量的使用存储过程，到程序交付使用的时候随着用户需求的增加会导致数据结构的变化，接着就是系统的相关问题了，最后如果用户想维护该系统可以说是很难很难、而且代价是空前的，维护起来更麻烦。
世间所有事物都有两面性，没有绝对好的东西，也没有绝对不好的东西。存储过程既有它的优点也有缺点，我们不能盲目的完全使用存储过程，也不能将其打入冷宫不去用。根据具体的项目，根据实际情况去决定如何使用存储过程，最重要的就是不要过度，平衡就好。
oracle存储过程学习 第6篇
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存储过程学习 第7篇
关键词：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存储过程学习 第8篇
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语言与程序设计》
