SQL 存储过程

 

http://daociyiyou111.blog.163.com/blog/static/16470151220116884350369/

 

推荐： 何庆刚博客

最新日志

• 创建一个可移动的DIV
• js 操作select下拉框
• 项目文件""已被重命名或已不
• Ajax的原理和应用
• SQL语句执行顺序分析
• SQL语句执行顺序分析

SQL Server 安全性管理的途径（一部分是视图）

sql 2005 本地临时表VS全局临时表

sql存储过程及应用

 

一、简介：

存储过程（Stored Procedure）， 是一组为了完成特定功能的SQL 语句，集经编译后
存储在数据库中，用户通过指定存储过程的名字并给出参数，如果该存储过程带有参数来执行

它，
在SQL Server 的系列版本中，存储过程分为两类：系统提供的存储过程和用户自定义存储过程

。
系统SP，主要存储master 数据库中，并以sp_为前缀并且系统存储过程主要是从系统表中获取
信息，从而为系统管理员管理SQLServer。用户自定义存储过程是由用户创建，并能完成
某一特定功能，如：查询用户所需数据信息的存储过程。

　　存储过程具有以下优点
1.存储过程允许标准组件式编程(模块化设计)
存储过程在被创建以后，可以在程序中被多次调用，而不必重新编写该存储过程的SQL语句，而

且数
据库专业人员可随时对存储过程进行修改，但对应用程序源代码毫无影响。因为应用程序源代

码只包含存
储过程的调用语句，从而极大地提高了程序的可移植性。

2.存储过程能够实现快速的执行速度
如果某一操作包含大量的Transaction-SQL 代码,，或分别被多次执行，那么存储过程要比批处理

的
执行速度快很多，因为存储过程是预编译的，在首次运行一个存储过程时，查询优化器对其进

行分析优
化，并给出最终被存在系统表中的执行计划，而批处理的Transaction-SQL 语句在每次运行时

都要进行
编译和优化，因此速度相对要慢一些。

3.存储过程能够减少网络流量
对于同一个针对数据数据库对象的操作，如查询修改，如果这一操作所涉及到的Transaction-SQL
语句被组织成一存储过程，那么当在客户计算机上调用该存储过程时，网络中传送的只是该调

用语句，否
则将是多条SQL 语句从而大大增加了网络流量降低网络负载。

4.存储过程可被作为一种安全机制来充分利用
系统管理员通过，对执行某一存储过程的权限进行限制，从而能够实现对相应的数据访问权限的

限
制。


二、变量

@I

三、流程控制语句(if else |select case | while )
Select ... CASE 实例
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

四、存储过程格式

创建存储过程
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 authors


-- Select INTO 从一个查询的计算结果中创建一个新表。数据并不返回给客户端，这一点和普通的
-- Select 不同。 新表的字段具有和 Select 的输出字段相关联（相同）的名字和数据类型。

select * into NewTable
from Uname


-- Insert INTO Select
-- 表ABC必须存在
-- 把表Uname里面的字段Username复制到表ABC
Insert INTO ABC Select Username FROM Uname

-- 创建临时表
Create TABLE #temp(
UID int identity(1, 1) PRIMARY KEY,
UserName varchar(16),
Pwd varchar(50),
Age smallint,
Sex varchar(6)
)
-- 打开临时表
Select * from #temp

-- 存储过程
-- 要创建存储过程的数据库
Use Test
-- 判断要创建的存储过程名是否存在
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,'男'

4.各种存储过程使用指南

<%@ Language=VBScript %>
<%

'---开始链接数据库
Dim strConnString
strConnString = "driver={SQLServer};server=songhp;uid=sa;pwd=;database=XVZDemo"
set Conn = Server.CreateObject("ADODB.Connection")
Conn.Open strConnstring
'---结束链接数据库

'---开始为输入参数赋值
Dim SelectSql , SelectRs
Dim SelectID , SelectName , SelectReturn
SelectSql = "Select Max(CompanyID) From Dim_Company"
Set SelectRs = Conn.Execute(SelectSql)
SelectID = SelectRs(0)
'---结束为输入参数赋值

Dim TiggerType
TiggerType = 3

Set Cmd = Server.CreateObject("ADODB.Command")
Set Cmd.ActiveConnection = Conn
Cmd.CommandType = 4 '---声明此过程为存储过程

If TiggerType = 1 then

'---开始一个输入参数的存储过程调用
Cmd.CommandText = "TransCompany1"

Set CmdParam = Cmd.CreateParameter("@TransID",3,1)
Cmd.Parameters.Append CmdParam
Cmd("@TransID") = SelectID
Cmd.Execute
'---结束一个输入参数的存储过程调用

Elseif TiggerType = 2 then

'---开始一个输入参数，一个输出参数的存储过程调用
Cmd.CommandText = "TransCompany2"

Set CmdParamID = Cmd.CreateParameter("@TransID",3,1)
Cmd.Parameters.Append CmdParamID
Cmd("@TransID") = SelectID
Set CmdParamName = Cmd.CreateParameter("@TransName",202,2,50)
Cmd.Parameters.Append CmdParamName
Cmd.Execute
SelectName = Cmd("@TransName")
'---结束一个输入参数，一个输出参数的存储过程调用

Elseif TiggerType = 3 then

'---开始一个输入参数，一个输出参数，一个返回值的存储过程调用
Cmd.CommandText = "TransCompany3"
Set CmdParamReturn = Cmd.CreateParameter("Return_Value",3,4)
Cmd.Parameters.Append CmdParamReturn
Set CmdParamID = Cmd.CreateParameter("@TransID",3,1)
Cmd.Parameters.Append CmdParamID
Cmd("@TransID") = SelectID
Set CmdParamName = Cmd.CreateParameter("@TransName",202,2,50)
Cmd.Parameters.Append CmdParamName

Cmd.Execute
SelectName = Cmd("@TransName")
SelectReturn = Cmd("Return_Value")
'---结束一个输入参数，一个输出参数，一个返回值的存储过程调用


End if

Conn.Close
Set Conn = Nothing
Set Cmd = Nothing
Set CmdParamID = Nothing
Set CmdParamname = Nothing
Set CmdParamReturn = Nothing

%>

视频地址：http://v.youku.com/v_show/id_XMjY4NDU1NDg4.html