#----- MySQL5 Database Store Procedure Script -----
#----- Table Owner:mylibrary,Table:emp -----

Delimiter $$

#----- Drop Table And Create a New Table -----
use mylibrary$$
drop table  IF EXISTS emp$$
Create Table mylibrary.emp(
EMPNO int,
ENAME varchar(45),
JOB varchar(45),
MGR int,
HIREDATE datetime,
SAL double(22,0),
COMM double(22,0),
DEPTNO int,
CLOBCOL varchar(45),
FLOATCOL float(12,0),
primary key (EMPNO,ENAME)
)engine=innodb;
$$

 


#----- Insert Store Procedure -----
drop procedure if exists UserSP_Insertemp $$
Create Procedure UserSP_Insertemp
(
in v_EMPNO int,
in v_ENAME varchar(45),
in v_JOB varchar(45),
in v_MGR int,
in v_HIREDATE datetime,
in v_SAL double,
in v_COMM double,
in v_DEPTNO int,
in v_CLOBCOL varchar(45),
in v_FLOATCOL float
)
Begin
insert into mylibrary.emp (EMPNO,ENAME,JOB,MGR,HIREDATE,SAL,COMM,DEPTNO,CLOBCOL,FLOATCOL) values (v_EMPNO,v_ENAME,v_JOB,v_MGR,v_HIREDATE,v_SAL,v_COMM,v_DEPTNO,v_CLOBCOL,v_FLOATCOL);
end;
$$


#----- Delete Store Procedure -----
drop procedure if exists UserSP_Deleteemp $$
Create Procedure UserSP_Deleteemp
(
v_EMPNO int,
v_ENAME varchar(45)
)
begin
Delete from mylibrary.emp where  EMPNO=v_EMPNO and ENAME=v_ENAME;
end;
$$


#----- Update Store Procedure -----
drop procedure if exists UserSP_Updateemp $$
Create Procedure UserSP_Updateemp
(
in v_EMPNO int,
in v_ENAME varchar(45),
in v_JOB varchar(45),
in v_MGR int,
in v_HIREDATE datetime,
in v_SAL double,
in v_COMM double,
in v_DEPTNO int,
in v_CLOBCOL varchar(45),
in v_FLOATCOL float
)
begin
update mylibrary.emp set EMPNO=v_EMPNO,ENAME=v_ENAME,JOB=v_JOB,MGR=v_MGR,HIREDATE=v_HIREDATE,SAL=v_SAL,COMM=v_COMM,DEPTNO=v_DEPTNO,CLOBCOL=v_CLOBCOL,FLOATCOL=v_FLOATCOL where  EMPNO=v_EMPNO and ENAME=v_ENAME;
end;
$$
delimiter ;