1.6 DATABASE (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 명령문이라는 가정하에 좋아진다.
• 모듈식 프로그램 개발 가능 : 논리적인 작업 을 진행하는 여러 명령어들을 하나의 블록을 만들 수 있다.
• 이식성이 좋다

기본 특징

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

기본 PL/SQL Block 구조

영역	설명	옵션 / 필수 여부
DECLARE (선언)	모든 변수나 상수를 선언하는 부분 => 변수/상수/커서 등 선언	옵션
BEGIN  (실행)	제어문, 반복문, 함수 정의 등 로직을 기술	필수
EXCEPTION  (예외)	에러가 발생할 때 예외 상황	옵션
END (종료)		필수

PL/SQL Block의 종류

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

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

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

 

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

• DBMS_OUTPUT.PUT_LINE(출력할 내용) 같이 사용
• 사용하여 출력되는 내용을 화면에 보여주기 위해서는 환경 변수 SERVEROUTPUT(디폴트값이 OFF이므로) ON으로 변경

-- 메시지 출력하기 예시

SET serveroutput ON

BEGIN 

    dbms_output.put_line('God!!');

END;

put_line 출력 모습

변수 선언

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

 

문법
identifier [CONSTANT] datatype [NOT NULL]
[:=|DEFAULT expression];

 

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

--    DECLARE 변수이름 데이터타입;
DECLARE NAME VARCHAR2(10);

--    DECLARE 변수이름 데이터타입 :=값;
DECLARE NAME VARCHAR2(10) := '값';

--   DECLARE 변수이름 데이터타입 DEFAULT 기본값;
DECLARE NAME VARCHAR2(10) DEFAULT '디폴트';

변수를 한꺼번에 여러 개 선언하는 방법

   DECLARE 

	NAME    VARCHAR2(20);

	AGE      NUMBER(2); 

	GENDER VARCHAR(50)   DEFAULT '남';

변수를 선언하는 사용 예제

	DECLARE NAME VARCHAR2(100) := ' DECLARE 최초 선언';

	BEGIN

		DBMS_OUTPUT.put_Line('난최고다'|| NAME); -- 출력

	END;

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

 

1. %ROWTYPE

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

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

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

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;

 

변수 대입 방법

 

1. 명시적인 값 대입

 

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

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

identifier := expression;

 

2. 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로 대응 필수 => 개수와 데이터 타입, 길이를 일치해야 함