SQLSERVER 存储过程 语法

*****************************************************

***  author：Susan

***  date: 2005 / 08 / 05

***  expliation:如何写存储过程的格式及例子，有游标的用法！

***  本版:SQL SERVER 版！

******************************************************/

在存储过程中的格式规格：

CREATE   PROCEDURE  XXX

/*

列举传入参数

1：名称，2：类型，包括长度

Eg:@strUNIT_CODE varCHAR(3)

*/

参数1，

参数2……………

As

/*

定义内部参数

1：名称，2：类型，包括长度

Eg:@strUNIT_CODE varCHAR(3)

*/

Declare

参数1，

参数2……………

/*

初始化内部参数

Eg:SET @strUNIT_CODE=’’

*/

Set参数1的初始值

Set参数2的初始值…………

/*

过程的主内容区

Trascation：这裡起到的作用是，如果他中间的任何一个执行错误，就全部执行都返回，这裡sql sever 7.0以前一定要写入，以后的就可以省略

Return：结束这支sp

*/

Begin  trascation

     /*

        1:可以取得需要的值以存在内部参数中

     Eg:SELECT @strUNIT_CODE=UNIT_CODE FROM UNIT WHERE …….

2:可以用取到的或传入的参数进行判断，来进行update,insert,delete 等等操作

eg: IF @strUNIT_CODE=’’

         BEGIN

     //具体的操作

End

Else

Begin

    //具体的操作

End

 3：有关游标的问题

     Eg:

        declare db cursor for       //声明一个游标(db為其名称)

        SELECT UNIT_NAME FROM UNIT WHERE LEFT(UNIT_CODE,2)=LEFT(@strTO,2)//记录集

        open db                 //打开游标

            fetch next from db into @strUNIT_NAME //将第一个值放入一个参数中

        while @@fetch_status = 0 ---存在本笔值向下循环

(0：顺利执行;-1：失败，或资料列超出结果集;-2：擷取的资料列已遗漏)

        BEGIN             ----开始循环

                        //个体操作

        End                ----结束循环

        Close db            ---关闭游标

deallocate db         //移除资料指标参考

*/

Commit  trascation

Return

 

下面是一个例子

CREATE   PROCEDURE  TEST_2

@strTO   VARCHAR ( 3 )

AS

DECLARE

  @strUNIT_NAME   VARCHAR ( 800 ),

  @strSQL   VARCHAR ( 8000 ),

  @Link   VARCHAR ( 1 ),

  @Link1   VARCHAR ( 1 )

 

SET   @strUNIT_NAME = ''

SET   @strSQL = ''

SET   @Link = ''

SET   @Link1 = ''

 

/*

处理update 的部分

EXEC TEST_2 '011'

EXEC TEST_2 ''

SELECT UNIT_NAME FROM UNIT WHERE UNIT_CODE='011'

*/

BEGIN   TRANSACTION                                        

          IF   @strTO <> ''

         BEGIN

                 UPDATE  UNIT  SET  UNIT_NAME = REPLACE (UNIT_NAME, ' * ' , '' )  WHERE  UNIT_CODE = @strTO

         END

              ELSE

         BEGIN

                 UPDATE  UNIT  SET  UNIT_NAME = UNIT_NAME + ' * '   WHERE  UNIT_CODE = ' 011 '

         END

/*

EXEC TEST_2 '011'

功能说明：本sp用於处理cursor问题

*/

         IF     @strTO <> ''

             BEGIN

                         declare  db  cursor   for                                                  -- 必需声明在查询的前面

                 SELECT  UNIT_NAME  FROM  UNIT  WHERE   LEFT (UNIT_CODE, 2 ) = LEFT ( @strTO , 2 ) -- -取到相关信息

             END

         ELSE

             BEGIN

                      declare  db  cursor   for                                                -- 必需声明在查询的前面

                      SELECT  UNIT_NAME  FROM  UNIT  WHERE   LEFT (UNIT_CODE, 2 ) = LEFT ( ' 011 ' , 2 ) -- -取到相关信息

             END

         open  db                                                     -- -开起取到的信息

         fetch   next   from  db  into   @strUNIT_NAME          -- -把第一笔放入@strUNIT_NAME中

         while   @@fetch_status   =   0                                             -- -表示存在本笔资料

         BEGIN                                                      -- --开始循环

                 set   @strSQL   = @strSQL + @Link1 + @Link +   @strUNIT_NAME   -- --设定保存的值

                 fetch   next   from  db  into   @strUNIT_NAME                         -- --进行下次循环

                 SET   @Link = CHAR ( 13 )  + CHAR ( 10 )

                 SET   @Link1 = ' , '

         END                                                          -- --结束循环

         close  db                                                    -- -关闭信息

         deallocate  db                                             -- -移除资料指标参考

SELECT   @strSQL

COMMIT   TRANSACTION

RETURN

如果循环insert的例子

DECLARE   @strLoginID   VARCHAR ( 16 )
BEGIN
declare  db  cursor   for
SELECT  LoginID  FROM  dbo.s_Users  WHERE   len (UnitCoding)  in ( 9 , 12 )
END
open  db
fetch   next   from  db  into   @strLoginID
while   @@fetch_status   =   0   BEGIN
insert   into  s_P_User
select   @strLoginID ,LevelID  from  s_P_User  where  LoginID  =   ' aa '
fetch   next   from  db  into   @strLoginID
END
close  db
deallocate  db

一、TRUNCATE

二、Select INTO 建表
    把一个表中的数据复制到另外一个表中。

三、Insert INTO Select

四、补充：临时表
    临时表存储在系统数据库tempdb中
    临时表会被系统隐式地丢弃

---------------------------------------------------------

五、存储过程(**)

    一、简介：
    
　　　存储过程（Stored Procedure）， 是一组为了完成特定功能的SQL 语句，集经编译后
    存储在数据库中，用户通过指定存储过程的名字并给出参数，如果该存储过程带有参数来执行

它，
    在SQL Server 的系列版本中，存储过程分为两类：系统提供的存储过程和用户自定义存储过程

。
    系统SP，主要存储master 数据库中，并以sp_为前缀并且系统存储过程主要是从系统表中获取
    信息，从而为系统管理员管理SQL Server。 用户自定义存储过程是由用户创建，并能完成
    某一特定功能，如：查询用户所需数据信息的存储过程。
    
    　　存储过程具有以下优点
    1.存储过程允许标准组件式编程(模块化设计)
    存储过程在被创建以后，可以在程序中被多次调用，而不必重新编写该存储过程的SQL语句，而

且数
    据库专业人员可随时对存储过程进行修改，但对应用程序源代码毫无影响。因为应用程序源代

码只包含存
    储过程的调用语句，从而极大地提高了程序的可移植性。
            
    2.存储过程能够实现快速的执行速度
　　　如果某一操作包含大量的Transaction-SQL 代码,，或分别被多次执行，那么存储过程要比批处理

的
    执行速度快很多，因为存储过程是预编译的，在首次运行一个存储过程时，查询优化器对其进

行分析优
    化，并给出最终被存在系统表中的执行计划，而批处理的Transaction-SQL 语句在每次运行时

都要进行
    编译和优化，因此速度相对要慢一些。
                
    3.存储过程能够减少网络流量
　　　对于同一个针对数据数据库对象的操作，如查询修改，如果这一操作所涉及到的Transaction-SQL
    语句被组织成一存储过程，那么当在客户计算机上调用该存储过程时，网络中传送的只是该调

用语句，否
    则将是多条SQL 语句从而大大增加了网络流量降低网络负载。
            
    4.存储过程可被作为一种安全机制来充分利用
　　　系统管理员通过，对执行某一存储过程的权限进行限制，从而能够实现对相应的数据访问权限的

限
    制。


    二、变量

    @I

    三、流程控制语句(if else | select case | while )
    Select ... CASE 实例
    DECLARE @iRet INT, @PKDisp VARCHAR(20)
    SET @iRet = '1'
    Select @iRet =
    CASE
        WHEN @PKDisp = '一' THEN 1
        WHEN @PKDisp = '二' THEN 2
        WHEN @PKDisp = '三' THEN 3
        WHEN @PKDisp = '四' THEN 4
        WHEN @PKDisp = '五' THEN 5
        ELSE 100
    END

    四、存储过程格式
        
    创建存储过程
    Create Proc dbo.存储过程名
    存储过程参数
    AS
    执行语句
    RETURN
    执行存储过程
    GO
*********************************************************/

--  变量的声明,sql里面声明变量时必须在变量前加@符号
     DECLARE   @I   INT

--  变量的赋值，变量赋值时变量前必须加set
     SET   @I   =   30

--  声明多个变量
     DECLARE   @s   varchar ( 10 ), @a   INT

--  Sql 里if语句
     IF  条件  BEGIN
        执行语句
     END
     ELSE   BEGIN
        执行语句
     END
            
     DECLARE   @d   INT
     set   @d   =   1

     IF   @d   =   1   BEGIN

     --  打印
         PRINT   ' 正确 '
     END
     ELSE   BEGIN
         PRINT   ' 错误 '
     END


--  Sql 里的多条件选择语句.
     DECLARE   @iRet   INT ,  @PKDisp   VARCHAR ( 20 )
     SET   @iRet   =   1
     Select   @iRet   =
     CASE
         WHEN   @PKDisp   =   ' 一 '   THEN   1
         WHEN   @PKDisp   =   ' 二 '   THEN   2
         WHEN   @PKDisp   =   ' 三 '   THEN   3
         WHEN   @PKDisp   =   ' 四 '   THEN   4
         WHEN   @PKDisp   =   ' 五 '   THEN   5
         ELSE   100
     END

--  循环语句
     WHILE  条件  BEGIN     
        执行语句
     END

     DECLARE   @i   INT
     SET   @i   =   1
     WHILE   @i < 1000000   BEGIN
         set   @i = @i + 1
     END
     --  打印
     PRINT   @i


--  TRUNCATE 删除表中的所有行，而不记录单个行删除操作，不能带条件

     /*
    TRUNCATE TABLE 在功能上与不带 Where 子句的 Delete 语句相同：二者均删除表中的全部行

。但 TRUNCATE TABLE 比 Delete 速度快，且使用的系统和事务日志资源少。
    Delete 语句每次删除一行，并在事务日志中为所删除的每行记录一项。TRUNCATE TABLE 通过

释放存储表数据所用的数据页来删除数据，并且只在事务日志中记录页的释放。
    TRUNCATE TABLE 删除表中的所有行，但表结构及其列、约束、索引等保持不变。新行标识所用

的计数值重置为该列的种子。如果想保留标识计数值，请改用 Delete。如果要删除表定义及其数据，请

使用 Drop TABLE 语句。
    对于由 FOREIGN KEY 约束引用的表，不能使用 TRUNCATE TABLE，而应使用不带 Where 子句的

Delete 语句。由于 TRUNCATE TABLE 不记录在日志中，所以它不能激活触发器。
    TRUNCATE TABLE 不能用于参与了索引视图的表。
    示例
        下例删除 authors 表中的所有数据。 */
        
         TRUNCATE   TABLE  authors
                

--  Select INTO 从一个查询的计算结果中创建一个新表。 数据并不返回给客户端，这一点和普通的
--  Select 不同。 新表的字段具有和 Select 的输出字段相关联（相同）的名字和数据类型。
        
         select   *   into  NewTable
             from  Uname


--  Insert INTO Select
         --  表ABC必须存在
         --  把表Uname里面的字段Username复制到表ABC
         Insert   INTO  ABC  Select  Username  FROM  Uname

--  创建临时表
         Create   TABLE  # temp (
            UID  int   identity ( 1 ,  1 )  PRIMARY   KEY ,
            UserName  varchar ( 16 ),
            Pwd  varchar ( 50 ),
            Age  smallint ,
            Sex  varchar ( 6 )
        )
         --  打开临时表
         Select   *   from  # temp

--  存储过程
         --  要创建存储过程的数据库
         Use  Test
         --  判断要创建的存储过程名是否存在
             if   Exists ( Select  name  From  sysobjects  Where  name = ' csp_AddInfo '   And

type = ' P ' )
             --  删除存储过程
             Drop   Procedure  dbo.csp_AddInfo
         Go
                
                
         --  创建存储过程
         Create   Proc  dbo.csp_AddInfo
         --  存储过程参数
         @UserName   varchar ( 16 ),
         @Pwd   varchar ( 50 ),
         @Age   smallint ,
         @Sex   varchar ( 6 )
         AS
         --  存储过程语句体
         insert   into  Uname (UserName,Pwd,Age,Sex)
             values  ( @UserName , @Pwd , @Age , @Sex )
         RETURN
         --  执行
         GO
                
         --  执行存储过程
         EXEC  csp_AddInfo  ' Junn.A ' , ' 123456 ' , 20 , ' 男 '

本文来自CSDN博客，转载请标明出处：http://blog.csdn.net/mx1029/archive/2007/07/06/1680910.aspx