首页 > 代码库 > 存储过程--create procedure

存储过程--create procedure

1.Transact-SQL语言基本用法:

1 变量

2 流程控制命令

3 其它命令

4 常用函数


1.1变量

局部变量声明和赋值

declare @变量名 变量类型 [,@变量名 变量类型]--变量的声明

selelct @变量=变量值

set @变量=变量值                                               --变量赋值

【例】声明一个长度为8个字符的变量id,并赋值。

  declare@id char(8)

  select@id =10010001

1.2流程控制

1.2.1 流程控制种类

    a.begin.........end

    b.if......else

    c.case

    d.while.......continue....break

    e.waitfor

    f.goto

    g.return

    h.use

1.2.1-a

其语法如下:

  BEGIN

  <命令行或程序块块>

  END

BEGINEND 用来设定一个程序块,将在BEGINEND 内的所有程序视为一个单元执行。

BEGINEND 经常在条件语句(如IFELSE)中使用。

在BEGINEND 中可嵌套另外的BEGINEND 来定义另一程序块。

1.2.1-b

其语法如下:

  IF<条件表达式>

  <命令行或程序块>

  [ELSE[条件表达式]

  <命令行或程序块>]

其中:

v<条件表达式>可以是各种表达式的组合,但表达式的值必须是逻辑值

vELSE 子句是可选的,最简单的IF语句没有ELSE子句部分。

vIFELSE用来判断当某一条件成立时执行某段程序,条件不成立时执行另一段程序。

v如果不使用程序块,IF或ELSE只能执行一条命令。IFELSE 可以进行嵌套,在Transact-SQL中最多可嵌套32级

eg:

从SC数据表中求出学号为S1同学的平均成绩,如果此平均成绩大于或等于60分,则输出pass信息。

if (select avg(score) from sc where sid=‘s1‘ group by sid)>=60

 begin

    print ‘pass‘

 end


1.2.1-c

CASE 命令有两种语句格式:

格式1:

  CASE<运算式>

  WHEN <运算式>THEN <运算式>

 

  WHEN <运算式>THEN <运算式>

  [ELSE <运算式>]

  END

该语句的执行过程是:v将CASE后面表达式的值与各WHEN子句中的表达式的值进行比较,

v如果二者相等,则返回THEN后的表达式的值,然后跳出CASE语句,否则返回ELSE子句中的表达式的值。

vELSE子句是可选项。当CASE语句中不包含ELSE子句时,如果所有比较失败时,CASE语句将返回NULL。

eg:

从学生表S中,选取SNO,SEX,如果SEX为则输出M,如果为输出F

select sno,sex=

    case sex

    when ‘男‘ then ‘M‘

    when ‘女‘ then ‘F‘

    end

from s

格式2:CASE

     WHEN <条件表达式>THEN <运算式>

    

     WHEN <条件表达式>THEN <运算式>

     [ELSE <运算式>]

   END

eg:

从SC表中查询所有同学选课成绩情况,凡成绩为空者输出未考、小于60分输出不及格、60分至70分输出及格、70分至90分输出良好、大于或等于90分时输出优秀”.

select sno,cno,

    score=

    case

    when score is NULL then ‘未考‘

    when score < 60  then ‘不及格‘

    when score >=60 and  score <70 then ‘及格‘

    when score >=70 and  score<90 then ‘良好‘

    when score>=90 then ‘优秀‘

    end

from s

1.2.1-d

其语法如下:

  WHILE<条件表达式>

  BEGIN

    <命令行或程序块>

    [BREAK]

    [CONTINUE]

    [命令行或程序块]

  END

WHILE命令在设定的条件成立时,会重复执行命令行或程序块。

CONTINUE命令可以让程序跳过CONTINUE命令之后的语句,回到WHILE循环的第一行,继续进行下一次循环。

BREAK命令则让程序完全跳出循环,结束WHILE命令的执行。

WHILE语句也可以嵌套。

eg:

以下程序计算1-100之间所有能被3整除的数的个数及总和。

declare @s smallint,@i smallint,@nums smallint

set @s=0

set @i=1

set @nums=0

while(@i<=100)

  begin

    if (@i%3=0)

       begin

        set @s=@s+@i

        set @nums=@nums+1

       end

    set @i=@i+1

    end

  end

print @s

print @nums

1.2.1-e

eg:

等待1 小时2 分零3 秒后才执行SELECT语句。

waitfordelay ‘01:02:03’

Select  *  from    employee

1.2.1-f

如:求1+2+3+…+10的总和。

DECLARE@S SMALLINT,@I SMALLINT

SET@I=1

SET@S=0

BEG:

IF(@I<=10)

   BEGIN

       SET @S=@S+@I

       SET @I=@I+1

       GOTO BEG

   END

PRINT@S

1.2.1-h

USE {databasename}

USE teach

1.3创建存储过程

1.3.1有参无返

如:在teach数据库中,创建一个名称为InsertRecord的存储过程,该存储过程的功能是向数据表s中插入一条记录,新记录的值由参数提供。

USE teach

CREATE PROCEDURE InsertRecord

(

@sno char(6),

@sn char(20),

@age numeric(5),

@sex char(2),

@dept char(10)

)

AS

INSERT INTO s VALUES(@sno,@sn,@sex,@age,@dept)

1.3.2有参有返

定义能够返回值的存储过程。

如:在teach数据库中,创建一个名称为Query_Study的存储过程,该存储过程的功能是从数据表s中根据学号查询某一同学的姓名和系别。

USE teach

GO

CREATE PROCEDURE Query_Study

(

@sno char(6),

@sn char(20) OUTPUT,--返回值参数定义

@dept char(10) OUTPUT

)

AS

SELECT@sn=sn,@dept=dept

FROM s

WHERE sno=@sno

GO

DECLARE @sn char(20)

DECLARE @dept char(10)

EXECUTE Query_Study ‘S10‘,@sn OUTPUT,@dept OUTPUT

SELECT‘姓名‘=@sn,‘系别‘=@dept

1.3.3-调用

1.无参调用

EXECUTE myproc

2.执行teach库中存储过程InsertRecord (带参调用) 。

EXECUTE InsertRecord @sno =‘S1’, @sn = ‘王大利’,@sex = ‘男’,@age = 18,@dept= ‘计算机系’

3.执行teach库中的存储过程InsertRecordDefa(含默认值调用)。

EXECUTE InsertRecordDefa @sno =‘S10‘,@sn = ‘高平‘,@sex = ‘女‘,@age = 18

4.执行teach库中的存储过程Query_Study (含有输出参数)。

DECLARE @sn char(20)

DECLARE @dept char(10)

EXECUTE Query_Study ‘S10‘,@sn OUTPUT,@dept OUTPUT

SELECT‘姓名‘=@sn,‘系别‘=@dept

本文出自 “8756526” 博客,谢绝转载!

存储过程--create procedure