PL(Procedural Language)/SQL

• PL(Procedural Language)

-PL/SQL은 ORACLE의 형식으로 다른 DB는 다른 DB의 방식이 있다.

• 다른 DB와 ORACLE DB가 다른점

-ORACLE : PL/SQL을 DB Server에 한번에 보내고 Result를 한번에 받아옴(Traffic이 적음)

(SQL 처리 영역과 PL/SQL 처리영역이 따로 있다)

-다른 DB Local에서 Interprete해서 계속 통신하면서 DB Server에 던져줘야 함(Traffic이 많음)

(보통 그래서 DB가 아니라 Server에서 처리함 - C /JAVA 등으로 만든)

• PL/SQL Block의 구조

-전체 Block 내부에 SubBlock도 가능(전역변수와 지역변수 유사)

-선언부(Declare Section)은 Block의 다른 모든 부분에서 액세스할 수 있는 모든 변수, 상수, Exception, Cursor등을 선언하는 곳이다.

-Procedure Body(Procedure Body)에는 Block에 대해 실행 가능한 명령이 포함된다. PL/SQL Block의 처리 기능을 기술하는 곳이다.

 -예외 핸들링 섹션(Exception Handler Section)에는 Block에 대한 예외 핸들러가 포함된다.

• 변수의 형태(Declare) 

1.NUMBER

2.VARCHAR2

3.DATE

4.BOOLEAN

5.%TYPE          >> table.column%type, variable%type  - table의 변수형을, 명시한 colume이 따르게 함

6.%ROWTYPE  >> table%rowtype - 명시한 table의 변수형을 적용되는 colume이 따르게 함

7.ROWID          >>  char(18)의 sub-type

※ Table명과 RowId는 같아도 무방하다 >> SQL 처리영역과 PLSQL 처리영역이 다르므로

• 변수 초기화 형태

변수명 [constant] 변수형태 [not null] [:= 초기값(식)] ;

• 서브 타입을 이용한 변수 선언

DECLARE

  varchar2_50 VARCHAR2(50);      -- 제한된 변수 선언

  SUBTYPE s1 IS varchar2_50;     -- 서브타입 선언

/* 서브타입 이용 변수 선언 */

  current_desc s1 DEFAULT 'Unknown';

• 사용자 정의 복합형 변수 선언

1. Record >> 필드를 묶음으로 선언

CREATE OR REPLACE PROCEDURE Record_Test

( p_empno IN emp.empno%TYPE )

IS

-- 하나의 레코드의 세가지의 변수타입 선언

TYPE emp_record IS RECORD

(v_empno NUMBER,

v_ename VARCHAR2(30),

v_hiredate DATE );

emp_rec emp_record ;

BEGIN

DBMS_OUTPUT.ENABLE;

-- 레코드의 사용

SELECT empno, ename, hiredate

INTO emp_rec.v_empno, emp_rec.v_ename, emp_rec.v_hiredate

FROM emp

WHERE empno = p_empno;

DBMS_OUTPUT.PUT_LINE( '사원번호 : ' || emp_rec.v_empno );

DBMS_OUTPUT.PUT_LINE( '사원이름 : ' || emp_rec.v_ename );

DBMS_OUTPUT.PUT_LINE( '입 사 일 : ' || emp_rec.v_hiredate);

END;

참조 : http://www.gurubee.net/lecture/1048

2. Table >> 행 크기가  유동적임(데이터가 들어오면 자동으로 증가)

3. VArray >> 행 크기를 고정적으로 하여, 속도가 빠름

• Cursor
  대상이 되는 데이타베이스 데이타를 일시적으로 저장하는 기능을 갖는 메모리상의 buffer

                BEGIN

                  OPEN <cursor_name> [(parameter_value,......)];

                (Open될 때, Select문이 수행되어 cursor에 그 데이타가 보관된다.)

                  ......;

                  FETCH <cursor_name> INTO <variable1>, <variable2>,.....;

                (Cursor로부터 1개의 record을 읽어 정해진 변수에 그 값을 넣는 작업이다.)

                  ......;

                  CLOSE <cursor_name>;

                END;

• 동적인 SQL

고정된 SQL은 변수가 정해져 있지만, Dynamic SQL은 필요한 변수를 기준으로 값을 가지고 옴

• Exception

Exception 절이 없어도 실행은 되나, 발생 시 안쓰면 처리/확인 X

후행작업과 충돌방지

• Trigger

-Trigger가 걸린 colume의 변경이 commit/rollback 될때 실행

-Table이 수정되면 걸려있는 Trigger가 Disable로 됨 > Table 수정후에는 Enable로

-Summary Table의 갱신에 용이

ex) Trigger의 실제 사용예

월 매출 실적 > 출하를 집계하는 경우

경우의 수가 많아지면 유지보수가 어려워 진다(변경 적용시 누락)

중첩이 3중이 넘어가면 성능이 기하급수적으로 감소

>> 추상화하고 단순화하여서, 규칙성을 찾아서 3~4가지 안으로 줄여서 함

ex) 컬럼수 X 3가지(INSERT, DELETE, UPDATE) = 10가지 이상의 경우의 수 

ex) INSERT  > NEW:+

      DELETE  > OLD:-

      UPDATE > OLD:- / NEW :+

ex) if(delete or update){

    OLD - 

}

if(insert or update){

    NEW + 

}

※외부 라이브러리를 불러들여서 쓸 수 있으나(C로 짠 센서 프로그램), 반대의 경우가 더 많음