1 / 26

資料庫系統概論 CH10 預存程序

資料庫系統概論 CH10 預存程序. 鄧姚文. 大綱. 為何使用預存程序 預存程序的設計 在應用系統執行預存程序. 10-1 為何使用預存程序 使用預存程序的優點. 接受輸入參數，並以輸出參數的形式將一或多個數值予以傳回 可在資料庫中執行作業 執行完成後 傳回狀態值，以指示作業是成功或失敗 ( 及失敗原因 ) 可進行模組化的程式撰寫 可加快執行速度 可減少網路負荷. 10-1 為何使用預存程序 使用預存程序的優點. Stored Procedure 預存程序是此類程序的通稱 可分為系統預存 程序 觸發程序 Trigger 一般預存程序.

gyda
Download Presentation

資料庫系統概論 CH10 預存程序

An Image/Link below is provided (as is) to download presentation Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript

  1. 資料庫系統概論CH10 預存程序 鄧姚文

  2. 大綱 為何使用預存程序 預存程序的設計 在應用系統執行預存程序

  3. 10-1為何使用預存程序使用預存程序的優點 接受輸入參數，並以輸出參數的形式將一或多個數值予以傳回 可在資料庫中執行作業 執行完成後傳回狀態值，以指示作業是成功或失敗(及失敗原因) 可進行模組化的程式撰寫 可加快執行速度 可減少網路負荷

  4. 10-1為何使用預存程序使用預存程序的優點 • Stored Procedure • 預存程序是此類程序的通稱 • 可分為系統預存程序 • 觸發程序 Trigger • 一般預存程序

  5. 10-1為何使用預存程序建立預存程序 使用CREATE PROCEDURE

  6. 10-1為何使用預存程序撰寫程式的原則 • 以下不得用於預存程序中的任何位置 • CREATE DEFAULT、CREATE TRIGGER、CREATE PROCEDURE、CREATE VIEW 、 CREATE RULE • 可以建立其他資料庫物件 • 可以引用暫存資料表 • 建立本機暫存資料表後，該暫存資料表只為該預存程序而存在 • 被呼叫的預存程序可以存取第一個預存程序所建的所有物件 • 參數數量上限是2100個

  7. 10-1為何使用預存程序修改預存程序 選取欲更改的預存程序，按下右鍵及點選「修改」

  8. 10-1為何使用預存程序執行預存程序 使用EXEC陳述式

  9. 10-2 預存程序的相關設計使用參數 • 參數共分輸入、輸出及一個傳回碼 • 參數名稱前須加上@符號，有型態之分 • 預設值－程式10-1, pp.10-9 • 參數可以用 = 設定預設值 • 取得傳回值－程式10-2, pp. 10-2 • 以關鍵字 OUTPUT 設定『輸出』參數

  10. 10-2 預存程序的相關設計預存程序的編譯 • WITH RECOMPILE • 每次執行時均重新編譯 • 每次重新建立查詢計畫 • WITH ENCRYPTION • 為預存程序的內容予以加密 • 需要重新編譯的時機是若每次執行預存程序時指定的參數值差異很大

  11. 10-2 預存程序的相關設計使用資料指標 資料指標(Cursor) ，「指標」二字的意義是指向特定位置的標示，而特定位置就是記錄

  12. 10-2 預存程序的相關設計使用資料指標 • 由開啟至關閉 • 宣告一或多個變數，準備由指標含有的欄位獲得回傳值，每一欄位對應至一個變數。 • 以DECLARE CURSOR宣告資料指標名稱，並設定取出記錄的SELECT語句 • 以OPEN開啟資料指標 • 以FETCH NEXT或FETCH INTO提取各欄位的資料至各個變數 • 以迴圈逐一提取記錄及各欄資料 • 以CLOSE及DEALLOCATE關閉資料指標

  13. 10-2 預存程序的相關設計使用資料指標 DECLARE cursor_name[ LOCAL | GLOBAL ] [ FORWARD_ONLY | SCROLL ] [ STATIC | KEYSET | DYNAMIC | FAST_FORWARD ] [ READ_ONLY | SCROLL_LOCKS | OPTIMISTIC ] [ TYPE_WARNING ] FOR select_statement[ FOR UPDATE [ OF column_name [ ,...n ] ] ]

  14. 10-2 預存程序的相關設計使用資料指標 FETCH        [ [ NEXT | PRIOR | FIRST | LAST                 | ABSOLUTE { n |@nvar}                 | RELATIVE { n |@nvar}             ]             FROM         ] { { [ GLOBAL ] cursor_name } | @cursor_variable_name} [ INTO@variable_name[ ,...n ] ]

  15. 10-2 預存程序的相關設計使用資料指標 • @@FETCH_STATUS • =0 成功 • =-1 失敗，無法抓取資料，通常是資料指標已經耗盡 • =-2 資料遺失

  16. 10-2 預存程序的相關設計範例資料庫的預存程序

  17. 練習 重作 lab-ch09，將每一題的答案都包裝成 Stored Procedure 以 EXEC 執行 Stored Procedure，檢視輸入參數、輸出參數以及傳回值

  18. PIVOT 樞紐分析 語法 SELECT 列名 FROM 來源 PIVOT 值 FOR 欄位 IN (顯示名稱) AS PVT 範例：getCustomerAmount

  19. CREATEPROCEDURE[dbo].[getCustomerAmount] @CustomerCodeVARCHAR(20), @StartDateVARCHAR(10), @EndDateVARCHAR(10) AS SELECT*FROM ( SELECTTOP (100)PERCENT CONVERT(VARCHAR(4),YEAR(OrderDate))+'/'+CONVERT(VARCHAR(2),MONTH(OrderDate))ASYM, CS.CustCnName, SUM(R.WithTaxPriceTW)ASTotal, CS.CustSupCode FROMCustomerSupplierCSINNERJOINOrdersRONCS.CustSupCode=R.CustCode GROUPBYCS.CustCnName,CS.CustSupCode,OrderDate HAVING (CS.CustSupCodeLIKE@CustomerCode+'%')AND (R.OrderDateBETWEEN@StartDateAND@EndDate) ORDERBYCS.CustSupCode )ASx PIVOT(SUM(Total)FORYMIN([2009/1],[2009/2],[2009/3],[2009/4],[2009/5],[2009/6])) ASpvt;

  20. 10-3在應用系統執行預存程序Access 開啟Ch10\Ch10-3\Ch1031.mdb 使用ADODB的Command物件 執行預存程序－程式10-10

  21. 10-3在應用系統執行預存程序Visual Basic.NET 開啟Ch10\Ch10-3\Ch1032\Ch1302.sln 使用.NET的SqlCommand 物件 執行預存程序－程式10-11

More Related