PLSQL01_기초

PL/SQL

절차적 언어(PL)란?

• 처리방법과 순서가 있는 언어. C, Java 등이 절차적 언어이다.
• PLSQL은 SQL에 내가 필요한 것을 정의하여 동작하도록 하게 함.(SQL + PL기능)

구조화된 언어

• DECLARE과 EXCEPTION은 필수가 아니지만 BEGIN과 END는 반드시 있어야함.
• DECLARE ( 선언부 )
• BEGIN ( 실행부 )
• EXCEPTION ( 예외처리 )
• END;

PL 주요 특징

1. 변수, 상수 정의
2. 제어구문 사용가능(if문, loop문, go to문)
3. 예외처리
4. 모듈화

1. 변수선언이 가능하다.

• 대입연산자( := )
• 변수를 정의하고 초기화하지 않으면 해당 변수를 NULL로 취급한다!

DECLARE
  V_EMPNO   NUMBER(4)     :=8888;   --변수선언 및 초기화
  V_DEPTNO  NUMBER(2);
  V_ENAME   VARCHAR2(10)  :='XMAN'; --변수선언 및 초기화
  V_JOB     VARCHAR2(9);
  V_SAL     NUMBER(7,2);
BEGIN

• CURSOR : IS이하의 SELECT문장만 올 수 있다.

DECLARE
  CURSOR CUR_EXP IS
        SELECT EMPNO, JOB, SAL, COMM, FROM EMP WHERE DEPTNO = 10;
  V_ENAME   VARCHAR2(10)  :='XMAN'; --변수선언 및 초기화
  V_JOB     VARCHAR2(9);
  V_SAL     NUMBER(7,2);
  V_COMM    NUMBER(7,2);
BEGIN

2. 제어구문 사용가능(if문, loop문, go to문)

• PLSQL은 :=이 대입연산자, =는 비교연산자이다.

IF V_JOB IS NULL THEN
  V_JOB :='신입';
END IF;
IF V_JOB = '신입' THEN 
  V_SAL := 2000;
ELSIF V_JOB IN ('MANAGER', 'ANALYST') THEN
  V_SAL := 3500;
ELSE
  V_SAL := 2500;
END IF;

WHILE(V_INDEX >= 0)
LOOP
  DBMS_OUTPUT.PUT_LINE(' WHILE LOOP ['||TO_CHAR(V_INDEX)||']');
  -- DBMS_OUPUT : 패키지
  V_INDEX := V_INDEX -1
 END LOOP;

3. 예외처리

<<Nested_BLOCK_1>>
BEGIN
  INSERT INTO DEPT VALUES(76,'LOCAL_PART_1', 'Nested_Blk1');
  COMMIT;
--에서 Run Time Error발생시 아래에서 예외처리(에러처리) 수행

EXCEPTION
  WHEN NO_DATA_FOUND THEN
    NULL;
  WHEN OTHERS THEN
    NULL;
END Nested_BLOCK_1;

4. 모듈화

• 사용자정의 함수, 라이브러리화 하여 재사용
• 블럭안에 블럭을 사용하는 중첩BLOCK 가능( 모듈성을 높일 수 있다. )
• 사용자 정의 프로시져, 함수, 패키지, 트리거 등을 생성할 수 있다.

DECLARE
  V_EMPNO   NUMBER(4)   :=0;
  V_ENAME   VARCHAR2(10);
  V_DEPTNO  NUMBER(2);
BEGIN
  V_EMPNO :=7778;
  V_ENAME :='PL/SQL';
  INSERT INTO EMP(EMPNO, DEPTNO, ENAME) VALUES(V_EMPNO, V_DEPTNO, V_ENAME);
  
  COMMIT;
EXCEPTION
  WHEN OTHERS THEN
    DBMS_OUTPUT.PUT_LINE('INSERT ERR : '||SQLERRM);
    ROLLBACK;
END;

기존 SQL과 차이점

• SQL문장이 움직이는 SQL과는 다르게 PLSQL은 코드블럭단위로 움직인다.
• 클라이언트에서 DBMS서버로 PLSQL블럭을 통째로 전송.
• 블럭이 통째로 날라가는게 왜 좋을까?
  • 클라이언트와 서버간의 통신이 라인별로 발생하면 수십번을 왔다갔다 하게 된다.
  • 블럭으로 전송하게 되면 한 번에 배달하는 것과 같아서 효율적이다.

PLSQL 장점

1. 이식성
2. 통합성
3. 성능

PLSQL블럭 종류

• ANONYMOUS BLOCK : 이름이 없는 블록
  • Client App, SQL Script내에 저장
  • ProC
• NAMED BLOCK (= STORED BLOCK ) : 이름이 있는 블록
  • DBMS내에 저장.
  • Function/Procedure/Package/Trigger
• 빈번한 Context Switch는 성능저하의 원인이 된다.
• Java와 SQL간 Context Switch(문맥교환)보다 PL과 SQL의 문맥교환이 더 효율적.

별도의 디버깅 도구를 지원하지 않음.

• DBMS_OUTPUT_1.SQL

REM DEFAULT SIZE 20000 BYTES.
SET SERVEROUTPUT ON
BEGIN
  FOR I IN 1..10
  LOOP
    DBMS_OUTPUT.PUT_LINE('[ '||TO_CHAR(I)||' ] PROCESSED');
END;
/
  SET SERVEROUTPUT OFF
  BEGIN
    DBMS_OUTPUT.PUT_LINE('OK...');
END;
/

• DBMS_OUTPUT_2.SQL

REM --------------------------------------------------
REM 출력 버퍼의 크기 조절
REM     - DEFAULT SIZE 20000 BYTES
REM     - MAX SIZE UNLIMITED
REM --------------------------------------------------

SET SERVEROUTPUT ON SIZE 2000
BEGIN
  FOR I IN 1..40
  LOOP
    DBMS_OUTPUT.PUT_LINE('[ '||TO_CHAR(I)||' ] '||'123456789012345890123456789012345678901234567890123456789001234567890');
    END LOOP;
END;
/

• 1코드 실행 후, 2코드를 실행하면 아래와 같은 결과를 확인할 수 있음

오류 보고 -
ORA-20000: ORU-10027: buffer overflow, limit of 2000 bytes
ORA-06512: "SYS.DBMS_OUTPUT",  32행
ORA-06512: "SYS.DBMS_OUTPUT",  97행
ORA-06512: "SYS.DBMS_OUTPUT",  112행
ORA-06512:  4행
20000. 00000 -  "%s"
*Cause:    The stored procedure 'raise_application_error'
           was called which causes this error to be generated.
*Action:   Correct the problem as described in the error message or contact
           the application administrator or DBA for more information.

출력버퍼

• PLSQL 수행 후, 화면에 보여줌.
• DBMS_OUTPUT을 사용할 때 버퍼의 길이
• 라인의 출력 길이

  • 10G R2 이전	10G R2 이후
    255 Bytes	32767 Bytes

• 출력버퍼크기

  • 10G R2 이전	10G R2 이후
    1000000 Bytes	Unlimited