1、 SQL Server 2005數據庫應用技術數據庫應用技術 第第0909章章 銷售管理數據庫中存銷售管理數據庫中存儲過程的應用儲過程的應用技能目標技能目標u理解存儲過程概念以及存儲過程的作用；理解存儲過程概念以及存儲過程的作用；u學會創建、刪除、修改存儲過程；學會創建、刪除、修改存儲過程；u學會根據實際需要設計銷售管理數據庫中的存儲過程。學會根據實際需要設計銷售管理數據庫中的存儲過程。 SQL Server 2005 SQL Server 2005數據庫應用技術數據庫應用技術 知識目標知識目標u理解存儲過程的作用；理解存儲過程的作用；u了解系統存儲過程和擴展存儲過程；了解系統存儲過程和擴展存

2、儲過程；u掌握存儲過程的基本類型；掌握存儲過程的基本類型；u掌握創建、刪除、修改和加密存儲過程；掌握創建、刪除、修改和加密存儲過程；u掌握執行各類存儲過程掌握執行各類存儲過程 SQL Server 2005 SQL Server 2005數據庫應用技術數據庫應用技術 存儲過程的概念存儲過程的概念 u存儲過程（存儲過程（Stored ProcedureStored Procedure）是一組完成特定功能的）是一組完成特定功能的Transact-SQLTransact-SQL語句集語句集，經，經編譯后存儲在數據庫編譯后存儲在數據庫中，用戶調中，用戶調用過程名和給出參數來調用它們用過程名和給出參數來

3、調用它們. .u特點：特點：u完成特定功能完成特定功能u多條語句組成的程序多條語句組成的程序u存放在數據庫中（服務器）存放在數據庫中（服務器）u由客戶機調用由客戶機調用u可以帶參數，也可以不帶參數可以帶參數，也可以不帶參數u可以有返回值，也可以沒有可以有返回值，也可以沒有 SQL Server 2005 SQL Server 2005數據庫應用技術數據庫應用技術 為什么要建立存儲過程？為什么要建立存儲過程？數據庫服務器數據庫服務器客戶機客戶機實現相同功能的實現相同功能的SQL語句集語句集客戶機客戶機實現相同功能的實現相同功能的SQL語句集語句集缺點：缺點：1. 相同的相同的SQL語句要重復書寫

4、語句要重復書寫 2. 傳輸網絡面臨較大壓力傳輸網絡面臨較大壓力 3. 執行效率低執行效率低 4. 存在安全隱患存在安全隱患 SQL Server 2005 SQL Server 2005數據庫應用技術數據庫應用技術 為什么要建立存儲過程？為什么要建立存儲過程？數據庫服務器數據庫服務器存儲過程存儲過程客戶機客戶機調用存儲過程實現特定功能調用存儲過程實現特定功能客戶機客戶機優點：優點：1. 直接調用特定存儲過程實現某種功能，不需要用戶自己書寫直接調用特定存儲過程實現某種功能，不需要用戶自己書寫SQL語句語句 2. 存儲過程存放在服務器上，不需網絡傳輸存儲過程存放在服務器上，不需網絡傳輸 3. 存儲

5、過程經過編譯和優化，執行效率高存儲過程經過編譯和優化，執行效率高 4. 安全性高安全性高調用存儲過程實現特定功能調用存儲過程實現特定功能 SQL Server 2005 SQL Server 2005數據庫應用技術數據庫應用技術 存儲過程的優點存儲過程的優點 u允許模塊化程序設計允許模塊化程序設計u存儲過程由有數據庫專長的技術人員編寫。可以無限次調用存儲過程由有數據庫專長的技術人員編寫。可以無限次調用u存儲過程獨立與程序源代碼，維護方便存儲過程獨立與程序源代碼，維護方便u執行速度快執行速度快 u存儲過程經過編譯存儲過程經過編譯u存儲過程經過優化存儲過程經過優化u批處理的批處理的SQL語句每次均

6、需要編譯和優化語句每次均需要編譯和優化u有效降低網絡流量有效降低網絡流量 u只需通過網絡發送一條調用存儲過程的語句，不需要傳遞大量只需通過網絡發送一條調用存儲過程的語句，不需要傳遞大量的的SQL語句代碼語句代碼u提高數據庫的安全性提高數據庫的安全性u存儲過程具有安全性和所有權連接存儲過程具有安全性和所有權連接u存儲過程可以附加安全證書存儲過程可以附加安全證書 SQL Server 2005 SQL Server 2005數據庫應用技術數據庫應用技術 存儲過程的分類存儲過程的分類u系統存儲過程系統存儲過程u用戶自定義存儲過程用戶自定義存儲過程u擴展存儲過程擴展存儲過程 SQL Server 20

7、05 SQL Server 2005數據庫應用技術數據庫應用技術 存儲過程的分類存儲過程的分類 ( (續續) )u系統存儲過程系統存儲過程u完成完成SQL Server2005的許的許多過來活動多過來活動u以以sp_開頭開頭u物理上存儲在數據庫的物理上存儲在數據庫的resource中，邏輯上存在在中，邏輯上存在在每個數據庫的每個數據庫的SYS框架中框架中 SQL Server 2005 SQL Server 2005數據庫應用技術數據庫應用技術 存儲過程的分類（續）存儲過程的分類（續）u用戶自定義存儲過程用戶自定義存儲過程 用戶自定義存儲過程是指封裝的由用戶創建、能完成特定用戶自定義存儲過程是

8、指封裝的由用戶創建、能完成特定功能的、可重用代碼的模塊或例程。功能的、可重用代碼的模塊或例程。u擴展存儲過程擴展存儲過程 擴展存儲過程是指使用編程語言（例如擴展存儲過程是指使用編程語言（例如 C）創建自己的外）創建自己的外部例程，是指部例程，是指 Microsoft SQL Server 的實例可以動態加載和的實例可以動態加載和運行的運行的 DLL。 SQL Server 2005 SQL Server 2005數據庫應用技術數據庫應用技術 存儲過程語法格式存儲過程語法格式 語法格式：語法格式： CREATE PROC | PROCEDURE 存儲過程名存儲過程名 參數名稱參數名稱 參數數據類

9、型參數數據類型 = 參數的默認值參數的默認值 OUTPUT ,.n WITH ENCRYPTION WITH RECOMPILE AS sql_statement 參數：參數： u參數名稱：存儲過程可以沒有參數。也可以聲明一個或多個參數，參數名稱參數名稱：存儲過程可以沒有參數。也可以聲明一個或多個參數，參數名稱必須必須作為第一個字符。參數后面帶作為第一個字符。參數后面帶OUTPUT，表示為輸出參數。，表示為輸出參數。uWITH ENCRYPTION：對存儲過程加密，其他用戶無法查看存儲過程的定義。：對存儲過程加密，其他用戶無法查看存儲過程的定義。uWITH RECOMPILE：每次執行該存儲過

10、程都重新進行編譯。：每次執行該存儲過程都重新進行編譯。usql_statemen：該存儲過程中定義的編程語句。：該存儲過程中定義的編程語句。 SQL Server 2005 SQL Server 2005數據庫應用技術數據庫應用技術 存儲過程的組成存儲過程的組成 存儲過程的定義中包含如下的兩個主要組成部分。存儲過程的定義中包含如下的兩個主要組成部分。（1）過程名稱及其參數的說明：包括所有的輸入參數以及傳給調用者）過程名稱及其參數的說明：包括所有的輸入參數以及傳給調用者的輸出參數。的輸出參數。（2）過程的主體：也稱為過程體，針對數據庫的操作語句）過程的主體：也稱為過程體，針對數據庫的操作語句（T

11、ransact-SQL 語句），包括調用其它存儲過程的語句。語句），包括調用其它存儲過程的語句。 SQL Server 2005 SQL Server 2005數據庫應用技術數據庫應用技術 不帶參數的存儲過程不帶參數的存儲過程 u創建不帶參數的存儲過程創建不帶參數的存儲過程 語法格式：語法格式：CREATE PROC | PROCEDURE 存儲過程名存儲過程名 WITH ENCRYPTION WITH RECOMPILE AS sql_statement SQL Server 2005 SQL Server 2005數據庫應用技術數據庫應用技術 不帶參數的存儲過程不帶參數的存儲過程u【例例9

12、.19.1】創建一個名為創建一個名為Cu_informationCu_information的存儲過程，用的存儲過程，用于查詢客戶的信息。于查詢客戶的信息。 use studentuse student CREATE PROCEDURE Cu_information CREATE PROCEDURE Cu_information AS AS SELECT SELECT * * FROM Customer FROM Customer SQL Server 2005 SQL Server 2005數據庫應用技術數據庫應用技術 不帶參數的存儲過程（續）不帶參數的存儲過程（續）u 執行不帶參數的存儲過

13、程執行不帶參數的存儲過程 語法結構如下語法結構如下: EXEC 存儲過程名存儲過程名【例例9.3】執行創建的執行創建的Cu_Information存儲過程。存儲過程。 EXEC Cu_Information SQL Server 2005 SQL Server 2005數據庫應用技術數據庫應用技術 創建存儲過程的步驟創建存儲過程的步驟u實現過程體的功能實現過程體的功能u構建實現特定功能的構建實現特定功能的SQL語句語句u創建存儲過程創建存儲過程CREATE PROCEDURE ASSQL語句集語句集u驗證準確性驗證準確性 EXEC EXEC SQL Server 2005 SQL Server

14、 2005數據庫應用技術數據庫應用技術 帶輸入參數的存儲過程帶輸入參數的存儲過程 u創建帶輸入參數的存儲過程創建帶輸入參數的存儲過程CREATE PROC | PROCEDURE 存儲過程名存儲過程名 參數名稱參數名稱 參數數據類型參數數據類型 = 參數的默認值參數的默認值 ,.n WITH ENCRYPTION WITH RECOMPILE AS sql_statement SQL Server 2005 SQL Server 2005數據庫應用技術數據庫應用技術 帶輸入參數的存儲過程帶輸入參數的存儲過程u【例例9.49.4】創建一個存儲過程，實現根據訂單號獲取該訂創建一個存儲過程，實現根據

15、訂單號獲取該訂單的信息的功能。單的信息的功能。 CREATE PROCEDURE OrderDetailOrderID INTASSELECT * FROM Sell_Order WHERE SellOrderId=OrderID SQL Server 2005 SQL Server 2005數據庫應用技術數據庫應用技術 帶輸入參數的存儲過程帶輸入參數的存儲過程u【例例9.69.6】創建名為創建名為listEmployeelistEmployee的存儲過程，其功能為：的存儲過程，其功能為：在員工表在員工表employeeemployee中查找符合性別和超過指定工資條件的中查找符合性別和超過指定

16、工資條件的員工詳細信息。員工詳細信息。CREATE PROCEDURE listEmployeesex varchar(2),salary moneyASSELECT *FROM employee WHERE sex=sex and salarysalary SQL Server 2005 SQL Server 2005數據庫應用技術數據庫應用技術 帶輸入參數的存儲過程帶輸入參數的存儲過程 （續）（續）u執行輸入參數的存儲過程兩種方法：執行輸入參數的存儲過程兩種方法： u使用參數名傳遞參數值使用參數名傳遞參數值EXEC 存儲過程名存儲過程名 參數名參數名=參數值參數值 DEFAULT ，nu按

17、位置傳遞參數值按位置傳遞參數值EXEC 存儲過程名存儲過程名 參數值參數值1，參數值，參數值2， SQL Server 2005 SQL Server 2005數據庫應用技術數據庫應用技術 帶輸入參數的存儲過程帶輸入參數的存儲過程 （續）（續）u【例例9.79.7】使用使用【例例9.59.5】中創建的存儲過程中創建的存儲過程customer_ordercustomer_order，獲取，獲取 “ “三川實業有限公司三川實業有限公司”的信息，的信息，包括聯系人姓名、聯系方式以及該公司訂購產品的明細表。包括聯系人姓名、聯系方式以及該公司訂購產品的明細表。代碼如下：代碼如下：EXEC custome

18、r_ordercustomername=三川實業有限公司三川實業有限公司 SQL Server 2005 SQL Server 2005數據庫應用技術數據庫應用技術 帶輸入參數的存儲過程帶輸入參數的存儲過程 （續）（續）u【例例9.89.8】利用存儲過程利用存儲過程listEmployeelistEmployee，查找工資超過，查找工資超過40004000元的的男員工和工資超過元的的男員工和工資超過30003000元女員工的詳細信息。元女員工的詳細信息。代碼如下：代碼如下：EXEC listEmployee sex=男男,salary=4000EXEC listEmployee salary=

19、3500,sex=女女 SQL Server 2005 SQL Server 2005數據庫應用技術數據庫應用技術 帶輸入參數的存儲過程帶輸入參數的存儲過程 （續）（續）u【例例9.99.9】按位置傳遞執行存儲過程按位置傳遞執行存儲過程listEmployeelistEmployee，查找，查找工資超過工資超過40004000元的的男員工和工資超過元的的男員工和工資超過30003000元女員工的詳元女員工的詳細信息。細信息。代碼如下：代碼如下：EXEC listEmployee 男男, 4000EXEC listEmployee 女女, 3500 SQL Server 2005 SQL Ser

20、ver 2005數據庫應用技術數據庫應用技術 帶輸入參數的存儲過程帶輸入參數的存儲過程 （續）（續）u【例例9.119.11】利用利用Name_EmployeeName_Employee存儲過程查詢所有員工信存儲過程查詢所有員工信息和姓王的員工信息息和姓王的員工信息uEXEC Name_Employee 查詢所有員工信息查詢所有員工信息uEXEC Name_Employee 王王% 查詢姓王的員工信息查詢姓王的員工信息 SQL Server 2005 SQL Server 2005數據庫應用技術數據庫應用技術 帶輸出參數的存儲過程帶輸出參數的存儲過程u【例例9.129.12】 創建帶返回參數的

21、存儲過程求兩個整數的和。創建帶返回參數的存儲過程求兩個整數的和。代碼如下：代碼如下：CREATE PROCEDURE PRO_SUMN1 INT,N2 INT,RESULT INT OUTPUTASSET RESULT = N1 + N2 SQL Server 2005 SQL Server 2005數據庫應用技術數據庫應用技術 帶輸出參數的存儲過程帶輸出參數的存儲過程u【例例9.139.13】執行執行【例例9.119.11】創建的創建的PRO_SUMPRO_SUM存儲過程。存儲過程。代碼如下：代碼如下：DECLARE ANSWER INTEXEC PRO_SUM 20,69, ANSWER

22、OUTPUTselect ANSWER 結果結果 SQL Server 2005 SQL Server 2005數據庫應用技術數據庫應用技術 查看存儲過程查看存儲過程 usp_helpsp_help用于顯示存儲過程的參數及其數據類型，語法格用于顯示存儲過程的參數及其數據類型，語法格式如下：式如下：sp_help objname= 存儲過程名存儲過程名 usp_helptextsp_helptext用于顯示存儲過程的源代碼，語法格式如下：用于顯示存儲過程的源代碼，語法格式如下：sp_helptext objname=存儲過程存儲過程 SQL Server 2005 SQL Server 2005

23、數據庫應用技術數據庫應用技術 查看存儲過程查看存儲過程u【例例9.149.14】查看查看customer_ordercustomer_order存儲過程的參數和數據類存儲過程的參數和數據類型。型。代碼如下：代碼如下：USE CompanySalesGOsp_help customer_order SQL Server 2005 SQL Server 2005數據庫應用技術數據庫應用技術 查看存儲過程查看存儲過程u【例例9.159.15】查看查看customer_ordercustomer_order存儲過程的源代碼。存儲過程的源代碼。代碼如下：代碼如下：USE CompanySalesGOsp

24、_helptext customer_order SQL Server 2005 SQL Server 2005數據庫應用技術數據庫應用技術 刪除用戶存儲過程刪除用戶存儲過程 u刪除用戶存儲過程可以使用刪除用戶存儲過程可以使用DROPDROP命令，語法格式如下：命令，語法格式如下：DROP PROC | PROCEDURE 存儲過程名存儲過程名 ,.n SQL Server 2005 SQL Server 2005數據庫應用技術數據庫應用技術 刪除用戶存儲過程刪除用戶存儲過程u【例例9.169.16】刪除刪除PRO_SUMPRO_SUM存儲過程。存儲過程。代碼如下：代碼如下：USE Compa

25、nySalesGODROP PROCEDURE PRO_SUM SQL Server 2005 SQL Server 2005數據庫應用技術數據庫應用技術 修改存儲過程修改存儲過程 u ALTER PROCEDURE ALTER PROCEDURE語句的語法格式如下：語句的語法格式如下： ALTER PROC | PROCEDURE 存儲過程名存儲過程名 參數名稱參數名稱 參數數據類型參數數據類型 = 參數的默認值參數的默認值 OUTPUT ,.n WITH ENCRYPTION WITH RECOMPILE AS sql_statement SQL Server 2005 SQL Serve

26、r 2005數據庫應用技術數據庫應用技術 系統存儲過程系統存儲過程 常用系統存儲過程：常用系統存儲過程：usp_tablessp_tables：返回可在當前環境中查詢的對象列表。這代表可：返回可在當前環境中查詢的對象列表。這代表可在在 FROM FROM 子句中出現的任何對象。子句中出現的任何對象。usp_stored_proceduressp_stored_procedures：返回當前環境中的存儲過程列表。：返回當前環境中的存儲過程列表。usp_renamesp_rename：在當前數據庫中更改用戶創建對象的名稱。此對：在當前數據庫中更改用戶創建對象的名稱。此對象可以是表、索引、列、別名數

27、據類型。象可以是表、索引、列、別名數據類型。usp_renamedbsp_renamedb：更改數據庫的名稱。：更改數據庫的名稱。 SQL Server 2005 SQL Server 2005數據庫應用技術數據庫應用技術 系統存儲過程系統存儲過程 usp_helpsp_help：報告有關數據庫對象（：報告有關數據庫對象（sys.sysobjects sys.sysobjects 兼容視圖中列出的所有兼容視圖中列出的所有對象）、用戶定義數據類型或對象）、用戶定義數據類型或 SQL Server 2005 SQL Server 2005 提供的數據類型的信息。提供的數據類型的信息。usp_hel

28、ptextsp_helptext：是用戶定義規則的定義、默認值、未加密的：是用戶定義規則的定義、默認值、未加密的 Transact-SQL Transact-SQL 存儲過程、用戶定義存儲過程、用戶定義 Transact-SQL Transact-SQL 函數、觸發器、計算列、函數、觸發器、計算列、CHECK CHECK 約束、約束、視圖或系統對象（如系統存儲過程）。視圖或系統對象（如系統存儲過程）。usp_whosp_who：提供有關：提供有關 Microsoft SQL Server Database Engine Microsoft SQL Server Database Engine 實例中的當實例中的當前用戶和進程