PL/SQL 시작하기

PL/SQL - Procedural Language/SQL 

PL/SQL 의 런타임 구조

PL/SQL 실행 시 실행 원리

PL/SQL 기본 구조

PL/SQL 은 기본적으로 블록(BLOCK) 구조를 가지고 있다.

블록의 기본 구성은 선언부(DECLARE), 실행부(BEGIN), 예외처리부(EXCEPTION)로 구성

그 블록 안에 블록을 포함시킬 수 있는 데 포함된 블록을 Nested Block(중첩 블록) 이라고 부름

블록의 유형에는 크게 Anonymous PL/SQL Block(익명블록) 과 Stored PL/SQL Block(저장된 블록) 이 있다.

익명블록 = 일회성으로 사용할 경우

저장된 블록 = 서버에 파싱해서 자장해 놓고 주기적으로 반복해서 사용할 경우

DECLARE(선언부)	모든 변수나 상수를 선언하는 부분
BEGIN(실행부)	제어문, 반복문, 함수정의 등의 로직을 기술함 (실행)
EXCEPTION(예외처리부)	실행 도중 에러 발생시 해결하는 문장들을 기술

예외 처리부는 경우에 따라 생략 가능 / 단 실행부는 반드시 있어야 함

※ PL/SQL은 기본적으로 처리된 문장을 화면에 출력하지 않는다.

그래서 화면에 출력하기 위해서 아래와 같은 사전작업이 필요

SQL>SET SERVEROUTPUT ON ;      

오류 내용을 보고 싶을 경우

SQL>SET ERRORS ;

PL/SQL 기본 실행 예 )

emp 테이블에서 empno 가 7900 번인 사원의 사번과 이름을 화면에 출력하시오

SCOTT>DECLARE

v_ename emp.ename%TYPE ;

BEGIN

SELECT ename into v_ename

FROM emp

WHERE empno=&empno ;

DBMS_OUTPUT.PUT_LINE ('사원명은'||v_ename||'입니다') ;

EXCEPTION

WHEN NO_DATA_FOUND THEN

DBMS_OUTPUT.PUT_LINE(' 해당 사원이 없습니다 ') ;

WHEN TOO_MANY_ROWS THEN

DBMS_OUTPUT.PUT_LINE(' 사원이 두 명 이상입니다 ') ;

END ;

/

Enter value for empno : 7900

-> 사원명은 JAMES 입니다

Enter value for empno : 8000

-> 해당 사원이 없습니다

1 > PL/SQL 문 내에서의 SQL 문장 사용하기

- END 키워드는 트랜잭션의 끝이 아니라 PL/SQL 블록의 끝을 나타냄

- PL/SQL 은 DDL 문을 직접 지원하지 않는다

- PL/SQL 은 GRANT 또는 REVOKE 와 같은 DCL 문을 직접 지원하지 않는다

1. PL/SQL 내에서의 SELECT 문장 사용하기

문법 : 

SELECT select_list

INTO {variable_name[, variable_name]...| record_name}

FROM table

[WHERE condition] ;

사용 예 1) Professor 테이블에서 교수번호가 1001 번인 교수의 교수번호와 급여를 조회 한 후 변수에 저장해서 출력

SCOTT> DECLARE

2    v_profno professor.profno%TYPE ;

3    v_pay     professor.pay%TYPE ;

4    BEGIN

5    SELECT profno, pay, INTO v_profno, v_pay

6    FROM professor

7    WHERE profno=1001 ;

8    DBMS_OUTPUT.PUT_LINE (v_profno||' 번 교수의 급여는 '||v_pay||' 입니다') ;

9    END ;

10    /

-> 1001 번 교수의 급여는 550 입니다

사용 예 2)emp2 테이블을 사용하여 사원번호를 입력 받아서 사원의 사번과 이름, 생일을 출력하세요.

SCOTT> DECLARE

2    v_empno emp2.empno%TYPE ;

3    v_name emp2.name%TYPE ;

4    v_birth emp2.birthday%TYPE ;

5    BEGIN

6    SELECT empno,name,birthday INTO v_empno,v_name,v_birth

7    FROM emp2

8    WHERE empno=&empno;

9    DBMS_OUTPUT.PUT_LINE (v_empno||' '||v_name||' '||v_birth) ;

10   END ;

11    /

ENTER value for empnno : 20000102

-> 20000102 김설악 22-MAR-83

사용자에게 값을 입력 받아서 변수에 할당 할 때 사용하는 기호 = &(엠퍼센트)

2 > PL/SQL 내에서의 DML 문장 사용하기

INSERT , UPDATE, DELETE, MERGE 문장을 이용하여 PL/SQL 블록 내에서 데이터를 변경 할 수 있다.

(1) INSERT 문장 수행하기 예 1 :

- 이 작업을 하기 위해 테스트용 테이블 pl_test 와 시퀀스를 아래와 같이 먼저 생성

SCOTT>CREATE TABLE pl_test

2    (no number,

3     name varchar2(10)) ;

SCOTT>CREATE SEQUENCE pl_seq ;

- PL/SQL 에서 INSERT 를 수행

SCOTT>BEGIN 

2    INSERT INTO pl_test

3    VALUES(pl_seq.NEXTVAL,'AAA') ;

4    END ;   

5    /

PL/SQL procedure successfully completed.

SCOTT>/

PL/SQL procedure successfully completed.

SCOTT>SELECT * FROM pl_test ;

NO   NAME 

---  -----

   1    AAA

   2    AAA

SCOTT>commit ;

Commit complete

(2) INSERT 문장 수행하기 예 2 :

- 작업을 수행하기 전에 pl_test2 테이블 생성

SCOTT>CREATE TABLE pl_Test2

2    (no number,

3     name varchar2(10),

4     addr varchar2(10) ) ;

사용자로부터 번호(no) , 이름(name), 주소(addr) 값을 입력 받은 후 pl_test2 테이블에 입력하는 PL/SQL 문장을 작성

SCOTT>SET VERIFY OFF

SCOTT>DECLARE

2    v_no number :='&no' ;

3    v_name varchar2(10) := '&name' ;

4    v_addr varchar2(10) := '&addr' ;

5    BEGIN

6    INSERT INTO pl_test2 VALUES(v_no, v_name, v_addr) ;

7    END ;

8    /

Enter value for no : 10

Enter value for name : AAA

Enter value for addr : 서울

SCOTT>SELECT * FROM pl_test2 ;

NO    NAME    ADDR

---   -----   ------

  10    AAA       서울

(3) PL/SQL 에서 UPDATE 수행

SCOTT>BEGIN

2    UPDATE pl_test

3    SET name='BBB'

4    WHERE no = 2 ;

5    END ;

6    /

SCOTT>SELECT * FROM pl_test ;

NO    NAME

---   -----

   1    AAA   

   2    BBB

SCOTT>commit ;

(4) PL/SQL 에서 DELETE 수행

SCOTT>BEGIN

2    DELETE FROM pl_test

3    WHERE no=1 ;

4    END ;

5    /

SCOTT>SELECT * FROM pl_test ;

NO    NAME

---   -----

   2    BBB

SCOTT>commit ;

(5) PL/SQL 에서 MERGE 작업 수행

- 작업을 수행하기 위해 연습용 테이블 pl_merge1 과 pl_merge2 를 생성

SCOTT>CREATE TABLE pl_merge1

2    (no number,

3     name varchar2(10) ) ;

SCOTT>CREATE TABLE pl_merge2

2    AS SELECT * FROM pl_merge1 ;

SCOTT>INSERT INTO pl_merge1 VALUES(1,'AAA') ;

SCOTT>INSERT INTO pl_merge1 VALUES(2,'BBB') ;

SCOTT>INSERT INTO pl_merge2 VALUES(1,'CCC') ;

SCOTT>INSERT INTO pl_merge2 VALUES(3,'DDD') ;

SCOTT>commit ;

SCOTT>SELECT * FROM pl_merge1 ;

NO    NAME

---   -----

   1     AAA

   2     BBB

SCOTT>SELECT * FROM pl_merge2 ;

NO    NAME

---   -----

   1     CCC

   3     DDD

SCOTT> BEGIN

2    MERGE INTO pl_merge2 m2

3    USING pl_merge1 m1

4    ON(m1.no = m2.no)

5    WHEN MATCHED THEN

6    UPDATE SET

7    m2.name = m1.name

8    WHEN NOT MATCHED THEN

9    INSERT VALUES(m1.no , m1.name) ;

10   END ;

11    /

SCOTT>SELECT * FROM pl_merge1 ;

NO    NAME

---   -----

   1     AAA

   3     DDD

   2     BBB     <- 이줄 추가되어 있음

PL/SQL 에서의 렉시칼 (Lexical)

렉시칼 이란 PL/SQL 안에 사용되는 문자들을 의미하며 식별자, 구분자, 리터럴, 주석등으로 구성

식별자 - PL/SQL 객체에게 부여되는 이름 (테이블 이름이나 변수명 등)

아래와 같은 경우 식별자를 쌍따옴표로 묶어서 사용할 수 있다

- 식별자의 대소문자 구분이 필요한 경우

- 공백과 같은 문자를 포함할 경우

- 예약어를 사용해야 할 경우

구분자 - 특별한 의미를 지닌 기호, 예를 들어 SQL 문장을 끝낼 때는 끝내는 의미를 가진 ; 을 사용

리터럴 - 변수에 할당되는 모든 값, 식별자가 아닌 모든 문자, 숫자, 부울 또는 날짜 값

- 문자 리터럴 : 모든 문자열 리터럴은 데이터 유형이CHAR 또는 VARCHAR2 이므로 문자 리터럴이라고 함

- 숫자 리터럴 : 정수 또는 실수 값을 나타냄

- 부울 리터럴 : 부울 변수에 할당된 값, TRUE, FALSE 및 NULL 은 부울 리터럴이거나 키워드

- 주석 : 코드의 설명이나 해설등을 기록해 두는 것

PL/SQL 에서의 사용되는 주요 연산자들

PL/SQL 에서 블록 구문 작성시 주의사항

1) 문자 리터럴이나 날짜 리터럴 사용시 반드시 홀 따옴표로 묶어서 표시

2) 문장에서의 주석은 한 줄일 경우 --(하이픈 두개) 를 써서 표시하고 여러 줄 일 경우 /* */ 기호를 사용해서 표시

3) 프로시져 내에서는 단일행 함수만을 사용해야 하며 DECODE 함수나 그룹함수는 사용할 수 없음

4) 데이터의 형 변환에 주의