存储过程,2008从入门到精通

作者:新京葡娱乐场388官网

1.锁

当多少个用户同临时候对同贰个多少开始展览改变时会发生并发难题,使用专门的学问就能够化解这一个主题素材。可是为了堤防其余用户修改另三个还没变成的政工中的数据,就需求在业务中用到锁。
SQL Server 2009提供了七种锁方式:排他锁,分享锁,更新锁,意向锁,键范围锁,架构锁和大体积更新锁。
查询sys.dm_tran_locks视图能够高速精晓SQL Server 二〇〇九内的加锁景况。

SELECT * FROM sys.dm_tran_locks;

注:关于锁的知识书中没细讲,将要后头的博客中补充。

目录

积攒进程优点

运维T-SQL语句举行编制程序有二种格局,一种是把T-SQL语句全体写在应用程序中,并积攒在本地;另一种是把有些T-SQL语句编写的次第当做存款和储蓄进程存款和储蓄在SQL Server中,只有本地的应用程序调用存款和储蓄进程。大很多技术员偏向利用前面一个,原因在于存款和储蓄进程具备以下优点:

  • 贰次编写翻译,数10次实践。第贰次执行有个别进程时,将编写翻译该进度以明确检索数据的最优访谈陈设。 如若已经更动的布置仍保存在数据库引擎陈设缓存中,则该进度随之施行的操作大概再次使用该铺排。
  • 可在应用程序中一再调用;修改存款和储蓄进度不会影响使用程序源代码。
  • 积存进程存款和储蓄在服务中,能够减少网络流量。比方贰个亟需数百行T-SQL代码的操作能够透过一条实行存款和储蓄进程代码的语句来调用,而无需在网络中发送数百行代码。
  • 积累进度可被作为一种安全体制来丰硕利用。能够只授予用户实施存款和储蓄进程的权力,而不给予用户直接访谈存款和储蓄进度中关系的表的权柄。那样,用户只可以通过存款和储蓄进度来访问表,并拓展个别的操作,进而确认保障了表中多少的平安。选取授权操作设置种种用户的权能

2.游标

游标是看似于C语言指针一样的结构,是一种多少访谈机制,允许用户访谈单独的数据行。游标首要由游标结果集和游标地方组成。游标结果集是概念游标的SELECT语句重返行的汇聚,游标地方是指向这几个结果聚焦某一行的指针。
示例1:用游标检索出student表中每行记录
Student表记录如图所示
新京葡娱乐场388官网 1
实践下列语句

USE test
DECLARE stu_cursor CURSOR FOR
SELECT * FROM student--声明student表的游标stu
OPEN stu_cursor--打开游标
FETCH NEXT FROM stu_cursor--移动该记录指针
WHILE @@FETCH_STATUS=0--@@FETCH_STATUS用于保存FETCH操作的结束信息,=0表示有记录检索成功
BEGIN
FETCH NEXT FROM stu_cursor--游标指针移动到下一条记录
END
CLOSE stu_cursor--关闭游标
DEALLOCATE stu_cursor--释放游标资源

结果如图所示
新京葡娱乐场388官网 2

  • 1.选用Transact-SQL语言编制程序
    • 1.1.数量定义语言DDL
    • 1.2.数码垄断(monopoly)语言DML
    • 1.3.数额调控语言DCL
    • 1.4.Transact-SQL言语基础
  • 2.运算符
    • 2.1.算数运算符
    • 2.2.赋值运算符
    • 2.3.位运算符
    • 2.4.比较运算符
    • 2.5.逻辑运算符
    • 2.6.连接运算符
    • 2.7.一元运算符
    • 2.8.运算符的预先级
  • 3.操纵语句
    • 3.1.BEGIN END语句块
    • 3.2.IF ELSE语句块
    • 3.3.CASE分支语句
    • 3.4.WHILE语句
    • 3.5.WAITFO凯雷德延缓语句
    • 3.6.RETU汉兰达N无条件退出语句
    • 3.7.GOTO跳转语句
    • 3.8.TEnclaveY CATCH错误处理语句
  • 4.常用函数
    • 4.1.数据类型调换函数

存款和储蓄进度分类

(1)系统存储进度
  SQL Server提供的蕴藏进程,用于试行与系统有关的职分,首要囤积在master数据库并以sp_为前缀,例如sp_addtype、sp_rename等。

新京葡娱乐场388官网 3

(2)扩大存款和储蓄进程
  扩张存款和储蓄进度是以在SQL Server遭受之外施行的动态链接库(Dymatic-Link)Libraries,DDL)来促成的,奉行系统存储进程无法胜任的任务,如发邮件、文件管理等,日常以前缀xp_开头。实施扩充存款和储蓄进度的点子与仓库储存进度的一般。

(3)有的时候存款和储蓄进度
  一时存款和储蓄进程首先是本地存款和储蓄进度。SQL Server支持两种临时存款和储蓄进程:局地有的时候进度和全局不经常进程。
  假诺存款和储蓄进度的先头有一个符号“#”,那么它正是某个不常进程,只可以在三个用户会话中动用,在此时此刻对话停止时就能被除去。
  假若存款和储蓄进度的前边有四个暗号“##”,那么把该存款和储蓄进程称为全局有的时候存款和储蓄进度,能够在具备用户会话中选用,在选拔该进程的末尾三个会话结束时除了。

(4)用户定义的积累进程
  用户自定义的囤积进程由用户成立的一组T-SQL语句集结组成,能够收到和重返用户提供的参数,完毕有个别特定成效。
  存款和储蓄进程创造好且语法准确后,系统将储存进程的名号存款和储蓄在此时此刻数据库的系统表sysobject中;将积存进度的文书存款和储蓄在前段时间数据库的系统表syscomments中。

2.1.游标定义的参数LOCAL和GLOBAL

游标定义参数LOCAL表示该游标只好效用于此番批管理或函数或存款和储蓄进程。游标定义参数GLOBAL表示该游标能够效用于大局。
推行下列语句

DECLARE stu_cursor CURSOR LOCAL
FOR SELECT * FROM student
GO
OPEN stu_cursor
GO

实践结果如下
新京葡娱乐场388官网 4
语句中,注解了四个student表的游标stu_cursor,在开拓游标时提醒游标不设有。因为该游标参数是LOCAL,只可以成效于这段日子批管理语句中,而打开游标语句和注明语句不在一个批管理中。纵然去掉第二个GO,使多少个语句在同一个批管理中,就能够胜利试行不会报错。
举行下列语句

DECLARE stu_cursor1 CURSOR GLOBAL
FOR SELECT * FROM student
GO
OPEN stu_cursor1
GO

试行结果:命令已成功达成
和LOCAL参数比较,GOLBAL参数设置游标功用于大局,因而OPEN和DECLARE语句不在同贰个批管理中如故得以成功执行。

1.用到Transact-SQL语言编制程序

就算SQL Server 二〇〇九提供了图形化分界面,但独有一种Transact-SQL语言能够一向与数据库引擎实行交互。依据实施遵循特色能够将Transact-SQL语言分成3大类:数据定义语言DDL,数据垄断(monopoly)语言DML,数据调控语言DCL。

创造存储进度

积存过程语法如下:

CREATE PROCEDURE|PROC [schema_name.] procedure_name
    -- Add the parameters for the stored procedure here
    [ { @parameter [ type_schema_name. ] data_type }  
        [ VARYING ] [ = default ] [ OUT | OUTPUT | [READONLY]  
    ] [ ,...n ]   
    [WITH <procedure_option> [ ,...n ]]
    [FOR REPLICATION]
AS { [ BEGIN ] sql_statement [;] [ ...n ] [ END ] }

<procedure_option> ::=   
    [ ENCRYPTION ]  
    [ RECOMPILE ]  
    [ EXECUTE AS Clause ]  
  • schema_name:该进度所属的架构的名称。假使在成立进程时未钦赐架构名称,则自动分配正在创建进度的用户的暗中同意框架结构。
  • 能够透过动用三个#符号在procedure_name此前制造本地一时进程(#procedure_name)或两个#标识创建全局有的时候进度(## procedure_name) 。局地有的时候程序仅对创制了它的连天可知,并且在关门该连接后将被删去。 全局有的时候程序可用以全数连接,並且在行使该进度的最终一个对话停止时将被去除。
  • @parameter:钦命进程中的参数,是有些的,能够声惠氏个或四个。
  • 万一内定了FOWrangler REPLICATION,则无从表明参数。
  • parameter能够是输入参数or输出参数,若为输入参数IN能够不写,系统默许;若为输出参数则要增添OUTPUT。
  • 表值参数只好是 INPUT 参数,而且这么些参数必须含有 READONLY 关键字。
  • 光标数据类型只能是出口参数和必须附带由 VA宝马7系YING 关键字。
  • OUT | OUTPUT提醒参数是出口参数,使用 OUTPUT 参数将值重回给进度的调用方。
  • [ =default ]:参数的暗许值。 假使默肯定义值,该函数能够进行而不须要点名该参数的值。
  • WITH ENC奇骏YPTION:SQL Server加密syscomments表中包罗CREATE PROCEDURE语句文本的条条框框,即对用户隐敝存款和储蓄进度的文本,无法从syscomments表中获得该存款和储蓄进程的消息。
  • WITH RECOMPILE:提醒数据库引擎不缓存该过程的安排,该进度就要每一次运转时再也编写翻译。如若内定了FOLacrosseREPLICATION,则不能够利用此选项。
  • EXECUTE AS子句:钦定在里面实践进度的平安上下文。

关于参数

  • 仓库储存进程参数也足以涵盖默许值,如:
create procedure pun_info @pubname varchar(20)='ALGOdata'
  • 积存过程参数能够涵盖通配符,如:
create procedure pun_info 
   @name varchar(20)='D%'
as
  select name from authors where name like @name

有关出口
①OUTPUT参数
  假如在经过定义中为参数钦命 OUTPUT 关键字,则存款和储蓄进度在脱离时可将该参数的前段时间值重临至调用程序。若要用变量保存参数值以便在调用程序中动用,则调用程序必须在实施存款和储蓄进度时选取OUTPUT 关键字。
  也得以在施行进程时为 OUTPUT 参数钦定输入值。 那将同意进度从调用程序接收值,使用该值改换或实行操作,然后将新值再次来到给调用程序。
②施用再次来到代码再次来到数据
  进度能够回去一个整数值(称为“重回代码”),以提醒进度的推市价况。 使用 RETU大切诺基N 语句内定进度的回到代码。 与 OUTPUT 参数同样,推行进度时务必将赶回代码保存到变量中,本事在调用程序中采用重返代码值。
  RETUSportageN是从查询或进程中无条件退出,不推行位于 RETU卡宴N 之后的言辞。RETULX570N再次回到的无法是空值,假诺经过试图再次回到空值,将生成警告新闻并回到 0 值。用输出参数OUTPUT能够出口肆意档期的顺序的结果(不满含表类型),而RETU索罗德N只可以回到整型况且总能再次回到三个整型值。一般的RETU奥迪Q3N用来回到重临代码(如0表示施行成功,1代表未钦点所需参数值)。
  RETUPAJERON和OUTPUT还足以出现在一样存款和储蓄进程中,详见示例(3)。

2.2.游标分为游标变量和游标类型

一般来讲列语句

--语句1
DECLARE stu_cursor CURSOR
FOR SELECT * FROM student--声明student表的游标名称为stu_cursor并赋值
GO

--语句2
DECLARE @stu_cursor CURSOR--声明游标类型的变量@stu_cursor
SET @stu_cursor=CURSOR FOR--给该变量赋值
SELECT * FROM student

在语句第11中学央直属机关接注解了一个游标并赋值,而语句第22中学注解了游标类型的变量@stu_cursor,然后给该变量赋值。这两侧是不一致的。

1.1.数据定义语言DDL

是最基础的Transact-SQL语言类型,用来创制数据库和开创,修改,删除数据库中的种种对象,为其余语言的操作提供对象。举例数据库,表,触发器,存款和储蓄进度,视图,函数,索引,类型及用户等都以数据库中的对象。常见的DDL语句富含

CREATE TABLE--创建表
DROP TABLE--删除表
ALTER TABLE--修改表

受制与范围

①在单个批处理中,CREATE PROCEDURE 语句不能够与别的 Transact-SQL 语句组合使用。
②以下语句不可能用于存储进度主体中的任何地点。

新京葡娱乐场388官网 5

③历程能够援引尚子虚乌有的表。 在创设刻,只进行语法检查。 直到第二回实践该进度时才对其开始展览编写翻译。 独有在编写翻译进度中才深入分析进度中援用的享有目的。 由此,若是语法准确的过程引用了不真实的表,则仍可以够成功创办;但倘若被引述的表不设有,则经过就要实践时将退步。
④不能够将某一函数名称钦点为参数默许值可能在实践进程时传递给参数的值。 然则,您能够将函数作为变量传递,如以下示例中所示:

-- Passing the function value as a variable.  
DECLARE @CheckDate datetime = GETDATE();  
EXEC dbo.uspGetWhereUsedProductID 819, @CheckDate;   

⑤如若该进度对 SQL Server 的远程实例举办改换,将不恐怕回滚这一个改换。 远程进程不出席事业。

2.3.游标参数FO福特ExplorerWALX570D_ONLY和SCROLL

FORWARD_ONLY参数设置游标只好从结果集的开头向甘休方向读取,使用FETCH语句时不得不用NEXT,而SCROLL参数设置游标可以从结果集的随便方向,狂妄地方移动。如下列语句

--语句1,默认FORWARD_ONLY
DECLARE stu_cursor CURSOR LOCAL
FOR SELECT * FROM student
OPEN stu_cursor
FETCH NEXT FROM stu_cursor
GO
--语句2,FORWARD_ONLY参数,FETCH时只能从开始往结束方向
DECLARE stu_cursor CURSOR FORWARD_ONLY LOCAL
FOR SELECT * FROM student
OPEN stu_cursor 
FETCH NEXT FROM stu_cursor
GO
--语句3,SCROLL参数,FETCH时可以从任意位置往任意方向
DECLARE stu_cursor CURSOR SCROLL LOCAL
FOR SELECT * FROM student
OPEN stu_cursor
FETCH LAST FROM stu_cursor
GO

1.2.数额垄断语言DML

是用于操纵表和视图中的数据的言语,譬喻查询数据(SELECT),插入数据(INSERT),更新数据(UPDATE)和删除数据(DELETE)等。

执行存储进程

调用存储进度使用Execute|Exec关键字,不可能大致。

Execute|Exec
{
  [@整形变量=]
  存储过程名[,n]|@存储过程变量名
  [[@过程参数=]参数值|@可变参数名 [OUTPUT]|[DEFAULT]]
  [,..,n]
  [WITH RECOMPILE]
}
  • @整形变量:可选,代表存款和储蓄进程的回到状态。
  • n:可选,用于对同名的历程分组。
  • @进程参数:为存储进程的参数赋值。

SQL Server提供了三种传递参数的艺术:
(1)按职务传递参数,即传送的参数和定义时的参数顺序一致,如:
execute au_info 'Dull','Ann'
(2)通过参数名传递,选用“参数=值”的方式,此时逐一参数能够随心所欲排序,如:
execute au_info @firstName='Dull',@lastName='Ann' 或
execute au_info @lastName='Ann',@firstName='Dull'

  • OUTPUT:钦定该参数为出口参数。
  • DEFAULT:指明该参数使用暗中同意值。若是该参数定义时不曾点名暗中同意值,则不可能选择DEFAULT选项。
  • WITH RECOMPILE:强制在实行存款和储蓄进度时再度对其开始展览编写翻译。

【示例】
(1)带OUTPUT参数的囤积进度——末了的重返值存款和储蓄在调用程序表明的OUTPUT变量中

create procedure Query_Relationer
   @QueryCID int,                   -- 输入的形参
   @QueryRName varchar(20) OUTPUT   -- 输出的形参
as
begin
  if exists(select rid from Customer where cid = @QueryCID)
    select @QueryRName = RName from Relationer
    where rid = (select rid from Customer where cid = @QueryCID and cStatus = 1)
  else
    set @QueryRName = '不存在'
end
go

调用进程如下:

declare @Relationer_name varchar(20),@Cust_ID int
execute Query_Relationer @Cust_ID=20103530,@Relationer_name OUTPUT
print '客户ID为' convert(char(8),@Cust_ID) '的联系人是:' @Relationer_name

(2)带Return参数的储存进程

create proc up_user
as
delcare @age int
begin
  select @age=uage from user
  return @age
end

(3)同不常候带Return和output参数的仓库储存进程

create proc up_user
@id int,
@name varchar(20) output
as
 declare @age int
 begin
  select @age=stuage,@name=stuname from stuinfo where uid=@id
  return @age
 end

调用进度如下:

declare @age int
declare @name varchar(20)
exec @age=up_user 2,@name output
-- 输出age和name
select @age,@name

2.4.游标的简易利用

示例2:将student表中stu_enter_score大于600分的学习者都减去100分
Student表中的数据如图所示
新京葡娱乐场388官网 6
进行下列语句

--游标的简单应用
DECLARE stu_cursor CURSOR FORWARD_ONLY LOCAL
FOR SELECT stu_no,stu_enter_score FROM student
OPEN stu_cursor
DECLARE @score INT
DECLARE @stu_no VARCHAR(8)
FETCH NEXT FROM stu_cursor INTO @stu_no,@score--把游标值赋值给变量@score
WHILE @@FETCH_STATUS=0 
BEGIN
IF @score>=600
BEGIN
UPDATE student
SET stu_enter_score=@score-100 WHERE stu_no=@stu_no 
END
FETCH NEXT FROM stu_cursor INTO @stu_no,@score--把游标值赋值给变量@score
END
CLOSE stu_cursor
DEALLOCATE stu_cursor
GO

结果如图所示
新京葡娱乐场388官网 7

1.3.数量调节语言DCL

关系到权力处理的言语称为数据调节语言,首要用于试行有关安全管理的操作。如授予权限(GRANT),收回权限(REVOKE),拒绝授予主体权限,并防范主体通过组或角色成员持续权限(DENY

仓库储存进度传递集合参数以及再次回到、接收结果集

(1)传递集结参数

A、传递多少个形参

B、使用表值参数
  使用表值参数类型将几个行插入表中。 一下演示将开创参数类型,注明表变量来引用它,填充参数列表,然后将值传递给存款和储蓄进程。 存款和储蓄进度使用那几个值将多少个行插入表中。

/* Create a table type. */  
CREATE TYPE LocationTableType AS TABLE   
( LocationName VARCHAR(50)  
, CostRate INT );  
GO  

/* Create a procedure to receive data for the table-valued parameter. */  
CREATE PROCEDURE usp_InsertProductionLocation  
    @TVP LocationTableType READONLY  
    AS   
    SET NOCOUNT ON  
    INSERT INTO [AdventureWorks2012].[Production].[Location]  
           ([Name]  
           ,[CostRate]  
           ,[Availability]  
           ,[ModifiedDate])  
        SELECT *, 0, GETDATE()  
        FROM  @TVP;  
GO  

/* Declare a variable that references the type. */  
DECLARE @LocationTVP   
AS LocationTableType;  

/* Add data to the table variable. */  
INSERT INTO @LocationTVP (LocationName, CostRate)  
    SELECT [Name], 0.00  
    FROM   
    [AdventureWorks2012].[Person].[StateProvince];  

/* Pass the table variable data to a stored procedure. */  
EXEC usp_InsertProductionLocation @LocationTVP;  
GO  

(2)重返结果集

A、使用 OUTPUT 游标参数
  以下示例使用 OUTPUT 游标参数将经过的片段游标传递回试行调用的批管理、进度或触发器。
  首先,创建在 Currency表上声称并开采一个游标的长河:

IF OBJECT_ID ( 'dbo.uspCurrencyCursor', 'P' ) IS NOT NULL  
    DROP PROCEDURE dbo.uspCurrencyCursor;  
GO  
CREATE PROCEDURE dbo.uspCurrencyCursor   
    @CurrencyCursor CURSOR VARYING OUTPUT  
AS  
    SET NOCOUNT ON;  
    SET @CurrencyCursor = CURSOR  
    FORWARD_ONLY STATIC FOR  
      SELECT CurrencyCode, Name  
      FROM Sales.Currency;  
    OPEN @CurrencyCursor;  
GO  

接下去,运营以下批管理:声喜宝(Hipp)个局地游标变量,实践上述进度以将游标赋值给一些变量,然后从该游标提取行。

DECLARE @MyCursor CURSOR;  
EXEC dbo.uspCurrencyCursor @CurrencyCursor = @MyCursor OUTPUT;  
WHILE (@@FETCH_STATUS = 0)  
BEGIN;  
     FETCH NEXT FROM @MyCursor;  
END;  
CLOSE @MyCursor;  
DEALLOCATE @MyCursor;  
GO  

B、使用OUTPUT重回七个出口参数
  这种办法短处在于一旦结果聚集几百个要素,那么在仓库储存进度将要注脚几百个变量,十一分烦劳。

CREATE PROCEDURE Student.singS
      @id int,
      @name varchar(20) OUTPUT,
      @age int OUTPUT
AS
   select name,age from Student where id=@id
GO

调用段:

DECLARE @name varchar(20),@age int
EXECUTE Student.singS 28, @name OUTPUT,@age OUTPUT
print '学生的姓名为:' @name ',年龄为:' @age

C、SELECT再次回到结果集
  在蕴藏进度中写一段重回三个结出集的SELECT语句,倘若在调用段中仅仅EXEC procedure_name [parameter1...parametern],那么该SELECT语句的结果独有只会输出到显示器上,而不能够用这么些结果集做持续管理。借使要封存此结果集,唯有一种办法,即经过行使 INSERT/EXEC 将其积攒到永世表、临时表或表变量中,进而将结果流式管理到磁盘。

①把结果集存款和储蓄在一时表
创设存款和储蓄进度:

CREATE PROCEDURE Proc1
 @a varchar(50)
AS
 SELECT id,name FROM Table1 WHERE name=@a

调用段:

-- 创建一个临时表,和存储过程的结果集结构一致
CREATE TABLE #t1
(
  id int,
  name varchar(50)
)

-- 把结果集插入临时表中
INSERT INTO #t1 EXEC Proc1 'Ada'
-- do something with results
--用完之后要把临时表清空
DROP TABLE #t1

②把结果集存储在表变量
  但这种方法在查询的数据量异常的大的动静下比较影响属性,查询速度异常慢,在数据量非常小的景观下这种差别并不明明。

create proc proc1 as
   select col1 from dbo.table1;

create proc proc2 as
   declare @t table(col1 int);
   insert @t (col1) exec proc1;
   -- do something with results

3.存款和储蓄进程

仓库储存进程是一组用于达成一定作用的语句集,经过编写翻译后存储在数据库中。在SQL Server 二〇〇八中,不只能够用T-SQL编写存款和储蓄进度,也足以用CL本田UR-V编写存款和储蓄进度。

1.4.Transact-SQL语言基础

管住存款和储蓄进程

①查看存款和储蓄进度新闻

新京葡娱乐场388官网 8

②修改存款和储蓄进度

ALTER PROCEDURE|PROC [schema_name.] procedure_name
    -- Add the parameters for the stored procedure here
    [ { @parameter [ type_schema_name. ] data_type }  
        [ VARYING ] [ = default ] [ OUT | OUTPUT | [READONLY]  
    ] [ ,...n ]   
    [WITH <procedure_option> [ ,...n ]]
    [FOR REPLICATION]
AS { [ BEGIN ] sql_statement [;] [ ...n ] [ END ] }

<procedure_option> ::=   
    [ ENCRYPTION ]  
    [ RECOMPILE ]  
    [ EXECUTE AS Clause ]

③去除存款和储蓄进度

DROP { PROC | PROCEDURE } [ IF EXISTS ] { [ schema_name. ] procedure } [ ,...n ]  

3.1.用户定义的寄放进程

该种存款和储蓄进度是指封装了可选拔代码的模块恐怕经过,有2类别型:T-SQL存款和储蓄进程和CL奥迪Q3存款和储蓄进程。
T-SQL存款和储蓄进度是指保存的T-SQL语句集合
CLKuga存款和储蓄进程是指对Microsoft .NET Framework公共语言运营时(CL奥迪Q3)方法的援引

1.4.1.常量与变量

常量十分的少说。在SQL Server 二〇一〇中,存在三种变量。一种是系统定义和掩护的全局变量,一种是用户定义用来保存中间结果的片段变量。

3.2.恢宏存款和储蓄进程

扩充存款和储蓄进度是指能够动态加载和周转的DLL,允许使用编制程序语言(如C语言)创立自个儿的外部例程。增加存款和储蓄进度平素在SQL Server 二零零六的实例的地方空间中运作,能够采用SQL Server扩充存款和储蓄进程API完结编程。

1.4.1.1.系统全局变量

系统全局变量分为两大类,一类是与自然SQL Server连接或与当前拍卖有关的全局变量,如@@Rowcount意味着近些日子三个口舌影响的行数。@@error意味着保留近年来进行操作的失实状态。一类是与成套SQL Server系统有关的全局变量,如@@Version表示前段时间SQL Server的版本消息。

SELECT @@VERSION AS 当前版本;--查看当前SQL Server的版本信息

结果如图所示
新京葡娱乐场388官网 9

3.3.种类存款和储蓄进度

系统存款和储蓄进程是指积存在源数据库中,以sp早先的存款和储蓄进程,出现在各样系统定义数据库和用户定义数据库的sys架构中。

1.4.1.2.局地变量

一对变量能够享有一定数据类型,有必然的作用域,一般用来充当计数器总括或决定循环实行次数,只怕用于保存数据值。局部变量前独有1个@符,用DECLARE语句证明局地变量。

USE test
DECLARE @StudentId varchar(20)
SET @StudentId=(
SELECT Student.stu_no
FROM Student
WHERE stu_enter_score='603')
SELECT @StudentId AS 入学分数为603的学生学号
GO

结果如图所示
新京葡娱乐场388官网 10

3.3.1.创设存储进度准则

在准备和创建存款和储蓄进度时,应该满足一定的封锁和准则。

  • CREATE PROCEDURE定义自个儿可以富含自由数量和类其他SQL语句,但下表中的语句除此而外。无法在仓库储存进程的别的岗位接纳这么些讲话。
  • 能够引用在联合存款和储蓄进度中开创的对象,只要引用时已开立了该指标
  • 能够在存款和储蓄进程内援引临时表
  • 如若在仓库储存进度中开创了本地不时表,该有的时候表仅为该存款和储蓄进程而留存,退出该存储进程后,该有的时候表会消失
  • 若是进行的蕴藏进程调用了另一个储存过程,被调用的贮存进度能够访问第一个存款和储蓄进程的具有指标,包罗一时表
  • 万一实施对长距离SQL Server 二〇〇九实例实行转移的中远距离存储进程,那个改换将不能够被回滚。远程存储进度不到场事务管理
  • 积存进度中的参数的最大数量为2100
  • 存款和储蓄进程中的局地变量的最大数量仅受可用内部存款和储蓄器的限定
  • 基于可用内存的分歧,存款和储蓄进程最大可达128MB
语句 语句 语句
CREATE AGGREGATE CREATE RULE CREATE DEFAULT
CREATE SCHEMA CREATE(ALTER) FUNCTION CREATE(ALTER) TRIGGER
CREATE(ALTER) PROCEDURE CREATE(ALTER) VIEW SET PARSEONLY
SET SHOWPLAN_ALL SET SHOWPLAN_TEXT SET SHOWPLAN_XML
USE database_name

2.运算符

3.3.2.范围存款和储蓄进程内的名号

在积累进程内,就算用于语句的对象未有限定架构,则架构将默以为该存款和储蓄进度的框架结构。要是创制该存储进程的用户未有限制INSERT,SELECT,UPDATE或DELETE语句中援用的表名或试图名,则私下认可景况下通过该存款和储蓄进程进行的访谈将碰着该进程创制者权限的限量。借使有别的用户要动用存储进度,则具备用于数据定义语言(DDL)的说话(如CREATE,ALTE奥迪Q5,EXECUTE,DROP,DBCC或动态SQL语句)的目的名应当用该指标架构的名号来限制。

2.1.算数运算符

在SQL Server 二零零六中,算数运算富含加( )减(-)乘(*)除(/)取模(%)。举四个总结的事例。
示例1:在Student表中增多一列,列名叫stu_age,根据Student表的stu_birthday列计算stu_age列并插入数据。(演示插入整列数据的点子)
Student表数据如图所示
新京葡娱乐场388官网 11
试行上面包车型地铁讲话

ALTER TABLE Student
ADD stu_age int;--在Student表中添加stu_age列
CREATE TABLE #agetemp(stu_no varchar(8),age int);--新建一个临时表
INSERT INTO #agetemp(stu_no,age)--在临时表中插入学号和计算出来的年龄
SELECT Student.stu_no,YEAR(GETDATE())-YEAR(stu_birthday)--利用函数和运算符计算年龄
FROM Student;
UPDATE Student
SET Student.stu_age=#agetemp.age--将临时表中的age列数据整个复制到Student表的stu_age列
FROM #agetemp
WHERE Student.stu_no=#agetemp.stu_no--条件是两个表的stu_no列值相等
GO
SELECT * FROM Student

结果如图所示
新京葡娱乐场388官网 12

3.3.3.加密存款和储蓄进程的概念

假诺要创造存款和储蓄进度并确定保障其余用户无法查看该存款和储蓄进程的定义,则可以使用WITH ENCLacrosseYPTION,那样,进程定义将以不足读的样式储存。

2.2.赋值运算符

即等号(=),将表达式的值赋予另三个变量。举二个简约的例证。
示例2:总计Student表中学生的平分入学成绩并打字与印刷。
Student表的数额如图所示,stu_enter_score列存放了学员的入学战绩
新京葡娱乐场388官网 13
施行上面包车型客车言辞

DECLARE @average int--声明@average变量
SET @average=(--将计算出的平均值赋值给@average
SELECT AVG(stu_enter_score)
FROM Student)
PRINT @average--打印@average的值

结果如图所示
新京葡娱乐场388官网 14

3.3.4.SET语句选项

当创造可能更动T-SQL存款和储蓄进度后,数据库引擎将保存SET QUOTED_IDENTIFIER和SET ANSI_NULLS的设置,试行存款和储蓄进度时将运用这个原来设置而忽视任何客户端会话的ET QUOTED_IDENTIFIER和SET ANSI_NULLS设置。别的SET选项在创设或改造存款和储蓄进程后不保留。

2.3.位运算符

位运算符包含与运算(&),或运算(|)和异或运算(^),能够对四个表明式进行位操作,那多少个表达式可以是整型数据或二进制数据。Transact-SQL首先把整型数据调换为二进制数据,然后按位运算。举个轻便的事例。
示例3:评释2个int型变量@num1,@num2,对那四个赋值且做与或异或运算。
实行下边包车型大巴口舌

DECLARE @num1 int,@num2 int
SET @num1=5 
SET @num2=6
SELECT @num1&@num2 AS 与,
@num1|@num2 AS 或,
@num1^@num2 AS 异或

结果如图所示
新京葡娱乐场388官网 15
庞大示例4:写贰个十进制调换为二进制的函数

CREATE FUNCTION Bin_con_dec(@dec int)--定义十进制转换为二进制函数
RETURNS varchar(20)
AS
BEGIN
DECLARE @quo int,@remainder varchar(20),@quo1 int
SET @quo=@dec
SET @remainder=''
WHILE @quo<>0
BEGIN
SET @quo1=@quo/2
SET @remainder=CAST(@quo%2 AS varchar(20)) @remainder
SET @quo=@quo1
END
RETURN @remainder
END

施行下边包车型客车函数后,运维下列语句验证函数准确性

PRINT dbo.Bin_con_dec(42)

结果为101010,函数定义正确。

3.4.使用存款和储蓄进度

2.4.相比较运算符

也称关系运算符,用于比非常多少个值的关联,常见的有等于(=),大于(>),小于(<),大于等于(>=),小于等于(<=),不等于(<>或!=)
示例5:从Student表中查询入学战绩在平均分以上的学员新闻
Student表的数量如图所示
新京葡娱乐场388官网 16
实践下列语句

DECLARE @ave int
SET @ave=(SELECT AVG(stu_enter_score) FROM Student)
SELECT *FROM Student
WHERE stu_enter_score>=@ave;

结果如下图所示
新京葡娱乐场388官网 17

注:无法一直把代码写成上边的样式

SELECT * FROM Student
WHERE stu_enter_score>=AVG(stu_enter_score)

消息147,级别15,状态1,第2 行
汇合不应出现在WHERE 子句中,除非该聚合位于HAVING 子句或选拔列表所包罗的子查询中,而且要对其张开联谊的列是外界引用。

因为AVG是聚合函数。

3.4.1.创设存款和储蓄进度

示例3:将示例2用存款和储蓄进度达成
Student表的数量如图所示
新京葡娱乐场388官网 18
实行下列语句

CREATE PROCEDURE alter_data
@a int--参数
AS
BEGIN
DECLARE stu_cursor CURSOR FORWARD_ONLY LOCAL
FOR SELECT stu_no,stu_enter_score FROM student
OPEN stu_cursor
DECLARE @score INT
DECLARE @stu_no VARCHAR(8)
FETCH NEXT FROM stu_cursor INTO @stu_no,@score--把游标值赋值给变量@score
WHILE @@FETCH_STATUS=0 
BEGIN
IF @score>=@a
BEGIN
UPDATE student
SET stu_enter_score=@score-100 WHERE stu_no=@stu_no 
END
FETCH NEXT FROM stu_cursor INTO @stu_no,@score--把游标值赋值给变量@score
END
CLOSE stu_cursor
DEALLOCATE stu_cursor
END
GO
EXEC dbo.alter_data '600'

结果如图所示
新京葡娱乐场388官网 19

2.5.逻辑运算符

逻辑运算符的意义是对标准实行测验。ALL,AND,ANY,BETWEEN,EXISTS,IN,LIKE,NOT,ALL,SOME。上边用SOME来比喻。SOME的功用是要是在一组比较中,有个别为true那就为true。
示例6:查询Student表中是还是不是留存入学成绩超过平均分的上学的小孩子,假如存在,输出true,不设有输出false。
Student表的stu_enter_score列(入学战表)数据如图所示
新京葡娱乐场388官网 20
推行下边包车型地铁讲话

USE test
IF (SELECT AVG(stu_enter_score) FROM Student)<=SOME(SELECT stu_enter_score FROM Student)
PRINT 'true'
ELSE
PRINT 'false'
GO

结果如图所示
新京葡娱乐场388官网 21

3.4.2.翻看存款和储蓄进程

能够透过利用系统存款和储蓄进程只怕目录视图查看存款和储蓄进程的概念

2.6.连接运算符

加号( )是字符串连接运算符,能够用它把字符串串连起来,在示例4的十进制转二进制函数中,就用上了加号。
示例7:将Student表的stu_name列和stu_enter_score列放在同样列彰显,列名字为score
Student表的数目如图所示
新京葡娱乐场388官网 22
实施下列语句

SELECT stu_name CAST(stu_enter_score AS VARCHAR(3)) AS score FROM Student

实践结果如图所示
新京葡娱乐场388官网 23

注:stu_enter_score列数据类型为int,加号只对字符串类型数据有效,由此要用CAST函数将stu_enter_score的数据类型转变为varchar(3),那样技艺兑现字符串拼接。

3.4.2.1.图形化分界面

如下图
新京葡娱乐场388官网 24

2.7.一元运算符

一元运算符只对一个表明式实行操作,该表达式能够是数字数据类型中的任何一种数据类型。SQL Server 二〇〇八提供的一元运算符包涵正( ),负(-),位反(~)。
示例8:声美素佳儿(Friso)个int数据类型变量@num并赋值,对该变量做正负位反操作。
进行下列语句

DECLARE @num INT
SET @num=45
SELECT  @num AS 正,-@num AS 负,~@num AS 位反
GO

结果如图所示
新京葡娱乐场388官网 25

注:位反操作符用于取二个数的补数,只好用来整数。

3.4.2.2.连串存款和储蓄进度sp_helptext查看存款和储蓄进度定义

实施下列语句

EXEC sp_helptext 'alter_data'

结果如图所示
新京葡娱乐场388官网 26

2.8.运算符的优先级

优先级 运算符
1 ~(位反)
2 *(乘),/(除),%(取模)
3 (正),-(负), (加), (连接),-(减),&(位与)
4 =,>,<,>=,<=,<>,!=,!>,!<(比较运算符)
5 ^(位异或),位或(符号打不出来,前面有,自己翻)
6 NOT
7 AND
8 ALL,ANY,BETWEEN,IN,LIKE,ALL,SOME
9 =(赋值)

当表明式中的运算符有一样的先行级时,遵照它们在表明式中的地方,一元运算符按从右往左运算,二元运算符(对多少个表明式功能的运算符)按从左往右运算。
示例9:验证运算符优先级
执行下列语句

DECLARE @result INT,@num INT
SET @num=45
SET @result=@num (~@num)*4-@num/(~@num)
SELECT @result AS result
GO

结果如图所示
新京葡娱乐场388官网 27
测算代码中的表明式
@result=@num (~@num)4-@num/(~@num)
=@num (-46)
4-@num/(-46)
=45 (-46)4-45/(-46)
=45 (-46)
4
=-139

新京葡娱乐场388官网,3.4.2.3.种类存款和储蓄进程sp_depends查看存款和储蓄进度有关信息

举办下列语句

EXEC sp_depends 'alter_data'

结果如图所示
新京葡娱乐场388官网 28

3.决定语句

3.4.2.4.目录视图查看存款和储蓄进度

实践下列语句

SELECT * FROM sys.procedures

结果如图所示
新京葡娱乐场388官网 29

3.1.BEGIN END语句块

BEGIN END能够定义SQL Server语句块,使这几个言辞作为一组语句实践,允许语句嵌套。举个例子请见示例4

3.4.3.改变存款和储蓄进程

ALTER PROCEDURE语句修改存款和储蓄进程,只需将上边示例中的CREATE修改成ALTEEscort运转就行了。

3.2.IF ELSE语句块

用来钦点T-SQL语句的实施尺度,若条件为真,则实行标准表明式前边的说话,条件为假时,可以试用ELSE关键字钦赐要施行的T-SQL语句。举例请见示例4

3.4.4.剔除存款和储蓄进程

施行下列语句删除存款和储蓄进程

DROP PROCEDURE alter_data

3.3.CASE分支语句

示例10:将Student表的学习者,性别和原籍打字与印刷出来,必要籍贯只可以展现外省,省内或自治区。
Student表的数额如图所示
新京葡娱乐场388官网 30
施行下列语句

SELECT stu_name AS 姓名,stu_sex AS 性别,
(CASE stu_native_place
WHEN '浙江' THEN '省内'
WHEN '内蒙古' THEN '自治区'
WHEN '西藏' THEN '自治区'
WHEN '宁夏' THEN '自治区'
WHEN '新疆' THEN '自治区'
WHEN '广西' THEN '自治区'
ELSE '省外'
END) AS 籍贯 
FROM Student

结果如图所示
新京葡娱乐场388官网 31

3.4.WHILE语句

用以安装双重推行T-SQL语句或语句块的条件。
示例11:用“*”在荧屏上输出三个宽度为9的菱形。
进行下列语句

DECLARE @width int,@j int
SET @width=9--@width为菱形的最大宽度
SET @j=1--@j表示每行打印的“*”符号的个数
WHILE @j<=@width
BEGIN
PRINT SPACE((@width-@j)/2) REPLICATE('*',@j)--SPACE函数打印n个空字符,REPLICATE打印n个特定字符串
SET @j=@j 2
END
SET @j=@width-2
WHILE @j>0
BEGIN
PRINT SPACE((@width-@j)/2) REPLICATE('*',@j)
SET @j=@j-2
END

结果如图所示
新京葡娱乐场388官网 32

3.5.WAITFO智跑延缓语句

WAITFO宝马X3延迟语句能够让在它现在的言语在贰个钦点的随时也许时间距离后实践,能够悬挂起批管理,存款和储蓄进程或业务的实行。
示例12:在有些时间点查询Student表学号为20180101的学员新闻

BEGIN
WAITFOR TIME '15:03'--在15点03分查询
SELECT * FROM Student
WHERE stu_no='20180101'
END

示例13:在3秒钟后查询Student表学号为20180102的学生新闻

BEGIN
WAITFOR DELAY '00:03'--在3分钟后查询
SELECT * FROM Student
WHERE stu_no='20180102'
END

新京葡娱乐场388官网 33

3.6.RETULacrosseN无条件退出语句

该语句表示无条件停止查询,批管理或存款和储蓄进程的推行。存款和储蓄进度和批管理RETU纳瓦拉N语句前边的语句都不再进行。当在仓库储存进程中应用该语句时,能够钦定再次来到给调用应用程序、批处理或进程的整数值。假使RETUENCOREN未钦赐重临值,则存款和储蓄进程的重临值是0

3.7.GOTO跳转语句

该语句使T-SQL批管理的进行跳转至钦定标签。由于该语句破坏结构化语句的组织,尽量少用
示例13:将GOTO作为分支机制
实践上面语句

DECLARE @Counter int;  
SET @Counter = 1;  
WHILE @Counter < 10  
BEGIN   
    SELECT @Counter  
    SET @Counter = @Counter   1  
    IF @Counter = 4 GOTO Branch_One --Jumps to the first branch.  
    IF @Counter = 5 GOTO Branch_Two  --This will never execute.  
END  
Branch_One:  
    SELECT 'Jumping To Branch One.'  
    GOTO Branch_Three; --This will prevent Branch_Two from executing.  
Branch_Two:  
    SELECT 'Jumping To Branch Two.'  
Branch_Three:  
SELECT 'Jumping To Branch Three.';

结果如图所示
新京葡娱乐场388官网 34
当Counter=4时,实践GOTO语句输出Branch One,试行完这些讲话之后就打破了WHILE循环,接着实行Branch_One语句中的GOTO,输出Branch Three,结束。

注:在WHILE循环中应用GOTO会打破循环。

示例14:用GOTO语句完成示例1第11中学打字与印刷菱形的功能
实施下列语句

DECLARE @width int,@j int,@i int
SET @width=9--@width为菱形的最大宽度
SET @j=1--@j表示每行打印的“*”符号的个数
SET @i=1--@i表示下一行打印第i行
Set3:PRINT SPACE((@width-@j)/2) REPLICATE('*',@j)--SPACE函数打印n个空字符,REPLICATE打印n个特定字符串
SET @i=@i 1
IF @i<=(@width 1)/2
GOTO Set1
ELSE
GOTO Set2
Set1:
SET @j=@j 2
GOTO Set3
Set2:
SET @j=@j-2
IF @j>=1
GOTO Set3

结果如图所示
新京葡娱乐场388官网 35

3.8.T安德拉Y CATCH错误处理语句

要是TEnclaveY块内部产生错误,会将决定传递给CATCH块内的语句组。T奇骏Y CATCH构造捕捉全体严重等第大于10但不会甘休数据库连接的谬误。
示例15:TRY CATCH的示例
试行下列语句

BEGIN TRY
SELECT * FROM Student
SELECT 120/0 FROM Student
END TRY
BEGIN CATCH
SELECT ERROR_MESSAGE() AS '错误信息'
END CATCH

实践结果如图所示
新京葡娱乐场388官网 36
语句中3个select语句全体都施行了。假设把报错的select语句放到平常的select语句前边,平常的select语句仍是能够不可能实行呢?实践下列语句

BEGIN TRY
SELECT 120/0 FROM Student
SELECT * FROM Student
END TRY
BEGIN CATCH
SELECT ERROR_MESSAGE() AS '错误信息'
END CATCH

结果如图所示
新京葡娱乐场388官网 37
不奇怪select语句不可能实施。THighlanderY CATCH语句的逻辑是,一旦TLANDY语句块中出现难题讲话,立刻跳转到CATCH语句块,T翼虎Y语句块接下去的言辞不再实施。

4.常用函数

4.1.数据类型转换函数

暗中同意意况下SQL Server会对部分数据类型进行活动调换,这种转移称为隐式转变。蒙受不大概自动调换,则须要用CAST()函数和CONVERT()函数转变,这种转移称为显式转变。CAST()函数和CONVERT()函数的效率是平等的,CAST函数更便于选取,CONVERT函数的优点是能够内定日期和数值格式。
示例16:将Student表中的学号调换为日期格式
上边两句语句的效率是同一的,试行下列语句

SELECT stu_name,CAST(stu_no AS DATE) AS 学号转换成日期,stu_enter_score,stu_birthday FROM Student
SELECT stu_name,CONVERT(DATE,stu_no) AS 学号转换成日期,stu_enter_score,stu_birthday FROM Student

结果如图所示
新京葡娱乐场388官网 38
示例17:用CONVERT()函数将stu_birthday转化成钦定格式的日期
实行下列语句

SELECT stu_name,CONVERT(VARCHAR(20),stu_birthday,101) FROM Student
--CONVERT函数将DATE类型的stu_birthday字段转化为字符串,并限定了样式,代码101

结果如图所示
新京葡娱乐场388官网 39

注:在上述代码中,CONVERT(DATE,stu_birthday,101)这么写是没用的。101格式码只对日期格式转化为字符串有效,别的格式转化为日期格式是不行的。

任何常用函数太轻便了这里不写了,略。

本文由www.204.net发布,转载请注明来源

关键词: www.204.net