SQL基础系列(3)-变量、函数、存储过程等

1.    变量

定义变量

DECLARE @a INT

赋值

SET @a=1

PRINT @a

DECLARE @name NVARCHAR(30)

--select 赋值

SELECT @name='zcx'

PRINT @name

SELECT @name=FirstName FROM dbo.Persons

PRINT @name

 

DECLARE @name NVARCHAR(50)

--update 赋值

UPDATE  Persons

SET     @name = FirstName

WHERE   ID = 1

PRINT @name

2.    临时表

--创建临时表1

create table #persion

(

     [id] [int]  NOT NULL,

     [name] [nvarchar](5) NOT NULL,

     [pwd] [nvarchar](20) NULL

)

 

insert into #persion values(1,'zcx','123')

 

SELECT * INTO #psersion1 FROM #persion

select * from #persion UNION ALL select * from #psersion1

 

DROP TABLE #persion

DROP TABLE #psersion1

插入select的数据

insert into #persion select id,FirstName,LastName from dbo.Persons

注意和select into的区别

Select into 会创建新的临时表

修改临时表结构

alter table #persion add [tmpid] int NOT NULL IDENTITY(10,1)

alter table #persion add [tmpid1] uniqueidentifier NOT NULL default(newid())

                                                                          

给查询结果集增加自增长列

要求主表Persons没得主键

select IDENTITY(int,1,1)as ID, * into #temp from Persons

SELECT * FROM #temp

和count性质类似

select SUM(1) from Persons

 

有主键的表添加列

select (select SUM(1) from Persons where id<= a.id) as myID,* from Persons a order by myID

 

创建表变量：下面代码要同时执行

declare @mytable table

(

    id int not null,

    name nvarchar(50) null

)

 

 

INSERT INTO @mytable

        ( id, name )

VALUES  ( 0, -- id - int

          N'zcx'  -- name - nvarchar(50)

          )

         INSERT INTO @mytable

                 ( id, name )

         VALUES  ( 0, -- id - int

                   N'zjj'  -- name - nvarchar(50)

                   )

 

select * from @mytable

 

3.    循环

DECLARE @index INT

DECLARE @resukt INT

SET @index = 1

SET @resukt = 0

WHILE @index <= 100

    BEGIN

        SET @resukt += @index

        SET @index += 1

    END

PRINT @resukt

4.    条件

IF ( 3 > 5 )

    BEGIN

        PRINT 'true'

    END

ELSE

    BEGIN

        PRINT 'false'

    END

 

When then

DECLARE @num INT

DECLARE @name NVARCHAR(30)

SET @num = 3

SET @name = CASE WHEN @num = 1 THEN 'elephant'

                 WHEN @num = 2 THEN 'hippo'

                 WHEN @num = 3 THEN 'trigger'

                 ELSE 'false'

            END

PRINT @name

5.    游标

原则上尽量不要使用游标

(更多信息：http://www.cnblogs.com/moss_tan_jun/archive/2011/11/26/2263988.html)

DECLARE @name NVARCHAR(30)

DECLARE mycursor CURSOR FOR SELECT FirstName FROM dbo.Persons

--打开游标

OPEN mycursor

WHILE @@FETCH_STATUS=0

BEGIN

    FETCH NEXT FROM mycursor INTO @name

    PRINT @name

END

CLOSE mycursor

--回收

DEALLOCATE mycursor

6.    触发器

触发器中的临时表：

　　Inserted

　　存放进行insert和update 操作后的数据

　　Deleted

存放进行delete 和update操作前的数据

Create trigger persion_update

    On dbo.Persons

    for Update

As

    declare @msg nvarchar(50)

    --@msg记录修改情况

    select @msg = N'姓名从“' + Deleted.FirstName + N'”修改为“' + Inserted.FirstName + '”' from Inserted,Deleted

    --插入日志表

    insert into TAB(Col2)values(@msg)

7.    存储过程

DROP PROCEDURE compute_add

CREATE PROCEDURE compute_add

    @p1 INT ,

    @p2 INT ,

    @p3 INT OUTPUT

AS

    BEGIN

        SET @p3=@p1+@p2

    END

 

    DECLARE @result INT

    EXEC compute_add 1,2,@result OUTPUT

    PRINT @result

8.    函数

标量函数

CREATE FUNCTION COMPUTE_ADD ( @p1 INT, @p2 INT )

RETURNS INT

AS

    BEGIN

        RETURN @p1+@p2

    END

调用方式

DECLARE @result INT

SET @result=dbo.COMPUTE_ADD(1,2)

PRINT @result

表值函数

CREATE FUNCTION mytable ( @id INT )

RETURNS TABLE

AS

RETURN

    ( SELECT    *

      FROM      dbo.Persons

      WHERE     id < @id

    )

调用方式

    select * from dbo.mytable(3)

表值函数：返回指定的列

CREATE FUNCTION mytable ( @id INT )

RETURNS @table TABLE

    (

      firstname NVARCHAR(50) ,

      lastname NVARCHAR(50)

    )

AS

    BEGIN

        INSERT  INTO @table

                SELECT  firstname ,

                        lastname

                FROM    dbo.Persons

                WHERE   id < @id

        RETURN

END

 

调用方式

    select * from dbo.mytable(3)

函数和存储过程区别

函数	存储过程
可以返回表变量	不能返回表变量
不能使用output参数	可以使用output参数
不能用临时表	可以执行对数据库表的操作，可以返回数据集
函数内部的操作不能影响到外部环境
不能通过select返回结果集
不能update，delete，数据库表(在函数内对带副作用的运算符 'UPDATE' 的使用无效。)
必须return 一个标量值或表变量或空	可以return一个标量值，也可以省略return