sql預存程序及應用

一、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,'男'

來源：http://bokea.cn/article.asp?id=129