24.plsql함수
SET SERVEROUTPUT ON;
DESC EMP;
DECLARE
emp_id EMP.EMP_ID%TYPE;
emp_name EMP.EMP_NAME%TYPE;
emp_no EMP.EMP_NO%TYPE;
job_code EMP.JOB_CODE%TYPE;
V_CNT VARCHAR2(30);
BEGIN
emp_id := '&사번';
emp_name := '&사원명';
emp_no := '&주민번호';
job_code := '&직급코드';
--입력받은 JOB_CODE가 사용가능한 코드인지 체크(유효성 검사)
SELECT COUNT(*)
INTO V_CNT
FROM JOB
WHERE JOB_CODE = job_code;
--유효하지 않은 값이 입력된 경우
IF V_CNT = 0 THEN
INSERT INTO EMP(EMP_ID, EMP_NAME, EMP_NO, JOB_CODE)
VALUES (emp_id, emp_name, emp_no, job_code);
ELSE
DBMS_OUTPUT.PUT_LINE('직급코드를 확인하세요');
END IF;
END;
/
/*
<FUNCTION>
절차형 SQL을 활용하여 일련의 SQL 처리를 수행하고,
수행 결과를 단일 값으로 반환할 수 있는 절차형 SQL
[표현법]
CREATE FUNCTION 함수명
(
매개변수명 1 타입,
매개변수명 2 타입,
...
)
RETURN 데이터타입
IS
선언부 (변수 정의)
BEGIN
실행부 (로직 구현)
RETRUN 반환값; -- 프로시저랑 다르게 RETURN 구문이 추가된다.
EXCEPTION
예외처리부
END [함수명];
/
*/
-- 사번을 입력받아 급여를 반환하는 사용자 정의 함수 생성
CREATE OR REPLACE FUNCTION FN_GET_SALARY
(
P_EMP_ID EMP.EMP_ID%TYPE
)
RETURN VARCHAR2
IS
-- 선언부
V_SALARY VARCHAR2(20);
BEGIN
-- 실행부
-- 급여를 조회 후 변수에 담기
SELECT SALARY -- TO_CHAR(V_SALARY, '9,999,999');
INTO V_SALARY
FROM EMP
WHERE EMP_ID = P_EMP_ID;
-- 반환
RETURN TO_CHAR(V_SALARY, '9,999,999'); -- SELECT에서 바꿔도 가능
END;
/
SELECT FN_GET_SALARY('200') FROM DUAL;
SELECT FN_GET_SALARY(EMP_ID)
FROM EMP;
-- 주민등록번호를 입력받아서 성별을 반환하는 함수
-- FN_GET_GENDER(emp_no)
SELECT DECODE(SUBSTR(EMP_NO,8,1), '1','남','2','여','확인불가') 성별
FROM emp;
CREATE OR REPLACE FUNCTION FN_GET_GENDER(
-- 매개변수
P_EMP_NO EMP.EMP_NO%TYPE
)
RETURN CHAR
IS
V_GENDER CHAR(12); -- 한글인 경우 문자코드에 맞게 크기 설정
BEGIN
SELECT DECODE(SUBSTR(P_EMP_NO,8,1), '1','남','2','여','확인불가') 성별
-- 조회결과를 변수에 담아줍니다.
INTO V_GENDER
FROM DUAL; --EMP
--WHERE EMP_NO = P_EMP_NO;
-- 조회결과를 반환
RETURN V_GENDER;
END;
/
SELECT FN_GET_GENDER('621235-1985634') FROM DUAL;
SELECT FN_GET_GENDER(EMP_NO) 성별 FROM EMP;
-- 주민번호를 받아서 나이를 반환하는 함수
-- FN_GET_AGE
SELECT TO_CHAR(SYSDATE, 'YYYY') -(CASE
WHEN SUBSTR(emp_no,8,1) = 1 OR SUBSTR(emp_no,8,1) = 2 THEN 19||SUBSTR(emp_no,1,2)
WHEN SUBSTR(emp_no,8,1) = 3 OR SUBSTR(emp_no,8,1) = 4 THEN 19||SUBSTR(emp_no,1,2)
END) 나이
FROM emp;
CREATE OR REPLACE FUNCTION FN_GET_AGE (
P_EMP_NO EMP.EMP_NO%TYPE
)
RETURN NUMBER
IS
BEGIN
/*
SELECT TO_CHAR(SYSDATE, 'YYYY') -(CASE
WHEN SUBSTR(P_EMP_NO,8,1) = 1 OR SUBSTR(P_EMP_NO,8,1) = 2 THEN 19||SUBSTR(P_EMP_NO,1,2)
WHEN SUBSTR(P_EMP_NO,8,1) = 3 OR SUBSTR(P_EMP_NO,8,1) = 4 THEN 19||SUBSTR(P_EMP_NO,1,2)
END) 나이
INTO V_AGE
FROM DUAL;
RETURN V_AGE;
*/
RETURN TO_CHAR(SYSDATE, 'YYYY') -(CASE
WHEN SUBSTR(P_EMP_NO,8,1) = 1 OR SUBSTR(P_EMP_NO,8,1) = 2 THEN 19||SUBSTR(P_EMP_NO,1,2)
WHEN SUBSTR(P_EMP_NO,8,1) = 3 OR SUBSTR(P_EMP_NO,8,1) = 4 THEN 19||SUBSTR(P_EMP_NO,1,2)
END);
END;
/
/*
CREATE OR REPLACE FUNCTION FN_GET_AGE (
P_EMP_NO EMP.EMP_NO%TYPE
)
RETURN NUMBER
IS
V_AGE NUMBER;
BEGIN
SELECT TO_CHAR(SYSDATE, 'YYYY') -(CASE
WHEN SUBSTR(P_EMP_NO,8,1) = 1 OR SUBSTR(P_EMP_NO,8,1) = 2 THEN 19||SUBSTR(P_EMP_NO,1,2)
WHEN SUBSTR(P_EMP_NO,8,1) = 3 OR SUBSTR(P_EMP_NO,8,1) = 4 THEN 19||SUBSTR(P_EMP_NO,1,2)
END) 나이
INTO V_AGE
FROM DUAL;
RETURN V_AGE;
END;
/
*/
SELECT FN_GET_AGE('911129-1') 나이 FROM DUAL;
SELECT FN_GET_AGE(emp_no) 나이 FROM emp;
-- 사번을 입력받아 해당 사원의 보너스를 포함하는 연봉을 계산하고 리턴하는 함수를 생성
CREATE OR REPLACE FUNCTION FN_GET_YEARSALARY (
P_EMP_ID EMP.EMP_ID%TYPE
)
RETURN VARCHAR2
IS
-- 변수선언
v_sal emp.salary%TYPE;
v_bonus emp.bonus%TYPE;
BEGIN
-- 로직작성
SELECT salary, NVL(bonus,0)
INTO v_sal, v_bonus
FROM emp
WHERE emp_id = p_emp_id;
-- 반환
RETURN (v_sal+v_sal*v_bonus)*12;
END;
/
SELECT FN_GET_YEARSALARY(EMP_ID) 연봉 FROM EMP;
-- 변수선언
VARIABLE V_CALC NUMBER;
-- 함수 실행 후 반환값을 변수에 저장
EXEC :V_CALC := FN_GET_YEARSALARY('200');
PRINT V_CALC;