广医信息系统oracle作业.doc

第3章 关系数据库标准语言
案例1： 供应商-供应-药品数据库中包括以下3张表，表结构参见表3.1、表3.2、表3.3。

表3.1 供货商表（ Provider）
字段中文名
字段名
类型
空值
备注
供应商代码
ProviderCode
Char(4)
No
主码
供应商
ProviderName
Char(60)
No

拼音简码
PyCode
Char(10)
Yes

地址
Address
Char(50)
Yes

电话
Tel
Char(15)
Yes

邮编
Zip
Char(6)
Yes

Email
Email
Char(30)
Yes

联系人
Relation
Char(8)
Yes

表3.2 药品表（Medicine）
字段中文名
字段名

类型
空值
备注
药品代码
MedicineCode

Char(5)
No
主码
药品名称
MedicineName

Varchar(50)
No

拼音简码
PyCode

Char(10)
Yes

剂型
DosageForm

Char(6)
Yes

规格
Standard

Char(15)
Yes

单位
Unit

Char(10)
Yes

批号
BatchNumber

Char(20)
Yes

生产日期
ProductionDate

SmallDatetime
Yes

失效日期
ExpirationDate

SmallDatetime
Yes

药品类别
Category

Char(10)
Yes
中成药、西药等
医保
YB

Char(2)
Yes
默认为“否”

表3.3 供应表 （PM）
字段中文名
字段名
类型
空值
备注
药品代码
MedicineCode
Char(5)
No
主码
供应商代码
ProviderCode
Char(4)
No
主码
供应日期
PMDate
SmallDatetime
No
主码
价格
Price
Money
Yes

数量
Qyt
Int
Yes

各张表的数据示例参见表3.4、表3.5、表3.6。

表3.4 供货商表（ Provider）实例数据
ProviderCode
ProviderName
PyCode
Address
Tel
Zip
Email
Relation
S001
河北东风药业
Hbdfyy
河北省永年县城西
0310-6806999
057150
hbdf@
张三
S002
浙江康恩贝
Zzkeb
杭州市高新技术开发区
0571-87774811
310045
ttm_8512@
李四
S003
青岛鲁健药业
Qdljyy
青岛市北区延安路

266000
1375685404@
王五
S004
哈药制药
Hyzy
哈尔滨市南岗区学府路

150000
hh@
王六

表3.5 药品表（Medicine）实例数据
MedicineCode
MedicineName
PyCode
DosageForm
Standard
BatchNumber
ProductionDate
ExpirationDate
category
YB
10001
小儿感冒颗粒
Xegmkl
颗粒剂
12g/袋
Z53020405
2009-01-01
2012-12-31
中成药
是
10002
维生素C银翘片
Wsscyqp
片剂
49.5mg/片
Z41022318
2010-01-01
2012-06-30
中成药
是
10003
清热解毒胶囊
Qrjdjn
胶囊剂
0.3g/粒
Z20054663
2012-06-30
2014-06-30
中成药
是
10004
小柴胡冲剂
Xchcj
颗粒剂
10g/袋
Z44020709
2012-12-01
2014-08-30
中成药
是
20006
新康泰克
Xktk
胶囊剂
0.25g/粒
H20010430
2011-02-25
2013.08-25
西药
是
20007
护彤
ht
颗粒剂
2g/袋
H23022613
2004-10-07
2007-10-07
西药
否
20008
救急散
Jjs
散剂
1.5g/瓶
Z11020138
2012-01-01
2015-01-01
西药
否

表3.6 供应表 （PM）实例数据
MedicineCode
ProviderCode
Price
Qyt
PMDate
10002
S001
3.00
150
2010-02-01
10003
S001
24.00
230
2012-08-01
10004
S001
9.00
500
2013.01-01
10004
S002
9.00
100
2013.02-02
20004
S002
35.00
200
2012-01-01
20008
S003
70
100
2012-04-01

首先：为药库数据库通过DBCA创建了数据库YK，在YK数据库里创建用户数据表空间，建立表空间P_M及临时表空间为P_M_TEMP，创建用户MDAdmin、用户密码为123456。授予角色CONNECT及DBA，完成了药库数据库建设的框架工作，为创建数据库对象做好准备，过程如下：
（1） 创建用户表空间P_M，用来存放药库的数据表;
create tablespace p_m2 datafile 'C:\app\Administrator\oradata\YK\p_m1' size 100m;
（2） 创建临时表空间P_M_TEMP；
Create temporary tablespace p_m2_temp tempfile'C:\app\Administrator\oradata\YK\p_m2_temp.dbf' size 100m;
（3） 创建用户MDADMIN并指定表空间;
create user MDADMIN identified by 123456 default tablespace p_m2 temporary tablespace p_m2_temp;
（4） 为用户授予权限;
grant connect to MDADMIN;
grant dba to MDADMIN;
（5） 通过SQL DEVELOPER建立用户MDAdmin 到YK数据库的连接，更直观的对数据库进行操作。
再完成以下工作：
1) 创建药品表（Medicine），药品代码是主码，批号取值唯一，在创建表的过程中使用check约束和默认(DEFAULT)约束。
create table Medicine
(MedicineCode CHAR(5) primary key not null,
MedicineName Varchar(50) not null,
BatchNumber CHAR(20) unique,
YB char(2) default '否',
CHECK (YB ='是'or YB='否'),
PyCode CHAR(10),
DosageForm CHAR(6),
Standard CHAR(15),
Unit CHAR(10),
ProductionDate Date,
ExpirationDate Date,
Category CHAR(10)
);
2) 创建供货商表（Provider），主码建为表级约束。
create table Provider
(ProviderCode CHAR(4) primary key not null,
ProviderName CHAR(60) not null,
PyCode CHAR(10) null,
Address CHAR(50) null,
Tel CHAR(15) null,
Zip CHAR(6) null,
Email CHAR(30) null,
Relation CHAR(8) null,
FOREIGN KEY (ProviderCode) REFERENCES Provider(ProviderCode)
);
3) 建立供应表（PM），包含主码、外码，均为表级约束。
create table PM
(MedicineCode CHAR(5) not null,
ProviderCode char(4) not null,
PMDate Date not null,
Price number null,
Qyt Int null,
primary key (MedicineCode,ProviderCode, PMDate),
foreign key (MedicineCode) references Medicine(MedicineCode),
foreign key (ProviderCode) references Provider(ProviderCode)
);
4) 向Medicine表增加“使用说明（Memo）”列，其数据类型为字符串类型。
ALTER TABLE Medicine ADD Memo CHAR;
5) 将Medicine表的Memo列删除。
ALTER TABLE Medicine DROP column Memo;
6) 将Provider表的Address列的数据类型由Char(50)改为Char(60)。
ALTER TABLE Provider modify Address char(60);
7) 要求PM表的Qyt取值在0至1000之间。
ALTER TABLE PM ADD constraint Qyt check(Qyt>0 and Qyt<1000);
8) 要求供应表（PM）中的PMDate默认为当前时间。
ALTER TABLE PM modify PMDate DATE default sysdate;
9) 删除PM表的默认约束DF_PMDate。
ALTER TABLE PM drop constraint DF_PMDate;
10) 查询所有药品的药品代码、药品名称。
select MedicineCode,MedicineName from Medicine;
11) 查询所有药品的剂型、药品名称、药品类别。
select DosageForm,MedicineName,Category from Medicine;
12) 查询所有药品的详细记录。
select * from Medicine;
13) 查询所有药品的药品代码、药品名称、生产年份。
select MedicineCode,MedicineName,ProductionDate from Medicine;
14) 查询药品名称、批号，生产年份、保质期。可使用列别名改变查询结果的列标题。
select MedicineName,BatchNumber,ProductionDate,ExpirationDate-ProductionDate AS "保质期" from Medicine;
15) 查询供应了药品的供应商代码。
select ProviderCode from PM;
16) 查询中成药类的药品名称。
select MedicineName from Medicine where category='中成药';
17) 查询所有过期药品的药品代码、药品名称、失效日期。
select MedicineCode,MedicineName,ExpirationDate from Medicine where
ExpirationDate<sysdate;
18) 查询供应价格在10元以内的药品代码。
select MedicineCode from PM where Price<10;
19) 查询生产日期在2011-06-01与2012-06-01之间的药品信息。
select * from Medicine where ProductionDate>＝to_date('2011-06-01','YYYY-MM-DD') and ProductionDate<to_date('2012-06-01','YYYY-MM-DD');
20) 查询生产日期不在2011-06-01与2011-06-01之间的药品代码、药品名称、批号。
select MedicineCode,MedicineName,BatchNumber from Medicine where ProductionDate not between to_date('2011-06-01','YYYY-MM-DD') and to_date('2012-06-01','YYYY-MM-DD');
21) 查询片剂、散剂和颗粒剂的药品代码、药品名称。
select MedicineCode,MedicineName from Medicine where DosageForm='片剂' or DosageForm='散剂' or DosageForm='颗粒剂';
22) 查询既不片剂、也不是散剂的药品代码、药品名称。
select MedicineCode,MedicineName from Medicine where DosageForm!='片剂' and DosageForm!='散剂';
23) 查询所有拼音简码以x开头的药品代码、药品名称。
select MedicineCode,MedicineName from Medicine where PyCode like 'x_';
24) 查询邮编以“3”开头倒数第二个字符为“4”的供应商信息。
select * from Provider where Zip like '3_%4_';
25) 查询邮箱为“ttm_8512@”的供应商信息。
select * from Provider where Email='ttm_8512@';
26) 查询邮箱为以“tt_”开头，且倒数第二个字符为“6”的供应商信息。
select * from Provider where Email like 'tt\_%6_' escape '\';
27) 查询没有提供联系电话的供应商基本信息。
select * from Provider where Tel is null;
28) 查询所有有联系电话的供应商基本信息。
select * from Provider where Tel is not null;
29) 查询2012年以后生产的剂型为“胶囊”的药品名称。
select MedicineCode from Medicine where DosageForm='胶囊剂' and ProductionDate>＝to_date('2012-01-01','YYYY-MM-DD');
30) 查询供应药品代码为的供应商代码，供应日期，价格，数量查询结果按价格降序排列。
select MedicineCode,ProviderCode,PMDate,Price,Qyt from PM order by Price desc;
31) 查询药品基本信息，查询结果按照药品类别升序排列，同一类别按照生产日期降序排列。
select * from Medicine order by Category,ProductionDate desc;
32) 查询药品的总个数。
select count(*) from PM;
33) 查询供应药品的供应商个数。
select count(distinct ProviderCode) from PM;
34) 计算“10004”号药品的平均供应价格。
select avg(Price) from PM where MedicineCode=10004;
35) 计算“10004”号药品的最高供应价格。
select max(Price) from PM where MedicineCode=10004;
36) 查询“S001”供应商供应的药品总数量。
select sum(Qyt) from PM where ProviderCode='S001';
37) 求每个供应商供应的药品个数。
select ProviderCode,sum(Qyt) from PM group by ProviderCode;
38) 查询供应了3种以上药品的供应商代码。
select ProviderCode from PM group by ProviderCode having count(*)>3;
39) 查询每个供应商及其供应药品的情况。
select Provider.*,PM.* from Provider,PM where Provider.ProviderCode=PM.ProviderCode;
40) 查询与小儿感冒颗粒相同剂型的药品信息。
select * from Medicine where DosageForm in(select DosageForm from Medicine where MedicineName='小儿感冒颗粒');
41) 右外连接：查询所有药品被供应的情况。
select * from Medicine right outer join PM on(Medicine.MedicineCode=PM.MedicineCode);
42) 查询维生素C银翘片的供应情况
select PM.MedicineCode,ProviderCode,Price,Qyt,PMDate from Medicine,PM where Medicine.MedicineCode=PM.MedicineCode and MedicineName='维生素C银翘片';
43) 查询每个供应商供应的药品代码，药品名称、价格、数量、供应商名称、供应年份。
select PM.MedicineCode,Price,Qyt,PMDate,MedicineName,ProviderName from Medicine,Provider,PM where Provider.ProviderCode=PM.ProviderCode and Medicine.MedicineCode=PM.MedicineCode;
44) 查询"浙江康恩贝"供应的药品代码和药品名称。
select MedicineCode,MedicineName from Medicine where MedicineCode in(select MedicineCode from PM where ProviderCode in(select ProviderCode from Provider where ProviderName='浙江康恩贝'));
45) 找出每种药品供应价格超出它的供应平均价格的供应商代码（相关子查询）。
select MedicineCode,ProviderCode from PM x where Price>=(select avg(Price) from PM y where y.MedicineCode=x.MedicineCode);
46) 查询S001供应商供应的药品名称（用EXISTS谓词）。
select MedicineName from Medicine where EXISTS(select * from PM where MedicineCode=Medicine.MedicineCode and ProviderCode='S001');
47) 查询颗粒剂的药品及中成药，用集合操场完成。
select * from Medicine where DosageForm='颗粒剂'union select * from Medicine where category='中成药';
48) 查询颗粒剂的药品与中成药的交集。
select * from Medicine where DosageForm='颗粒剂'intersect select * from Medicine where category='中成药';
49) 查询颗粒剂的药品与中成药的差集。
select * from Medicine where DosageForm='颗粒剂'minus select * from Medicine where category='中成药';
将（药品代码:10007，药品名称：藿香正气水，拼音简码：hxzqs，剂型：口服液，规格：ml/支，批号：Z51021352，生产日期：-12-20，失效日期：-12-20，药品类别：中成药，是否医保：是）插入到Medicine表中。
insert into Medicine(MedicineCode,MedicineName,PyCode,DosageForm,Standard,BatchNumber,ProductionDate,ExpirationDate,category,YB) values(10007,'藿香正气水','hxzqs','口服液','ml/支','Z51021352',to_date('-12-20','-MM-DD'),to_date('-12-20','-MM-DD'),'中成药','是');
50) 增加一条供应商记录（供应商代码：S005 ，供应商名称：华西制药，其他信息暂时未知）
insert into Provider(ProviderCode,ProviderName) values ('S005','华西制药');
对每一种药品，求供应商供应的平均价格，并把结果存入数据库。（先建立一张表：
CREATE TABLE AVG_Medicine_Price
(MedicineCode Char(10),
Avg_Price number) ;
Insert into AVG_Medicine_Price(MedicineCode,Avg_Price) select MedicineCode,avg(Price) from PM group by MedicineCode;
51) 将供应商S001的联系人改为彭大。
update Provider set Relation='彭大' where ProviderCode='S001';
52) 将所有药品的供应价格提高5%。
update PM set Price=Price*1.05;
53) 将浙江康恩贝供应的药品数量置零。
update PM set Qyt=0 where ProviderCode=(select ProviderCode from Provider where ProviderName='浙江康恩贝');
54) 删除供应商代码为S005的供应商记录。
delete from PM where ProviderCode='S005';
55) 删除所有的学生选课记录。
Delete from PM;
56) 删除青岛鲁健药业的供应药品记录。
delete from PM where ProviderCode=(select ProviderCode from Provider where ProviderName='青岛鲁健药业');
57) 创建医保药品的视图（视图中的YB='是'）。
向创建的医保视图中插入（'20014','牛黄解毒片','nhjdp','片剂','0.27g/片',
'Z11020452','2012-10-01','2014-09-30','西药','否'）检查结果。
create view IS_Medicine as select * from Medicine where YB='是';
insert into IS_Medicine(MedicineCode,MedicineName,PyCode,DosageForm,Standard,BatchNumber,ProductionDate,ExpirationDate,Category,YB) values('20014','牛黄解毒片','nhjdp','片剂','0.27g/片','Z11020452',to_date('2012-10-01','YYYY-MM-DD'),to_date('2014-09-30','YYYY-MM-DD'),'西药','否');
58) 创建“浙江康恩贝”供应商供应的药品信息（包含药品代码、药品名称、药品类别、供应商等属性列）的视图。
create view ZJ_Medicine as select MedicineCode,MedicineName,DosageForm from Medicine where MedicineCode in(select MedicineCode from PM where ProviderCode in(select ProviderCode from Provider where ProviderName='浙江康恩贝'));
59) 定义一个反映药品生产年份的视图。
create view NF_Medicine as select extract(year from ProductionDate) Medicine_id from Medicine;
60) 将各供应商供应的药品总个数定义为一个视图。
create view ZS_PM as select ProviderCode,count(Qyt) PM_id from PM group by ProviderCode;
（ORACLE系统部分及PL/SQL、游标触发器等）。
61) 查询控制文件的名称，位置和状态
select * from v$controlfile;
62) 对M_Medi表的MedicineName列创建索引，之后删除。
CREATE INDEX M_INDEX ON M_Medi(MedicineName);
drop INDEX M_INDEX;
63) 药库的某些工作人员，关注的是药品表里那些药品将要失效。可以创建一个视图。
create view OE_Medicine as select * from Medicine where ExpirationDate<sysdate;
64) 可以通过视图修改失效药的名字。将药品名称为“小儿感冒颗粒”改为“小儿清热散”
（序列部分增加部分）
update OE_Medicine set MedicineName='小儿清热散' where MedicineName='小儿感冒颗粒';
65) 向药品表M_Medi添加一个药品编号，编号值由序列产生，从1开始，增量为1。
CREATE SEQUENCE M_Med_sequence
INCREMENT BY 1 -- 每次加几个
START WITH 1 -- 从1开始计数
NOMAXvalue -- 不设置最大值
NOCYCLE -- 一直累加，不循环
CACHE 10;--设置缓存cache个序列
66) 序列创建完成后，可以查看序列的值。
select M_Med_sequence from M_Medi;
（同义词部分）
67) 为M_Medi创建同义词，使用后删除。
CREATE SYNONYM Y_M FOR YK.M_Medi;
DROP SYNONYM Y_M;
68) 想将药品表（M_MEDI）的MedicineCode=“10001”的药品名称存入变量Med_Name。
select MedicineName into field_Med_Name from M_MEDI where MedicineCode='10001';
69) 定义一个记录变量M_MD，包含药品的药品代码、药品名称、拼音简码等所有字段。将药品编码为’10001’的所有信息存入到该变量。
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循环语句给M_MEDI数据库，插入药品编码。再通过SELECT语句检查插入是否成功。

72) 用游标实现，显示M_MEDI表中药品的名称，用逗号隔开，保存在一个字符串内，打印。如“小儿感冒颗粒,护彤，….”。

73) 向M_MEDI表添加药品编码，编码为‘10006到10010’，编写存储过程实现。

74) 编写一个存储过程，输入参数是药品编码，要求运行后能得到药品名称，当存储过程的使用着调用该存储过程，得到药品的名称。（存储过程是不能“RETURN 某个值”是不允许的，需要定义输出参数来完成）。

75) 定义一个触发器，在给供应表插入数据时，供货商不能是“哈药制药”。
create trigger trg after insert ON Provider for each row where ProviderName!='哈药制药';
76) 修改海南东风药业公司的药品价格，如果供应量大于300，则药品的供货必须价格低于20元。
77) 创建用户名为user1，口令为usertest，默认表空间USERS，临时表空间为TEMP的用户。
create user user1 identified by usertest DEFAULT TABLESPACE USERS temporary tablespace USERSTEMP;
78) 将用户名为user1的用户口令改为newpassword。
alter user user1 identified by newpassword;
79) 查看用户user1的的默认表空间和临时表空间（通过数据字典来查看）。
select username,default_tablespace,temporary_tablespace from dba_users where username = 'user1';
80) 将药品表（Medicine）上的SELECT、INSERT授予user1用户。
grant SELECT,INSERT on Medicine to user1;
81) 从用户user1收回药品表（Medicine）对象上的所有权限。
revoke all on Medicine from user1;
82) 查看当前用户所具有的系统权限（通过数据字典来查看）
select * from dba_sys_privs;
select * from user_sys_privs;
83) 创建一个名为role1的角色，口令为roletest。
create role role1 identified by roletest;
84) 为角色role1授予CREATE SESSION和CREATE DATABASE LINK系统权限。
grant CREATE SESSION,CREATE DATABASE LINK to role1;
85) 将系统预定义角色CONNECT，RESOURCE和自定义角色role1授予用户user1。
grant CONNECT，RESOURCE,role1 to user1;
86) 取消用户user1的CONNECT角色。
revoke CONNECT from user1;
87) 收回角色role1的CREATE SESSION系统权限。
revoke CREATE SESSION from role1;
88) 删除角色role1。
drop role role1;