PL/SQL - DAY 9

BUMSOO·2024년 7월 15일
PL/SQL오라클 DBA

복습내용

DROP TABLE hr.test PURGE;

CREATE TABLE hr.test (
    id number(1) CONSTRAINTS id_pmk PRIMARY KEY );
    
SELECT * FROM user_constraints WHERE table_name = 'TEST';
SELECT * FROM user_cons_columns WHERE table_name ='TEST';

DECLARE
    TYPE id_type IS TABLE OF number;
    v_id id_type := id_type(1,2,3,4,5,1,10,2,20);
BEGIN
    FOR i IN v_id.first..v_id.last LOOP
        INSERT INTO hr.test(id) VALUES(v_id(i));
    END LOOP;
END;
/

-- primary key 제약조건의 위배된 데이터가 입력되는 순간 프로그램은 비정상적인 종료가 발생, transaction은 자동 rollback되었다.  
SELECT * FROM hr.test;

EXCEPTION을 통해 END가 되면 transaction은 계속 살아있다. 

DECLARE
    TYPE id_type IS TABLE OF number;
    v_id id_type := id_type(1,2,3,4,5,1,10,2,20);
BEGIN
    FOR i IN v_id.first..v_id.last LOOP
        INSERT INTO hr.test(id) VALUES(v_id(i));
    END LOOP;
EXCEPTION 
    WHEN others THEN
        dbms_output.put_line(sqlcode);
        dbms_output.put_line(sqlerrm);
END;
/

-- 예외사항이 발생하는 순간 EXCEPTION 절로 트랩을 시킨 후 예외사항 처리를 하고 프로그램을 정상적으로 종료한다.
-- 단, transaction은 살아 있다. 꼭 transaction을 종료하는 습관을 갖자.
SELECT * FROM hr.test;
ROLLBACK;

배열 안에 있는 값을 기준으로 데이터를 입력하면서 예외사항이 발생하더라도
배열안에 있는 값 기준으로 끝까지 수행할 수 있도록 하려면?

<중복 블락으로 해결>

DECLARE
    TYPE id_type IS TABLE OF number;
    v_id id_type := id_type(1,2,3,4,5,1,10,2,20,7);
BEGIN
    FOR i IN v_id.first..v_id.last LOOP
        BEGIN 
            INSERT INTO hr.test(id) VALUES(v_id(i));
        EXCEPTION
            WHEN others THEN
            dbms_output.put_line(sqlcode);
            dbms_output.put_line(sqlerrm);
        END;
    END LOOP;
END;
/

SELECT * FROM hr.test;
ROLLBACK;

<FORALL 문으로 해결>

/* 배열 안에 있는 값들을 기준으로 DML 작업을 수행할 경우 PL/SQL 엔진과 SQL 엔진
   사이에 문맥 전환을 줄이기 위해서 FORALL문을 이용하면 성능이 좋아진다.*/
DECLARE
    TYPE id_type IS TABLE OF number;
    v_id id_type := id_type(1,2,3,4,5,1,10,2,20);
    edd_err EXCEPTION;
    PRAGMA EXCEPTION_INIT(edd_err,-24381);    
BEGIN
    FORALL i IN v_id.first..v_id.last SAVE EXCEPTIONS
        INSERT INTO hr.test(id) VALUES(v_id(i));
EXCEPTION 
    WHEN edd_err THEN
            dbms_output.put_line(sql%bulk_exceptions.count);
        FOR i IN 1..sql%bulk_exceptions.count LOOP
            dbms_output.put_line(sql%bulk_exceptions(i).error_index);
            dbms_output.put_line(-sql%bulk_exceptions(i).error_code);
            dbms_output.put_line(sqlerrm(-sql%bulk_exceptions(i).error_code));
        END LOOP;
    ROLLBACK;
END;
/

특정 테이블에 대량으로 DML 작업시 배열 인덱스에 갭이 있을 경우 오류가 발생

<sparse collection 예시>

/* sparse collection, 즉 배열에 갭이 있는 경우 */
DECLARE
    TYPE test_tab IS TABLE OF test%rowtype INDEX BY pls_integer;
    v_tab test_tab;
    dml_erros EXCEPTION;
    PRAGMA EXCEPTION_INIT(dml_erros,-24381);
    erros number;
BEGIN
    SELECT object_id,object_name
    BULK COLLECT INTO v_tab
    FROM all_objects
    WHERE rownum <= 10;
    
    dbms_output.put_line(v_tab.count);
    v_tab.delete(2);
    v_tab.delete(4);
    v_tab.delete(6);
    dbms_output.put_line(v_tab.count);
    
    FORALL i IN v_tab.first..v_tab.last 
        INSERT INTO hr.test
        VALUES v_tab(i); -- 배열의 요소 번호가 갭이 있는 경우 forall 이용해서 대량에 데이터를 입력시 오류 발생한다.
                         -- 프로그램이 비정상적으로 종료 발생 transaction은 자동 rollback된다.           
END;
/

<SAVE EXCEPTIONS을 활용한 해결>

DECLARE
    TYPE test_tab IS TABLE OF test%rowtype INDEX BY pls_integer;
    v_tab test_tab;
    dml_erros EXCEPTION;
    PRAGMA EXCEPTION_INIT(dml_erros,-24381);
    erros number;
BEGIN
    SELECT object_id,object_name
    BULK COLLECT INTO v_tab
    FROM all_objects
    WHERE rownum <= 10;
    
    v_tab.delete(2);
    v_tab.delete(4);
    v_tab.delete(6);

    FORALL i IN v_tab.first..v_tab.last SAVE EXCEPTIONS --save exceptions를 이용
        INSERT INTO hr.test
        VALUES v_tab(i); 
EXCEPTION 
    WHEN dml_erros THEN
            dbms_output.put_line(sql%bulk_exceptions.count);
    
        FOR i IN 1..sql%bulk_exceptions.count LOOP
            dbms_output.put_line(sql%bulk_exceptions(i).error_index);
            dbms_output.put_line(-sql%bulk_exceptions(i).error_code);
            dbms_output.put_line(sqlerrm(-sql%bulk_exceptions(i).error_code));
        END LOOP;
    ROLLBACK;
END;
/
  • INDICES OF : 배열의 입력되어 있는 값들을 특정한 테이블에 입력할 시 배열 요소 번호에 갭이 있는 경우 기존 방식으로는 SAVE EXCEPTIONS를 이용해서 했던 방식을 FORALL i IN INDICES OF 으로 개선 했다.

<INDICES OF를 활용한 개선코드>

DECLARE
    TYPE test_tab IS TABLE OF test%rowtype INDEX BY pls_integer;
    v_tab test_tab;
    dml_erros EXCEPTION;
    PRAGMA EXCEPTION_INIT(dml_erros,-24381);
    erros number;
BEGIN
    SELECT object_id,object_name
    BULK COLLECT INTO v_tab
    FROM all_objects
    WHERE rownum <= 10;
    
    v_tab.delete(2);
    v_tab.delete(4);
    v_tab.delete(6);
   
    FORALL i IN INDICES OF v_tab -- 배열의 갭이 있는 부분을 알아서 제외후 처리
        INSERT INTO hr.test
        VALUES v_tab(i);        
END;
/

익명블록(Anonymous Block)

  • 이름이 없는 PL/SQL 블록
  • 매번 컴파일 해야한다.
  • 데이터베이스에 저장되지 않습니다.
  • 다른 응용프로그램에서 호출할 수 없다.
  • 입력처리,리턴값 처리를 바인드변수를 이용해서 수행해야 한다.
  • 파라미터를 사용할 수 없다.
  • 객체가 아니다

서브프로그램(SubProgram)

  • 이름이 있는 PL/SQL블록
  • 한번만 컴파일한 후 호출시에는 컴파일된 코드를 사용한다.
  • 데이터베이스에 저장되어있다.
  • 다른 응용프로그램에서 호출할 수 있다.
  • 입력처리, 리턴값 처리를 할 수 있다.
  • 파라미터를 사용할 수 있다.
  • 객체처리 된다.
  • procedure, function, package
  • CREATE PROCEDURE 시스템권한(CREATE function, package라는 권한은 없다)
  • 바인드 변수 사용할 수 없다.

PROCEDURE(프로시저)

  • 특정 작업을 수행하는 서브프로그램
  • 데이터베이스에 객체로 저장된다.
  • 재사용성과 유지관리 편리성 때문에 사용한다.
  • sql문에서 사용 불가, 함수처럼 사용 불가
  • 호출단위 프로그램
  • 프로시저 생성시 컴파일 오류가 발생하여도 소스코드, 객체 정보는 딕셔너리에 저장되어 있다. 다만 invalid 상태이기 때문에 작동은 불가능 하다.
CREATE OR REPLACE PROCEDURE emp_proc(p_id IN number)
IS
    v_rec hr.employees%rowtype;
BEGIN
    SELECT *
    INTO v_rec
    FROM hr.employees
    WHERE employee_id = p_id;
    
    dbms_output.put_line(v_rec.last_name || ' ' || v_rec.salary);
EXCEPTION
    WHEN no_data_Found THEN
        dbms_output.put_line(p_id || '번 사원은 존재하지 않습니다');
END;
/

execute emp_proc(200);
  • 프로시저 호출 방법
-- 프로시저 실행
execute emp_proc(200);

또는

BEGIN
    emp_proc(200);
END;
/

  • 오류 확인
    show error

  • 서브 프로그램 소스 확인
    SELECT * FROM user_source;

FORMAL PARAMETER(형식 파라미터)

  • 형식 매개변수(변수 모드 데이터타입)

  • 프로그램 안에서 로컬변수로 사용한다.

  • 파라미터 모드
    1) IN : 입력값 처리하는 기능, 즉 호출환경에서 프로시저로 전달한다.
    상수로 동작한다. 한번 받은 값만 사용해야 한다. 프로그램 안에서 다른값으로 수정할 수 없다.
    기본값으로 사용, IN을 작성하지 않아도 된다.

    2) OUT : 프로그램안에서 값을 호출환경으로 전달한다. 변수로 동작한다.

    3) IN OUT : 값을 호출환경에서 프로시저로 전달하고, 프로시저 안에서 새로운 값을 호출환경에 전달한다. 초기값이 있는 변수로 동작해야 한다.

  • 사원번호를 입력값으로 받아서 사원의 last_name, salary를 받아오는 프로그램
    • 즉 last_name, salary 값을 호출환경으로 전달한다.
CREATE OR REPLACE PROCEDURE emp_proc
(p_id IN number,
 p_name OUT varchar2,
 p_sal OUT number)
IS -- IS는 필수값으로 생략해서는 안된다.
BEGIN
    SELECT last_name, salary
    INTO p_name, p_sal
    FROM hr.employees
    WHERE employee_id  = p_id;
EXCEPTION
    WHEN no_data_found THEN
        dbms_output.put_line(p_id || ' 사원은 존재하지 않습니다.');
END;
/
desc emp_proc;

var b_name varchar2(30)
variable b_sal number
execute emp_proc(100, :b_name, :b_sal);
  • 사원번호를 입력값으로 받아서 사원의 last_name, salary를 출력하는 프로그램
CREATE OR REPLACE PROCEDURE emp_proc
(p_id IN number)
IS -- IS는 필수값으로 생략해서는 안된다.
    v_name varchar2(30);
    v_sal number;
BEGIN
    SELECT last_name, salary
    INTO v_name, v_sal
    FROM hr.employees
    WHERE employee_id  = p_id;
    dbms_output.put_line(v_name || ' ' ||v_sal);
EXCEPTION
    WHEN no_data_found THEN
        dbms_output.put_line(p_id || ' 사원은 존재하지 않습니다.');
END;
/

execute emp_proc(100);
  • IN OUT 모드를 활용한 전화번호 변환 프로그램
CREATE OR REPLACE PROCEDURE format_phone
    (p_phone IN OUT varchar2)
IS
BEGIN
    p_phone := substr(p_phone,1,3) || '-' || substr(p_phone,4,4) || '-' || substr(p_phone,8);
END;
/

var b_phone varchar2(30)
execute :b_phone := '01012345678'
print :b_phone

execute format_phone(:b_phone);
print :b_phone

ACTUAL PARAMETER(실제 파라미터), 실제 매개변수

  • 호출 서브프로그램의 파라미터 리스트에 사용되는 리터럴값, 변수, 표현식
execute emp_proc(200) --200(리터럴값)은 실제 파라미터, 실제 매개변수
DECLARE
    v_id number := 110;
BEGIN
    emp_proc(v_id); -- v_id(변수) 실제 파라미터, 실제 매개변수
END;
/

변수 지정 방식

DROP TABLE hr.test PURGE;
CREATE TABLE hr.test
( id number,
  name varchar2(30),
  day date,
  deptno number);
  
CREATE OR REPLACE PROCEDURE insert_proc
( p_id  IN  number,
  p_name IN varchar2,
  p_day IN date default sysdate,
  p_dept_no IN number := 0)
IS
BEGIN
    INSERT INTO hr.test(id,name,day,deptno)
    VALUES(p_id, p_name, p_day, p_dept_no);    
END insert_proc;
/

desc hr.test;

위치지정방식

/* 형식매개변수에 실제매개변수값이 위치적으로 대응되게 입력한다.*/
execute insert_proc(1,'scott',to_date('2020-01-20','yyyy-mm-dd'),10);
SELECT * FROM hr.test;

이름지정방식

변수명 => 대입값

/* 형식매개변수의 이름지정방식으로 실제매개변수값을 입력*/
execute insert_proc(p_id => 2, p_name => 'james', p_dept_no => 20);
SELECT * FROM hr.test;

혼합방식(위치+이름)

EXECUTE insert_proc(3,p_name=>'sophia');
SELECT * FROM hr.test;

⚠️ 혼합방식 사용시 주의 할점은 이름지정방식 뒤에는 위치지정방식 사용할 수 없다.

EXECUTE insert_proc(p_id=>4, 'emma'); -- 오류발생

[문제20]

사원번호를 입력 값으로 받아서 그 사원의 이름,급여,부서 이름을 출력하는 프로시저 프로그램을 생성하세요. 단, 100번 사원이 입력 값으로 들어오면 프로그램은 아무런 작업하지 않고 종료 될 수 있어야 합니다. 또한 사원이 없을 경우 예외 사항 처리해주세요.

<RAISE_APPLICATION_ERROR 풀이>

CREATE OR REPLACE PROCEDURE emp_proc
( p_id hr.employees.employee_id%type)
IS  
    v_name hr.employees.last_name%type;
    v_salary hr.employees.salary%type;
    v_dept_name hr.departments.department_name%type;
BEGIN
    IF p_id = 100 THEN
        RAISE_APPLICATION_ERROR(-20000,'100번 사원은 조회 할 수 없습니다');
    END IF;
    
    SELECT a.last_name, a.salary, (SELECT department_name FROM hr.departments WHERE department_id = a.department_id) 
    INTO v_name, v_salary, v_dept_name
    FROM hr.employees a
    WHERE employee_id = p_id;
    
    dbms_output.put_line(v_name || ' ' || v_salary || ' ' ||v_dept_name);
    
EXCEPTION
    WHEN no_data_found THEN
        dbms_output.put_line(p_id || ' 사원은 찾을 수 없습니다');
END;
/

execute emp_proc(102);
  • RETURN : 값이 없는 RETURN문은 프로시저를 종료하는 문이다.
    <RETURN 풀이>
CREATE OR REPLACE PROCEDURE emp_proc
( p_id hr.employees.employee_id%type)
IS  
    v_name hr.employees.last_name%type;
    v_salary hr.employees.salary%type;
    v_dept_name hr.departments.department_name%type;
BEGIN
    IF p_id = 100 THEN
       RETURN; --아무런 행동 없이 프로그램 종료
    END IF;
    
    SELECT a.last_name, a.salary, (SELECT department_name FROM hr.departments WHERE department_id = a.department_id) 
    INTO v_name, v_salary, v_dept_name
    FROM hr.employees a
    WHERE employee_id = p_id;
    
    dbms_output.put_line(v_name || ' ' || v_salary || ' ' ||v_dept_name);
    
EXCEPTION
    WHEN no_data_found THEN
        dbms_output.put_line(p_id || ' 사원은 찾을 수 없습니다');
END;
/

[느낀점]

오늘 처음으로 3일을 쉬고 학원에 왔더니 오랜만에 오는 기분이 들었고, 그 이전에 배웠던거가 기억이 가물가물해서 등원 전부터 복습을 하면서 왔다. 강사님께서도 우리들의 학습 상태가 조금 불안 하셨는지 오전에는 거의 저번주 마지막날 배웠던 EXCEPTION 핸들링에 대한 복습 수업을 하였다. 확실히 복습 수업을 안했다면 EXCEPTION 핸들링은 조금 헷갈리는 부분들이 있었을 것 같다. 오후에는 그동안은 PL/SQL로 익명블록구조 프로그램만 만들었다면, 드디어 객체 타입으로 만들 수 있는 서브프로그램의 한 종류인 PROCEDURE에 대해 공부하였다. 익명블록구조 같은 경우 재사용 또는 다른 응용프로그램에서는 사용이 불가능 하지만 프로시저는 객체타입으로 익명블록구조의 단점을 완전히 보완했다고 볼 수 있다. 프로시저를 배우면서 흥미로웠던 점은 형식 매개변수 속 데이터 모드 였는데, 모드는 총 3가지고 IN,OUT, IN/OUT으로 나뉘었다. 우리가 그동안 함수나 프로시저를 사용시 그냥 간단하게 값을 대입했던건 나도 모르게 IN 모드로 값을 넣고 있었던 것이였다. 프로시저를 놓고 자세히 생각하던 중 프로시저 형식 매개변수 선언을 다 스칼라 데이터 형태로 선언하고 있는점이 의문이여서 배열 또는 레코드 타입으로는 지정 할 수 없는지 강사님께 질문 드렸더니 그건 패키지를 이용해야 한다는 답변을 들었다. 벌써 수업을 들은지 한달이 넘어가고 11월은 금방 올것 같은데 이대로 수업만 듣는다고 과연 취업은 할 수 있을지 더 열심히 공부 해야겠다는 생각이 드는 하루 인 것 같다.

profile
BUMSOO
이전 포스트

PL/SQL - DAY 8

다음 포스트

PL/SQL - DAY 10

0개의 댓글