【正文】 ), odate date, enumber char(32), umber char(32), quant int, pn int, pnumber char(32) as if exists ( select * from orders where o_number=onumber ) print39。,39。0539。,39。該供應(yīng)商不存在 39。,39。 else insert into po values (pnumber ,onumber,quany )。 else insert 18 into orders values (o_number ,empl_number,c_number,time , pn) 圖 9 插入訂單圖 插入產(chǎn)品訂購(gòu)信息 功能：插入產(chǎn)品訂購(gòu)信息 目的：存儲(chǔ)插入產(chǎn)品訂購(gòu)信息的操作 意義：下次重復(fù)同樣的動(dòng)作時(shí)，可直接執(zhí)行存儲(chǔ)過(guò)程，簡(jiǎn)化操作步驟 create procedure procedure_oeder_pro 插入訂單產(chǎn)品 pnumber char(32),onumber char(32)， quany int as if not exists ( select * from product where p_number=pnumber ) print39。 else update customer set c_adress=address,c_telnub=tell where c_number=number 。 圖 3 最多訂單客戶效果圖 14 查詢客戶信息 功能：查詢客戶信息 目的：存儲(chǔ)查詢客戶信息的操作 意義：下次重復(fù)同樣的動(dòng)作時(shí)，可直接執(zhí)行存儲(chǔ)過(guò)程，簡(jiǎn)化操作步驟 create procedure procedure_select_coustom 查詢客戶信息 name char(32) as if not exists ( select * from customer where c_name=name ) print39。 存儲(chǔ)過(guò)程 的設(shè)計(jì) 查詢訂單數(shù)量 功能：查詢訂單的數(shù)量 目的：存儲(chǔ)查詢訂單數(shù)量的操作 意義：下次重復(fù)同樣的動(dòng)作時(shí)，可直接執(zhí)行存儲(chǔ)過(guò)程，簡(jiǎn)化操作步驟 create procedure procedure_SearchOrdersNum 查詢訂單的存儲(chǔ)過(guò)程 as select COUNT(*) 39。 視圖的設(shè)計(jì) 建立一張客戶信息的視圖 目的：屏蔽客戶的地址信息。在本銷售訂單數(shù)據(jù)庫(kù)管理系統(tǒng)中，有一些限制性條件，比如一個(gè)客戶可以下多個(gè)訂單，但一個(gè)訂單只能由一個(gè)客戶下，一個(gè)訂單可以訂購(gòu)多種產(chǎn)品，每種產(chǎn)品可被不同訂單訂購(gòu)等等，這些限制條件的加入能夠使本數(shù)據(jù)庫(kù)管理系統(tǒng)的功能更加完善，能夠滿足更高的要求。最后要求完成一次下訂單的流程。數(shù)據(jù)庫(kù)銷售訂單管理系統(tǒng)課程設(shè)計(jì) 目 錄 1．引言 ................................................................................................................................................................................. 1 編寫目的 ............................................................................................................................................................. 1 參考資料 ............................................................................................................................................................. 1 ............................................................................................................................................................................ 2 業(yè)務(wù)描述 ................................................................................................................................................................... 2 需求分析 ................................................................................................................................................................... 2 ............................................................................................................................................................... 3 ........................................................................................................................................................... 4 數(shù)據(jù)庫(kù)對(duì)象命名規(guī)則 ............................................................................................................................................... 4 數(shù)據(jù)項(xiàng)編碼規(guī)則 ....................................................................................................................................................... 4 ............................................................................................................................................................................ 5 ER 圖 .......................................................................................................................................................................... 5 關(guān)系模型 ................................................................................................................................................................... 5 ............................................................................................................................................................................ 6 表匯總 ........................................................................................................................................................................ 6 表 [1]:[ SUPPLIERS 表 ](供應(yīng)商表 ) ............................................................................................................................ 6 表 [2]:[PRODUCT 表 ](產(chǎn)品表 ) ................................................................................................................................... 7 表 [3]:[SP 表 ](供應(yīng)商產(chǎn)品供應(yīng)表 ) .......................................................................................................................... 7 表 [4]:[ ORDERS 表 ](訂單表 ) ..................................................................................................................................... 8 表 [5]:[PO 表 ](產(chǎn)品訂購(gòu)表 ) ...................................................................................................................................... 9 表 [6]:[ EMPLOYEE 表 ](雇員表 ) .............................................................................................................................. 10 表 [7]:[CUSTOMER表 ](客戶表 )............................................................................................................................... 11 視圖的設(shè)計(jì) ............................................................................................................................................................ 11 存儲(chǔ)過(guò)程 的設(shè)計(jì) ................................................................................................................................................. 12 觸發(fā)器的設(shè)計(jì) ..................................................................................................................................................... 29 函數(shù)的設(shè)計(jì) ....................................................................................................................................................... 3