<모듈식 개발의 예>

CREATE OR REPLACE PROCEDURE raise_salary(
    p_id        IN number,
    p_percent   IN number)
IS
BEGIN
    UPDATE hr.employees
    SET salary = salary * (1+p_percent/100)
    WHERE employee_id = p_id;
END;
/

입력된 사원id에 맞는 사원의 급여를 10% 인상 시키는 프로시저 생성

CREATE OR REPLACE PROCEDURE process_employees
IS
    cursor emp_cursor IS
        SELECT employee_id
        FROM hr.employees;
BEGIN
    FOR emp_rec IN emp_cursor LOOP
        raise_salary(emp_rec.employee_id,10);
    END LOOP;
END process_employees;
/

해당 프로시저를 전체 직원에 적용시키기 위해 프로시저 생성 후 이전 급여인상 프로시저를 모듈식 적용

---

신규 부서정보를 입력하는 프로시저를 생성

<준비단계>

CREATE TABLE hr.emp 
AS
SELECT * FROM hr.employees;

CREATE TABLE hr.dept
AS
SELECT * FROM hr.departments;

ALTER TABLE hr.emp ADD CONSTRAINT empid_pk PRIMARY KEY(employee_id);
ALTER TABLE hr.dept ADD CONSTRAINT deptid_pk PRIMARY KEY(department_id);
ALTER TABLE hr.dept ADD CONSTRAINT deptmgr_fk FOREIGN KEY(manager_id) REFERENCES hr.emp(employee_id);

SELECT * FROM user_constraints WHERE table_name IN ('EMP','DEPT');
SELECT * FROM user_cons_columns WHERE table_name in ('EMP','DEPT');

<프로시저>

CREATE OR REPLACE PROCEDURE add_dept(
    p_name      IN      varchar2,
    p_mgr       IN      number,
    p_loc       IN      number)
IS
    v_max number;
BEGIN

SELECT MAX(department_id)+10 --기존 마지막 부서번호에서 10씩 더한 부서번호 할당
INTO v_max
FROM hr.dept;

INSERT INTO hr.dept(department_id, department_name, manager_id, location_id)
VALUES(v_max, p_name, p_mgr, p_loc);

END add_dept;
/

<실행>

BEGIN
    add_dept('경영지원',100,1800);
    add_dept('데이터분석',200,1700);
    add_dept('인프라',110,1800);
END;
/

<참조 무결성 제약조건 위반>

BEGIN
    add_dept('경영지원',100,1800);
    add_dept('데이터분석',90,1700); --참조무결성 위반
    add_dept('인프라',110,1800);
END;
/

EXCEPTION 핸들링을 하지 않아 프로그램 비정상적인 종료가 된다. 따라서 transaction은 자동 롤백 된다.

<실행 프로그램에서 EXCEPTION 핸들링 예>

BEGIN
    add_dept('경영지원',100,1800);
    add_dept('데이터분석',90,1700); --참조무결성 위반
    add_dept('인프라',110,1800);
EXCEPTION
    WHEN others THEN
        dbms_output.put_line(sqlerrm);
END;
/
SELECT * FROM hr.dept;

참조 무결성을 위반하였지만 EXCEPTION핸들링을 통해 EXCEPTION절로 트랩되어 프로그램 종료 됨으로 참조 무결성 위반 전 transaction만 반영 되어 그 뒤 transaction은 반영되지 않는다.

<생성 프로시저 안에서 EXCEPTION 핸들링 예>

CREATE OR REPLACE PROCEDURE add_dept(
    p_name      IN      varchar2,
    p_mgr       IN      number,
    p_loc       IN      number)
IS
    v_max number;
BEGIN

SELECT MAX(department_id)+10
INTO v_max
FROM hr.dept;

INSERT INTO hr.dept(department_id, department_name, manager_id, location_id)
VALUES(v_max, p_name, p_mgr, p_loc);

EXCEPTION
    WHEN others THEN
        dbms_output.put_line('error : '||p_name);
        dbms_output.put_line(sqlerrm);
        dbms_output.put_line(sqlcode);
END add_dept;
/
-------------------------------------------------------------------------------
BEGIN
    add_dept('경영지원',100,1800);
    add_dept('데이터분석',90,1700);
    add_dept('인프라',110,1800);
END;
/

프로시저 안에서 EXCEPTION 핸들링을 할 경우 호출환경에서 프로시저 실행시 서브블락 EXCEPTION을 한 효과를 낼 수 있어 에러처리 된 프로시저 뒤에 transaction도 반영 가능하다.

---

Function(함수)

• 파라미터를 사용하고 호출되며 값을 반환할 수 있는 이름이 있는 PL/SQL 블록이다.
• 함수는 header에 RETURN 절이 있어야 하며, BEGIN절에는 적어도 한개의 RETURN문이 있어야 한다.
• 형식매개변수의 모드는 IN 모드만 사용한다.
• 함수는 표현식으로 호출

CREATE OR REPLACE FUNCTION get_sal_func(
    p_id        IN  number)
RETURN number --리턴문의 데이터 타입과 동일해야 한다.
IS
    v_sal number := 0; -- 초기값을 0으로 설정
BEGIN
    SELECT salary
    INTO  v_sal  
    FROM    hr.employees
    WHERE employee_id = p_id;
    
    RETURN v_sal; -- 리턴 문
EXCEPTION
    WHEN no_data_found  THEN
        RETURN v_sal; --null 값이라도 리턴
END get_sal_func;
/

CREATE OR REPLACE FUNCTION get_sal_func(
    p_id        IN  number)
RETURN varchar2 --리턴문이 문자 타입이여서 varchar2로 선언
IS
    v_sal number := 0; -- 초기값을 0으로 설정
BEGIN
    SELECT salary
    INTO  v_sal  
    FROM    hr.employees
    WHERE employee_id = p_id;
    
    RETURN to_char(v_sal,'l999,999'); -- 리턴 문
EXCEPTION
    WHEN no_data_found  THEN
        RETURN v_sal; --null 값이라도 리턴
END get_sal_func;
/

[문제21]

자신의 부서 평균 급여보다 더 많은 급여를 받는 사원은 'YES' 아니면 'NO'를 리턴하는 함수를 생성해주세요.

<풀이>

CREATE OR REPLACE FUNCTION avg_dept(
    dept_id     IN  number,
    sal     IN  number)
RETURN varchar2
IS
    v_avg_sal number;
BEGIN
    SELECT AVG(salary) -- no data found 오류임에도 그룹함수를 사용하면 exception이 발생되지 않는다.
    INTO v_avg_sal
    FROM hr.employees
    WHERE department_id = dept_id;
    
    IF v_avg_sal IS NULL THEN
        RETURN NULL;
    ELSIF sal > v_avg_sal THEN
        RETURN 'YES';
    ELSE
        RETURN 'NO';
    END IF;
END;
/

<결과>

SELECT employee_id, salary, department_id, avg_dept(department_id, salary)
FROM hr.employees;

• no data found 오류임에도 그룹함수를 사용하면 exception이 발생되지 않는다. 함수는 null 값이라도 리턴하기 때문에 v_avg_sal은 null을 fetch 받는 개념으로 작동

mutating 오류

• SQL 표현식에서 함수를 호출할 때의 부작용 발생 할 수있다.

• 함수를 통해서 SELECT할때 함수안에 동일한 테이블에 DML 작업을 수행하면 부작용이 발생한다.

• 함수를 통해서 DML 작업을 수행할때 함수 안에 동일한 테이블에 대해서 SELECT를 수행하면 부작용이 발생한다.

• 함수를 통해서 DML 작업을 수행할때 함수 안에 동일한 테이블에 대해서 DML을 수행하면 부작용이 발생한다.

• 함수를 통해서 SELECT할때 함수안에 동일한 테이블에 SELECT 작업을 수행할 수 있다.

<부작용 예시>

CREATE OR REPLACE FUNCTION query_call(
    p_id    IN   NUMBER)
RETURN NUMBER
IS
    v_sal number;
    v_years number;
BEGIN
    SELECT salary, trunc(months_between(sysdate,hire_date)/12)
    INTO v_sal, v_years
    FROM hr.employees
    WHERE employee_id = p_id;
    
    IF v_years >= 20 THEN
        RETURN v_sal * 1.2;
    ELSIF v_years < 20 AND v_years >= 17 THEN
        RETURN v_sal * 1.1;
    ELSE
        RETURN v_sal;
    END IF;
END;
/
/*mutating 오류, 동일한 hr.employees을 참조함. 오라클이 검색 타이밍 시점을 못잡음 */
UPDATE hr.employees
SET salary = query_call(100) 
WHERE employee_id = 100;

Local Subprogram

• CREATE PROCEDURE 시스템 권한이 필요 없다.
• 특정한 프로그램 안에서만 사용하는 서브프로그램
• 객체가 생성되지 않기 때문에 객체 관리하기에는 수월하다.
• 익명블록 구조에서는 DECLARE절에 서브프로그램 에서는 IS 절에 만든다
  • 단, 로컬 서브프로그램을 선언 하기 전에 다른 선언 할 것들을 전부 선언 후 맨 마지막에 로컬 서브 프로그램을 선언 해야한다.

DECLARE
    TYPE emp_id_type IS TABLE OF number;
    v_id emp_id_type := emp_id_type(100,101,103);
    v_emp hr.employees%rowtype;

    FUNCTION tax( -- 함수 선언 , CREATE OR REPLACE만 안하고 나머지는 동일
        p_sal IN number)
    RETURN number
    IS 
    BEGIN
        RETURN p_sal * 0.08;
    END tax;
    
    PROCEDURE message -- 프로시저 선언 , 형식매개변수 생략 가능
    IS
    BEGIN
        dbms_output.put_line('목표를 위해서 열심히 노력하자!!');
    END message;
BEGIN
    FOR i IN v_id.first..v_id.last LOOP
        SELECT *
        INTO v_emp
        FROM hr.employees
        WHERE employee_id = v_id(i);
        
        dbms_output.put_line('사번 : '||v_emp.employee_id || ' Tax : ' || tax(v_emp.salary));
    END LOOP;
    message; -- 프로시저 호출
END;
/

---

[느낀점]

요즘 강사님께서 우리들의 학습량에 위기의식을 느끼시는지 수업 스타일이 오전에는 전날 배운거 복습 느낌 오후에는 새로운 진도 나가는 걸로 바뀐 것 같다. PL/SQL은 모듈식 개발로 블록 조립하듯이 개발 할 수 있는데, 프로시저를 하나 만든 후 다른 프로시저 안에서 작동되게 구현도 할수 있었다. 그리고 프로시저 안에서 EXCEPTION 핸들링을 하면 해당 프로시저에서 오류가 발생 하여도 EXCEPTION 절로 빠져 END 되기 때문에 다음 프로시저 실행에는 영향을 주지는 않았다. 오후에는 프로시저 다음 서브 프로그램인 함수(FUNCTION)에 대해 공부하였다. 함수 프로그램 같은 경우 프로시저와는 다르게 IN 모드로만 형식 매개변수를 선언 할 수 있고, RETURN 값을 통해 OUT 처리를 해줄 수 있다. 문법에도 차이가 좀 있었는데, RETURN 데이터타입을 선언 함으로써 RETRUN문에서 나오는 값을 정의 해줘야 했다. 프로시저에서도 비슷한 궁금증 이었던 형식 매개변수 값들을 항상 스칼라타입으로만 선언이 가능 했는데, 함수에서 또한 패키지를 배워야만 레코드 타입으로 선언이 가능하다고 하셨다. 마지막에는 로컬 서브프로그램을 공부하였는데, 로컬 서브프로그램을 쓰는 이유라 함은 만약 나한테 CREATE PROCEDURE 시스템 권한이 없다는 가정하에 프로그램 안에서 프로시저나 함수를 구현하고 싶을때 사용 한다. 또한 프로시저나 함수 등 객체를 만들게 되면 후에 객체 관리함에 있어서도 일이 가중 될 수 있기 때문에 중요한게 아니라면 로컬 서브프로그램으로 사용해도 좋을 것 같다.