銷售管理數(shù)據(jù)庫(kù)中存儲(chǔ)過(guò)程的運(yùn)用

1、第09章 銷售管理數(shù)據(jù)庫(kù)中存儲(chǔ)過(guò)程的運(yùn)用 SQL Server 2005數(shù)據(jù)庫(kù)運(yùn)用技術(shù) 技藝目的了解存儲(chǔ)過(guò)程概念以及存儲(chǔ)過(guò)程的作用；學(xué)會(huì)創(chuàng)建、刪除、修正存儲(chǔ)過(guò)程；學(xué)會(huì)根據(jù)實(shí)踐需求設(shè)計(jì)銷售管理數(shù)據(jù)庫(kù)中的存儲(chǔ)過(guò)程。 SQL Server 2005數(shù)據(jù)庫(kù)運(yùn)用技術(shù) 知識(shí)目的了解存儲(chǔ)過(guò)程的作用；了解系統(tǒng)存儲(chǔ)過(guò)程和擴(kuò)展存儲(chǔ)過(guò)程；掌握存儲(chǔ)過(guò)程的根本類型；掌握創(chuàng)建、刪除、修正和加密存儲(chǔ)過(guò)程；掌握?qǐng)?zhí)行各類存儲(chǔ)過(guò)程 SQL Server 2005數(shù)據(jù)庫(kù)運(yùn)用技術(shù) 存儲(chǔ)過(guò)程的概念 存儲(chǔ)過(guò)程Stored Procedure是一組完成特定功能的Transact-SQL語(yǔ)句集，經(jīng)編譯后存儲(chǔ)在數(shù)據(jù)庫(kù)中，用戶調(diào)用過(guò)程名和給出參

2、數(shù)來(lái)調(diào)用它們.特點(diǎn)：完成特定功能多條語(yǔ)句組成的程序存放在數(shù)據(jù)庫(kù)中效力器由客戶機(jī)調(diào)用可以帶參數(shù)，也可以不帶參數(shù)可以有前往值，也可以沒(méi)有 SQL Server 2005數(shù)據(jù)庫(kù)運(yùn)用技術(shù) 為什么要建立存儲(chǔ)過(guò)程？數(shù)據(jù)庫(kù)效力器客戶機(jī)實(shí)現(xiàn)一樣功能的SQL語(yǔ)句集客戶機(jī)實(shí)現(xiàn)一樣功能的SQL語(yǔ)句集缺陷：1. 一樣的SQL語(yǔ)句要反復(fù)書寫 2. 傳輸網(wǎng)絡(luò)面臨較大壓力 3. 執(zhí)行效率低 4. 存在平安隱患 SQL Server 2005數(shù)據(jù)庫(kù)運(yùn)用技術(shù) 為什么要建立存儲(chǔ)過(guò)程？數(shù)據(jù)庫(kù)效力器存儲(chǔ)過(guò)程客戶機(jī)調(diào)用存儲(chǔ)過(guò)程實(shí)現(xiàn)特定功能客戶機(jī)優(yōu)點(diǎn)：1. 直接調(diào)用特定存儲(chǔ)過(guò)程實(shí)現(xiàn)某種功能，不需求用戶本人書寫SQL語(yǔ)句 2. 存儲(chǔ)過(guò)程存

3、放在效力器上，不需網(wǎng)絡(luò)傳輸 3. 存儲(chǔ)過(guò)程經(jīng)過(guò)編譯和優(yōu)化，執(zhí)行效率高 4. 平安性高調(diào)用存儲(chǔ)過(guò)程實(shí)現(xiàn)特定功能 SQL Server 2005數(shù)據(jù)庫(kù)運(yùn)用技術(shù) 存儲(chǔ)過(guò)程的優(yōu)點(diǎn) 允許模塊化程序設(shè)計(jì)存儲(chǔ)過(guò)程由有數(shù)據(jù)庫(kù)專長(zhǎng)的技術(shù)人員編寫?？梢詿o(wú)限次調(diào)用存儲(chǔ)過(guò)程獨(dú)立與程序源代碼，維護(hù)方便執(zhí)行速度快 存儲(chǔ)過(guò)程經(jīng)過(guò)編譯存儲(chǔ)過(guò)程經(jīng)過(guò)優(yōu)化批處置的SQL語(yǔ)句每次均需求編譯和優(yōu)化有效降低網(wǎng)絡(luò)流量 只需經(jīng)過(guò)網(wǎng)絡(luò)發(fā)送一條調(diào)用存儲(chǔ)過(guò)程的語(yǔ)句，不需求傳送大量的SQL語(yǔ)句代碼提高數(shù)據(jù)庫(kù)的平安性存儲(chǔ)過(guò)程具有平安性和一切權(quán)銜接存儲(chǔ)過(guò)程可以附加平安證書 SQL Server 2005數(shù)據(jù)庫(kù)運(yùn)用技術(shù) 存儲(chǔ)過(guò)程的分類系統(tǒng)存儲(chǔ)過(guò)程用戶自定

4、義存儲(chǔ)過(guò)程擴(kuò)展存儲(chǔ)過(guò)程 SQL Server 2005數(shù)據(jù)庫(kù)運(yùn)用技術(shù) 存儲(chǔ)過(guò)程的分類 (續(xù))系統(tǒng)存儲(chǔ)過(guò)程完成SQL Server2005的許多過(guò)來(lái)活動(dòng)以sp_開頭物理上存儲(chǔ)在數(shù)據(jù)庫(kù)的resource中，邏輯上存在在每個(gè)數(shù)據(jù)庫(kù)的SYS框架中 SQL Server 2005數(shù)據(jù)庫(kù)運(yùn)用技術(shù) 存儲(chǔ)過(guò)程的分類續(xù)用戶自定義存儲(chǔ)過(guò)程 用戶自定義存儲(chǔ)過(guò)程是指封裝的由用戶創(chuàng)建、能完成特定功能的、可重用代碼的模塊或例程。擴(kuò)展存儲(chǔ)過(guò)程 擴(kuò)展存儲(chǔ)過(guò)程是指運(yùn)用編程言語(yǔ)例如 C創(chuàng)建本人的外部例程，是指 Microsoft SQL Server 的實(shí)例可以動(dòng)態(tài)加載和運(yùn)轉(zhuǎn)的 DLL。 SQL Server 2005數(shù)據(jù)庫(kù)運(yùn)用

5、技術(shù) 存儲(chǔ)過(guò)程語(yǔ)法格式 語(yǔ)法格式： CREATE PROC | PROCEDURE 存儲(chǔ)過(guò)程名 參數(shù)稱號(hào) 參數(shù)數(shù)據(jù)類型 = 參數(shù)的默許值 OUTPUT ,.n WITH ENCRYPTION WITH RECOMPILE AS sql_statement 參數(shù)： 參數(shù)稱號(hào)：存儲(chǔ)過(guò)程可以沒(méi)有參數(shù)。也可以聲明一個(gè)或多個(gè)參數(shù)，參數(shù)稱號(hào)必需作為第一個(gè)字符。參數(shù)后面帶OUTPUT，表示為輸出參數(shù)。WITH ENCRYPTION：對(duì)存儲(chǔ)過(guò)程加密，其他用戶無(wú)法查看存儲(chǔ)過(guò)程的定義。WITH RECOMPILE：每次執(zhí)行該存儲(chǔ)過(guò)程都重新進(jìn)展編譯。sql_statemen：該存儲(chǔ)過(guò)程中定義的編程語(yǔ)句。 SQL S

6、erver 2005數(shù)據(jù)庫(kù)運(yùn)用技術(shù) 存儲(chǔ)過(guò)程的組成 存儲(chǔ)過(guò)程的定義中包含如下的兩個(gè)主要組成部分。1過(guò)程稱號(hào)及其參數(shù)的闡明：包括一切的輸入?yún)?shù)以及傳給調(diào)用者的輸出參數(shù)。2過(guò)程的主體：也稱為過(guò)程體，針對(duì)數(shù)據(jù)庫(kù)的操作語(yǔ)句Transact-SQL 語(yǔ)句，包括調(diào)用其它存儲(chǔ)過(guò)程的語(yǔ)句。 SQL Server 2005數(shù)據(jù)庫(kù)運(yùn)用技術(shù) 不帶參數(shù)的存儲(chǔ)過(guò)程 創(chuàng)建不帶參數(shù)的存儲(chǔ)過(guò)程 語(yǔ)法格式：CREATE PROC | PROCEDURE 存儲(chǔ)過(guò)程名 WITH ENCRYPTION WITH RECOMPILE AS sql_statement SQL Server 2005數(shù)據(jù)庫(kù)運(yùn)用技術(shù) 不帶參數(shù)的存儲(chǔ)過(guò)程【例

7、9.1】創(chuàng)建一個(gè)名為Cu_information的存儲(chǔ)過(guò)程，用于查詢客戶的信息。 use student CREATE PROCEDURE Cu_information AS SELECT * FROM Customer SQL Server 2005數(shù)據(jù)庫(kù)運(yùn)用技術(shù) 不帶參數(shù)的存儲(chǔ)過(guò)程續(xù) 執(zhí)行不帶參數(shù)的存儲(chǔ)過(guò)程 語(yǔ)法構(gòu)造如下: EXEC 存儲(chǔ)過(guò)程名【例9.3】執(zhí)行創(chuàng)建的Cu_Information存儲(chǔ)過(guò)程。 EXEC Cu_Information SQL Server 2005數(shù)據(jù)庫(kù)運(yùn)用技術(shù) 創(chuàng)建存儲(chǔ)過(guò)程的步驟實(shí)現(xiàn)過(guò)程體的功能構(gòu)建實(shí)現(xiàn)特定功能的SQL語(yǔ)句創(chuàng)建存儲(chǔ)過(guò)程CREATE PROCEDU

8、RE ASSQL語(yǔ)句集驗(yàn)證準(zhǔn)確性 EXEC SQL Server 2005數(shù)據(jù)庫(kù)運(yùn)用技術(shù) 帶輸入?yún)?shù)的存儲(chǔ)過(guò)程 創(chuàng)建帶輸入?yún)?shù)的存儲(chǔ)過(guò)程CREATE PROC | PROCEDURE 存儲(chǔ)過(guò)程名 參數(shù)稱號(hào) 參數(shù)數(shù)據(jù)類型 = 參數(shù)的默許值 ,.n WITH ENCRYPTION WITH RECOMPILE AS sql_statement SQL Server 2005數(shù)據(jù)庫(kù)運(yùn)用技術(shù) 帶輸入?yún)?shù)的存儲(chǔ)過(guò)程【例9.4】創(chuàng)建一個(gè)存儲(chǔ)過(guò)程，實(shí)現(xiàn)根據(jù)訂單號(hào)獲取該訂單的信息的功能。 CREATE PROCEDURE OrderDetailOrderID INTASSELECT * FROM Sell_O

9、rder WHERE SellOrderId=OrderID SQL Server 2005數(shù)據(jù)庫(kù)運(yùn)用技術(shù) 帶輸入?yún)?shù)的存儲(chǔ)過(guò)程【例9.6】創(chuàng)建名為listEmployee的存儲(chǔ)過(guò)程，其功能為：在員工表employee中查找符合性別和超越指定工資條件的員工詳細(xì)信息。CREATE PROCEDURE listEmployeesex varchar(2),salary moneyASSELECT *FROM employee WHERE sex=sex and salarysalary SQL Server 2005數(shù)據(jù)庫(kù)運(yùn)用技術(shù) 帶輸入?yún)?shù)的存儲(chǔ)過(guò)程 續(xù)執(zhí)行輸入?yún)?shù)的存儲(chǔ)過(guò)程兩種方法： 運(yùn)用參數(shù)

10、名傳送參數(shù)值EXEC 存儲(chǔ)過(guò)程名 參數(shù)名=參數(shù)值 DEFAULT ，n按位置傳送參數(shù)值EXEC 存儲(chǔ)過(guò)程名 參數(shù)值1，參數(shù)值2， SQL Server 2005數(shù)據(jù)庫(kù)運(yùn)用技術(shù) 帶輸入?yún)?shù)的存儲(chǔ)過(guò)程 續(xù)【例9.7】運(yùn)用【例9.5】中創(chuàng)建的存儲(chǔ)過(guò)程customer_order，獲取 “三川實(shí)業(yè)的信息，包括聯(lián)絡(luò)人姓名、聯(lián)絡(luò)方式以及該公司訂購(gòu)產(chǎn)品的明細(xì)表。代碼如下：EXEC customer_ordercustomername=三川實(shí)業(yè) SQL Server 2005數(shù)據(jù)庫(kù)運(yùn)用技術(shù) 帶輸入?yún)?shù)的存儲(chǔ)過(guò)程 續(xù)【例9.8】利用存儲(chǔ)過(guò)程listEmployee，查找工資超越4000元的的男員工和工資超越30

11、00元女員工的詳細(xì)信息。代碼如下：EXEC listEmployee sex=男,salary=4000EXEC listEmployee salary=3500,sex=女 SQL Server 2005數(shù)據(jù)庫(kù)運(yùn)用技術(shù) 帶輸入?yún)?shù)的存儲(chǔ)過(guò)程 續(xù)【例9.9】按位置傳送執(zhí)行存儲(chǔ)過(guò)程listEmployee，查找工資超越4000元的的男員工和工資超越3000元女員工的詳細(xì)信息。代碼如下：EXEC listEmployee 男, 4000EXEC listEmployee 女, 3500 SQL Server 2005數(shù)據(jù)庫(kù)運(yùn)用技術(shù) 帶輸入?yún)?shù)的存儲(chǔ)過(guò)程 續(xù)【例9.11】利用Name_Employe

12、e存儲(chǔ)過(guò)程查詢一切員工信息和姓王的員工信息EXEC Name_Employee 查詢一切員工信息EXEC Name_Employee 王% 查詢姓王的員工信息 SQL Server 2005數(shù)據(jù)庫(kù)運(yùn)用技術(shù) 帶輸出參數(shù)的存儲(chǔ)過(guò)程【例9.12】 創(chuàng)建帶前往參數(shù)的存儲(chǔ)過(guò)程求兩個(gè)整數(shù)的和。代碼如下：CREATE PROCEDURE PRO_SUMN1 INT,N2 INT,RESULT INT OUTPUTASSET RESULT = N1 + N2 SQL Server 2005數(shù)據(jù)庫(kù)運(yùn)用技術(shù) 帶輸出參數(shù)的存儲(chǔ)過(guò)程【例9.13】執(zhí)行【例9.11】創(chuàng)建的PRO_SUM存儲(chǔ)過(guò)程。代碼如下：DECLARE

13、 ANSWER INTEXEC PRO_SUM 20,69, ANSWER OUTPUTselect ANSWER 結(jié)果 SQL Server 2005數(shù)據(jù)庫(kù)運(yùn)用技術(shù) 查看存儲(chǔ)過(guò)程 sp_help用于顯示存儲(chǔ)過(guò)程的參數(shù)及其數(shù)據(jù)類型，語(yǔ)法格式如下：sp_help objname= 存儲(chǔ)過(guò)程名 sp_helptext用于顯示存儲(chǔ)過(guò)程的源代碼，語(yǔ)法格式如下：sp_helptext objname=存儲(chǔ)過(guò)程 SQL Server 2005數(shù)據(jù)庫(kù)運(yùn)用技術(shù) 查看存儲(chǔ)過(guò)程【例9.14】查看customer_order存儲(chǔ)過(guò)程的參數(shù)和數(shù)據(jù)類型。代碼如下：USE CompanySalesGOsp_help cu

14、stomer_order SQL Server 2005數(shù)據(jù)庫(kù)運(yùn)用技術(shù) 查看存儲(chǔ)過(guò)程【例9.15】查看customer_order存儲(chǔ)過(guò)程的源代碼。代碼如下：USE CompanySalesGOsp_helptext customer_order SQL Server 2005數(shù)據(jù)庫(kù)運(yùn)用技術(shù) 刪除用戶存儲(chǔ)過(guò)程 刪除用戶存儲(chǔ)過(guò)程可以運(yùn)用DROP命令，語(yǔ)法格式如下：DROP PROC | PROCEDURE 存儲(chǔ)過(guò)程名 ,.n SQL Server 2005數(shù)據(jù)庫(kù)運(yùn)用技術(shù) 刪除用戶存儲(chǔ)過(guò)程【例9.16】刪除PRO_SUM存儲(chǔ)過(guò)程。代碼如下：USE CompanySalesGODROP PROCED

15、URE PRO_SUM SQL Server 2005數(shù)據(jù)庫(kù)運(yùn)用技術(shù) 修正存儲(chǔ)過(guò)程 ALTER PROCEDURE語(yǔ)句的語(yǔ)法格式如下： ALTER PROC | PROCEDURE 存儲(chǔ)過(guò)程名 參數(shù)稱號(hào) 參數(shù)數(shù)據(jù)類型 = 參數(shù)的默許值 OUTPUT ,.n WITH ENCRYPTION WITH RECOMPILE AS sql_statement SQL Server 2005數(shù)據(jù)庫(kù)運(yùn)用技術(shù) 系統(tǒng)存儲(chǔ)過(guò)程 常用系統(tǒng)存儲(chǔ)過(guò)程：sp_tables：前往可在當(dāng)前環(huán)境中查詢的對(duì)象列表。這代表可在 FROM 子句中出現(xiàn)的任何對(duì)象。sp_stored_procedures：前往當(dāng)前環(huán)境中的存儲(chǔ)過(guò)程列表

16、。sp_rename：在當(dāng)前數(shù)據(jù)庫(kù)中更改用戶創(chuàng)建對(duì)象的稱號(hào)。此對(duì)象可以是表、索引、列、別名數(shù)據(jù)類型。sp_renamedb：更改數(shù)據(jù)庫(kù)的稱號(hào)。系統(tǒng)存儲(chǔ)過(guò)程 sp_help：報(bào)告有關(guān)數(shù)據(jù)庫(kù)對(duì)象sys.sysobjects 兼容視圖中列出的一切對(duì)象、用戶定義數(shù)據(jù)類型或 SQL Server 2005 提供的數(shù)據(jù)類型的信息。sp_helptext：是用戶定義規(guī)那么的定義、默許值、未加密的 Transact-SQL 存儲(chǔ)過(guò)程、用戶定義 Transact-SQL 函數(shù)、觸發(fā)器、計(jì)算列、CHECK 約束、視圖或系統(tǒng)對(duì)象如系統(tǒng)存儲(chǔ)過(guò)程。sp_who：提供有關(guān) Microsoft SQL Server Database Engine 實(shí)例中的當(dāng)前用戶和進(jìn)程的信息。sp_password：為 Microsoft SQL Server 登錄名添加或更改密碼。 SQL Server 2005數(shù)據(jù)庫(kù)運(yùn)用技術(shù) 擴(kuò)展存儲(chǔ)過(guò)程運(yùn)用外部言語(yǔ)編寫的外部存儲(chǔ)過(guò)程，如C言語(yǔ)，通常以動(dòng)態(tài)鏈接庫(kù)DLL方式出現(xiàn)，擴(kuò)展SQL Server2005功能普通以xp_開頭xp_cmdshell:用來(lái)運(yùn)轉(zhuǎn)從命令行執(zhí)行的程序Xp_sscanf：將數(shù)據(jù)從字符串讀入時(shí)每個(gè)格式參數(shù)所指定的參數(shù)位置Xp_sprintf SQL Server 2