PL / SQL

SQL문장에서 변수 정의, 조건 처리(IF), 반복 처리(LOOP, WHILE, FOR) 등을

   지원하는 오라클 자체에 내장되어 있는 절차적 언어(Procedure Language)로

   SQL의 단점을 보완.

PL/SQL은 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절을 이용하여 프로그래밍 언어처럼 예외(오류)를 처리 할 수 있으며 사용자 정의 예외의 사용도 가능하다.

PL/SQL은 블록(BLOCK)구조로 되어 있으며 3부분으로 나눌 수 있다.

간단한 출력문 실행하기

오라클의 환경변수 SERVEROUTPUT은 PUT_LINE 프로시저가 출력해주는 내용을 화면에   보여주도록 설정하는 환경변수인데 디폴트 값이 OFF이기에 0N으로 변경해야 한다.)

변수

변수 선언

변수는 사용하기에 앞서 먼저 선언이 되어 있어야 하는데 선언부에서는 변수 명 다음에 자료형을 기술한다. 이렇게 선언된 변수는 실행부에서 사용된다.

변수 이름 [CONSTRAINT] 데이터 타입 [NOT NULL]

  [:= | DEFAULT EXPRESSION];

  ([ ]안의 내용은 써도 되고, 안써도 됨.

   EXPRESSION : 상수, 다른 변수, 연산자나 함수를 포함하는 표현식)

변수의 값 지정

 변수 이름 := 상수, 다른 변수, 연산자나 함수를 포함하는 표현식

예제2

변수의 선언 및 값을 입력 받고 그 변수의 값 출력하기

        (사용자로부터 두 개의 숫자를 입력 받아서 출력)

  - || 는 Java 출력문에서의 + 와 같은 개념임.

  - DECLARE 부분에서 선언한 변수들은 / 기호를 만나서 해당 PL/SQL문이

    종료됨과 동시에 사라진다. (프로시저로 만들면 다시 정의할 필요 없음)

결과

예제3

결과

스칼라변수

단순 변수 

PL/SQL에서 변수를 선언할 때 지정하는 자료형은 숫자를 저장하려면 NUMBER를 사용하고, 문자를 저장하려면 VARCHAR2를 사용해서 선언한다. SQL에서의 자료형 지정과 거의 동일하다.

즉, 단일 값을 가지는 변수의 데이터 형을 직접 지정해 주는 변수, 즉 정수, 실수 이런 형태를 직접 지정해주는 변수를 스칼라 변수라고 한다.

참조변수

%TYPE 내가쓰는 타입을 불러온다 값이 아니라 

테이블의 컬럼들을 변수 선언해서 특정 칼럼에 %TYPE하면 그 칼럼의 타입을 불러온다.

예제

결과

참조변수 %ROWTYPE

행 단위로 여러가의 타입을 가지고온다.

결과

내가가진 타입을 전부 받아올수있다.

복합 변수

 - 레코드타입

복합 변수(조합 변수) – 레코드 타입, 테이블 타입

  변수 하나 안에 사용자가 원하는 여러 가지 다른 유형의   데이터를 포함해서 사용자가 원하는 새로운 형태로 만든 변수

레코드 타입 변수란?

프로그램 언어의 구조체와 유사하다. PL/SQL RECORD는 FIELD들의 집합을 하나의 논리적 단위로 처리할 수 있게 해주므로 테이블의 ROW를 읽어올 때 편리하다.

형식 : TYPE type_name IS RECORD

  (field_name1 {scalar_datatype / record_type}, …);

          identifier_name  type_name;

type_name은 구조체의 이름이고, identifier_name은 구조체를 이용해 구체화시킨 것인다. 자바로 치면 일종의 객체라고 할 수 있음.

- 테이블 타입

TABLE TYPE 변수란?

PL/SQL TABLE TYPE은 로우에 대해 배열처럼 액세스하기 위해 기본키를 사용한다. 배열과 유사하고 PL/SQL테이블을 액세스 하기 위해 BINARY_INTEGER 데이터형의 기본키와 PL/SQL테이블 요소를 저장하는 스칼라 또는 레코드 데이터형의 칼럼을 포함해야 한다. 또한 이들은 동적으로 자유롭게 증가할 수 있다.

형식 : TYPE table_type_name IS TABLE OF

  {column_type / variable%type | table.column%type}

  [INDEX BY BINARY_INTEGER];

          identifier  table_type_name;

  table_type_name : 테이블형의 이름

  table.column : VARCHAR2, DATE, NUMBER과 같은 스칼라

            데이터 형

  identifier : 전체 PL/SQL 테이블을 나타내는 식별자의 이름

Table Type 변수 사용 예

Table Type 변수를 사용하여 사원 번호가 7499 인 사원의 이름을 조회해서 해당 변수에 저장 한 후 출력.

결과

제어문

조건문  IF

<형태>

IF 조건 THEN          << 이조건이 참일때에 THEN뒤의 문장을 실행해라

실행문장;

END IF;

/

다음 if문을 쓸떄는 꼭 end if를 사용한다.

제어문이 들어가는 부분은 BEGIN 부분이다.

예제)EMPLOYEES테이블에서 EMPLOYEE_ID가 10인 사원의 EMPLOYEE_ID,FIRST_NAME,VDNAME을 출력하시오.단 DNAME의 값은 아래와 같습니다

FIRST_NAME이 ‘추＇이면 ‘마케팅부서‘

FIRST_NAME이 ‘김＇이면 ‘기획부‘

예제2번 예외처리

결과

IF~ then ~ elsif~end if 문

CASE문

<형태>

CASE 조건

  WHEN 조건1 THEN 결과1

  WHEN 조건2 THEN 결과2

   …

  WHEN 조건N THEN 결과N

ELSE 기타처리문; (없으면생략가능)

END CASE;

/

반복문 BASIC LOOP

<형태>

LOOP

PL/SQL문장;

PL/SQL문장;

EXIT조건;

END LOOP;

/

반복문 WHILE LOOP

<형태>

WHILE 조건 LOOP

PL/SQL문장;

PL/SQL문장;

EXIT조건;

END LOOP;

/

반복문 FOR LOOP

<형태>

FOR counter IN [REVERSE] start..end LOOP

실행코드;

실행코드;

…

END LOOP;

/

프로시저

커서

DECLARE

-- 커서 선언

CURSOR cursor_name IS statement;

BEGIN

-- 커서 열기

OPEN cursor_name;<< 여기서 메모리가 할당된다.

--커서로부터 데이터를 읽어와 변수에 저장

FECTCH cur_name INTO variable_name;<< 데이터값을 받아온다

--커서 닫기

CLOSE cursor_name;

END;

 복수의 컬럼을 가지고와서 복수의행을 출력할수있다

%타입은 하나의 칼럼, 하나의 값

테이블타입 하나의 칼럼, 복수의값

%로우타입 복수의 칼럼, 하나의값

예제