oracle三级项目

1、项目名称：超市管理系统的设计与实现 1. 需求描述大型超市在正常运营中面对大量会员、员工信息商品信息及部门信息的复杂多样性。现有的人工记录方法既效率低又错误过多，大大影响了大型超市的正常管理工作。因此需要对商品资源、会员资源、员工信息、部门结构信息进行管理，及时了解各个环节中信息的变更，有利于管理效率的提高。2. 概念结构设计商品号商品名画出系统E-R图。出售时间BILL出售价格账单号会员名性别出售时间年龄出售时间Us电话会员号会员等级地址类别重量出售时间产地GOODS商品名商品号地址性别年龄出售时间电话EMPLOYEES员工名工资员工号职位部门名部门号管理号Department3. 逻辑结构

2、设计（1）表的设计 根据E-R图设计关系模式，设计表。商品出售表（BILL）的结构序号字段名称字段说明类型位数属性备注1BILLID账单号数字6必须非空2GOODSNAME商品名文本20必须非空3GOODNUM商品号数字10必须非空4BILLTIME出售时间Date2可为空5ISPAY售出价格文本10可为空会员信息表（US）的结构序号字段名称字段说明类型位数属性备注1USERID用户编号数字6必须非空2USERNAME 用户姓名文本20可为空3SEX 性别数字10可为空4AGE 年龄数字2可为空5TELPHONE电话文本20可为空6ADDRESS 地址文本20可为空7Power会员等级文本20

3、可为空8PASSWORD密码文本20可为空商品信息表（GOODS）的结构序号字段名称类型位数属性备注1GOODSID商品编号数字6必须非空2GOODSNAME商品名文本20可为空3FAMILY 类别文本200可为空4WEIGHT 重量数字2可为空5ADDRESS 出产地文本20可为空员工信息表（EMPLOYEES）的结构序号字段名称类型位数属性备注1EMPLOYEEID员工编号数字6必须非空2EMPLOYEENAME员工名文本20可为空3Sex 性别文本200可为空4Avg 年龄数字2可为空5TELPHONE电话数字15可为空6ADDRESS地址文本20可为空7SALARY工资数字10可为空8

4、JOB职位文本20可为空部门信息表（DEPARTMENTS）的结构序号字段名称类型位数属性备注1DEPARTMENTID部门号数字6必须非空2DEPARTMENTNAME部门名文本20可为空3MANAGER_ID 管理号文本200可为空（2） 序列的设计为了方便产生员工信息、部门信息创建两个序列自动产生相应的编号创建一个名为employeesseq的序列，用于产生员工编号，起始值为100，步长为1，不缓存，不循环。 创建一个名为DEPARTMENTS_SEQ的序列，用于产生部门编号，起始值为5，步长为10，最大值为10000，不缓存，不循环。（3） 视图的设计创建一个名为emp_detail_

5、view的视图，用于员工信息综合查询，包括员工编号、员工名、职位名称、地址等信息。（4） 索引的设计为了提高查询效率创建索引。在EMPLOYEES表的JOB列上建名为EMP_JOB_INDX的平衡树索引在employees表的employeename列上创建EMP_NAME_INDX的索引在employees表的ADDRESS列上创建EMP_ADD_INDX的索引GOODS 表的GOODSNAME列上创建GOOD_NAME_INDX的平衡树索引；4. 数据库实施（1）创建表create table BILL( BILLID NUMBER not null, GOODSNAME VARCHAR2

6、(20) not null, GOODSNUM NUMBER not null, ISPAY VARCHAR2(4) not null, BILLTIME DATE not null)tablespace USERS pctfree 10 initrans 1 maxtrans 255 storage ( initial 64K minextents 1 maxextents unlimited );alter table BILL add constraint BILLID primary key (BILLID) using index tablespace USERS pctfree 1

7、0 initrans 2 maxtrans 255 storage ( initial 64K minextents 1 maxextents unlimited );commit;insert into BILL (BILLID, GOODSNAME, GOODSNUM, MONEY, ISPAY, SUPPLIERNAME, GOODSDESCRIBLE, BILLTIME)values (164, '名称', 100, 1.2E+005, '是', '名称', '描述', to_date('06-08-2015

8、9;, 'dd-mm-yyyy');commit;create table US( USERID NUMBER not null, USERNAME VARCHAR2(20), SEX VARCHAR2(10), AGE NUMBER, TELPHONE VARCHAR2(20), ADDRESS VARCHAR2(20), POWER VARCHAR2(20), PASSWORD VARCHAR2(20)tablespace USERS pctfree 10 initrans 1 maxtrans 255 storage ( initial 64K minextents 1

9、maxextents unlimited );alter table US add constraint USERID primary key (USERID) using index tablespace USERS pctfree 10 initrans 2 maxtrans 255 storage ( initial 64K minextents 1 maxextents unlimited );commit;insert into US (USERID, USERNAME, SEX, AGE, TELPHONE, ADDRESS, POWER, PASSWORD)values (91,

10、 '清露', '男', 33, '#39;, '说的', '普通会员', '12345');insert into US (USERID, USERNAME, SEX, AGE, TELPHONE, ADDRESS, POWER, PASSWORD)values (85, 'zhshde', '男', 22, '#39;, 'xiam', '普通会员', '890628')

11、;insert into US (USERID, USERNAME, SEX, AGE, TELPHONE, ADDRESS, POWER, PASSWORD)values (87, 'zhsd', '男', 23, '#39;, 'sda', '普通会员', '890628');insert into US (USERID, USERNAME, SEX, AGE, TELPHONE, ADDRESS, POWER, PASSWORD)values (90, 'zhou

12、9;, '男', 45, '#39;, 'dv', '普通会员', '123456');insert into US (USERID, USERNAME, SEX, AGE, TELPHONE, ADDRESS, POWER, PASSWORD)values (1, 'admin', '男', 22, '#39;, '本地', '管理员', 'admin');insert into US

13、(USERID, USERNAME, SEX, AGE, TELPHONE, ADDRESS, POWER, PASSWORD)values (22, '王文宇', '男', 22, '#39;, '大连', '普通会员', '123456');commit;create table goods( GOODSID NUMBER not null, GOODSNAME VARCHAR2(20), FAMILY VARCHAR2(10), WEIGHT NUMBER, ADDRESS V

14、ARCHAR2(20) )tablespace USERS pctfree 10 initrans 1 maxtrans 255 storage ( initial 64K minextents 1 maxextents unlimited ); SELECT * FROM GOODS insert into GOODS (GOODSID, GOODSNAME, FAMILY, WEIGHT,ADDRESS)values (01, '百事可乐', '饮料', 0.5,'武汉');insert into GOODS (GOODSID, GOODSN

15、AME, FAMILY, WEIGHT,ADDRESS)values (02, '可比克', '膨化食品', 0.3,'浙江');create table employees( employeeID NUMBER not null, employeeNAME VARCHAR2(20), SEX VARCHAR2(10), AGE NUMBER, TELPHONE VARCHAR2(20), ADDRESS VARCHAR2(20), salary NUMBER(20), JOB VARCHAR2(20)tablespace USERS pctfr

16、ee 10 initrans 1 maxtrans 255 storage ( initial 64K minextents 1 maxextents unlimited ); insert into employees (employeeID, employeeNAME, SEX, AGE, TELPHONE, ADDRESS, salary,job)values (01, '刘伟康', '男', 23, '#39;, '东软', '1200', '销售员');select *fr

17、om billcreate table departments( departmentID NUMBER not null, departmentNAME VARCHAR2(20), manager_id VARCHAR2(20)tablespace USERS pctfree 10 initrans 1 maxtrans 255 storage ( initial 64K minextents 1 maxextents unlimited ); insert into departments (departmentID, departmentNAME,manager_id)values (0

18、1, '销售部',001);select * from departmentsselect * from us（2）创建序列创建一个名为employeesseq的序列，用于产生员工编号，起始值为100，步长为1，不缓存，不循环。 CREATE SEQUENCE EMPLOYEES_SEQSTART WITH 100 INCREMENT BY 1 NOCACHE NOCYCLE创建一个名为DEPARTMENTS_SEQ的序列，用于产生部门编号，起始值为5，步长为10，最大值为10000，不缓存，不循环。CREATE SEQUENCE DEPARTMENTS_SEQSTART WI

19、TH 5 INCREMENT BY 10 MAXVALUE 10000 NOCACHE NOCYCLE（3） 创建索引在EMPLOYEES表的JOB列上建名为EMP_JOB_INDX的平衡树索引CREATE INDEX EMP_JOB_INDX ON EMPLOYEES(JOB) TABLESPACE INDX;在employees表的employeename列上创建EMP_NAME_INDX的索引CREATE INDEX EMP_NAME_INDX ON EMPLOYEES(EMPLOYEENAME) TABLESPACE INDX;在employees表的ADDRESS列上创建EMP_AD

20、D_INDX的索引CREATE INDEX EMP_ADD_INDX ON EMPLOYEES(ADDRESS) TABLESPACE INDX;GOODS 表的GOODSNAME列上创建GOOD_NAME_INDX的平衡树索引；CREATE INDEX GOOD_NAME_INDX ON GOODS(GOODSNAME) TABLESPACE INDX;（4） 创建视图创建一个名为emp_detail_view的视图，用于员工信息综合查询，包括员工编号、员工名、职位名称、地址等信息。CREATE OR REPLACE VIEW EMP_DETAIL_VIEW(EMPLOYEE_ID,JOB,

21、EMPLOYEENAME,ADDRESS)ASSELECT EMPLOYEE_ID,EMPLOYEE_NAME,JOB,ADDRESS FROM EMPLOYEESWITH READ ONLY;5PL/SQL程序设计开发（1）存储过程的设计1.创建一个名为proc_show_emp的存储过程，以员工编号为参数，查询并返回该员工的工资。CREATE OR REPLACE PROCEDURE PROC_SHOW_EMP(P_EMPID EMPLOYEES.EMPLOYEEID%TYPE)ASV_SAL EMPLPYEES.SALARY%TYPE;BEGINSELECT SALARY INTO V_

22、SAL FROM EMPLOYEES WHERE EMPLOYEEID=P_EMPID;DBMS_OUT.PUT_LINE(P_EMPID|SALARY IS:|V_SAL);EXCEPTION WHEN NO_DATA_FOUND THENDBMS_OUT.PUT_LINE(THE EMPLOYEE DOESN”T EXTSTS!);END PROC_SHOW_EMP;2.创建一个名为proc_RETURN_DEPTINFO的存储过程，以部门编号为参数，查询并返回该部门的名字。CREATE OR REPLACE PROCEDURE proc_RETURN_DEPTINFO(P_DEPTID

23、DEPARTMENTS.DEPARTMENTID%TYPE,P_name DEPARTMENTS.DEPARTMENTNAME%TYPE)ASBEGINSELECT DEPARTMENTNAME INTO P_NAME FROM DEPARTMENTSWHERE DEPARTMENTID=P_DEPTNO;EXCEPTION WHEN NO_DATA_FOUND THENDBMS_OUT.PUT_LINE(THE DEPARTMENT DOESN”T EXTSTS!);END proc_RETURN_DEPTINFO;（2） 函数的设计1. 创建名为FUNC_DEPT_MAGID的函数，以部门号为参数，返回部门管理的ID。CREATE OR REPLACE FUNCTION FUNC_DEPT_MAGID(P_DEPTNO DEPARTMENTS.DEPARTMENTID%TYPE)ASV_MAGID DEPARTMENTS.MANAGER_ID%TYPEBEGINSELECT MANAGER_ID INTO V_MAGID FROM DEPARTMENTS WHERE DEPARTMENTID=P_DEPTNO;RETURN V_MAGID;EXCEPTION WHEN NO_DATA_FOUND T