1. PL/SQL 이란?
PL/SQL 이란 Procedural Language/SQL 의 약자로 절차적인 기능을 기본적으로 가지는 프로그래밍 언어로
데이터 트랜잭션 처리 능력이나 정보보호, 데이터에 대한 보안, 예외처리 기능, 객체 지향 등 데이터 베이스와 관련된 중요한 기능을 지원하는
데이터베이스 업무를 처리하기에 최적화된 언어이다.
2. PL/SQL 의 런타임 구조
PL/SQL 엔진이 SQL 을 발견하게 되면 컨텍스트 변환 과정을 거친 후 오라클 서버 프로세스에 전달되고
오라클 서버 프로세스가 SQL문장 수행과정 (Parse → Bind → Execute → Fetch (SELECT 인 경우만)) 을 거쳐서
쿼리를 수행한 후 값을 리턴해야 PL/SQL 나머지 부분이 실행된다.
※ PL/SQL 이 빨리 실행되기 위해서는 SQL 문장이 빨라야 한다.
PL/SQL 엔진 데이터베이스
PL/SQL 블록 PL/SQL 블록
[SQL 구문] ↔ [변수 1] ↔ [SQL 구문]
[변수 2]
3. PL/SQL 기본 구조
블록의 기본적인 구성
선언부 (DECLARE), 실행부 (BEGIN), 예외처리부 (EXCEPTION)
블록 안의 블록 Nested Block (중첩 블록)
블록의 유형
Anonymous PL/SQL Block (익명 블록)
일회성으로 사용
Stored PL/SQL Block (저장된 블록)
서버에 파싱해서 저장해 놓고 주기적으로 사용
서버 프로그램, 프로그램 단위라고도 하며, 스키마를 구성하는 오브젝트로 파싱된 후 오라클 서버 내부에 저장되거나 오라클 툴 안에 라이브러리 형태로 저장됨
4. PL/SQL BLOCK 기본 구성
DECLARE (선언부) 모든 변수나 상수를 선언하는 부분
EXECUTABLE (실행부) 제어문, 반복문, 함수 정의 등의 로직을 기술
EXCEPTION (예외 처리부) 실행 도중 에러 발생시 해결하는 문장들을 기술
선언부와 예외처리부는 경우에 따라 생략가능 하지만 실행부는 반드시 있어야 한다.
DECLARE, BEGIN, EXCEPTION 과 같은 예약어들은 ;(세미콜론) 으로 끝나지 않지만
나머지 명령어들은 ;(세미콜론) 으로 끝난다.
화면 출력 기능 활성화
SET SERVEROUTPUT ON ;
에러 출력
SHOW ERRORS ;
예1. EMP 테이블에서 EMPNO 가 7900 인 사원의 사번과 이름을 화면에 출력하는 PL/SQL 문장
DECLARE ← 선언부 시작
vno NUMBER(4) ;
vname VARCHAR2(10) ; ← 데이터베이스에서 수행된 결과 값을 저장할 변수 두개 선언
BEGIN ← 실행부 시작
SELECT empno, ename INTO vno, vname ← 데이터베이스에서 수행된 결과 값을 변수에 저장
FROM emp
WHERE empno=7900 ;
DBMS_OUTPUT.PUT_LINE(vno||' '||vname) ; ← 두 변수에 저장된 값을 출력
END ; ←실행부 종료
/ ← 작성된 PL/SQL 블록을 실행시킨다.
5. PL/SQL 블록 작성시 기본 규칙과 권장 사항
* 문장은 여러줄에 걸쳐칠 수 있으나, 키워드는 분리될 수 없다.
* 블록의 내용을 읽기 쉽도록 공백 문자를 사용하여 키워드 내지는 문장을 적절하게 분리함으로써 의미 분석이 되도록 하며, 들여쓰기도 권장한다.
* 예약어는 식별자명으로 사용될 수 없으나, Alias 로는 사용될 수 있다. 즉, 이중부호(")를 함께 사용할 수 있다는 뜻이다.
* 식별자 명은 기본 오라클 Naming Rule 을 준수한다.
* 리터럴 (문자, 날짜) 은 단일 인용부호(') 로 표시해야 하며 Null 값은 Null 상수로 기술한다.
* 주석 처리를 하고자 할 경우 단일행은 -- (하이픈 두개), 복수행은 /* 로 시작하고 */ 로 끝낸다.
* PL/SQL 블록 내의 명령(수식) 에서는 오라클 함수를 사용할 수 있으나 그룹 함수와 DECODE 함수는 SQL 문장에 포함되어야만 사용될 수 있다.
만약 다른 경우에 그룹함수와 DECODE 함수를 사용할 경우 에러가 발생하며 PL/SQL 에서는 에러 (EXCEPTION) 처리와 관련된 함수에는 SQLCODE 함수와
SQLERRM 함수가 별도로 존재한다.
6. PL/SQL 문 내에서의 SQL 문장 사용하기
SQL 내에서도 DML 명령을 사용하여 데이터베이스 테이블에서 데이터를 수정할 수 있다.
PL/SQL 블록에서 DML 문과 TCL문 (COMMIT, ROLLBACK) 을 사용할 경우 주의할 사항이 있다.
* END 키워드는 트랙잭션의 끝이 아니라 PL/SQL 블록의 끝을 나타낸다.
* PL/SQL 은 DDL (데이터 정의어) 문을 직접 지원하지 않는다.
DDL 문은 동적 SQL 문으로 런타임에 문자열로 작성되며 파라미터의 위치 표시자를 포함할 수 있다.
동적 SQL 을 사용하면 PL/SQL 에서 DDL 문을 실행할 수 있다.
* PL/SQL 은 GRANT 또는 REVOKE 와 같은 DCL (데이터 제어어) 문을 직접 지원하지 않는다.
그러나 앞의 DDL 문과 마찬가지로 동적 SQL 을 사용하여 DCL 문을 실행할 수 있다.
(1) PL/SQL 내에서의 SELECT 문장 사용하기
문법
SELECT select_list
INTO {variable_name[, variable_name]... | record_name}
FROM table
[WHERE condition];
위 문법을 사용하여 PL/SQL 에서 데이터를 조회한 후 INTO 절에 있는 변수에 조회된 데이터를 저장할 수 있다.
그래서 SELECT List 항목과 INTO 절의 변수 개수와 데이터 타입이 동일해야만 한다.
사용 예1. professor 테이블에서 교수번호가 1001 번인 교수의 교수번호와 급여를 조회한 후 변수에 저장해서 화면에 출력하세요.
DECLARE
v_profno professor.profno%TYPE ;
v_pay professor.pay%TYPE ;
BEGIN
SELECT profno, pay INTO v_profno, v_pay
FROM professor
WHERE profno = 1001 ;
DBMS_OUTPUT.PUT_LINE(v_profno||' 번 교수의 급여는 '||v_pay||' 입니다. ') ;
END ;
/
사용 예2. emp2 테이블을 사용하여 사원번호를 입력 받아서 사원의 사번과 이름, 생일을 출력하세요.
DECLARE
v_empno emp2.empno%TYPE ;
v_name emp2.name%TYPE ;
v_birth emp2.birthday%TYPE ;
BEGIN
SELECT empno, name, birthday
INTO v_empno, v_name, v_birth
FROM emp2
WHERE empno = '&empno' ;
DBMS_OUTPUT.PUT_LINE(v_empno||' '||v_name||' '||v_birth) ;
END ;
/
* 사용자에게 값을 입력받아서 변수에 할당 할 때는 '&(앰퍼샌트)' 기호를 사용하면 된다.
사용 예3. 교수번호를 입력 받은 후 professor 테이블을 조회하여 해당 교수의 교수번호와 이름, 부서번호, 입사일을 출력하세요.
DECLARE
v_profno professor.profno%TYPE ;
v_name professor.name%TYPE ;
v_deptno professor.deptno%TYPE ;
v_hdate professor.hiredate%TYPE ;
BEGIN
SELECT profno, name, deptno, hiredate
INTO v_profno, v_name, v_deptno, v_hdate
FROM professor
WHERE profno = '&교수번호' ;
DBMS_OUTPUT.PUT_LINE(v_profno||' '||v_name||' '||v_deptno||' '||v_hdate) ;
END ;
/
(2) PL/SQL 내에서의 DML 문장 사용하기
INSERT, UPDATE, DELETE, MERGE 문장을 이용하여 PL/SQL 블록 내에서 데이터를 변경할 수 있다.
1) INSERT 문장 수행하기 1
테스트용 테이블, 시퀀스 생성
CREATE TABLE pl_test
(
no number,
name varchar2(10)
) ;
CREATE SEQUENCE pl_seq ;
INSERT 수행
BEGIN
INSERT INTO pl_test
VALUES(pl_seq.NEXTVAL, 'AAA') ;
END ;
/
/
SELECT *
FROM pl_test ;
COMMIT ;
2) INSERT 문장 수행하기 2
테스트용 테이블 생성
CREATE TABLE pl_test2
(
no number,
name varchar2(10),
addr varchar2(10)
) ;
사용자로부터 번호, 이름, 주소 값을 입력받은 후 pl_test2 테이블에 입력하는 PL/SQL 문장을 작성하세요.
SET VERIFY OFF
DECLARE
v_no number := '&no' ;
v_name varchar2(10 ) := '&name' ;
v_addr varchar2(10) := '&addr' ;
BEGIN
INSERT INTO pl_test2
VALUES(v_no, v_name, v_addr) ;
END ;
/
SELECT *
FROM pl_test2 ;
COMMIT ;
PL/SQL 에서 UPDATE 를 수행합니다.
BEGIN
UPDATE pl_test2
SET name='BBB'
WHERE no = 2 ;
END ;
/
SELECT *
FROM pl_test2 ;
COMMIT ;
PL/SQL 에서 DELETE 를 수행합니다.
BEGIN
DELETE FROM pl_test2
WHERE no = 1 ;
END ;
/
SELECT *
FROM pl_test2 ;
COMMIT ;
PL/SQL 에서 MERGE 작업을 수행합니다.
연습용 테이블을 생성합니다.
CREATE TABLE pl_merge1
(
no number,
name varchar2(10)
) ;
CREATE TABLE pl_merge2
AS SELECT * FROM pl_merge1 ;
INSERT INTO pl_merge1 VALUES (1, 'AAA');
INSERT INTO pl_merge1 VALUES (2, 'BBB');
INSERT INTO pl_merge2 VALUES (1, 'CCC');
INSERT INTO pl_merge2 VALUES (3, 'DDD');
COMMIT ;
SELECT *
FROM pl_merge1 ;
SELECT *
FROM pl_merge2 ;
두 테이블을 서로 비교하여 MERGE 작업을 수행한다.
BEGIN
MERGE INTO pl_merge2 m2
USING pl_merge1 m1
ON(m1.no = m2.no)
WHEN MATCHED THEN
UPDATE SET
m2.name = m1.name
WHEN NOT MATCHED THEN
INSERT VALUES (m1.no, m1.name) ;
END ;
/
SELECT *
FROM pl_merge1 ;
SELECT *
FROM pl_merge2 ;
7. PL/SQL 에서의 렉시칼
렉시칼이란 일반적으로 특정 언어에 포함되는 문자 집합들을 의미한다.
PL/SQL 렉시칼이란 PL/SQL 안에 사용되는 문자 집합들을 의미하며 식별자, 구분자, 리터럴, 주석 등으로 구성된다.
(1) 식별자
식별자는 PL/SQL 객체에게 부여되는 이름이다. 즉, 테이블 이름이나 변수명 등은 모두 식별자에 해당한다.
아래와 같은 경우 따옴표로 묶어서 사용할 수 있다.
* 식별자의 대소문자 구분이 필요한 경우
* 공백과 같은 문자 포함할 경우
* 예약어를 사용해야 할 경우
(2) 구분자
구분자는 특별한 의미를 지닌 기호다.
+ 더하기 연산자
- 빼기/ 부정 연산자
* 곱하기 연산자
/ 나누가 연산자
= 등호 연산자
@ 원격 액세스 표시자
; 명령문 종료자
<> 부등호 연산자
!= 부등호 연산자
|| 연결 연산자
-- 단일 행 주석 표시자
/* 주석 시작 구분자
*/ 주석 종료 구분자
:= 할당 연산자
(3) 리터럴
식별자가 아닌 모든 문자, 숫자, 부울 또는 날짜값은 리터럴이다.
* 문자 리터럴: 모든 문자열 리터럴은 데이터 유형이 CHAR 또는 VARCHAR2 이므로 문자 리터럴이라고 한다.
* 숫자 리터럴: 숫자 리터럴은 정수 또는 실수 값을 나타낸다.
* 부울 리터럴: 부울 변수 에 할당된 값은 부울 리터럴이다. TRUE, FALSE 및 NULL
(4) 주석
프로그래밍 코드의 성능에는 영향을 주지 않지만 나중을 위해 설명이나 해설을 기록해 두는 것.
한 줄 주석은 --
여러줄 주석은 /* */
8. PL/SQL 에서의 블록 구문 작성 지침
1. 문자 리터럴이나 날짜 리터럴 사용시에는 반드시 홑따옴표로 묶어서 표시해야 한다.
2. 문장에서의 주석은 한 줄일 경우 -- (하이픈 2개), 여러 줄일 경우 /* ~ */ 기호를 사용해서 표시해야 한다.
3. 프로시저 내에서는 단일행 함수만을 사용해야 하며 DECODE 함수나 그룹 함수는 사용할 수 없다.
PL/SQL 내부에 포함되어 있는 SQL 에서는 위 함수를 쓸 수 있지만 그 외의 PL/SQL 에서는 사용할 수 없다.
4. 시퀀스를 사용할 때: 11g 이전 버전에서는 시퀀스를 사용하기 위해서는 SQL 문장을 이용하여 시퀀스를 변수에
할당한 후 해당 변수값을 사용했으나 11g 버전부터는 PL/SQL 문장에서 바로 시퀀스를 사용할 수 있게 되었다.
11g 이전 방법
DECLARE
v_seq NUMBER ;
BEGIN
SELECT s_seq.NEXTVAL INTO v_seq
FROM dual ;
END ;
/
11g 이후 방법
DECLARE
v_seq NUMBER ;
BEGIN
v_seq := s_seq.NEXTVAL ;
END ;
/
5. 데이터의 형변환에 주의해야 한다. 데이터의 형 변환은 묵시적 형 변환 (자동 형 변환) 과 명시적 형 변환 (수동 형 변환) 으로 나눌 수 있다.
이 부분은 SQL 에서의 규칙과 동일하다. 묵시적 형 변환은 문자와 숫자, 문자와 날짜를 연산할 때 발생하며 이 부분 때문에 성능에 의도하지 않게 나쁜 영향을 줄 수 있으므로 데이터 형의 일치에 항상 주의해야 한다.
9. 중첩된 PL/SQL 블록 작성하기
프로시저를 작성할 경우 블록 안에 또 다른 블록을 중첩으로 포함할 수 있다.
DECLARE
v_first VARCHAR2(5) := 'Outer' ;
BEGIN
DECLARE
v_second VARCHAR2(5) := 'Inner' ;
BEGIN
DBMS_OUTPUT.PUT_LINE(v_first) ;
DBMS_OUTPUT.PUT_LINE(v_second) ;
END ;
DBMS_OUTPUT.PUT_LINE(v_first) ;
DBMS_OUTPUT.PUT_LINE(v_second) ;
END ;
/
앞의 예는 PL/SQL 블록 안에 또 다른 PL/SQL 블록을 사용하였다.
그리고 외부 블록에 v_first 라는 변수를 사용하였고 내부 블록에 v_second 라는 변수를 사용하여 값을 할당하였다.
내부 블록에서 두 개의 변수 값을 출력시키고 (7,8번 라인) 외부 블록에서도 두개의 변수 값을 출력시켰다.(10,11번 라인)
그런데 11번 라인에서 에러 메시지가 발생하면 v_second 변수가 선언되지 않았다고 나온다.
외부 블록에서 선언한 v_first 변수는 내부 블록에서도 사용할 수 있지만 내부 블록에서 선언된 v_second 변수는 외부 블록으로 나올 수 없다는 것이다.
문제가 되는 11번 라인을 삭제 후 다시 위 문장을 실행하겠다.
DECLARE
v_first VARCHAR2(5) := 'Outer' ;
BEGIN
DECLARE
v_second VARCHAR2(5) := 'Inner' ;
BEGIN
DBMS_OUTPUT.PUT_LINE(v_first) ;
DBMS_OUTPUT.PUT_LINE(v_second) ;
END ;
DBMS_OUTPUT.PUT_LINE(v_first) ;
END ;
/
10. PL/SQL 에서의 연산자 사용하기
이 부분은 SQL에서 사용하는 연산자와 그 규칙들이 모두 동일하다.
즉 SQL 에서와 마찬가지로 산술 연산자, 비교 연산자, 논리 연산자 등의 사용이 모두 동일하고
연산자들 우선 순우를 조절하기 위한 괄호의 사용도 모두 동일하다.
** 제곱 연산자
+,- 일치, 부정
*,/ 곱하기, 나누기
+,-,|| 더하기, 빼기, 연결하기
=, <, >, <=, >=, <>, !=, ~=, ^=, IS NULL, LIKE, BETWEEN, IN 비교 연산자
NOT 논리 부정 연산자
AND 두 조건 모두 참일 경우 참을 반환
OR 두 조건 중 하나만 참일 경우 참을 반환
[출처] 다양한 예제로 쉽게 배우는 오라클 SQL과 PL/SQL 서진수 저