《數(shù)據(jù)庫管理與應用》課件-32 存儲過程

數(shù)據(jù)庫原理與應用存儲過程思考：是否可以用函數(shù)實現(xiàn)？要求：根據(jù)學生學號查詢是否存在該學生，如果存在查詢該學生的全部信息；否則，返回“查無此人！”結(jié)果1：以返回表的形式返回學生信息。用表值函數(shù)。結(jié)果2：返回一個字符串。用標量值函數(shù)。結(jié)果：用函數(shù)無法實現(xiàn)！表值函數(shù)？標量值值函數(shù)？優(yōu)點：（1）存儲過程在服務器端運行，執(zhí)行速度快。（2）存儲過程執(zhí)行一次后，就駐留在高速緩沖存儲器，提高了系統(tǒng)性能。（3）使用存儲過程可以完成所有數(shù)據(jù)庫操作，并可通過編程方式控制對數(shù)據(jù)庫信息訪問的權(quán)限，確保數(shù)據(jù)庫的安全。（4）自動完成需要預先執(zhí)行的任務。存儲過程可以在SQLServer啟動時自動執(zhí)行。1.存儲過程2.系統(tǒng)存儲過程（1）系統(tǒng)存儲過程：由SQLServer提供，可以作為命令執(zhí)行。定義在master中，其前綴是“sp”。（2）用戶存儲過程：可以使用T-SQL語言編寫。1.存儲過程sp_addtype‘學號’,‘char(6)‘--添加用戶自定義數(shù)據(jù)類型sp_attach_db‘xscj',‘c:\xscj.mdf'--附加數(shù)據(jù)庫sp_detach_db‘xscj'--分離數(shù)據(jù)庫sp_helptextsp_addtype--查看源代碼2.系統(tǒng)存儲過程視頻：幾個系統(tǒng)存儲過程的使用createprocedure存儲過程名/*定義過程名*/@參數(shù)參數(shù)類型/*定義參數(shù)的類型*/asbegin

功能語句/*執(zhí)行的操作*/end創(chuàng)建的語法更加自由3.用戶存儲過程——定義存儲過程的執(zhí)行語法格式：存儲過程名@參數(shù)=值調(diào)用的語法更加簡單3.用戶存儲過程——調(diào)用createprocedurep1@idchar(6)asbeginifexists(select*from學生where學號=@id)select*from學生where學號=@idelseselect'查無此人！'endp1'98001'例1：創(chuàng)建一個存儲過程，根據(jù)學生學號查詢學生信息，如果該學生不存在則返回“查無此人”。3.用戶存儲過程——案例視頻：存儲過程的創(chuàng)建、調(diào)用和刪除例2：創(chuàng)建一個存儲過程,目的:根據(jù)學生學號查詢學生的姓名.如果該學生不存在則返回“查無此人”,學號的默認值是‘98001’。createprocedurep2@idchar(6)=‘98001’asbeginifexists(select*from學生where學號=@id)select*from學生where學號=@idelseselect'查無此人！'end3.用戶存儲過程——帶默認值p2p2‘98002’sp_helptextp23.用戶存儲過程——帶默認值例3：修改例2，使之成為加密的存儲過程。alterprocedurep2@idchar(6)=‘98001’withencryptionasbeginifexists(select*from學生where學號=@id)select*from學生where學號=@idelseselect'查無此人！'end3.用戶存儲過程——加密加密前：sp_helptextp2加密后：sp_helptextp23.用戶存儲過程——加密createprocedurep3@xmvarchar(8)asbeginifexists(select*from學生where姓名

like@xm)select*from學生where姓名

like@xmelseselect'查無此人！'end例4：創(chuàng)建存儲過程，查詢指定姓氏的學生的信息。p3'李%'3.用戶存儲過程——帶通配符思考：創(chuàng)建一個存儲過程，要求（1）功能：根據(jù)課程名求成績小于指定分數(shù)的學生記錄。（2）課程名默認值以“數(shù)據(jù)”開頭，分數(shù)默認值為60分。（3）可以使用通配符實現(xiàn)模糊查詢。倒計時3分鐘！createprocedurep4@kcmvarchar(20)=‘數(shù)據(jù)%',@cjint=60asselect課程.課程號,課程名,學號,成績from選課,課程where選課.課程號=課程.課程號and課程名like@kcmand成績<@cj調(diào)用：p4@cj=704.練習

5.修改用戶存儲過程createprocedure存儲過程名/*定義過程名*/@參數(shù)參數(shù)類型/*定義參數(shù)的類型*/asbegin

功能語句/*執(zhí)行的操作*/endalter

語法格式：dropprocedure存儲過程1，存儲過程2…..永久地刪除存儲過程前，須確認該存儲過程沒有任何依賴關(guān)系。例5:刪除數(shù)據(jù)庫中的存儲過程p1