create or replace package SS_PMTH_ADDNEW_PACKAGE Is
/*** Header Remark
*Description:Remark Header Template
*Create Date:2007.09.14
*Create By:minghuiliu
*Version:1.0.0
*Modification Information:(Module/Project/CRNo-Date_Version-Author_Description
Header Remark ***/

/*** Body Remark
* Modification Information :(Modification Information:(Module/Project/CRNo-Date_Version-Author_Description)
Body Remark ***/





--存储过程，实现新增5名员工的信息
create or replace procedure Employee_add_p(EMP_SEQUENCE_V in varchar2, EMP_NUMBER_V in varchar2,EMP_NAME_V in varchar2,EMP_SEX_V in varchar2,EMP_IN_DATE_D date,EMP_IS_WHMAN_V in varchar2,EMP_BEWHMAN_DATE_D date ) is
/*
EMP_SEQUENCE_V :输入参数 为新增的序号
EMP_NUMBER_V :输入参数 为新增的员工工号
EMP_NAME_V :输入参数 为新增的员工姓名
EMP_SEX_V :输入参数 为新增员工的性别
EMP_IN_DATE_D :输入参数 为新增员工的入职日
EMP_IS_WHMAN_V :输入参数 新增员工是否为仓管
EMP_BEWHMAN_DATE_D :输入参数 新增员工成为仓管的时间
*/
begin
insert into SS_PMTH_EMPLOYEE values(EMP_SEQUENCE_V,EMP_NUMBER_V,EMP_NAME_V,EMP_SEX_V,EMP_IN_DATE_D,EMP_IS_WHMAN_V,EMP_BEWHMAN_DATE_D);
commit;
end;




--存储过程，实现新增3个仓库的信息
create or replace procedure Storage_add_p(STOR_SEQUENCEE_V in varchar2, STOR_NAME_V in varchar2,STOR_DESCRIBING_V in varchar2,STOR_ESTABLISH_DATE_D date,STOR_WH_NUMBER_V in varchar2) is
/*
STOR_SEQUENCEE_V :输入参数 为新增的序号
STOR_NAME_V :输入参数 为新增的仓库的名字
STOR_DESCRIBING_V :输入参数 对新增仓库的描述
STOR_ESTABLISH_DATE_D :输入参数 为新增仓库的创建日期
STOR_WH_NUMBER_V :输入参数 为新增仓库的管理人员号码
*/
begin
insert into SS_PMTH_STORAGE values(STOR_SEQUENCEE_V,STOR_NAME_V,STOR_DESCRIBING_V,STOR_ESTABLISH_DATE_D,STOR_WH_NUMBER_V);
commit;
end;

--创建仓库管理人员视图，栏位：仓库名称，仓库描述，员工工号，员工姓名
create or replace view warehouseman as
select
A.STOR_NAME,
A.STOR_DESCRIBING,
B.EMP_NUMBER,
B.EMP_NAME
From
SS_PMTH_STORAGE A,SS_PMTH_EMPLOYEE B
where A.STOR_WH_NUMBER=B.EMP_NUMBER ;

--游标的使用
-- Created on 2007-9-17 by MINGHUILIU
declare
-- Local variables here
CURSOR cur_warehouseman IS
select
A.STOR_NAME,
A.STOR_DESCRIBING,
B.EMP_NUMBER,
B.EMP_NAME,
B.EMP_IS_WHMAN
From SS_PMTH_STORAGE A
,SS_PMTH_EMPLOYEE B
where A.STOR_WH_NUMBER =B.EMP_NUMBER;
begin
for r1 in cur_warehouseman loop
update apps.SS_PMTH_EMPLOYEE spe
set spe.EMP_IS_WHMAN='Y'
WHERE spe.EMP_NUMBER =r1.EMP_NUMBER;
end loop;
end;

--在Package 中新增一个 Procedure, 名称 test, 无参数, 依次调用 新增员工、新增仓库、
--根据仓库维护的仓管人员更新员工信息
create or replace procedure test()
begin
Declare
--调用存储过程，新增员工信息
EMP_SEQUENCE_V varchar2(10);
EMP_NUMBER_V varchar2(10);
EMP_NAME_V varchar2(10);
EMP_SEX_V varchar2(1);
EMP_IN_DATE_D date;
EMP_IS_WHMAN_V varchar2(1);
EMP_BEWHMAN_DATE_D date;
begin

for i in 1..5 Loop
--循环新增员工信息
EMP_SEQUENCE_V := i ;
EMP_NUMBER_V :='000' || i;
EMP_NAME_V := 'luc6';
EMP_SEX_V:='M';
EMP_IN_DATE_D :=sysdate();
EMP_IS_WHMAN_V :='N';
EMP_BEWHMAN_DATE_D :=sysdate();
--调用上面实现的过程新增信息
Employee_add_p(EMP_SEQUENCE_V,EMP_NUMBER_V,EMP_NAME_V,EMP_SEX_V,EMP_IN_DATE_D,EMP_IS_WHMAN_V,EMP_BEWHMAN_DATE_D);
end loop;
end;

Declare
--调用存储过程，新增仓库信息
STOR_SEQUENCEE_V varchar2(10);
STOR_NAME_V varchar2(10);
STOR_DESCRIBING_V varchar2(40);
STOR_ESTABLISH_DATE_D date ;
STOR_WH_NUMBER_V varchar2(10);
i integer;
begin
i:=1;

while i<=3 loop
--循环新增仓库信息
STOR_SEQUENCEE_V := i ;
STOR_NAME_V :=i;
STOR_DESCRIBING_V := 'stor';
STOR_ESTABLISH_DATE_D:=sysdate();
STOR_WH_NUMBER_V :='000' || i;

--调用上面实现的过程新增信息
Storage_add_p(STOR_SEQUENCEE_V,STOR_NAME_V,STOR_DESCRIBING_V,STOR_ESTABLISH_DATE_D,STOR_WH_NUMBER_V);
i:=i+1;
end loop;
end;

--根据仓管人员工号更新员工表中是否为仓管人员的栏位
-- Created on 2007-9-17 by MINGHUILIU
declare
-- Local variables here
CURSOR cur_warehouseman IS
select
A.STOR_NAME,
A.STOR_DESCRIBING,
B.EMP_NUMBER,
B.EMP_NAME,
B.EMP_IS_WHMAN
From SS_PMTH_STORAGE A
,SS_PMTH_EMPLOYEE B
where A.STOR_WH_NUMBER =B.EMP_NUMBER;
begin
for r1 in cur_warehouseman loop
update apps.SS_PMTH_EMPLOYEE spe
set spe.EMP_IS_WHMAN='Y'
WHERE spe.EMP_NUMBER =r1.EMP_NUMBER;
end loop;
end;
end;

//触发器的使用
create or replace trigger UpdateEmployee_BEWHMAN_DATE_T
after update of EMP_IS_WHMAN on ss_pmth_employee
begin
set EMP_BEWHMAN_DATE=sysdate
end ;

--在Package 中新增一个 Procedure, 名称 test, 无参数, 依次调用 新增员工、新增仓库、
--根据仓库维护的仓管人员更新员工信息
create or replace procedure test()
begin
Declare
--调用存储过程，新增员工信息
EMP_SEQUENCE_V varchar2(10);
EMP_NUMBER_V varchar2(10);
EMP_NAME_V varchar2(10);
EMP_SEX_V varchar2(1);
EMP_IN_DATE_D date;
EMP_IS_WHMAN_V varchar2(1);
EMP_BEWHMAN_DATE_D date;
v_seq Number;
begin

for i in 1..5 Loop
--循环新增员工信息
Select SS_PMTH_employee_S.Nextval Into v_seq From dual;
EMP_SEQUENCE_V :=to_char(v_seq);
--EMP_SEQUENCE_V := i ;
EMP_NUMBER_V :=lpad(i,8,'0');
EMP_NAME_V := 'luc6';
EMP_SEX_V:='M';
EMP_IN_DATE_D :=sysdate();
EMP_IS_WHMAN_V :='N';
EMP_BEWHMAN_DATE_D :=sysdate();
--调用上面实现的过程新增信息
Employee_add_p(EMP_SEQUENCE_V,EMP_NUMBER_V,EMP_NAME_V,EMP_SEX_V,EMP_IN_DATE_D,EMP_IS_WHMAN_V,EMP_BEWHMAN_DATE_D);
end loop;
commit;
end;

-- Create sequence
create sequence SS_PMTH_employee_S
minvalue 1
maxvalue 5
start with 1
increment by 1
cache 2;