廣醫(yī)信息系統(tǒng)oracle作業(yè)

1、案例1：供應(yīng)商供應(yīng)藥品數(shù)拯庫中包括以下3張表，表結(jié)構(gòu)參見表3.1、 表 3.2、表 3.3。3.1 供貨商表(Provider)牛段中文名字段名類型空值缶注供應(yīng)商代碼PrwiderCodeChar(4)No主碼供應(yīng)商PrwidcrNeinicCheir(60)No拼咅簡碼PyCodcChart 10)Ycg地址AddrccoChar(50)Ycq電話TelChar(l 5)Ycq郵編ZipCT6)YeoEmailEmailCheir(30)Yeo聯(lián)系人RelationChar(8)Yeo表 3.2 藥品表(Medicine)牛段中立名牛段名類型空值缶注藥品代碼McdicincCodeChar

2、No主碼藥品名林McdicincNanieVArchar(50)No拼音簡碼PyCodcCliar(lO)Yeo劑2!DoceiCcFoniiChar Yeo規(guī)梧StandardChar(】5)Yes單位UnitCliar(lO)Yes批號BatchNiuuberChar(20)Yeo生產(chǎn)日期Prodi xctionDatcSnAollDatetim eYeo失效日期ErqirationDateSuiallDatetiui eYeo藥品類別CategoryCllar(lO)Yeo中成藥、西藥等匡保YBChar(2)Yeo默認(rèn)為“否”表3.3供應(yīng)表（PM）字段中文名字段名類顯空值缶注藥品代碼Me

3、dicincCodcCT5)No主碼供應(yīng)商代碼ProvidcrCodeCheir(4)No土碼供應(yīng)日期PMDatcSmallDatetiui cNo主碼價格PriceMoneyYec數(shù)量QytIntYec各張表的數(shù)據(jù)示例參見表3.4.表3.5.表3.6。表3.4供貨商表（Provider）實例數(shù)據(jù)ProvidcrCo deProviderIJauxePyCodeAddrecoTelZipEninilReleitionS001河北東風(fēng)藥業(yè)Hbdfyy河北省永年縣城西0310-6806999057150hbdf張三S002浙遼康思mZzkeb杭州市商新技術(shù)開發(fā)區(qū)0571-8777481131004

4、5ttiu_8512126.co1U李四S003青島魯漣藥業(yè)Qdljyy青島市北區(qū)辿安路2660001375685404®qq.coux王五S004哈藥軻藥Hyzy喑爾濱市南崗區(qū)學(xué)府路150000hh王六表3.5藥品表（Medicine）實例數(shù)據(jù)KledicineCo deMcdicineNeuncPyCoduDoceiCeForUiStandardBatchNixuibrrProdi xctionDa teEnjnrationDatecategoryYB10001小兒感WWfiieguild賴粒劑12g/«Z530204052009-01-012012-12-31中成藥是

5、10002維生素C銀翹片W3ocy*qP片劑49.5ui£/片Z410223182010-01-012012-06-30中成藥是10003清熱鮮毒膠査Qrjdjn膠抵劑0.3g/fiZ200546632012-06-302014-06-30中成藥是10004小柴胡沖劑'ichcj10g/«Z440207092012-12-012014-08-30中成藥是20006新康泰克膠抵劑0.25g/«iH200104302011-02-252013.08-25是20007護(hù)彤ht賴粒劑2&/«H230226132004-10-072007-10-0

6、7西藥否20008救急散Jjc散劑i.5g/MZ110201382012-01-012015-01-01面藥否表3.6供應(yīng)表（PM）實例數(shù)據(jù)McdicineCodeProviderCo dePriceQytPMDate10002S0013.001502010-02-0110003S00124.002302012-08-0110004S0019.005002013.01-0110004S0029.001002013.02-0220004S00235.002002012-01-0120008SOOS701002012-04-01首先：為藥庫數(shù)據(jù)庫通過DECA創(chuàng)建了數(shù)據(jù)庫YK,在YK數(shù)據(jù)庫里創(chuàng)建用戶

7、數(shù)據(jù)表空間， 建立表空間P_M及臨時表空間為P_M_TEMP,創(chuàng)建用戶MDAdmin、用戶密碼為123456。 授予角色CONNECT及DBA,完成了藥庫數(shù)據(jù)庫建設(shè)的框架工作，為創(chuàng)建數(shù)據(jù)庫對象做 好準(zhǔn)備，過程如下：(1) 創(chuàng)建用戶表空間P_M,用來存放藥庫的數(shù)據(jù)表；createtablespaceP_m2datafiler C: app Adminis t r at or or adat a YI< p_m 1 * size 100m;(2) 創(chuàng)建臨時表空間P_M_TEMP;Createtemporarytablespacep_m2_temptempfile,C:appAdministr

8、atororadataYKp_m2_temp.dbf size 100m;(3) 創(chuàng)建用戶MDADMIN并指定表空間；create user MDADMIN identified by 123456 default tablespace p_m2 temporary tablespace p_m2_temp;<4)為用戶授予權(quán)限；grant connect to MDADMIN;grant dba to MDADMIN;(5)通過SQL DEVELOPER建立用戶MDAdmin到Y(jié)K數(shù)據(jù)庫的連接，更直觀 的對數(shù)據(jù)庫進(jìn)行操作。再充成以下工作：1) 創(chuàng)建藥品表(Medicine),藥品代碼是

9、主碼，批號取值唯一，在創(chuàng)建表的過程中使用check約束和默認(rèn)(DEFAULT)約束。create table Medicine(MedicineCode CHAR(5) primary key not null,MedicineName Varchar(50) not null,BatchNumber CHAR(20) unique,YB char default'否；CHECK (YB ='是S YB-否')，PyCocle CHAR(IO),DosageForm CHAR(6),Standard CHAR(15),Unit CHAR(10),ProductionDa

10、te Date,ExpirationDate Date,Category CHAR(10);2) 創(chuàng)建供貨商表(Provider),主碼建為表級約束。create table Provider(ProviderCode CHAR(4) primary key not null,ProviderName CHAR(60) not null,PyCode CHAR(IO) null,Address CHAR(50) null,Tel CHAR(15) null,Zip CHAR(6) null,Email CHAR(30) null,Relation CHAR(8) null,FOREIGN KE

11、Y (ProviderCode) REFERENCES Provider(ProviderCode);3) 建立供應(yīng)表(PM),包含主碼、外碼，均為表級約束。create table PM(MedicineCode CHAR not null,ProviderCode char(4) not null,PMDate Date not null,Price number null,Qyt Int null,primary key (M edicineCocle, Provid er Code, PMDate),foreign key (MedicineCode) references Medic

12、ine(MedicineCocle), foreign key (ProviderCode) references Provider(ProviderCode);4) 向Medicine表增加“使用說明(Memo) ”列，其數(shù)據(jù)類型為字符串類型。ALTER TABLE Medicine ADD Memo CHAR;5) 將Medicine表的Memo列刪除。ALTER TABLE Medicine DROP column Memo;6) 將Provider表的Address列的數(shù)據(jù)類型由Cliar(50)改為Char(60)oALTER TABLE Provider modify Addres

13、s char(60);7) 要求PM表的Qyt取值在0至1000之間。ALTER TABLE PM ADD constraint Qyt check(Qyt>0 and Qyt<1000);8) 要求供應(yīng)表(PM)中的PMDate默認(rèn)為當(dāng)前時間。ALTER TABLE PM modify PMDate DATE default sysdate;9) 刪除PM表的默認(rèn)約束DF_PMDateoALTER TABLE PM drop constraint DF_PMDate;10) 査詢所有藥品的藥品代碼、藥品名稱。select MedicineCode,MeclicineName fr

14、om Medicine;11) 査詢所有藥品的劑型、藥品名稱、藥品類別。select DosageForm,MedicineName,Category from Medicine;12) 查詢所有藥品的詳細(xì)記錄。select * from Medicine;13) 查詢所有藥品的藥品代碼、藥品名稱、生產(chǎn)年份。select MedicineCode,MedicineName,ProductionDate from Medicine;14) 查詢藥品名稱、批號，生產(chǎn)年份、保質(zhì)期。可使用列別名改變查詢結(jié)果的列標(biāo)題。 selectMedicineName,BatchNumber,ProductionD

15、ate,ExpirationDate-ProductionDateAS ”保質(zhì)期” from Medicine;15）査詢供應(yīng)了藥品的供應(yīng)商代碼。select ProviderCode from PM;16）查詢中成藥類的藥品名稱。select MedicineName from Medicine where category中成藥'；17）查詢所有過期藥品的藥品代碼、藥品名稱、失效日期。select MedicineCode,MedicineName,ExpirationDate from Medicine whereExpirationDate<sysdate;18）査詢供應(yīng)

16、價格在10元以內(nèi)的藥品代碼。select MedicineCode from PM where Price<10;19）查詢生產(chǎn)日期在2011-06-01與2012-06-01之間的藥品信息。select * from Medicine where ProductionDate> = to.dateC2011 -06-0 r/YVYY-MM-DD1）andProductionDate<to_date（*2012-06-01 *, 'YYYY-MM-DD1）；20）査詢生產(chǎn)日期不在2011-06-01與2011-06-01之間的藥品代碼、藥品名稱、批號。select M

17、edicineCodeMeclicineNameatchNumber from Medicine where ProductionDate not between to_date（*2011 -06-011, 'YYYY-MM-DD1） and to_dateC2012-06-0 lVYYYY-MM-DD*）；21）查詢片劑、散劑和顆粒劑的藥品代碼、藥品名稱。select MedicineCode,MedicineName from Medicine where DosageForm=*片劑' or DosageForm=,散劑or DosageForm='顆粒劑：22

18、）査詢既不片劑、也不是散劑的藥品代碼、藥品名稱。select MedicineCode,MedicineName from Medicine where DosageForm!=*片劑' and DosageForm!=*散劑：23）查詢所有拼音簡碼以x開頭的藥品代碼、藥品名稱。select Medicin eCode,MedicineName from Medicine where PyCode like *xj;24）査詢郵編以“3”開頭倒數(shù)第二個字符為“4”的供應(yīng)商信息。select * from Provider where Zip like '3_%4_；25）査詢郵

19、箱為“ttm_8512”的供應(yīng)商信息。select * from Provider where Email=*ttm_8512 *;26）查詢郵箱為以“tt_”開頭，且倒數(shù)第二個字符為“6”的供應(yīng)商信息。select * from Provider where Email like *tt_%6J escape27）查詢沒有提供聯(lián)系電話的供應(yīng)商基本信息。select * from Provider where Tel is null;28）査詢所有有朕系電話的供應(yīng)商基本信息。select * from Provider where Tel is not null;29）查詢2012年以后生產(chǎn)的

20、劑型為“膠囊”的藥品名稱。select MedicineCode from Medicine where DosageForm=,膠囊劑'and ProductionDate>=to_date（*2012-01-01 '/YYYY-MM-DDj;30）查詢供應(yīng)藥品代碼為的供應(yīng)商代碼，供應(yīng)日期，價格，數(shù)量査詢結(jié)果按價格降序排列。 select MedicineCoderoviderCodejPMDatePriceQyt from PM order by Pricedesc;31）查詢藥品基本信息，查詢結(jié)果按照藥品類別升序排列，同一類別按照生產(chǎn)日期降序排 列。select *

21、 from Medicine order by Category,ProductionDate desc;32) 查詢藥品的總個數(shù)。select count(*) from PM;33) 查詢供應(yīng)藥品的供應(yīng)商個數(shù)。select count(distinct ProviderCode) from PM;34) 計算“10004"號藥品的平均供應(yīng)價格。select avg(Price) from PM where MeclicineCode= 10004;35) 計算“10004”號藥品的最高供應(yīng)價格。select max(Price) from PM where MedicineCod

22、e= 10004;36) 查詢“S001”供應(yīng)商供應(yīng)的藥品總數(shù)童。select sum(Qyt) from PM wiiere ProviderCode=*S001'37) 求每個供應(yīng)商供應(yīng)的藥品個數(shù)。select ProviderCode,sum(Qyt) from PM group by ProviderCode;38) 査詢供應(yīng)了 3種以上藥品的供應(yīng)商代碼。select ProviderCode from PM group by ProviderCode having count(*)>3;39) 查詢每個供應(yīng)商及其供應(yīng)藥品的情況。selectProvider.*,PM.*

23、fromProvider,PMwhereProvider. ProviderCode=PM .ProviderCode;40) 查詢與小兒感冒顆粒相同劑型的藥品信息。select * from Medicine where DosageForm in(select DosageForm from Medicine where MedicineName= */J兒感冒顆粒 1；41) 右外連接：查詢所有藥品被供應(yīng)的惜況。select * from Medicine right outer join PMon(Medicine.MedicineCode=PM Medicin eCode);42)

24、查詢維生素C銀翹片的供應(yīng)情況select PM.MedicineCode,ProviderCode,Price,Qyt,PMDate from Medicine,PM where Medicine.MedicineCode=PM.MedicineCode and MedicineName= *維生素C 銀翹片'；43) 査詢每個供應(yīng)商供應(yīng)的藥品代碼，藥品名稱、價格、數(shù)量、供應(yīng)商名稱、供應(yīng)年份。select PM.MedicineCode,Price,Qyt,PMDate,MedicineName,ProviderName fromM edicin e, Pro vid er, PM w

25、iiere Provider ProviderCode=PM .ProviderCode andM edicine. M edicineCocle=P M MedicineCode;44) 查詢”浙江康恩貝”供應(yīng)的藥品代碼和藥品名稱。select MedicineCode,MedicineName from Medicine where MedicineCode in(select MedicineCode from PM where ProviderCode in(select ProviderCode from Provider where ProviderName=,浙遼康恩貝'

26、)；45) 找出每種藥品供應(yīng)價格超出它的供應(yīng)平均價格的供應(yīng)商代碼(相關(guān)于查詢)。select MedicineCode,ProviderCode from PM x where Price>=(select avg(Price)from PM y where y.MedicineCode=x.MedicineCode);46) 査詢S001供應(yīng)商供應(yīng)的藥品名稱(用EXISTS謂詞)。select MedicineName from Medicine where EXISTS(select * from PM whereMedicineCode=Medicine.MedicineCode

27、and Pro vid er Cod e=1 SO 0 T)；47) 查詢顆粒劑的藥品及中成藥，用集合操場完成。select * from Medicine where DosageForm='顆粒劑 Linion select * from Medicine where category中成藥'；48）査詢顆粒劑的藥品與中成藥的交集。select * from Medicine where DosageForm=*顆粒劑'intersect select * from Medicine where category中成藥'；49）查詢顆粒劑的藥品與中成藥的差集

28、。select * from Medicine where DosageForm=,顆粒劑'minus select * from Medicine wliere category中成藥'；將（藥品代碼：10007,藥品名稱：釜香正氣水，拼音簡碼：hxzqs,劑型：口服液，規(guī)格： ml/支，批號：Z51021352,生產(chǎn)日期：-12-20,失效日期：-12-20,藥品類別：中成 藥，是否醫(yī)保：是）插入到Medicine表中。insertintoMedicine（MedicineCode,MedicineName,PyCode,DosageForm,Standard,Batcli

29、Nu mberfroductionDatqExpirationDate,category,YB） values（ 10007；套香 正氣水 'JixzqsY口服液,'ml/支TZ51021352；to_date（'1220TMMDD）,to_date（1220TMMDD）,中成藥 丫 是50）增加一條供應(yīng)商記錄（供應(yīng)商代碼：S005 ,供應(yīng)商名稱：華西制藥，其他信息暫時 未知）insert into Provider（ProviderCode,ProviderName） values （'S005J華西制藥; 對每一種藥品，求供應(yīng)商供應(yīng)的平均價格，并把結(jié)果存入數(shù)

30、據(jù)庫。（先建立一張表： CREATE TABLE AVG_Medicine_Price（MedicineCode Char（10）,Avg_Price number）;Insert into AVG_Medicine_Price（MeclicineCode,Avg_Price） selectMedicineCode,avg（Price） from PM group by MedicineCode;51）將供應(yīng)商S001的聯(lián)系人改為彭大。update Provider set Relation彭大where ProviclerCode=*SOO 1 *;52）將所有藥品的供應(yīng)價格提高5%。upd

31、ate PM set Price=Price*1.05;53）將浙江康恩貝供應(yīng)的藥品數(shù)量直零。update PM set Qyt=O where ProviderCode= （select ProviderCode from Provider where ProviderName浙江康思貝'）；54）刪除供應(yīng)商代碼為S005的供應(yīng)商記錄。delete from PM wliere ProviderCode=*S005'55）刪除所有的學(xué)生選課記錄。Delete from PM;56）刪除青島魯健藥業(yè)的供應(yīng)藥品記錄。delete from PM where Pro vid er

32、C od e= （s elect ProviderCode from Provider where Provid erName=，青島魯健藥業(yè)'）；57）創(chuàng)建醫(yī)保藥品的視圖（視圖中的YB='>'）O向創(chuàng)建的醫(yī)保視圖中插入（'20014',牛黃解毒片Tnhjdp',片劑'；0.27g/片； ,Z11020452,2012-10-0r,2014-09-30,；ffi藥；否）檢査結(jié)果。create view IS_Medicine as select * from Medicine where YB='是：insertintoIS

33、_Medicine（MedicineCode,MedicineName,PyCode,DosageForm,Standard,B atchNumberroductionDatexpirationDateCategoryB） values（*200147牛 黃 解 毒 片 ','nhjdpY 片 劑 70.27g/ 片 ；'Z11020452；to_dat 刖 2012-10-01TYYYY-MMDD）,to_dateC20140930JYY YY-MM-DD'）；® 藥;否）;58）創(chuàng)建“浙江康恩貝”供應(yīng)商供應(yīng)的藥品信息（包含藥品代碼、藥品名稱、藥品類

34、別、 供應(yīng)商等屬性列）的視圖。create view ZJMedicine as select MedicineCode,MedicineNanie,DosageForni from Medicine where MedicineCode in（select MedicineCode from PM where ProviderCode in（select ProviderCocle from Provider where ProviderNamA浙江康 恩貝'）；59）定義一個反映藥品生產(chǎn)年份的視圖。create view NF_Medicine as select extract

35、（year from ProductionDat e） Medicine_id from Medicine;60）將各供應(yīng)商供應(yīng)的藥品總個數(shù)定義為一個視圖。create view ZS_PM as select ProviderCode,count（Qyt） PM_id from PM group by ProviderCode;（ORACLE系統(tǒng)部分及PL/SQL、游標(biāo)做發(fā)器誓）.61）查詢控制文件的名稱，位置和狀態(tài)select * from v$controlfile;62）對M_Medi表的MedicineName列創(chuàng)建索引，之后刪除。CREATE INDEX MNDEX ON M.Me

36、di（MedicineName）;drop INDEX MJNDEX;63）藥庫的某些工作人員，關(guān)注的是藥品表里那些藥品將要失效?？梢詣?chuàng)建一個視圖。 create view OE_Medicine as select * from Medicine whereExpirationDate<sysdate;64）可以通過視圖修改失效藥的名字。將藥品名稱為“小兒感冒顆?！备臑椤靶呵鍩?散”（序列部分増加部分）update OEMedicine set MedicineName=1 小兒清熱散,where MedicineName= *小 兒感冒顆粒'；65）向藥品表M_Medi添加

37、一個藥品編號，編號值由序列產(chǎn)生，從1開始，増量為1。 CREATE SEQUENCE NLMedsequenceINCREMENT BY 1 -每次加幾個START WITH 1 一從1開始計數(shù)NOMAXvalue -不設(shè)直最大值NOCYCLE - 一直累加，不循環(huán)CACHE 10;設(shè)直緩存cache個序列66）序列創(chuàng)建完成后，可以查看序列的值。select M_Medsequence from M_Mecli;（同義詞部分）67）為M_Medi創(chuàng)建同義詞，使用后刪除。CREATE SYNONYM Y_M FOR YK.MMedi;DROP SYNONYM Y_M;68）想將藥品表（M_MED

38、I）的MedicineCodb “10001”的藥品名稱存入變量 Med_Nameoselect MedicineName into field_Med_Name from M_MEDI where MedicineCode=* 10001:69）定義一個記錄變量M_MD,包含藥品的藥品代碼、藥品名稱、拼音簡碼等所有字段。 將藥品編碼為'1 ooor的所有信息存入到該變量。declare M_MD（MedicineCode,MedicineName,PyCode） select * from Medicine where MedicineCode=' 10001'70）定義一個表變量M_MDT,用該變量存放幾種藥品的名字。、declare TYPE NewMedicine IS TABLE OF varchar2（10） index by M_MDT;71）通過FOR循環(huán)語句給M_MEDI數(shù)據(jù)庫，描入藥品編碼。再通過SELECT語句檢査 插入是否成功。72）用游標(biāo)實現(xiàn)，顯示M.MEDI表中藥品的名稱，用逗號隔開，保存在一個字符串內(nèi)， 打印。如“小兒感冒顆粒，護(hù)彤，.”。73）向M_MEDI表添加藥品編碼，編碼為'10006到10010',編寫存儲過程實現(xiàn)。74）編寫一個存儲過