2024.12.27 7일차

복습)

declare
    type id_type is table of number;
    v_id id_type := id_type(); -- 배열의 뼈대만 있는 것
begin
    v_id.extend(10);
    for i in 1..10 loop
        v_id(i) := i * 10;
    dbms_output.put_line(v_id(i));
    end loop;
end;
/

 

출력

declare
    type id_type is table of number;
    v_id id_type;
    --v_id id_type := id_type(); -- 배열의 뼈대만 있는 것
begin
    for i in 1..10 loop
        v_id.extend;
        v_id(i) := i * 10;
    end loop;
    
    for i in 1..10 loop
        dbms_output.put_line(v_id(i));
    end loop;
end;
/

 

출력

▶ 초기화를 시키지 않았기 때문에 에러가 발생한다.

 

 # 해결방법

declare
    type id_type is table of number;
    --v_id id_type;
    v_id id_type := id_type(); -- 초기화
begin
    for i in 1..10 loop
        v_id.extend;
        v_id(i) := i * 10;
    end loop;
    
    for i in 1..10 loop
        dbms_output.put_line(v_id(i));
    end loop;
end;
/

출력

▶ id_type(null); 도 가능하다.

▶ 꼭 확장(extend)하고 데이터를 넣어야 한다.

 

declare
    type id_type is varray(10) of number;
    --v_id id_type;
    v_id id_type := id_type(); -- 초기화
begin
    for i in 1..10 loop
        v_id.extend;
        v_id(i) := i * 10;
    end loop;
    
    for i in 1..10 loop
        dbms_output.put_line(v_id(i));
    end loop;
end;
/

출력

▶ varray를 사용할 땐 초기화할 때 null을 넣으면 에러 발생한다.

---

ex)

declare
    cursor parm_cur_80 is
        select employee_id id, last_name name, job_id job
        from hr.employees
        where department_id = 80 -- 상수로 고정되어 있음
        and job_id = 'SA_MAN';   -- 상수로 고정되어 있음
        
    cursor parm_cur_50 is
       select employee_id id, last_name name, job_id job
        from hr.employees
        where department_id = 50 -- 상수로 고정되어 있음
        and job_id = 'ST_MAN'; -- 상수로 고정되어 있음
        
    v_rec1 parm_cur_80%rowtype; --커서를 기반으로 하는 레코드 변수 선언, fetch에 사용하기 위해서 선언한 것
begin
    open parm_cur_80; -- open을 선언해야 메모리에 할당이 된다.
    loop
        fetch parm_cur_80 into v_rec1; -- fetch할 땐 꼭 loop로 수행하기
        exit when parm_cur_80%notfound;
        dbms_output.put_line(v_rec1.id||' '||v_rec1.name||' '||v_rec1.job);
    end loop;
    close parm_cur_80; -- 메모리 해지
    
    for v_rec2 in parm_cur_50 loop
        dbms_output.put_line(v_rec2.id||' '||v_rec2.name||' '||v_rec2.job);
    end loop;
end;
/

출력

▶ 마지막 for문은 v_rec2라는 커서 선언을 암시적으로 해주고, for문 안에서 내부적으로 open, fetch, close 작업을 실행한다.

▶ 데이터를 계속 fetch 해주고, 데이터가 더 이상 없으면 end loop를 타고 나온다.

▶ 하지만 성능상에 문제가 있다. select절에는 실행계획이 꼭 필요하다. 둘 다 같은 실행계획이지만 쉐어링을 못한다. 그 이유는 상수로 고정(department_id = 50 and job_id = 'ST_MAN')되어 있기 때문이다.

 

★ DB에서 데이터를 찾고 싶을 땐 무조건 실행계획이 있어야 한다.

 

# 위에 코드를 개선하는 방법

 ■ PARAMETER를 포함한 CURSOR : 이유는 실행계획을 공유하기 위해서

    - 파라미터에서 변수 선언 시 사이즈를 쓰면 에러가 발생한다. (타입만 써야 함)

    - 값의 분포도가 다를 경우엔 쉐어링을 하면 안 된다. 그럴 경우엔 Hard Parsing 하는 게 더 낫다.(select문을 또 만들기)

declare
    cursor parm_cur(p_id number, p_job varchar2) is
        select employee_id id, last_name name, job_id job
        from hr.employees
        where department_id = p_id
        and job_id = p_job;   
        
    v_rec1 parm_cur%rowtype; 
begin
    open parm_cur(80,'SA_MAN'); -- 위치에 맞게 써줘야한다. 
    loop
        fetch parm_cur into v_rec1; 
        exit when parm_cur%notfound;
        dbms_output.put_line(v_rec1.id||' '||v_rec1.name||' '||v_rec1.job);
    end loop;
    close parm_cur;
    
    for v_rec2 in parm_cur(50,'ST_MAN') loop -- for문에 적용할 경우
        dbms_output.put_line(v_rec2.id||' '||v_rec2.name||' '||v_rec2.job);
    end loop;
end;
/

출력

▶  (50, 'ST_MAN') : 실제 매개변수라고 한다.

▶  (p_id number, p_job varchar2) : 형식 매개변수라고 한다.

 

ex)

select *
from hr.employees
where employee_id = 300;

출력

▶  뼈대만 있다.

 

declare
    v_rec hr.employees%rowtype;
begin
    select *
    into v_rec
    from hr.employees
    where employee_id = 300;
    dbms_output.put_line(v_rec.employee_id);
    dbms_output.put_line(v_rec.first_name);
end;
/

출력

▶ 무조건 한 건을 fetch 해야 하는데 0건이라 오류 발생

 

select *
from hr.employees
where employee_id = 300;

declare
    v_rec hr.employees%rowtype;
begin
    select *
    into v_rec
    from hr.employees
    where department_id = 20;
    dbms_output.put_line(v_rec.employee_id);
    dbms_output.put_line(v_rec.first_name);
end;
/

출력

▶  1건만 fetch 해야 하는데 2건 이상 나와서 에러가 발생한다.

▶ 이런 에러를 비정상적인 종료라고 한다. 그래서 자동 롤백이 된다.

 

■ EXCEPTION (예외) - 오류를 유발하고 싶을 경우

   - 프로그램 실행 중에 발생한 PL/SQL 오류이다.

   - 오라클에 의해 암시적으로 발생한다.

   - 프로그램에 의해 명시적으로 발생시킬 수 있다.

   - when절에 오류번호나 메시지는 안 되고, 무조건 이름으로 해야한다. (오라클 레퍼런스 가면 에러코드 옆에 그거에 맞는 이름을 지정해 놓았다.)

 

  # 예외처리

     - EXCEPTION 키워드로 시작한다.

     - 여러 예외 처리기를 사용할 수 있다.

     - 블록을 종료하기 전 하나의 처리기만 실행한다. (처리기 중에 해당 오류에 대한 처리기만 실행된다.)

     - when others then은 무조건 마지막에 기술해야 한다. (앞에 먼저 쓰면 컴파일 에러 발생)

        - 처리기에 걸리는 게 없으면 마지막에 수행한다.

 

ex) EXCEPTION 구조

declare
    v_id number := 300; 
    v_rec hr.employees%rowtype;
begin
    select *
    into v_rec
    from hr.employees
    where department_id = v_id; -- ORA-01403(에러코드: +100) : no_data found 발생
    dbms_output.put_line(v_rec.employee_id);
    dbms_output.put_line(v_rec.first_name);
exception
    when no_data_found then -- 처리기라 한다.
        dbms_output.put_line(v_id||'사원은 존재하지 않습니다.');
end;
/

출력

▶ 내부적으로 오류는 맞지만 EXCEPTION 핸들링을 해서 처리기를 통해 출력이 된 것이다.

▶ 에러가 발생 지점에서 끝나는 게 아니라 EXCEPTION으로 넘어가서 맞는 처리기가 있으면 그거에 맞는 출력을 한다.

 

# 중요

declare
    v_rec hr.employees%rowtype;
begin
    DML; -- DML 작업했다 가정, 트랜잭션이 진행 중인 상태
    select *
    into v_rec
    from hr.employees
    where department_id = 300;
    dbms_output.put_line(v_rec.employee_id);
    dbms_output.put_line(v_rec.first_name);
end;
/

★ 프로그램이 비정상적인 종료가 발생했다. 트랜잭션은 자동 rollback 발생한다.

 

declare
	v_id number := 300;
    v_rec hr.employees%rowtype;
begin
    DML; -- 트랜잭션이 진행 중인 상태
    select *
    into v_rec
    from hr.employees
    where department_id = v_id;
    dbms_output.put_line(v_rec.employee_id);
    dbms_output.put_line(v_rec.first_name);
exception
    when no_data_found then -- 처리기라 한다.
        dbms_output.put_line(v_id||'사원은 존재하지 않습니다.');
end;
/

★ 프로그램 실행 중에 오류가 발생했더라도 예외사항 처리를 했기 때문에 프로그램은 정상적인 종료, 트랜잭션은 진행 중인 상태로 살아있다. 이 경우에는 트랜잭션이 살아있기 때문에 트랜잭션 제어를 해야 한다.(commit, rollback)

 

ex) EXCEPTION 활용

declare
    v_id number := 300;
    v_rec hr.employees%rowtype;
begin
    insert into hr.test(id, name) values(1,'홍길동');  -- 트랜잭션 진행 중
    select *
    into v_rec
    from hr.employees
    where department_id = v_id;
    dbms_output.put_line(v_rec.employee_id);
    dbms_output.put_line(v_rec.first_name);
exception
    when no_data_found then 
        dbms_output.put_line(v_id||'사원은 존재하지 않습니다.');
end;
/

select * from hr.test;

출력

▶ 정상적인 종료를 하고, 조회하면 데이터가 들어가 있다. 하지만 EXCEPTION을 하지 않았으면 데이터가 들어가지 않고 에러가 발생한다.

 

ex) too_many_rows 이용

declare
    v_rec hr.employees%rowtype;
begin
    select *
    into v_rec
    from hr.employees
    where department_id = 20;
    dbms_output.put_line(v_rec.employee_id);
    dbms_output.put_line(v_rec.first_name);
exception
    when too_many_rows then 
        dbms_output.put_line('결과집합이 여러 건이 나왔습니다. 명시적커서를 사용해야 합니다.');
end;
/

출력

 

ex) 어떤 오류가 날 지 모를 경우

declare
    v_rec hr.employees%rowtype;
begin
    select *
    into v_rec
    from hr.employees
    where department_id = 20;
    dbms_output.put_line(v_rec.employee_id);
    dbms_output.put_line(v_rec.first_name);
exception
    when others then 
        dbms_output.put_line('꿈을 꾸자!!');
        dbms_output.put_line(SQLCODE);
        dbms_output.put_line(SQLERRM); 
end;
/

출력

▶ SQLCODE : 오류 번호

                ex) 0 : 예외가 발생하지 않음

                      1 : 유저 정의 예외사항

                  +100 : NO_DATA_FOUND 예외사항

                   음수 : 오라클 서버에서 발생한 오류 번호

▶ SQLERRM : 오류번호, 오류메시지

 

ex)

begin
    delete from hr.departments where department_id = 20; -- 에러 발생 시 미리 정의된 EXCEPTION이 없다.
EXCEPTION
    when others then
        dbms_output.put_line(SQLCODE);
        dbms_output.put_line(SQLERRM);
end;
/

출력

 

# 만약 위에 코드를 많이 쓸 경우 (exception을 만들어서 처리할 경우)

declare
    pk_error EXCEPTION; --EXCEPTION 선언
    PRAGMA EXCEPTION_INIT(pk_error, -2292);
begin
    delete from hr.departments where department_id = 20; -- 에러 발생 시 미리 정의된 EXCEPTION이 없다.
EXCEPTION
    when pk_error then
        dbms_output.put_line('PK값을 참조하는 자식 행들이 있기때문에 삭제가 안 됩니다.');
    when others then
        dbms_output.put_line(SQLCODE);
        dbms_output.put_line(SQLERRM);
end;
/

출력

▶ 오류번호는 있지만 이름이 없는 경우가 더 많다. 그래서 오류번호를 가지고 EXCEPTION 선언해서 만들어주고, 처리기를 따로 만들어 예외를 만들어준다.

 

ex) 오류 예외 처리

insert into hr.departments(department_id, department_name)
values(280,null);

출력

not null 제약조건 걸린 컬럼에 null을 입력해서 에러가 발생한다.

 

  # 오류 이름이 없기 때문에 선언해줘야 함

declare
    e_insert_notnull exception;
    pragma exception_init(e_insert_notnull, -1400);
begin
    insert into hr.departments(department_id, department_name)
    values(280,null);
exception
    when e_insert_notnull then
        dbms_output.put_line('입력시에 null 허용할 수 없다.');
end;
/

출력

※ 오류 처리할 때 오류 번호에서 앞에 0은 생략해서 써도 된다. ex) ORA-01400 이면 입력 시 -1400 쓸 수 있다.

 

# 유저 정의 예외

   - RAISE문 : 유저가 정의한 예외사항 발생, RAISE문을 만나면 무조건 exception으로 넘어간다.

update hr.employees
set salary = salary * 1.1
where employee_id = 300;

출력

▶ 오류가 발생하지 않는다. 프로그램 안에서도(PL/SQL) 에러가 발생하지 않는다.

 

※ 하지만 업무 상 만약 업데이트 했을 때 데이터가 없으면 예외사항으로 처리하고 싶을 경우

declare
    e_invalid exception; -- 에러 발생하지 않았기 때문에 선언만 한다.
begin
    update hr.employees
    set salary = salary * 1.1
    where employee_id = 300;
    if sql%notfound then
        raise e_invalid;
    end if;
exception
    when e_invalid then
        dbms_output.put_line('수정된 데이터가 없습니다.');
        dbms_output.put_line(SQLCODE);
end;
/

출력

▶ 오류번호가 1이 나온 이유는 유저 예외 사항이기 때문에 1이 출력된 것이다.

 

ex) 비즈니스 업무 상 비정상적으로 종료를 해야 할 경우

begin
    update hr.employees
    set salary = salary * 1.1
    where employee_id = 300;
    
    if sql%notfound then
        raise_application_error(-20000,'수정된 데이터가 없습니다.');
    end if;
end;
/

출력

 

♣ RAISE_APPLICATION_ERROR 프로시저

    - 유저가 정의한 오류 번호 메시지를 실행하는 프로그램

    - RAISE_APPLICATION_ERROR 수행하는 순간 프로그램은 비정상적인 종료

    - 오류번호는 -20000 ~ -20999 범위 안에서만 오류 번호를 만들어야 한다.

 

ex) 우리 회사에서 만든 에러 메시지를 쓰고 싶을 경우

declare
    v_id number := 300;
    v_rec hr.employees%rowtype;
begin
    select *
    into v_rec
    from hr.employees
    where department_id = v_id;
    dbms_output.put_line(v_rec.employee_id);
    dbms_output.put_line(v_rec.first_name);
exception
    when no_data_found then
        RAISE_APPLICATION_ERROR(-20000,'조회데이터가 없다.');
end;
/

출력

★ 현장에서는 그 회사에 맞게 오류메시지를 출력하라는 경우가 많으니 숙지해야 한다. (디벨로퍼보다 sqlplus에서 실행하는 게 더 깔끔하게 보인다.)

 

- FALSE(기본값) : 오라클의 오류가 내가 만든 오류로 바뀐다.

- TRUE : 오라클의 오류하고 내가 만든 오류가 같이 나온다.

 

ex) TRUE일 경우

declare
    v_id number := 300;
    v_rec hr.employees%rowtype;
begin
    select *
    into v_rec
    from hr.employees
    where department_id = v_id;
    dbms_output.put_line(v_rec.employee_id);
    dbms_output.put_line(v_rec.first_name);
exception
    when no_data_found then
        RAISE_APPLICATION_ERROR(-20000,'조회데이터가 없다.', true);
end;
/

출력

ex) FALSE일 경우

declare
    v_id number := 300;
    v_rec hr.employees%rowtype;
begin
    select *
    into v_rec
    from hr.employees
    where department_id = v_id;
    dbms_output.put_line(v_rec.employee_id);
    dbms_output.put_line(v_rec.first_name);
exception
    when no_data_found then
        RAISE_APPLICATION_ERROR(-20000,'조회데이터가 없다.', false);
end;
/

출력

 

# 데이터 안에 값을 모르는데 중간에 데이터가 없으면 예외처리 하고 다음 데이터로 넘어가고 싶을 경우

 

ex) 

declare
    type id_type is table of number;
    v_id id_type := id_type(100,102,300,200);
    v_rec hr.employees%rowtype;
begin
    for i in v_id.first..v_id.last loop
        select *
        into v_rec
        from hr.employees
        where employee_id = v_id(i);
        dbms_output.put_line(v_rec.employee_id||' '||v_rec.first_name);
    end loop;
exception
    when no_data_found then
        dbms_output.put_line('사원이 존재하지 않습니다.');
end;
/

출력

▶ 300번 데이터는 없기 때문에 더 검색하지 않고, EXCEPTION절로 넘어가서 출력한다.

 

ex) 하지만 EXCEPTION도 수행하고, 뒤에 데이터도 출력하고 싶을 경우 (서브블록 이용)

declare
    type id_type is table of number;
    v_id id_type := id_type(100,102,300,200);
    v_rec hr.employees%rowtype;
begin
    for i in v_id.first..v_id.last loop
        BEGIN
        select *
        into v_rec
        from hr.employees
        where employee_id = v_id(i);
        dbms_output.put_line(v_rec.employee_id||' '||v_rec.first_name);
        EXCEPTION
            when no_data_found then
            dbms_output.put_line(v_id(i)||'사원이 존재하지 않습니다.');
        END;
    end loop;
exception
    when others then
        dbms_output.put_line(SQLERRM);
end;
/

출력

▶ 이 코드에서 서브블록을 쓰는 이유는 각 사원들에 대해 개별적으로 예외를 처리하고, 오류가 발생하더라도 전체 루프 실행에 영향을 미치지 않도록 하기 위해서 사용한다.

▶ 서브블록 안에서 EXCEPTION 구문을 사용하여 예외를 처리함으로써, 현재 직원에 대한 예외가 발생해도 다른 직원에 대한 처리는 계속될 수 있도록 할 수 있다.

▶ 만약 서브블록 없이 for문 밖에서 예외를 처리한다면, no_data_found 예외가 발생할 때 루프 전체가 중단될 수 있다. 서브블록을 사용하여 각 직원별로 독립적인 예외처리를 할 수 있다.