PL/SQL 기본_1

프로시저 작성을 위해 PL/SQL을 사용하게 되면서 갓대희님의 블로그를 참고하며 공부를 했다.

 

공부한 내용을 까먹지 않기 위해 블로그에 정리해보고자 한다.

 

우선 PL/SQL의 기본적인 정의를 정리해보자.

 

PL/SQL

• SQL을 확장한 절차적 언어(Procedural Language)
• Oracle의 표준 데이터 엑세스 언어로, 프로시저 생성자를 SQL과 완벽하게 통합
• 유저 프로세스가 PL/SQL 블록을 보내면, 서버 프로세서는 PL/SQL Engine에서 해당 블록을 받고 SQL과 Procedural를 나눠서 SQL은 SQL Statement Executer로 보냄
• PL/SQL 프로그램의 종료는 크게 Procedure, Function, Trigger로 나뉨
• SQL에서는 사용할 수 없는 절차적 프로그래밍의 기능을 가지고 있어 SQL의 단점 보완

위와 같은 정의를 가지는 PL/SQL의 기본적인 특징도 정리해보자.

 

PL/SQL의 기본 특징

• 블록 단위 실행 제공
  • 이를 위해 BEGIN, END; 사용
  • 마지막 라인에 /를 입력하면 해당 블록 실행
• 변수, 상수 등을 선언하여 SQL과 절차형 언어에서 사용
• 변수의 선언은 DECLARE 절에서만 가능
• BEGIN 섹션에서 새 값 할당 가능
• IF문을 통해 조건에 따라 문장들 분기 가능
• LOOP문을 사용하여 문장 반복가능
• 커서를 사용하여 여러 행 검색 및 처리
• PL/SQL에서 사용 가능한 SQL은 Query, DML, TCL
  • DDL(CREATE, DROP, ALTER, TRUNCATE…), DCL(GRANT, REVOKE) 명령어는 동적 SQL을 이용할 때만 사용 가능
• PL/SQL의 SELECT 문은 결과를 PL/SQL Engine으로 보냄
  • 이를 캐치하기 위한 변수를 DECLARE해야 하고, SELECT 문장은 반드시 한 개의 행이 검색되어야 함
  • 그리고 이를 위해 INTO 절을 꼭 사용해야 함
  • 검색되는 행이 없으면 문제 발생

 

PL/SQL Block의 기본 구조

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

 

PL/SQL Block의 종류

• 익명 블록 : 이름이 없는 PL/SQL Block
• 이름 있는 블록 : DB의 객체로 저장되는 블록
  
  • 프로시저 : 리턴 값을 하나 이상 가질 수 있는 프로그램
  • 함수 : 리턴 값을 반드시 반환해야 하는 프로그램
  • 패키지 : 하나 이상의 프로시저, 함수, 변수, 예외 등의 묶음
  • 트리거 : 지정된 이벤트가 발생하면 자동으로 실행되는 PL/SQL 블록

PL/SQL 프로그램 작성 요령

• PL/SQL 블록 내에서 한 문장이 종료될 때마다 세미콜론(;)을 사용하여 한 문장의 끝을 명시
• END 뒤에 세미콜론(;)을 사용하여 하나의 블록의 끝을 명시
• 단일 행 주석은 --, 여러 행 주석은 /* */
• 쿼리문을 실행하기 위해 '/'가 필수, PL/SQL 블록은 행에 '/'가 있으면 종결된 것으로 간주

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

• DBMS_OUTPUT.PUT_LINE(출력할 내용)
• 위 프로시저를 사용하여 출력되는 내용을 화면에 보여주기 위해서 환경 변수 SERVEROUTPUT ON(기본 값이 OFF)으로 변경

 

• 메시지 출력하기 예시

SET serveroutput ON
BEGIN
	dbms_output.put_line('출력 예시');
END;

결과 - 출력 예시

 

변수 선언

• 블록 내에서 변수를 사용하려면 선언부(DECLARE)에서 선언해야 하며 변수명 다음에는 데이터 타입을 기술
• 문법
  • identifier [CONSTANT] datatype [NOT NULL]
    [:=|DEFAULT expression];

identifier	변수명(식별자)
CONSTANT	상수로 지정(초기값 반드시 할당)
datatype	자료형 기술
NOT NULL	값을 반드시 포함
expression	Literal, 다른 변수, 연산자나 함수를 포함하는 표현식

 

문법으로만 보면 이해가 어렵기 때문에 예시를 통해 이해를 해보자

 

예시)

• DECALRE 변수이름 데이터타입;

DECLARE NAME VARCHAR2(10);

 

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

DECLARE NAME VARCHAR2(10) := 'Pdache';

 

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

DECLARE NAME VARCHAR2(10) DEFAULT 'PDH';

 

• 한 번에 여러 개의 변수 선언

DECLARE 
	NAME	VARCHAR2(20);
	AGE		NUMBER(2); 
	GENDER	VARCHAR(50)	DEFAULT '남';

 

• 변수 선언하고 사용

DECLARE NAME VARCHAR2(20) := '이효리';
	BEGIN
		DBMS_OUTPUT.put_Line('이효리'|| NAME); -- 출력
	END;

 

• 변수 Type을 선언할 때 명시적으로 작성하지 않고 사용하는 방법
  • %ROWTYPE
    
    • 해당 테이블이나 뷰의 컬럼 속성을 그대로 들고 오는 형태
    • 사용 방법 : 변수명 테이블이름%ROWTYPE
  • %TYPE
    • 해당 테이블의 컬럼 속성을 지정하여 그대로 들고 오는 형태
    • 사용 방법 : 변수명 테이블이름.컬럼명%TYPE
• %ROWTYPE 예시

DECLARE 
	DATA EMP%ROWTYPE;
BEGIN
	SELECT 	* INTO DATA 
	FROM 	EMP 
	WHERE 	EMPNO = '1234';
	DBMS_OUTPUT.PUT_LINE(DATA.ENAME ||','||DATA.DEPTNO);
END;

 

• %TYPE 예시

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;

 

변수 대입 방법

• 명시적 값 대입
  • 변수 값을 저장하기 위해 := 사용
  • := 좌측에는 변수, 우측에는 값
• SELECT 문을 이용한 값 대입
  • 기존 SELECT 문과는 다르게 INTO 추가
  • INTO 절에는 조회 결과 값을 저장할 변수 기술
  • SELECT 문은 INTO 절에 의해 하나의 행만을 저장
  • SELECT 다음에 기술한 컬럼은 INTO 절에 있는 변수와 1:1로 대응해야 하기 때문에 개수와 데이터 타입, 길이를 일치시켜야 한다.