[Oracle] PL/SQL

 

 

공부했던 자료 정리하는 용도입니다.

재배포, 수정하지 마세요.

 

예제는 Oracle Database에서 기본으로 제공되는 SCOTT계정 데이터로 진행됩니다.

 

 

 

SQLDeveloper에서 PL/SQL 실행하기  보기 → DBMS 출력 → DBMS 출력 창의  +  버튼 누르기 → 접속할 DB 선택 후 확인

 

 

PL/SQL

  PL/SQL은 SQL만으로는 구현이 어렵거나 구현 불가능한 작업을 수행하기 위해 오라클에서 제공하는 프로그래밍 언어이다. 변수 · 조건 처리 · 반복 처리 등 다른 프로그래밍 언어에서 제공하는 다양한 기능을 사용할 수 있다.

 

 

  ■ PL/SQL의 기본 형식

DECLARE
    [실행에 필요한 여러 요소 선언];
BEGIN
    [작업을 위해 실제 실행하는 명령어];
EXCEPTION
    [PL/SQL수행 도중 발생하는 오류 처리];
END;

선언부와 예외부는 생략 가능하지만 실행부는 반드시 존재해야 한다.

필요에 따라 PL/SQL 블록 안에 다른 블록을 포함할 수도 있는데 이를 중첩 블록(nested block)이라고 한다.

 

 블록(block)  : PL/SQL 프로그램의 기본 단위

 

구성 키워드	필수/선택	설  명
DECLARE(선언부)	선택	실행에 사용될 변수·상수·커서 등을 선언
BEGIN(실행부)	필수	조건문 · 반복문 ·  SELECT  ·  DML  · 함수 등을 정의
EXCEPTION(예외 처리부)	선택	PL/SQL 실행 도중 발생하는 오류(예외 상황)를 해결하는 문장 서술

 

PL/SQL 작성 주의사항      1. PL/SQL 블록을 구성하는  DECLARE ,  BEGIN ,  EXCEPTION 키워드에는 세미콜론(;)을 사용하지 않는다.     2. PL/SQL블록의 각 부분에서 실행해야 하는 문장 끝에는 세미콜론(;)을 사용        ex)  DBMS_OUTPUT.PUT_LINE('HELLO, PL/SQL !');     3. SQL에서와 마찬가지로 PL/SQL 내부에서도 주석 사용 가능        한 줄 주석 :  --        여러 줄 주석 :  /*  주석내용  */     4. PL/SQL문 작성을 마치고 실행하기 위해 마지막에 슬래시( / )를 사용(SQLDeveloper에서는 생략 가능)

 

 

 

  ■ PL/SQL 주석

  주석은 PL/SQL 코드에 포함되어 있지만 실행되지 않는 문장을 말한다. 일반적으로 특정 기호를 사용하여 코드 설명이나 이력 등을 남겨 놓거나, 일시적으로 실행되지 않기를 원하는 코드를 삭제하지 않고 남겨 두는 용도로 주석을 사용한다. PL/SQL에서 사용하는 주석은 한 줄 주석과 여러 줄 주석으로 나뉜다. 

종  류	사용 기호	설  명
한 줄 주석	-- [주석 내용]	현재 줄만 주석 처리
여러 줄 주석	/* [주석 내용] */	/* 에서  */ 까지 (줄의 개수에 상관없이) 주석 처리

 

 

 

  ■ PL/SQL에서 간단한 문장 출력

-- 문장 출력(SQLPlus)
SET SERVEROUTPUT ON;	-- 실행결과를 화면에 출력하기 위한 것
BEGIN
    DBMS_OUTPUT.PUT_LINE('HELLO, PL/SQL !');
END;
/	-- 오타아님. 실행하기 위해 사용(SQLDeveloper에서는 생략 가능)


-- SQLDeveloper인 경우 아래와 같이 쳐도 똑같이 실행된다.
BEGIN
    DBMS_OUTPUT.PUT_LINE('HELLO, PL/SQL !');
END;
/

SQLPlus에서 PL/SQL 실행결과를 화면에 출력하려면  SERVEROUTPUT 환경변수 값을  ON 으로 해야 한다.

SQLPlus의 접속 계정이 변경되거나 접속이 끊어진 후 다시 SQLPlus를 접속했다면  SERVEROUTPUT 을 다시  ON 해줘야 PL/SQL 결과를 확인할 수 있다.

 PUT_LINE 은 화면 출력을 할 때 사용한다.  DBMS_OUTPUT 패키지 안에 있다.

 

 

 

 

 

---

변수와 상수

  ■ 변수(variable)

 변수는 데이터를 일시적으로 저장하는 요소이다. 선언부( DECLARE )에서 작성하며 작성한 변수는 실행부( BEGIN )에서 활용한다.

[변수 이름] [자료형] := [값 or 값을 도출하는 표현식];

• [변수 이름] : 데이터를 저장할 변수 이름 지정
• [자료형] : 선언한 변수에 저장할 데이터의 자료형 지정
•  :=  : 선언한 변수에 값을 할당하기 위해 사용하는 연산자
          값을 할당하지 않고 변수 선언만 한다면 생략 가능
  
• [값 or 값이 도출되는 표현식]
  : 변수에 값을 저장할 첫 데이터 값이나 저장할 수 있는 값이 결과로 반환되는 표현식을 지정한다. 
    변수에 지정한 자료형과 같아야 하며 값을 할당하지 않고 변수 선언만 한다면 생략 가능하다.
  

 

-- ex) 변수 선언과 변수값 출력
DECLARE	-- 선언부
    V_EMPNO NUMBER(4) := 7788;	-- 변수 V_EMPNO에 7788할당. NUMBER(4)이므로 4자리 숫자
    V_ENAME VARCHAR(10);	-- 변수 V_ENAME을 VARCHAR(10)자료형으로 선언. 값은 지정하지 X 
BEGIN	-- 실행부
    V_ENAME := 'SCOTT';		-- 변수 V_ENAME에 SCOTT을 저장
    DBMS_OUTPUT.PUT_LINE('V_EMPNO : ' || V_EMPNO);	-- 변수 V_EMPNO 출력
    DBMS_OUTPUT.PUT_LINE('V_ENAME : ' || V_ENAME);	-- 변수 V_ENAME 출력
END;	-- 현재 블록 종료
/	-- 작성한 PL/SQL문을 실행(SQLDeveloper에서는 생략 가능)

간단하게 변수를 선언하고 값을 대입한 뒤 출력하는 예시이다.

문자를 연결할 때는  || 연산자를 사용한다.

 

실행 결과

 

   식별자(identifier) 

  변수를 포함한 PL/SQL문에서 지정하는 객체 이름을 식별자라고 한다.

 

식별자에 이름을 붙이는 규칙      1. 같은 블록 안에서 식별자는 고유해야 하며 중복될 수 X     2. 대·소문자를 구별하지 X     3. 테이블 이름을 붙이는 규칙         ① 문자로 시작해야 한다.(한글도 가능, 숫자로 시작은 안됨)         ② 이름은 30byte 이하여야 한다.(영어는 30자, 한글은 15자 까지 사용 가능)         ③ 이름은 영문자(또는 한글), 숫자( 0 - 9 ), 특수문자( $ ,  # ,  _ )사용 가능         ④ SQL 키워드는 테이블 이름으로 사용불가             ex)  SELECT ,  FROM  등

 

 

  ■ 상수(constant)

[변수 이름] CONSTANT [자료형] := [값 or 값을 도출하는 표현식];

  상수는 변수와 달리 한번 저장한 값이 프로그램이 종료될 때까지 유지되는 저장 요소이다.

기존 변수 선언에  CONSTANT 키워드를 사용하기만 하면 된다.

 

 

  ■ 기본값(default)

[변수 이름] [자료형] DEFAULT [값 or 값을 도출하는 표현식];

 DEFAULT 키워드를 사용하면 변수에 저장할 기본값을 지정할 수 있다.

 

 

  ■ 변수와 NOT NULL

[변수 이름] [자료형] NOT NULL [:= or DEFAULT] [값 or 값을 도출하는 표현식];


-- NOT NULL과 DEFAULT를 같이 사용하는 것도 가능
V_DEPTNO NUMBER(2) NOT NULL DEFAULT 20;

  PL/SQL에서 선언한 변수는 특정 값을 할당하지 않으면  NULL  값이 기본으로 할당되는데,  NOT NULL 키워드를 사용하면 특정 변수에  NULL 이 저장되지 않도록 할 수 있다. 대신 선언과 동시에 특정 값을 반드시 지정해 주어야 한다.

 

 

  

   변수의 자료형 

1.  스칼라(scalar)  : 내부 구성요소가 없는 단일 값 (C나 Java의 원시(primitive) 자료형과 유사)
2.  복합(composite)  : 여러 종류 및 개수의 데이터를 저장하기 위해 사용자가 정의하는 자료형
   컬렉션(TABLE) : 한 가지 자료형의 데이터를 여러 개 저장(테이블의 열과 유사)
   레코드(RECORD) : 여러 종류 자료형의 데이터를 저장(테이블의 행과 유사)
   
3.  참조(reference)  : 특정 테이블 열의 자료형 or 하나의 행 구조를 참조하는 자료형
    %ROWTYPE  : 행구조 전체 참조
   %TYPE  : 열 참조
4.  LOB(Large OBject)  : 대용량의 텍스트·이미지·동영상·사운드 데이터 등 대용량 데이터를 저장하기 위한 자료형
   ex)  BLOB ,  CLOB 
   

 

  1. 스칼라형(scalar type)

  오라클에서 기본으로 정의해 놓은 자료형으로 내부 구성 요소가 없는 단일 값을 의미한다. 스칼라형은 숫자 · 문자열 · 날짜 · 논리 데이터로 나뉘며 대표적인 스칼라형은 아래와 같다.

 

분  류	자료형	설  명
숫자	NUMBER	소수점을 포함할 수 있는 최대 38자리 숫자 데이터
문자열	CHAR	최대 32,767 byte 고정 길이 문자열 데이터
	VARCHAR2	최대 32,767 byte 가변 길이 문자열 데이터
날짜	DATE	기원전 4712년 1월 1일부터 서기 9999년 12월 31일 까지 날짜 데이터
논리 데이터	BOOLEAN	PL/SQL에서만 사용할 수 있는 논리 자료형으로  true ,  false ,  NULL 을 포함

 

 

  3. 참조형(reference type)

[변수 이름] [테이블이름.열이름%TYPE]	--이후 :=나 DEFAULT 키워드를 사용하여 값을 지정하는것도 가능


-- ex) 예시
V_DEPTNO DEPT.DEPTNO%TYPE := 50;	-- DEPT테이블의 DEPTNO열을 참조하는 참조형 변수 V_DEPTNO를 선언하고 50을 대입

  참조형은 데이터베이스에 존재하는 특정 테이블 열의 자료형이나 하나의 행 구조를 참조하는 자료형이다. 

•  %ROWTYPE  : 행구조 전체 참조
•  %TYPE  : 열 참조

 

DECLARE
    V_DEPT_ROW DEPT%ROWTYPE;	-- V_DEPT_ROW변수가 DEPT 테이블의 행 구조를 참조 (DEPT, DNAMD, LOC필드를 갖게된다.)
BEGIN
    SELECT DEPTNO, DNAME, LOC INTO V_DEPT_ROW	-- SELECT문의 결과 행을 V_DEPT_ROW에 저장함
      FROM DEPT
     WHERE DEPTNO = 40;
    DBMS_OUTPUT.PUT_LINE('DEPTNO : ' || V_DEPT_ROW.DEPTNO);	-- 저장한 V_DEPT_ROW의 필드(DEPTNO, DNAME, LOC)를 불러옴
    DBMS_OUTPUT.PUT_LINE('DNAME : ' || V_DEPT_ROW.DNAME);
    DBMS_OUTPUT.PUT_LINE('LOC : ' || V_DEPT_ROW.LOC);
END;
/

   %ROWTYPE 을 활용한 예시이다.  DEPT 테이블의 행 구조를 참조하도록 선언되었기 때문에 내부에  DEPTNO ,  DNAME ,  LOC 필드를 갖게 된다.  INTO 키워드로  DEPT 테이블의  SELECT 문 결과 행을  V_DEPT_ROW 에 저장할 수 있는데,  INSERT 문과 마찬가지로  V_DEPT_ROW 가 소유한 필드 개수 및 자료형과  SELECT 문 결과 열의 개수와 자료형은 같아야 한다.

 

실행 결과