[데이터베이스] PL/SQL

PL/SQL

1. 언어적 주요 특징 SQL + PL
2. 장점, 활용
3. Function, Procedure, Package, Trigger

모델링

1. 엔티티 정의, 종류
2. 속성 정의, 종류
3. 관계 정의, 종류

ERD (Entity Relationship Diagram)

SDLC (Software Development Life Cycle)

분석 → 설계 → 개발 → 테스트 → 이행 → 운영

모델링 : 고객의 요구사항을 분석해서 ERD 를 그린다.

PL/SQL 의 장점 : 효율성과 생산성

개발&유지보수

실행(처리) → 성능 향상

SQL 의 비절차적 언어라는 특징에 절차적인 언어의 장점을 결합하여 상호 보완적인 관계

절차적언어의 특징

1. 블록구조
2. 변수선언
3. 제어구조
4. 예외처리
5. 모듈화

대입연산자 :=

비교연산자 =

재사용 (Reuse)

Function / Procedure 차이점 정리

모듈화란 프로그램 개발시 모듈단위로 나누어 개발 하거나 독립 모듈 (Function / Procedure / Package) 를 만들어 라이브러리화 시켜 재사용

server stored block 은 dbms 에 소스가 위치

client stored block 은 client 에 소스가 위치

이식성 (Portability)

통합성 (Integration)

→ 처리할 대상 데이터와 데이터 처리 로직이 동일한 위치에 통합되어 처리

성능 (Performance)

CLOB : character

BLOB : binary

⇒ DBMS 에 4GB 까지 저장 가능하다

특징

• 하나의 테이블에 여러 개의 LOB 열(column) 가능
• 최고 4GB까지 저장
• SELECT로 위치자 반환
• 순서대로 또는 순서없이 데이터 저장
• 임의적 데이터 액세스

LOB 구성

• LOB 값 : 저장될 실제 객체를 구성하는 데이터
• LOB 위치자 : 데이터베이스에 저장된 LOB값의 위치에 대한 포인터

트랜잭션 유형

OLTP

OLAP - DSS, batch : PL/SQL 사용 시 성능향상을 체감할 수 있음

DTP

---

Nested block & Exception

목적 : 1) 예외처리 2) 모듈화

실행가능한 첫 번째 SQL 문장에서 트랜잭션 시작

ora-01403 원인 및 조치 방안

select NAME INTO P_NAME from MEMBER where MEMBER_ID = 1

error : no data found

→ solution

select (
    select NAME from MEMBER where MEMBER_ID = 1
    ) INTO P_NAME
from DUAL;

begin
          select   NVL(FIRST_ZERO_APPROVED,'Y')
         ,from   aaa
          into   VVV_FIRST_ZERO_APPROVED
         ,       VVV_ASSESSMENT_STATUS
          from   gbsc_plan_manage_header
          where  le              = :parameter.le
          and    periods_seq     = :SEARCH.PERIODS_SEQ
          and    EMPLOYEE_NUMBER = :parameter.employee_number;
exception
    when no_data_found then null;

SET SERVEROUTPUT ON

DECLARE
    TYPE t_address IS RECORD (
        addr1      VARCHAR2(60),
        addr2      VARCHAR2(60),
        zip        VARCHAR2(7),
        phone      VARCHAR2(14)
    );
    TYPE t_emp_record IS RECORD (
        empno      NUMBER(4),
        ename      VARCHAR2(10),
        job        VARCHAR2(9),
        address    t_address,
        hiredate   DATE
    );
    rec_emp    t_emp_record;
BEGIN
    rec_emp.empno := 1234;
    rec_emp.ename := 'XMAN';
    rec_emp.job := 'DBA';
    rec_emp.address.addr1 := '강남구 역삼동';
    rec_emp.address.zip := '150-036';
    rec_emp.hiredate := SYSDATE - 365;
    dbms_output.put_line('***********************************');
    dbms_output.put_line('사번 : ' || rec_emp.empno);
    dbms_output.put_line('이름 : ' || rec_emp.ename);
    dbms_output.put_line('직업 : ' || rec_emp.job);
    dbms_output.put_line('주소 : ' || rec_emp.address.addr1);
    dbms_output.put_line('우편번호 : ' || rec_emp.address.zip);
    dbms_output.put_line('입사일 : '
     || TO_CHAR(rec_emp.hiredate,'YYYY-MM-DD') );
    dbms_output.put_line('***********************************');
END;
/

SET SERVEROUTPUT ON

DECLARE
    TYPE t_emp_list IS
        TABLE OF VARCHAR2(20) INDEX BY BINARY_INTEGER;
    tbl_emp_list   t_emp_list;
    v_tmp          VARCHAR2(20);
    v_index        NUMBER(10);
BEGIN
    tbl_emp_list(1) := 'SCOTT';
    tbl_emp_list(1000) := 'MILLER';
    tbl_emp_list(-2134) := 'ALLEN';
    tbl_emp_list(0) := 'XMAN';
    v_tmp := tbl_emp_list(1000);
    dbms_output.put_line('DATA OF KEY 1000 IS ' || tbl_emp_list(1000) );
    dbms_output.put_line('DATA OF KEY -2134 IS' || tbl_emp_list(-2134) );
    dbms_output.put_line('DATA OF KEY 1 IS' || tbl_emp_list(1) );
    IF
        NOT tbl_emp_list.EXISTS(888)
    THEN
        dbms_output.put_line('DATA OF KEY 888 IS NOT EXIST');
    END IF;

    v_index := tbl_emp_list.first;
    LOOP
        dbms_output.put_line('LOOP : '
         || TO_CHAR(v_index)
         || ' ==> '
         || tbl_emp_list(v_index) );

        v_index := tbl_emp_list.next(v_index);
        EXIT WHEN
            v_index IS NULL;
    END LOOP;

    dbms_output.put_line('DATA OF KEY 999 IS ' || tbl_emp_list(999) );
    dbms_output.put_line('DATA OF KEY 0 IS ' || tbl_emp_list(0) );
EXCEPTION
    WHEN no_data_found THEN
        dbms_output.put_line('ERROR CODE => ' || TO_CHAR(sqlcode) );
        dbms_output.put_line('ERROR MSG => ' || sqlerrm);
END;
/

• pl/sql 에서 SELECT 이후에 INTO 변수가 잇어야 한다. select 한 결과를 저장해야 한다.

ROW TYPE

SET SERVEROUTPUT ON
DECLARE
    REC_EMP     EMP%ROWTYPE;
    V_EMPNO     EMP.EMPNO%TYPE;
BEGIN
    SELECT * INTO REC_EMP FROM EMP WHERE EMPNO = 7369;

    DBMS_OUTPUT.PUT_LINE('EMPNO => ' || REC_EMP.EMPNO);
    DBMS_OUTPUT.PUT_LINE('ENAME -> ' || REC_EMP.ENAME);
    DBMS_OUTPUT.PUT_LINE('JOB          =>'||REC_EMP.JOB);
    DBMS_OUTPUT.PUT_LINE('MGR         =>'||REC_EMP.MGR);
    DBMS_OUTPUT.PUT_LINE('HIREDATE   =>'||REC_EMP.HIREDATE);
    DBMS_OUTPUT.PUT_LINE('SAL           =>'||REC_EMP.SAL);

    SELECT EMPNO, ENAME INTO V_EMPNO, REC_EMP.ENAME
    FROM EMP
    WHERE EMPNO = 7369;

    DBMS_OUTPUT.PUT_LINE('-------------------------------');
    DBMS_OUTPUT.PUT_LINE('EMPNO => ' || V_EMPNO);
    DBMS_OUTPUT.PUT_LINE('ENAME => ' || REC_EMP.ENAME);
END;
/

SET SERVEROUTPUT ON
DECLARE
    V_EMPNO EMP.EMPNO%???;
    V_ENAME EMP.ENAME%???;
    V_HIREDATE EMP.HIREDATE%???;
BEGIN
    SELECT EMPNO, ENAME, HIREDATE INTO V_EMPNO, V_ENAME, V_HIREDATE
    FROM EMP
    WHERE EMPNO >= 1;

    DBMS_OUTPUT.PUT_LINE('selected exactly one row by ' || V_EMPNO);
EXCEPTION
    WHEN NO_DATA_FOUND THEN
        DBMS_OUTPUT.PUT_LINE('NO DATA FOUND!!!');
    WHEN TOO_MANY_ROWS THEN
        DBMS_OUTPUT.PUT_LINE('TOO MANY ROWS FOUND!!!');
    WHEN OTHERS THEN
        DBMS_OUTPUT.PUT_LINE('OTHER ERROR!!!');
END;
/