도 닦는 개발자

정의

PL/SQL이란 오라클 DBMS의 SQL언어를 확장하기 위해 사용하는 프로그래밍 언어이다. (Oracle's Procedural Language extension to SQL)

튜닝포인트

잦은 DBMS Call은 성능 저하를 일으킴.
절차지향적 로직 → 비절차지향적 쿼리

개발 시 명심할 것

주석으로 이력 남기기
예외처리를 통해 오류 내용을 처리한다.
1. 전체 Exception
2. 부분 Exception
  1. 상세예외처리
Transaction을 생각하며 프로그래밍 할 것. (commit, rollback, savepoint, rollback to)
프로시저의 out 파라미터를 사용하여 프로시저 성공/실패 여부 및 메세지 리턴

PL/SQL 실행과정

PL/SQL → 컴파일(파서) → 명령어 → 프로그램에서 해당 명령어를 실행

디버그

디버그 기능 있다.

출력

find_file.put_line : 파일 저장
dbms_output.put_line : 콘솔 출력
SQLCODE
- 오라클에서 정의한 에러코드 반환
SQLERRM
- 에러메시지 반환

Function Procedure 차이점

function은 IN 파라미터만을 받을 수 있으며 무조건 값을 반환해야함.

procedure은 IN, OUT 파라미터를 사용할 수 있음. 그리고 값을 반환하지 않아도 됨.

→ 사용의 차이가 존재.

명시적 커서, 암시적 커서

암시적 커서는 SQL문이 실행될 때 생성된다.
- SQL%FOUND
- SQL%NOTFOUND
- SQL%ISOPEN
- SQL%ROWCOUNT
명시적 커서는 명시적으로 선언되는 커서로 이름이 있다.
- %FOUND
- %NOTFOUND
- %ROWCOUNT
- %ISOPEN

    begin
        xxcrsh_mail_pkg.send_encouraging_mail('1', '2', '3'); -- 순서대로 파라미터 삽입
        xxcrsh_mail_pkg.send_encouraging_mail(p_status_code => '1', p_caring_seqs => '2', p_schedule_code => '3'); -- 파라미터명에 맞게 파라미터 삽입
    end;

%TYPE, %ROWTYPE

    rec_inf xxcdhd_pu_supplier_r_i_v%ROWTYPE;
    rec_inf xxcdhd_pu_supplier_r_i_v.seq_id%TYPE;

Oracle RECORD, TABLE

기존 테이블 구성과 같음.
레코드는 하나의 로우라고 생각.
테이블은 그 로우들의 집합.

 -- 레코드 선언 
 -- 선언 예제
 TYPE record_test IS RECORD 
   ( record_empno   NUMBER, 
     record_ename   VARCHAR2(30), 
     record_sal     NUMBER); 


 -- record_test 레코드 타입으로 prd_record변수를 선언해서 사용 
 prd_record    record_test;

-- 아래 프로시저에서 사용된 예제를 보면 이해가 쉽게 갈 것이다.
-- 레코드 테이블
-- 선언 예제
TYPE dept_table_type IS TABLE OF dept%ROWTYPE
INDEX BY BINARY_INTEGER; 

-- Each element of dept_table  is a record 
dept_table   dept_table_type;

-- 아래 프로시저에서 사용된 예제를 보면 이해가 쉽게 갈 것이다.

savepoint, rollback to

    savepoint s_1;


    rollback to s_1;

예외처리

Java Exception과 같은 기능.
미리 정의된 예외

사용자 정의 예외

  -- 미리 정의된 예외(Predefined ORACLE Exception) EX)
  declare
    emp_lastname employees.last_name%type;
  begin
    select last_nameinto emp_lastname
    from employees
    where last_namelike 'B%';

    DBMS_OUTPUT.put_line('사원명 : ' || emp_lastname);

  exception
    when no_data_found then
      DBMS_OUTPUT.PUT_LINE('해당 사원이 없습니다.');
    when too_many_rows then
      DBMS_OUTPUT.PUT_LINE('사원이 두명 이상입니다.');
  end;
  /

  -- 사용자 정의 예외처리 EX)
  -- 1. pragma를 이용한 컴파일러에 지정하는 방법
  declare
    emp_exist exception;
    pragma exception_init(emp_exist, -2292);-- emp_exist예외는 2292번 에러 코드로 처리하겠다고 컴파일러에 알려주는 부분
  begin
    delete departments
    where department_id = &deptno;-- 사용자에게 detpno값 입력 받아 해당 내용을 지움

    commit;-- 반영함

    exception
      when emp_exist then
        DBMS_OUTPUT.PUT_LINE('사원이 존재하기 때문에 지울 수 없습니다.');
  end;
  /-- 20번 입력

  -- 2. raise를 이용한 예외처리 발생 방법
  declare
    emp_id employees.employee_id%typ

    no_emp exception;-- 예외명 선언
  begin
    delete employees
    where employee_id = &empid;

    if sql%notfoundthen -- "묵시적커서" 속성을 이용
      raise no_emp;-- 예외를 발생시킴
    end if;

    exception
      when no_emp then -- raise로 no_emp가 발생할 경우 처리할 부분
        DBMS_OUTPUT.PUT_LINE('해당사원이 없습니다.');
  end;
  /

  -- raise_application_error라는 프로시저를 이용하는 방법
      --  이때, 이용가능한 번호 : 20000 ~ 20999 번
  declare
    emp_id employees.employee_id%type;
    emp_name employees.last_name%type;
  begin
    delete employees
    where employee_id = &empid;

    if (sql%notfound)then
      raise_application_error(-20111,'사원이 없습니다..');-- 에러코드와 에러메시지 지정
    end if;
  end;
  /
  set serveroutputon;

파일 로그 처리

    BEGIN
    EXCEPTION
        WHEN OTHERS THEN
            fnd_file.out_put(fnd_file.log, 'msg');
    END;

PL/SQL 스택 트레이스

    func (p_err_code, p_err_msg);

    IF(p_err_code <> 0)  THEN
        p_err_msg := 'thisfunc:::' || p_err_msg; -- 호출한 함수, 프로시저명 명시, 스택이 풀리면서 스택트레이스가 가능.
    END IF;

저작자표시 비영리 변경금지

정의