[Oracle] PL/SQL ( Procedural Language for SQL )

 

PL/SQL

Procedural Language extensions to the Structured Query Language

 

---

 

 

• 오라클에서 제공하는 프로그래밍 언어
• 일반 프로그래밍 언어적인 요소를 가지고 있어 DB업무 처리에 최적화 된 언어
• SQL을 확장한 절차적 언어
• Oracle의 표준 데이터 엑세스 언어

 

 

[ 기본 개념 ]

 

• 선언부 DECLARE 

모든 변수나 상수 선언

 

• 실행부 Executable

BEGIN ~ END

실제 로직이 처리되는 부분

제어문, 반복문, 함수정의 등의 로직 기술

 

• 예외처리부 Exception ( Optional )

실행 도중 에러 발생 시, 에러 처리를 위한 명령 기술 

 

 

* DECLARE, BEGIN, EXCEPTION 등의 키워드들은 세미콜론을 붙이지 않는다.

 

 

 

• 익명 블록 ( Anonymous PL/SQL Block )

주로 일회성으로 사용

 

• 저장 블록 ( Stored PL/SQL Block )

서버에 저장하여 주기적으로 반복하여 사용

• 프로시저: 리턴 값을 하나 이상 가질 수 있음
• 함수: 리턴 값 반드시 반환
• 패키지: 하나 이상의 프로시저, 함수, 변수, 예외 등의 묶음
• 트리거: 지정 된 이벤트가 발생하면 자동으로 실행되는 Block

 

[ 예제 ]

 

DECLARE -- 익명 블록
	cnt integer;
BEGIN
	cnt := cnt + 1;  -- 할당 연산자 :=
	IF cnt IS NULL THEN
	dbms_output.put_line('결과: cnt는 null');
	END IF;
END;

 

DECLARE
	empNo number(20);
	empName varchar(30);
BEGIN
	SELECT EMPLOYEE_ID, FIRST_NAME || ' ' || last_name
	INTO empNO, empName
	FROM EMPLOYEES e2
	WHERE EMPLOYEE_ID = 123;

	dbms_output.put_line(empNO || ' ' || empName);
END;
/

 

 

 

[ 변수 ]

 

• 반드시 문자로 시작
• 문자나 숫자, 특수문자를 포함할 수 있다.
• 변수명은 30bytes 이하
• 예약어(키워드) 사용 x

 

• 변수의 선언은 선언부(DECLARE)에서 선언되고, 값으로 초기화 가능
• 실행부에서 실행할 경우 값이 할당 됨
• 서브프로그램의 파라미터로 전달되기도 하며, 서브프로그램의 출력 결과를 저장하기도 함

 

선언 예

• emp_no number(6, 3) : 숫자를 저장하는 변수로 총 6자리를 의미하며 소수점 이하 3자리 의미
• emp_name varchar2(5)
• emp_date date

 

오라클 데이터 타입

• char : 고정길이 문자, 기본최소값 1, 최대 32,767 바이트 값 저장
• varchar2 : 가변길이 문자, 최대 32,767 바이트 값 저장
• number(전체자리 수, 소수점이하 자리수) : 가변 숫자 (전체 자리수 : +1 ~ +38, 소수점이하 자리수 -84 ~ +127 )
• binary_double : 부동 소수점 수 저장, 9바이트 필요
• date : 날짜 및 시간 저장, 초단위, 범위는 4712B.C ~ 9999 A.D
• timestamp : date 타입 확장, 연도, 월, 일, 시, 분, 초 및 소수로 표시되는 초단위 저장 ( 자리수 0~9 기본값 6 )

 

참조 변수

empNo employees.employee_id%TYPE

  : employees 테이블의 employee_id와 동일한 데이터 타입으로 선언

 

 empRow employees%ROWTYPE

  : employees 테이블의 모든 컬럼을 한번에 저장하기 위한 변수

 

 

예제 1

  : Table 복사 후 %TYPE 을 이용하여 값 지정

 

-- table copy

CREATE TABLE EMPLOYEES_1 AS
SELECT EMPLOYEE_ID, DEPARTMENT_ID, SALARY
FROM EMPLOYEES e;


SET serveroutput ON;

DECLARE

      empNo employees_1.employee_id%TYPE;
      empSal employees_1.salary%TYPE;
 
BEGIN

      select EMPLOYEE_ID, SALARY
      INTO empNo, empSal
      from EMPLOYEES
      WHERE DEPARTMENT_ID = 10;
 
      dbms_output.put_line(empNo || ' ' || empSal);

END;

/

 

 

예제2

 : %ROWTYPE 활용

 

DECLARE

      empRow employees_1%ROWTYPE;
 
BEGIN

      SELECT * INTO EMPROW
      FROM EMPLOYEES_1
      WHERE employee_id = 100;

      dbms_output.put_line(empRow.employee_id || ' ' || empRow.salary || ' ' || empRow.department_id);

END;

/

 

예제3

  : 치환연산자 사용

 

DECLARE

      no1 NUMBER := &no1;
      no2 NUMBER := &no2;
      v_sum NUMBER;

BEGIN

      v_sum := no1 + no2;
      dbms_output.put_line('첫번째 수: ' || no1 || ', 두번째 수: ' || no2 || ', 합: ' || sum || '입니다.');
END;
/