存儲(chǔ)過程精品課件

1、存儲(chǔ)過程第1頁，共65頁，2022年，5月20日，16點(diǎn)9分，星期二第10章 存儲(chǔ)過程第2頁，共65頁，2022年，5月20日，16點(diǎn)9分，星期二 存儲(chǔ)過程是一種高效、安全的訪問數(shù)據(jù)庫的方法。主要被用于提高數(shù)據(jù)庫中檢索數(shù)據(jù)的速度，也經(jīng)常被用來訪問數(shù)據(jù)或管理被修改的數(shù)據(jù)。本章介紹如何創(chuàng)建存儲(chǔ)過程、管理存儲(chǔ)過程。 第3頁，共65頁，2022年，5月20日，16點(diǎn)9分，星期二第10章 存儲(chǔ)過程教學(xué)目標(biāo)：存儲(chǔ)過程是由一系列的T-SQL語句組成的子程序，用來滿足更高的應(yīng)用需求，存儲(chǔ)過程可以通過存儲(chǔ)過程的名字被直接調(diào)用，它們可以說是SQL Server程序設(shè)計(jì)的靈魂，掌握和使用好它們對(duì)數(shù)據(jù)庫的開發(fā)與應(yīng)用非

2、常重要。教學(xué)提示：通過本章的學(xué)習(xí)，要求掌握存儲(chǔ)過程的概念、用途、創(chuàng)建、修改等管理和操作，能編寫簡(jiǎn)單的存儲(chǔ)過程。第4頁，共65頁，2022年，5月20日，16點(diǎn)9分，星期二存儲(chǔ)過程是什么？ 功能 分類 結(jié)構(gòu) 優(yōu)點(diǎn)存儲(chǔ)過程與視圖的區(qū)別？第5頁，共65頁，2022年，5月20日，16點(diǎn)9分，星期二10.1 存儲(chǔ)過程概述10.1.1 存儲(chǔ)過程的概念存儲(chǔ)過程是在數(shù)據(jù)庫服務(wù)器執(zhí)行的一組T-SQL語句的集合，經(jīng)編譯后存放在數(shù)據(jù)庫服務(wù)器端。存儲(chǔ)過程作為一個(gè)單元進(jìn)行處理并以一個(gè)名稱來標(biāo)識(shí)。它能夠向用戶返回?cái)?shù)據(jù)，向數(shù)據(jù)庫中寫入或修改數(shù)據(jù)，還可以執(zhí)行系統(tǒng)函數(shù)和管理操作，用戶在編程中只需要給出存儲(chǔ)過程的名稱和必需的參

3、數(shù)，就可以方便地調(diào)用它們。存儲(chǔ)過程與其他編程語言中的過程有些類似。 存儲(chǔ)過程也是一種數(shù)據(jù)庫對(duì)象。第6頁，共65頁，2022年，5月20日，16點(diǎn)9分，星期二10.1.1 存儲(chǔ)過程的概念存儲(chǔ)過程分類：系統(tǒng)存儲(chǔ)過程(SP_開頭，學(xué)習(xí)使用)用戶自定義存儲(chǔ)過程(主要學(xué)習(xí))擴(kuò)展存儲(chǔ)過程(XP_開頭)臨時(shí)存儲(chǔ)過程(#或#開頭)遠(yuǎn)程存儲(chǔ)過程 第7頁，共65頁，2022年，5月20日，16點(diǎn)9分，星期二1系統(tǒng)存儲(chǔ)過程 系統(tǒng)存儲(chǔ)過程(System Stored Procedures)主要存儲(chǔ)在master數(shù)據(jù)庫中，并以sp_為前綴，并且系統(tǒng)存儲(chǔ)過程主要是從系統(tǒng)表中獲取信息，從而為系統(tǒng)管理員管理SQL Serve

4、r 提供支持。2本地存儲(chǔ)過程 本地存儲(chǔ)過程(Local Stored Procedures)也就是用戶自行創(chuàng)建并存儲(chǔ)在用戶數(shù)據(jù)庫中的存儲(chǔ)過程。事實(shí)上一般所說的存儲(chǔ)過程指的就是本地存儲(chǔ)過程。因?yàn)楫?dāng)用戶存儲(chǔ)過程與系統(tǒng)存儲(chǔ)過程重名時(shí)，調(diào)用系統(tǒng)存儲(chǔ)過程，故用戶不要用sp_為前綴命名。（系統(tǒng)提供的，如：sp_help）（即：將學(xué)習(xí)的自定義存儲(chǔ)過程）第8頁，共65頁，2022年，5月20日，16點(diǎn)9分，星期二3擴(kuò)展存儲(chǔ)過程 擴(kuò)展存儲(chǔ)過程（Extended Stored Procedures）是用戶可以使用外部程序語言（例如 C）編寫的存儲(chǔ)過程。顯而易見，通過擴(kuò)展存儲(chǔ)過程可以彌補(bǔ)SQL Server編程的不

5、足之處，并按需要自行大幅擴(kuò)展其功能。擴(kuò)展存儲(chǔ)過程在使用和執(zhí)行上與一般的存儲(chǔ)過程完全相同?？梢詫?shù)傳遞給擴(kuò)展存儲(chǔ)過程，擴(kuò)展存儲(chǔ)過程也能夠返回結(jié)果和狀態(tài)值。 為了區(qū)別，擴(kuò)展存儲(chǔ)過程的名稱通常以xp_開頭。擴(kuò)展存儲(chǔ)過程是以動(dòng)態(tài)鏈接庫（DLL）的形式存在，能讓SQL Server 動(dòng)態(tài)裝載和執(zhí)行。擴(kuò)展存儲(chǔ)過程一定要存放在系統(tǒng)數(shù)據(jù)庫 master 中。第9頁，共65頁，2022年，5月20日，16點(diǎn)9分，星期二4臨時(shí)存儲(chǔ)過程（1）本地臨時(shí)存儲(chǔ)過程 不論哪一個(gè)數(shù)據(jù)庫是當(dāng)前數(shù)據(jù)庫，如果在創(chuàng)建存儲(chǔ)過程時(shí)，以井字號(hào)（#）作為其名稱的第一個(gè)字符，則該存儲(chǔ)過程將成為一個(gè)存放在tempdb中的本地臨時(shí)存儲(chǔ)過程（例如

6、：CREATE PROCEDURE # book_proc）。 （2）全局臨時(shí)存儲(chǔ)過程 不論哪一個(gè)數(shù)據(jù)庫是當(dāng)前數(shù)據(jù)庫，只要所創(chuàng)建的存儲(chǔ)過程名稱是以兩個(gè)井字號(hào)（# #）開始，則該存儲(chǔ)過程將成為一個(gè)放在tempdb中的全局臨時(shí)存儲(chǔ)過程（例如：CREATE PROCEDURE # # book_proc）。注意： 不論創(chuàng)建的是本地臨時(shí)存儲(chǔ)過程還是全局臨時(shí)存儲(chǔ)過程，只要SQL Server 一停止運(yùn)行，它們將不復(fù)存在。第10頁，共65頁，2022年，5月20日，16點(diǎn)9分，星期二5遠(yuǎn)程存儲(chǔ)過程 在SQL Server中，遠(yuǎn)程存儲(chǔ)過程（Remote Stored Procedures）位于遠(yuǎn)程服務(wù)器上的

7、存儲(chǔ)過程，通?？梢允褂梅植际讲樵兒虴XECUTE命令執(zhí)行一個(gè)遠(yuǎn)程存儲(chǔ)過程。第11頁，共65頁，2022年，5月20日，16點(diǎn)9分，星期二10.1.2 存儲(chǔ)過程的優(yōu)點(diǎn) P151-132執(zhí)行速度快：通過本地存儲(chǔ)、代碼預(yù)編譯和緩存技術(shù)實(shí)現(xiàn)高性能的數(shù)據(jù)操作模塊化編程：增強(qiáng)代碼的重用性和共享性減少網(wǎng)絡(luò)通信量：通過通用編程結(jié)構(gòu)和過程實(shí)現(xiàn)編程框架化存儲(chǔ)過程可以作為安全性機(jī)制：通過隔離和加密的方法提高了數(shù)據(jù)庫的安全性第12頁，共65頁，2022年，5月20日，16點(diǎn)9分，星期二10.2 創(chuàng)建存儲(chǔ)過程要使用存儲(chǔ)過程，首先要?jiǎng)?chuàng)建一個(gè)存儲(chǔ)過程，下面介紹存儲(chǔ)過程的創(chuàng)建和執(zhí)行。10.2.1 創(chuàng)建存儲(chǔ)過程1. 使用SQL

8、 Server Management Studio創(chuàng)建存儲(chǔ)過程2.T-SQL的CREATE PROCEDURE語句來創(chuàng)建存儲(chǔ)過程。第13頁，共65頁，2022年，5月20日，16點(diǎn)9分，星期二10.2.1 創(chuàng)建存儲(chǔ)過程【例10-1】使用SQL Server Management Studio創(chuàng)建存儲(chǔ)過程ProAvgPrice，用于輸出所有圖書的平均價(jià)格。解：其操作步驟如下：1) 啟動(dòng)SSMS。2) 在“對(duì)象資源管理器”中展開當(dāng)前服務(wù)器節(jié)點(diǎn)。3) “數(shù)據(jù)庫”“Library”“可編程性”“存儲(chǔ)過程”節(jié)點(diǎn)，右擊，在彈出的快捷菜單中選擇“新建存儲(chǔ)過程”命令，如圖10-1所示。4) 在右側(cè)的“查詢編輯

9、器”中出現(xiàn)存儲(chǔ)過程模板，可以參照模板在其中輸入存儲(chǔ)過程的T-SQL語句。單擊工具欄中的按鈕，出現(xiàn)“指定模板參數(shù)的值”對(duì)話框，如圖10-2所示。在其中設(shè)置模板中相關(guān)參數(shù)的值。 第14頁，共65頁，2022年，5月20日，16點(diǎn)9分，星期二10.2.1 創(chuàng)建存儲(chǔ)過程 圖10-1 選擇“新建存儲(chǔ)過程”命令 圖10-2 存儲(chǔ)過程模板與“指定模板參數(shù)的值”對(duì)話框 圖10-3 在模板中創(chuàng)建存儲(chǔ)過程ProAvgPrice 圖10-4 顯示存儲(chǔ)過程ProAvgPrice 第15頁，共65頁，2022年，5月20日，16點(diǎn)9分，星期二10.2.1 創(chuàng)建存儲(chǔ)過程 本例創(chuàng)建的存儲(chǔ)過程Pro_AvgPrice不需要輸

10、入?yún)?shù)、輸出參數(shù)，在模板中將其刪除。相應(yīng)SQL語句為：SELECT AVG(Price) AS 平均價(jià)格 FROM Book單擊工具欄中的按鈕，出現(xiàn)“指定模板參數(shù)的值”對(duì)話框，輸入存儲(chǔ)過程名為Pro_AvgPrice，如圖10-3所示。單擊“確定”按鈕，在“指定模板參數(shù)的值”對(duì)話框中設(shè)置的參數(shù)值寫入模板中。（5）單擊工具欄中的按鈕，將其保存在數(shù)據(jù)庫中。此時(shí)選中“存儲(chǔ)過程”節(jié)點(diǎn)，右擊，在彈出的快捷菜單中選擇“刷新”命令，會(huì)看到Pro_AvgPrice存儲(chǔ)過程，如圖10-4所示。 第16頁，共65頁，2022年，5月20日，16點(diǎn)9分，星期二1.創(chuàng)建存儲(chǔ)過程的簡(jiǎn)單格式(無參數(shù))GOCREATE P

11、ROCEDURE procedure_nameWITH ENCRYPTIONWITH RECOMPILEAS Sql_statementGOCREATE PROCEDURE第17頁，共65頁，2022年，5月20日，16點(diǎn)9分，星期二10.2.1 創(chuàng)建存儲(chǔ)過程【例10-2】創(chuàng)建一個(gè)簡(jiǎn)單的存儲(chǔ)過程ProSeBook，查詢所有圖書的信息。解：use LibraryGO CREATE PROCEDURE Pro_SeBook AS SELECT * FROM BookGO思考：1.設(shè)計(jì)返回蔣瑞珍的姓名成績(jī)信息的存儲(chǔ)過程？ 2.改成任意某個(gè)姓名呢？第18頁，共65頁，2022年，5月20日，16點(diǎn)9分

12、，星期二執(zhí)行存儲(chǔ)過程 在存儲(chǔ)過程創(chuàng)建成功后，用戶可以執(zhí)行存儲(chǔ)過程來檢查存儲(chǔ)過程的返回結(jié)果。執(zhí)行存儲(chǔ)過程的基本語法如下:EXEC procedure_name注意：如執(zhí)行存儲(chǔ)過程的代碼是批的第一條，可以省略EXEC?！纠?0-6】USE Library EXEC Pro_SeBook-或：USE Library GOPro_SeBook第19頁，共65頁，2022年，5月20日，16點(diǎn)9分，星期二練習(xí)一 使用圖書管理信息庫1. 設(shè)計(jì)查某位讀者的已借數(shù)量的存儲(chǔ)過程p_borrow_sum。2. 設(shè)計(jì)查某位讀者的全部借書記錄。p_borrow。3. 執(zhí)行存儲(chǔ)過程，檢查執(zhí)行結(jié)果。無參數(shù)的存儲(chǔ)過程：第2

13、0頁，共65頁，2022年，5月20日，16點(diǎn)9分，星期二CREATE PROCEDURE2帶輸入?yún)?shù)的存儲(chǔ)過程（輸入?yún)?shù)） 為了定義接受輸入?yún)?shù)的存儲(chǔ)過程，需要在CREATE PROCEDURE語句中聲明一個(gè)或多個(gè)變量作為參數(shù)。GOCREATE PROCEDURE procedure_nameparameter_name datatype = defaultwith encryptionwith recompileAS Sql語句塊GO第21頁，共65頁，2022年，5月20日，16點(diǎn)9分，星期二【例10-3】創(chuàng)建一個(gè)帶有輸入?yún)?shù)的存儲(chǔ)過程Pro_IdBook，查詢指定讀者編號(hào)的讀者的借書記

14、錄。其中輸入?yún)?shù)用于接收讀者編號(hào)值，設(shè)有默認(rèn)值“G001”。解：CREATE PROCEDURE Pro_IdBook UserId char(6)=G001AS SELECT * FROM Lending WHERE UserId = UserId go提示：如果沒有參數(shù)輸入時(shí)，默認(rèn)查詢學(xué)號(hào)為G001的讀者借書記錄。 第22頁，共65頁，2022年，5月20日，16點(diǎn)9分，星期二執(zhí)行含有輸入?yún)?shù)的存儲(chǔ)過程1) 使用參數(shù)名傳遞參數(shù)值其語法格式如下：EXEC procedure_name parameter_name=value ,n【例10-7】EXECUTE ProIdBook UserId

15、 = T001GO第23頁，共65頁，2022年，5月20日，16點(diǎn)9分，星期二執(zhí)行含有輸入?yún)?shù)的存儲(chǔ)過程2) 按位置傳遞參數(shù)值EXEC procedure_name value1,value2,.【例10-7】改為：EXEC Pro_IdBook T001 GOEXEC Pro_IdBook S003GO第24頁，共65頁，2022年，5月20日，16點(diǎn)9分，星期二-【例】使用輸入?yún)?shù)，顯示某人成績(jī)情況存儲(chǔ)過程USE 教學(xué)成績(jī)管理數(shù)據(jù)庫GOCREATE PROCEDURE Pro_s_sc1name varchar(8)ASSELECT S.學(xué)號(hào) ,姓名,成績(jī)FROM 學(xué)生信息表 S , 成

16、績(jī)表 SCWHERE S .學(xué)號(hào) = SC.學(xué)號(hào) AND 姓名=nameGO第25頁，共65頁，2022年，5月20日，16點(diǎn)9分，星期二-調(diào)用存儲(chǔ)過程EXEC Pro_s_sc1 蔣瑞珍 -用SET設(shè)置變量, 調(diào)用存儲(chǔ)過程DECLARE temp1 VARCHAR(8)SET temp1=蔣瑞珍 EXEC Pro_s_sc1 temp1-用SELECT設(shè)置變量, 調(diào)用存儲(chǔ)過程DECLARE temp1 VARCHAR(8)SELECT temp1=蔣瑞珍 EXEC Pro_s_sc1 temp1第26頁，共65頁，2022年，5月20日，16點(diǎn)9分，星期二練習(xí)一：使用教學(xué)成績(jī)管理數(shù)據(jù)庫1.使

17、用多輸入?yún)?shù)， 顯示某人某門課的成績(jī)情況存儲(chǔ)過程。2.調(diào)用該存儲(chǔ)過程，查看結(jié)果。練習(xí)二：使用Library庫book2表1.創(chuàng)建存儲(chǔ)過程pro_book_price，顯示某出版社，圖書定價(jià)在某個(gè)區(qū)間的圖書信息。2.調(diào)用該存儲(chǔ)過程，查看結(jié)果。有輸入?yún)?shù)的存儲(chǔ)過程：第27頁，共65頁，2022年，5月20日，16點(diǎn)9分，星期二練習(xí)三：(此例題顯示存儲(chǔ)過程可以完成復(fù)雜的查詢)查找與李明同學(xué)選修課程完全相同的學(xué)生的學(xué)號(hào)和姓名（不能多選也不能少選）有輸入?yún)?shù)的存儲(chǔ)過程：第28頁，共65頁，2022年，5月20日，16點(diǎn)9分，星期二3.創(chuàng)建帶參數(shù)的存儲(chǔ)過程(輸入輸出參數(shù))GOCREATE PROCEDUR

18、E pro_過程名稱輸入?yún)?shù)變量 變量類型 , ,輸入?yún)?shù)變量 變量類型 OUTPUT, AS 實(shí)現(xiàn)某功能的T-SQL命令的集合GOCREATE PROCEDURE第29頁，共65頁，2022年，5月20日，16點(diǎn)9分，星期二【例10-4】創(chuàng)建一個(gè)帶有輸入?yún)?shù)和輸出參數(shù)的存儲(chǔ)過程Proc_BookCate，返回指定讀者所借圖書的圖書類別。解：CREATE PROCEDURE Proc_BookCate 讀者編號(hào) char(6),類別 nvarchar(20) OUTPUTASSELECT 類別 = BookCate FROM BookCategory, Lending, Book WHERE

19、BookCategory.BkCateId=Book.BkCateId AND Lending .BookId = Book .BookId AND UserId = 讀者編號(hào)第30頁，共65頁，2022年，5月20日，16點(diǎn)9分，星期二1. 申請(qǐng)對(duì)應(yīng)輸出參數(shù)變量及類型2. 將此變量取代Exec命令中的對(duì)應(yīng)位置，并加output選項(xiàng)3. Print 顯示此變量值 調(diào)用帶輸出參數(shù)的過程：第31頁，共65頁，2022年，5月20日，16點(diǎn)9分，星期二執(zhí)行存儲(chǔ)過程【例10-8】執(zhí)行【例10-4】創(chuàng)建的帶輸入和輸出參數(shù)的存儲(chǔ)過程ProcBookCate，返回指定讀者所借圖書的圖書類別。USE Libr

20、aryGODECLARE 讀者編號(hào) char(6) DECLARE 類別 nvarchar(20)SET 讀者編號(hào) = T002EXEC ProcBookCate 讀者編號(hào) = 讀者編號(hào), 類別 = 類別 OUTPUTPRINT 讀者編號(hào) + 讀者所借圖書的圖書目錄是: + 類別第32頁，共65頁，2022年，5月20日，16點(diǎn)9分，星期二-例：利用輸出參數(shù)計(jì)算階乘的存儲(chǔ)過程GOCREATE PROCEDURE p_factorial in int , out int OUTPUTAS DECLARE i int DECLARE s int SET i=1 SET s=1 WHILE i=in

21、 BEGIN SET s=s * i SET i=i + 1 END set out=sGO第33頁，共65頁，2022年，5月20日，16點(diǎn)9分，星期二1.申請(qǐng)對(duì)應(yīng)輸出參數(shù)變量2.將此變量取代Exec命令中的對(duì)應(yīng)位置，并加out選項(xiàng)3.Print顯示此變量 -p_factorial 案例的調(diào)用，設(shè)置輸入?yún)?shù)為10DECLARE kk intExec p_factorial 10 , kk outPrint 計(jì)算結(jié)果是：+ cast ( kk as varchar(20) )go調(diào)用帶輸出參數(shù)的過程：第34頁，共65頁，2022年，5月20日，16點(diǎn)9分，星期二練習(xí)：使用教學(xué)成績(jī)管理數(shù)據(jù)庫查找

22、某學(xué)號(hào)、某課號(hào)的姓名、成績(jī)。使用輸入輸出參數(shù)的存儲(chǔ)過程實(shí)現(xiàn)。分析：輸入?yún)?shù)？ 輸出參數(shù)？ 查詢涉及幾個(gè)表？ 多表or嵌套？有輸入、輸出參數(shù)的存儲(chǔ)過程：第35頁，共65頁，2022年，5月20日，16點(diǎn)9分，星期二復(fù) 習(xí)存儲(chǔ)過程的優(yōu)點(diǎn)？分類？句法？第36頁，共65頁，2022年，5月20日，16點(diǎn)9分，星期二存儲(chǔ)過程與視圖的比較(1) 可以在單個(gè)存儲(chǔ)過程中執(zhí)行一系列T-SQL語句，而在視圖中只能是單個(gè)SELECT語句。(2) 視圖不能接受參數(shù)，只能返回結(jié)果集；而存儲(chǔ)過程可以接受參數(shù)，包括輸入、輸出參數(shù)，并能返回單個(gè)或多個(gè)結(jié)果集以及返回值，這樣可大大地提高應(yīng)用的靈活性。第37頁，共65頁，2022

23、年，5月20日，16點(diǎn)9分，星期二創(chuàng)建存儲(chǔ)過程-設(shè)計(jì)實(shí)用存儲(chǔ)過程1設(shè)計(jì)存儲(chǔ)過程P_sum1，檢查已知讀者編號(hào)的某個(gè)讀者借書時(shí)，判斷該讀者編號(hào)對(duì)應(yīng)的已借數(shù)量是否2本，小于則給出提示信息輸出可以借書！，否則給出提示信息輸出此讀者不能借書,請(qǐng)先還再借！。設(shè)計(jì)存儲(chǔ)過程P_sum2，檢查已知讀者編號(hào)的某個(gè)讀者借書時(shí)，判斷該讀者編號(hào)對(duì)應(yīng)的已借數(shù)量是否 =sum begin print 借書數(shù)量為+ltrim(cast(sum as char)+已借書數(shù)量為+ltrim(cast(num as char)+不能借書！ return endElse if (select 圖書狀態(tài) from 圖書信息表 whe

24、re 圖書編號(hào)=BookId)在庫 begin print 此書當(dāng)前狀態(tài)不能借出 return end 第42頁，共65頁，2022年，5月20日，16點(diǎn)9分，星期二Else -借出一本書要修改讀者信息表和圖書信息表 begin update 讀者信息表 set 已借書數(shù)量=已借書數(shù)量+1 where 讀者編號(hào)=ReaderId update 圖書信息表 set 圖書狀態(tài)=借出 where 圖書編號(hào)=BookId INSERT INTO 借閱信息表 (讀者編號(hào), 圖書編號(hào),借書日期,還書日期)VALUES(ReaderId,BookId,GETDATE(),null) end GO第43頁，共

25、65頁，2022年，5月20日，16點(diǎn)9分，星期二-借書調(diào)用EXEC p_borrow S001,EC101 -s001為學(xué)生,能借本書-查看select *from 圖書信息表select *from 讀者信息表select *from 借閱信息表-再次借書調(diào)用EXEC p_borrow S001,EC102EXEC p_borrow S001,tp101第44頁，共65頁，2022年，5月20日，16點(diǎn)9分，星期二還書存儲(chǔ)過程？輸入?yún)?shù)為：ReaderId, BookId 1.判斷，借書是否超限？超限給出提示即可。2. 還書：修改借書信息表相應(yīng)記錄的還書日期為getdate()；讀者信息表

26、已借書數(shù)量-1圖書信息表的圖書狀態(tài)=在庫3.設(shè)計(jì)調(diào)用存儲(chǔ)過程，并檢查是否正確第45頁，共65頁，2022年，5月20日，16點(diǎn)9分，星期二goCREATE PROCEDURE p_returnReaderId VARCHAR(20) , BookId VARCHAR(50)AS DECLARE sum int,num int select sum=datediff(day,借書日期,getdate() from 借閱信息表 where 讀者編號(hào)=ReaderId and 圖書編號(hào)=BookId AND 還書日期 is null第46頁，共65頁，2022年，5月20日，16點(diǎn)9分，星期二-計(jì)算

27、限借書時(shí)間select num=借書期限 from 讀者信息表 A,讀者類型表 B where A.讀者類別名=B.讀者類別名 and 讀者編號(hào)=ReaderId if sum =num print 已超期，需要罰款？元！ -此處可以考慮設(shè)計(jì)相關(guān)處理的存儲(chǔ)過程第47頁，共65頁，2022年，5月20日，16點(diǎn)9分，星期二-以下處理還書功能，不管是否超期，都要還書！update 讀者信息表set 已借書數(shù)量=已借書數(shù)量-1where 讀者編號(hào)=ReaderIdupdate 圖書信息表set 圖書狀態(tài)=在庫where 圖書編號(hào)=BookIdupdate 借閱信息表set 還書日期=GETDATE(

28、)WHERE 讀者編號(hào)=ReaderId and 圖書編號(hào)=BookIdGO第48頁，共65頁，2022年，5月20日，16點(diǎn)9分，星期二-還書書調(diào)用EXEC p_return S001,EC101 -s001為學(xué)生,能借本書-查看select *from 圖書信息表select *from 讀者信息表select *from 借閱信息表EXEC p_return S003,EC102EXEC p_return S001,tp101第49頁，共65頁，2022年，5月20日，16點(diǎn)9分，星期二1.已知課程代碼、班級(jí),初始化生成臨時(shí)班級(jí)成績(jī)錄入表CREATE PROCEDURE p_sc_ls課

29、程代碼VARCHAR(20) , 班級(jí)編號(hào) VARCHAR(20) AS SELECT 學(xué)號(hào),姓名, 課程代碼= 課程代碼, 期末成績(jī)=0 ,平時(shí)成績(jī)=0,綜合成績(jī)=0 into class_sc_ls FROM 學(xué)生信息表 WHERE 班級(jí)編號(hào)=班級(jí)編號(hào) Go說明：以上存儲(chǔ)過程相當(dāng)于界面初始化按鈕的代碼創(chuàng)建存儲(chǔ)過程-設(shè)計(jì)實(shí)用存儲(chǔ)過程3第50頁，共65頁，2022年，5月20日，16點(diǎn)9分，星期二-初始化按鈕調(diào)用的存儲(chǔ)過程exec p_sc_ls 900013,200301 go-刷新顯示到界面select *from class_sc_ls-完成界面錄入后的確認(rèn)按鈕執(zhí)行計(jì)算Update cl

30、ass_sc_lsSet 綜合成績(jī)=期末成績(jī)*0.7+平時(shí)成績(jī)*0.3說明：使用游標(biāo)，可以將此成績(jī)class_sc_ls信息逐條插入到目標(biāo)表成績(jī)表2第51頁，共65頁，2022年，5月20日，16點(diǎn)9分，星期二2.已經(jīng)有成績(jī)，修改某班級(jí)成績(jī)CREATE PROCEDURE p_sc_ls2課程代碼VARCHAR(20) , 班級(jí)編號(hào) VARCHAR(20) AS SELECT S.學(xué)號(hào),姓名, 課程代碼= 課程代碼, 期末成績(jī),平時(shí)成績(jī),綜合成績(jī)=期末成績(jī)*0.7+平時(shí)成績(jī)*0.3 into class_sc_ls FROM 學(xué)生信息表 S, 成績(jī)表2WHERE S.學(xué)號(hào)=成績(jī)表2.學(xué)號(hào) an

31、d 班級(jí)編號(hào)=班級(jí)編號(hào) and 課程代碼=課程代碼Go說明：以上存儲(chǔ)過程相當(dāng)于界面修改按鈕的代碼第52頁，共65頁，2022年，5月20日，16點(diǎn)9分，星期二思考： 參考PPT 32-34頁實(shí)用存儲(chǔ)過程， 設(shè)計(jì)一個(gè)存儲(chǔ)過程，同時(shí)完成初始化和修改的功能。第53頁，共65頁，2022年，5月20日，16點(diǎn)9分，星期二10.3 管理存儲(chǔ)過程存儲(chǔ)過程的管理主要包括存儲(chǔ)過程的查看、修改、重命名和刪除。10.3.1 查看存儲(chǔ)過程1. 使用SQL Server Management Studio查看存儲(chǔ)過程下面通過一個(gè)例子介紹如何使用SQL Server Management Studio查看存儲(chǔ)過程?！纠?/p>

32、10-9】使用SQL Server Management Studio來查看【例10-1】創(chuàng)建的存儲(chǔ)過程ProAvgPrice。解：其操作步驟如下：1) 啟動(dòng)SQL Server Management Studio。2) 在“對(duì)象資源管理器”中展開SS服務(wù)器節(jié)點(diǎn)。 第54頁，共65頁，2022年，5月20日，16點(diǎn)9分，星期二10.3.1 查看存儲(chǔ)過程3) 展開“數(shù)據(jù)庫”“Library”“可編程性”“存儲(chǔ)過程”“dbo.ProAvgPrice”節(jié)點(diǎn)，右擊，在出現(xiàn)的快捷菜單中選擇“編寫存儲(chǔ)過程腳本為”“CREATE到”“新查詢編輯器窗口”命令，如圖10-10所示。4) 在右邊的編輯器窗口中出現(xiàn)

33、存儲(chǔ)過程ProAvgPrice源代碼，如圖10-11所示?？梢詫?duì)其修改。 圖10-10 查看存儲(chǔ)過程ProAvgPrice 圖10-11 查看存儲(chǔ)過程ProAvgPrice源代碼 第55頁，共65頁，2022年，5月20日，16點(diǎn)9分，星期二10.3.1 查看存儲(chǔ)過程2. 使用命令方式查看存儲(chǔ)過程SQL Server 2008可以使用命令方式即調(diào)用系統(tǒng)存儲(chǔ)過程來查看有關(guān)存儲(chǔ)過程的信息。(1) sp_help用于顯示存儲(chǔ)過程的信息，如存儲(chǔ)過程的參數(shù)、創(chuàng)建日期等。其語法如下：EXECUTE sp_help 存儲(chǔ)過程名(2) sp_helptext用于顯示存儲(chǔ)過程的源代碼。其語法如下：EXECUTE

34、 sp_helptext 存儲(chǔ)過程名(3) sp_depends 用于顯示和存儲(chǔ)相關(guān)的數(shù)據(jù)庫對(duì)象。其語法如下：EXECUTE sp_ depends 存儲(chǔ)過程名 第56頁，共65頁，2022年，5月20日，16點(diǎn)9分，星期二10.3.1 查看存儲(chǔ)過程【例10-10】使用相關(guān)系統(tǒng)存儲(chǔ)過程查詢【例10-4】創(chuàng)建的存儲(chǔ)過程Proc_BookCate的所有者、創(chuàng)建時(shí)間和各個(gè)參數(shù)的信息。解：程序如下：EXEC sp_help Proc_BookCate執(zhí)行結(jié)果如圖10-12所示第57頁，共65頁，2022年，5月20日，16點(diǎn)9分，星期二10.3.1 查看存儲(chǔ)過程【例10-11】使用相關(guān)系統(tǒng)存儲(chǔ)過程查詢

35、【例10-4】創(chuàng)建的存儲(chǔ)過程Proc_BookCate的源代碼。解：程序如下：EXEC sp_helptext Proc_BookCate執(zhí)行結(jié)果如圖10-13所示。第58頁，共65頁，2022年，5月20日，16點(diǎn)9分，星期二 10.3.2 修改存儲(chǔ)過程1. 使用SSMS修改存儲(chǔ)過程下面通過一個(gè)例子介紹如果使用SQL Server Management Studio修改存儲(chǔ)過程?！纠?0-12】使用SQL Server Management Studio修改【例10-3】所創(chuàng)建的存儲(chǔ)過程ProIdBook。解：其操作步驟如下：1) 啟動(dòng)SQL Server Management Studio。2) 在“對(duì)象資源管理器”中展開SS服務(wù)器節(jié)點(diǎn)。3) 展開“數(shù)據(jù)庫”“Library” “可編程性”“存儲(chǔ)過程”“dbo.ProIdBook”節(jié)點(diǎn)，右擊，在出現(xiàn)的快捷菜單中選擇“修改”命令，如圖10-14所示。 第59頁，共65頁，2022年，5月20日，16點(diǎn)9分，星期二10.3.2 修改存儲(chǔ)過程4