PL/SQL

 

 

 

▶ PL/SQL (Procedural Language extension to SQL)

 

 - SQL을 확장한 절차적 언어(Procedural Language)이다. 

 

 - 관계형 데이터베이스에서 사용되는 Oracle의 표준 데이터 엑세스 언어로, 프로시저 생성자를 SQL과 완벽하게 통합한다.

 

 - 유저 프로세스가 PL/SQL 블록을 보내면, 서버 프로세서는 PL/SQL Engine에서 해당 블록을 받고 SQL과 Procedural를 나눠서 SQL은 SQL Statement Executer로 보낸다.

 

 - PL/SQL 프로그램의 종류는 크게 Procedure, Function, Trigger 로 나뉘어 진다.

 

 - 오라클에서 지원하는 프로그래밍 언어의 특성을 수용하여 SQL에서는 사용할수없는 절차적 프로그래밍 기능을 가지고 있어 SQL의 단점을 보완하였다.

 

 

 

▶ 장점

 

 - 프로시저 생성자와 SQL의 통합

 

 - 성능 향상 : 잘 만들어진 PL/SQL 명령문이라는 가정하에 좋아진다.

 

 - 모듈식 프로그램 개발 가능 : 논리적인 작업 을 진행하는 여러 명령어들을 하나의 블록을 만들 수 있다.

 

 - 이식성이 좋다

 

 - 예외 처리 가능

 

 => 또한 SQL의 다음 단점을 해결 가능하다.

1) 변수가 없다.

2) 한번에 하나의 명령문만 사용 가능하기 떄문에 트래픽이 상대적으로 증가한다.

3) 제어문이 사용 불가. (IF, LOOP)

4) 예외처리가 없다. 등등

 

 

 

 

 

SQL은 절차형 언어 X
처리와 집합을 하지만 절차적 과정은 제공 안함

SQL 로직을 작성하게 되면 문장의 실행순서는 옵티마이저에 의해 이루어지는데, 여러가지 데이터 베이스 통계를 기초로 실행 계획을 처리하기 떄문에 데이터를 일반적으로 빠르게 다루어 주지만 결국 개발자 입장에서는 융통성있는 데이터 처리는 불가능 하다

따라서 그 단점을 극복하고자 상용 DBMS는 절차적으로 작성이 가능한 PL/SQL을 제공한다!
절라적언어 특성이 있어서 결과, 제약조건, 처리 절차를 개발자가 작성해야 한다 !

1. 일반 SQL 문 따로 작업 없이 사용 가능
SELECT, DELETE, UPDATE, INSERT, MERGE 같은것
2. DECLARE -  BEGIN -  END로 작성해야한다.
블록구조로 이루어져 있어서 
3. 제어문 반복문의 사용이 가능 (IF, LOOP, FOR WHILE..)
순차 처리를 지원하는 언어이기 때문에 
4. 예외 처리기 지원
실행 중 정상적인 흐름에 위반되는 상황 발생시 예외 처리기가 동작해 처리해줌
5. 서브 프로그램 지원
PL/SQL FUNCTION / 프로시
6. 패키지를 사용한 모듈화를 지원
7. 트리거 실행
이벤트를 처리하는 기능을 제공 (작성된 PL/SQL로직을 호출)
8. 객체 지행 프로그래밍 지원
JAVA, C++, C# 등
9. 다양한 내장 API
오라클 DBMS안에 수많은 API가 제공되고 있어서 
패키지나 함수, 프로시저 등으로 제공되어 PL/SQL 작성시 유용하다
10. 플랫폼 독립적
어떤 운영체제인것과 별개로 같은 오라클 버전 에선 관계없이 수행이 가능

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

▶ 기본 특징

 

 

 - 변수의 선언은 DECLARE절에서만 가능하다. 그리고 BEGIN 섹션에서 새 값이 할당될 수 있다.

 

 - IF문을 사용하여 조건에 따라 문장들을 분기 가능

 

 - LOOP문을 사용하여 일련의 문장을 반복 가능

 

 - 커서를 사용하여 여러 행을 검색 및 처리

 

 - [ PL/SQL에서 사용 가능한 SQL은 Query, DML, TCL이다. ]

   DDL (CREATE, DROP, ALTER, TRUNCATE …), DCL (GRANT, REVOKE) 명령어는 동적 SQL을 이용할 때만 사용 가능하다.

 

 - [ PL/SQL의 SELECT문은 해당 SELECT의 결과를 PL/SQL Engine으로 보낸다. ]

   이를 캐치하기 위한 변수를 DECLARE해야 하고, INTO절을 꼭 선언하여 넣을 변수를 꼭 표현해주어야 SELECT 문장은 반드시 한 개의 행이 검색되어야 한다.

   그리고 이를 INTO절을 꼭 사용해야한다. 또한 검색되는 행이 없으면 문제가 발생한다.

 

 

 

▶ 기본 PL/SQL Block 구조

 

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

 

 

 

▶ PL/SQL Block의 종류

 

1) 익명 블록 : 이름이 없는 PL/SQL Block을 말한다.

2) 이름 있는 블록 : DB의 객체로 저장되는 블록이 있다.

 - 프로시저 : 리턴 값을 하나 이상 가질 수 있는 프로그램을 말한다.

 - 함수 : 리턴 값을 반드시 반환해야 하는 프로그램을 말한다.

 - 패키지 : 하나 이상의 프로시저, 함수, 변수, 예외 등의 묶음을 말한다.

 - 트리거 : 지정된 이벤트가 발생하면 자동으로 실행되는 PL/SQL 블록이다.

 

 

 

▶ PL/SQL 프로그램의 작성 요령

 

 - PL/SQL블록 내에서는 한문장이 종료할때마다 세미콜론(;)을 사용하여 한문장이 끝났다는것을 명시

 

 - END 뒤에 세미콜론( ; )을 사용하여 하나의 블록이 끝났다는 것을 명시

 

 - 단일행주석은 -- 이고 여러행 주석은 /* */입니다.

 

 - 쿼리문을 수행하기 위해서 '/'가 반드시 입력되어야 하며, PL/SQL 블록은 행에 '/'가 있으면 종결된것으로 간주.

 

 

 

[참고] 오라클에서 화면 출력을 위해서는 PUT_LINE이란 프로시저를 이용 

 

 - DBMS_OUTPUT.PUT_LINE(출력할 내용) 같이 사용. 

 

 - 위 프로시저를 사용하여 출력되는 내용을 화면에 보여주기 위해서는 환경 변수 SERVEROUTPUT(디폴트값이 OFF이므로) ON으로 변경.

 

 - SET serveroutput ON

 

-- 메시지 출력하기 예시

SET serveroutput ON

BEGIN 

    dbms_output.put_line('God Dman!!');

END;

 

결과 - God Damn!!

 

 

 

 

■ SERVEROUTPUT 설정

 

SQL> SET SERVEROUTPUT ON;

 

기본적으로 PL/SQL은 결과물을 보여주지 않는다.

결과물을 보고 싶다면 SERVEROUTPUT 설정 을 ON 으로 설정해 주어야 한다.

 

■ Error~!?

 

SQL> SHOW ERRORS;

 

PL/SQL 은 오류를 안보여준다.

그렇기 때문에 위 명령어로 확인 한다.

 

 

 

 

 

 

▶ 변수 선언

 

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

- 값을 지정하거나 재지정하기 위해 PL/SQL 지정 문자를 사용한다

지정연산자 ( ;= ) 의 좌측에 변수를, 우측에  새 값을 적는다.

 - 문법

identifier [CONSTANT] datatype [NOT NULL]

[:=|DEFAULT expression];

 

identifier       변수명(식별자)

CONSTANT    상수로 지정 (초기치를 반드시 할당해야함)

datatype       자료형을 기술

NOT NULL     값을 반드시 포함

expression     Literal, 다른 변수, 연산자나 함수를 포함하는 표현식

 

 

 - 문법으로만 보면 이해가 어렵다..

* 예시)

    DECLARE 변수이름 데이터타입;

ex) DECLARE NAME VARCHAR2(10);

 

    DECLARE 변수이름 데이터타입 :=값;

ex) DECLARE NAME VARCHAR2(10) := '갓댐';

 

    DECLARE 변수이름 데이터타입 DEFAULT 기본값;

ex) DECLARE NAME VARCHAR2(10) DEFAULT '갓대미';

 

 

 

* 변수를 한번에 여러개 선언방법

   DECLARE 

NAME    VARCHAR2(20);

AGE      NUMBER(2); 

GENDER VARCHAR(50)   DEFAULT '남';

 

 

* 변수선언하여 사용방법

ex)

DECLARE NAME VARCHAR2(20) := '이효리';

BEGIN

DBMS_OUTPUT.put_Line('이효리'|| NAME); -- 출력

 

 SELECT     INTO  

 FROM 

 WHERE ENAME='이효리';

END;

 

 

* 변수 Type을 선언할때 꼭 명시적으로 작성하지 않고 사용하는 방법도 있다.

 

1. %ROWTYPE

 - 해당 테이블이나 뷰의 컬럼 속성을 그대로 들고 오는 형태이다. 

 - 사용방법 : 변수명 테이블이름%ROWTYPE

 

ex)

DECLARE 

DATA EMP%ROWTYPE;

BEGIN

SELECT  * INTO DATA 

FROM  EMP 

WHERE  EMPNO = '1234';

DBMS_OUTPUT.PUT_LINE(DATA.ENAME ||','||DATA.DEPTNO);

END;

 

 

2. %TYPE

 - 해당 테이블의 컬럼 속성을 지정하여 그대로 들고 오는 형태이다.

 - 사용방법 : 변수명 테이블이름.컬럼명%TYPE

 

ex)

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;

 

 

 

▶ 변수 대입 방법

 

* 명시적인 값 대입

 

 - 변수값을 저장하기 위해서는 := 를 사용한다.

 - := 의 좌측에는 변수를 , 우측에는 값을 기술

   identifier := expression;

 

 

* SELECT 문을 이용하여 값 대입

 

 - 기존 SELECT 문과는 다르게 INTO절이 추가 된다.

   INTO절에는 조회 결과 값을 저장할 변수를 기술. select 문은 INTO절에 의해 하나의 행만을 저장 가능

 

 - 문법

SELECT  select_list

INTO  {variable_name1[,variable_name2,..]|record_name}

FROM  table_name

WHERE  condition;

 

※ SELECT 다음에 기술한 컬럼은 INTO 절에 있는 변수와 1:1로 대응해야하기 때문에 개수와 데이터 타입, 길이를 일치시켜야함

- PL/SQL TABLE TYPE