1. 실습 테이블 생성
Region
CREATE TABLE REGION
( REGIONID NUMBER
, REGIONNAME VARCHAR2(30)
, CONSTRAINT REGION_ID_PK PRIMARY KEY(REGIONID)
);
뷰 생성
SELECT R.REGIONID AS REGIONID
, R.REGIONNAME AS REGIONNAME
, ( SELECT COUNT(*)
FROM EMPLOYEE
WHERE REGIONID = R.REGIONID) AS DELCHECK
FROM REGION R;
SELECT R.REGIONID AS REGIONID, R.REGIONNAME AS REGIONNAME, ( SELECT COUNT(*) FROM EMPLOYEE WHERE REGIONID = R.REGIONID) AS DELCHECK FROM REGION R
;
CREATE OR REPLACE VIEW REGIONVIEW
AS
SELECT R.REGIONID AS REGIONID
, R.REGIONNAME AS REGIONNAME
, ( SELECT COUNT(*)
FROM EMPLOYEE
WHERE REGIONID = R.REGIONID) AS DELCHECK
FROM REGION R;
Position
CREATE TABLE POSITION
( POSITIONID NUMBER
, POSITIONNAME VARCHAR2(30)
, MINBASICPAY NUMBER
, CONSTRAINT POSITION_ID_PK PRIMARY KEY(POSITIONID)
);
뷰 생성
CREATE OR REPLACE VIEW POSITIONVIEW
AS
SELECT P.POSITIONID AS POSITIONID
, P.POSITIONNAME AS POSITIONNAME
, P.MINBASICPAY AS MINBASICPAY
, (SELECT COUNT(*)
FROM EMPLOYEE
WHERE POSITIONID = P.POSITIONID) AS DELCHECK
FROM POSITION P;
Department
CREATE TABLE DEPARTMENT
( DEPARTMENTID NUMBER
, DEPARTMENTNAME VARCHAR2(30)
, CONSTRAINT DEPARTMENT_ID_PK PRIMARY KEY(DEPARTMENTID)
);
뷰 생성
SELECT D.DEPARTMENTID AS DEPARTMENTID
, D.DEPARTMENTNAME AS DEPARTMENTNAME
,(SELECT COUNT(*)
FROM EMPLOYEE
WHERE DEPARTMENTID = D.DEPARTMENTID) AS DELCHECK
FROM DEPARTMENT D;
CREATE OR REPLACE VIEW DEPARTMENTVIEW
AS
SELECT D.DEPARTMENTID AS DEPARTMENTID
, D.DEPARTMENTNAME AS DEPARTMENTNAME
,(SELECT COUNT(*)
FROM EMPLOYEE
WHERE DEPARTMENTID = D.DEPARTMENTID) AS DELCHECK
FROM DEPARTMENT D;
SELECT *
FROM DEPARTMENTVIEW;
Employee
CREATE TABLE EMPLOYEE
( EMPLOYEEID NUMBER
, NAME VARCHAR2(30)
, SSN VARCHAR2(30)
, BIRTHDAY DATE
, LUNAR NUMBER(1) DEFAULT 0
, TELEPHONE VARCHAR2(40)
, DEPARTMENTID NUMBER
, POSITIONID NUMBER
, REGIONID NUMBER
, BASICPAY NUMBER
, EXTRAPAY NUMBER
, CONSTRAINT EMPLOYEE_ID_PK PRIMARY KEY(EMPLOYEEID)
, CONSTRAINT EMPLOYEE_DEPARTMENTID_FK FOREIGN KEY(DEPARTMENTID)
REFERENCES DEPARTMENT(DEPARTMENTID)
, CONSTRAINT EMPLOYEE_POSITIONID_FK FOREIGN KEY(POSITIONID)
REFERENCES POSITION(POSITIONID)
, CONSTRAINT EMPLOYEE_REGIONID_FK FOREIGN KEY(REGIONID)
REFERENCES REGION(REGIONID)
, CONSTRAINT EMPLOYEE_LUNAR_CK CHECK(LUNAR=0 OR LUNAR =1)
);
INSERT INTO EMPLOYEE(EMPLOYEEID, NAME, SSN, BIRTHDAY, LUNAR, TELEPHONE
, DEPARTMENTID, POSITIONID, REGIONID, BASICPAY, EXTRAPAY)
VALUES(EMPLOYEESEQ.NEXTVAL, '엄소연', CRYPTPACK.ENCRYPT('9411242234567', '9411242234567')
, TO_DATE('1994-11-24','YYYY-MM-DD'), 0, '010-7193-4562', 1,1,1, 1500000, 1500000);
로그인/로그아웃기능
ALTER TABLE EMPLOYEE
ADD(SSN1 CHAR(6), SSN2 VARCHAR2(50));
SELECT *
FROM EMPLOYEE;
UPDATE EMPLOYEE
SET SSN1 = SUBSTR(CRYPTPACK.DECRYPT(SSN,'9411242234567') ,1,6)
, SSN2 = CRYPTPACK.ENCRYPT(SUBSTR(CRYPTPACK.DECRYPT(SSN,'9411242234567'),7,7),SUBSTR(CRYPTPACK.DECRYPT(SSN,'9411242234567'),7,7))
WHERE EMPLOYEEID = 1;
INSERT INTO EMPLOYEE(EMPLOYEEID, NAME, SSN1, SSN2, BIRTHDAY, LUNAR, TELEPHONE
, DEPARTMENTID, POSITIONID, REGIONID, BASICPAY, EXTRAPAY)
VALUES(EMPLOYEESEQ.NEXTVAL, '정미경', '981009',CRYPTPACK.ENCRYPT('2345678', '2345678')
, TO_DATE('1998-10-09','YYYY-MM-DD'), 0, '010-3098-3091', 1,1,8, 1500000, 1500000);
ALTER TABLE EMPLOYEE
DROP COLUMN SSN;
ALTER TABLE EMPLOYEE
ADD GRADE NUMBER(1) DEFAULT 1;
SELECT *
FROM EMPLOYEE;
UPDATE EMPLOYEE
SET GRADE=0
WHERE EMPLOYEEID = 1;
SELECT *
FROM EMPLOYEE;
로그인 방식 구현
SELECT NAME
FROM EMPLOYEE
WHERE EMPLOYEEID = 아이디
AND SSN2 = 패스워드;
SELECT NAME
FROM EMPLOYEE
WHERE EMPLOYEEID = 2
AND SSN2 = CRYPTPACK.ENCRYPT('2345678','2345678');
SELECT NAME
FROM EMPLOYEE
WHERE EMPLOYEEID = 2
AND SSN2 = CRYPTPACK.ENCRYPT('1234566','1234566');
SELECT NAME
FROM EMPLOYEE
WHERE EMPLOYEEID = 1
AND SSN2 = CRYPTPACK.ENCRYPT('2234567','2234567');
SELECT NAME FROM EMPLOYEE WHERE EMPLOYEEID = 'ID문자열' AND SSN2 = CRYPTPACK.ENCRYPT('PW문자열','PW문자열')
;
뷰 생성
CREATE OR REPLACE VIEW EMPLOYEEVIEW
AS
SELECT E.EMPLOYEEID AS EMPLOYEEID
, E.NAME AS NAME
, E.SSN1 AS SSN
, TO_CHAR(E.BIRTHDAY, 'YYYY-MM-DD') AS BIRTHDAY
, E.LUNAR AS LUNAR
, DECODE(E.LUNAR, 0, '양력', 1, '음력') AS LUNARNAME
, E.TELEPHONE AS TELEPHONE
, E.DEPARTMENTID AS DEPARTMENTID
, (SELECT DEPARTMENTNAME
FROM DEPARTMENT
WHERE DEPARTMENTID = E.DEPARTMENTID) AS DEPARTMENTNAME
, E.POSITIONID AS POSITIONID
, (SELECT POSITIONNAME
FROM POSITION
WHERE POSITIONID = E.POSITIONID) AS POSITIONNAME
, E.REGIONID AS REGIONID
, (SELECT REGIONNAME
FROM REGION
WHERE REGIONID = E.REGIONID) AS REGIONNAME
, E.BASICPAY AS BASICPAY
, E.EXTRAPAY AS EXTRAPAY
, NVL(E.BASICPAY, 0) + NVL(E.EXTRAPAY, 0) AS PAY
, E.GRADE AS GRADE
FROM EMPLOYEE E;
SELECT EMPLOYEEID, NAME, SSN, BIRTHDAY, LUNAR, LUNARNAME, TELEPHONE, DEPARTMENTID, DEPARTMENTNAME, POSITIONID, POSITIONNAME, REGIONID, REGIONNAME, BASICPAY, EXTRAPAY, PAY, GRADE FROM EMPLOYEEVIEW
;