存储过程小结

  

1.1.    存储过程

1.1.1.    大小写

大小写不敏感

1.1.2.    标识符

@ + 一般规则

set @i=1

1.1.3.    语句分隔符与续行符

直接写成多行

1.1.4.    注释

--

/*    */

1.1.5.    基本数据类型

int,money,varchar,bit,DateTime

1.1.6.    数组类型

无

1.1.7.    运算符

+ - * / %	加、减、乘、除、取余
> >= < <= = <>	大于、大于等于、小于、小于等于、等于、不等于
Not and or	与、或、非
+	串连接

1.1.8.    语句

顺序：

declare @i int

set @i=1

 

分支：

declare @i int

set @i=1

 

if      (@i>10)

       print 'true'

else

       print 'false'

 

SELECT   Category =

      CASE type

         WHEN 'popular_comp' THEN 'Popular Computing'

         WHEN 'mod_cook' THEN 'Modern Cooking'

         WHEN 'business' THEN 'Business'

         WHEN 'psychology' THEN 'Psychology'

         WHEN 'trad_cook' THEN 'Traditional Cooking'

         ELSE 'Not yet categorized'

      END

 

循环：

 

declare @i int

set @i=1

while @i<=20000

begin

insert into t_team(FCode,FName,FLeader,FDwID,FType,FNo)

       values(Convert(varchar(255),@i),'FName','FLeader','AAC7E414-6C94-4F29-9816-DC39F34E5132','FType',@i)

set @i=@i+1

end

 

break

continue

return

 

1.1.9.    函数与过程调用

CREATE FUNCTION ISOweek (@DATE datetime)

RETURNS int

AS

BEGIN

   DECLARE @ISOweek int

   SET @ISOweek= DATEPART(wk,@DATE)+1

      -DATEPART(wk,CAST(DATEPART(yy,@DATE) as CHAR(4))+'0104')

--Special cases: Jan 1-3 may belong to the previous year

   IF (@ISOweek=0) 

      SET @ISOweek=dbo.ISOweek(CAST(DATEPART(yy,@DATE)-1 

         AS CHAR(4))+'12'+ CAST(24+DATEPART(DAY,@DATE) AS CHAR(2)))+1

--Special case: Dec 29-31 may belong to the next year

   IF ((DATEPART(mm,@DATE)=12) AND 

      ((DATEPART(dd,@DATE)-DATEPART(dw,@DATE))>= 28))

      SET @ISOweek=1

   RETURN(@ISOweek)

END

 

SELECT master.dbo.ISOweek('12/26/1999') AS 'ISO Week'

1.1.10.             代词

无

1.1.11.             变量作用域

@ISOweek      局部变量

@@                全局变量

 

 

1.1.12.             存储过程

 

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

 

 

EXECUTE au_info @lastname = 'Dull', @firstname = 'Ann'