SQL server 存储过程语法大全

时　间:2016-12-29 10:24:12
作　者:易勋   ID：35404  城市：上海
摘　要:SQLSERVER存儲過程的寫法格式規格
正　文:

在存儲過程中的格式規格：

Create PROCEDURE XXX

參數1，

參數2……………

As

Declare

參數1，

參數2……………

Set參數1的初始值

Set參數2的初始值…………

Begin trascation

   

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=''

 

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

       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 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
               
       -- 执行存储过程

Access软件网QQ交流群 (群号:54525238) 　　　　　　Access源码网店