存储过程

1、  存储过程是数据库管理系统的专用术语，相当与普通编程语言的函数、过程。普通过程的作用是完成特定的工作，存储过程专用于处理数据库的数据更新、查询。  
   普通工程往往有编译程序生成，存在与操作系统的特定文件中，存储过程由数据库管理系统生成存放到数据库的数据字典中。
 
   直接通过SQL语句访问数据库时，SQL语句以字符串的形式提交给服务器，服务器要经过分析、编译，然后才执行；若将SQL语句写到存储过程中，那么分析和编译是在生成存储过程时完成的，需要执行时只需要调用存储过程就可以了。
 
   如下SQL语句完成数据的更新，可以将他们写到存储过程中
   update   table1   set   column1='1',column2='2';
   update   table2   set   column1='11',column2='22';
   若将他们直接提交给服务器每一条语句都需要经过分析、编译、执行。
   将他们写入如下存储过程中（对oracle）

   create   or   replace   procedure   proc_name   is
   begin
      update   table1   set   column1='1',column2='2';
      update   table2   set   column1='11',column2='22';
   end   proc_name;
 
   需要执行更新时直接调用（exec   proc_name）存储过程proc_name。若你一次要执行很多SQL语句而且这些语句要反复执行就应该写成存储过程。  
 
2、   存储过程（Stored Procedure）是一组为了完成特定功能的SQL语句集，经编译后存储在数据库中。用户通过指定存储过程的名字并给出参数（如果该存储过程带有参数）来执行它。
  每个参数名前要有一个“@”符号 ,每一个存储过程的参数仅为该程序内部使用,参数的类型除了IMAGE外，其他SQL Server所支持的数据类型都可使用。

　例子：
　　CREATE PROCEDURE order_tot_amt
          @o_id int,@p_tot int output
          AS
　　    SELECT @p_tot = sum(Unitprice*Quantity) FROM orderdetails
　　    WHERE ordered=@o_id
　　例子说明：
　　该例子是建立一个简单的存储过程order_tot_amt,这个存储过程根据用户输入的定单ID号码(@o_id),由定单明细表 (orderdetails)中计算该定单销售总额[单价(Unitprice)*数量(Quantity)],这一金额通过@p_tot这一参数输出给调用这一存储过程的程序。

 

优点:
1,减少网络带宽,按理论存储过程会提高性能.
2,无需重新编译,更改后即可运行,无需重新编译代码
3,安全性,(在传输用户名密码时,可防止注入等情况)

缺点:
1,依赖于数据库厂商,难以移植(当一个小系统发展到大系统时,对数据库的要求也会发生改变)
2,业务逻辑大的时候,封装性不够,难调试难以维护
3,复杂的应用用存储过程来实现，就把业务处理的负担压在数据库服务器上了。没有办法通过中间层来灵活分担负载和压力.均衡负载等

 

存储过程文章:

http://wangqiaowqo.iteye.com/blog/576343

http://baike.baidu.com/view/68525.htm#sub68525

 

    用java调用存储过程:

1、最简单的例子：

create  proc proc_select
    @pid varchar(20)
    @address varchar(20) output
    as
    select @address=address from userinfo where pid=@pid
    go

 

class.forName("sun.jdbc.odbc.JdbcOdbcDriver"); //加载驱动
Connection con=DriverManager.getConnection("Jdbc:Odbc:test\","sa",""); //获得连接
String call="{call proc_select(?,?)};" //调用语句
CallableStatement proc=con.preparecall(call); //调用存储过程
proc.setString(1,"12345678"); //给输入参数传值
proc.registerOutParameter(2,Type.varchar); //声明输出参数是什么类型的
proc.execute(); //执行
String address=proc.getString(2); //获得输出参数

  存储过程可以有返回值，所以CallableStatement类有类似getResultSet这样的方法来获取返回值。当存储过程返回一个值时，你必须使用registerOutParameter方法告诉JDBC驱动器该值的SQL类型是什么 。

 

例子2：

	String message = (String)this.baseDao.getHibernateTemplate().execute(
			new HibernateCallback(){
				public Object doInHibernate(Session session)  throws HibernateException, SQLException{
					Connection conn = session.connection();
					java.sql.CallableStatement cs = conn.prepareCall("{Call FareAdmin.ImportAndUpdateTariff(?)}",
							java.sql.ResultSet.TYPE_SCROLL_INSENSITIVE, java.sql.ResultSet.CONCUR_READ_ONLY);
					cs.setString(1, loginName);
					cs.execute();
					String message = "";
					
					ResultSet rs = cs.getResultSet();
					if(rs != null) {
						while(rs.next()) {
							String msg = rs.getString(1);
							int linenumber = rs.getInt(2);
							message += "<br/> Row : " + linenumber + " " + msg + ""; 
						}
					}
					return message;
				}
			});

 

sql server 2005下：
开启xp_cmdshell的办法
EXEC sp_configure 'show advanced options', 1;
RECONFIGURE;
EXEC sp_configure 'xp_cmdshell', 1;
RECONFIGURE;

开启'OPENROWSET'支持的方法：
exec sp_configure 'show advanced options', 1;
RECONFIGURE;
exec sp_configure 'Ad Hoc Distributed Queries',1;
RECONFIGURE;

关闭：
exec sp_configure 'Ad Hoc Distributed Queries',0
reconfigure
exec sp_configure 'show advanced options',0
reconfigure

开启'sp_oacreate'支持的方法:
exec sp_configure 'show advanced options', 1;
RECONFIGURE;
exec sp_configure 'Ole Automation Procedures',1;
RECONFIGURE;

 

 

 

charindex(@name,Ttopic)>0
判断@name在字符Ttopic中首次出现的位置,如果@name在Ttopic里存在，返回>0，否则返回0。

CHARINDEX('SQL', 'Microsoft SQL Server')
  这个函数命令将返回在“Microsoft SQL Server”中“SQL”的起始位置，在这个例子中，CHARINDEX函数将返回“S”在“Microsoft SQL Server”中的位置11。

 

 

 

2、Oracle存储过程

建立一个最简单的存储过程
create or replace procedure test_xg_p1 is
begin
dbms_output.put_line('hello world! this is the first procedure');
end;
建立一个带输入输出参数的存储过程：把输入的数据传给输出参数
create or replace procedure test_xg_p2(a in number,x out number) is
begin
x:=a;
end test_xg_p2;
建立一个逻辑判断的存储过程，并包含输入输出参数：近似分数的登记判断
create or replace procedure test_xg_p3(a in number,x out varchar2) is
begin
if a>=90 then
   begin
   x := 'A';
   end;
end if;
if a<90 then
   begin
   x:='B';
   end;
end if;
if a<80 then
   begin
   x:='C';
   end;
end if;
if a<70 then
   begin
   x:='D';
   end;
end if;
if a<60 then
   begin
   x:='E';
   end;
end if;
end test_xg_p3;
建立一个带循环逻辑的存储过程：近似累加函数
create or replace procedure test_xg_p4(a in number,x out varchar2) is
tempresult number(16);
begin
tempresult :=0;
for tempa in 0..a loop
    begin
    tempresult := tempresult + tempa;
    end;
end loop;
x:=tempresult;
end test_xg_p4;
建立一个能从数据库中特定表中返回数据的存储过程：
create or replace procedure test_xg_p5(x out varchar2) is
tempresult varchar2(1024);
begin
tempresult := 'start->';
select hotelid||hotelname into tempresult from hotel where hotelid =10041764;
x:=tempresult;
end test_xg_p5;
建立一个能使用游标的带循环的存储过程:
create or replace procedure test_xg_p6(x out varchar2) is
tempresult varchar2(10240);
cursor cursor1 is select * from hotel where hotelname like '浙江%';
begin
tempresult := 'start->';
for cursor_result in cursor1 loop
begin
tempresult :=tempresult||cursor_result.hotelid||cursor_result.hotelname;
end;
end loop;
x:=tempresult;
end test_xg_p6;