数据库存储过程

【存储过程的概念】


 


存储过程（Stored Procedure）是一组为了完成特定功能的SQL语句集。经编译后存储在数据库中。


存储过程是数据库中的一个重要对象，用户通过指定存储过程的名字并给出参数（如果该存储过程带有参数）来执行它。


存储过程是由 流控制 和 SQL语句书写的过程，这个过程经编译和优化后存储在数据库服务器中。


存储过程 可由应用程序通过一个调用来执行，而且允许用户声明变量。


同时，存储过程可以接收和输出参数、返回执行存储过程的状态值，也可以嵌套调用。


 


【存储过程的功能】


 


这类语言主要提供以下功能，让用户可以设计出符合引用需求的程序：


① 变量说明


② ANSI兼容的SQL命令(如Select,Update….)


③ 一般流程控制命令(if…else…、while….)


④ 内部函数


 


【存储过程的优点】


① 存储过程的能力大大增强了SQL语言的功能和灵活性。


存储过程可以用流控制语句编写，有很强的灵活性，可以完成复杂的判断和较复杂的 运算。


 


② 可保证数据的安全性和完整性。


理由1：通过存储过程可以使没有权限的用户在控制之下间接地存取数据库，从而保证数据的安全。


理由2：通过存储过程可以使相关的动作在一起发生，从而可以维护数据库的完整性。


 


③ 在运行存储过程前，数据库已对其进行了语法和句法分析，并给出了优化执行方案。


这种已经编译好的过程可极大地改善SQL语句的性能。


由于执行SQL语句的大部分工作已经完成，所以存储过程能以极快的速度执行。


 


④ 可以降低网络的通信量。


理由：客户端调用存储过程只需要传存储过程名和相关参数即可，与传输SQL语句相比自然数据量少了很多。


 


⑤ 使体现企业规则的运算程序放入数据库服务器中，以便：


1). 集中控制。


2). 当企业规则发生变化时在服务器中改变存储过程即可，无须修改任何应用程序。


企业规则的特点是要经常变化，如果把体现企业规则的运算程序放入应用程序中，则当企业规则发生变化时，就需要修改应用程序工作量非常之大（修改、发行和安装应用程序）。


如果把体现企业规则的运算放入存储过程中，则当企业规则发生变化时，只要修改存储过程就可以了，应用程序无须任何变化。


 


【存储过程优点概括】


① 存储过程只在创造时进行编译，以后每次执行存储过程都不需再重新编译，而一般SQL语句每执行一次就编译一次,所以使用存储过程可提高数据库执行速度。


 


② 当对数据库进行复杂操作时(如对多个表进行Update,Insert,Query,Delete时)，可将此复杂操作用存储过程封装起来与数据库提供的事务处理结合一起使用。


 


③ 存储过程可以重复使用,可减少数据库开发人员的工作量。


 


④ 安全性高,可设定只有某些用户才具有对指定存储过程的使用权


 


【存储过程缺点】


① 调试麻烦，但是用 PL/SQL Developer 调试很方便！弥补这个缺点。


 


② 移植问题，数据库端代码当然是与数据库相关的。但是如果是做工程型项目，基本不存在移植问题。


 


③ 重新编译问题，因为后端代码是运行前编译的，如果带有引用关系的对象发生改变时，受影响的存储过程、包将需要重新编译（不过也可以设置成运行时刻自动编译）。


 


④ 如果在一个程序系统中大量的使用存储过程，到程序交付使用的时候随着用户需求的增加会导致数据结构的变化，接着就是系统的相关问题了，最后如果用户想维护该系统可以说是很难很难、而且代价是空前的，维护起来更麻烦。


 


【存储过程的种类】


① 系统存储过程：以sp_开头,用来进行系统的各项设定.取得信息.相关管理工作。


 


② 本地存储过程：用户创建的存储过程是由用户创建并完成某一特定功能的存储过程，事实上一般所说的存储过程就是指本地存储过程。


 


③ 临时存储过程：分为两种存储过程：


一是本地临时存储过程，以井字号(#)作为其名称的第一个字符，则该存储过程将成为一个存放在tempdb数据库中的本地临时存储过程，且只有创建它的用户才能执行它;


二是全局临时存储过程，以两个井字号(##)号开始，则该存储过程将成为一个存储在tempdb数据库中的全局临时存储过程，全局临时存储过程一旦创建，以后连接到服务器的任意用户都可以执行它，而且不需要特定的权限。


 


④ 远程存储过程：在SQL Server2005中，远程存储过程(Remote Stored Procedures)是位于远程服务器上的存储过程，通常可以使用分布式查询和EXECUTE命令执行一个远程存储过程。


 


⑤ 扩展存储过程：扩展存储过程(Extended Stored Procedures)是用户可以使用外部程序语言编写的存储过程，而且扩展存储过程的名称通常以xp_开头。


 


【Oracle 数据库的一个分页存储过程示例】


create or replace procedure fenye(


tableName in varchar2,--表名
pageSize in number,--1页显示的记录数
pageNow in number,--当前为第几页
myrows out number, --总记录数(一定要小心代码中不能有Oracle的保留字，保留字是以青色显示的)
myPageCount out number,--总页数
p_cursor out testpackage.test_cursor--返回的记录集
) is
--定义部分
--定义一个sql语句字符串
v_sql varchar2(1000);
--定义两个整数
v_begin number:=(pageNow-1)*pageSize+1;
v_end number:=pageNow*pageSize;
begin
--执行部分(排序的话在最内层加条件)
v_sql:='select * from (select t1.*,rownum rn from (select * from '|| tableName
   ||' order by sal) t1 where rownum<='|| v_end ||') where rn>='|| v_begin;
--把游标和sql关联
open p_cursor for v_sql;
--要计算myrows和myPageCount
--组织一个sql
v_sql:='select count(*) from '|| tableName;
--执行sql，并把返回的值，赋给myrows
execute immediate v_sql into myrows;
--计算myPageCount
if mod(myrows,pageSize)=0 then
  myPageCount:=myrows/pageSize;
else
  myPageCount:=myrows/pageSize+1;
end if;
--关闭游标(关了的话运行java程序竟然报游标已经关闭的错误！)
--close p_cursor;
end;




【java调用存储过程的示例】
package org.bruce.test.oracle.procedure;


import java.sql.CallableStatement;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;


public class TestOracleProc6 {
public static void main(String[] args) {
Connection ct = null;
CallableStatement cs = null;
int pageNow = 1;
try {
Class.forName("oracle.jdbc.driver.OracleDriver"); // 1.加载驱动
ct = DriverManager.getConnection("jdbc:oracle:thin:@127.0.0.1:1521:MYORA1","SCOTT","m123");
cs = ct.prepareCall("{call my_fenye(?,?,?,?,?,?)}");

cs.setString(1, "emp"); // 取emp表
cs.setInt(2, 5); // 每页取 5 条记录
cs.setInt(3, pageNow); // 当前页为第 pageNow 页
cs.registerOutParameter(4, oracle.jdbc.OracleTypes.INTEGER); // 注册总记录数
cs.registerOutParameter(5, oracle.jdbc.OracleTypes.INTEGER); // 注册总页数
cs.registerOutParameter(6, oracle.jdbc.OracleTypes.CURSOR); // 注册返回的结果集


cs.execute();

int rowNum = cs.getInt(4); // 取出总记录数(getInt(4)中4，是由该参数的位置决定的
int pageCount = cs.getInt(5);
ResultSet rs = (ResultSet)cs.getObject(6);


System.out.println("总共有多少行记录：" + rowNum);
System.out.println("总共有多少页：" + pageCount);
System.out.println("当前为多少页：" + pageNow);
while(rs.next()) {
System.out.println(rs.getInt(1)+" "+rs.getString(2)+" "+rs.getFloat(6));
}
} catch(Exception e) {
e.printStackTrace();
} finally {
try { // 关闭各个打开的资源...
if(cs != null) {
cs.close();
}
if(ct != null) {
ct.close();
}
} catch (SQLException e) {
e.printStackTrace();
}
}
}
}