广医信息系统oracle作业

案例 1 供应商 供应 药品数据库中包括以下 3 张表 表结构参见表 3 1 表 3 2 表 3 3 表 3 1 供货商表 Provider 字段中文名字段名类型空值备注 供应商代码ProviderCodeChar 4 No主码 供应商ProviderNameChar 60 No 拼音简码PyCodeChar 10 Yes 地址AddressChar 50 Yes 电话TelChar 15 Yes 邮编ZipChar 6 Yes EmailEmailChar 30 Yes 联系人RelationChar 8 Yes 表 3 2 药品表 Medicine 字段中文名字段名类型空值备注 药品代码MedicineCodeChar 5 No主码 药品名称MedicineNameVarchar 50 No 拼音简码PyCodeChar 10 Yes 剂型DosageFormChar 6 Yes 规格StandardChar 15 Yes 单位UnitChar 10 Yes 批号BatchNumberChar 20 Yes 生产日期ProductionDateSmallDatetimeYes 失效日期ExpirationDateSmallDatetimeYes 药品类别CategoryChar 10 Yes中成药 西药等 医保YBChar 2 Yes默认为 否 第 3 章 关系数据库标准语言 表 3 3 供应表 PM 字段中文名字段名类型空值备注 药品代码MedicineCodeChar 5 No主码 供应商代码ProviderCodeChar 4 No主码 供应日期PMDateSmallDatetimeNo主码 价格PriceMoneyYes 数量QytIntYes 各张表的数据示例参见表 3 4 表 3 5 表 3 6 第 3 章 关系数据库标准语言 表 3 4 供货商表 Provider 实例数据 ProviderCodeProviderNamePyCodeAddressTelZipEmailRelation S001河北东风药业Hbdfyy河北省永年县城西0310 6806999057150hbdf 张三 S002浙江康恩贝Zzkeb杭州市高新技术开发区0571 87774811 310045ttm 8512 李四 S003青岛鲁健药业Qdljyy青岛市北区延安路2660001375685404 王五 S004哈药制药Hyzy哈尔滨市南岗区学府路150000hh 王六 表 3 5 药品表 Medicine 实例数据 MedicineCodeMedicineNamePyCodeDosageFormStandardBatchNumberProductionDateExpirationDatecategoryYB 10001小儿感冒颗粒Xegmkl颗粒剂12g 袋Z53020405 2009 01 012012 12 31中成药是 10002维生素 C 银翘片Wsscyqp片剂49 5mg 片Z410223182010 01 012012 06 30中成药是 10003清热解毒胶囊Qrjdjn胶囊剂0 3g 粒Z200546632012 06 302014 06 30中成药是 10004小柴胡冲剂Xchcj颗粒剂10g 袋Z440207092012 12 012014 08 30中成药是 20006新康泰克Xktk胶囊剂0 25g 粒H200104302011 02 252013 08 25西药是 20007护彤ht颗粒剂2g 袋H230226132004 10 072007 10 07西药否 20008救急散Jjs散剂1 5g 瓶Z110201382012 01 012015 01 01西药否 表 3 6 供应表 PM 实例数据 MedicineCodeProviderCodePriceQytPMDate 10002S0013 001502010 02 01 10003S00124 002302012 08 01 10004S0019 005002013 01 01 10004S0029 001002013 02 02 20004S00235 002002012 01 01 20008S003701002012 04 01 第 3 章 关系数据库标准语言 首先 首先 为药库数据库通过 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 第 3 章 关系数据库标准语言 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 第 3 章 关系数据库标准语言 ExpirationDate sysdate 18 查询供应价格在10元以内的药品代码 select MedicineCode from PM where Price to date 2011 06 01 YYYY MM DD 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 第 3 章 关系数据库标准语言 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 Producti 第 3 章 关系数据库标准语言 onDate 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 P roductionDate 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 第 3 章 关系数据库标准语言 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 编写一个存储过程 输入参数是药品编码 要求运行后能得到药品名称 当存储过程 第 3 章 关系数据库标准语言 的使用着调用该存储过程 得到药品的名称 存储过程是不能 RETURN 某个值 是不允许的 需要定义输出参数来完成 75 定义一个触发器 在给供应表插入数据时 供货商不能是 哈药制药 create trigger trg after inse