首先介绍一下什么是存储过程:存储过程就是将常用的或很复杂的工作,预先用SQL语句写好并用一个指定的名称存储起来,并且这样的语句是放在数据库
中的,还可以根据条件执行不同SQL语句,那么以后要叫数据库提供与已定义好的存储过程的功能相同的服务时,只需调用execute,即可自动完成命
令。
请大家来了解一下存储过程的语法。
CreatePROC[EDURE]procedure_name[;number]
[{@parameterdata_type}
[VARYING][=default][OUTPUT]
][,...n]
[WITH
{RECOMPILE|ENCRYPTION|RECOMPILE,ENCRYPTION}]
[FORREPLICATION]
ASsql_statement[...n]
一、参数简介
procedure_name
新存储过程的名称。过程名必须符合标识符规则,且对于数据库及其所有者必须唯一。
要创建局部临时过程,可以在procedure_name前面加一个编号符(#procedure_name),要创建全局临时过程,可以
在procedure_name前面加两个编号符(##procedure_name)。完整的名称(包括#或##)不能超过128个字
符。指定过程所有者的名称是可选的。
;number
是可选的整数,用来对同名的过程分组,以便用一条DropPROCEDURE语句即可将同组的过程一起除去。例如,名为orders的应用程
序使用的过程可以命名为orderproc;1、orderproc;2等。DropPROCEDUREorderproc语句将除去整个组。
如果名称中包含定界标识符,则数字不应包含在标识符中,只应在procedure_name前后使用适当的定界符。
@parameter
过程中的参数。在CreatePROCEDURE语句中可以声明一个或多个参数。用户必须在执行过程时提供每个所声明参数的值(除非定义了该参数的默认值)。存储过程最多可以有2100个参数。
使用@符号作为第一个字符来指定参数名称。参数名称必须符合标识符的规则。每个过程的参数仅用于该过程本身;相同的参数名称可以用在其它过程中。默认情况下,参数只能代替常量,而不能用于代替表名、列名或其它数据库对象的名称。
data_type
参数的数据类型。所有数据类型(包括text、ntext和image)均可以用作存储过程的参数。不过,cursor数据类型只能用于OUTPUT参数。如果指定的数据类型为cursor,也必须同时指定VARYING和OUTPUT关键字。
说明:对于可以是cursor数据类型的输出参数,没有最大数目的限制。
VARYING
指定作为输出参数支持的结果集(由存储过程动态构造,内容可以变化)。仅适用于游标参数。
default
参数的默认值。如果定义了默认值,不必指定该参数的值即可执行过程。默认值必须是常量或NULL。如果过程将对该参数使用LIKE关键字,那么默认值中可以包含通配符(%、_、[]和[^])。
OUTPUT
表明参数是返回参数。该选项的值可以返回给EXEC[UTE]。使用OUTPUT参数可将信息返回给调用过程。Text、ntext和image参数可用作OUTPUT参数。使用OUTPUT关键字的输出参数可以是游标占位符。
n
表示最多可以指定2100个参数的占位符。
{RECOMPILE|ENCRYPTION|RECOMPILE,ENCRYPTION}
RECOMPILE表明SQLServer不会缓存该过程的计划,该过程将在运行时重新编译。在使用非典型值或临时值而不希望覆盖缓存在内存中的执行计划时,请使用RECOMPILE选项。
ENCRYPTION表示SQLServer加密syscomments表中包含CreatePROCEDURE语句文本的条目。使用ENCRYPTION可防止将过程作为SQLServer复制的一部分发布。
说明:在升级过程中,SQLServer利用存储在syscomments中的加密注释来重新创建加密过程。
FORREPLICATION
指定不能在订阅服务器上执行为复制创建的存储过程。.使用FORREPLICATION选项创建的存储过程可用作存储过程筛选,且只能在复制过程中执行。本选项不能和WITHRECOMPILE选项一起使用。
AS
指定过程要执行的操作。
sql_statement
过程中要包含的任意数目和类型的Transact-SQL语句。但有一些限制。
n
是表示此过程可以包含多条Transact-SQL语句的占位符。
注释
/*和*/之间的为注释,可以包含一行和多行的说明文字。
其他说明
存储过程的最大大小为128MB。
二、存储过程的优点都有哪些呢?
1.存储过程只在创造时进行编译即可,以后每次执行存储过程都不需再重新编译,而我们通常使用的SQL语句每执行一次就编译一次,所以使用存储过程可提高数据库执行速度。
2.经常会遇到复杂的业务逻辑和对数据库的操作,这个时候就会用SP来封装数据库操作。当对数据库进行复杂操作时(如对多个表进行
Update,Insert,Query,Delete时),可将此复杂操作用存储过程封装起来与数据库提供的事务处理结合一起使用。可以极大的提高数据
库的使用效率,减少程序的执行时间,这一点在较大数据量的数据库的操作中是非常重要的。在代码上看,SQL语句和程序代码语句的分离,可以提高程序代码的
可读性。
3.存储过程可以设置参数,可以根据传入参数的不同重复使用同一个存储过程,从而高效的提高代码的优化率和可读性。
4.安全性高,可设定只有某此用户才具有对指定存储过程的使用权存储过程的种类:
(1)系统存储过程:以sp_开头,用来进行系统的各项设定.取得信息.相关管理工作,如sp_help就是取得指定对象的相关信息。
(2)扩展存储过程以XP_开头,用来调用操作系统提供的功能execmaster..xp_cmdshell'ping10.8.16.1'
(3)用户自定义的存储过程,这是我们所指的存储过程常用格式
模版:Createprocedureprocedue_name[@parameterdata_type][output][with]{recompile|encryption}assql_statement
解释:output:表示此参数是可传回的
with{recompile|encryption}recompile:表示每次执行此存储过程时都重新编译一次;encryption:所创建的存储过程的内容会被加密。
三、实例讲解
实例1:只返回单一记录集的存储过程。
要求1:查询表bankMoney的内容的存储过程
createproceduresp_query_bankMoney
as
select*frombankMoney
go
execsp_query_bankMoney
注*在使用过程中只需要把中的SQL语句替换为存储过程名,就可以了很方便吧!
实例2(向存储过程中传递参数):
加入一笔记录到表bankMoney,并查询此表中userID=Zhangsan的所有存款的总金额。
Createprocinsert_bank@param1char(10),@param2varchar(20),@param3varchar(20),@param4int,@param5intoutput
withencryption---------加密
as
insertbankMoney(id,userID,sex,Money)Values(@param1,@param2,@param3,@param4)
select@param5=sum(Money)frombankMoneywhereuserID='Zhangsan'
go
在SQLServer查询分析器中执行该存储过程的方法是:
declare@total_priceint
execinsert_bank'004','Zhangsan','男',100,@total_priceoutput
print'总余额为'+convert(varchar,@total_price)
go
在这里再啰嗦一下存储过程的3种传回值(方便正在看这个例子的朋友不用再去查看语法内容):
1.以Return传回整数
2.以output格式传回参数
3.Recordset
传回值的区别:
output和return都可在批次程式中用变量接收,而recordset则传回到执行批次的客户端中。
实例3:使用带有复杂Select语句的简单过程
下面的存储过程从四个表的联接中返回所有作者(提供了姓名)、出版的书籍以及出版社。该存储过程不使用任何参数。
USEpubs
IFEXISTS(SelectnameFROMsysobjects
Wherename='au_info_all'ANDtype='P')
DropPROCEDUREau_info_all
GO
CreatePROCEDUREau_info_all
AS
Selectau_lname,au_fname,title,pub_name
FROMauthorsaINNERJOINtitleauthorta
ONa.au_id=ta.au_idINNERJOINtitlest
ONt.title_id=ta.title_idINNERJOINpublishersp
ONt.pub_id=p.pub_id
GO
au_info_all存储过程可以通过以下方法执行:
EXECUTEau_info_all
--or
EXECau_info_all
如果该过程是批处理中的第一条语句,则可使用:
au_info_all
实例4:使用带有参数的简单过程
CreatePROCEDUREau_info
@lastnamevarchar(40),
@firstnamevarchar(20)
AS
Selectau_lname,au_fname,title,pub_name
FROMauthorsaINNERJOINtitleauthorta
ONa.au_id=ta.au_idINNERJOINtitlest
ONt.title_id=ta.title_idINNERJOINpublishersp
ONt.pub_id=p.pub_id
Whereau_fname=@firstname
ANDau_lname=@lastname
GO
au_info存储过程可以通过以下方法执行:
EXECUTEau_info'Dull','Ann'
--or
EXECUTEau_info@lastname='Dull',@firstname='Ann'
--or
EXECUTEau_info@firstname='Ann',@lastname='Dull'
--or
EXECau_info'Dull','Ann'
--or
EXECau_info@lastname='Dull',@firstname='Ann'
--or
EXECau_info@firstname='Ann',@lastname='Dull'
如果该过程是批处理中的第一条语句,则可使用:
au_info'Dull','Ann'
--or
au_info@lastname='Dull',@firstname='Ann'
--or
au_info@firstname='Ann',@lastname='Dull'
实例5:使用带有通配符参数的简单过程
CreatePROCEDUREau_info2
@lastnamevarchar(30)='D%',
@firstnamevarchar(18)='%'
AS
Selectau_lname,au_fname,title,pub_name
FROMauthorsaINNERJOINtitleauthorta
ONa.au_id=ta.au_idINNERJOINtitlest
ONt.title_id=ta.title_idINNERJOINpublishersp
ONt.pub_id=p.pub_id
Whereau_fnameLIKE@firstname
ANDau_lnameLIKE@lastname
GO
au_info2存储过程可以用多种组合执行。下面只列出了部分组合:
EXECUTEau_info2
--or
EXECUTEau_info2'Wh%'
--or
EXECUTEau_info2@firstname='A%'
--or
EXECUTEau_info2'[CK]ars[OE]n'
--or
EXECUTEau_info2'Hunter','Sheryl'
--or
EXECUTEau_info2'H%','S%'
分享到:
相关推荐
首先介绍一下什么是存储过程:存储过程就是将常用的或很复杂的工作,预先用SQL语句写好并用一个指定的名称存储起来,并且这样的语句是放在数据库中的,还可以根据条件执行不同SQL语句, 那么以后要叫数据库提供与已...
一些sql 存储过程语句的总结 对于不熟悉存储过程的人很有帮助
SQLPrompt for SQLServer2016 智能提示插件 SQL2016 提示 SQLPrompt最新版本 绿色版 SQL Prompt 是一款拥有SQL智能提示功能的SQL Server和VS插件。SQL Prompt能根据数据库的对象名称,语法和用户编写的代码片段自动...
阅读实验教材《SQL Server 2012数据库管理与开发》第137页到155页的内容,掌握存储过程的概念、了解存储过程的类别(系统存储过程,用户自定义存储过程,扩展存储过程);掌握存储过程的建立;掌握存储过程的两种...
如果不熟悉sqlserver可以使用数据库引擎优化顾问来对数据库提出优化建议,然后通过系统管理的修改达到目的。 数据库引擎优化顾问 数据库引擎优化顾问介绍 分析一个或多个数据库的工作负荷和物理实现,工作负荷...
1.熟悉大型数据库实验环境,以MS SQL SERVER为例; 2.掌握视图; 3.掌握存储过程与触发器; 4.掌握MS SQL SERVER的导入和导出; 5.掌握MS SQL SERVER的索引。 二、实验内容 (1)使用“实验一”中的数据库“abc”,...
此版本被更新,以涵盖查询存储中等待统计信息的分析、CXCONSUMER等待事件以及SQLServer 2019年的最新情况。无论您是刚刚开始等待统计,还是已经熟悉这些统计信息,这本书提供了关于等待统计信息是如何生成的以及它们...
曾有网友在CGI论坛发表过类似的程序,...因为调用的系统存储过程是属于ms sql server的,因此本程序只在ms sql server上测试过( 也许能用在sybase sql server上,熟悉其他数据库系统的人员可以根据此原理自行修改源码)
SQLServer数据库开发课程,熟悉 SQL Servers管理开发环境,理解和运用主要的SQL语法,能够编写 SQL Server函数和存储过程,掌握基本的 SQL Server数据库开发知识。
本书以学生和教师都非常熟悉的“教学成绩管理信息系统”为案例贯穿全书例题、习题,目的使同学们以“教学成绩管理系统”为案例学会SQL数据库管理与编程开发技能。本书例题、实训、习题中所有案例的库、表、视图、...
做ERP管理软件的,主要核心就在数据存储管理上。所以数据库设计是我们的重中之重。为了让我们的管理软件能够稳定、可扩展、性能优秀、可...而我本人呢,只熟悉SQLSERVER这一种数据库产品,所以我就拿SQLSERVER来分析
然而,在SQL Server 2005中,我们可以用.NET家族的语言——主要是VB.NET和C#来编写存储过程(以及方法、触发器和其它组件)。让我们来熟悉一下关于编写存储过程新方法的5个常见问题。它们是非常值得我们探讨的。 1、...
演示:打开企业管理器,展开服务器,详细分析各个系统数据库和系统表 6、了解SQL Server 的主要管理工具,熟悉企业管理器、查询分析器。 (1)讲授SQL Server 的主要管理工具,分别打开九个工具,演示分析企业管理...
SQL Prompt 是一款拥有SQL智能提示功能的SQL Server和VS插件。SQL Prompt能根据数据库的对象名称,语法和用户编写的代码片段自动进行检索,智能的为用户提供唯一合适的代码选择。自动脚本设置为用户提供了简单的代码...
本书以学生和教师都非常熟悉的“教学成绩管理信息系统”为案例贯穿全书例题、习题,目的使同学们以“教学成绩管理系统”为案例学会SQL数据库管理与编程开发技能。本书例题、实训、习题中所有案例的库、表、视图、...
基于c#的三层架构,数据库SQL Server 2000,使用参数查询,提高了保密性。不过三层架构编写起来比较麻烦,但是维护起来效果还是不错的,请大家多多指教!
熟悉SQL Server的开发人员和DBA通过将他们已有的知识与Azure开发和管理的新功能相结合,应该可以迅速地掌握它的使用方法。在本次的技术手册中,我们将为您奉上最完整的SQL Azure教程,从入门到深层次了解微软云...
描述了sql server的一些小技巧,例如:日期格式的转换、行列转换、存储过程、视图、触发器以及设置数据表结构的一些常见技巧。
BDB是跨数据库平台的数据库设计和自动安装工具、支持Oracle、SQLServer、Access、MySQL、SQLAnyWhere数据库。 通过BDB可以快速建立数据库模型、并随时与实体数据库进行双向同步。 同时它还可以为您的应用程序创建...
通过系统存储过程或企业管理器可以创建新的数据库用户。 角色的管理 创建一个角色,使创建的用户成为该角色的成员,并授予一定的操作权限。 在学生表中定义主键、外键约束。 在课程表的“课程名”字段上定义...