SQLSERVER 存储过程 语法

 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 //具體的操作EndElseBegin //具體的操作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 trascationReturn

 

下面是一個例子

CREATE PROCEDURE TEST_2@strTO VARCHAR(3)ASDECLARE @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 @strSQLCOMMIT TRANSACTIONRETURN

 

如果循环insert的例子

 

DECLARE @strLoginID VARCHAR(16)BEGINdeclare db cursor forSELECT LoginID FROM dbo.s_Users WHERE len(UnitCoding) in(9,12)ENDopen dbfetch next from db into @strLoginIDwhile @@fetch_status = 0 BEGINinsert into s_P_Userselect @strLoginID,LevelID from s_P_User where LoginID = 'aa'fetch next from db into @strLoginIDENDclose dbdeallocate 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' Andtype='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,'男'