9. PL/SQL 내장 프로시저 및 함수 소개

9. PL/SQL 내장 프로시저 및 함수 소개

 

A. 프로시저 및 함수

 

 

§명명된 PL/SQL 블록

§PL/SQL 서브프로램

§블록 구조

-선택적 선언 섹션 (DECLARE 키워드 사용X)

     IS, AS 키워드 다음에 나옴

-필수 실행 섹션 (BEGIN), END 키워드로 끝남

-선택적 예외 처리 섹션(EXCEPTION)

 

B. 익명 블록  VS 서브 프로그램

- 차이점

 

 

C. 프로시저 : 구문

 

 

§MODE

-IN (기본값) // OUT // IN OUT

§DATATYPE  - 파라미터의 데이터유형은 명시적 크기 가질 수
    없음 -> %TYPE 사용

 

D. PARAMETER MODES

 

E. 프로시저 작성 예제

 

CREATE TABLE dept

AS SELECT * FROM departments;

 

CREATE OR REPLACE PROCEDURE add_dept IS

  v_dept_id dept.department_id%TYPE;

  v_dept_name dept.department_name%TYPE;

BEGIN

  v_dept_id := 280;

  v_dept_name := ‘ST-Curriculum’;

  INSERT INTO dept (department_id, department_name)

  VALUES (v_dept_id, v_dept_name);

  DBMS_OUTPUT.PUT_LINE (‘ Inserted ‘ ||

     SQL%ROWCOUNT || ‘ row ‘ );

END;

/

F. 객체 및 객체 소스 확인

§user_objects 테이블 – 객체 작성 후, 성공적인 실행 여부 확인

SELECT object_name, object_type

FROM user_objects;

§user_source 테이블 – 프로시저의 소스 확인 가능

SELECT * FROM user_source

WHERE name = ‘ADD_DEPT’;

 

§user_objects 테이블

set pages 50

col name for a20

col text for a80

SELECT object_name, object_type

FROM user_objects

WHERE object_name = ‘ADD_DEPT’;

 

§user_source 테이블

 

G. 프로시저 호출

 

BEGIN

  add_dept;

END;

/

 

SELECT department_id, department_name

FROM dept

WHERE department_id = 280;

 

H. 함수 : 구문 

§ARGUMENT: 함수 파라미터에 부여된 이름

§MODE: 파라미터 유형, IN 파라미터만 선언되어야 함

§RETURN datatype: 함수에 의해 반환된 값의 데이터 유형

 

ex)

CREATE OR REPLACE FUNCTION check_sal

RETURN boolean IS

  v_dept_id employees.department_id%TYPE;

  v_empno employees.employee_id%TYPE;

  v_sal employees.salary%TYPE;

  v_avg_sal employees.salary%TYPE;

BEGIN

  v_empno := 205;

  SELECT salary, department_id INTO v_sal, v_dept_id

  FROM employees

  WHERE employee_id = v_empno;

  SELECT avg(salary) INTO v_avg_sal FROM employees

  WHERE department_id = v_dept_id;

  IF v_sal > v_avg_sal THEN

  RETURN TRUE;

  ELSE

  RETURN FALSE;

  END IF;

EXCEPTION

  WHEN NO_DATA_FOUND THEN

  RETURN NULL;

END;

/

 

I. 함수 호출

BEGIN

  IF (check_sal IS NULL) THEN

  DBMS_OUTPUT.PUT_LINE

  (‘The function returned NULL due to exception’);

  ELSIF (check_sal) THEN

  DBMS_OUTPUT.PUT_LINE (‘ Salary > Average ’);

  ELSE

  DBMS_OUTPUT.PUT_LINE (‘ Salary < Average ‘);

  END IF;

END;

/

* DESCRIBE 명령어

§DESCRIBE 명령을 통해서
함수의 인수 및 반환 유형 확인 가능

§프로시저 역시 DESCRIBE 가능

 

J. 파라미터가 포함된 함수 예제

 

DROP FUNCTION check_sal;

CREATE OR REPLACE FUNCTION

  check_sal(p_empno employees.employee_id%TYPE)

RETURN boolean IS

  v_dept_id employees.department_id%TYPE;

   v_sal employees.salary%TYPE;

  v_avg_sal employees.salary%TYPE;

BEGIN

  SELECT salary, department_id INTO v_sal, v_dept_id

  FROM employees

  WHERE employee_id = p_empno;

  SELECT avg(salary) INTO v_avg_sal FROM employees

  WHERE department_id = v_dept_id;

  IF v_sal > v_avg_sal THEN

  RETURN TRUE;

  ELSE

  RETURN FALSE;

  END IF;

EXCEPTION

  WHEN NO_DATA_FOUND THEN

  RETURN NULL;

END;

/

 

- 호출

BEGIN

DBMS_OUTPUT.PUT_LINE(‘ Checking for employee with id 205 ‘);

  IF (check_sal (205) IS NULL) THEN

  DBMS_OUTPUT.PUT_LINE (‘ The function returned NULL due to exception ‘);

  ELSIF (check_sal (205)) THEN

  DBMS_OUTPUT.PUT_LINE (‘ Salary > Average ‘);

  ELSE

  DBMS_OUTPUT.PUT_LINE (‘ Salary < Average ‘);

  END IF;

DBMS_OUTPUT.PUT_LINE(‘ Checking for employee with id 70 ‘);

  IF (check_sal (70) IS NULL) THEN

  DBMS_OUTPUT.PUT_LINE (‘ The function returned NULL due to exception ‘);

  ELSIF (check_sal (70)) THEN

  DBMS_OUTPUT.PUT_LINE (‘ Salary > Average ‘);

  ELSE

  DBMS_OUTPUT.PUT_LINE (‘ Salary < Average ‘);

  END IF;

END;