User Defined Function : Function

User Defined Function : Function

• 프로시져와 유사한 구조

• 반환 값이 존재 --프로시져와 반대

• 일반 내장함수처럼 사용

  (사용형식)
  CREATE [OR REPLACE] FUNCTION 함수명
  변수명 모드 타입명[:=|DEFAULT 값,]
  : : : : : : : :
  변수명 모드 타입명[:=|DEFAULT 값])]
  RETURN 타입명 --세미콜론이 없음

   IS|AS   
      선언부
  BEGIN   
      실행부
        RETURN expr;   --반드시 변수 상수 수식이 나와야 함 끝인 RETURN 으로 결과값을 호출한 쪽에 돌려 줌;;;;
        
       [EXCEPTION
         예외처리;
         ]
   END;

  사용예) 사원번호를 입력 받아 부서내에서 입사순번을 출력하는 함수--형태가 달라짐 반환되는 값이 두개라면 변수 하나에 반환값 두개를 합쳐야함.

       (함수)
         CREATE OR REPLACE FUNCTION FN_EMP_HIRE_DATE(
              P_EID IN HR.EMP.EMPLOYEE_ID%TYPE)
              RETURN NUMBER --넘버는 숫자로 반환 타입
         IS
           V_ORDER_NUM NUMBER:=0;   -- 입사순번  반환될 데이터를 얘가 가지고 있을 것임.
         BEGIN
           SELECT A.ONUM INTO V_ORDER_NUM
             FROM (SELECT DEPARTMENT_ID AS DID,  --서브쿼리는 부서번소 사원번호에 따른 입사순번이 나오고 메인쿼리에선 사원번호가 같은 사람을 찾아서 입사순번 V_ORDER_NUM 으로 반환
                          EMPLOYEE_ID AS EID,
                          RANK() OVER(PARTITION BY DEPARTMENT_ID  -- 파티션 바이 뒤에 있는 컬럼으로 그룹을 만들어서 입사순번이 그룹 내에서 몇번 째인지를 순위매김
                                      ORDER BY HIRE_DATE ASC) AS ONUM
                     FROM HR.EMP) A
            WHERE A.EID = P_EID;
            RETURN V_ORDER_NUM;
         END;
  
      
      (실행)
        SELECT A.EMPLOYEE_ID AS 사원번호,
               A.EMP_NAME AS 사원명,
               B.DEPARTMENT_NAME AS 부서명,
               FN_EMP_HIRE_DATE(A.EMPLOYEE_ID) AS 입사순번
          FROM HR.EMP A, HR.DEPT B
         WHERE A.DEPARTMENT_ID = B.DEPARTMENT_ID
         ORDER BY 3,4;

  / 주문상품 /
  CREATE TABLE order_goods (
  prod_id char(6) NOT NULL, / 상품번호 /
  order_id NUMBER(10) NOT NULL, / 주문번호 /
  order_qty NUMBER(4) / 수량 /
  );

ALTER TABLE order_goods
ADD
CONSTRAINT PK_order_goods
PRIMARY KEY (
prod_id,
order_id
);

/ 고객 /
CREATE TABLE customer (
cust_id CHAR(5) NOT NULL, / 고객번호 /
cost_name VARCHAR(30), / 고객명 /
cost_addr VARCHAR2(100) / 주소 /
);

ALTER TABLE customer
ADD
CONSTRAINT PK_customer
PRIMARY KEY (
cust_id
);

/ 상품 /
CREATE TABLE goods (
prod_id char(6) NOT NULL, / 상품번호 /
prod_name VARCHAR2(30) NOT NULL, / 상품명 /
prod_price NUMBER(7) DEFAULT 0 / 단가 /
);

ALTER TABLE goods
ADD
CONSTRAINT PK_goods
PRIMARY KEY (
prod_id
);

/ 주문 /
CREATE TABLE order (
order_id NUMBER(10) NOT NULL, / 주문번호 /
order_date DATE DEFAULT sysdate, / 주문일자 /
order_amt number(8) DEFAULT 0, / 금액 /
cust_id CHAR(5) / 고객번호 /
);

ALTER TABLE order
ADD
CONSTRAINT PK_order
PRIMARY KEY (
order_id
);

ALTER TABLE order_goods
ADD
CONSTRAINT FK_goods_TO_order_goods
FOREIGN KEY (
prod_id
)
REFERENCES goods (
prod_id
);

ALTER TABLE order_goods
ADD
CONSTRAINT FK_order_TO_order_goods
FOREIGN KEY (
order_id
)
REFERENCES order (
order_id
);

ALTER TABLE order
ADD
CONSTRAINT FK_customer_TO_order
FOREIGN KEY (
cust_id
)
REFERENCES customer (
cust_id
);

    DROP TABLE CUSTOMER;
            DROP TABLE GOODS;
                    DROP TABLE ORDER_GOODS;