9. PL/SQL 내장 프로시저 및 함수 소개
A. 프로시저 및 함수
IS, AS 키워드 다음에 나옴
B. 익명 블록 VS 서브 프로그램
– 차이점
C. 프로시저 : 구문
없음 -> %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. 객체 및 객체 소스 확인
SELECT object_name, object_type
FROM user_objects;
SELECT * FROM user_source
WHERE name = ‘ADD_DEPT’;
set pages 50
col name for a20
col text for a80
SELECT object_name, object_type
FROM user_objects
WHERE object_name = ‘ADD_DEPT’;
G. 프로시저 호출
BEGIN
add_dept;
END;
/
SELECT department_id, department_name
FROM dept
WHERE department_id = 280;
H. 함수 : 구문
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 명령어
함수의 인수 및 반환 유형 확인 가능
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;
