[PL/SQL] CREATE FUNCTION

들어가며

조건에 따른 COUNT를 멋지게 할 수 있는 방법을 고민하며,
CASE WHEN THEN~ , OVER(PARTITON BY) 말고는 뭐가 있을까 하다가
PL/SQL 에 손대보기로 하였다.

pre-required

• SQL 서버 버전에 따라서 함수 생성 옵션을 on 해주어야만 한다.

show global variables like 'log_bin_trust_function_creators';
SET GLOBAL log_bin_trust_function_creators = 1;

• 함수 생성 권한이 없을 경우 발생할 수 있는 옵션 에러

This function has none of DETERMINISTIC, NO SQL, or READS SQL DATA in its declaration and binary logging is enabled

선언부

• 함수의 이름, parameter, return type을 지정한다.
• CREATE OR REPLACE로 작성할 경우 drop function 하지 않아도 될 것이다.
• parameter 선언 부에서 name label과 함께 argument의 data type을 명시해야 하는 것에 주의!
• tablename.columnname%TYPE [:=defaultvalue] 와 같이 사용하여 datatype을 구할 수도 있다.

DROP FUNCTION IF EXISTS get_result_count;
CREATE FUNCTION get_result_count(e_id NUMBER,e_name VARCHAR(20),d_type VARCHAR(20)) RETURNS NUMBER

-- EMPLOYEES.FIRST_NAME%TYPE

구현부

• 예외처리는 optional
• BEGIN/DECLARE/END로 구성된다.
• 로직 안에서 사용하고 반환할 변수는 DECLARE 문에서 선언된다.
• 아래의 로직은 조건에 만족하는 record 의 수를 반환한다.
• SELECT의 결과를 INTO 로 declare 한 변수에 copy한다.

BEGIN
    DECLARE r_count INT;
    SELECT count(*)
        INTO r_count
        FROM oms_ob_call_results
        WHERE EMPLOYEE_ID = e_id AND FIRST_NAME = e_name AND DEPARTMENT_ID = d_type;
    RETURN r_count;
END;

사용하기

SELECT EMPLOYEE_ID, FIRST_NAME, get_result_count(EMPLOYEE_ID, FIRST_NAME, 'SALES') as sales_number, get_result_count(strategy_id, call_list_id, 'MARKETING') as marketing_number
FROM EMPLOYEE;