存储过程学习笔记.docx
- 文档编号:3274105
- 上传时间:2022-11-21
- 格式:DOCX
- 页数:9
- 大小:20.71KB
存储过程学习笔记.docx
《存储过程学习笔记.docx》由会员分享,可在线阅读,更多相关《存储过程学习笔记.docx(9页珍藏版)》请在冰豆网上搜索。
存储过程学习笔记
SQLSERVER存储过程学习笔记
关键字:
ms-sql
将常用的或很复杂的工作,预先用SQL语句写好并用一个指定的名称存储起来,那么以后要叫数据库提供与已定义好的存储过程的功能相同的服务时,只需调用execute,即可自动完成命令。
存储过程的优点
1.存储过程只在创造时进行编译,以后每次执行存储过程都不需再重新编译,而一般SQL语句每执行一次就编译一次,所以使用存储过程可提高数据库执行速度。
2.当对数据库进行复杂操作时(如对多个表进行Update,Insert,Query,Delete时),可将此复杂操作用存储过程封装起来与数据库提供的事务处理结合一起使用。
3.存储过程可以重复使用,可减少数据库开发人员的工作量
4.安全性高,可设定只有某此用户才具有对指定存储过程的使用权
创建存储过程
*************************************************
语法
CREATEPROC[EDURE][owner.]procedure_name[;number]
[{@parameterdata_type}
[VARYING][=default][OUTPUT]
][,...n]
[WITH
{RECOMPILE|ENCRYPTION|RECOMPILE,ENCRYPTION}]
[FORREPLICATION]
ASsql_statement[...n]
参数
owner
拥有存储过程的用户ID的名称。
owner必须是当前用户的名称或当前用户所属的角色的名称。
procedure_name
新存储过程的名称。
过程名必须符合标识符规则,且对于数据库及其所有者必须唯一。
;number
是可选的整数,用来对同名的过程分组,以便用一条DROPPROCEDURE语句即可将同组的过程一起除去。
例如,名为orders的应用程序使用的过程可以命名为orderproc;1、orderproc;2等。
DROPPROCEDUREorderproc语句将除去整个组。
如果名称中包含定界标识符,则数字不应包含在标识符中,只应在procedure_name前后使用适当的定界符。
@parameter
过程中的参数。
在CREATEPROCEDURE语句中可以声明一个或多个参数。
用户必须在执行过程时提供每个所声明参数的值(除非定义了该参数的默认值,或者该值设置为等于另一个参数)。
存储过程最多可以有2.100个参数。
使用@符号作为第一个字符来指定参数名称。
参数名称必须符合标识符的规则。
每个过程的参数仅用于该过程本身;相同的参数名称可以用在其它过程中。
默认情况下,参数只能代替常量,而不能用于代替表名、列名或其它数据库对象的名称。
data_type
参数的数据类型。
除table之外的其他所有数据类型均可以用作存储过程的参数。
但是,cursor数据类型只能用于OUTPUT参数。
如果指定cursor数据类型,则还必须指定VARYING和OUTPUT关键字。
对于可以是cursor数据类型的输出参数,没有最大数目的限制。
VARYING
指定作为输出参数支持的结果集(由存储过程动态构造,内容可以变化)。
仅适用于游标参数。
default
参数的默认值。
如果定义了默认值,不必指定该参数的值即可执行过程。
默认值必须是常量或NULL。
如果过程将对该参数使用LIKE关键字,那么默认值中可以包含通配符(%、_、[]和[^])。
OUTPUT
表明参数是返回参数。
该选项的值可以返回给EXEC[UTE]。
使用OUTPUT参数可将信息返回给调用过程。
Text、ntext和image参数可用作OUTPUT参数。
使用OUTPUT关键字的输出参数可以是游标占位符。
n
表示最多可以指定2.100个参数的占位符。
{RECOMPILE|ENCRYPTION|RECOMPILE,ENCRYPTION}
RECOMPILE表明SQLServer不会缓存该过程的计划,该过程将在运行时重新编译。
在使用非典型值或临时值而不希望覆盖缓存在内存中的执行计划时,请使用RECOMPILE选项。
ENCRYPTION表示SQLServer加密syscomments表中包含CREATEPROCEDURE语句文本的条目。
使用ENCRYPTION可防止将过程作为SQLServer复制的一部分发布。
FORREPLICATION
指定不能在订阅服务器上执行为复制创建的存储过程。
.使用FORREPLICATION选项创建的存储过程可用作存储过程筛选,且只能在复制过程中执行。
本选项不能和WITHRECOMPILE选项一起使用。
AS
指定过程要执行的操作。
sql_statement
过程中要包含的任意数目和类型的Transact-SQL语句。
但有一些限制。
n
是表示此过程可以包含多条Transact-SQL语句的占位符。
**********************************************
几个实例
(AjaxCity表中内容)
ID CityName Short
1 苏州市 SZ
2 无锡市 WX
3 常州市 CZ
1.选择表中所有内容并返回一个数据集
CREATEPROCEDUREmysp_All
AS
select*fromAjaxCity
GO
Execmysp_ALL
2.根据传入的参数进行查询并返回一个数据集
CREATEPROCEDUREmysp_para
@CityNamevarchar(255),
@Short varchar(255)
AS
select*fromAjaxCitywhereCityName=@CityName AndShort=@Short
GO
Exec
3.带有输出参数的存储过程(返回前两条记录的ID的和)
CREATEPROCEDUREmysp_output
@SUMint output
AS
select@SUM=sum([ID])from(selecttop2*fromAjaxCity)astmpTable
GO
4.在存储过程中使用游标
有这样一个表,存储的是各超阶级市下面的县级市的信息.
现在想统计出各个地级市下面的县级市的个数,并组成一个字符串.结果应该是"5,2,2".
CREATEPROCEDUREmysp_Cursor
@Resultvarchar(255)output//声明输出变量
AS
declarecity_cursorcursorfor//声明游标变量
select[ID]fromAjaxCity
set@Result=''
declare@Fieldint//声明临时存放CityID的变量
opencity_cursor//打开游标
fetchnextfromcity_cursorinto@Field//将实际ID赋给变量
while(@@fetch_status=0)//循环开始
begin
if@Result=''
select@Result=convert(nvarchar
(2),count(*)) fromAjaxCountywhereCityID=@Field
else
select@Result=@Result+','+convert(nvarchar
(2),count(*))fromAjaxCountywhereCityID=@Field
fetchnextfromcity_cursorinto@Field//下一个CityID
end
closecity_cursor//关闭游标
deallocatecity_cursor//释放游标引用
GO
好了,关于存储过程先写到这里.以上几个例子基本上实现了平常所用到的大部分功能.至于复杂的存储过程,所用到的知道主要是SQL的语法,以及SQL中内置函数的使用.已不属于本文所要讨论的范围了.
sql 存储过程学习一
一、简介:
存储过程(StoredProcedure),是一组为了完成特定功能的SQL语句,集经编译后存储在数据库中,用户通过指定存储过程的名字并给出参数,如果该存储过程带有参数来执行它,在SQLServer的系列版本中,存储过程分为两类:
系统提供的存储过程和用户自定义存储过程.
系统SP,主要存储master数据库中,并以sp_为前缀并且系统存储过程主要是从系统表中获取信息,从而为系统管理员管理SQLServer。
用户自定义存储过程是由用户创建,并能完成某一特定功能,如:
查询用户所需数据信息的存储过程。
存储过程具有以下优点
1.存储过程允许标准组件式编程(模块化设计)
存储过程在被创建以后,可以在程序中被多次调用,而不必重新编写该存储过程的SQL语句,而且数据库专业人员可随时对存储过程进行修改,但对应用程序源代码毫无影响。
因为应用程序源代码只包含存储过程的调用语句,从而极大地提高了程序的可移植性。
2.存储过程能够实现快速的执行速度
如果某一操作包含大量的Transaction-SQL代码,,或分别被多次执行,那么存储过程要比批处理的执行速度快很多,因为存储过程是预编译的,在首次运行一个存储过程时,查询优化器对其进行分析优化,并给出最终被存在系统表中的执行计划,而批处理的Transaction-SQL语句在每次运行时都要进行编译和优化,因此速度相对要慢一些。
3.存储过程能够减少网络流量
对于同一个针对数据数据库对象的操作,如查询修改,如果这一操作所涉及到的Transaction-SQL语句被组织成一存储过程,那么当在客户计算机上调用该存储过程时,网络中传送的只是该调用语句,否则将是多条SQL语句从而大大增加了网络流量降低网络负载。
4.存储过程可被作为一种安全机制来充分利用系统管理员通过,对执行某一存储过程的权限进行限制,从而能够实现对相应的数据访问权限的限制。
二、流程控制语句(ifelse|selectcase|while)
Select...CASE实例
DECLARE@iRetINT,@PKDispVARCHAR(20)
SET@iRet='1'
Select@iRet=
CASE
WHEN@PKDisp='一'THEN1
WHEN@PKDisp='二'THEN2
WHEN@PKDisp='三'THEN3
WHEN@PKDisp='四'THEN4
WHEN@PKDisp='五'THEN5
ELSE100
END
三、存储过程格式
创建存储过程
CreateProceduredbo.存储过程名
存储过程参数
AS
执行语句
RETURN
执行存储过程
GO
*********************************************************/
四、变量的有关操作
--变量的声明,sql里面声明变量时必须在变量前加@符号
DECLARE@IINT
--变量的赋值,变量赋值时变量前必须加set
SET@I=30
--声明多个变量
DECLARE@svarchar(10),@aINT
--Sql里if语句
IF条件
BEGIN
执行语句
END
ELSE
BEGIN
执行语句
END
举例说明:
DECLARE@dINT
set@d=1
IF@d=1
BEGIN
--打印
PRINT'正确'
END
ELSE
BEGIN
PRINT'错误'
END
--Sql里的多条件选择语句.
DECLARE@iRetINT,@PKDispVARCHAR(20)
SET@iRet=1
Select@iRet=
CASE
WHEN@PKDisp='一'THEN1
WHEN@PKDisp='二'THEN2
WHEN@PKDisp='三'THEN3
WHEN@PKDisp='四'THEN4
WHEN@PKDisp='五'THEN5
ELSE100
END
--循环语句
WHILE条件
BEGIN
执行语句
END
DECLARE@iINT
SET@i=1
WHILE@i<1000000
BEGIN
set@i=@i+1
END
--打印
PRINT@i
--TRUNCATE删除表中的所有行,而不记录单个行删除操作,不能带条件
TRUNCATETABLEauthors(表名)
--SelectINTO从一个查询的计算结果中创建一个新表。
数据并不返回给客户端,这一点和普通的Select不同。
新表的字段具有和Select的输出字段相关联(相同)的名字和数据类型。
select*intoNewTablefromUname
--InsertINTOSelect
--表ABC必须存在
--把表Uname里面的字段Username复制到表ABC
InsertINTOABCSelectUsernameFROMUname
--创建临时表
CreateTABLE#temp(
UIDintidentity(1,1)PRIMARYKEY,
UserNamevarchar(16),
Pwdvarchar(50),
Agesmallint,
Sexvarchar(6)
)
--打开临时表
Select*from#temp
完整存储过程的形式
--存储过程
--要创建存储过程的数据库
UseTest
--判断要创建的存储过程名是否存在
--sysobjects在数据库内创建的每个对象(约束、默认值、日志、规则存储过程等)在表中占一行。
只有在tempdb内,每个临时对象才在该表中占一行。
--sysdatabases数据库表(在masters数据内才能查到)
ifExists(SelectnameFromsysobjectsWherename='csp_AddInfo'Andtype='P')
--删除存储过程
DropProceduredbo.csp_AddInfo
Go
--创建存储过程
CreateProcdbo.csp_AddInfo
--存储过程参数
@UserNamevarchar(16),
@Pwdvarchar(50),
@Agesmallint,
@Sexvarchar(6)
--@allamountfloatoutput
AS
--存储过程语句体
insertintoUname(UserName,Pwd,Age,Sex)
values(@UserName,@Pwd,@Age,@Sex)
RETURN
GO
--执行存储过程
EXECcsp_AddInfo'Junn.A','123456',20,'男'
--执行带输出参数的存储过程
Declare@allamountfloat
EXECcsp_AddInfo'Junn.A','123456',20,'男'@allaountoutput
Select@allamount
Print‘总金额是:
’+convert(varchar(20),@allamount)
- 配套讲稿:
如PPT文件的首页显示word图标,表示该PPT已包含配套word讲稿。双击word图标可打开word文档。
- 特殊限制:
部分文档作品中含有的国旗、国徽等图片,仅作为作品整体效果示例展示,禁止商用。设计者仅对作品中独创性部分享有著作权。
- 关 键 词:
- 存储 过程 学习 笔记
![提示](https://static.bdocx.com/images/bang_tan.gif)