(11) SQL 모음

--create user middelpro identified by java;
--grant connect, resource, dba to middelpro;

drop table employee;
commit;

create table employee (
    empno varchar2(10) not null, -- 사원번호, 패턴 : 2010-입사년도/10-부서/10-직급/01-몇번째사원
    empname varchar2(30) not null, -- 사원명, 한글 10글자
    empjob varchar2(30) not null, -- 담당부서
    empcostat varchar2(30) not null, -- 재직상태
    empsal varchar2(9) not null, -- 급여
    emphp1 varchar2(3) not null, -- 전화번호 010
    emphp2 varchar2(4) not null, -- 전화번호 1234
    emphp3 varchar2(4) not null, -- 전화번호 5678
    empaddr varchar2(250) not null, -- 사원 주소
    empjoindate date not null, -- 입사일
    empretiredate date, -- 퇴사일
    emppass varchar2(20) not null, -- 비번
    empemail varchar2(200) not null, -- 이메일
    emppos varchar2(20) not null, -- 직급
    empPerCol varchar2(100) null, -- 퍼스널 컬러
    empPhoto varchar2(200) null, -- 프로필 사진
    constraint pk_empno primary key(empno)
);
commit;

select * from employee;

select * from employee where empjob = #{empjob}
;

DELETE FROM employee
WHERE empno = '1990204002';
commit;

INSERT INTO employee (empno, empname, empjob, empcostat, empsal, emphp1, emphp2, emphp3, empaddr, empjoindate, empretiredate, emppass, empemail, emppos, empPerCol, empPhoto) 
VALUES ('2023101001', '카즈하', '10', '재직중', '250', '010', '1234', '5678', '대전시 중구 오류동 101번지 11', sysdate, null, '12341234', 'bbbb7788@naver.com', '10', 'perColPri', null);
INSERT INTO employee (empno, empname, empjob, empcostat, empsal, emphp1, emphp2, emphp3, empaddr, empjoindate, empretiredate, emppass, empemail, emppos, empPerCol, empPhoto) 
VALUES ('2023202001', '윈터', '20', '재직중', '350', '010', '9999', '8888', '대전시 서구 도마동 10번지 33', sysdate, null, '12341234', 'bbbb7788@naver.com', '20', 'perColGray', null);
INSERT INTO employee (empno, empname, empjob, empcostat, empsal, emphp1, emphp2, emphp3, empaddr, empjoindate, empretiredate, emppass, empemail, emppos, empPerCol, empPhoto) 
VALUES ('2023204001', '원빈', '20', '재직중', '560', '010', '7234', '5341', '대전시 동구 퐁동 10번지 33', sysdate, null, '12341234', 'bbbb7788@naver.com', '40', 'perColDan', null);
INSERT INTO employee (empno, empname, empjob, empcostat, empsal, emphp1, emphp2, emphp3, empaddr, empjoindate, empretiredate, emppass, empemail, emppos, empPerCol, empPhoto) 
VALUES ('2023303001', '장동건', '30', '재직중', '420', '010', '9876', '4632', '대전시 대덕구 신마동 10번지 33', sysdate, null, '12341234', 'bbbb7788@naver.com', '30', 'perColSuc', null);
commit;

select 'private ' || 
        decode(lower(data_type), 'number', 'int ', 'String ') || 
        lower(column_name) || ';'
  from cols
 where lower(table_name) = 'employee';

select empno from employee where empno = '2008101010';
commit;

select empno from employee;
select max(empno) as maxempno from employee where empno like '20102020' || '%';

INSERT INTO employee (empno, empname, empjob, empcostat, empsal, emphp1, emphp2, emphp3, empaddr, empjoindate, empretiredate, emppass, empemail, emppos, emppercol, empphoto) 
VALUES (#{empno}, #{empname}, #{empjob}, '재직중', #{empsal}, #{emphp1}, #{emphp2}, #{emphp3}, #{empaddr}, sysdate, null, #{emppass}, #{empemail}, #{emppos}, #{emppercol}, #{empphoto})
;

UPDATE employee
SET
    empname = #{empname},
    empjob = #{empjob},
    empcostat = #{empcostat},
    empsal = #{empsal},
    emphp1 = #{emphp1},
    emphp2 = #{emphp2},
    emphp3 = #{emphp3},
    empaddr = #{empaddr},
    empjoindate = sysdate,
    emppass = #{emppass},
    empemail = #{empemail},
    emppos = #{emppos},
    emppercol = #{emppercol},
    empphoto = #{empphoto}
WHERE
    empno = #{empno}
;

--------------------------------------------------

drop table fileinfo;
commit;

create table fileinfo(
    fileno number not null, -- 파일 번호(자동 증가)
    filewriter varchar2(50) not null, -- 작성자 이름
    originfilename varchar2(250) not null, -- 원본 파일 이름
    savefilename varchar2(250) not null, -- 저장 파일 이름
    filesize number, -- 파일 크기
    filedate date not null, -- 파일 등록 날짜
    primary key (fileno, savefilename)
);

DROP SEQUENCE fileinfo_seq;
commit;

create sequence fileinfo_seq
start with 1 
increment by 1 
nomaxvalue nocache;

commit;

select 'private ' || 
        decode(lower(data_type), 'number', 'int ', 'String ') || 
        lower(column_name) || ';'
  from cols
 where lower(table_name) = 'fileinfo';
 
INSERT INTO fileinfo (fileno, filewriter, originfilename, savefilename, filesize, filedate) 
VALUES (fileinfo_seq.nextval, :v1, :v2, :v3, :v4, sysdate);

select * from fileinfo;

DELETE FROM fileinfo
WHERE fileno = 31;
commit;

------------------------------------------------------------

drop table sealinfo;
commit;

create table sealinfo(
    sealno number not null, -- 인감 번호(자동 증가)
    fileno varchar2(50) not null, -- 파일 테이블 참조키(fileno)
    empno varchar2(250) not null, -- 사원 테이블 참조키(empno)
    primary key (fileno)
);

DROP SEQUENCE sealinfo_seq;
commit;

create sequence sealinfo_seq
start with 1 
increment by 1 
nomaxvalue nocache;

commit;

select 'private ' || 
        decode(lower(data_type), 'number', 'int ', 'String ') || 
        lower(column_name) || ';'
  from cols
 where lower(table_name) = 'sealinfo';
 
select * from sealinfo;

DELETE FROM sealinfo
WHERE sealno = 1;
commit;

INSERT INTO sealinfo (sealno, fileno, empno) 
VALUES (sealinfo_seq.nextval, #{fileno}, #{empno})
;

--------------------------------------------------------------

drop table signlineinfo;
commit;

create table signlineinfo(
    signno number not null, -- 결제라인 번호(자동 증가)
    oriempno varchar2(10) not null, -- 결제라인 사원번호
    empnofir varchar2(10) not null, -- 최초 승인자 사원번호
    empnamefir varchar2(30) not null, -- 최초 승인자 이름
    empjobfir varchar2(30) not null, -- 최초 승인자 부서
    empposfir varchar2(20) not null, -- 최초 승인자 직급
    empnosec varchar2(10) null, -- 중간 승인자 사원번호
    empnamesec varchar2(30) null, -- 중간 승인자 이름
    empjobsec varchar2(30) null, -- 중간 승인자 부서
    emppossec varchar2(20) null, -- 중간 승인자 직급
    empnothi varchar2(10) null, -- 최종 승인자 사원번호
    empnamethi varchar2(30) null, -- 최종 승인자 이름
    empjobthi varchar2(30) null, -- 최종 승인자 부서
    empposthi varchar2(20) null, -- 최종 승인자 직급
    primary key (signno)
);

DROP SEQUENCE signlineinfo_seq;
commit;

create sequence signlineinfo_seq
start with 1 
increment by 1 
nomaxvalue nocache;

commit;

select 'private ' || 
        decode(lower(data_type), 'number', 'int ', 'String ') || 
        lower(column_name) || ';'
  from cols
 where lower(table_name) = 'signlineinfo';
 
select * from signlineinfo;

select * from signlineinfo where oriempno = '2023206001'
;

INSERT INTO signlineinfo (signno, oriempno, empnofir, empnamefir, empjobfir, empposfir, empnosec, empnamesec, empjobsec, emppossec, empnothi, empnamethi, empjobthi, empposthi) 
VALUES (sealinfo_seq.nextval, #{oriempno}, #{empnofir}, #{empnamefir}, #{empjobfir}, #{empposfir}, #{empnosec}, #{empnamesec}, #{empjobsec}, #{emppossec}, #{empnothi}, #{empnamethi}, #{empjobthi}, #{empposthi})
;

DELETE FROM signlineinfo
WHERE oriempno = #{oriempno}
;

----------------------------------------------------------------

create table jobinfo(
    jobno number not null, -- 번호(자동 증가)
    empjob varchar2(30) not null, -- 부서번호
    empjobnm varchar2(100) not null, -- 부서명
    primary key (jobno)
);
commit;

DROP SEQUENCE jobinfo_seq;
commit;

create sequence jobinfo_seq
start with 1 
increment by 1 
nomaxvalue nocache;

commit;

select 'private ' || 
        decode(lower(data_type), 'number', 'int ', 'String ') || 
        lower(column_name) || ';'
  from cols
 where lower(table_name) = 'jobinfo';
 
select * from jobinfo;

INSERT INTO jobinfo (jobno, empjob, empjobnm) 
VALUES (jobinfo_seq.nextval, '10', '인사팀');
INSERT INTO jobinfo (jobno, empjob, empjobnm) 
VALUES (jobinfo_seq.nextval, '20', '개발팀');
INSERT INTO jobinfo (jobno, empjob, empjobnm) 
VALUES (jobinfo_seq.nextval, '30', '사업관리팀');
commit;

create table posinfo(
    posno number not null, -- 번호(자동 증가)
    emppos varchar2(30) not null, -- 직급번호
    empposnm varchar2(100) not null, -- 직급명
    primary key (posno)
);
commit;

DROP SEQUENCE posinfo_seq;
commit;

create sequence posinfo_seq
start with 1 
increment by 1 
nomaxvalue nocache;

commit;

select 'private ' || 
        decode(lower(data_type), 'number', 'int ', 'String ') || 
        lower(column_name) || ';'
  from cols
 where lower(table_name) = 'posinfo';
 
select * from posinfo;

INSERT INTO posinfo (posno, emppos, empposnm) 
VALUES (posinfo_seq.nextval, '10', '사원');
INSERT INTO posinfo (posno, emppos, empposnm) 
VALUES (posinfo_seq.nextval, '20', '대리');
INSERT INTO posinfo (posno, emppos, empposnm) 
VALUES (posinfo_seq.nextval, '30', '과장');
INSERT INTO posinfo (posno, emppos, empposnm) 
VALUES (posinfo_seq.nextval, '40', '차장');
INSERT INTO posinfo (posno, emppos, empposnm) 
VALUES (posinfo_seq.nextval, '50', '부장');
INSERT INTO posinfo (posno, emppos, empposnm) 
VALUES (posinfo_seq.nextval, '60', '임원');
commit;

drop table signacceptinfo;
commit;

create table signacceptinfo( -- 품의서 테이블
    acceptno number not null, -- 품의서 번호(자동 증가)
    acceptoriempno varchar2(10) not null, -- 품의서 사원번호
    acceptoriempphoto varchar2(200) null, -- 품의서 사원 인감번호
    acceptnofir varchar2(10) not null, -- 최초 승인자 사원번호
    acceptnamefir varchar2(30) not null, -- 최초 승인자 이름
    acceptphotofir varchar2(200) null, -- 최초 승인자 인감번호
    acceptnosec varchar2(10) null, -- 중간 승인자 사원번호
    acceptnamesec varchar2(30) null, -- 중간 승인자 이름
    acceptphotosec varchar2(200) null, -- 중간 승인자 인감번호
    acceptnothi varchar2(10) null, -- 최종 승인자 사원번호
    acceptnamethi varchar2(30) null, -- 최종 승인자 이름
    acceptphotothi varchar2(200) null, -- 최종 승인자 인감번호
    acceptoriempnm varchar2(30) not null, -- 품의서 사원명
    acceptoriempjob varchar2(30) not null, -- 품의서 사원부서
    acceptoriemppos varchar2(20) not null, -- 품의서 사원직급
    accepttitle varchar2(2000) not null, -- 품의서 제목
    acceptcont varchar2(2000) not null, -- 품의서 내용
    acceptdate date, -- 품의서 작성일
    primary key (acceptno)
);

DROP SEQUENCE signacceptinfo_seq;
commit;

create sequence signacceptinfo_seq
start with 1 
increment by 1 
nomaxvalue nocache;

commit;

select 'private ' || 
        decode(lower(data_type), 'number', 'int ', 'String ') || 
        lower(column_name) || ';'
  from cols
 where lower(table_name) = 'signacceptinfo';
 
select * from signacceptinfo;

select * from employee a, jobinfo b, posinfo c 
where a.empjob = b.empjob and a.emppos = c.emppos
and empno = #{empno} and emppass = #{emppass}
;

select * from employee a, jobinfo b, posinfo c 
where a.empjob = b.empjob and a.emppos = c.emppos
and empno = #{empno} and emppass = #{emppass};

select * from employee where empno = #{empno} and emppass = #{emppass}