2025.01.02 PL/SQL 10일차

create or replace package body hr.comm_pkg
is
    procedure reset_comm(p_comm in number)
    is
    begin
        if 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;
    
    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;
end comm_pkg;
/

▶ 주의할 점 : 패키지 바디에 정의되는 생성자들은 내 앞에 있는 것만 참조(전방참조)가 된다.

# 해결방법 (해더 부분 복사)

create or replace package body hr.comm_pkg
is
    function validate_comm(p_comm in number)
    return boolean;
    
    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;
    
    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;
end comm_pkg;
/

▶ 해더 부분을 복사해서 젤 앞부분에 붙여놓기 하면 사용할 수 있다.

※ 글로벌 변수는 패키지 내에 Spec부분에서 선언해서 사용해야 한다.

■ Package Overloading

- 동일한 이름의 생성자(프로시저, 함수)를 만들 수 있다.

- 패키지 내에서만 오버로딩이 가능하다.

- 형식 파라미터의 개수 or 모드(IN, OUT, IN/OUT) or 데이터 유형이 다를 경우 동일한 이름의 서브 프로그램을 생성할 수 있다.

- 하나 만들어 놓으면 사용자 입장에서 사용하기 편리하다.

ex)

create or replace package hr.pack_over
is
    type date_tab_type is table of date index by pls_integer;
    type num_tab_type is table of number index by pls_integer;
    
    procedure init(tab out date_tab_type, n in number);
    procedure init(tab out num_tab_type, n in number);
end pack_over;
/

create or replace package body hr.pack_over
is
    procedure init(tab out date_tab_type, n in number)
    is
    begin
        for i in 1..n loop
            tab(i) := sysdate;
        end loop;
    end;
    procedure init(tab out num_tab_type, n in number)
    is
    begin
        for i in 1..n loop
            tab(i) := i;
        end loop;
    end;
end pack_over;
/

# 호출환경

declare
    date_tab hr.pack_over.date_tab_type;
    num_tab hr.pack_over.num_tab_type;
begin
    hr.pack_over.init(date_tab, 5); -- 날짜형식
    hr.pack_over.init(num_tab, 5); -- 숫자형식
    
    for i in 1..5 loop
        dbms_output.put_line(date_tab(i));
        dbms_output.put_line(num_tab(i));
    end loop;
end;
/

▶ 같은 프로시저명이어도 안에 인수값 타입이 다르기 때문에 생성할 수 있다.

ex)

create or replace package hr.pack_over
is
    type date_tab_type is table of date index by pls_integer;
    type num_tab_type is table of date index by pls_integer;
    
    procedure init(tab out date_tab_type, n in number);
    procedure init(tab out num_tab_type, n in number);
end pack_over;
/

create or replace package body hr.pack_over
is
    procedure init(tab out date_tab_type, n in number)
    is
    begin
        for i in 1..n loop
            tab(i) := sysdate;
        end loop;
    end;
    procedure init(tab out num_tab_type, n in number)
    is
    begin
        for i in 1..n loop
            tab(i) := sysdate;
        end loop;
    end;
end pack_over;
/

▶ 데이터 타입이 같고 모드도 같은데 타입 이름이 다를 경우 쓸 순 있지만 안에 서브 프로그램에 프로시저 하나만 만들면 되는데 굳이 2개나 쓸 경우 의미가 없다.

▶ 이름만 다르게 쓸 경우 BODY 안에 로직에서 변경을 해서 쓰도록 하는 게 효율적이다.

문제) 사원을 조회하는 프로그램을 작성해 주세요.

- select문을 먼저 짜보고 프로시저로 개발할지 함수로 개발할지 잘 모르겠으면 호출방식을 생각하면 된다.

- 호출방식이 exec면 프로시저로 개발하면 되고, SQL 문장으로 개발하고 싶으면 함수로 개발하면 된다.

- 문제가 어려우면 일단 가장 중요한 select문을 먼저 짜고 실행해 보고, 익명 블록으로 짜보는 순서로 가면 된다.

- 익명블록에서 프로시저로 전환하는 건 아주 쉽기 때문이고, 익명블록 구조에서 변수 처리할 때 바인드 변수를 쓸 텐데

- 프로시저에서는 create 변수를 선언하면 되기 때문에 바인드변수는 지워주면 된다.

ex)

select *
from hr.employees
where employee_id = 100;

select *
from hr.employees
where last_name = 'king';

exec hr.emp_find.find(100)
exec hr.emp_find.find('king')

답)

1. Spec 부분

create or replace package hr.emp_find
is
    procedure find(p_id in number);
    procedure find(p_name in varchar2);
end;
/

2. Body 부분

- 만약 for loop 구조로 명시적 커서로 풀 시 for문 밑에 if문을 쓰게 되면 에러가 발생한다. 그 이유는 for문 안에서 open, bind, execute, close가 내부적으로 발생하기 때문에 밑에 if문에서 커서이름을 작성하더라도 인식을 못한다. 그렇다고 if문을 for문 안에 넣으면 있는 사원에 대해선 출력이 되지만 없는 last_name이 들어오면 출력이 나오지 않는다.

- for문 작성 시 주의할 점은 fetch 할 게 없으면 바로 end loop로 빠져나간다. (다르게 푸는 걸 추천)

1) 첫 번째 방법 (명시적커서에서 에러처리 시 if문 활용)

create or replace package body hr.emp_find
as
procedure find(p_id in number) -- 프로시저에서 패키지로 수정 시 hr 꼭 지울 것
as
    v_rec hr.employees%rowtype;
begin
    select *
    into v_rec
    from hr.employees
    where employee_id = p_id;
    dbms_output.put_line(v_rec.employee_id||' '||v_rec.last_name);
exception
    when no_data_found then
        dbms_output.put_line(p_id||'번 사원은 존재하지 않습니다.');
end;
procedure find(p_name in varchar2) -- 프로시저에서 패키지로 수정 시 hr 꼭 지울 것
as
    CURSOR emp_cur is
        select *
        from hr.employees
        where last_name = initcap(p_name);
        
        v_rec emp_cur%rowtype;
        
        err exception;
begin
    open emp_cur; -- 메모리 할당
     fetch emp_cur into v_rec; -- 패치를 진행하는데
     if emp_cur%notfound then -- 존재하지 않은 last_name이면
        dbms_output.put_line(p_name||'이라는 성은 존재하지 않습니다.');
     else -- 존재하는 last_name 이면
        dbms_output.put_line(v_rec.employee_id||' '||v_rec.last_name);
        loop -- ex) king이라는 이름이 2명 이상일 수 있으므로 loop를 통해 한 번 더 돌려본다.
            fetch emp_cur into v_rec; -- 패치를 진행하는데
            exit when emp_cur%notfound; --패치한 게 없으면 탈출
            dbms_output.put_line(v_rec.employee_id||' '||v_rec.last_name); -- 패치한 게 있으면 출력
        end loop;
    end if;
        close emp_cur;
exception
    when others then
        dbms_output.put_line(SQLERRM);
end find;
end emp_find;
/

2) 두 번째 방법 (명시적 커서에서 에러처리 시 %rowcount 이용)

create or replace package body hr.emp_find
as
procedure find(p_id in number) -- 프로시저에서 패키지로 수정 시 hr 꼭 지울 것
as
    v_rec hr.employees%rowtype;
begin
    select *
    into v_rec
    from hr.employees
    where employee_id = p_id;
    dbms_output.put_line(v_rec.employee_id||' '||v_rec.last_name);
exception
    when no_data_found then
        dbms_output.put_line(p_id||'번 사원은 존재하지 않습니다.');
end;
procedure find(p_name in varchar2) -- 내부에 데이터 유형이 다르기 때문에 find로 동일하게 오버로딩해도 된다.
as
    CURSOR emp_cur is
        select *
        from hr.employees
        where last_name = initcap(p_name);
        
        v_rec emp_cur%rowtype;
        
        err exception;
begin
    open emp_cur; -- 메모리 할당
     loop
        fetch emp_cur into v_rec;
        exit when emp_cur%notfound;
        dbms_output.put_line(v_rec.employee_id||' '||v_rec.last_name);
        end loop;
        if emp_cur%rowcount = 0 then
            dbms_output.put_line(p_name||'이라는 성은 존재하지 않습니다.');
        end if;
        close emp_cur;
exception
    when others then
        dbms_output.put_line(SQLERRM);
end find;
end emp_find;
/

3. 호출환경

exec hr.emp_find.find('king')
exec hr.emp_find.find('kin')
exec hr.emp_find.find(101)
exec hr.emp_find.find(400)

ex) 만약 데이터에 날짜를 입력하면 1년 치 데이터를 보고 싶을 경우

create or replace package hr.emp_find
is
    procedure find(p_id in number);
    procedure find(p_name in varchar2);
    procedure find(p_date in date);
end;
/

procedure find(p_date in date)
as
    CURSOR emp_cur is
        select *
        from hr.employees
        where hire_date >= p_date
        and hire_date < add_months(p_date, 12);
    
    v_cnt number := 0;
begin
    for v_rec in emp_cur loop
        dbms_output.put_line(v_rec.employee_id||' '||v_rec.last_name);
        v_cnt := emp_cur%rowcount;
    end loop;
    if v_cnt = 0 then
        dbms_output.put_line('데이터가 존재하지 않습니다.');
    end if;
exception
    when others then
        dbms_output.put_line(SQLERRM);
end find;

1년치 데이터를 다 뽑아야 되기 때문에 데이터 개수가 2개 이상이므로 명시적 커서를 이용해야 한다.
커서 선언 후 날짜를 입력값으로 받고, hire_date가 입력값보다 크거나 같고, hire_date가 입력값의 12달 더한(1년 치) 값보다 작은 범위를 지정한다.
for loop를 통해 open, fetch, close 작업을 수행하는데 커서 이름(emp_cur)을 기반으로 v_rec에 담고 출력을 한다.
v_cnt는 초기값으로 0을 설정하고, 데이터가 있으면 %rowcount를 통해 FETCH가 되면 1이 증가가 될 것이다.
만약 if v_cnt = 0 then, FETCH가 0이라는 것은 데이터가 없는 걸 뜻하기 때문에 그에 맞는 출력을 한다.

exec hr.emp_find.find(to_date('2002-01-01','yyyy-mm-dd'))

▶ 날짜는 민감하게 반응해야 한다. 그래서 꼭 to_date를 써줄 것!!

'oracle PLSQL' 카테고리의 다른 글

2025.01.06 PL/SQL 12일차 (1)	2025.01.06
2025.01.03 PL/SQL 11일차 (2)	2025.01.03
2024.12.31 PL/SQL 9일차 (0)	2024.12.31
2024.12.30 PL/SQL 8일차 (0)	2024.12.30
2024.12.27 7일차 (2)	2024.12.27

영산 개발 공부

2025.01.02 PL/SQL 10일차

'oracle PLSQL' 카테고리의 다른 글

티스토리툴바

2025.01.02 PL/SQL 10일차

'oracle PLSQL' 카테고리의 다른 글

'oracle PLSQL' Related Articles

티스토리툴바