存储过程
sql语句执行的时候要先编译,然后执行。存储过程就是编译好了的一些sql语句。应用程序需要用的时候直接调用就可以了,所以效率会高。
存储过程介绍
存储过程是由流控制和SQL语句书写的过程,这个过程经编译和优化后存储在数据库服务器中,应用程序使用时只要调用即可。在ORACLE中,若干个有联系的过程可以组合在一起构成程序包。
使用存储过程有以下的优点:
* 存储过程的能力大大增强了SQL语言的功能和灵活性。存储过程可以用流控制语句编写,有很强的灵活性,可以完成复杂的判断和较复杂的 运算。
* 可保证数据的安全性和完整性。
# 通过存储过程可以使没有权限的用户在控制之下间接地存取数据库,从而保证数据的安全。
# 通过存储过程可以使相关的动作在一起发生,从而可以维护数据库的完整性。
* 再运行存储过程前,数据库已对其进行了语法和句法分析,并给出了优化执行方案。这种已经编译好的过程可极大地改善SQL语句的性能。 由于执行SQL语句的大部分工作已经完成,所以存储过程能以极快的速度执行。
* 可以降低网络的通信量。
* 使体现企业规则的运算程序放入数据库服务器中,以便:
# 集中控制。
# 当企业规则发生变化时在服务器中改变存储过程即可,无须修改任何应用程序。企业规则的特点是要经常变化,如果把体现企业规则的运算程序放入应用程序中,则当企业规则发生变化时,就需要修改应用程序工作量非常之大(修改、发行和安装应用程序)。如果把体现企业规则的 运算放入存储过程中,则当企业规则发生变化时,只要修改存储过程就可以了,应用程序无须任何变化。
数据库存储过程的实质就是部署在数据库端的一组定义代码以及SQL。
利用SQL的语言可以编写对于数据库访问的存储过程,其语法如下:
CREATE PROC[EDURE] procedure_name [;number] [ {@parameter data_type} ][VARYING] [= default] [OUTPUT] ] [,...n] [WITH { RECOMPILE | ENCRYPTION | RECOMPILE, ENCRYPTION } ] [FOR REPLICATION] AS sql_statement [...n] |
[ ]内的内容是可选项,而()内的内容是必选项,
例: 若用户想建立一个删除表tmp中的记录的存储过程Select_delete可写为:
Create Proc select_del As Delete tmp |
例:用户想查询tmp表中某年的数据的存储过程
create proc select_query @year int as select * from tmp where year=@year |
在这里@year是存储过程的参数
本文从多个角度阐述了SQL Server与存储过程的比较。
①为什么要使用存储过程?
因为它比SQL语句执行快。
②存储过程是什么?
把一堆SQL语句罗在一起,还可以根据条件执行不通SQL语句。
③来一个最简单的存储过程:
CREATE PROCEDURE dbo.testProcedure_AX AS select userID from USERS order by userid desc |
注:dbo.testProcedure_AX是你创建的存储过程名,可以改为:AXzhz等,别跟关键字冲突就行了.AS下面就是一条SQL语句,不会写SQL语句的请回避。
④怎么在ASP.NET中调用这个存储过程:
public static string GetCustomerCName (ref ArrayList arrayCName,ref ArrayList arrayID) { SqlConnection con=ADConnection.createConnection(); SqlCommand cmd=new SqlCommand("testProcedure_AX",con); cmd.CommandType=CommandType.StoredProcedure; con.Open(); try { SqlDataReader dr=cmd.ExecuteReader(); while(dr.Read()) { if(dr[0].ToString()=="") { arrayCName.Add(dr[1].ToString()); } } con.Close(); return "OK!"; } catch(Exception ex) { con.Close(); return ex.ToString(); } } |
注:其实就是把以前:
SqlCommand cmd=new SqlCommand("select userID from USERS order by userid desc",con); |
中的SQL语句替换为存储过程名,再把cmd的类型标注为CommandType.StoredProcedure(存储过程)。
⑤写个带参数的存储过程:
CREATE PROCEDURE dbo.AXzhz /* 这里写注释 */ @startDate varchar(16), @endDate varchar(16) AS select id from table_AX where commentDateTime> @startDate and commentDateTime<@endDate order by contentownerid DESC |
注:@startDate varchar(16)是声明@startDate 这个变量,多个变量名间用【,】隔开.后面的SQL就可以使用这个变量了。
⑥我怎么在ASP.NET中调用这个带参数的存储过程:
public static string GetCustomerCNameCount (string startDate,string endDate,ref DataSet ds) { SqlConnection con=ADConnection.createConnection(); //-----------------------注意这一段------------------- -------------------------------------------------------- ----------------------------- SqlDataAdapter da=new SqlDataAdapter("AXzhz",con); para0=new SqlParameter("@startDate",startDate); para1=new SqlParameter("@endDate",endDate); da.SelectCommand.Parameters.Add(para0); da.SelectCommand.Parameters.Add(para1); da.SelectCommand.CommandType=CommandType.StoredProcedure; //------------------------------------------------------------- ------------------------------------------------------------------ try { con.Open(); da.Fill(ds); con.Close(); return "OK"; } catch(Exception ex) { return ex.ToString(); } } |
注:把命令的参数添加进去,就可以了。
⑦重新验证SQL命令执行是否成功。
CREATE PROCEDURE dbo.AXzhz /* @parameter1 用户名 @parameter2 新密码 */ @password nvarchar(20), @userName nvarchar(20) AS declare @err0 int update WL_user set password=@password where UserName=@userName set @err0=@@error select @err0 as err0 |
注:先声明一个整型变量@err0,再给其赋值为@@error(这个是系统自动给出的语句是否执行成功,0为成功,其它为失败),最后通过select把它选择出来。
⑧那怎么从后台获得这个执行成功与否的值:
下面这段代码可以告诉你答案:
public static string GetCustomerCName() { SqlConnection con=ADConnection.createConnection(); SqlCommand cmd=new SqlCommand("AXzhz",con); cmd.CommandType=CommandType.StoredProcedure; para0=new SqlParameter("@startDate","2006-9-10"); para1=new SqlParameter("@endDate","2006-9-20"); da.SelectCommand.Parameters.Add(para0); da.SelectCommand.Parameters.Add(para1); con.Open(); try { Int32 re=(int32)cmd.ExecuteScalar(); con.Close(); if (re==0) return "OK!"; else return "false"; } catch(Exception ex) { con.Close(); return ex.ToString(); } } |
注:就是通过SqlCommand的ExecuteScalar()方法取回这个值。
⑨我要根据传入的参数判断执行哪条SQL语句:
ALTER PROCEDURE dbo.selectCustomerCNameCount @customerID int AS if @customerID=-1 begin select contentownerid ,userCName,count(*) as countAll from view_usercomment group by contentownerid,userCName order by contentownerid DESC end else begin select contentownerid ,userCName,count(*) as countAll from view_usercomment where contentownerid=@customerID group by contentownerid ,userCName order by contentownerid DESC end |
轻松掌握SQL Server存储过程的命名标准 |
发布时间:2007.12.05 04:58 来源:赛迪网 作者:81291 |
在本文中,此示例标准蓝图的存储过程命名方法只适用于SQL内部,假如你正在创建一个新的存储过程,或是发现一个没有按照这个标准构造的存储过程,即可以参考使用这个标准。 注释:假如存储过程以sp_ 为前缀开始命名那么会运行的稍微的缓慢,这是因为SQL Server将首先查找系统存储过程,所以我们决不推荐使用sp_作为前缀。 存储过程的命名有这个的语法:
(1) 所有的存储过程必须有前缀'proc'. 所有的系统存储过程都有前缀"sp_", 推荐不使用这样的前缀因为会稍微的减慢。 (2) 表名就是存储过程访问的对象。 (3) 可选字段名就是条件子句。 例如:
(4) 最后的行为动词就是存储过程要执行的任务。 如果存储过程返回一条记录那么后缀是:Select 如果存储过程插入数据那么后缀是:Insert 如果存储过程更新数据那么后缀是:Update 如果存储过程有插入和更新那么后缀是:Save 如果存储过程删除数据那么后缀是:Delete 如果存储过程更新表中的数据 (ie. drop and create) 那么后缀是:Create 如果存储过程返回输出参数或0,那么后缀是:Output 例子: 一个仅仅返回一个输出参数的存储过程:
从前的写法:
目前的写法:
|