PL/SQL

1. PL/SQL (Procedural Language Extenstion to SQL)

• SQL을 확장한 절차적 언어(Procedural Language)
• 관계형 데이터베이스에서 사용되는 Oracle의 표준 데이터 엑세스 언어로, 프로시저 생성자를 SQL과 완벽하게 통합한다.
• 유저 프로세스가 PL/SQL 블록을 보내면, 서버 프로세서는 PL/SQL Engine에서 해당 블록을 받고
• SQL과 Procedural을 나눠서 SQL은 SQL Statement Executer로 보낸다.
• PL/SQL 프로그램의 종류는 크게 Procedure, Function, Trigger로 나뉘어진다.

 

2. PL/SQL의 특징

• 블록 단위의 실행을 제공한다. 이를 위해 BEGIN 과 END를 사용한다. 마지막 라인에 /를 입력하면 해당 블록이 실행된다.
• 변수의 선언은 DECLARE절에서만 가능하다. 그리고 BEGIN 섹션에서 새 값이 할당될 수 있다.
• IF문 조건
• LOOP문 반복
• 커서를 사용하여 여러 행을 검색 및 처리

 

3. 기본 PL/SQL Block 구조

영역	설명	옵션/필수
DECLARE(선언부)	PL/SQL에서 사용하는 모든 변수나 상수를 선언하는 부분으로서 DECLARE로 시작	옵션
BEGIN(실행부)	절차적 형식으로 SQL문을 실행할 수 있도록 절차적 언어의 요소인 제어문, 반복문, 함수 정의 등 로직을 기술할 수 있는 부분이며 BEGIN으로 시작	필수
EXCEPTION(예외 처리부)	PL/SQL 문이 실행되는 중에 에러가 발생할 수 있는데 이를 예외사항이라고 한다.	옵션
END(실행문 종료)		필수

 

4. PL/SQL Block의 종류

1) 익명 블록
2) 이름 있는 블록 : DB의 객체로 저장되는 블록

• 프로시저 : 리턴 값을 하나 이상 가질 수 있는 프로그램
• 함수 : 리턴 값을 반드시 반환해야 하는 프로그램
• 패키지 : 하나 이상의 프로시저, 함수, 변수, 예외 등의 묶음
• 트리거 : 지정된 이벤트가 발생하면 자동으로 실행되는 PL/SQL 블록 

* 오라클에서 화면 출력을 위해 PUT_LINE이라는 프로시저 사용*

 

5. 변수 선언

블록 내에서 변수를 사용하려면 선언부(DECLARE)에서 선언해야하며, 변수명 다음에 데이터 타입을 기술해야 한다.

문법 : identifier [CONSTANT] datatype [NOT NULL] [:=|DEFAULT expression];

• identifier : 변수명(식별자)
• CONSTANT : 상수로 지정(초기치를 반드시 할당해야 함)
• datatype : 자료형
• expression : Literal, 다른 변수, 연산자나 함수를 포함하는 표현식

ex)

DECLARE NAME VARCHAR2(10) DEFAULT '기본값';

DECLARE
     NAME     VARCHAR2(20);
     AGE        NUMBER(2);
    GENDER VARCHAR(50) DEFAULT '남';

DECLARE NAME VARCHAR2(20) := '이효리';
BEGIN
        DBMS_OUTPUT.put_Line('이효리'||NAME);
END

 

%ROWTYPE

• 해당 테이블이나 뷰의 컬럼 속성을 그대로 들고 오는 형태이다.
• 사용 방법 : 변수명 테이블이름%ROWTYPE

DECLARE
        DATA EMP%ROWTYPE;
BEGIN
         SELECT * INTO DATA
         FROM EMP
         WHERE EMPNO = '1234'
         DBMS_OUTPUT.PUT_LINE(DATA.ENAME||','||'DATA.DEPTNO);
END;

 

%TYPE

• 해당 테이블의 컬럼 속성을 지정하여 그대로 들고 오는 형태이다.
• 사용방법 : 변수명 테이블이름. 컬럼명 %TYPE

DECLARE
 V_ENAME EMP.ENAME%TYPE;
 V_DEPTNO EMP.DEPTNO%TYPE;
BEGIN
 SELECT ENAME, DEPTNO INTO V_ENAME, V_DEPTNO
 FROM EMP
 WHERE EMPNO = '1234';
 DBMS_OUTPUT.PUT_LINE(V_ENAME||','||V_DEPTNO);
END;