[오라클학원,SQL학원,PLSQL학원추천◆탑크리에듀]#1. PLSQL개요,장점,작성법,간단예제

#1. PLSQL개요,장점,작성법,간단예제 

PL/SQL 개요 

1-1. PL/SQL 이란? 

 PL/SQL은 PROCEDUAL LANGUAGE/SQL의 약어로 SQL 구문을 절차적인 형태로 프로그래밍 할 수 있게 한 SQL의 확장이다. 
 PL/SQL은 BEGIN~END 사이에 PL/SQL코드를 기술하는데 DML문, 절차적 언어의 IF문과 같은 제어문, 반복문(LOOP~END LOOP) 등을 사용하여 SQL을 이용하여 프로그래밍이 가능하도록 한 것이다. 
 PL/SQL은 DECLARE로 선언되는 익명 블록, 값을 반드시 리턴하는 함수(FUNCTION), 실행만 하는 프로시저(PROCEDURE), 트리거링 이벤트에 의해 자동 실행되는 프러시저인 트리거(Trigger), 비슷한 기능을 하는 함수, 프러시저를 묶어 놓은 패키지 등이 있다. 
 익명 블럭은 오라클 서버에서 컴파일되어 SGA의 SHARED POOL에 위치시키지만 소스코드와 컴파일된 코드를 별도로 저장하지는 않는다. 
 함수나 프로시저, 패키지는 STORED PROGRAM 이라 하며 데이터베이스 SCHEMA OBJECTS로 저장되고 호출되어 실행된다. 
 PL/SQL로 작성된 구문중 절차적 언어와 같은 구문은 PROCEDUAL STATEMENT EXECUTOR에서 실행하고 SQL구문은 SQL STATEMENT EXECUTOR에서 실행한다. 
 PL/SQL 프로그램은 오라클 서버에서 컴파일 되고 저장되어 호출되어 실행된다 

  


1-2. PL/SQL 장점 

 BEGIN~END와 같은 블록 구조를 가지므로 블록안의 여러 SQL 구문이 한번에 서버로 전송되므로 수행속도가 향상되고 통신량도 줄일 수 있다. 
 블록안에 또 다른 블록을 포함하는 형태로 코드를 모듈화 할 수 있다. 
 단일형 데이터 타입(SCALAR DATA TYPE), 복합데이터 타입(COMPOSITE DATA TYPE), 상수 등 여러 형태의 변수 선언이 가능하다. 
 테이블의 칼럼을 참조하는 동적 변수를 정의 할 수 있다.(%TYPE, %ROWTYPE) 
 IF문과 같은 조건문을 사용할 수 있다. 
 LOOP~END LOOP, FOR..LOOP~END LOOP, WHILE .. LOOP~END LOOP와 같은 반복문을 사용할 수 있다. 
 Exception절을 이용하여 프로그래밍 언어처럼 예외(오류)를 처리 할 수 있으며 사용자 정의 예외의 사용도 가능하다. 

  
1-3. PL/SQL 작성방법 

 명령문의 종료는 세미콜론(;) 이다. 
 CREATE 구문을 사용했다면 실행을 위해 마지막에 슬래시(‘/’)가 필요하다. 
 익명의 블록을 만들기 위해서는 DECLARE 구문으로, 이름있는 블록을 만들기 위해서는 CREATE 구문을 사용해야 한다. 
 하나의 BEGIN에 대응되는 END 구문은 하나이며 세미콜론(;)으로 끝나야 하고, END 다음에 CREATE 구문에서 사용된 함수나 프로시저의 이름을 써줘도 된다. 
 Declare로 선언된 익명블럭인 경우 컴파일시 에러가 발생하면 에러 코드/메시지를 하단에 보여주지만 CREATE로 시작하는 함수나 프러시저는 오류 발생시 SHOW ERRORS(ERROR)로 확인해야 한다. 

SQL> SHOW ERRORS 
LINE/COL              ERROR 
-------------  ---------------------------------------------------------------- 
10/13          PL/SQL: SQL Statement ignored 
11/10          PLS-00201: ‘EMP.SAL' ….. 


1-4. PL/SQL 블록 구조 

DECLARE 
    Variable declaration 
BEGIN 
    Program Execution 
…… 
EXCEPTION 
    Exception handling 
  …… 
END; 
[PL/SQL 블록 구조] 

 Declare절 : 선언부로써 필수는 아님, 변수 or 커서 등을 선언한다. 이름을 정의하고 재 사용할 PL/SQL 블록을 만들기 위해서는 CREATE구문을 사용해야 한다. 

- 변수선언예 
goodsNo  NUMBER(4); 
isLogin    BOOLEAN; 
ename    VARCHAR2(20); 

    - 커서 선언 예 
        Declare 
          Cursor cEmp is select empno, ename from emp where deptno = 10; 
Begin 
…… 
End;
/ 

 Begin ~ End절 : 필수 요소이며 절차적 언어의 특징 및 SQL을 사용하여 비즈니스 로직을 구현한다. 
 Exception절 : 예외처리절(프로그래밍 언어의 CATCH구문) 이다. 

[PL/SQL 예문] 

SQL> DROP TABLE emp_temp; 
SQL> CREATE TABLE emp_temp AS SELECT empno, sal, ename FROM emp WHERE 1 = 2; 
SQL> EDIT SAMPLE.SQL 

-- 사번이 7876인 사원부터 시작해서 관리자(MGR)를 계속 확인하면서 
-- 계층구조 위쪽으로 탐색하다가 급여가 3000보다큰 경우 반복문을 빠져 나와 
-- EMP_TEMP 테이블에 INSERT하는 예제 

--이름없는 익명 블록을 만든다. 
DECLARE 
  -- 선언부 : BEGIN절에서 사용할 변수를 선언한다. 
  V_EMPNO                EMP.EMPNO%TYPE; 
  V_SAL                      EMP.SAL%TYPE := 0; 
  V_MGR_NUM            EMP.MGR%TYPE; 
  V_ENAME                  EMP.ENAME%TYPE; 
V_STARTING_EMPNO  EMP.EMPNO%TYPE := 7876; 

-- 실행부(BEGIN~END) 
BEGIN 
  SELECT MGR INTO V_MGR_NUM 
  FROM EMP 
  WHERE EMPNO = V_STARTING_EMPNO; 
  
-- 7876부터 상위로 관리자(MGR)을 따라 위로 검색하면서 급여가 3000보다 크다면 루프를 빠져나간다. 
  WHILE V_SAL <= 3000 LOOP 
    SELECT  EMPNO,    SAL,  ENAME,    MGR 
      INTO  V_EMPNO, V_SAL, V_ENAME, V_MGR_NUM 
    FROM  EMP 
    WHERE EMPNO = V_MGR_NUM; 
  END LOOP; 
  
  INSERT INTO EMP_TEMP VALUES(V_EMPNO, V_SAL, V_ENAME); 
  
COMMIT; 

-- 예외처리부 
EXCEPTION 
  -- 오라클 서버에 사전에 정의된 예외들 
  WHEN NO_DATA_FOUND THEN 
    INSERT INTO EMP_TEMP VALUES (NULL,NULL,'NOT FOUND'); 
    COMMIT; 
  WHEN TOO_MANY_ROWS THEN 
    INSERT INTO EMP_TEMP VALUES (NULL,NULL,'TOO MANY ROWS'); 
    COMMIT; 
END; 
/  

SQL>@SAMPLE 
SQL>SELECT * FROM EMP_TEMP;