PLSQL01_기초

2 minute read



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