MSSQL SERVER认识存储过程语法

大旨提示:创设存款和储蓄进度,存款和储蓄进度是保存起来的能够承担和重返顾客提供的参数的
Transact-SQL 语句的会合

CREATE PROCEDURE
成立存款和储蓄进度,存款和储蓄进度是保存起来的能够担当和再次来到客商提供的参数的
Transact-SQL 语句的集纳。
能够创制贰个经过供永恒使用,或在八个对话中权且使用,或在富有会话中不常选拔。也足以成立在
Microsoft? SQL Server? 运转时自动运营的储存进程。 语法 CREATE PROC [
EDURE ] procedure_name [ ; number ] [ { @parameter data_type } [
VARYING ] [ = default ] [ OUTPUT ] ] [ ,…n ] [ WITH {
RECOMPILE | ENCRYPTION | RECOMPILE , ENCRYPTION } ] [ FOR REPLICATION
] AS sql_statement [ …n ] 参数 procedure_name
新存款和储蓄进程的名称。进度名必得适合标记符准则,且对于数据库及其主人必需独一。有关越来越多音信,请参见使用标志符。
要开创局地一时进度,能够在 procedure_name 后面加三个数码符
(#procedure_name卡塔尔国,要创设全局不经常进度,能够在 procedure_name
前边加七个号码符 (##procedure_name卡塔尔(قطر‎。完整的名目无法凌驾 1二十七个字符。钦赐进程全部者的名号是可选的。 ;number
是可选的大背头,用来对同名的历程分组,以便用一条 DROP PROCEDURE
语句就可以将同组的长河一同除去。比方,名字为 orders
的应用程序使用的进程能够命名称为 orderproc;1、orderproc;2 等。DROP
PROCEDURE orderproc
语句将除了整个组。借使名称中蕴藏定界标记符,则数字不应包含在标志符中,只应在
procedure_name 前后使用合适的定界符。 @parameter 进度中的参数。在
CREATE PROCEDURE
语句中能够声Bellamy(AptamilState of Qatar个或多少个参数。客商必得在推行进程时提供每一种所评释参数的值。存款和储蓄进度最多能够有
2.100 个参数。 使用 @
符号作为第叁个字符来内定参数名称。参数名称必须相符标志符的平整。每一种进程的参数仅用于该进程本身;雷同的参数名称可以用在其他进度中。暗中认可意况下,参数只好替代常量,而不能够用来替代表名、列名或别的数据库对象的名称。有关越多新闻,请参见
EXECUTE。 data_type
参数的数据类型。全数数据类型均可以看作存款和储蓄进程的参数。可是,cursor
数据类型只可以用于 OUTPUT 参数。若是钦赐的数据类型为
cursor,也亟须同一时候内定 VA兰德库罗德YING 和 OUTPUT 关键字。有关 SQL Server
提供的数据类型及其语法的愈来愈多音讯,请参见数据类型。 表达 对于能够是
cursor 数据类型的输出参数,未有最大数额的范围。 VAEscortYING
内定作为出口参数协助的结果集。仅适用于游标参数。 default
参数的暗许值。假使定义了暗中同意值,不必钦赐该参数的值就可以实践进程。暗中同意值必需是常量或
NULL。假若经过将对该参数使用 LIKE 关键字,那么暗中同意值中得以分包通配符。
OUTPUT 表明参数是回去参数。该接纳的值可以回去给 EXEC[UTE]。使用 OUTPUT
参数可将消息再次来到给调用进度。Text、ntext 和 image 参数可用作 OUTPUT
参数。使用 OUTPUT 关键字的输出参数能够是游标占位符。 表示最多能够钦命2.100 个参数的占位符。 {RECOMPILE | ENC景逸SUVYPTION | RECOMPILE, ENCLX570YPTION}
RECOMPILE 注解 SQL Server
不会缓存该进程的布署,该进度就要运作时再也编写翻译。在选择非规范值或不经常值而不指望覆盖缓存在内部存款和储蓄器中的推行计划时,请使用
RECOMPILE 选项。 ENCEvoqueYPTION 代表 SQL Server 加密 syscomments 表中含有
CREATE PROCEDURE 语句文本的条目款项。使用 ENCGL450YPTION 可幸免将经过作为 SQL
Server 复制的一某个发表。 表达 在升级历程中,SQL Server 利用存款和储蓄在
syscomments 中的加密注释来再一次创造加密进度。 FOEvoque REPLICATION
钦命不可能在订阅服务器上履行为复制创造的存款和储蓄进度。.使用 FOLacrosse REPLICATION
选项创设的蕴藏进程可用作存款和储蓄进程筛选,且只好在复制进度中实行。本选项不可能和
WITH RECOMPILE 选项一齐行使。 AS 钦定进度要进行的操作。 sql_statement
进程中要含有的随便数目和花色的 Transact-SQL 语句。但有一点节制。
是表示此进程能够分包多条 Transact-SQL 语句的占位符。 注释
存款和储蓄进度的最大尺寸为 128 MB。
顾客定义的储存进度只好在当前数据库中开创。在单个批管理中,CREATE
PROCEDURE 语句无法与其他 Transact-SQL 语句组合使用。
暗中认可情形下,参数可为空。要是传递 NULL 参数值何况该参数在 CREATE 或 ALTER
TABLE 语句中动用,而该语句中援用的列又不准选拔 NULL,则 SQL Server
会发生一条错误音信。为了防止向不容许利用 NULL 的列传递 NULL
参数值,应向进度中增多编制程序逻辑或为该列使用暗中同意值。假设某些连接实施的存款和储蓄进程对这一个采用的设置与创造该进程的接连的安装分歧,则为第三个三回九转成立的表列恐怕会有两样的为空性,并且展现出分化的一颦一笑艺术。要是为各样列显式证明了
NULL 或 NOT
NULL,那么将对负有试行该存款和储蓄进度的连年使用同一的为空性创制有的时候表。
在成立或转转移存入款和储蓄进度时,SQL Server 将保存 SET QUOTED_IDENTIFIER 和 SET
ANSI_NULLS
的安装。执行存款和储蓄进度时,将利用那些原来设置。因而,全部顾客端会话的 SET
QUOTED_IDENTIFIER 和 SET ANSI_NULLS
设置在履行存款和储蓄进程时都将被忽略。在存款和储蓄进程中冒出的 SET
QUOTED_IDENTIFIER 和 SET ANSI_NULLS 语句不影响存款和储蓄进度的效果与利益。 别的SET
选项在开立或改造存储进度时不保留。借使存款和储蓄进程的逻辑决意于特定的装置,应在经过开头加多一条
SET 语句,以保险设置科学。从存款和储蓄进程中施行 SET
语句时,该装置只在存款和储蓄进程做到早先有效。之后,设置将复苏为调用存款和储蓄进程时的值。那使各自的顾客端能够设置所需的选项,而不会影响存款和储蓄进程的逻辑。
表明 SQL Server
是将空字符串解释为单个空格照旧分解为确实的空字符串,由十分品级设置调节。要是协作品级小于或等于
65,SQL Server 就将空字符串解释为单个空格。假设同盟等级等于 70,则 SQL
Server 将空字符串解释为空字符串。有关更加多音讯,请参见 sp_dbcmptlevel。
得到有关仓储进度的消息若要呈现用来创制进度的文书,请在进度所在的数据库中实践sp_helptext,并应用进程名作为参数。 表达 使用 ENCKoleosYPTION
选项创造的蕴藏过程不能够选取 sp_helptext 查看。
若要来得有关进度援用的对象的报表,请使用 sp_depends。
若要为进度重命名,请使用 sp_rename。 援用对象 SQL Server
允许创立的储存进程援引尚子虚乌有的靶子。在创制时,只实行语法检查。推行时,纵然高速缓存中尚无有效的安排,则编写翻译存款和储蓄进程以生成试行布置。独有在编写翻译进度中才深入分析存款和储蓄进程中引用的有着指标。由此,倘使语法精确的存放进程引用了不设有的靶子,则仍可以够成功开创,但在运转时将停业,因为所引述的目的不设有。有关越来越多音信,请参见延迟名称解析和编写翻译。
延迟名称深入分析和包容等级 SQL Server 允许 Transact-SQL
存款和储蓄进度在创建时引用不设有的表。这种力量称为延迟名称解析。不过,假如Transact-SQL 存款和储蓄进程引用了该存款和储蓄进度中定义的表,而合作等级设置为
65,则在创造时会发出警报新闻。而要是在运转时所引用的表不真实,将回到错误新闻。有关越来越多消息,请参见
sp_dbcmptlevel 和推迟名称拆解剖判和编写翻译。 试行存款和储蓄进度 成功施行 CREATE
PROCEDURE 语句后,进程名称将积攒在 sysobjects 系统表中,而 CREATE
PROCEDURE 语句的公文将积累在 syscomments
中。第三遍执行时,将编译该进程以分明检索数据的最好访问铺排。 使用 cursor
数据类型的参数 存款和储蓄进程只可以将 cursor 数据类型用于 OUTPUT
参数。假若为有个别参数内定了 cursor 数据类型,也不得不钦命 VATiggoYING 和 OUTPUT
参数。假诺为有些参数钦点了 VA奥迪Q3YING 关键字,则数据类型必需是
cursor,并且必需钦定 OUTPUT 关键字。 表达 cursor 数据类型不能够通过数据库
API绑定到应用程序变量上。因为必得先绑定 OUTPUT
参数,应用程序才足以试行存款和储蓄进度,所以饱含 cursor OUTPUT
参数的积攒进程不能够由此数据库 API 调用。唯有将 cursor OUTPUT 变量赋值给
Transact-SQL 局地 cursor 变量时,才得以经过 Transact-SQL
批管理、存款和储蓄进程或触发器调用那么些进度。 Cursor 输出参数
在推行进程时,以下法规适用于 cursor 输出参数:
对于只进游标,游标的结果集中重返的行只是那个存款和储蓄进程举办实现时处于或当先游标地点的行,比如:
在进度中的名字为 LANDS 的 100 行结果集上展开一个非滚动游标。 进程提取结果聚焦华VS 的头 5 行。 进度重临到其调用者。 重回到调用者的结果集 LacrosseS 由 CR-VS 的第 6
到 100 行组成,调用者中的游标处于 EscortS 的首先行在此以前。
对于只进游标,假设存储进程一鼓作气后,游标坐落于第一行的眼前,则整个结果集将再次回到给调用批处理、存款和储蓄进程或触发器。再次来到时,游标将放在第一行的前方。
对于只进游标,假设存款和储蓄进度完毕后,游标的职责超出最后一行的末尾,则为调用批管理、存款和储蓄进度或触发器再次回到空结果集。
表达 空结果集与空值差别。
对于可滚动游标,在蕴藏进度试行完成时,结果聚集的有所行均会重返给调用批管理、存款和储蓄进度或触发器。再次来到时,游标保留在进度中最终贰回施行提取时的职责。
对于自由档案的次序的游标,假如游标关闭,则将空值传递回调用批处理、存款和储蓄进度或触发器。就算将游标指使给七个参数,但该游标从未展开过,也会身不由己这种状态。
说明关闭状态唯有在重临时才有震慑。举例,可以在经过中关闭游标,稍后再打开游标,然后将该游标的结果集重返给调用批管理、存款和储蓄进度或触发器。
有的时候存款和储蓄进程 SQL Server
援救三种临时进程:局地有的时候进程和全局一时进程。局地一时进度只好由创立该进度的接连使用。全局一时进程则可由具有连接使用。局地有的时候过程在时下对话停止时自动除去。全局有的时候进程在采纳该进程的末段一个对话停止时除了。常常是在开立该进度的对话结束时。
临时进度用 # 和 ##
命名,能够由其余顾客创设。创设进度后,局地进度的全数者是唯一能够应用该进度的顾客。执香港行政局地有时进程的权力不能够授予别的客户。假使成立了大局一时进度,则怀有顾客均能够访谈该进程,权限无法显式裁撤。唯有在
tempdb 数据库中兼有显式 CREATE PROCEDURE
权限的顾客,才足以在该数据库中显式制造有的时候进程。能够给予或扬弃那个进度中的权限。
表达 频仍使用有时存款和储蓄进度会在 tempdb
中的系统表上发出争用,进而对品质产生消极的一面影响。提议接受 sp_executesql
代替。sp_executesql 不在系统表中积累数据,因而得避防止这一难题。
自动试行存款和储蓄过程 SQL Server
运维时能够自动实行一个或三个存款和储蓄进程。那一个囤积进程必需由系统管理员创立,并在
sysadmin 固定服务器角色下作为后台进程实行。这几个进程不能够有此外输入参数。
对运营进程的多少未有界定,可是要注意,各样运营进程在举办时都会攻下一个老是。如若非得在运行时推行五个进程,但不需求并行实践,则足以钦定三个历程作为运维进度,让该进度调用此外进程。那样就只占用叁个连连。
在运营时恢复了最终一个数据库后,即开首执行存款和储蓄进程。若要跳过那些囤积进度的试行,请将运维参数钦定为追踪标记4022。纵然以低于配置运转 SQL
Server,则运行存款和储蓄进程也不会实施。有关更加的多音讯,请参见追踪标识。
若要开创运行存款和储蓄进程,必得作为 sysadmin 固定服务器角色的分子登陆,并在
master 数据库中开创存款和储蓄进程。 使用 sp_procoption 能够:
将现成存款和储蓄进度钦定为运营进度。 停止在 SQL Server 运行时实践进度。 查看
SQL Server 运营时实施的具有进度的列表。 存款和储蓄进程嵌套
存款和储蓄进程能够嵌套,即一个累积进程能够调用另贰个囤积进程。在被调用进度早先进行时,嵌套级将净增,在被调用进程推行完成后,嵌套级将精减。若是超过最大的嵌套级,会使一切调用进程链战败。可用
@@NESTLEVEL 函数再次回到当前的嵌套级。
若要推断编译后的存放进程大小,请使用下列品质监视流速計。
品质监视器对象名 质量监视计数器名称 SQLServer:缓冲区微处理机 高速缓存大徐熙娣(Elephant Dee卡塔尔QLServer:高速缓存微处理器 高速缓存命中率 高速缓存页 高速缓存对象计数*
* 各样分类的高速缓存对象均能够采取那一个计数器,饱含特殊 sql、打算sql、进程、触发器等。 有关更加的多新闻,请参见 SQL Server:Buffer Manager
对象和 SQL Server:Cache Manager 对象。 sql_statement 限制 除了 SET
SHOWPLAN_TEXT 和 SET SHOWPLAN_ALL 之外,任何 SET
语句均可以在仓库储存进程之中内定。所选用的 SET
选项在存款和储蓄进程推行进度中央银卓有成效,之后苏醒为本来的装置。
倘使别的客商要选用某些存款和储蓄进度,那么在该存款和储蓄进度里面,一些言语使用的指标名必需接收对象全部者的名目限制。那些语句蕴涵:
ALTEHighlander TABLE CREATE INDEX CREATE TABLE 全体 DBCC 语句 DROP TABLE DROP
INDEX TRUNCATE TABLE UPDATE STATISTICS 权限 CREATE PROCEDURE
的权位默许给与 sysadmin 固定服务器剧中人物成员和 db_owner 和 db_ddladmin
固定数据库剧中人物成员。sysadmin 固定服务器剧中人物成员和 db_owner
固定数据库角色成员能够将 CREATE PROCEDURE
权限转让给其余客商。实践存款和储蓄进度的权杖授予进程的持有者,该全体者可认为别的数据库客商安装举行权限。
示例 A. 使用含有复杂 SELECT 语句的粗略进度上面包车型客车积累进度从八个表的连结中回到全体作者、出版的书本以致书局。该存款和储蓄进程不行使别的参数。
USE pubs IF EXISTS (SELECT name FROM sysobjects WHERE name =
‘au_info_all’ AND type = ‘P’) DROP PROCEDURE au_info_all GO CREATE
PROCEDURE au_info_all AS SELECT au_lname, au_fname, title, pub_name
FROM authors a INNER JOIN titleauthor ta ON a.au_id = ta.au_id INNER
JOIN titles t ON t.title_id = ta.title_id INNER JOIN publishers p ON
t.pub_id = p.pub_id GO au_info_all 存款和储蓄进度能够透过以下措施实践:
EXECUTE au_info_all — Or EXEC au_info_all
如若该进度是批管理中的第一条语句,则可接纳: au_info_all B.
使用带有参数的简易过程上边包车型地铁囤积进程从八个表的对接中只回去钦赐的撰稿人、出版的书籍以至书局。该存款和储蓄过程选拔与传递的参数准确匹配的值。
USE pubs IF EXISTS (SELECT name FROM sysobjects WHERE name = ‘au_info’
AND type = ‘P’) DROP PROCEDURE au_info GO USE pubs GO CREATE PROCEDURE
au_info @lastname varchar(40), @firstname varchar(20) AS SELECT
au_lname, au_fname, title, pub_name FROM authors a INNER JOIN
titleauthor ta ON a.au_id = ta.au_id INNER JOIN titles t ON
t.title_id = ta.title_id INNER JOIN publishers p ON t.pub_id =
p.pub_id WHERE au_fname = @firstname AND au_lname = @lastname GO
au_info 存款和储蓄进程能够由此以下形式实行: EXECUTE au_info ‘Dull’, ‘Ann’
— Or EXECUTE au_info @lastname = ‘Dull’, @firstname = ‘Ann’ — Or
EXECUTE au_info @firstname = ‘Ann’, @lastname = ‘Dull’ — Or EXEC
au_info ‘Dull’, ‘Ann’ — Or EXEC au_info @lastname = ‘Dull’,
@firstname = ‘Ann’ — Or EXEC au_info @firstname = ‘Ann’, @lastname =
‘Dull’ 要是该进程是批管理中的第一条语句,则可利用: au_info ‘Dull’,
‘Ann’ — Or au_info @lastname = ‘Dull’, @firstname = ‘Ann’ — Or
au_info @firstname = ‘Ann’, @lastname = ‘Dull’ C.
使用含有通配符参数的简易进度上边包车型客车累积进度从多个表的对接中只回去钦点的审核人、出版的图书以致出版社。该存款和储蓄进度对传递的参数实行方式匹配,若无提供参数,则选择预设的暗许值。
USE pubs IF EXISTS (SELECT name FROM sysobjects WHERE name = ‘au_info2’
AND type = ‘P’) DROP PROCEDURE au_info2 GO USE pubs GO CREATE PROCEDURE
au_info2 @lastname varchar(30) = ‘D%’, @firstname varchar(18) = ‘%’ AS
SELECT au_lname, au_fname, title, pub_name FROM authors a INNER JOIN
titleauthor ta ON a.au_id = ta.au_id INNER JOIN titles t ON
t.title_id = ta.title_id INNER JOIN publishers p ON t.pub_id =
p.pub_id WHERE au_fname LIKE @firstname AND au_lname LIKE @lastname
GO au_info2 存款和储蓄进度可以用多种整合实行。下边只列出了一些构成: EXECUTE
au_info2 — Or EXECUTE au_info2 ‘Wh%’ — Or EXECUTE au_info2
@firstname = ‘A%’ — Or EXECUTE au_info2 ‘[CK]ars[OE]n’ — Or
EXECUTE au_info2 ‘Hunter’, ‘Sheryl’ — Or EXECUTE au_info2 ‘H%’, ‘S%’
D. 使用 OUTPUT 参数 OUTPUT 参数允许外界进程、批管理或多条 Transact-SQL
语句访谈在进程进行时期设置的某部值。上边包车型客车亲自过问创设二个仓库储存进度(titles_sum卡塔尔国,并利用一个可选的输入参数和二个出口参数。 首先,创造进度:
USE pubs GO IF EXISTS(SELECT name FROM sysobjects WHERE name =
‘titles_sum’ AND type = ‘P’) DROP PROCEDURE titles_sum GO USE pubs GO
CREATE PROCEDURE titles_sum @@TITLE varchar(40卡塔尔 = ‘%’, @@SUM money
OUTPUT AS SELECT ‘Title Name’ = title FROM titles WHERE title LIKE
@@TITLE SELECT @@SUM = SUM(price卡塔尔国 FROM titles WHERE title LIKE @@TITLE
GO 接下去,将该 OUTPUT 参数用于调节流语言 表明 OUTPUT
变量必需在成立表和平运动用该变量时都进展定义。
参数名和变量名不自然要合营,但是数据类型和参数地方必得合作。 DECLARE
@@TOTALCOST money EXECUTE titles_sum ‘The%’, @@TOTALCOST OUTPUT IF
@@TOTALCOST 200 BEGIN P冠道INT ‘ ‘ PXC90INT ‘All of these titles can be
purchased for less than $200.’ END ELSE SELECT ‘The total cost of these
titles is $’ + RTHighlanderIM(CAST(@@TOTALCOST AS varchar(20卡塔尔卡塔尔(قطر‎卡塔尔(قطر‎ 上边是结果集:

簡單SQL存儲過程實例

摘自:

实例1:只回去单一记录集的仓库储存进度。

银行储蓄表(bankMoney)的内容如下

Id

userID

Sex

Money

001

Zhangsan

30

002

Wangwu

50

003

Zhangsan

40

务求1:查询表bankMoney的内容的囤积进度

create procedure sp_query_bankMoney
as
select * from bankMoney
go
exec sp_query_bankMoney

注* 
在采用进度中只供给把中的SQL语句替换为存款和储蓄进程名,就足以了很有益于啊!

实例2(向存储进度中传递参数):

参与单笔记录到表bankMoney,并询问此表中userID=
Zhangsan的保有积贮的总金额。

Create proc insert_bank @param1 char(10),@param2 varchar(20),@param3
varchar(20),@param4 int,@param5 int output
with encryption ———加密
as
insert bankMoney (id,userID,sex,Money)
Values(@param1,@param2,@param3, @param4)
select @param5=sum(Money) from bankMoney where userID=’Zhangsan’
go
在SQL Server查询解析器中实施该存款和储蓄进度的措施是:
declare @total_price int
exec insert_bank ‘004’,’Zhangsan’,’男’,100,@total_price output
print ‘总余额为’+convert(varchar,@total_price)
go

在这里地再啰嗦一下存款和储蓄进度的3种传回值(方便正在看那些事例的朋友不要再去查看语法内容):

1.以Return传回整数

2.以output格式传开参数

3.Recordset

传回值的界别:

output和return都可在批次程式中用变量选择,而recordset则传回到实行批次的顾客端中。

实例3:使用含有复杂 SELECT 语句的简洁明了进度

  上面的仓库储存进程从多个表的连通中回到全体小编(提供了人名)、出版的书籍甚至书局。该存款和储蓄进度不行使其余参数。

USE pubs
IF EXISTS (SELECT name FROM sysobjects
         WHERE name = ‘au_info_澳门新葡萄京官网注册,all’ AND type = ‘P’)
   DROP PROCEDURE au_info_all
GO
CREATE PROCEDURE au_info_all
AS
SELECT au_lname, au_fname, title, pub_name
   FROM authors a INNER JOIN titleauthor ta
      ON a.au_id = ta.au_id INNER JOIN titles t
      ON t.title_id = ta.title_id INNER JOIN publishers p
      ON t.pub_id = p.pub_id
GO

  au_info_all 存款和储蓄进程能够经过以下措施实行:

EXECUTE au_info_all

  实例4:使用含有参数的简易进程

CREATE PROCEDURE au_info
   @lastname varchar(40),
   @firstname varchar(20)
AS
SELECT au_lname, au_fname, title, pub_name
   FROM authors a INNER JOIN titleauthor ta
      ON a.au_id = ta.au_id INNER JOIN titles t
      ON t.title_id = ta.title_id INNER JOIN publishers p
      ON t.pub_id = p.pub_id
   WHERE  au_fname = @firstname
      AND au_lname = @lastname
GO

  au_info 存款和储蓄进度能够通过以下方法执行:

EXECUTE au_info ‘Dull’, ‘Ann’
— Or
EXECUTE au_info @lastname = ‘Dull’, @firstname = ‘Ann’
— Or
EXECUTE au_info @firstname = ‘Ann’, @lastname = ‘Dull’

实例5:使用含有通配符参数的精简进度

CREATE PROCEDURE au_info2
@lastname varchar(30) = ‘D%’,
@firstname varchar(18) = ‘%’
AS
SELECT au_lname, au_fname, title, pub_name
FROM authors a INNER JOIN titleauthor ta
   ON a.au_id = ta.au_id INNER JOIN titles t
   ON t.title_id = ta.title_id INNER JOIN publishers p
   ON t.pub_id = p.pub_id
WHERE au_fname LIKE @firstname
   AND au_lname LIKE @lastname
GO

  au_info2 存款和储蓄进程能够用各个组成实施。上边只列出了一部分构成:

EXECUTE au_info2
— Or
EXECUTE au_info2 ‘Wh%’
— Or
EXECUTE au_info2 @firstname = ‘A%’
— Or
EXECUTE au_info2 ‘[CK]ars[OE]n’
— Or
EXECUTE au_info2 ‘Hunter’, ‘Sheryl’
— Or
EXECUTE au_info2 ‘H%’, ‘S%’

CREATE PROCEDURE
成立存储进度,存款和储蓄进度是保存起来的能够肩负和再次来到客商提供的参数的
Transact-SQL
语句的集合。能够创建三个历程供恒久使用,或在一个会话中有的时候选拔,或在全心得话中一时半刻使用。也足以创造在
Microsoft SQL Server启动时自动运营的寄存进程。

Title Name

The Busy Executive’s Database Guide The Gourmet Microwave The Psychology
of Computer Cooking (3 row(s卡塔尔 affected卡塔尔(قطر‎ Warning, null value eliminated
from aggregate. All of these titles can be purchased for less than $200.
E. 应用 OUTPUT 游标参数 OUTPUT
游标参数用来将积累进程的一些游标传递回调用批管理、存款和储蓄进度或触发器。
首先,创立以下进度,在 titles 表上宣称并展开叁个游标: USE pubs IF
EXISTS (SELECT name FROM sysobjects WHERE name = ‘titles_cursor’ and
type = ‘P’) DROP PROCEDURE titles_cursor GO CREATE PROCEDURE
titles_cursor @titles_cursor CURSOR VARYING OUTPUT AS SET
@titles_cursor = CURSOR FORWARD_ONLY STATIC FOR SELECT * FROM titles
OPEN @titles_cursor GO
接下去,实践三个批管理,声美赞臣(Meadjohnson卡塔尔(قطر‎个局地游标变量,推行上述进程以将游标赋值给一些变量,然后从该游标提取行。
USE pubs GO DECLARE @MyCursor CU摩根AeroSO酷威 EXEC titles_cursor @titles_cursor
= @MyCursor OUTPUT WHILE (@@FETCH_STATUS = 0State of Qatar BEGIN FETCH NEXT FROM
@MyCursor END CLOSE @MyCursor DEALLOCATE @MyCursor GO F. 使用 WITH
RECOMPILE 选项
假诺为经过提供的参数不是特出的参数,并且新的举行计划不应高速缓存或存款和储蓄在内部存款和储蓄器中,WITH
RECOMPILE 子句会很有援助。 USE pubs IF EXISTS (SELECT name FROM
sysobjects WHERE name = ‘titles_by_author’ AND type = ‘P’) DROP
PROCEDURE titles_by_author GO CREATE PROCEDURE titles_by_author
@@LNAME_PATTERN varchar(30) = ‘%’ WITH RECOMPILE AS SELECT
RTRIM(au_fname) + ‘ ‘ + RTRIM(au_lname) AS ‘Authors full name’, title
AS Title FROM authors a INNER JOIN titleauthor ta ON a.au_id =
ta.au_id INNER JOIN titles t ON ta.title_id = t.title_id WHERE
au_lname LIKE @@LNAME_PATTEXC60N GO G. 使用 WITH ENC哈弗YPTION 选项 WITH
ENC宝马X5YPTION 子句对客商隐蔽存款和储蓄进度的文本。下例创设加密进度,使用
sp_helptext 系统存款和储蓄进度获得有关加密经过的信息,然后尝试直接从
syscomments 表中获得有关该进程的消息。 IF EXISTS (SELECT name FROM
sysobjects WHERE name = ‘encrypt_this’ AND type = ‘P’) DROP PROCEDURE
encrypt_this GO USE pubs GO CREATE PROCEDURE encrypt_this WITH
ENCRYPTION AS SELECT * FROM authors GO EXEC sp_helptext encrypt_this
上边是结果集: The object’s comments have been encrypted.
接下去,接收加密存款和储蓄进度内容的标志号和文书。 SELECT c.id, c.text FROM
syscomments c INNE奥迪TT JOIN sysobjects o ON c.id = o.id WHERE o.name =
‘encrypt_this’

语法 CREATE PROC [ EDURE ] procedure_name [ ; number ] [ {
@parameter data_type } [ VARYING ] [ = default ] [ OUTPUT ] ] [
,…n ]

[ WITH { RECOMPILE | ENCRYPTION | RECOMPILE , ENCRYPTION } ]

[ FOR REPLICATION ]

AS sql_statement [ …n ]

参数 procedure_name

新存款和储蓄进度的名号。进度名必得切合标记符准绳,且对于数据库及其主人必需独一。有关越多音信,请参见使用标记符。
要创造局地有时进度,能够在 procedure_name 后面加叁个号码符
(#procedure_nameState of Qatar,要开创全局不时进度,能够在 procedure_name
前边加两个号码符 (##procedure_name卡塔尔(قطر‎。完整的名称不可能凌驾 1三十多少个字符。内定进程全数者的名目是可选的。

;number 是可选的平头,用来对同名的经过分组,以便用一条 DROP PROCEDURE
语句就能够将同组的历程一同除去。比方,名字为 orders
的应用程序使用的长河能够命名字为 orderproc;1、orderproc;2 等。DROP
PROCEDURE orderproc
语句将除了整个组。若是名称中满含定界标记符,则数字不应包蕴在标志符中,只应在
procedure_name 前后使用方便的定界符。

@parameter

进程中的参数。在 CREATE PROCEDURE
语句中能够声Bellamy(Bellamy卡塔尔个或四个参数。客户必需在实行进度时提供每一种所注解参数的值。存款和储蓄进程最多能够有
2.100 个参数。

利用 @
符号作为第二个字符来内定参数名称。参数名称必得适合标志符的不成方圆。种种进度的参数仅用于该进度自身;形似的参数名称能够用在任何进度中。暗中认可情状下,参数只好代替常量,而不能够用于替代表名、列名或此外数据库对象的名目。有关更加多音讯,请参见
EXECUTE。

data_type

参数的数据类型。全部数据类型均可以当作存储进度的参数。可是,cursor
数据类型只好用来 OUTPUT 参数。假如钦点的数据类型为
cursor,也非得同有的时候候钦定 VA福睿斯YING 和 OUTPUT 关键字。有关 SQL Server
提供的数据类型及其语法的越来越多音讯,请参见数据类型。

表明 对于能够是 cursor 数据类型的出口参数,未有最大数指标限制。

VARYING

点名作为出口参数帮忙的结果集。仅适用于游标参数。

default

参数的私下认可值。要是定义了暗许值,不必钦点该参数的值就可以实行进程。私下认可值必需是常量或
NULL。假如经过将对该参数使用 LIKE 关键字,那么暗许值中得以包涵通配符。

OUTPUT

标注参数是回来参数。该选取的值可以回去给 EXEC[UTE]。使用 OUTPUT
参数可将新闻再次回到给调用进度。Text、ntext 和 image 参数可用作 OUTPUT
参数。使用 OUTPUT 关键字的输出参数能够是游标占位符。

n

意味着最多能够钦点 2.100 个参数的占位符。

{RECOMPILE | ENCRYPTION | RECOMPILE, ENCRYPTION}

RECOMPILE 表明 SQL Server
不会缓存该进程的布署,该进度将要运作时再也编写翻译。在动用非规范值或有时值而不愿意覆盖缓存在内部存款和储蓄器中的推行安排时,请使用
RECOMPILE 选项。

ENC奥迪Q5YPTION 代表 SQL Server 加密 syscomments 表中饱含 CREATE PROCEDURE
语句文本的条款。使用 ENC中华VYPTION 可防范将经过作为 SQL Server
复制的一有的发表。

注明 在进步进程中,SQL Server 利用存款和储蓄在 syscomments
中的加密注释来重新创制加密经过。

FOR REPLICATION

点名无法在订阅服务器上实行为复制创立的仓库储存进程。.使用 FOHaval REPLICATION
选项成立的存款和储蓄过程可用作存款和储蓄进度筛选,且一定要在复制进程中举行。本选项无法和
WITH RECOMPILE 选项一同利用。

AS

内定进程要执行的操作。

sql_statement

进程中要含有的大肆数目和类别的 Transact-SQL 语句。但有点限制。

n

是象征此进度能够富含多条 Transact-SQL 语句的占位符。

表明 存款和储蓄进程的最大尺寸为 128 MB。

客商定义的存款和储蓄进程只能在这段日子数据库中开创。在单个批管理中,CREATE
PROCEDURE 语句无法与别的 Transact-SQL 语句组合使用。

默认情状下,参数可为空。如果传递 NULL 参数值并且该参数在 CREATE 或 ALTER
TABLE 语句中选用,而该语句中援用的列又不许使用 NULL,则 SQL Server
会发生一条错误新闻。为了防止向分化意行使 NULL 的列传递 NULL
参数值,应向进程中增多编制程序逻辑或为该列使用暗中同意值。如果有些连接实践的存放进度对那几个接纳的设置与创建该进度的连天的安装差异,则为第叁个三回九转成立的表列也许会有例外的为空性,並且呈现出不一样的表现情势。借使为每一个列显式注脚了
NULL 或 NOT
NULL,那么将对拥有推行该存款和储蓄进程的连接使用同一的为空性创造有时表。

在创设或改造存款和储蓄进度时,SQL Server 将保存 SET QUOTED_IDENTIFIER 和 SET
ANSI_NULLS
的安装。试行存款和储蓄进度时,将动用这一个原来设置。因而,全体顾客端会话的 SET
QUOTED_IDENTIFIER 和 SET ANSI_NULLS
设置在推行存款和储蓄进度时都将被忽视。在积累进度中现身的 SET
QUOTED_IDENTIFIER 和 SET ANSI_NULLS 语句不影响存款和储蓄进度的功能。

其余 SET
选项在开创或更改存款和储蓄进程时不保留。要是存款和储蓄进程的逻辑决议于特定的设置,应在经过起头增添一条
SET 语句,以管教设置科学。从存款和储蓄进度中实践 SET
语句时,该装置只在积存进程做到早前有效。之后,设置将还原为调用存款和储蓄进度时的值。那使个其余客商端可以设置所需的选项,而不会影响存款和储蓄进度的逻辑。

注明 SQL Server
是将空字符串解释为单个空格仍旧分解为确实的空字符串,由非常等第设置调整。假设合作品级小于或等于
65,SQL Server 就将空字符串解释为单个空格。要是协作级别等于 70,则 SQL
Server 将空字符串解释为空字符串。有关越多消息,请参见 sp_dbcmptlevel。

取得有关仓库储存进程的消息若要彰显用来创建进度的文件,请在进程所在的数据库中施行sp_helptext,并接受进度名作为参数。

表明 使用 ENC奥迪Q7YPTION 选项创立的积攒进程无法利用 sp_helptext 查看。

若要呈现有关进度引用的对象的报表,请使用 sp_depends。

若要为经过重命名,请使用 sp_rename。

援用对象 SQL Server
允许创制的积累进度援用尚一纸空文的对象。在成立时,只举行语法检查。施行时,借使高速缓存中尚无有效的安排,则编译存款和储蓄进程以生成奉行安排。唯有在编译进度中才分析存款和储蓄进程中援用的兼具指标。因而,如果语法准确的积累进程引用了荒诞不经的目的,则还是能够成功成立,但在运作时将失利,因为所引述的对象不设有。有关更加的多音讯,请参见延迟名称深入分析和编译。

推迟名称分析和相称品级 SQL Server 允许 Transact-SQL
存款和储蓄进度在创设时引用荒诞不经的表。这种本领称为延迟名称解析。不过,即使Transact-SQL 存款和储蓄进度引用了该存款和储蓄进度中定义的表,而协作等级设置为
65,则在开创时会发出警报新闻。而借使在运行时所引述的表不设有,将回到错误音信。有关越来越多消息,请参见
sp_dbcmptlevel 和推迟名称深入深入分析和编写翻译。

施行存款和储蓄进度 成功执行 CREATE PROCEDURE 语句后,进程名称将储存在
sysobjects 系统表中,而 CREATE PROCEDURE 语句的公文将积攒在 syscomments
中。第叁次实行时,将编写翻译该进度以显明检索数据的精品访谈安顿。

利用 cursor 数据类型的参数 存款和储蓄进程只好将 cursor 数据类型用于 OUTPUT
参数。如若为有些参数指定了 cursor 数据类型,也必需钦点 VAKoleosYING 和 OUTPUT
参数。要是为某些参数钦点了 VAMuranoYING 关键字,则数据类型必须是
cursor,而且必得钦赐 OUTPUT 关键字。

证实 cursor 数据类型不可能经过数据库
API绑定到应用程序变量上。因为必得先绑定 OUTPUT
参数,应用程序才方可实行存款和储蓄进度,所以满含 cursor OUTPUT
参数的存款和储蓄进度不能够经过数据库 API 调用。独有将 cursor OUTPUT 变量赋值给
Transact-SQL 局地 cursor 变量时,才足以由此 Transact-SQL
批处理、存款和储蓄进度或触发器调用那个进程。

Cursor 输出参数 在进行进程时,以下法则适用于 cursor 输出参数:

对此只进游标,游标的结果集中再次来到的行只是那个存款和储蓄进度实践达成时处于或高于游标地点的行,举例:
在经过中的名字为 奇骏S 的 100 行结果集上开采二个非滚动游标。

进程提取结果集 TiggoS 的头 5 行。

进程重返到其调用者。

回到到调用者的结果集 中华VS 由 ENCORES 的第 6 到 100 行组成,调用者中的游标处于
揽胜极光S 的率先行此前。
对于只进游标,要是存款和储蓄进程落成后,游标坐落于第一行的前头,则整个结果集将赶回给调用批管理、存款和储蓄进程或触发器。重回时,游标将坐落于第一行的眼下。

对于只进游标,若是存款和储蓄进程实现后,游标的岗位超过最终一行的结尾,则为调用批管理、存款和储蓄进程或触发器重回空结果集。

表明 空结果集与空值分歧。

对于可滚动游标,在蕴藏进程实践完成时,结果集中的有所行均会再次来到给调用批处理、存储进度或触发器。重回时,游标保留在进度中最终一遍实践提取时的任务。

对此随便等级次序的游标,假若游标关闭,则将空值传递回调用批管理、存款和储蓄进程或触发器。假如将游标支使给一个参数,但该游标从未展开过,也会合世这种情状。

注解关闭状态唯有在回来时才有震慑。例如,能够在进度中关闭游标,稍后再展开游标,然后将该游标的结果集重临给调用批管理、存储进程或触发器。

一时存款和储蓄进程 SQL Server
扶植二种最近进程:局地一时进度和全局临时进程。局地有的时候进度只好由成立该进程的三番两次使用。全局有的时候进程则可由具有连接使用。局地不时进程在当下对话甘休时自动除去。全局不常进程在行使该进度的末了贰个对话甘休时除了。日常是在创立该进度的对话截止时。

临时进度用 # 和 ##
命名,能够由别的客商成立。创立进度后,局地进程的持有者是独一能够利用该进程的用户。执香港行政局部一时进度的权柄无法授予其余顾客。要是成立了全局有时进度,则有着顾客均能够访问该过程,权限不能够显式裁撤。唯有在
tempdb 数据库中具有显式 CREATE PROCEDURE
权限的客商,技术够在该数据库中显式创造一时进度。能够授予或丢掉那些经过中的权限。

注解 频仍利用临时存款和储蓄进程会在 tempdb
中的系统表上发出争用,进而对品质发生消极的一面影响。建议利用 sp_executesql
代替。sp_executesql 不在系统表中寄存数据,因而得以免止这一难题。

自动施行存款和储蓄进程 SQL Server
运转时能够活动实施叁个或多少个存款和储蓄进度。那些囤积进程必需由系统管理员创立,并在
sysadmin 固定服务器剧中人物下作为后台进度实行。那么些进程无法有其它输入参数。

对运维进度的多少未有限制,不过要潜心,种种运营进度在举办时都会占领三个老是。假若非得在运行时实施八个经过,但无需并行推行,则能够钦定二个进度作为运维进程,让该进程调用此外过程。那样就只占用二个一而再。

在运行时恢复生机了最终贰个数据库后,即起来施行存款和储蓄进程。若要跳过这一个囤积进度的实施,请将起动参数钦定为追踪标记4022。假使以最低配置运营 SQL
Server,则运转存款和储蓄进度也不会实践。有关更加多音信,请参见追踪标志。

若要创设运行存款和储蓄进程,必需作为 sysadmin 固定服务器剧中人物的成员登入,并在
master 数据库中开创存款和储蓄进度。

使用 sp_procoption 可以:

将现存存款和储蓄进程内定为运转进度。

停下在 SQL Server 运营时实行进度。

翻看 SQL Server 运转时实施的具备进度的列表。 存款和储蓄进程嵌套
存款和储蓄进程能够嵌套,即一个囤积进程能够调用另三个仓储进程。在被调用进度开头实践时,嵌套级将加码,在被调用进程进行达成后,嵌套级将回退。倘使当先最大的嵌套级,会使全部调用进程链退步。可用
@@NESTLEVEL 函数重临当前的嵌套级。

若要臆度编写翻译后的囤积进度大小,请使用下列品质监视流速計。

品质监视器对象名 品质监视流速計名称 SQLServer:缓冲区微机 高速缓存大小SQLServer:高速缓存管理器 高速缓存命中率 高速缓存页
高速缓存对象计数*

* 种种分类的高速缓存对象均可以应用那几个计数器,包罗特别 sql、打算sql、进度、触发器等。

关于越来越多音信,请参见 SQL Server:Buffer Manager 对象和 SQL Server:Cache
Manager 对象。

sql_statement 限制 除了 SET SHOWPLAN_TEXT 和 SET SHOWPLAN_ALL
之外,任何 SET 语句均能够在蕴藏进度里面钦点。所选用的 SET
选项在仓库储存进程实行进度中有效,之后苏醒为本来的安装。

设若其余客商要采用有些存储进度,那么在该存款和储蓄进程里面,一些言语使用的目的名必需选拔对象全数者的名目约束。那几个语句富含:

ALTER TABLE

CREATE INDEX

CREATE TABLE

所有 DBCC 语句

DROP TABLE

DROP INDEX

TRUNCATE TABLE

UPDATE STATISTICS 权限 CREATE PROCEDURE 的权杖默许授予 sysadmin
固定服务器角色成员和 db_owner 和 db_ddladmin
固定数据库角色成员。sysadmin 固定服务器剧中人物成员和 db_owner
固定数据库角色成员能够将 CREATE PROCEDURE
权限让渡给别的客商。实践存储进程的权位赋予进度的主人,该全数者可以为别的数据库客商安装实行权限。

示例 A. 使用含有复杂 SELECT 语句的精短进程下边包车型大巴寄放进度从八个表的连接中回到全数作者、出版的图书以至书局。该存款和储蓄进程不使用别的参数。

USE pubs IF EXISTS (SELECT name FROM sysobjects WHERE name =
au_info_all AND type = P) DROP PROCEDURE au_info_all GO CREATE
PROCEDURE au_info_all AS SELECT au_lname, au_fname, title, pub_name
FROM authors a INNER JOIN titleauthor ta ON a.au_id = ta.au_id INNER
JOIN titles t ON t.title_id = ta.title_id INNER JOIN publishers p ON
t.pub_id = p.pub_id GO

au_info_all 存款和储蓄进度能够通过以下办法实施:

EXECUTE au_info_all — Or EXEC au_info_all

假如该过程是批管理中的第一条语句,则可使用:

au_info_all

B. 使用带有参数的粗略进程下边包车型大巴储存进程从多少个表的连结中只回去钦赐的小编、出版的书籍以致书局。该存款和储蓄进程接纳与传递的参数正确相称的值。

USE pubs IF EXISTS (SELECT name FROM sysobjects WHERE name = au_info
AND type = P) DROP PROCEDURE au_info GO USE pubs GO CREATE PROCEDURE
au_info @lastname varchar(40), @firstname varchar(20) AS SELECT
au_lname, au_fname, title, pub_name FROM authors a INNER JOIN
titleauthor ta ON a.au_id = ta.au_id INNER JOIN titles t ON
t.title_id = ta.title_id INNER JOIN publishers p ON t.pub_id =
p.pub_id WHERE au_fname = @firstname AND au_lname = @lastname GO

au_info 存款和储蓄进程能够通过以下方法奉行:

EXECUTE au_info Dull, Ann — Or EXECUTE au_info @lastname = Dull,
@firstname = Ann — Or EXECUTE au_info @firstname = Ann, @lastname =
Dull — Or EXEC au_info Dull, Ann — Or EXEC au_info @lastname = Dull,
@firstname = Ann — Or EXEC au_info @firstname = Ann, @lastname = Dull

假设该进度是批管理中的第一条语句,则可应用:

au_info Dull, Ann — Or au_info @lastname = Dull, @firstname = Ann —
Or au_info @firstname = Ann, @lastname = Dull

C. 使用带有通配符参数的简约过程下边包车型地铁仓库储存过程从多少个表的衔接中只回去钦定的审核人、出版的书本以至出版社。该存款和储蓄进度对传递的参数进行格局相配,若无提供参数,则应用预设的默许值。

USE pubs IF EXISTS (SELECT name FROM sysobjects WHERE name = au_info2
AND type = P) DROP PROCEDURE au_info2 GO USE pubs GO CREATE PROCEDURE
au_info2 @lastname varchar(30) = D*, @firstname varchar(18) = * AS
SELECT au_lname, au_fname, title, pub_name FROM authors a INNER JOIN
titleauthor ta ON a.au_id = ta.au_id INNER JOIN titles t ON
t.title_id = ta.title_id INNER JOIN publishers p ON t.pub_id =
p.pub_id WHERE au_fname LIKE @firstname AND au_lname LIKE @lastname
GO

au_info2 存款和储蓄进程可以用两种重新整合实行。上面只列出了某些组成:

EXECUTE au_info2 — Or EXECUTE au_info2 Wh* — Or EXECUTE au_info2
@firstname = A* — Or EXECUTE au_info2 [CK]ars[OE]n — Or EXECUTE
au_info2 Hunter, Sheryl — Or EXECUTE au_info2 H*, S*

D. 使用 OUTPUT 参数 OUTPUT 参数允许外界进程、批处理或多条 Transact-SQL
语句访谈在进度实行时期设置的某部值。下边包车型客车躬行实施创设叁个仓库储存进度(titles_sum卡塔尔,并动用叁个可选的输入参数和贰个出口参数。 12全文阅读

发表评论

电子邮件地址不会被公开。 必填项已用*标注