2024.12.31 PL/SQL 9일차

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

select max(department_id)
into 마지막부서 코드 변수
from hr.dept;
insert into hr.dept(department_id, department_name, manager_id, location_id)
values(마지막 부서코드 + 10, 입력변수, 입력변수, 입력변수);

create or replace procedure hr.add_dept
(p_name in varchar2, p_mgr in number, p_loc in number)
is
    v_max number;
begin
    select max(department_id)
    into v_max
    from hr.dept;
    insert into hr.dept(department_id, department_name, manager_id, location_id)
    values(v_max + 10, p_name, p_mgr, p_loc);
end add_dept;
/

 

■ EXCEPTION을 어디에 처리하는지에 따라 다른 결과 확인하기

  - 포인터를 어디에 떨어졌는지 생각하면서 EXCEPTION 사용하기

 

- 첫 번째 케이스  

begin
    hr.add_dept('경영기획팀',100,1800); -- 얘를 실행하는 순간 270 + 10 = 280번 부서로 들어간다,
    hr.add_dept('데이터아키텍처',99,1800); -- 280 + 10 = 290번 부서, pk값에 99번이 없기 때문에 오류 발생
    hr.add_dept('인재개발',101,1800);
end;
/

출력

▶ 앞에서 실행된 프로시저는 비정상적인 종료이기 때문에 자동 롤백이 돼가지고 출력이 되지 않고 밖으로 나온다.

▶ EXCEPTION 핸들링 필요하다. 

▶  1차적으로 기본적으로 호출하는 쪽에서 EXCEPTION이 있는지 확인을 한다.

▶  호출한 프로시저에서 에러가 발생한 것이고, 오류에 따른 이셉션 여부를 확인하고 없으니 호출환경에서 EXCEPTION 여부를 확인하고 없어서 에러가 발생한 거다.

 

- 두 번째 케이스 (호출환경에서 EXCEPTION 처리)

begin
    hr.add_dept('경영기획팀',100,1800); 
    hr.add_dept('데이터아키텍처',99,1800); 
    hr.add_dept('인재개발',101,1500);
exception
    when others then
        dbms_output.put_line(SQLERRM);
end;
/

출력

▶ 오류는 떴지만 자동처리했기 때문에 이전에 들어간 데이터는 살아있다. 정상 종료되었기 때문에 트랜잭션은 살아있다.

▶ 프로시저에 EXCEPTION 여부를 확인하고 없으니 호출환경에서 EXCEPTION 여부를 확인한다.

▶ 첫 번째(경영기획팀)은 insert되고, 에러가 발생하는 (데이터아키텍처)이후는 들어가지 않았다. 그 이유는 호출하는 곳에서 EXCEPTION 핸들링을 했기 때문에 경영기획팀이 insert 된 것이다.

 

- 3번째 케이스 (PROCEDURE에서 EXCEPTION 처리)

create or replace procedure hr.add_dept
(p_name in varchar2, p_mgr in number, p_loc in number)
is
    v_max number;
begin
    select max(department_id)
    into v_max
    from hr.dept;
    insert into hr.dept(department_id, department_name, manager_id, location_id)
    values(v_max + 10, p_name, p_mgr, p_loc);
exception
    when others then
        dbms_output.put_line('오류부서 : '||p_name);
        dbms_output.put_line(SQLERRM);
end add_dept;
/

begin
    hr.add_dept('경영기획팀',100,1800); 
    hr.add_dept('데이터아키텍처',99,1800); -- 오류발생
    hr.add_dept('인재개발',101,1500);
exception
    when others then
        dbms_output.put_line(SQLERRM);
end;
/

출력

▶ 프로시저 안에 EXCEPTION이 있는지 체크하고 있으니 처리하고, 나를 호출한 3번째로 가서 실행이 되기 때문에 1,3번째는 실행이 된다.

 

# 조회해보기

select * from hr.dept;

출력

▶ 호출되는 곳에서 EXCEPTION 처리를 하는 것이 좋다.

 

■ 함수 (Function)

   - 값을 반환하는 이름이 있는 PL/SQL블록이다.

   - 반복 실행을 위해 DB에 객체로 저장할 수도 있고, 안 할 수도 있다.

   - 표현식의 일부로 호출되거나 다른 서브 프로그램에서 호출할 수 있다.

   - return 값을 반환할 때 값이 없어도 null이라도 반환해야 한다.

   - IN MODE만 가능하다. (OUT MODE, IN/OUT MODE 사용 불가)

   - return 타입은 꼭 써줘야 한다.

 

# 함수 구조

create or replace function function_name(사용자지정)
(parameter in datatype...) -- 필수 항목은 아님
return datatype -- 
is
    변수, 상수, 명시적커서, 사용자가 정의한 예외사함
begin
    return 값; -- return 작성은 여러 개 할 수 있지만 실행은 하나만 가능
exception
end;
/

 

ex) 함수 개발하기

create or replace function hr.get_sal_func
(p_id in number)
return number
is
    v_sal number := 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;
end get_sal_func;
/

★ 밑에 return 타입이랑 헤더부분의 return 타입이 서로 무조건 일치해야 한다. 타입이 다르면 타입 불일치로 에러가 발생한다. 

 

 # 호출환경

exec dbms_output.put_line(hr.get_sal_func(100))

출력

# 표현식의 일부로 호출하는 방식

   - 개발한 함수명을 변수에 할당한다.

declare
    v_sal number;
begin
    v_sal := hr.get_sal_func(101);
    dbms_output.put_line(v_sal);
end;
/

▶ 프로시저에서는 이런 식으로 호출하면 안 된다. 

출력

 

# 표현식을 이용해서 select에서도 가능하다.

select employee_id, hr.get_sal_func(employee_id)
from hr.employees;

출력

 

ex)

create or replace function hr.tax(p_value in number)
return number
is
begin
    return (p_value * 0.08);
end tax;
/

▶ return문에 타입을 쓰지 않고, 계산값을 return할 수 있다.

select employee_id, salary, hr.tax(salary)
from hr.employees
where department_id = 20;

출력

 

 

문제) 연봉을 자동으로 계산해 주는 함수를 개발하시오.

create or replace function hr.get_annual_comp
(p_sal in number, p_pct in number)
return number
as
begin
    return (p_sal * 12) + (p_sal * 12 * nvl(p_pct,0));
end get_annual_comp;
/

select employee_id, (salary * 12) + (salary * 12 * commission_pct) ann_sal_1,
       (salary * 12) + (salary * 12 * nvl(commission_pct, 0)) ann_sal_2,
       hr.get_annual_comp(salary, commission_pct) ann_sal_3 -- 내가 개발한 함수
from hr.employees;

출력

 

문제) 사원번호를 입력값으로 받아서 그 사원의 근무연수를 리턴하는 함수를 생성해 주세요.

create or replace function hr.get_year
(p_id in number)
return number
is
    v_year number;
begin
    select trunc(months_between(sysdate, hire_date) / 12)
    into v_year
    from hr.employees
    where employee_id = p_id;
    return v_year;
exception
    when no_data_found then
        return v_year;
end get_year;
/

exec dbms_output.put_line(hr.get_year(100))

출력

select employee_id, hire_date, get_year(employee_id) 
from hr.employees;

출력

 

문제) 부서코드를 입력값으로 받아서 부서이름을 리턴하는 함수를 생성해 주세요.

create or replace function hr.get_dept
(p_id in number)
return varchar2 -- department_name을 리턴해야 하는데 name은 문자형이기 때문에 varchar2 쓴다.
as
    v_dept_name varchar2(30);
begin
    select department_name
    into v_dept_name
    from hr.departments
    where department_id = p_id;
    return v_dept_name;
exception
    when no_data_found then
            return v_dept_name; -- null이랑 동일
end get_dept;
/

select employee_id, department_id, get_dept(department_id)
from hr.employees;

출력

exec dbms_output.put_line(hr.get_dept(30))

출력

 

# 함수를 개발했을 때 생기는 부작용 테스트

create or replace function hr.query_call_sql(p_id in number)
return number
is
    v_sal number;
begin
    select salary
    into v_sal
    from hr.employees
    where employee_id = p_id;
    return v_sal;
end query_call_sql;
/

select employee_id, hr.query_call_sql(employee_id)
from hr.employees;

출력

 

ex) 현 상태에선 아무런 문제가 되지 않는다. 하지만 업데이트 시에서도 사용하고 싶을 경우

update hr.employees
set salary = hr.query_call_sql(employee_id) * 1.1
where employee_id = 110;

출력

▶ 함수/트리거를 만들 때는 문제는 없지만 DML작업을 할 때 오류가 발생한다. 그 이유는 같은 테이블에 대해서는 동시에 write/read를 못한다. 왜냐하면 타이밍 시점을 못 잡는다.UPDATE를 먼저 해야 할지SELECT를 해야 할지 모른다. 그래서 함수/트리거를 만들 때 주의해야 한다.

 

# 패키지를 써야 하는 이유

create or replace function hr.validate_comm(p_comm in number)
return boolean
is
    v_max_comm number;
begin
    select max(commission_pct)
    into v_max_comm
    from hr.employees;
    
    if p_comm > v_max_comm then
        return false;
    else
        return true;
    end if;
end validate_comm;
/

create or replace procedure hr.reset_comm(p_comm in number)
is
    v_comm number := 0.1;
begin
    if hr.validate_comm(p_comm) then -- 함수를 만든 거기때문에 표현식 일부로 사용 가능하다.
        dbms_output.put_line('old : '||v_comm);
        v_comm := p_comm;
        dbms_output.put_line('New : '||v_comm);
    else
        raise_application_error(-20000, '기존 최고값을 넘을 수 없습니다.');
    end if;
end reset_comm;
/

begin
    hr.reset_comm(0.2);
end;
/

출력

begin
    hr.reset_comm(0.5);
end;
/

출력

※ 서로 두 개의 프로그램은 종속관계에 있기 때문에 둘은 세트로 가지고 있어야 하는데 관리하기 귀찮을 수 있다. 함수 따로 프로시저 따로 관리해야 하는 불편함이 있다. 그래서 이 걸 하나로 묶어서 사용하면 관리하기 편한 패키지를 사용한다.

 

■ PACKAGE

  - 관련성 있는 서브 프로그램(프로시저, 함수), 변수, 데이터타입을 모아놓은 프로그램

  - BEGIN절은 옵션이다.

  - Specification(Public, Global)은 패키지의 인터페이스이다.

     1) 패키지 외부에서 참조할 수 있는 변수, 상수, 커서, 예외사항, 서브프로그램을 선언한다.

  - Body(Private)는 서브 프로그램에 대한 코드를 정의한다.

  - 테스트할 때 디벨로퍼에서 하지 말고, sqlplus에서 창을 여러 개 띄어놓고 테스트해보기!

 

★ Public > Private > 로컬변수

 

# Package Specification 생성

CREATE OR REPLACE PACKAGE package_name
is
	Public type and variable decalarations(변수, 상수, 커서, 예외사항)
    Subprogram specifications
END package_name;
/

▶ 글로벌 변수나, 프로시저를 생성할 수 있는데 spec에서 생성했기 때문에 어디서든 사용이 가능하다.

 

# PACKAGE BODY 생성

CREATE OR REPLACE PACKAGE BODY package_name
is
	Private type and variable decalarations(변수, 상수, 커서, 예외사항)
    Subprogram 코드 정의
BEGIN -- 옵션
END package_name;
/

▶ Private는 Body 안에서만 사용하는 타입을 지정하는 것이다. (외부에서 사용 불가)

 

  1. Specification (Public) - 어디서든 볼 수 있다. , 선언부분

- 어디서든 볼 수 있기 때문에 선언할 때 보이면 안 되는 것들은 절대 선언하면 안 된다.

- 선언한 변수들은 전역변수가 된다.

- spec 부분에서는 꼭 선언만 해야 한다. 함수를 선언하고 싶을 경우엔 형식에 맞게 써주고 return 타입까지 써줘야 한다.

- spec 없는 body는 만들 수 없다.

ex)

create or replace package hr.comm_pkg
is
    g_comm number := 0.1;
    procedure reset_comm(p_comm in number);
end comm_pkg;
/

exec dbms_output.put_line(hr.comm_pkg.g_comm);

출력

▶ 바깥에서 어디든 사용 가능하다. 

 

exec hr.comm_pkg.g_comm := 0.2
exec dbms_output.put_line(hr.comm_pkg.g_comm);

출력

▶ 바깥에서 수정해서 다시 실행해도 그대로 수정된 값으로 출력이 된다.

 

# 하지만 글로벌변수는 다른 섹션에서 하면 결과값이 다르게 나온다. 자기 섹션에서만 수정이 되는 게 글로벌 변수 특징이다.

출력

▶  자기 섹션이 종료되지 않는 한 계속 지속된다.

 

2. Body (Private) - 바깥쪽에선 볼 수 없다. , 실제 로직 프로그램 작성

  - 실제 로직 구현은 Body에서 한다.

create or replace package body hr.comm_pkg
is
    function validate_comm(p_comm in number)
    return boolean
    is
        v_max_comm number; -- Private 보다도 작은 개념의 로컬변수이다. validate_comm 안에서만 사용 가능
    begin --함수 안에 begin절이지 패키지 안에 begin절이 아니기 때문에 사용한다.
        select max(commission_pct)
        into v_max_comm
        from hr.employees;
    
        if p_comm > v_max_comm then
            return false;
        else
            return true;
        end if;
end validate_comm;

    procedure reset_comm(p_comm in number)
    is
    begin
        if hr.validate_comm(p_comm) then
            dbms_output.put_line('old : '||g_comm);
            g_comm := p_comm;
            dbms_output.put_line('New : '||g_comm);
        else
            raise_application_error(-20000, '기존 최고값을 넘을 수 없습니다.');
        end if;
    end reset_comm;
end comm_pkg;
/

▶ body의 패키지명은 spec의 패키지명과 꼭 동일해야 한다.

▶ 프로시저와 함수 옆에 reset_comm, validate_comm  작성 시 hr은 생략해서 적어도 된다.

 

exec hr.comm_pkg.reset_comm(0.2)

출력

▶ 세션이 종료되지 않는 한 지속적으로 바뀐 값을 쓴다. (주의)

 

# 테스트할 때

exec hr.comm_pkg.g_comm := 0.2
exec hr.comm_pkg.reset_comm(0.2)

▶ 테스트 할 때 세션 2개 띄어놓고 값 수정해 가면서 old, new 값 보면서 테스트해볼 것!!

 

ex) 글로벌 변수를 사용하지 않을 경우

   1. spec 부분

create or replace package hr.comm_pkg
is
    procedure reset_comm(p_comm in number);
end comm_pkg;
/

  

   2. body 부분

create or replace package body hr.comm_pkg
is
    function validate_comm(p_comm in number)
    return boolean
    is
        v_max_comm number;
    begin
        select max(commission_pct)
        into v_max_comm
        from hr.employees;
    
        if p_comm > v_max_comm then
            return false;
        else
            return true;
        end if;
end validate_comm;

    procedure reset_comm(p_comm in number)
    is
        v_comm number := 0.1;
    begin
        if hr.validate_comm(p_comm) then
            dbms_output.put_line('old : '||v_comm);
            v_comm := p_comm;
            dbms_output.put_line('New : '||v_comm);
        else
            raise_application_error(-20000, '기존 최고값을 넘을 수 없습니다.');
        end if;
    end reset_comm;
end comm_pkg;
/