[PL/SQL]코드 설계 고려 사항 기초

코드 설계 고려 사항 기초

상수 및 예외 표준화

- Package Spec으로 구현

- 장점

  - 일관성

  - 재사용 증가

  - 유지관리 간편

  - 회사 표준 구현

- 대상

  - 예외 이름

  - 상수 정의

로컬 서브 프로그램

- 서브 프로그램의 선언 섹션 끝에 정의된 프로시져 또는 함수

정의자 권한

- 생성자의 권한으로 프로그램 실행

- 프로시저 실행 권한만 필요

- 작업을 진행 했다면 하나의 프로시져에서 테이블을 delete할 수 있는 명령어를 썼다면

  DELETE TABLE으로 해야하는데 이때 TABLE은 user이름을 생략하면 자신의 TABLE로 처      리 한다.

호출자권한

- 호출자의 권한으로 프로그램 실행

- 프로시저에 액세스하는 기본객체에 대한 권한필요.

- 이경우에서 위와 같은 작업을 진행하면 사용되는 이름은 호출자의 스키마에서 분석된다.

독립 트랜잭션(Autonomous Transaction)

- 다른 주 트랜잭션에 의해 시작되는 독립적인 트랜잭션

- PRAGMA AUTONOMOUS_TRANSACTION의로 정의

- 주 트랜잭션이 롤백하면 롤백 적용X

- 독립 트랜잭션에서 빠져나가기 전에 무조건 COMMIT을 실행해야한다.(종료된체로 넘어가야한다.)

다음 예시를 확인하자

--주 트랜잭셕
PROCEDURE TEST1 IS
    EMPNO NUMBER;
BEGIN
    EMPNO := 7788;
    COMMIT;
    INSERT -- 1
    TEST2; -- 2
    DELETE
    COMMIT -- 7
END TEST1;

PROCEDURE TEST2 IS
    PRAGMA
    AUTONOMOUS_TRANSACTION
    DEPTNO = NUMBER := 30;
BEGIN -- 3
    UPDATE ... -- 4
    INSERT ... 
    COMMIT; -- 5
END TEST2 -- 6

위와 같은 구문은 주석 처리를 해놓은 순서대로 처리가 되는데, 이때 PRAGMA문을 적지 않으면 TEST2에서 COMMIT시 3,4,5과정과 함께 1번 과정도 같이 COMMIT이 이루어 진다.

하지만 PRAGMA문을 사용하면 TEST2인 3,4,5만 COMMIT이 이루어지게 된다.

NOCOPY 힌트 사용

- 값을 전달하는 것이 아닌 주소를 넘겨주는 방식

  - IN, OUT은 값을 COPY해서 안 또는 밖으로 보내주는 방식이다.

- NOCOPY를 사용하면 항상 값이 전달이 안되고 주소만 넘겨주는 것만은 아니다

- 처리되지 않는 예외가 발생하거나 일관성을 유지하지 못한다면 서브프로그램이 종료되며, 완료되지 않은 수정 사항은 ROLLBACK되지 않는다.

세션간 PL/SQL 함수 결과 CACHE 사용

- 서로 다른 파라미터 값으로 결과 캐시된 PL/SQL함수를 호출할 때마다 해당 파라미터와 결과가 CACHE에 저장된다.
- 데이터가 큰 경우 영향이 있지만, 데이터가 많은 경우는 사용하지 않는다.

- 함수선언 혹은 함수 정의에서 RESULT_CACHE절을 포함시킨다.

 다음 예시를 보자

CREATE OR REPLACE FUNCTION TEST1 (EMPNO
    NUMBER) RETURN VARCHAR
RESULT_CACHE RELIES_ON (EMP) IS
    V_HIREDATE DATE;
BEGIN
    SELECT HIREDATE INTO V_HIREDATE
    FROM HR.TEST1
    WHERE EMPNO = P_EMPNO;
    RETURN to_char(V_HIREDATE);
END;
/
 

RETURNING 절 사용 (중요)

- INSERT, UPDATE, DELETE문을 사용하여 열값 반환

- SELECT가 필요가 없음

- 성능이 향상

다음 예시를 보자

CREATE OR REPLACE PROCEDURE up_sal(p_empno NUMBER) IS
    v_ename emp.ename%TYPE;
    v_sal emp.sal%TYPE;
BEGIN
    UPDATE emp
    SET sal = sal * 1.1
    WHERE empno = p_empno
    RETURNING ename, sal INTO v_ename, v_sal;
    DBMS_OUTPUT.PUT_LINE(v_ename || ' new salary is ' || v_sal);
END up_sal;
/

대량 바인드(Bulk Binding) 사용 (중요)

- 루프를 사용하여 FETCH, INSERT, UPDATE, DELETE를 여러번 하는 대신, 한 번의 작업으로 전체 값 배열을 바인드 하는 것.

- 한번의 작업으로 전체 값 배열을 바인드

- INTO 절은 하나의 행을 가져온다는 것인데 BULK를 사용하면 여러 행을 가져올 수 있다. 이때 연관 배열이 정의되어 있어야 가능하다.

- 

다음과 같은 구문을 사용한다.

FORALL 키워드

- 입력 컬렉션을 SQL 엔진에 보내기전에 PL/SQL엔진에서 대량으로 바인드

FORALL index IN lower_bound .. upper_bound
    [SAVE EXCEPTIONS]
    sql_statement;

   

BULK COLLECT 키워드

- 출력 컬렉션을 PL/SQL엔진에 반환하기 전에 SQL엔진에서 대량으로 바인드

- BULK COLLECT 는 SQL엔진에서 PL/SQL엔진으로 가져오는것

- RETURNING절과 함께 사용이 가능하다.

... BULK COLLECT INTO
    collection_name[,collection_name] ...

SQL을 배운지 얼마 되지 않아 잘못된 내용이 있을 수 있습니다. 틀린 내용이있다면, 댓글로 달아주세요.