數(shù)據(jù)庫實(shí)用技術(shù)(基于Oracle)第10章課件

數(shù)據(jù)庫原理與應(yīng)用第10章Oracle綜合實(shí)例1數(shù)據(jù)庫原理與應(yīng)用第10章Oracle綜合實(shí)例1學(xué)習(xí)目標(biāo)●掌握數(shù)據(jù)庫設(shè)計(jì)的基本步驟和方法?！裾莆绽肑ava和PowerBuilder連接Oracle數(shù)據(jù)庫的方法。2學(xué)習(xí)目標(biāo)●掌握數(shù)據(jù)庫設(shè)計(jì)的基本步驟和方法。210.1系統(tǒng)功能分析10.2系統(tǒng)數(shù)據(jù)庫設(shè)計(jì)10.3連接Oracle數(shù)據(jù)庫10.4小結(jié)第10章Oracle綜合實(shí)例

310.1系統(tǒng)功能分析10.2系統(tǒng)數(shù)據(jù)庫設(shè)計(jì)10.310.1系統(tǒng)功能分析

本章以進(jìn)銷存系統(tǒng)為例來介紹數(shù)據(jù)庫設(shè)計(jì)的過程，系統(tǒng)功能分析是在匯總系統(tǒng)開發(fā)的總體任務(wù)的基礎(chǔ)上完成。本系統(tǒng)中的進(jìn)銷存管理系統(tǒng)需要完成商品管理、倉庫管理、客戶管理、供應(yīng)商管理、庫存管理、商品銷售管理、商品供應(yīng)管理等多個(gè)功能，滿足企業(yè)高效率的需求，系統(tǒng)功能模塊如圖10-1所示。

進(jìn)銷存系統(tǒng)商品管理模塊客戶管理模塊供應(yīng)商管理模塊倉庫管理模塊商品銷售模塊商品供應(yīng)模塊商品庫存模塊410.1系統(tǒng)功能分析本章以進(jìn)銷存系統(tǒng)為例來介紹數(shù)據(jù)庫設(shè)10.2系統(tǒng)數(shù)據(jù)庫設(shè)計(jì)10.1系統(tǒng)功能分析10.3連接Oracle數(shù)據(jù)庫10.4小結(jié)第10章Oracle綜合實(shí)例

510.2系統(tǒng)數(shù)據(jù)庫設(shè)計(jì)10.1系統(tǒng)功能分析10.310.2.1需求分析10.2.2概念結(jié)構(gòu)設(shè)計(jì)10.2.3邏輯結(jié)構(gòu)設(shè)計(jì)10.2.4在Oracle中創(chuàng)建表10.2系統(tǒng)數(shù)據(jù)庫設(shè)計(jì)10.2.5在Oracle中創(chuàng)建索引10.2.6在Oracle中創(chuàng)建視圖10.2.7在Oracle中創(chuàng)建序列10.2.9在Oracle中創(chuàng)建存儲(chǔ)過程或存儲(chǔ)函數(shù)10.2.8在Oracle中創(chuàng)建觸發(fā)器610.2.1需求分析10.2.2概念結(jié)構(gòu)設(shè)計(jì)需求分析

系統(tǒng)開發(fā)的目標(biāo)是實(shí)現(xiàn)企業(yè)進(jìn)銷存管理的系統(tǒng)化、規(guī)劃化和自動(dòng)化，基本要求如下：（1）客戶管理：對(duì)整個(gè)銷售過程進(jìn)行管理，對(duì)銷售對(duì)象進(jìn)行統(tǒng)計(jì)。（2）供應(yīng)商管理：對(duì)整個(gè)供應(yīng)過程進(jìn)行管理，對(duì)供應(yīng)對(duì)象進(jìn)行統(tǒng)計(jì)。（3）倉庫管理：對(duì)倉庫信息進(jìn)行管理。（4）商品管理：對(duì)系統(tǒng)銷售/供應(yīng)過程中的商品進(jìn)行管理。（5）進(jìn)/退貨管理：對(duì)整個(gè)供應(yīng)過程中發(fā)生的進(jìn)貨/退貨進(jìn)行跟蹤統(tǒng)計(jì)。（6）售/退貨管理：對(duì)整個(gè)銷售過程中發(fā)生的進(jìn)貨/退貨進(jìn)行跟蹤統(tǒng)計(jì)。（7）庫存管理：對(duì)商品入/出倉庫進(jìn)行跟蹤統(tǒng)計(jì)。710.2.1需求分析系統(tǒng)開發(fā)的目標(biāo)是實(shí)現(xiàn)企業(yè)進(jìn)銷存管理10.2.1需求分析

通過對(duì)企業(yè)進(jìn)銷存管理的內(nèi)容和數(shù)據(jù)流程分析，系統(tǒng)的數(shù)據(jù)結(jié)構(gòu)如表10-1所示。

數(shù)據(jù)結(jié)構(gòu)名含義組成客戶信息（Customer）對(duì)銷售客戶進(jìn)行記錄、統(tǒng)計(jì)客戶編碼、客戶名稱、地址、電話、郵編供應(yīng)商信息（Supplier）對(duì)供應(yīng)商進(jìn)行記錄、統(tǒng)計(jì)供應(yīng)商編碼、供應(yīng)商名稱、地址、電話、郵編、聯(lián)系人倉庫信息（StoreHouse）對(duì)倉庫進(jìn)行記錄、統(tǒng)計(jì)倉庫編碼、地址、電話、成立時(shí)間商品信息（ProductClass）對(duì)商品進(jìn)行記錄、統(tǒng)計(jì)商品編號(hào)、商品名稱、單價(jià)、規(guī)格810.2.1需求分析通過對(duì)企業(yè)進(jìn)銷存管理的內(nèi)容和數(shù)據(jù)流10.2.1需求分析

系統(tǒng)的數(shù)據(jù)項(xiàng)如表10-2～10-5所示。

數(shù)據(jù)項(xiàng)名含義類型長(zhǎng)度約束Supplier_ID

供應(yīng)商編號(hào)INT

惟一標(biāo)識(shí)Supplier_Name供應(yīng)商名稱VARCHAR2250非空Address地址VARCHAR2250非空Phone電話VARCHAR225

PostalCode郵編VARCHAR210

ConstactPerson聯(lián)系人VARCHAR220

910.2.1需求分析系統(tǒng)的數(shù)據(jù)項(xiàng)如表10-2～10-510.2.1需求分析

數(shù)據(jù)項(xiàng)名含義類型長(zhǎng)度約束Customer_ID

客戶編號(hào)INT

惟一標(biāo)識(shí)Customer_Name客戶名稱VARCHAR2250非空Address地址VARCHAR2250非空Phone電話VARCHAR225

PostalCode郵編VARCHAR210

1010.2.1需求分析數(shù)據(jù)項(xiàng)名含義類型長(zhǎng)度約束Custo10.2.1需求分析

數(shù)據(jù)項(xiàng)名含義類型長(zhǎng)度約束StoreHouse_ID

倉庫編號(hào)INT

惟一標(biāo)識(shí)Address地址VARCHAR2250非空Phone電話VARCHAR225

CreateDate成立時(shí)間DATE

1110.2.1需求分析數(shù)據(jù)項(xiàng)名含義類型長(zhǎng)度約束Store10.2.1需求分析

數(shù)據(jù)項(xiàng)名含義類型長(zhǎng)度小數(shù)位約束ProductClass_ID

商品編號(hào)INT

惟一標(biāo)識(shí)ProductClass_Name商品名稱VARCHAR230

非空ProductSpec_ID商品規(guī)格VARCHAR230

非空Price

單價(jià)NUMBER52非空1210.2.1需求分析數(shù)據(jù)項(xiàng)名含義類型長(zhǎng)度小數(shù)位約束Pr10.2.1需求分析

在實(shí)際應(yīng)用中，系統(tǒng)中存在著各種聯(lián)系：（1）一個(gè)倉庫可以存儲(chǔ)多種商品，一種商品只能存儲(chǔ)在一個(gè)倉庫，而且商品入庫或出庫時(shí)記錄是入庫還是出庫，還記錄時(shí)間、數(shù)量、經(jīng)手人；（2）一個(gè)供應(yīng)商可以供應(yīng)多種商品，一種商品可以由多個(gè)供應(yīng)商供應(yīng)，而且供應(yīng)商供應(yīng)商品時(shí)記錄是進(jìn)貨還是退貨，還記錄時(shí)間、變化數(shù)量、金額、經(jīng)手人；（3）一個(gè)客戶可以購買多種商品，一種商品可以由多個(gè)客戶購買，而且客戶購買或退商品時(shí)記錄是賣貨還是退貨，還記錄時(shí)間、變化數(shù)量、金額、經(jīng)手人。因此，倉庫與商品之間是一對(duì)多的聯(lián)系，客戶與商品之間是多對(duì)多的聯(lián)系，供應(yīng)商與商品之間是多對(duì)多的聯(lián)系。由于在一個(gè)商品同一天可能多次存入同一倉庫，因此增加入/出庫編號(hào)以標(biāo)識(shí)每一次庫存操作；同理，由于一個(gè)客戶同一天可能購買多次同一商品，因此增加售/退編號(hào)以標(biāo)識(shí)每一次銷售操作；一個(gè)供應(yīng)商同一天可能多次供應(yīng)同一商品，因此增加進(jìn)/退編號(hào)以標(biāo)識(shí)每一次供應(yīng)操作。1310.2.1需求分析在實(shí)際應(yīng)用中，系統(tǒng)中存在著各種聯(lián)系10.2.2概念結(jié)構(gòu)設(shè)計(jì)10.2.1需求分析10.2.3邏輯結(jié)構(gòu)設(shè)計(jì)10.2.4在Oracle中創(chuàng)建表10.2系統(tǒng)數(shù)據(jù)庫設(shè)計(jì)10.2.5在Oracle中創(chuàng)建索引10.2.6在Oracle中創(chuàng)建視圖10.2.7在Oracle中創(chuàng)建序列10.2.9在Oracle中創(chuàng)建存儲(chǔ)過程或存儲(chǔ)函數(shù)10.2.8在Oracle中創(chuàng)建觸發(fā)器1410.2.2概念結(jié)構(gòu)設(shè)計(jì)10.2.1需求分析概念結(jié)構(gòu)設(shè)計(jì)

根據(jù)需求分析，進(jìn)銷存管理系統(tǒng)的E-R圖如圖10-2所示。

1510.2.2概念結(jié)構(gòu)設(shè)計(jì)根據(jù)需求分析，進(jìn)銷存管理系統(tǒng)的10.2.3邏輯結(jié)構(gòu)設(shè)計(jì)10.2.2概念結(jié)構(gòu)設(shè)計(jì)10.2.1需求分析10.2.4在Oracle中創(chuàng)建表10.2系統(tǒng)數(shù)據(jù)庫設(shè)計(jì)10.2.5在Oracle中創(chuàng)建索引10.2.6在Oracle中創(chuàng)建視圖10.2.7在Oracle中創(chuàng)建序列10.2.9在Oracle中創(chuàng)建存儲(chǔ)過程或存儲(chǔ)函數(shù)10.2.8在Oracle中創(chuàng)建觸發(fā)器1610.2.3邏輯結(jié)構(gòu)設(shè)計(jì)10.2.2概念結(jié)構(gòu)設(shè)計(jì)1010.2.3邏輯結(jié)構(gòu)設(shè)計(jì)

根據(jù)概念模型向邏輯模型轉(zhuǎn)換原則，進(jìn)銷存管理系統(tǒng)的邏輯結(jié)構(gòu)為：（1）一個(gè)實(shí)體轉(zhuǎn)換成一個(gè)關(guān)系，所以應(yīng)有：供應(yīng)商表（供應(yīng)商編號(hào)、供應(yīng)商名稱、地址、電話、郵編、聯(lián)系人）?？蛻舯恚蛻艟幪?hào)、客戶名稱、地址、電話、郵編）。倉庫表（倉庫編號(hào)、地址、電話、成立時(shí)間）。商品表（商品編號(hào)、商品名稱、單價(jià)、規(guī)格）。（2）一對(duì)多的聯(lián)系，可以將一方的主鍵傳到多方，成為多方的非主屬性，還可以形成新的關(guān)系，雙方的主鍵和聯(lián)系本身的屬性作為新關(guān)系的屬性。1710.2.3邏輯結(jié)構(gòu)設(shè)計(jì)根據(jù)概念模型向邏輯模型轉(zhuǎn)換原則10.2.3邏輯結(jié)構(gòu)設(shè)計(jì)

由于倉庫與商品之間是一對(duì)多的聯(lián)系，而且聯(lián)系本身有屬性，所以可以形成新關(guān)系：商品庫存表（商品編號(hào)、倉庫編號(hào)、入/出編號(hào)、入/出標(biāo)志、時(shí)間、數(shù)量、經(jīng)手人）由于在同一商品同一天可能多次存入同一倉庫，因此商品編號(hào)、倉庫編號(hào)和時(shí)間三者聯(lián)合也很難惟一標(biāo)識(shí)一次出入庫行為，因此將入/出庫編號(hào)作為商品庫存表的主鍵，修改后的商品庫存表為：商品庫存表（商品編號(hào)、倉庫編號(hào)、入/出編號(hào)、入/出標(biāo)志、時(shí)間、數(shù)量、經(jīng)手人）（3）多對(duì)多的聯(lián)系形成一個(gè)新的關(guān)系，多方的主鍵和聯(lián)系本身的屬性作為新關(guān)系的屬性，多方的主鍵聯(lián)合作主鍵。由于商品和供應(yīng)商、客戶與商品之間是多對(duì)多的聯(lián)系，所以形成新的關(guān)系：商品銷售表（商品編號(hào)、客戶編號(hào)、售/退編號(hào)、售/退標(biāo)志、時(shí)間、變化數(shù)量、金額、經(jīng)手人）

1810.2.3邏輯結(jié)構(gòu)設(shè)計(jì)由于倉庫與商品之間是一對(duì)多的聯(lián)10.2.3邏輯結(jié)構(gòu)設(shè)計(jì)

商品供應(yīng)表（商品編號(hào)、供應(yīng)商編號(hào)、進(jìn)/退編號(hào)、進(jìn)/退標(biāo)志、時(shí)間、變化數(shù)量、金額、經(jīng)手人）由于同一客戶同一天可能購買多次同一商品，因此商品編號(hào)、客戶編號(hào)和時(shí)間三者聯(lián)合也很難惟一標(biāo)識(shí)一次銷售行為，而售/退編號(hào)是不可以重復(fù)的有規(guī)律的編號(hào)，因此將售/退編號(hào)作為商品銷售表的主鍵，修改后的商品銷售表為：商品銷售表（商品編號(hào)、客戶編號(hào)、售/退編號(hào)、售/退標(biāo)志、時(shí)間、變化數(shù)量、金額、經(jīng)手人）由于同一供應(yīng)商同一天可能多次供應(yīng)同一商品，因此商品編號(hào)、供應(yīng)商編號(hào)和時(shí)間三者聯(lián)合也很難惟一標(biāo)識(shí)一次供應(yīng)行為，而進(jìn)/退編號(hào)是不可以重復(fù)的有規(guī)律的編號(hào)，因此將進(jìn)/退編號(hào)作為商品供應(yīng)表的主鍵，修改后的商品供應(yīng)表為：商品供應(yīng)表（商品編號(hào)、供應(yīng)商編號(hào)、進(jìn)/退編號(hào)、進(jìn)/退標(biāo)志、時(shí)間、變化數(shù)量、金額、經(jīng)手人）1910.2.3邏輯結(jié)構(gòu)設(shè)計(jì)商品供應(yīng)表（商品編號(hào)、供應(yīng)商編10.2.4在Oracle中創(chuàng)建表10.2.2概念結(jié)構(gòu)設(shè)計(jì)10.2.3邏輯結(jié)構(gòu)設(shè)計(jì)10.2.1需求分析10.2系統(tǒng)數(shù)據(jù)庫設(shè)計(jì)10.2.5在Oracle中創(chuàng)建索引10.2.6在Oracle中創(chuàng)建視圖10.2.7在Oracle中創(chuàng)建序列10.2.9在Oracle中創(chuàng)建存儲(chǔ)過程或存儲(chǔ)函數(shù)10.2.8在Oracle中創(chuàng)建觸發(fā)器2010.2.4在Oracle中創(chuàng)建表10.2.2概念結(jié)10.2.4在Oracle數(shù)據(jù)庫中創(chuàng)建表

CREATETABLESupplier

(

Supplier_ID

INT

PRIMARYKEY,Supplier_Name

VARCHAR2(250)

NOTNULL,Address

VARCHAR2(250)

NOTNULL,Phone

VARCHAR2(25),PostalCode

VARCHAR2(10),

ConstactPerson

VARCHAR2(20));

2110.2.4在Oracle數(shù)據(jù)庫中創(chuàng)建表CREATE10.2.4在Oracle數(shù)據(jù)庫中創(chuàng)建表

CREATETABLECustomer

(Customer_ID

INT

PRIMARYKEY,Customer_Name

VARCHAR2(250)

NOTNULL,Address

VARCHAR2(250)

NOTNULL,Phone

VARCHAR2(25)

,

PostalCode

VARCHAR2(10)

);2210.2.4在Oracle數(shù)據(jù)庫中創(chuàng)建表CREATE10.2.4在Oracle數(shù)據(jù)庫中創(chuàng)建表

CREATETABLEStoreHouse

(

StoreHouse_ID

INTPRIMARYKEY,

Address

VARCHAR2(250)

NOTNULL,

Phone

VARCHAR2(25),

CreateDate

DATE);

2310.2.4在Oracle數(shù)據(jù)庫中創(chuàng)建表CREATE10.2.4在Oracle數(shù)據(jù)庫中創(chuàng)建表

CREATETABLEProductClass

(ProductClass_ID

INTPRIMARYKEY,

ProductClass_Name

VARCHAR2(30)

NOTNULL,

ProductSpec_ID

VARCHAR2(30)

NOTNULL,

Price

NUMBER(5,2)

NOT

NULL

);

2410.2.4在Oracle數(shù)據(jù)庫中創(chuàng)建表CREATE10.2.4在Oracle數(shù)據(jù)庫中創(chuàng)建表

CREATETABLEP_C

(ProductClass_ID

INTREFERENCESProductClass(ProductClass_ID),

Customer_ID

INT

REFERENCESCustomer(Customer_ID),

PC_IDINTPRIMARYKEY,

PC_markINT,

PC_timeDATE,

PC_amountNUMBER(6,2),

PC_sumNUMBER(10,2)

,PC_workerCHAR(10));

2510.2.4在Oracle數(shù)據(jù)庫中創(chuàng)建表CREATE10.2.4在Oracle數(shù)據(jù)庫中創(chuàng)建表

為了實(shí)時(shí)統(tǒng)計(jì)每種商品銷售情況，增加商品銷售統(tǒng)計(jì)表。CREATETABLEC_ALL

/*商品銷售統(tǒng)計(jì)表*/(

ProductClass_ID

INTREFERENCESProductClass(ProductClass_ID),

CA_amountNUMBER(6,2),

CA_sumNUMBER(10,2),PRIMARYKEY(ProductClass_ID)

);

2610.2.4在Oracle數(shù)據(jù)庫中創(chuàng)建表為了實(shí)時(shí)統(tǒng)計(jì)每10.2.4在Oracle數(shù)據(jù)庫中創(chuàng)建表

CREATETABLEP_S

(ProductClass_ID

INTREFERENCESProductClass(ProductClass_ID),

Supplier_ID

INT

REFERENCESSupplier(Supplier_ID),

PS_IDINTPRIMARYKEY,

PS_markINT,

PS_timeDATE,

PS_amountNUMBER(6,2),

PS_sumNUMBER(10,2)

,PC_workerCHAR(10));

2710.2.4在Oracle數(shù)據(jù)庫中創(chuàng)建表CREATE10.2.4在Oracle數(shù)據(jù)庫中創(chuàng)建表

為了實(shí)時(shí)統(tǒng)計(jì)每種商品供應(yīng)情況，增加商品供應(yīng)統(tǒng)計(jì)表。CREATETABLES_ALL

/*商品供應(yīng)統(tǒng)計(jì)表*/(

ProductClass_ID

INTREFERENCESProductClass(ProductClass_ID),

Supplier_ID

INT

REFERENCESSupplier(Supplier_ID),

SA_amountNUMBER(6,2),

SA_sumNUMBER(10,2),PRIMARYKEY(Supplier_ID)

);

2810.2.4在Oracle數(shù)據(jù)庫中創(chuàng)建表為了實(shí)時(shí)統(tǒng)計(jì)每10.2.4在Oracle數(shù)據(jù)庫中創(chuàng)建表

CREATETABLEP_H

(ProductClass_ID

INTREFERENCESProductClass(ProductClass_ID),

StoreHouse_ID

INT

REFERENCESStoreHouse(StoreHouse_ID),

PH_IDINTPRIMARYKEY,

PH_markINT,

PH_timeDATE,

PH_amountNUMBER(6,2),

PC_workerCHAR(10));

2910.2.4在Oracle數(shù)據(jù)庫中創(chuàng)建表CREATE10.2.4在Oracle數(shù)據(jù)庫中創(chuàng)建表

為了實(shí)時(shí)統(tǒng)計(jì)每種商品庫存情況，增加商品庫存統(tǒng)計(jì)表。CREATETABLEH_ALL

/*商品庫存統(tǒng)計(jì)表*/(

ProductClass_ID

INTREFERENCESProductClass(ProductClass_ID),

StoreHouse_ID

INT

REFERENCESStoreHouse(StoreHouse_ID),

HA_amountNUMBER(6,2),PRIMARYKEY(StoreHouse_ID));為了系統(tǒng)功能更加完善，可以增加其他統(tǒng)計(jì)表等，請(qǐng)讀者自行分析。3010.2.4在Oracle數(shù)據(jù)庫中創(chuàng)建表為了實(shí)時(shí)統(tǒng)計(jì)每10.2.5在Oracle中創(chuàng)建索引10.2.2概念結(jié)構(gòu)設(shè)計(jì)10.2.3邏輯結(jié)構(gòu)設(shè)計(jì)10.2.4在Oracle中創(chuàng)建表10.2系統(tǒng)數(shù)據(jù)庫設(shè)計(jì)10.2.1需求分析10.2.6在Oracle中創(chuàng)建視圖10.2.7在Oracle中創(chuàng)建序列10.2.9在Oracle中創(chuàng)建存儲(chǔ)過程或存儲(chǔ)函數(shù)10.2.8在Oracle中創(chuàng)建觸發(fā)器3110.2.5在Oracle中創(chuàng)建索引10.2.2概念10.2.5在Oracle數(shù)據(jù)庫中創(chuàng)建索引

在進(jìn)銷存系統(tǒng)中經(jīng)常需要查詢數(shù)據(jù)，因此可以根據(jù)查詢要求在表中創(chuàng)建索引。例如，在商品名稱上創(chuàng)建索引。CREATEINDEXp_name_indexONProductCLass(ProductClass_name);為了系統(tǒng)功能更加完善，可以增加其他索引，請(qǐng)讀者自行分析。

3210.2.5在Oracle數(shù)據(jù)庫中創(chuàng)建索引在進(jìn)銷存系統(tǒng)10.2.6在Oracle中創(chuàng)建視圖10.2.2概念結(jié)構(gòu)設(shè)計(jì)10.2.3邏輯結(jié)構(gòu)設(shè)計(jì)10.2.4在Oracle中創(chuàng)建表10.2系統(tǒng)數(shù)據(jù)庫設(shè)計(jì)10.2.5在Oracle中創(chuàng)建索引10.2.1需求分析10.2.7在Oracle中創(chuàng)建序列10.2.9在Oracle中創(chuàng)建存儲(chǔ)過程或存儲(chǔ)函數(shù)10.2.8在Oracle中創(chuàng)建觸發(fā)器3310.2.6在Oracle中創(chuàng)建視圖10.2.2概念10.2.6在Oracle數(shù)據(jù)庫中創(chuàng)建視圖

為了提高系統(tǒng)的隱蔽性和查詢方便可以創(chuàng)建一些視圖。CREATEVIEWP_S

_VIEW/*商品供應(yīng)視圖*/ASSELECTSupplier..Supplier_ID,

Supplier_Name

,

ProductClass.ProductClass_ID,ProductClass_Name

FROMSupplier

,ProductClass,P_SWHEREProductClass.ProductClass_ID=

P_S.ProductClass_IDANDSupplier.Supplier_ID=P_S.Supplier_IDWITHREADONLY;為了系統(tǒng)功能更加完善，可以增加其他視圖，請(qǐng)讀者自行分析。

3410.2.6在Oracle數(shù)據(jù)庫中創(chuàng)建視圖為了提高系統(tǒng)10.2.7在Oracle中創(chuàng)建序列10.2.2概念結(jié)構(gòu)設(shè)計(jì)10.2.3邏輯結(jié)構(gòu)設(shè)計(jì)10.2.4在Oracle中創(chuàng)建表10.2系統(tǒng)數(shù)據(jù)庫設(shè)計(jì)10.2.5在Oracle中創(chuàng)建索引10.2.6在Oracle中創(chuàng)建視圖10.2.1需求分析10.2.9在Oracle中創(chuàng)建存儲(chǔ)過程或存儲(chǔ)函數(shù)10.2.8在Oracle中創(chuàng)建觸發(fā)器3510.2.7在Oracle中創(chuàng)建序列10.2.2概念10.2.7在Oracle數(shù)據(jù)庫中創(chuàng)建序列

在進(jìn)銷存系統(tǒng)中商品銷售過程中售/退編號(hào)是不可以重復(fù)的有規(guī)律的編號(hào)，且作為主鍵，其值可以用序列來填充。CREATESEQUENCEP_C_SQEINCREMENTBY1STARTWITH1MAXVALUE100000MINVALUE1CYCLECACHE20ORDER;同理，在商品供應(yīng)過程中進(jìn)/退編號(hào)是不可以重復(fù)的有規(guī)律的編號(hào)，且作為主鍵，其值也可以用序列來填充。為了系統(tǒng)功能更加完善，可以增加其他序列，請(qǐng)讀者自行分析。

3610.2.7在Oracle數(shù)據(jù)庫中創(chuàng)建序列在進(jìn)銷存系統(tǒng)10.2.8在Oracle中創(chuàng)建觸發(fā)器10.2.2概念結(jié)構(gòu)設(shè)計(jì)10.2.3邏輯結(jié)構(gòu)設(shè)計(jì)10.2.4在Oracle中創(chuàng)建表10.2系統(tǒng)數(shù)據(jù)庫設(shè)計(jì)10.2.5在Oracle中創(chuàng)建索引10.2.6在Oracle中創(chuàng)建視圖10.2.7在Oracle中創(chuàng)建序列10.2.9在Oracle中創(chuàng)建存儲(chǔ)過程或存儲(chǔ)函數(shù)10.2.1需求分析3710.2.8在Oracle中創(chuàng)建觸發(fā)器10.2.2概10.2.8在Oracle數(shù)據(jù)庫中創(chuàng)建觸發(fā)器

在進(jìn)銷存系統(tǒng)中有時(shí)某種商品的庫存量太少會(huì)影響銷售活動(dòng)，所以當(dāng)某商品的庫存量太少時(shí)系統(tǒng)應(yīng)自動(dòng)告警，這時(shí)需要用到觸發(fā)器。CREATETRIGGERP_H_TRIGGER/******為商品庫存統(tǒng)計(jì)表創(chuàng)建缺貨觸發(fā)器，數(shù)量小于1000時(shí)觸發(fā)********/AFTERDELETEORUPDATEONH_ALLFOREACHROWWHEN(HA_amount<1000)BEGINDBMS_OUTPUT.PUT_LINE('編號(hào)為：'||:NEW.ProductClass_ID||'的商品在'||:NEW.StoreHouse_ID||'號(hào)倉庫庫存總量小于1000');END;為了系統(tǒng)功能更加完善，可以增加其他觸發(fā)器，請(qǐng)讀者自行分析。

3810.2.8在Oracle數(shù)據(jù)庫中創(chuàng)建觸發(fā)器在進(jìn)銷存系10.2.9在Oracle中創(chuàng)建存儲(chǔ)過程或存儲(chǔ)函數(shù)10.2.2概念結(jié)構(gòu)設(shè)計(jì)10.2.3邏輯結(jié)構(gòu)設(shè)計(jì)10.2.4在Oracle中創(chuàng)建表10.2系統(tǒng)數(shù)據(jù)庫設(shè)計(jì)10.2.5在Oracle中創(chuàng)建索引10.2.6在Oracle中創(chuàng)建視圖10.2.7在Oracle中創(chuàng)建序列10.2.1需求分析10.2.8在Oracle中創(chuàng)建觸發(fā)器3910.2.9在Oracle中創(chuàng)建存儲(chǔ)過程或存儲(chǔ)函數(shù)在Oracle數(shù)據(jù)庫中創(chuàng)建存儲(chǔ)過程或函數(shù)

在進(jìn)銷存系統(tǒng)中有時(shí)需要統(tǒng)計(jì)某供應(yīng)商供應(yīng)的商品名稱列表，這時(shí)可以使用存儲(chǔ)過程。CREATEPROCEDUREP_S_PRO(s1Supplier.Supplier_ID%TYPE)ASp1ProductClass.ProductClass_ID%TYPE;p2ProductClass.ProductClass_Name

%TYPE;Cursorc1ISSELECTProductClass_IDFROMP_SWHERESupplier_ID=s1;BEGINOPENc1;LOOPFETCHc1intop1;EXITWHENc1%NOTFOUND;

4010.2.9在Oracle數(shù)據(jù)庫中創(chuàng)建存儲(chǔ)過程或函數(shù)在10.2.9在Oracle數(shù)據(jù)庫中創(chuàng)建存儲(chǔ)過程或函數(shù)

SELECTProductClass_Name

intop2FROMProductClassWHEREProductClass_ID=p1;DBMS_OUTPUT.PUT_LINE(p2);ENDLOOP;CLOSEc1;END;為了系統(tǒng)功能更加完善，可以增加其他存儲(chǔ)過程或函數(shù)，請(qǐng)讀者自行分析。

4110.2.9在Oracle數(shù)據(jù)庫中創(chuàng)建存儲(chǔ)過程或函數(shù)S10.3連接Oracle數(shù)據(jù)庫10.2系統(tǒng)數(shù)據(jù)庫設(shè)計(jì)10.1系統(tǒng)功能分析10.4小結(jié)第10章Oracle綜合實(shí)例

4210.3連接Oracle數(shù)據(jù)庫10.2系統(tǒng)數(shù)據(jù)庫設(shè)計(jì)110.3.1利用Java連接Oracle10.3連接Oracle數(shù)據(jù)庫10.3.2利用PowerbBilder連接Oracle4310.3.1利用Java連接Oracle10.3連接10.3.1Java連接Oracle

Java利用JDBC（JavaDatabaseConnectivity，Java數(shù)據(jù)庫連接）連接Oracle數(shù)據(jù)庫。假設(shè)使用Oracle安裝過程中創(chuàng)建的Oracle數(shù)據(jù)庫“DBSEPI”。JDBC是一種用于執(zhí)行SQL語句的JavaAPI，可以為多種關(guān)系數(shù)據(jù)庫提供統(tǒng)一的訪問接口。JDBC由一組用Java語言編寫的類與接口組成，通過調(diào)用這些類和接口所提供的方法，用戶能夠以一致的方式連接多種不同的數(shù)據(jù)庫系統(tǒng)（如Access、Server2000、Oracle、Sybase等），進(jìn)而使用標(biāo)準(zhǔn)的SQL語言來存取數(shù)據(jù)庫中的數(shù)據(jù)，而不必再為每一種數(shù)據(jù)庫系統(tǒng)編寫不同的Java程序代主鍵。JDBC提供的主要功能如下：（1）同一個(gè)數(shù)據(jù)庫建立連接；（2）向數(shù)據(jù)庫發(fā)送SQL語句；（3）處理數(shù)據(jù)庫返回的結(jié)果。4410.3.1Java連接OracleJava利用JD1.設(shè)置數(shù)據(jù)源

打開操作系統(tǒng)的控制面板，雙擊“數(shù)據(jù)源（ODBC）”圖標(biāo)，選擇“用戶DSN”選項(xiàng)卡，如圖10-3所示，畫面中顯示了已有的數(shù)據(jù)源名稱。

451.設(shè)置數(shù)據(jù)源打開操作系統(tǒng)的控制面板，雙擊“數(shù)據(jù)源（O1.設(shè)置數(shù)據(jù)源

單擊“添加”按鈕，出現(xiàn)建立新數(shù)據(jù)來源對(duì)話框，如圖10-4所示。

461.設(shè)置數(shù)據(jù)源單擊“添加”按鈕，出現(xiàn)建立新數(shù)據(jù)來源對(duì)話1.設(shè)置數(shù)據(jù)源

選中“OracleinOraHome92”作為新數(shù)據(jù)源的驅(qū)動(dòng)程序，單擊“完成”按鈕，出現(xiàn)OracleODBC驅(qū)動(dòng)器配置窗口，如圖10-5所示。

471.設(shè)置數(shù)據(jù)源選中“OracleinOraHome1.設(shè)置數(shù)據(jù)源

在“DataSourceName”編輯框輸入數(shù)據(jù)源的名稱，假設(shè)為

“dbspei”，在“TNSServerName”編輯框輸入Oracle數(shù)據(jù)庫的名稱，假設(shè)使用Oracle安裝過程中創(chuàng)建的數(shù)據(jù)庫“dbsepi”，在“User”編輯框輸入用戶名“usepi”，單擊“TestConnection”按鈕，出現(xiàn)OracleODBC驅(qū)動(dòng)連接窗口，如圖10-6所示。

481.設(shè)置數(shù)據(jù)源在“DataSourceName”編1.設(shè)置數(shù)據(jù)源

在“ServerName”編輯框顯示數(shù)據(jù)庫的名稱“dbspei”，在“UseName”編輯框顯示用戶名“usepi”，在“Password”編輯框輸入密碼“usepi”，單擊“OK”按鈕，如果配置正確，則出現(xiàn)連接成功窗口，如圖10-7所示。

491.設(shè)置數(shù)據(jù)源在“ServerName”編輯框顯示數(shù)2.建立JDBC-ODBC橋接器

在和某一特定數(shù)據(jù)庫建立連接之前，必須首先加載一種可用的JDBC驅(qū)動(dòng)程序。這需要使用java.sql包中的下列方法來加載JDBC驅(qū)動(dòng)程序，一般的使用格式如下：Class.forName("DriverName");“DriverName”是要加載的JDBC驅(qū)動(dòng)程序名稱。驅(qū)動(dòng)程序名稱根據(jù)數(shù)據(jù)庫廠商提供的JDBC驅(qū)動(dòng)程序的種類來確定。由于本例采用的是Oracle數(shù)據(jù)庫，所以加載Oracle數(shù)據(jù)庫驅(qū)動(dòng)程序的方法為：Class.forName("sun.jdbc.odbc.JdbcOdbcDriver");502.建立JDBC-ODBC橋接器在和某一特定數(shù)據(jù)庫建立3.連接數(shù)據(jù)庫

創(chuàng)建和指定數(shù)據(jù)庫的連接需要使用DriverManager類的getConnection()方法，其一般的使用格式如下：Connectionconn=DriverManager.getConnection(URL,user,password);該方法返回的是一個(gè)Connection對(duì)象。這里的URL是一個(gè)字符串，代表了將要連接的數(shù)據(jù)源，即具體的數(shù)據(jù)庫位置。不同的JDBC驅(qū)動(dòng)程序其URL是不同的。本例為：Connectionconn=DriverManager.getConnection("jdbc:odbc:dbsepi","usepi","usepi");

513.連接數(shù)據(jù)庫創(chuàng)建和指定數(shù)據(jù)庫的連接需要使用Drive4.向數(shù)據(jù)庫發(fā)送SQL語句

在與某個(gè)特定數(shù)據(jù)庫建立連接之后，這個(gè)連接會(huì)話就可以用于發(fā)送SQL語句。在發(fā)送SQL語句之前，必須創(chuàng)建一個(gè)Statement類的對(duì)象，該對(duì)象負(fù)責(zé)將SQL語句發(fā)送給數(shù)據(jù)庫。如果SQL語句運(yùn)行后產(chǎn)生結(jié)果集，Statement對(duì)象會(huì)將結(jié)果集返回給一個(gè)ResultSet對(duì)象。例如：創(chuàng)建Statement對(duì)象是使用Connection接口的createStatement()方法來實(shí)現(xiàn)的：Statementsmt=conn.createStatement();Statement對(duì)象創(chuàng)建好之后，就可以使用該對(duì)象的executeQuery()方法來執(zhí)行數(shù)據(jù)庫查詢語句，executeQuery()方法返回一個(gè)ResultSet類的對(duì)象，它包含了SQL查詢語句執(zhí)行的結(jié)果。例如：ResultSetrs=smt.executeQuery("SELECT*FROMstudent");

524.向數(shù)據(jù)庫發(fā)送SQL語句在與某個(gè)特定數(shù)據(jù)庫建立連接之5.處理查詢結(jié)果

一個(gè)ResultSet對(duì)象包含了執(zhí)行某個(gè)SQL語句后滿足條件的所有的行，它還提供了對(duì)這些行的訪問，用戶可以通過一組get方法來訪問當(dāng)前行的不同列。通常結(jié)果集的形式是一張帶有表頭和相應(yīng)數(shù)值的表，例如：

while(rs.next()){ out.print(""+rs.getString(1)); out.print(""+rs.getString(2)); out.print(""+rs.getString(3)+"<br>");

535.處理查詢結(jié)果一個(gè)ResultSet對(duì)象包含了執(zhí)行某6.關(guān)閉創(chuàng)建的各個(gè)對(duì)象

一個(gè)Statement對(duì)象在同一時(shí)間只能打開一個(gè)結(jié)果集，所以如果在同一個(gè)Statement對(duì)象中運(yùn)行下一條SQL語句時(shí)，第一條SQL語句生成的ResultSet對(duì)象就被自動(dòng)關(guān)閉了。當(dāng)然也可以通過調(diào)用ResultSet接口的close()方法來手工關(guān)閉。關(guān)閉Statement對(duì)象和Connection對(duì)象可以分別使用各自的close()方法，例如：rs.close(); stmt.close(); conn.close();

546.關(guān)閉創(chuàng)建的各個(gè)對(duì)象一個(gè)Statement對(duì)象在同一10.3.2利用PowerbBilder連接Oracle10.3連接Oracle數(shù)據(jù)庫10.3.1利用Java連接Oracle5510.3.2利用PowerbBilder連接Oracle10.3.2PowerBuilder連接Oracle

PowerBuilder連接Oracle數(shù)據(jù)庫有兩種方法，一是使用專用接口，二是使用通用接口。假設(shè)使用Oracle安裝過程中創(chuàng)建的Oracle數(shù)據(jù)庫“DBSEPI”。1.通過專用接口連接數(shù)據(jù)庫PowerBuilder的專用接口都是為網(wǎng)絡(luò)上的大型數(shù)據(jù)庫提供的，如Sybase、Oracle、Informix、SQLServer等。因此，首先要調(diào)通網(wǎng)絡(luò)，其次安裝數(shù)據(jù)庫和專用接口軟件，接下來配置數(shù)據(jù)庫的描述文件（Profile）。通過專用接口連接數(shù)據(jù)庫需要首先創(chuàng)建數(shù)據(jù)庫描述文件，然后利用數(shù)據(jù)庫描述文件即可連接數(shù)據(jù)庫了。Oracle數(shù)據(jù)庫是大型分布式數(shù)據(jù)庫，在PowerBuilder中通過專用接口“O90Oracle9.0.1”連接Oracle9數(shù)據(jù)庫。連接步驟共分為創(chuàng)建數(shù)據(jù)庫描述文件、連接數(shù)據(jù)庫兩步。

5610.3.2PowerBuilder連接OracleP10.3.2PowerBuilder連接Oracle

（1）創(chuàng)建數(shù)據(jù)庫描述文件首先進(jìn)入數(shù)據(jù)庫描述文件配置窗口，如圖10-8所示。

5710.3.2PowerBuilder連接Oracle（10.3.2PowerBuilder連接Oracle

在數(shù)據(jù)庫描述文件窗口中選中“O90Oracle9.0.1”，單擊鼠標(biāo)右鍵，在快捷菜單中選中“NewProfile”，出現(xiàn)數(shù)據(jù)庫描述文件配置窗口，選中“Connection”選項(xiàng)卡，如圖10-9所示。

5810.3.2PowerBuilder連接Oracle在10.3.2PowerBuilder連接Oracle

在“ProfileName”編輯框輸入用戶命名的數(shù)據(jù)庫描述文件名，假設(shè)為

“dbsepi”，在“Server”編輯框輸入數(shù)據(jù)庫名，假設(shè)使用Oracle安裝過程中創(chuàng)建的數(shù)據(jù)庫“dbsepi”，在“LoginID”編輯框輸入用戶名“usepi”，在“Password”編輯框輸入該用戶的密碼“usepi”，在“Connectas”下拉列表中選擇該用戶的級(jí)別“SYSDBA”，其他選項(xiàng)不做修改，單擊“OK”按鈕即可保存此數(shù)據(jù)庫描述文件，同時(shí)此數(shù)據(jù)庫描述文件“dbsepi”出現(xiàn)在數(shù)據(jù)庫畫板對(duì)象窗口中“O90Oracle9.0.1”的級(jí)聯(lián)列表中，如圖10-10所示。

5910.3.2PowerBuilder連接Oracle在10.3.2PowerBuilder連接Oracle

（2）連接數(shù)據(jù)庫在數(shù)據(jù)庫畫板中通過不同的數(shù)據(jù)庫描述文件連接不同的數(shù)據(jù)庫。選中要連接的數(shù)據(jù)庫描述文件“dbsepi”，單擊數(shù)據(jù)庫畫筆圖標(biāo)或單擊鼠標(biāo)右鍵，在快捷菜單中選中“Connect”即可連接指定的數(shù)據(jù)庫了。

連接成功后，數(shù)據(jù)庫描述文件“dbsepi”就顯示在“O90Oracle9.0.1”級(jí)聯(lián)列表中。

6010.3.2PowerBuilder連接Oracle（2.通過ODBC通用接口連接數(shù)據(jù)庫

PowerBuilder通過ODBC通用接口可以連接各種數(shù)據(jù)庫，如Access、Foxpro、Excel、Oracle、Syabse等。在PowerBuilder中通過通用接口ODBC連接Oracle數(shù)據(jù)庫的步驟共分為配置ODBC數(shù)據(jù)源、創(chuàng)建數(shù)據(jù)庫描述文件、連接數(shù)據(jù)庫三步。

612.通過ODBC通用接口連接數(shù)據(jù)庫PowerBuild2.通過ODBC通用接口連接數(shù)據(jù)庫

（1）配置ODBC數(shù)據(jù)源在數(shù)據(jù)庫畫板對(duì)象窗口中依次單擊“ODBODBC”→“Utilities”左邊的“+”號(hào)，使之展開，如圖10-11所示。

622.通過ODBC通用接口連接數(shù)據(jù)庫（1）配置ODBC數(shù)2.通過ODBC通用接口連接數(shù)據(jù)庫

選中“ODBCAdministrator”，雙擊鼠標(biāo)左鍵，再參照?qǐng)D10-3、10-4、10-5、10-6、10-7完成ODBC數(shù)據(jù)源的配置。

（2）創(chuàng)建數(shù)據(jù)庫描述文件在圖10-11所示的數(shù)據(jù)庫畫板對(duì)象窗口中選中“ODBODBC”，單擊鼠標(biāo)右鍵，在快捷菜單中選中“NewProfile”，出現(xiàn)數(shù)據(jù)庫描述文件配置窗口，選中“Connection”選項(xiàng)卡，如圖10-12所示。

632.通過ODBC通用接口連接數(shù)據(jù)庫選中“ODBCAd2.通過ODBC通用接口連接數(shù)據(jù)庫

在“ProfileName”編輯框輸入數(shù)據(jù)庫描述文件名

“pb_oracle”，從“DataSource”下拉列表中選擇ODBC數(shù)據(jù)源“dbsepi”，在“UserID”編輯框輸入用戶名“usepi”，在“Password”編輯框輸入該用戶的密碼“usepi”，單擊“OK”按鈕即可完成數(shù)據(jù)庫描述文件的建立。數(shù)據(jù)庫描述文件創(chuàng)建成功后，該描述文件即出現(xiàn)在數(shù)據(jù)庫畫板對(duì)象窗口中“ODBODBC”的級(jí)聯(lián)列表中。（3）連接數(shù)據(jù)庫在數(shù)據(jù)庫畫板對(duì)象窗口中，選中要連接的數(shù)據(jù)庫描述文件“pb_oracle”，單擊數(shù)據(jù)庫畫筆圖標(biāo)或單擊鼠標(biāo)右鍵，在快捷菜單中選中“Connect”即可連接指定的數(shù)據(jù)庫。

642.通過ODBC通用接口連接數(shù)據(jù)庫在“Profile3.編寫應(yīng)用對(duì)象Open事件腳本

在PowerBuilder的應(yīng)用對(duì)象的OPEN事件中編寫如下腳本：SQLCA.DBMS="ODBC"SQLCA.AutoCommit=FalseSQLCA.DBPARM="CONNECTSTRING='DSN=pb_oracle;UID=usepi;PWD=usepi'"http://此處連接的數(shù)據(jù)源為“pb_oracle”connect;ifSQLCA.SQLCODE=-1thenmessagebox('提示信息','數(shù)據(jù)庫連接失??！')elsemessagebox('提示信息','數(shù)據(jù)庫連接成功！')endif

653.編寫應(yīng)用對(duì)象Open事件腳本在PowerBuild4.向數(shù)據(jù)庫發(fā)送SQL語句

PowerBuilder一般通過數(shù)據(jù)窗口對(duì)象與數(shù)據(jù)庫連接，顯示數(shù)據(jù)查詢、更新的結(jié)果，數(shù)據(jù)窗口對(duì)象是一可視化編輯數(shù)據(jù)的窗口，所以應(yīng)先創(chuàng)建一數(shù)據(jù)窗口對(duì)象（例如dw_1），利用dw_1.settransobject(SQLCA)語句連接數(shù)據(jù)庫，利用dw_1.retrieve()來獲取數(shù)據(jù)庫中的數(shù)據(jù)，利用dw_1.insertrow()插入一行數(shù)據(jù)，在dw_1中可以向空行中添加或修改原有數(shù)據(jù)，利用dw_1.update()和COMMIT命令保存數(shù)據(jù)，利用dw_1.deleterow()刪除一行數(shù)據(jù)。

664.向數(shù)據(jù)庫發(fā)送SQL語句PowerBuilder一般5.斷開數(shù)據(jù)庫，釋放資源

在PowerBuilder的應(yīng)用對(duì)象的CLOSE事件中編寫如下腳本：disconnect;//斷開與數(shù)據(jù)庫的連接關(guān)于具體編程已不屬于本教材的內(nèi)容，請(qǐng)讀者查閱其他相關(guān)參考資料。

675.斷開數(shù)據(jù)庫，釋放資源在PowerBuilder的應(yīng)10.4小結(jié)10.2系統(tǒng)數(shù)據(jù)庫設(shè)計(jì)10.3連接Oracle數(shù)據(jù)庫10.1系統(tǒng)功能分析第10章Oracle綜合實(shí)例

6810.4小結(jié)10.2系統(tǒng)數(shù)據(jù)庫設(shè)計(jì)10.3連接Or10.4小結(jié)

（1）數(shù)據(jù)庫設(shè)計(jì)包含需求分析、概念結(jié)構(gòu)設(shè)計(jì)、邏輯結(jié)構(gòu)設(shè)計(jì)、物理結(jié)構(gòu)設(shè)計(jì)、數(shù)據(jù)庫實(shí)施、數(shù)據(jù)庫運(yùn)行與維護(hù)六步，其中前三步是數(shù)據(jù)庫設(shè)計(jì)成功與否的關(guān)鍵。（2）數(shù)據(jù)庫設(shè)計(jì)過程中需求分析形成應(yīng)用系統(tǒng)的數(shù)據(jù)字典，包含數(shù)據(jù)結(jié)構(gòu)、數(shù)據(jù)項(xiàng)、處理要求等；概念結(jié)構(gòu)設(shè)計(jì)形成應(yīng)用系統(tǒng)的全局E-R圖；邏輯結(jié)構(gòu)設(shè)計(jì)形成由E-R圖根據(jù)轉(zhuǎn)換規(guī)則形成的數(shù)據(jù)庫的模式；在實(shí)際應(yīng)用設(shè)計(jì)過程中經(jīng)常需要根據(jù)實(shí)際情況為應(yīng)用系統(tǒng)添加視圖、序列、存儲(chǔ)過程、存儲(chǔ)函數(shù)或觸發(fā)器等數(shù)據(jù)庫對(duì)象，以使數(shù)據(jù)庫設(shè)計(jì)更加完善。（3）利用Java和PowerBuilder連接Oracle數(shù)據(jù)庫的方法。

6910.4小結(jié)（1）數(shù)據(jù)庫設(shè)計(jì)包含需求分析、概念結(jié)構(gòu)設(shè)計(jì)數(shù)據(jù)庫原理與應(yīng)用第10章Oracle綜合實(shí)例70數(shù)據(jù)庫原理與應(yīng)用第10章Oracle綜合實(shí)例1學(xué)習(xí)目標(biāo)●掌握數(shù)據(jù)庫設(shè)計(jì)的基本步驟和方法?！裾莆绽肑ava和PowerBuilder連接Oracle數(shù)據(jù)庫的方法。71學(xué)習(xí)目標(biāo)●掌握數(shù)據(jù)庫設(shè)計(jì)的基本步驟和方法。210.1系統(tǒng)功能分析10.2系統(tǒng)數(shù)據(jù)庫設(shè)計(jì)10.3連接Oracle數(shù)據(jù)庫10.4小結(jié)第10章Oracle綜合實(shí)例

7210.1系統(tǒng)功能分析10.2系統(tǒng)數(shù)據(jù)庫設(shè)計(jì)10.310.1系統(tǒng)功能分析

本章以進(jìn)銷存系統(tǒng)為例來介紹數(shù)據(jù)庫設(shè)計(jì)的過程，系統(tǒng)功能分析是在匯總系統(tǒng)開發(fā)的總體任務(wù)的基礎(chǔ)上完成。本系統(tǒng)中的進(jìn)銷存管理系統(tǒng)需要完成商品管理、倉庫管理、客戶管理、供應(yīng)商管理、庫存管理、商品銷售管理、商品供應(yīng)管理等多個(gè)功能，滿足企業(yè)高效率的需求，系統(tǒng)功能模塊如圖10-1所示。

進(jìn)銷存系統(tǒng)商品管理模塊客戶管理模塊供應(yīng)商管理模塊倉庫管理模塊商品銷售模塊商品供應(yīng)模塊商品庫存模塊7310.1系統(tǒng)功能分析本章以進(jìn)銷存系統(tǒng)為例來介紹數(shù)據(jù)庫設(shè)10.2系統(tǒng)數(shù)據(jù)庫設(shè)計(jì)10.1系統(tǒng)功能分析10.3連接Oracle數(shù)據(jù)庫10.4小結(jié)第10章Oracle綜合實(shí)例

7410.2系統(tǒng)數(shù)據(jù)庫設(shè)計(jì)10.1系統(tǒng)功能分析10.310.2.1需求分析10.2.2概念結(jié)構(gòu)設(shè)計(jì)10.2.3邏輯結(jié)構(gòu)設(shè)計(jì)10.2.4在Oracle中創(chuàng)建表10.2系統(tǒng)數(shù)據(jù)庫設(shè)計(jì)10.2.5在Oracle中創(chuàng)建索引10.2.6在Oracle中創(chuàng)建視圖10.2.7在Oracle中創(chuàng)建序列10.2.9在Oracle中創(chuàng)建存儲(chǔ)過程或存儲(chǔ)函數(shù)10.2.8在Oracle中創(chuàng)建觸發(fā)器7510.2.1需求分析10.2.2概念結(jié)構(gòu)設(shè)計(jì)需求分析

系統(tǒng)開發(fā)的目標(biāo)是實(shí)現(xiàn)企業(yè)進(jìn)銷存管理的系統(tǒng)化、規(guī)劃化和自動(dòng)化，基本要求如下：（1）客戶管理：對(duì)整個(gè)銷售過程進(jìn)行管理，對(duì)銷售對(duì)象進(jìn)行統(tǒng)計(jì)。（2）供應(yīng)商管理：對(duì)整個(gè)供應(yīng)過程進(jìn)行管理，對(duì)供應(yīng)對(duì)象進(jìn)行統(tǒng)計(jì)。（3）倉庫管理：對(duì)倉庫信息進(jìn)行管理。（4）商品管理：對(duì)系統(tǒng)銷售/供應(yīng)過程中的商品進(jìn)行管理。（5）進(jìn)/退貨管理：對(duì)整個(gè)供應(yīng)過程中發(fā)生的進(jìn)貨/退貨進(jìn)行跟蹤統(tǒng)計(jì)。（6）售/退貨管理：對(duì)整個(gè)銷售過程中發(fā)生的進(jìn)貨/退貨進(jìn)行跟蹤統(tǒng)計(jì)。（7）庫存管理：對(duì)商品入/出倉庫進(jìn)行跟蹤統(tǒng)計(jì)。7610.2.1需求分析系統(tǒng)開發(fā)的目標(biāo)是實(shí)現(xiàn)企業(yè)進(jìn)銷存管理10.2.1需求分析

通過對(duì)企業(yè)進(jìn)銷存管理的內(nèi)容和數(shù)據(jù)流程分析，系統(tǒng)的數(shù)據(jù)結(jié)構(gòu)如表10-1所示。

數(shù)據(jù)結(jié)構(gòu)名含義組成客戶信息（Customer）對(duì)銷售客戶進(jìn)行記錄、統(tǒng)計(jì)客戶編碼、客戶名稱、地址、電話、郵編供應(yīng)商信息（Supplier）對(duì)供應(yīng)商進(jìn)行記錄、統(tǒng)計(jì)供應(yīng)商編碼、供應(yīng)商名稱、地址、電話、郵編、聯(lián)系人倉庫信息（StoreHouse）對(duì)倉庫進(jìn)行記錄、統(tǒng)計(jì)倉庫編碼、地址、電話、成立時(shí)間商品信息（ProductClass）對(duì)商品進(jìn)行記錄、統(tǒng)計(jì)商品編號(hào)、商品名稱、單價(jià)、規(guī)格7710.2.1需求分析通過對(duì)企業(yè)進(jìn)銷存管理的內(nèi)容和數(shù)據(jù)流10.2.1需求分析

系統(tǒng)的數(shù)據(jù)項(xiàng)如表10-2～10-5所示。

數(shù)據(jù)項(xiàng)名含義類型長(zhǎng)度約束Supplier_ID

供應(yīng)商編號(hào)INT

惟一標(biāo)識(shí)Supplier_Name供應(yīng)商名稱VARCHAR2250非空Address地址VARCHAR2250非空Phone電話VARCHAR225

PostalCode郵編VARCHAR210

ConstactPerson聯(lián)系人VARCHAR220

7810.2.1需求分析系統(tǒng)的數(shù)據(jù)項(xiàng)如表10-2～10-510.2.1需求分析

數(shù)據(jù)項(xiàng)名含義類型長(zhǎng)度約束Customer_ID

客戶編號(hào)INT

惟一標(biāo)識(shí)Customer_Name客戶名稱VARCHAR2250非空Address地址VARCHAR2250非空Phone電話VARCHAR225

PostalCode郵編VARCHAR210

7910.2.1需求分析數(shù)據(jù)項(xiàng)名含義類型長(zhǎng)度約束Custo10.2.1需求分析

數(shù)據(jù)項(xiàng)名含義類型長(zhǎng)度約束StoreHouse_ID

倉庫編號(hào)INT

惟一標(biāo)識(shí)Address地址VARCHAR2250非空Phone電話VARCHAR225

CreateDate成立時(shí)間DATE

8010.2.1需求分析數(shù)據(jù)項(xiàng)名含義類型長(zhǎng)度約束Store10.2.1需求分析

數(shù)據(jù)項(xiàng)名含義類型長(zhǎng)度小數(shù)位約束ProductClass_ID

商品編號(hào)INT

惟一標(biāo)識(shí)ProductClass_Name商品名稱VARCHAR230

非空ProductSpec_ID商品規(guī)格VARCHAR230

非空Price

單價(jià)NUMBER52非空8110.2.1需求分析數(shù)據(jù)項(xiàng)名含義類型長(zhǎng)度小數(shù)位約束Pr10.2.1需求分析

在實(shí)際應(yīng)用中，系統(tǒng)中存在著各種聯(lián)系：（1）一個(gè)倉庫可以存儲(chǔ)多種商品，一種商品只能存儲(chǔ)在一個(gè)倉庫，而且商品入庫或出庫時(shí)記錄是入庫還是出庫，還記錄時(shí)間、數(shù)量、經(jīng)手人；（2）一個(gè)供應(yīng)商可以供應(yīng)多種商品，一種商品可以由多個(gè)供應(yīng)商供應(yīng)，而且供應(yīng)商供應(yīng)商品時(shí)記錄是進(jìn)貨還是退貨，還記錄時(shí)間、變化數(shù)量、金額、經(jīng)手人；（3）一個(gè)客戶可以購買多種商品，一種商品可以由多個(gè)客戶購買，而且客戶購買或退商品時(shí)記錄是賣貨還是退貨，還記錄時(shí)間、變化數(shù)量、金額、經(jīng)手人。因此，倉庫與商品之間是一對(duì)多的聯(lián)系，客戶與商品之間是多對(duì)多的聯(lián)系，供應(yīng)商與商品之間是多對(duì)多的聯(lián)系。由于在一個(gè)商品同一天可能多次存入同一倉庫，因此增加入/出庫編號(hào)以標(biāo)識(shí)每一次庫存操作；同理，由于一個(gè)客戶同一天可能購買多次同一商品，因此增加售/退編號(hào)以標(biāo)識(shí)每一次銷售操作；一個(gè)供應(yīng)商同一天可能多次供應(yīng)同一商品，因此增加進(jìn)/退編號(hào)以標(biāo)識(shí)每一次供應(yīng)操作。8210.2.1需求分析在實(shí)際應(yīng)用中，系統(tǒng)中存在著各種聯(lián)系10.2.2概念結(jié)構(gòu)設(shè)計(jì)10.2.1需求分析10.2.3邏輯結(jié)構(gòu)設(shè)計(jì)10.2.4在Oracle中創(chuàng)建表10.2系統(tǒng)數(shù)據(jù)庫設(shè)計(jì)10.2.5在Oracle中創(chuàng)建索引10.2.6在Oracle中創(chuàng)建視圖10.2.7在Oracle中創(chuàng)建序列10.2.9在Oracle中創(chuàng)建存儲(chǔ)過程或存儲(chǔ)函數(shù)10.2.8在Oracle中創(chuàng)建觸發(fā)器8310.2.2概念結(jié)構(gòu)設(shè)計(jì)10.2.1需求分析概念結(jié)構(gòu)設(shè)計(jì)

根據(jù)需求分析，進(jìn)銷存管理系統(tǒng)的E-R圖如圖10-2所示。

8410.2.2概念結(jié)構(gòu)設(shè)計(jì)根據(jù)需求分析，進(jìn)銷存管理系統(tǒng)的10.2.3邏輯結(jié)構(gòu)設(shè)計(jì)10.2.2概念結(jié)構(gòu)設(shè)計(jì)10.2.1需求分析10.2.4在Oracle中創(chuàng)建表10.2系統(tǒng)數(shù)據(jù)庫設(shè)計(jì)10.2.5在Oracle中創(chuàng)建索引10.2.6在Oracle中創(chuàng)建視圖10.2.7在Oracle中創(chuàng)建序列10.2.9在Oracle中創(chuàng)建存儲(chǔ)過程或存儲(chǔ)函數(shù)10.2.8在Oracle中創(chuàng)建觸發(fā)器8510.2.3邏輯結(jié)構(gòu)設(shè)計(jì)10.2.2概念結(jié)構(gòu)設(shè)計(jì)1010.2.3邏輯結(jié)構(gòu)設(shè)計(jì)

根據(jù)概念模型向邏輯模型轉(zhuǎn)換原則，進(jìn)銷存管理系統(tǒng)的邏輯結(jié)構(gòu)為：（1）一個(gè)實(shí)體轉(zhuǎn)換成一個(gè)關(guān)系，所以應(yīng)有：供應(yīng)商表（供應(yīng)商編號(hào)、供應(yīng)商名稱、地址、電話、郵編、聯(lián)系人）。客戶表（客戶編號(hào)、客戶名稱、地址、電話、郵編）。倉庫表（倉庫編號(hào)、地址、電話、成立時(shí)間）。商品表（商品編號(hào)、商品名稱、單價(jià)、規(guī)格）。（2）一對(duì)多的聯(lián)系，可以將一方的主鍵傳到多方，成為多方的非主屬性，還可以形成新的關(guān)系，雙方的主鍵和聯(lián)系本身的屬性作為新關(guān)系的屬性。8610.2.3邏輯結(jié)構(gòu)設(shè)計(jì)根據(jù)概念模型向邏輯模型轉(zhuǎn)換原則10.2.3邏輯結(jié)構(gòu)設(shè)計(jì)

由于倉庫與商品之間是一對(duì)多的聯(lián)系，而且聯(lián)系本身有屬性，所以可以形成新關(guān)系：商品庫存表（商品編號(hào)、倉庫編號(hào)、入/出編號(hào)、入/出標(biāo)志、時(shí)間、數(shù)量、經(jīng)手人）由于在同一商品同一天可能多次存入同一倉庫，因此商品編號(hào)、倉庫編號(hào)和時(shí)間三者聯(lián)合也很難惟一標(biāo)識(shí)一次出入庫行為，因此將入/出庫編號(hào)作為商品庫存表的主鍵，修改后的商品庫存表為：商品庫存表（商品編號(hào)、倉庫編號(hào)、入/出編號(hào)、入/出標(biāo)志、時(shí)間、數(shù)量、經(jīng)手人）（3）多對(duì)多的聯(lián)系形成一個(gè)新的關(guān)系，多方的主鍵和聯(lián)系本身的屬性作為新關(guān)系的屬性，多方的主鍵聯(lián)合作主鍵。由于商品和供應(yīng)商、客戶與商品之間是多對(duì)多的聯(lián)系，所以形成新的關(guān)系：商品銷售表（商品編號(hào)、客戶編號(hào)、售/退編號(hào)、售/退標(biāo)志、時(shí)間、變化數(shù)量、金額、經(jīng)手人）

8710.2.3邏輯結(jié)構(gòu)設(shè)計(jì)由于倉庫與商品之間是一對(duì)多的聯(lián)10.2.3邏輯結(jié)構(gòu)設(shè)計(jì)

商品供應(yīng)表（商品編號(hào)、供應(yīng)商編號(hào)、進(jìn)/退編號(hào)、進(jìn)/退標(biāo)志、時(shí)間、變化數(shù)量、金額、經(jīng)手人）由于同一客戶同一天可能購買多次同一商品，因此商品編號(hào)、客戶編號(hào)和時(shí)間三者聯(lián)合也很難惟一標(biāo)識(shí)一次銷售行為，而售/退編號(hào)是不可以重復(fù)的有規(guī)律的編號(hào)，因此將售/退編號(hào)作為商品銷售表的主鍵，修改后的商品銷售表為：商品銷售表（商品編號(hào)、客戶編號(hào)、售/退編號(hào)、售/退標(biāo)志、時(shí)間、變化數(shù)量、金額、經(jīng)手人）由于同一供應(yīng)商同一天可能多次供應(yīng)同一商品，因此商品編號(hào)、供應(yīng)商編號(hào)和時(shí)間三者聯(lián)合也很難惟一標(biāo)識(shí)一次供應(yīng)行為，而進(jìn)/退編號(hào)是不可以重復(fù)的有規(guī)律的編號(hào)，因此將進(jìn)/退編號(hào)作為商品供應(yīng)表的主鍵，修改后的商品供應(yīng)表為：商品供應(yīng)表（商品編號(hào)、供應(yīng)商編號(hào)、進(jìn)/退編號(hào)、進(jìn)/退標(biāo)志、時(shí)間、變化數(shù)量、金額、經(jīng)手人）8810.2.3邏輯結(jié)構(gòu)設(shè)計(jì)商品供應(yīng)表（商品編號(hào)、供應(yīng)商編10.2.4在Oracle中創(chuàng)建表10.2.2概念結(jié)構(gòu)設(shè)計(jì)10.2.3邏輯結(jié)構(gòu)設(shè)計(jì)10.2.1需求分析10.2系統(tǒng)數(shù)據(jù)庫設(shè)計(jì)10.2.5在Oracle中創(chuàng)建索引10.2.6在Oracle中創(chuàng)建視圖10.2.7在Oracle中創(chuàng)建序列10.2.9在Oracle中創(chuàng)建存儲(chǔ)過程或存儲(chǔ)函數(shù)10.2.8在Oracle中創(chuàng)建觸發(fā)器8910.2.4在Oracle中創(chuàng)建表10.2.2概念結(jié)10.2.4在Oracle數(shù)據(jù)庫中創(chuàng)建表

CREATETABLESupplier

(

Supplier_ID

INT

PRIMARYKEY,Supplier_Name

VARCHAR2(250)

NOTNULL,Address

VARCHAR2(250)

NOTNULL,Phone

VARCHAR2(25),PostalCode

VARCHAR2(10),

ConstactPerson

VARCHAR2(20));

9010.2.4在Oracle數(shù)據(jù)庫中創(chuàng)建表CREATE10.2.4在Oracle數(shù)據(jù)庫中創(chuàng)建表

CREATETABLECustomer

(Customer_ID

INT

PRIMARYKEY,Customer_Name

VARCHAR2(250)

NOTNULL,Address

VARCHAR2(250)

NOTNULL,Phone

VARCHAR2(25)

,

PostalCode

VARCHAR2(10)

);9110.2.4在Oracle數(shù)據(jù)庫中創(chuàng)建表CREATE10.2.4在Oracle數(shù)據(jù)庫中創(chuàng)建表

CREATETABLEStoreHouse

(

StoreHouse_ID

INTPRIMARYKEY,

Address

VARCHAR2(250)

NOTNULL,

Phone

VARCHAR2(25),

CreateDate

DATE);

9210.2.4在Oracle數(shù)據(jù)庫中創(chuàng)建表CREATE10.2.4在Oracle數(shù)據(jù)庫中創(chuàng)建表

CREATETABLEProductClass

(ProductClass_ID

INTPRIMARYKEY,

ProductClass_Name

VARCHAR2(30)

NOTNULL,

ProductSpec_ID

VARCHAR2(30)

NOTNULL,

Price

NUMBER(5,2)

NOT

NULL

);

9310.2.4在Oracle數(shù)據(jù)庫中創(chuàng)建表CREATE10.2.4在Oracle數(shù)據(jù)庫中創(chuàng)建表

CREATETABLEP_C

(ProductClass_ID

INTREFERENCESProductClass(ProductClass_ID),

Customer_ID

INT

REFERENCESCustomer(Customer_ID),

PC_IDINTPRIMARYKEY,

PC_markINT,

PC_timeDATE,

PC_amountNUMBER(6,2),

PC_sumNUMBER(10,2)

,PC_workerCHAR(10));

9410.2.4在Oracle數(shù)據(jù)庫中創(chuàng)建表CREATE10.2.4在Oracle數(shù)據(jù)庫中創(chuàng)建表

為了實(shí)時(shí)統(tǒng)計(jì)每種商品銷售情況，增加商品銷售統(tǒng)計(jì)表。CREATETABLEC_ALL

/*商品銷售統(tǒng)計(jì)表*/(

ProductClass_ID

INTREFERENCESProductClass(ProductClass_ID),

CA_amountNUMBER(6,2),

CA_sumNUMBER(10,2),PRIMARYKEY(ProductClass_ID)

);

9510.2.4在Oracle數(shù)據(jù)庫中創(chuàng)建表為了實(shí)時(shí)統(tǒng)計(jì)每10.2.4在Oracle數(shù)據(jù)庫中創(chuàng)建表

CREATETABLEP_S

(ProductClass_ID

INTREFERENCESProductClass(ProductClass_ID),

Supplier_ID

INT

REFERENCESSupplier(Supplier_ID),

PS_IDINTPRIMARYKEY,

PS_markINT,

PS_timeDATE,

PS_amountNUMBER(6,2),

PS_sumNUMBER(10,2)

,PC_workerCHAR(10));

9610.2.4在Oracle數(shù)據(jù)庫中創(chuàng)建表CREATE10.2.4在Oracle數(shù)據(jù)庫中創(chuàng)建表

為了實(shí)時(shí)統(tǒng)計(jì)每種商品供應(yīng)情況，增加商品供應(yīng)統(tǒng)計(jì)表。CREATETABLES_ALL

/*商品供應(yīng)統(tǒng)計(jì)表*/(

ProductClass_ID

INTREFERENCESProductClass(ProductClass_ID),

Supplier_ID

INT

REFERENCESSupplier(Supplier_ID),

SA_amountNUMBER(6,2),

SA_sumNUMBER(10,2),PRIMARYKEY(Supplier_ID)

);

9710.2.4在Oracle數(shù)據(jù)庫中創(chuàng)建表為了實(shí)時(shí)統(tǒng)計(jì)每10.2.4在Oracle數(shù)據(jù)庫中創(chuàng)建表

CREATETABLEP_H

(ProductClass_ID

INTREFERENCESProductClass(ProductClass_ID),

StoreHouse_ID

INT

REFERENCESStoreHouse(StoreHouse_ID),

PH_IDINTPRIMARYKEY,

PH_markINT,

PH_timeDATE,

PH_amountNUMBER(6,2),

PC_workerCHAR(10));

9810.2.4在Oracle數(shù)據(jù)庫中創(chuàng)建表CREATE10.2.4在Oracle數(shù)據(jù)庫中創(chuàng)建表

為了實(shí)時(shí)統(tǒng)計(jì)每種商品庫存情況，增加商品庫存統(tǒng)計(jì)表。CREATETABLEH_ALL

/*商品庫存統(tǒng)計(jì)表*/(

ProductClass_ID

INTREFERENCESProductClass(ProductClass_ID),

StoreHouse_ID

INT

REFERENCESStoreHouse(StoreHouse_ID),

HA_amountNUMBER(6,2),PRIMARYKEY(StoreHouse_ID));為了系統(tǒng)功能更加完善，可以增加其他統(tǒng)計(jì)表等，請(qǐng)讀者自行分析。9910.2.4在Oracle數(shù)據(jù)庫中創(chuàng)建表為了實(shí)時(shí)統(tǒng)計(jì)每10.2.5在Oracle中創(chuàng)建索引10.2.2概念結(jié)構(gòu)設(shè)計(jì)10.2.3邏輯結(jié)構(gòu)設(shè)計(jì)10.2.4在Oracle中創(chuàng)建表10.2系統(tǒng)數(shù)據(jù)庫設(shè)計(jì)10.2.1需求分析10.2.6在Oracle中創(chuàng)建視圖10.2.7在Oracle中創(chuàng)建序列10.2.9在Oracle中創(chuàng)建存儲(chǔ)過程或存儲(chǔ)函數(shù)10.2.8在Oracle中創(chuàng)建觸發(fā)器10010.2.5在Oracle中創(chuàng)建索引10.2.2概念10.2.5在Oracle數(shù)據(jù)庫中創(chuàng)建索引

在進(jìn)銷存系統(tǒng)中經(jīng)常需要查詢數(shù)據(jù)，因此可以根據(jù)查詢要求在表中創(chuàng)建索引。例如，在商品名稱上創(chuàng)建索引。CREATEINDEXp_name_indexONProductCLass(ProductClass_name);為了系統(tǒng)功能更加完善，可以增加其他索引，請(qǐng)讀者自行分析。

10110.2.5在Oracle數(shù)據(jù)庫中創(chuàng)建索引在進(jìn)銷存系統(tǒng)10.2.6在Oracle中創(chuàng)建視圖10.2.2概念結(jié)構(gòu)設(shè)計(jì)10.2.3邏輯結(jié)構(gòu)設(shè)計(jì)10.2.4在Oracle中創(chuàng)建表10.2系統(tǒng)數(shù)據(jù)庫設(shè)計(jì)10.2.5在Oracle中創(chuàng)建索引10.2.1需求分析10.2.7在Oracle中創(chuàng)建序列10.2.9在Oracle中創(chuàng)建存儲(chǔ)過程或存儲(chǔ)函數(shù)10.2.8在Oracle中創(chuàng)建觸發(fā)器10210.2.6在Oracle中創(chuàng)建視圖10.2.2概念10.2.6在Oracle數(shù)據(jù)庫中創(chuàng)建視圖

為了提高系統(tǒng)的隱蔽性和查詢方便可以創(chuàng)建一些視圖。CREATEVIEWP_S

_VIEW/*商品供應(yīng)視圖*/ASSELECTSupplier..Supplier_ID,

Supplier_Name

,

ProductClass.ProductClass_ID,ProductClass_Name

FROMSupplier

,ProductClass,P_SWHEREProductClass.ProductClass_ID=

P_S.ProductClass_IDANDSupplier.Supplier_ID=P_S.Supplier_IDWITHREADONLY;為了系統(tǒng)功能更加完善，可以增加其他視圖，請(qǐng)讀者自行分析。

10310.2.6在Oracle數(shù)據(jù)庫中創(chuàng)建視圖為了提高系統(tǒng)10.2.7在Oracle中創(chuàng)建序列10.2.2概念結(jié)構(gòu)設(shè)計(jì)10.2.3邏輯結(jié)構(gòu)設(shè)計(jì)10.2.4在Oracle中創(chuàng)建表10.2系統(tǒng)數(shù)據(jù)庫設(shè)計(jì)10.2.5在Oracle中創(chuàng)建索引10.2.6在Oracle中創(chuàng)建視圖10.2.1需求