使用存儲過程實現(xiàn)進銷存系統(tǒng)的業(yè)務(wù)邏輯

1、第六章第六章 使用存儲過程使用存儲過程提高提高進銷存系統(tǒng)的進銷存系統(tǒng)的執(zhí)行效率執(zhí)行效率 終極目標：能根據(jù)需要靈活、快速地創(chuàng)建存儲過程 計算機應(yīng)用教研室本次課的學(xué)習(xí)目標本次課的學(xué)習(xí)目標學(xué)習(xí)目標u1.1.了解存儲過程了解存儲過程u2.2.掌握存儲過程的定義與使用掌握存儲過程的定義與使用學(xué)習(xí)重點:u掌握帶輸入?yún)?shù)和輸出參數(shù)的存儲過程掌握帶輸入?yún)?shù)和輸出參數(shù)的存儲過程的使用的使用6.1 6.1 工作任務(wù)工作任務(wù)任務(wù)：待審核入出庫單選擇功能的選擇描述： 此功能從沒有審核的入庫單的匯總數(shù)據(jù)中選擇需要審核的入出庫單，重點是此功能提供了通用查詢的實現(xiàn)方式6.1 6.1 工作任務(wù)工作任務(wù)6.2 6.2 存儲過程

2、的優(yōu)點存儲過程的優(yōu)點可以提供對數(shù)據(jù)的輕松訪問,客戶端不需要知道復(fù)雜的數(shù)據(jù)結(jié)構(gòu)或業(yè)務(wù)邏輯過程,只要把查詢指令通過存儲過程發(fā)給服務(wù)器,服務(wù)就把需要的數(shù)據(jù)返回給用戶.利用服務(wù)器強大的計算能力和速度,避免了把大量的數(shù)據(jù)從服務(wù)器下載到客戶端,減少了網(wǎng)絡(luò)上傳輸量服務(wù)器只用把計算結(jié)果給客戶端,可提高客戶端工作的效率. 6.3 6.3 存儲過程的分類存儲過程的分類大體分為以下兩類:u1.1.系統(tǒng)存儲過程系統(tǒng)存儲過程. .sqlsql server server有大量的系統(tǒng)存儲過程有大量的系統(tǒng)存儲過程, ,用于管理用于管理sqlsql server server并顯示有關(guān)數(shù)據(jù)庫和用戶的信息并顯示有關(guān)數(shù)據(jù)庫和用戶

3、的信息. .以以sp_sp_開關(guān)開關(guān), ,用戶一般不用戶一般不用用sp_sp_開頭的存儲過程開頭的存儲過程.(.(擴展存儲過程擴展存儲過程: :xpxp_,_,如如xp_cmdshellxp_cmdshell) )u2.2.用戶自定義存儲過程用戶自定義存儲過程. .用于解決用于的需要而建立的存儲過程用于解決用于的需要而建立的存儲過程. .按返回數(shù)據(jù)類型分為兩類:u1.1.類似于類似于selectselect查詢查詢, ,用于檢索數(shù)據(jù)用于檢索數(shù)據(jù), ,檢索到的數(shù)據(jù)能夠以結(jié)檢索到的數(shù)據(jù)能夠以結(jié)果集的形式返回給客戶端果集的形式返回給客戶端. .u2.2.通過輸出參數(shù)返回信息或不返回信息通過輸出參數(shù)返

4、回信息或不返回信息, ,或只執(zhí)行一個動作或只執(zhí)行一個動作. .6.4 6.4 存儲過程的定義存儲過程的定義語法格式Create Procedure|Proc 存儲過程名稱參數(shù)名稱 參數(shù)類型 output,參數(shù)名稱1 參數(shù)類型 output.As T-SQL語句實現(xiàn)的業(yè)務(wù)邏輯6.5 6.5 無參數(shù)存儲過程的創(chuàng)建無參數(shù)存儲過程的創(chuàng)建語法Create Proc 存儲過程名稱 asT-SQL語句6.5 6.5 無參數(shù)存儲過程的創(chuàng)建無參數(shù)存儲過程的創(chuàng)建【例】定義存儲過程up_GetDay輸出當前日期所在月份的第一天和最后一天create proc up_getdayasdeclare days int,

5、 st datetime, ed datetime select days=day(getdate() -1select st=dateadd(day,days*(-1),getdate()select ed=dateadd(day,-1,dateadd(month,1,st)select st as StDate,ed as EdDate6.5 6.5 無參數(shù)存儲過程的調(diào)用無參數(shù)存儲過程的調(diào)用1、Execute存儲過程名稱2、Exec 存儲過程名稱3、存儲過程名稱Execute up_getdayExec up_getdayUp_getday6.6 6.6 有參數(shù)存儲過程的定義有參數(shù)存儲過程

6、的定義語法格式Create Procedure|Proc 存儲過程名稱參數(shù)名稱 參數(shù)類型 ,參數(shù)名稱1 參數(shù)類型 .As T-SQL語句實現(xiàn)的業(yè)務(wù)邏輯6.6 6.6 有參數(shù)存儲過程的定義有參數(shù)存儲過程的定義【例】定義存儲過程up_GetEndDay，輸入?yún)?shù)年月(YYYYMM),輸入?yún)?shù)名稱ym int,輸出參數(shù)所在月份的最后一天Create proc up_getEndDayym intasdeclare days int, st datetime, ed datetime select st=convert(varchar(8),ym)+01select days=day(st) -1se

7、lect ed=dateadd(day,-1,dateadd(month,1,st)select ed as ed6.6 6.6 有參數(shù)存儲過程的定義有參數(shù)存儲過程的定義【例】定義存儲過程up_StudentQuery ，輸入?yún)?shù)cno,Sno,Sname，檢索學(xué)生課程成績信息，輸出學(xué)號，姓名，課程編號，課程名稱，成績6.6 6.6 有參數(shù)存儲過程的定義有參數(shù)存儲過程的定義6.6 6.6 有參數(shù)存儲過程的定義有參數(shù)存儲過程的定義Create proc up_studentquery cno int ,sno varchar(10),sname varchar(10)Asselect c.學(xué)號,

8、c.姓名,a.課程編號,a.課程名稱,b.成績from 課程 a inner join 學(xué)生成績 b on a.課程編號=b.課程編號join 學(xué)生 c on b.學(xué)號=c.學(xué)號where (a.課程編號=cno or cno=0) and (c.學(xué)號=sno or sno=) and (c.姓名=sname or sname=)6.6 6.6 有參數(shù)存儲過程的調(diào)用有參數(shù)存儲過程的調(diào)用Exec 存儲過程名 參數(shù)值，參數(shù)值1。必須按參數(shù)聲明順序輸入?yún)?shù)值Exec 存儲過程名 參數(shù)名=參數(shù)值， 參數(shù)名=參數(shù)值?？刹话磪?shù)聲明順序輸入?yún)?shù)值Up_getEndDay 200910Exec Up_get

9、EndDay 200910Exec Up_getEndDay ym=200910Exec up_studentquery Sno= ,cno=0,sname=6.7 6.7 帶參數(shù)的存儲過程的定義帶參數(shù)的存儲過程的定義語法格式Create Procedure|Proc 存儲過程名稱參數(shù)名稱 參數(shù)類型 output,參數(shù)名稱1 參數(shù)類型 output.As T-SQL語句實現(xiàn)的業(yè)務(wù)邏輯6.7 6.7 帶參數(shù)的存儲過程的定義帶參數(shù)的存儲過程的定義 什么時候用output 參數(shù)?u當當select select 語句返回一個值語句返回一個值( (而不是一個值集而不是一個值集) )時時, ,可以用可以

10、用output output 參數(shù)來返回值參數(shù)來返回值. .【例】使用output創(chuàng)建一個存儲過程CREATE PROCEDURE do_insert ASINSERT INTO 學(xué)生 VALUES(091201, 陶偉,男, 1359444, );再創(chuàng)建一個存儲過程CREATE PROCEDURE do_action X char(2), STR CHAR(8) OUTPUT ASBEGIN EXEC do_insertIF X=男BEGINUPDATE 學(xué)生 SET 姓名=劉英, 性別=女 WHERE 學(xué)號=091201SET STR=修改成功ENDELSEIF X=女BEGINDELET

11、E FROM 學(xué)生 WHERE 學(xué)號=091201SET STR=刪除成功ENDEND調(diào)用和輸出DECLARE str char(8)EXEC do_action 男, str OUTPUTSELECT str;5.加密的存儲過程if exists (select name from sysobjectswhere name = encrypt_this and type = p)drop procedure encrypt_thisgocreate procedure encrypt_this with encryptionasselect * from xsgo6.注意:u一般的存儲過程在

12、創(chuàng)建后一般的存儲過程在創(chuàng)建后,可利用可利用sp_helptext進行查看創(chuàng)建的命令進行查看創(chuàng)建的命令.u做為一個加密存儲過程做為一個加密存儲過程,利用該命令的結(jié)果為利用該命令的結(jié)果為:u也可以直接在查詢分析器中對存儲過程進行編也可以直接在查詢分析器中對存儲過程進行編輯輯:修改存儲過程1.界面2.命令 alter不返回值的存儲過程不返回值的存儲過程公司進貨時,讓庫存表中的數(shù)量自動進行增加.庫存表庫存表(貨物編號貨物編號,貨物名稱貨物名稱,貨物數(shù)量貨物數(shù)量)creaet proc c1 char(20),1 floatasupdate 庫存表庫存表set 貨物數(shù)量貨物數(shù)量=貨物數(shù)量貨物數(shù)量+1where 貨物編號貨物編號= 1 刪除存儲過程u基本語句格式刪除刪除存儲過程DROP PROCEDURE 存儲過程名存儲過程名例例:刪除上面所建存儲過程刪除上面所建存儲過程encrypt_this Drop procedure encrypt_this 常用系統(tǒng)存儲過程u sp_helpu sp_rename存儲過程sp_helpu 顯示存儲過程的參數(shù)及其數(shù)據(jù)類型 基本語句格式基本語句格式返回sp_help objname= name