SQL Server存儲(chǔ)過(guò)程

理論知識(shí)：開(kāi)始

一、TRUNCATE
    快速地從一堆表中刪除所有行。它和在每個(gè)表上進(jìn)行無(wú)條件的 DELETE 有同樣的效果，不過(guò)因?yàn)樗蛔霰頀呙?，因而快得多?在大表上最有用。
 
二、Select INTO 建表
    把一個(gè)表中的數(shù)據(jù)復(fù)制到另外一個(gè)表中。

三、Insert INTO Select

四、補(bǔ)充：臨時(shí)表
    臨時(shí)表存儲(chǔ)在系統(tǒng)數(shù)據(jù)庫(kù)tempdb中
    臨時(shí)表會(huì)被系統(tǒng)隱式地丟棄

五、存儲(chǔ)過(guò)程(**)

    一、簡(jiǎn)介：
   
    存儲(chǔ)過(guò)程（Stored Procedure）， 是一組為了完成特定功能的SQL 語(yǔ)句，集經(jīng)編譯后存儲(chǔ)在數(shù)據(jù)庫(kù)中，用戶通過(guò)指定存儲(chǔ)過(guò)程的名字并給出參數(shù)，如果該存儲(chǔ)過(guò)程帶有參數(shù)來(lái)執(zhí)行它，
    在SQL Server 的系列版本中，存儲(chǔ)過(guò)程分為兩類：系統(tǒng)提供的存儲(chǔ)過(guò)程和用戶自定義存儲(chǔ)過(guò)程。
    系統(tǒng)SP，主要存儲(chǔ) master 數(shù)據(jù)庫(kù)中，并以sp_為前綴并且系統(tǒng)存儲(chǔ)過(guò)程主要是從系統(tǒng)表中獲取信息，從而為系統(tǒng)管理員管理SQL Server。 用戶自定義存儲(chǔ)過(guò)程是由用戶創(chuàng)建，并能完成某一特定功能，如：查詢用戶所需數(shù)據(jù)信息的存儲(chǔ)過(guò)程。
   
    存儲(chǔ)過(guò)程具有以下優(yōu)點(diǎn):
 
    1.存儲(chǔ)過(guò)程允許標(biāo)準(zhǔn)組件式編程(模塊化設(shè)計(jì))
　　　存儲(chǔ)過(guò)程在被創(chuàng)建以后，可以在程序中被多次調(diào)用，而不必重新編寫(xiě)該存儲(chǔ)過(guò)程的SQL語(yǔ)句，而且數(shù)據(jù)庫(kù)專業(yè)人員可隨時(shí)對(duì)存儲(chǔ)過(guò)程進(jìn)行修改，但對(duì)應(yīng)用程序源代碼毫無(wú)影響。因?yàn)閼?yīng)用程序源代碼只包含存儲(chǔ)過(guò)程的調(diào)用語(yǔ)句，從而極大地提高了程序的可移植性。
           
    2.存儲(chǔ)過(guò)程能夠?qū)崿F(xiàn)快速的執(zhí)行速度
　　　如果某一操作包含大量的Transaction-SQL 代碼,，或分別被多次執(zhí)行，那么存儲(chǔ)過(guò)程要比批處理的執(zhí)行速度快很多，因?yàn)榇鎯?chǔ)過(guò)程是預(yù)編譯的，在首次運(yùn)行一個(gè)存儲(chǔ)過(guò)程時(shí)，查詢優(yōu)化器對(duì)其進(jìn)行分析優(yōu)化，并給出最終被存在系統(tǒng)表中的執(zhí)行計(jì)劃，而批處理的Transaction-SQL 語(yǔ)句在每次運(yùn)行時(shí)都要進(jìn)行編譯和優(yōu)化，因此速度相對(duì)要慢一些。
               
    3.存儲(chǔ)過(guò)程能夠減少網(wǎng)絡(luò)流量
　　　對(duì)于同一個(gè)針對(duì)數(shù)據(jù)數(shù)據(jù)庫(kù)對(duì)象的操作，如查詢修改，如果這一操作所涉及到的 Transaction-SQL語(yǔ)句被組織成一存儲(chǔ)過(guò)程，那么當(dāng)在客戶計(jì)算機(jī)上調(diào)用該存儲(chǔ)過(guò)程時(shí)，網(wǎng)絡(luò)中傳送的只是該調(diào)用語(yǔ)句，否則將是多條SQL 語(yǔ)句從而大大增加了網(wǎng)絡(luò)流量降低網(wǎng)絡(luò)負(fù)載。
           
    4.存儲(chǔ)過(guò)程可被作為一種安全機(jī)制來(lái)充分利用系統(tǒng)管理員通過(guò)，對(duì)執(zhí)行某一存儲(chǔ)過(guò)程的權(quán)限進(jìn)行限制，從而能夠?qū)崿F(xiàn)對(duì)相應(yīng)的數(shù)據(jù)訪問(wèn)權(quán)限的限制。

    二、變量

    @I

    三、流程控制語(yǔ)句(if else | select case | while )
    Select ... CASE 實(shí)例
    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

    四、存儲(chǔ)過(guò)程格式
       
    創(chuàng)建存儲(chǔ)過(guò)程
    Create Proc dbo.存儲(chǔ)過(guò)程名
    存儲(chǔ)過(guò)程參數(shù)
    AS
    執(zhí)行語(yǔ)句
    RETURN
    執(zhí)行存儲(chǔ)過(guò)程
    GO
理論知識(shí)：結(jié)束

語(yǔ)法示例：開(kāi)始

-- 變量的聲明,sql里面聲明變量時(shí)必須在變量前加@符號(hào)
    DECLARE @I INT

-- 變量的賦值，變量賦值時(shí)變量前必須加set
    SET @I = 30

-- 聲明多個(gè)變量
    DECLARE @s varchar(10),@a INT

-- Sql 里if語(yǔ)句
    IF 條件 BEGIN
        執(zhí)行語(yǔ)句
    END
    ELSE BEGIN
        執(zhí)行語(yǔ)句
    END
           
    DECLARE @d INT
    set @d = 1

    IF @d = 1 BEGIN

    -- 打印
        PRINT '正確'
    END
    ELSE BEGIN
        PRINT '錯(cuò)誤'
    END

-- Sql 里的多條件選擇語(yǔ)句.
    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

-- 循環(huán)語(yǔ)句
    WHILE 條件 BEGIN   
        執(zhí)行語(yǔ)句
    END

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

語(yǔ)法示例：結(jié)束

相關(guān)說(shuō)明：開(kāi)始

 -- TRUNCATE 刪除表中的所有行，而不記錄單個(gè)行刪除操作，不能帶條件

    TRUNCATE TABLE 在功能上與不帶 Where 子句的 Delete 語(yǔ)句相同：二者均刪除表中的全部行。但 TRUNCATE TABLE 比 Delete 速度快，且使用的系統(tǒng)和事務(wù)日志資源少。
    Delete 語(yǔ)句每次刪除一行，并在事務(wù)日志中為所刪除的每行記錄一項(xiàng)。TRUNCATE TABLE 通過(guò)釋放存儲(chǔ)表數(shù)據(jù)所用的數(shù)據(jù)頁(yè)來(lái)刪除數(shù)據(jù)，并且只在事務(wù)日志中記錄頁(yè)的釋放。
    TRUNCATE TABLE 刪除表中的所有行，但表結(jié)構(gòu)及其列、約束、索引等保持不變。新行標(biāo)識(shí)所用的計(jì)數(shù)值重置為該列的種子。如果想保留標(biāo)識(shí)計(jì)數(shù)值，請(qǐng)改用 Delete。如果要?jiǎng)h除表定義及其數(shù)據(jù)，請(qǐng)使用 Drop TABLE 語(yǔ)句。
    對(duì)于由 FOREIGN KEY 約束引用的表，不能使用 TRUNCATE TABLE，而應(yīng)使用不帶 Where 子句的Delete 語(yǔ)句。由于 TRUNCATE TABLE 不記錄在日志中，所以它不能激活觸發(fā)器。
    TRUNCATE TABLE 不能用于參與了索引視圖的表。
    示例
        下例刪除 authors 表中的所有數(shù)據(jù)。
       
        TRUNCATE TABLE authors
               

-- Select INTO 從一個(gè)查詢的計(jì)算結(jié)果中創(chuàng)建一個(gè)新表。 數(shù)據(jù)并不返回給客戶端，這一點(diǎn)和普通的
-- Select 不同。 新表的字段具有和 Select 的輸出字段相關(guān)聯(lián)（相同）的名字和數(shù)據(jù)類型。
       
        select * into NewTable
            from Uname

-- Insert INTO Select
        -- 表ABC必須存在
        -- 把表Uname里面的字段Username復(fù)制到表ABC
        Insert INTO ABC Select Username FROM Uname

-- 創(chuàng)建臨時(shí)表
        Create TABLE #temp(
            UID int identity(1, 1) PRIMARY KEY,
            UserName varchar(16),
            Pwd varchar(50),
            Age smallint,
            Sex varchar(6)
        )
        -- 打開(kāi)臨時(shí)表
        Select * from #temp

-- 存儲(chǔ)過(guò)程
        -- 要?jiǎng)?chuàng)建存儲(chǔ)過(guò)程的數(shù)據(jù)庫(kù)
        Use Test
        -- 判斷要?jiǎng)?chuàng)建的存儲(chǔ)過(guò)程名是否存在
            if Exists(Select name From sysobjects Where name='csp_AddInfo' And

type='P')
            -- 刪除存儲(chǔ)過(guò)程
            Drop Procedure dbo.csp_AddInfo
        Go
               
               
        -- 創(chuàng)建存儲(chǔ)過(guò)程
        Create Proc dbo.csp_AddInfo
        -- 存儲(chǔ)過(guò)程參數(shù)
        @UserName varchar(16),
        @Pwd varchar(50),
        @Age smallint,
        @Sex varchar(6)
        AS
        -- 存儲(chǔ)過(guò)程語(yǔ)句體
        insert into Uname (UserName,Pwd,Age,Sex)
            values (@UserName,@Pwd,@Age,@Sex)
        RETURN
        -- 執(zhí)行
        GO
               
        -- 執(zhí)行存儲(chǔ)過(guò)程
        EXEC csp_AddInfo 'Junn.A','123456',20,'男'