视频课 https://edu.csdn.net/course/play/7940

 

批处理是一组一起提交给SQL  Sever执行的SQL语句。当执行批处理时SQL Sever将批处理的语句编译到一个称为执行计划的可执行单元。这样可以节省执行时间。

 

为了创建批处理，你可以写出多个SQL语句，后面在结尾跟着关键字Go,Go是一个命令，它指定批处理的结束。

 

使用变量

   创建批处理的时候,你需要在执行的时候保存一些临时值,为存储这些临时值你可以声明变量并且为它们指定值.

定义变量

  declare 变量名称 数据类型

给变量赋值

(1)  直接赋值

set 变量名称=值

(2)  从表中获得值

select 变量名称=值 from 表名

使用结构

If…else条件选择结构

   If  <条件表达式>

    <SQL语句或语句块1>

  else

     <SQL语句或语句块2>

CASE结构

  CASE <表达式>

  WHEN <表达式> THEN  <表达式>

   [[WHEN  <表达式>  THEN <表达式>] […]]

   [ELSE  <表达式>]

 END

BEGIN…END语句块

  BEGIN

       <SQL语句块>

   END

WHILE循环结构

  WHILE <布尔表达式>

   BEGIN

       <SQL语句块或程序块>

      [BREAK]

      [CONTNUE]

      [SQL语句或程序块]

   END

什么是存储过程

   存储过程是为完成特定的功能而汇集在一起的一组SQL程序语句，经编译后存储在数据库中的SQL程序。

.

 

当创建存储过程时，需要确定存储过程的三个组成部分：

 （1）所有的输入参数以及传给调用者的输出参数。

 （2）被执行的针对数据库的操作语句，包括调用其他存储过程的语句。

 （3）返回给调用者的状态值，以指明调用是成功还是失败。

常用的系统存储过程 

 

Ø  定义不带参数存储过程的语法

  CREATE PROCEDURE  存储过程名

            AS            --注释:表示后面是存储过程要执行的语句

            SQL语句

    GO

Ø       定义带参数存储过程的语法

  CREATE PROCEDURE  存储过程名

              @参数1  数据类型[ = 默认值 OUTPUT],

               …… ,

              @参数n  数据类型[ = 默认值 OUTPUT]

            AS            --注释:表示后面是存储过程要执行的语句

            SQL语句

    GO

OUTPUT:指定存储过程必须返回一个参数.该存储过程的匹配参数也必须由关键字OUTPUT创建.使用游标变量作为参数时使用该关键字.

【例9-1】创一个存储过程，以简化对sc表的数据添加工作，使得在执行该存储过程时，其参数值作为数据添加到表中。

程序清单如下：

CREATE  PROCEDURE [dbo].[ pr1_sc_ins]

@Param1 char(10),@Param2char(2),@Param3 real

AS

BEGIN

      insert into sc(sno,cno,score) values(@Param1,@Param2,@Param3)

END

【例9-2】创建一个带有参数的简单存储过程，从视图中返回指定的雇员（提供名和姓）及其职务和部门名称，该存储过程接受与传递的参数精确匹配的值

程序清单如下。

USE AdventureWorks;

GO

CREATE  PROCEDURE GetEmployees

    @lastname varchar(40),

    @firstname varchar(20)

AS

    SELECT LastName, FirstName, JobTitle,Department

    FROM HumanResources.vEmployeeDepartment

    WHERE FirstName = @firstname AND LastName =@lastname;

GO

 

Ø       调用存储过程语法

EXEC   PROCEDURE 存储过程名

  【例9-3】执行存储过程au_infor_all。

au_infor_all 存储过程可以通过以下方法执行：

EXECUTE（EXEC） au_infor_all

【例9-4】使用 EXECUTE 命令传递参数，执行例9-1定义的存储过程pr1_sc_ins。

sc_ins存储过程可以通过以下方法执行：

EXEC pr1_sc_ins ‘3130040101’,’c1’,85

当然，在执行过程中变量可以显式命名：

EXEC sc_ins @Param1=’ 3130040101’,@Param2=’c1’,@Param3=85

【例9-5】 执行例9-2定义的存储过程GetEmployees。

GetEmployees存储过程可以通过以下方法执行：

EXECUTE（EXEC） GetEmployees'Dull', 'Ann'  或者

EXECUTE（EXEC） GetEmployees@lastname = 'Dull', @firstname = 'Ann'  或者

EXECUTE（EXEC） GetEmployees@firstname = 'Ann', @lastname = 'Dull'

修改存储过程

 

 

 

重命名存储过程

      例: 把存储名称p_name修改为 p­address

        EXEC sp_rename ‘p_name’,’p_address’

 删除存储过程

      例: 删除存储过程p_name

          DROP  PROCEDURE p_name

你可以创建函数来永久存储一系列SQL语句,根据函数返回值形式的不同,用户定义的函数有标量函数和表值函数.

Ø       创建函数的语法:

CreateFunction[schema_name] function_name

([{@parameter_name[AS][type_schema_name.]

  parameter_data_type

  [=default] }

  [,…n ]

 ]

 )

Returnsreturn_data_type

[WIIH<function_option>[,…n] ]

[AS]

BEGIN

function_body

        return expression

END

 

@parameter_name 是在函数中的参数.可以有一个或多个被声明的参数.

[typr_schema_name] parameter_data_type 是参数的数据类型,和可选的它的所属的模式.

[=default] 是参数的默认值.

return_data_type 是一个标量的用户定义函数的返回值.

function body 指定一系列T-SQL语句.

Ø       创建标量函数

标量函数接受一个参数并且返回在RETURNS从句中指定的类型的一个数据值。标量函数可以返回除了文本、ntext、图片、光标和时间戳之外的任何数据类型。有些标量函数，例如current_timestamp，不需要任何参数。

语法：

create function 函数名(@变量名1 数据类型)

returns 返回值的数据类型

as

begin

 declare @变量名2 数据类型

 select @变量名2=sum(列名) from 表名1 where 主键名=@变量2

 return @变量名2

end

 

select 函数名(主键名) from 表名2

例如:

     CREATE FUNCTION HumanResources.MonthlySal  (@PayRate float)

     RETURNS float

     AS

     BEGIN

     RETURN (@PayRate * 8 * 30)

     END

例如：

     DECLARE @PayRate float

     SET @PauRate = HumanResources.MonthlySal(12.25)

     PRINT @PauRate

注释：上述代码中，@PayRate是一个变量，它将存储MonthlySal函数返回的值。

创建表值函数

内联表值函数从一个SELECT语句的结果集返回一个表数据的变量。内联函数不再BEGIN和END语句中包含函数体。

例子1：

  Create function fx_Department_name(@grnamenavarchar(20))

  Return table

As

Return(

Select *

FromHumanResources.Department

WhereGroupName=@grname

)

GO

这里的内联表，接收一组名称作为参数并且饭回来自Department表属于组的部门的详情。

使用 SELECT*FROM fx_Department_name 可以查看上述代码的输出。

多语句表值函数 

多语句表值函数使用多个语句来创建表，它被返回给调用语句。函数体包含BEGIN.。。。END块，它保存一系列T-SQL语句以创建和插入行盗临时表。临时表被在结果集中返回，并且基于函数中提到的规范创建。

语法:

create  function 函数名(@变量名1 数据类型)

returns @变量名2 table

(

  和创建表中的内容一样

)

as

begin

 insert @变量名2 select表中的列名 from  表名 ----指把表中的内容加到新创建的函数表中

 where 表中的另一个列名>@变量名1

  insert @变量名2values(.......)

end

select * from 函数名(表中的另一个列中的内容)

 

 

例如：

    CREATE  FUNCTION PayRate (@rate money)

    RETURNS  @table TABLE

   (EmployeeID  int  NOT NULL,

   RateChangeDate  datetime  NOT NULL,

    Ratemoney  NOT NULL,

   PayFrequency  tinyint  NOT NULL,

   ModifiedDate  datetime  NOTNULL）

    AS

    BEGIN

    INSERT@table

    SELECT *

    FROMHumanResources.EmployeePayHistory

    WHERE Rate> @rate

    RETURN

    END

语句：

    SELECT *FROM PayRate(45)

注释：

     函数以在函数内创建的临时表@table，的形式返回一个结果集。以上的语句执行函数。

 

 

 

 

                                                                                     

1、批处理的用途是什么？

2、在批处理中本地变量的范围是什么？

3、存储过程如何返回值？

4、下面的哪个结构被使用，当你需要重复执行一系列T-SQL语句的时候？

   A、try – catch块

   B、while语句

   C、if-else语句

   D、case语句

 

 

1、批处理是一系列一起提交到服务器执行的SQL语句。

2、你可以使用变量存储一个临时值。

3、你可以使用print语句来在屏幕上显示一个变量的内容。

4、你可以在批处理中使用注释给代码写注释。

5、你可以使用 if –else语句从条件执行SQL语句。

6、CASE语句求一系列条件的值并且返回各种可能结果中的一个。

7、你可以在批处理中使用WHILE语句以允许一系列T-SQL语句重复执行，只要给定条件为真。

8、BREAK语句导致从WHILE循环中退出。

9、存储过程是各种T-SQL语句的集合，它被存储在一个名字下，并且作为一个单元执行。

10、存储过程可以使用CREATE PROCEDURE语句创建。

11、存储过程允许你声明参数、变量和使用T-SQL语句并且编程逻辑。

12、存储过程提供更好的性能、安全性和准确性并且减少网络拥塞

13、存储过程通过输入参数接受数据。

14、存储过程通过输出参数或返回语句返回数据。

15、存储过程可以使用EXECUTE语句执行。

16、存储过程使用alter procedute语句执行修改

17、用户定函数是一个数据库对象，它包含一系列T-SQL语句。

18、用户定义函数可以返回一个单一标量值或结果集。

文章来源: aaaedu.blog.csdn.net，作者：tea_year，版权归原作者所有，如需转载，请联系作者。

原文链接：aaaedu.blog.csdn.net/article/details/51446620