存储过程详细介绍.doc

存储过程一、存储过程的概念 存储过程（Stored Procedure）是一组为了完成特定功能的SQL 语句集，经编译后存储在数据库中。用户通过指定存储过程的名字并给出参数（如果该存储过程带有参数）来执行它。在SQL Server 的系列版本中存储过程分为两类：系统提供的存储过程和用户自定义存储过程。系统过程主要存储在master 数据库中并以sp_为前缀，并且系统存储过程主要是从系统表中获取信息，从而为系统管理员管理SQL Server 提供支持。通过系统存储过程，MS SQL Server 中的许多管理性或信息性的活动（如了解数据库对象、数据库信息）都可以被顺利有效地完成。尽管这些系统存储过程被放在master 数据库中，但是仍可以在其它数据库中对其进行调用，在调用时不必在存储过程名前加上数据库名。而且当创建一个新数据库时，一些系统存储过程会在新数据库中被自动创建。用户自定义存储过程是由用户创建并能完成某一特定功能（如查询用户所需数据信息）的存储过程。在本章中所涉及到的存储过程主要是指用户自定义存储过程。 注意：存储过程虽然既有参数又有返回值，但是它与函数不同。存储过程的返回值只是指明执行是否成功，并且它不能像函数那样被直接调用，也就是在调用存储过程时，在存储过程名字前一定要有EXEC保留字。 二、存储过程的优点1.提高系统效率a.提高应用程序与数据库的通信速度；【存储过程代码直接存储于数据库中，不会产生大量T-sql语句的代码流量，节约网络带宽。】b.减少应用程序与数据库的交互次数，降低消耗。【对于需要多次访问数据的复杂操作，如果写在程序当中，那么就需要不断的或者大量的提取数据库中的数据到程序中进行运算，而如果是使用存储过程，就减少了应用程序与数据库的交互次数。】c.数据库执行速度更快；【存储过程只在创造时进行编译，以后每次执行存储过程都不需再重新编译，而一般SQL 语句每执行一次就编译一次。】2增强系统安全性 a、通过向用户授予对存储过程（而不是基于表）的访问权限，它们可以提供对特定数据的访问； b、提高代码安全，防止 SQL注入（但未彻底解决，例如，将数据操作语言DML，附加到输入参数）； c、SqlParameter 类指定存储过程参数的数据类型，作为深层次防御性策略的一部分，可以验证用户提供的值类型（但也不是万无一失，还是应该传递至数据库前得到附加验证）。 3. 系统升级、维护方便更新存储过程通常比更改、测试以及重新部署程序集需要较少的时间和精力。 4.应用程序出错概率小如果把所有的数据逻辑都放在存储过程中，那么asp.net只需要负责界面的显示，出错的可能性最大就是在存储过程。5.写程序简单采用存储过程调用类，调用任何存储过程都只要1-2行代码。三、存储过程的缺点1.可移植性差由于存储过程将应用程序绑定到数据库，因此使用存储过程封装业务逻辑将限制应用程序的可移植性。如果应用程序的可移植性在非常重要，则将业务逻辑封装在中间层中可能是一个更佳的选择。 2. 无法形成通用的可支持复用的业务逻辑框架 大量采用存储过程进行业务逻辑的开发致命的缺点是很多存储过程不支持面向对象的设计，无法采用面向对象的方式将业务逻辑进行封装。3. 代码可读性差,相当难维护. 4.不支持群集 数据库服务器是单点的，极难扩展，即便Oracle的群集，他的共享存储数据库也是单点的，如果业务逻辑的运算非常消耗CPU和IO，你没有任何有效的办法来扩展系统的性能。但是应用服务器出现CPU和IO瓶颈，你只需要加服务器就行了。 对于并非极度依赖数据的业务逻辑运算，如果在应用服务器端来实现的话，特别是采用SNA架构的情况下，理论上可以获得无限的水平扩展能力，只要加服务器就行了。但如果你放在数据库里面，你就大眼瞪小眼了，加服务器都不管用了。 5.采用存储过程调用类，需要进行两次调用操作，一次是从sql server中取到过程的参数信息，并且建立参数；第二次才是调用这个过程。多了一次消耗。 6.如果更改范围大到需要对输入存储过程的参数进行更改，或者要更改由其返回的数据，则仍需要更新程序集中的代码以添加参数、更新GetValue()调用等，这时候就比较繁琐了。有人说存储过程是【一种开发效率低下的、维护成本非常高、业务修改成本极高、难以编写自动回归测试的、没有声明式事务的、没有缓存管理的、没有多线程的、几乎没有什么和其他系统作接口的、大量消耗数据库CPU的、无法轻易扩展的（只能scale up，不能scale out），没有异步消息功能的，没有清晰的内存数据存储模型的，较难复用业务逻辑代码的面向过程数据操纵语言而已。 】4、 什么情况适合使用存储过程1.当一个业务同时对多个表进行处理的时候采用存储过程比较合适。2.复杂的数据处理用存储过程，如有些报表处理。3.多条件多表联合查询，并做分页处理，用存储过程也比较适合。4.存储过程过分依赖数据库端，假如你要做一个工程，是可以的，但是如果你要做一个产品，或者以后可能还会用到这个工程的代码的话，建议少使用。五、存储过程与应用程序业务逻辑层 1.非极度依赖数据库的业务逻辑，不应放到PL/SQL中去。除了那些对大数据处理非常依赖的操作，其他所有的业务逻辑统统不应该用存储过程来实现，而应该放在应用服务器层实现。而WebSphere群集解决的就是当应用层业务逻辑负载太大的情况下，如何进行扩展的问题。 2.把业务分层，与数据和显示、硬件隔离的思想，已经出现了20年了。可以说分层的思想一出现，人们就在说这个问题。而且一直对这个问题的看法如此一致。这些都是建立在大量的大规模企业应用的基础上得到的血的教训带来的。可以说即便硬件的更新再快，也赶不上需求的要求。CPU和IO瓶颈，昨天是，今天是，明天依旧是问题。而一旦需求来了，不会有时间给你去解决这个问题。这个时候最简单的方式，就是直接加点应用服务器。这个方法比任何方法都见效快，而且往往也最便宜。 3.对于企业应用来说，有的是OLTP型的，有的是OLAP型的，也有兼而有之的。对于OLTP型的应用逻辑一定要放在应用服务器来执行，而对于OLAP型的应用的确适合使用存储过程来实现，用应用服务器去运算根本不行。不过一般说来，大部分的OLAP运算并不是实时性要求很高的，所以往往可以用存储过程实现以后，作为后台任务定期执行，这些后台任务往往会执行好几个小时才能结束，然后把执行结果保存下来。让应用服务器在展示报表的时候读取最终查询结果。六、T-SQL存储过程 创建存储过程 Create Proc dbo.存储过程名 存储过程参数 AS 执行语句 RETURN 执行存储过程GO*- 变量的声明,sql里面声明变量时必须在变量前加符号 DECLARE I INT- 变量的赋值，变量赋值时变量前必须加set SET I = 30- 声明多个变量 DECLARE s varchar(10),a INT- Sql 里if语句 IF 条件 BEGIN 执行语句 END ELSE BEGIN 执行语句 END DECLARE d INT set d = 1 IF d = 1 BEGIN - 打印 PRINT 正确 END ELSE BEGIN PRINT 错误 END- Sql 里的多条件选择语句. DECLARE iRet INT, PKDisp VARCHAR(20) SET iRet = 1 Select iRet = CASE WHEN PKDisp = 一 THEN 1 WHEN PKDisp = 二 THEN 2 WHEN PKDisp = 三 THEN 3 WHEN PKDisp = 四 THEN 4 WHEN PKDisp = 五 THEN 5 ELSE 100 END- 循环语句 WHILE 条件 BEGIN 执行语句 END DECLARE i INT SET i = 1 WHILE i<1000000 BEGIN set i=i+1 END - 打印 PRINT i- TRUNCATE 删除表中的所有行，而不记录单个行删除操作，不能带条件 TRUNCATE TABLE 在功能上与不带 Where 子句的 Delete 语句相同：二者均删除表中的全部行。但 TRUNCATE TABLE 比 Delete 速度快，且使用的系统和事务日志资源少。 Delete 语句每次删除一行，并在事务日志中为所删除的每行记录一项。TRUNCATE TABLE 通过释放存储表数据所用的数据页来删除数据，并且只在事务日志中记录页的释放。 TRUNCATE TABLE 删除表中的所有行，但表结构及其列、约束、索引等保持不变。新行标识所用的计数值重置为该列的种子。如果想保留标识计数值，请改用 Delete。如果要删除表定义及其数据，请使用 Drop TABLE 语句。 对于由 FOREIGN KEY 约束引用的表，不能使用 TRUNCATE TABLE，而应使用不带 Where 子句的Delete 语句。由于 TRUNCATE TABLE 不记录在日志中，所以它不能激活触发器。 TRUNCATE TABLE 不能用于参与了索引视图的表。 - 创建临时表Create TABLE #temp(UID int identity(1, 1) PRIMARY KEY,UserName varchar(16),Pwd varchar(50),Age smallint,Sex varchar(6)Select * from #temp-打开临时表- 判断要创建的存储过程名是否存在 if Exists(Select name From sysobjects Where name=csp_AddInfo And type=P)- 删除存储过程 （注：不能在一个存储过程中删除另一个存储过程，只能调用另一个存储过程） Drop Procedure dbo.csp_AddInfo Go - 创建存储过程 Create Proc dbo.csp_AddInfo- 存储过程参数 UserName varchar(16), Pwd varchar(50), Age smallint, Sex varchar(6) AS- 存储过程语句体 insert into Uname (UserName,Pwd,Age,Sex) values (UserName,Pwd,Age,Sex) RETURN GO - 执行存储过程 EXEC csp_AddInfo Junn.A,123456,20,男七、SQL Server存储过程一.注释 -单行注释，从这到本行结束为注释,类似C+,c#中/* */多行注释，类似C+，C#中/* */二.变量（int, smallint, tinyint, decimal,float,real, money ,smallmoney, text ,image, char, varchar。） 语法： DECLARElocal_variable data_type ,.n例如： declare ID int -申明一个名为ID的变量，类型为int型 三.在SQL Server窗口中打印出变量的值 语法： PRINT any ASCII text | local_variable | FUNCTION | string_expr四.变量赋值 例如： -从数据表中取出第一行数据的ID,赋值给变量id，然后打印出来 Declare ID intSet ID = (select top(1) categoryID from categories)Print ID在SQL中，我们不能像代码那样直接给变量赋值，例如id = 1，如果要达到这样的功能，可以这样写： Declare ID intSet ID = (select 1) -类似 ID=1Select id=1 -类似 ID=1Print ID五.变量运算(+,-,*,/,)以下必要时候省略变量申明 Set ID = (select 1+5) -类似 ID=1+5Set ID=(select 1-ID) -类似 ID=1-ID六.比较操作符 ? > (greater than).? < (less than).? = (equals).? <= (less than or equal to).? >= (greater than or equal to).? != (not equal to).? <> (not equal to).? ! < (not less than).? !> (not greater than).七.语句块：Begin end将多条语句作为一个块，类似与C+，C#中的 例如： BeginSet ID1 = (select 1)Set ID2 = (select 2)End八.If， ifelse语法： IF Boolean_expressionsql_statement | statement_blockELSEsql_statement | statement_block例如： If id is not nullPrint id is not nullif ID = 1beginSet ID = (select 1 + 1)endelsebeginset ID=(select 1+2)end上面的例子用到了比较操作符，语句块，和IF的语法。 九.执行其他存储过程 EXEC例如 EXEC dbo.Sales by Year Beginning_Date=1/01/90, Ending_Date=1/01/08十.事务 语法： BEGIN TRANSACTION transaction_name | tran_name_variable例如 BEGIN TRAN-做某些操作，例如Insert into if error <> 0BEGINROLLBACK TRANENDelseBEGINCOMMIT TRANEND十一.游标 我们可以在存储过程中用Select语句取出每一行数据进行操作，这就需要用到游标。 语法： DECLARE cursor_name CURSORLOCAL | GLOBALFORWARD_ONLY | SCROLLSTATIC | KEYSET | DYNAMIC | FAST_FORWARDREAD_ONLY | SCROLL_LOCKS | OPTIMISTICTYPE_WARNINGFOR select_statementFOR UPDATE OF column_name ,.n例如： DECLARE au_id varchar(11), au_fname varchar(20) 申明变量 -申明一个游标 DECLARE authors_cursor CURSOR FORSELECT au_id, au_fname FROM authors-打开游标 OPEN authors_cursor-取出值 FETCH NEXT FROM authors_cursor INTO au_id, au_fname-循环取出游标的值 WHILE FETCH_STATUS = 0BEGINPrint au_idPrint au_fnamePrint FETCH NEXT FROM authors_cursorINTO au_id, au_fnameENDCLOSE authors_cursor 关闭游标 DEALLOCATE authors_cursor -释放游标 例子：我自己做了一个,没有问题,你可以看一下 use Northwind go create proc test StartOrderID int, EndOrderID int, Code varchar(1000) Out As Begin Declare tmp int Set Code= Declare #cur_orders cursor for Select OrderID From Orders where OrderID>=startOrderID and OrderID<=EndOrderID for read only Open #cur_Orders fetch next from #cur_orders into tmp while fetch_Status=0 Begin Set Code=Code+-+convert(varchar(8),tmp) fetch next from #cur_orders into tmp End close #cur_Orders Deallocate #cur_Orders return End go 续2 String ret=null; try Class.forName("com.microsoft.jdbc.sqlserver.SQLServerDriver"); String url ="jdbc:microsoft:sqlserver:/192.168.0.102:1433;DatabaseName=Northwind" String user="sa" String password="" Connection conn= DriverManager.getConnection(url,user,password); CallableStatement stmt=conn.prepareCall("exec test ?,?,?"); stmt.setInt(1,10248); stmt.setInt(2,10284); stmt.registerOutParameter(3,Types.VARCHAR); stmt.setString(3,ret); stmt.execute(); System.out.println(stmt.getString(3); stmt.close(); stmt=null; conn.close(); catch(ClassNotFoundException e) e.printStackTrace(); catch(SQLException e) e.printStackTrace(); 上面的例子没有问题,针对你的情况,我又写了一个,应该可以解决你现在的问题 - 新建一个表 Create table tmpOrders ( OrderID int, CustomerID nchar(5) ) -把Orders 里的OrderID列全部插入,这样Orders与tmpOrders之间就是1:1关系了 insert into tmpOrders Select distinct orderID,tmp from Orders create proc test StartOrderID int, EndOrderID int, Code varchar(1000) Out As Begin Declare newOrderID int Declare newCustomerID nchar(5) Declare DummyInt int Declare DummyChar nchar(5) Set Code= /* 1:1 temp table/formal table is synchronized tmpOrders <-> Orders fetch from Orders, update tmpOrders */ - for temp table Declare #cur_tmpOrders Cursor for select OrderID,CustomerID From tmpOrders where OrderID>=startOrderID and OrderID<=EndOrderID for update -for formal table Declare #cur_orders cursor for Select OrderID,CustomerID From Orders where OrderID>=startOrderID and OrderID<=EndOrderID for read only Open #cur_Orders Open #cur_tmpOrders fetch next from #cur_tmpOrders into DummyInt,dummyChar -Important! fetch next from #cur_orders into NewOrderID,NewCustomerID while fetch_Status=0 Begin -Set Code=Code+-+convert(varchar(8),NewOrderID) -update tempOrders use corresponding Orders data Update tmpOrders set customerID=newCustomerID where current of #cur_tmpOrders -pay attention to sequence of cursor fetch action! fetch next from #cur_tmpOrders into DummyInt,dummyChar if fetch_Status<>0 break; - 没有行了 fetch next from #cur_orders into newOrderID,NewCustomerID End close #cur_Orders close #cur_tmpOrders Deallocate #cur_Orders Deallocate #cur_tmpOrders Set Code=Ok return End 程序如下 try Class.forName("com.microsoft.jdbc.sqlserver.SQLServerDriver"); String url= "jdbc:microsoft:sqlserver:/192.168.0.102:1433;DatabaseName=Northwind" String user="sa" String password="" Connection conn= DriverManager.getConnection(url,user,password); CallableStatement stmt=conn.prepareCall("exec test ?,?,?"); stmt.setInt(1,10248); stmt.setInt(2,10284); stmt.registerOutParameter(3,Types.VARCHAR,1000); stmt.setString(3,ret); stmt.executeUpdate(); System.out.println(stmt.getString(3); stmt.close(); stmt=null; conn.close(); conn=null; catch(ClassNotFoundException e) e.printStackTrace(); catch(SQLException e) e.printStackTrace();