함수와 RETURN

함수의 RETURN과 SELECT, WHERE 및 PL/SQL 호출, 테이블 조회 함수의 행별 실행 비용

CREATE OR REPLACE FUNCTIONRETURNSELECT INTO사용자 정의 함수바인드 변수익명 블록show err

#핵심 요약

함수는 RETURN 타입을 선언하고 본문에서 RETURN 값으로 반드시 값 하나를 돌려줌

예제의 함수는 SQL 식처럼 SELECT 절과 WHERE 절에서 부를 수 있음

함수는 PL/SQL 블록 안이나 EXEC :변수 := 함수(값)처럼 바인드 변수에 담아 부를 수도 있음
프로시저처럼 EXEC 함수(값)으로 단독 실행하면 오류임(호출 오류와 실행 제약)

함수는 행마다 한 번씩 호출됨
함수 안에 SELECT가 있으면 그 SELECT도 행 수만큼 실행되어, 수백만 건 테이블에서는 이행 후 성능 장애의 단골 원인이 됨

#개념과 동작 원리

설명: 실습 emp_t가 6행이면 f_dept_cnt 안의 SELECT도 6번 돎
실습에서는 이것을 SQL 튜닝 수업의 스칼라 서브쿼리 예제와 연결했음
사용자 정의 함수를 스칼라 서브쿼리나 Result Cache로 바꿔 반복 실행을 줄이는 방법은 스칼라 서브쿼리와 인라인 뷰에 있음

SQL 안에서 부를 수 있는 함수의 조건(부작용 제한 등)은 기재 없음
실습 비교표는 "SELECT 안에서 부르는 함수는 안에서 COMMIT할 수 없다"고만 적었음(프로시저, 함수, 트리거 비교)

#필수 명령어 또는 SQL

#계산만 하는 함수

SQL*Plus (객체 소유 계정)객체 또는 설정을 변경함

CREATE OR REPLACE FUNCTION f_tax (p_sal IN NUMBER)
RETURN NUMBER
IS
  v_rate NUMBER;
BEGIN
  IF p_sal >= 3000 THEN
    v_rate := 0.10;
  ELSIF p_sal >= 2000 THEN
    v_rate := 0.05;
  ELSE
    v_rate := 0.02;
  END IF;

  RETURN ROUND(p_sal * v_rate);
END;
/

SQL*Plus (객체 소유 계정)조회만 함

SELECT ename, sal, f_tax(sal) AS tax, sal - f_tax(sal) AS net_sal
  FROM emp_t
 ORDER BY sal DESC;                    -- 결과: KING 5000 → 세금 500

결과 해석: 개별 실행 출력은 기재 없음

#테이블을 조회하는 함수

SQL*Plus (객체 소유 계정)객체 또는 설정을 변경함

CREATE OR REPLACE FUNCTION f_dept_cnt (p_deptno IN NUMBER)
RETURN NUMBER
IS
  v_cnt NUMBER;
BEGIN
  SELECT COUNT(*) INTO v_cnt
    FROM emp_t
   WHERE deptno = p_deptno;
  RETURN v_cnt;
END;
/

SQL*Plus (객체 소유 계정)조회만 함

SELECT ename, deptno, f_dept_cnt(deptno) AS dept_cnt
  FROM emp_t
 ORDER BY deptno;

SELECT ename, deptno
  FROM emp_t
 WHERE f_dept_cnt(deptno) >= 2;

결과 해석: 개별 실행 출력은 기재 없음

#직업별 급여 합계

SQL*Plus (객체 소유 계정)객체 또는 설정을 변경함

CREATE OR REPLACE FUNCTION find_sumsal (p_job IN VARCHAR2)
RETURN NUMBER
IS
  v_sumsal NUMBER;
BEGIN
  SELECT SUM(sal) INTO v_sumsal
    FROM emp
   WHERE job = p_job;
  RETURN v_sumsal;
END;
/
SHOW ERR

SQL*Plus (객체 소유 계정)조회만 함

SELECT ename, sal, job, find_sumsal(job) FROM emp;

결과 해석:

SHOW ERR(SHOW ERRORS)는 직전 컴파일의 오류를 보여 주는 SQL*Plus 명령임

#함수를 부르는 다른 방법

SQL*Plus (객체 소유 계정)조회만 함

BEGIN
  DBMS_OUTPUT.PUT_LINE('급여 2500의 세금 : ' || f_tax(2500));
END;
/

VARIABLE v_tax NUMBER
EXEC :v_tax := f_tax(2500)
PRINT v_tax                             -- 결과: 125

결과 해석: 개별 실행 출력은 기재 없음