oracle企业员工管理课程设计

2013-2014学年第二学期《大型数据库技术》考查报告课题名称：企业员工系统班级：12软工设计二班学号：1215115274姓名：吕祖豪成绩：2014年5月1．课题概述1.1 课题简介随着时代的手工记录信息、小型的数据库已经满足不了大型企业的需要，Oracle数据的出现给大型公司提供了便利的条件，此系统则是实现企业员工信息的基本管理！1.2 功能描述该数据库主要实现的功能是用户的登录，和用户密码的修改。

企业员工基本信息的增加、删除、修改、查询，员工工资和出勤情况的登记和查询！2、数据库设计及实现2.1 数据库表清单此部分主要介绍数据库所有数据表的清单，包括表的类型、表的名称和中文含义。

用户表（user）字段名含义字段类型约束username 用户名Char（5）主键userpassword 密码Varchar2（10）NOT NULL企业员工表（staff）字段名含义字段类型约束sno 员工编号Char（10）主键sname 员工姓名Char(5) NOT NULLssex 员工性别Char(2) Check（‘男’，‘女’）sage 员工年龄intstel 员工电话Char(11)hiredate 入职日期date NOT NULLdeptno 部门编号Char(5) 与dept表中的deptno外键关联员工工资表（salary）字段名含义字段类型约束sname 员工姓名Char(10) 主键job 员工工作Cahr(5)sal 员工基本工资Number(7,2)bonus 员工奖金Number(7,2)deptno 部门编号Char(5) 与”dept”表中的deptno外键关联员工部门表（dept）字段名含义字段类型约束deptno 部门编号Char（5）主键dname 部门名称Char（5）location 部门地址Char（10）员工出勤表（attendance）字段名含义字段类型约束sno 员工编号Char（10）主键sname 员工姓名Char（5）与salary表中的sname外键关联Attendance_day 出勤天数intLost_day 缺勤天数int2.2 创建表空间create tablespace mytbsdatafile 'E:\mytbs1.dbf' size 250M,'F:\mytbs2.dbf' size 250M;2.3 创建用户配置文件要求密码的生存周期为30天，允许输入错误的密码不得超过5次，超过5次密码锁2天。

create profile lock_count limitpassword_life_time 30failed_login_attempts 5password_lock_time 2;2.4 创建用户及分配权限create user lzh identified by 1215115274default tablespace MYTBS;grant connect，resource to lzh;2.5 基本表实现2.5.1用户表的实现（1）users表的功能概述。

该表主要用于实现系统用户登录的用户名和密码的存储，存储在默认的表空间MYSTB中。

（2）表的实现代码create table users(username char(5),userpassword varchar2(10) NOT NULL,constraint users_username_pk primary key (username))2.5.2部门表的实现(1)dept表的功能描述该表主要用于实现存储员工的部门信息，包括部门编号，部门名称，部门所在地址，存储在默认的表空间MYTBS中。

（2）表的实现代码create table dept(deptno char(5),dname char(5),loction char(10),constraint dept_sdeptno_pk primary key(deptno));2.6 索引表的实现2.6.1 工资表（salary）的实现（1）表的功能概述。

主要描述表的功能、表的存储空间的分配等该表主要用于存储员工的工资信息包括员工的姓名、工作、基本工资、奖金和部门号。

由于需要是实现多次查询，所以创建了索引表，存储在默认的表空间MYTBS中。

（2）表的实现代码create table salary(sname char(5),job char(5),sal number(7,2),bonus number(7,2),deptno char(5),constraint salary_sname_pk primary key(sname),constraint salary_deptno_fk foreign key(deptno) references dept(deptno))organization indextablespace mytbs;2.7 分区表的实现2.7.1员工表(staff)列表分区的实现create table staff(sno char(10),sname char(5) NOT NULL,ssex char(2),sasge int,stel char(11),hiredate date NOT NULL,constraint staff_sno_pk primary key(sno),constraint staff_ssxe_ck check(ssex in('男','女')))partition by list (ssex)(partition staff_male values('男') tablespace mytbs,partition staff_female values('女') tablespace users)/2.7.2 员工出勤表（attendance）散列分区表create table attendance(sno Char(10),sname char(5),attendance_day int,lost_day int,constraint attendance_sno_pk primary key(sno),constraint attendance_sname_fk foreign key(sname) references salary(sname) )partition by hash(sno)(partition p1 tablespace mytbs,partition p2 tablespace users)3. 功能实现（1）用户登录模块：CREATE OR REPLACE PROCEDURE login(v_username in VARCHAR2,v_userpassword in varchar2,FLAG OUT int)iscounts number(2);pcounts number(2);BEGINSELECT COUNT(*) INTO counts FROM USERS WHERE username=v_username;IF COUNTS>0 THENSELECT COUNT(*) INTO pcounts FROM USERS WHERE userpassword=v_userpassword; IF PCOUNTS>0 THENFLAG:=1;ELSIF PCOUNTS=0 THENFLAG:=-1;END IF;ELSEFLAG:=0;END IF;End login;登录模块测试代码：declarev_num int;v_username char(10);v_userpassword varchar2(10);Beginv_username :='&username';v_userpassword :=&userpassword;login(v_username,v_userpassword,v_num); dbms_output.put_line(v_num);end;(2)员工信息添加模块：create or replace function add_staff(addstaff in staff%rowtype,information in out varchar2) return varchar2isinfrormation varchar2(10);begininsert into staff values(addstaff.sno,addstaff.sname,addstaff.ssex,addstaff.sasge,addstaff.stel,addstaff.hiredate);information := '员工信息添加成功!';return information;end add_staff;测试代码：declareinformation varchar2(20);v_add_staff staff%rowtype;beginv_add_staff.sno:=004;v_add_staff.sname:='李四';v_add_staff.ssex:='男';v_add_staff.sasge:=22;v_add_staff.stel:=158********;v_add_staff.hiredate:=to_date('2011-05-01','YY-MM-DD'); information :=add_staff(v_add_staff);dbms_output.put_line(information);*end;/（3）员工信息删除模块：create or replace function delete_staff(v_sno in staff.sno%type) return varchar2Isinformation varchar2(20);begindelete from staff where sno=v_sno;information :=’员工信息删除成功！’;end delete_staff;测试代码：declareinformation varchar2(20);begininformation :=delete_staff(001);dbms_output.put_line(information);end;(4)员工信息查找create or replace function find_staff(v_sno in staff.sno%type) return staff % rowtypeisshow_staff staff % rowtype;beginselect * into show_staff from staff where sno=v_sno;dbms_output.put_line(show_staff.sno||’’||show_staff.sname||’’||show_staff.ssex||’’||show_staff.sas ge||’’||show_staff.stel||’’||show_staff.hiredate);return show_staff;exceptionwhen no_data_found thendbms_output.put_line(‘没有此员工！’);end find_staff;测试代码：declareshow_staff staff%rowtype;beginshow_staff:=find_staff(1);end;(5)员工信息的修改create or replace procedure alter_staff(find_staff in stff%rowtype,v_sno in staff.sno%type) isbeginupdate staff set sno=find_staff.sno,sname=find_staff.sname,ssex=find_staff.ssex, sasge=find_staff.sasge,stel=find_staff.stel,hiredate=find_staff.hiredatewhere sno=v_sno;dbms_output.put_line('修改成功！');end alter_staff;测试代码：declarev_sno staff.sno %type;show_staff staff%rowtype;begindbms_output.put_line('输入要修改的员工编号：');v_sno :=&sno;show_staff.sno:=v_sno;Show_staff.sname:='金久茂';Show_staff.ssex:='男';show_staff.sasge:=23;Show_staff.stel:=154785;Show_staff.hiredate:=to_date('2012-11-25','yy-m m-dd');alter_staff(show_staff,v_sno);end;（6）员工出勤情况的录入create or replace function add_attendance(v_add_attendance in attendance%rowtype)return varchar2isinformation varchar2(10);begininsert into attendance values(v_add_attendance.sno,v_add_attendance.sname,v_add_attendance.attendance_day,v_add_attenda nce.lost_day);information := '添加成功!';return information;end add_attendance;declareinformation varchar2(20);v_add_attendance attendance%rowtype;beginv_add_attendance.sno:=002;v_add_attendance.sname:='吕祖豪';v_add_attendance.attendance_day:=20;v_add_attendance.lost_day:=2;Information :=add_attendance(v_add_attendance);dbms_output.put_line(information);end;(7)员工工资录入：create or replace function add_salary(v_add_salary in salary %rowtype)return varchar2isinformation varchar2(10);begininsert into salary values(v_add_salary.sname,v_add_salary.job,v_add_salary.sal,v_add_salary.bonus,v_add_salary.deptno); information := '添加成功!';return information;end add_salary;测试代码：declareinformation varchar2(20);v_add_salary salary%rowtype;beginv_add_salary.sname:='王森';v_add_salary.job:='职员';v_add_salary.sal:=1500; v_add_salary.bonus:=100;v_add_salary.deptno:=4;Information :=add_salary(v_add_salary);dbms_output.put_line(information);end;（8）员工姓名，工资，出勤查询create or replace package select_informationistype v_record is record(v_sno staff.sno%type,v_sname staff.sname%type,v_ssex staff.ssex%type,v_sage staff.sasge%type,v_deptno staff.deptno%type,v_sal salary.sal%type,v_bonus salary.bonus%type,v_job salary.job%type,v_attendance_day int,v_lost_day int);function staff_salary_attendance(v_sno in staff.sno%type) return v_record;end select_information ;create or replace package body select_informationisfunction staff_salary_attendance(v_sno in staff.sno%type) return v_recordisshow_staff v_record;beginselectstaff.sno,staff.sname,staff.ssex,staff.sasge,staff.deptno,salary.sal,salary.bonus,salary.jo b,attendance.attendance_day,attendance.lost_day into show_staff fromstaff,salary,dept,attendancewhere staff.sname=salary.sname and salary.sname=attendance.sname and staff.sno=v_sno; dbms_output.put_line(show_staff.v_sno||' '||show_staff.v_sname||' '||show_staff.v_ssex||' '||show_staff.v_sage||' '||show_staff.v_deptno||' '||show_staff.v_sal||' '||show_staff.v_bonus||' '||show_staff.v_job ||' '||show_staff.v_deptno ||' '||show_staff.v_attendance_day||' '||show_staff.v_lost_day );return show_staff;end staff_salary_attendance;end select_information ;测试代码：declarev_sno staff.sno %type;v_result select_information.v_record;begindbms_output.put_line('请输入要查询的员工的编号：');v_result:=select_information.staff_salary_attendance(v_sno);end;。