-- 데이터가 아주 많을 때, FK 제약조건을 사용하지 않고 SQL에서 구현하기...

DROP TABLE EMPX
/
CREATE TABLE EMPX(EMPNO NUMBER(6),ENAME VARCHAR2(10),
                  DEPTNO NUMBER(2))
/

CREATE OR REPLACE FUNCTION EMPX_I(EMPNO NUMBER,ENAME VARCHAR2,
                                  DEPTNO NUMBER)
RETURN NUMBER
IS
BEGIN
   IF DEPTNO IN(10,20,30,40) THEN
      INSERT INTO EMPX VALUES(EMPNO,ENAME,DEPTNO);
      RETURN 1;
   ELSE
      RETURN 0;
   END IF;
END;
/


SET SERVEROUTPUT ON

DECLARE
RET NUMBER(3);
BEGIN
 RET := EMPX_I(101,'현재',10);
 DBMS_OUTPUT.PUT_LINE(RET);
END;
/

DECLARE
RET NUMBER(3);
BEGIN
 RET := EMPX_I(102,'나영',11);
 DBMS_OUTPUT.PUT_LINE(RET);
END;
/

select * from empx
/
SELECT   ENAME,
   DECODE(DEPTNO,
                           10, 'ACCOUNTING',
           20, 'RESEARCH',
           30, 'SALES',
           40, 'OPERATIONS',
           'etc')
 FROM EMPX
/