[Oracle] PL/SQL

Ja L·2023년 5월 15일
PL/SQL절차형SQL
0

[PostgreSQL] Operation

목록 보기
9/39

절차형 SQL

절차형 프로그래밍 언어인 C언어처럼 SQL에도 절차 지향적인 프로그래밍이 가능하도록 벤더별로 프로그래밍 언어와 같은 절차형 SQL을 제공합니다.

오라클에서는 PL(Procedural Language)/SQL을 제공하며 SQL Server에서는 T-SQL 을 제공합니다.

이 포스팅은 오라클의 PL/SQL을 아주 간단하게 설명합니다.

PL/SQL 구조

PL/SQL은 기본적으로 BLOCK 구조로 되어있다. BLOCK 안에는 기본적으로 DECLARE, BEGIN, EXCEPTION으로 구성되어 있으며 마지막에는 END로 BLOCK의 끝을 표현합니다.

DECLARE: 선언부라고 하며 BEGIN ~ END 사이에서 사용할 변수에 대하여 정의하는 부분입니다. 정의할 게 없다면 생략도 가능합니다.

  • 변수란 데이터의 임시 저장영역으로 반복해서 재사용하기 위해 사용한다.
  • 변수를 사용할 수 있는 범위는 해당 블록 시작(BEGIN)에서 블록의 끝(END)에서만 사용가능하다.
  • 변수의 명칭은 반드시 문자로 시작해야 하며, 문자나 숫자, 특수문자를 포함할 수 있다. 단 변수명은 30 bytes 이하로 명명해야 한다.
  • 특정 값으로 미리 초기화 가능하다.
  • 데이터를 할당하려면 ':='을 사용한다.
  • 상수로 사용하려면 변수명 다음에 CONSTANT 키워드를 사용하면 된다.
  • 변수의 데이터 타입은 직접 지정할 수 있지만, 다른 테이블에서 참조해서 지정할 수 있다.
  • 아래는 변수 선언의 예시이다.
DECLARE
	V_PI CONSTANT NUMBER := 3.14;			-- 상수로 선언하였으며 변경 불가능하다.
	v_name VARCHAR2(100) := '홍길동';			-- 변수로 선언하였으며 디폴트 값으로 미리 '홍길동'을 할당
	v_addr VARCHAR2(100); 				-- 변수로 선언하였으며 디폴트 값을 선언하지 않았다.
	v_you VHARCHAR2(100) := '&your_name'; 		-- 해당 블록을 실행할때마다 사용자한테 입력받는다.​
	v_deptId DEPT.DEPT_ID%TYPE			-- DEPT 테이블의 DEPT_ID 컬럼의 데이터타입을 참조한다.
	v_temp	DEPT%ROWTYPE		-- DEPT 테이블의 선언된 컬럼의 모든 데이터타입 아무거나 들어갈 수 있다.

BEGIN: 실행 부라고 하며 처리하고자 하는 SQL문과 필요한 로직을 기술하는 부분이다.

EXCEPTION: 예외처리 부라고 하며 실행 도중에 에러가 발생할 경우 에러를 처리하는 로직을 기술하는 부분이다. 생략 가능하다.

EXCEPTION
WHEN 예외명1 THEN
	예외처리문장;
WHEN 예외명2 OR 예외명3 THEN
	예외처리문장;
WHEN OTHERS THEN
	나머지_예외처리문장;

PL/SQL 특징

DECLARE, BEGIN, EXCEPTION, END와 같은 예약어는 마지막에 세미콜론(;)을 붙이지 않습니다. 이외의 명령어는 세미콜론(;)을 붙입니다.

BLOCK안에 새로운 BLOCK을 포함할 수 있다. 이를 Nested Block이라고 표현합니다.

IF, LOOP 등의 조건을 사용하여 절차적인 프로그래밍이 가능합니다.

여러 SQL 문장을 Block으로 묶고 한 번에 Block 전부를 서버로 보내기 때문에 통신량을 줄일 수 있습니다.

PL/SQL로 작성된 코드는 PL/SQL 엔진이 처리하고, 일반적인 SQL 문장은 SQL 실행기가 처리합니다.

PL/SQL에서는 DML 문장은 직접적으로 쿼리를 작성하여 사용할 수 있습니다. 하지만 DDL, DCL, TCL은 직접적으로 지원하지 않습니다. 동적 SQL(런타임에 문자열로 작성되는 SQL)을 사용하면 가능하긴 합니다. 동적 SQL은 execute immediate를 이용합니다.

BEGIN
	execute immediate 'TRUNCATE TABLE 사원';
END

PL/SQL을 저장해서 쉽게 사용할 수 있습니다. 저장하여 사용할 경우 Stored PL/SQL Block이라고 합니다. 저장하지 않고 사용할 경우에는 Anonymous PL/SQL Block이라고 합니다.

PL/SQL SELECT

  • PL/SQL 의 SELECT 는 SELECT 한 데이터를 INTO 뒤에 변수에 값을 저장한다.
  • SELECT 컬럼 ㅎ아목의 수와 INTO 변수 항목의수는 동일해야 한다.
  • 반드시 로우의 결과는 1건만 나타나야 한다.
  • 아래 예시는 사원 ID가 '1'인 사원의 사원명이 v_name에 저장된다.
DECLARE
	v_name VARCHAR2(10);

BEGIN
	SELECT 사원명 INTO v_name FROM 사원 WHERE 사원ID = '1'; 

END

PL/SQL DML (INSERT, UPDATE, DELETE)

  • 기본적인 DML 문을 BEGIN 문장에 사용하면 된다.
  • 사용자로부터 입력을 받고자 한다면 선언부에 &을 사용하여 입력받는다.
  • 아래 예시는 사원 테이블에 INSERT를 하는 예시이다.
DECLARE
	v_id	VARCHAR2(4) := '&id';
	v_name	VARCHAR2(10) := '&name';
    
BEGIN
	INSERT INTO 사원 VALUES(v_id, v_name);

END

PL/SQL DML 제어문

IF 조건문

IF (조건) THEN
	실행문장;
ELSE
	실행문장;
END IF;


IF (조건) THEN
	실행문장;
ELSEIF (조건) THEN
	실행문장;
END IF;

CASE 조건문 

CASE 변수
	WHEN 조건1 THEN 결과1
	WHEN 조건1 THEN 결과1
	...
	WHEN 조건N THEN 결과N
ELSE 디폴트결과
END;

LOOP 반복문

LOOP
	문장;
    EXIT 조건;
ENd LOOP;

WHILE 반복문

WHILE 조건 LOOP
	문장;
END LOOP;

FOR 반복문

FOR 변수 IN 시작숫자..끝숫자 LOOP
	문장;
END LOOP;

Stored PL/SQL Block

PL/SQL을 마치 함수처럼 정의해서 사용할 수 있습니다. 이를 Stored PL/SQL Block 이라고 합니다. 저장하지 않고 이름 없이 사용하는 것은 Anonymous PL/SQL Block 이라고 합니다. 아래는 두 개의 차이점을 정리한 내용입니다.

Stored PL/SQL BlockAnonymous PL/SQL Block
한 번 컴파일되어 데이터베이스에 저장됨사용시마다 컴파일되고, 데이터베이스에 저장하지 않음
파라미터와 리턴값이 존재파라미터와 리턴값이 존재하지 않음
다른 프로그램에서 호출 가능다른 프로그램에서 호출 불가능

Stored PL/Block의 종류는 Procedure, Function, Package, Trigger 가 있습니다.

Procedure

  • 가장 기본적인 유형으로 한 번 컴파일된 후 데이터베이스에 저장되어 사용되고, 다른 프로그램에서 호출 가능합니다.
  • 아래는 데이터베이스에 Procedure를 생성하는 방법입니다.
CREATE PROCEDURE p_test -- 프로시저 명칭을 입력한다.
(
	파라미터1 IN 데이터타입,
	리턴값1 OUT 데이터타입,
	둘다가능 IN OUT 데이터타입
)
IS
	변수1 데이터타입;

PL/SQL Block
  • 파라미터1은 호출하는 곳에서 전달해줘야 하는 파라미터를 의미하므로 IN을 사용합니다. 생략 가능합니다.
  • 리턴값 1 은 호출하는 곳에서 리턴 값을 의미하므로 OUT을 사용합니다.
  • 둘 다 가능은 파라미터와 리턴 값이 된다는 의미로 IN OUT을 사용합니다.
  • Procedure을 호출하는 방법은 아래와 같습니다.
EXEC p_test() -- 인자가 없는 Procedure일 경우

EXEC p_test(파라미터1, 리턴값1, 둘다가능) -- 인자가 있는 Procedure일 경우

FUNCTION

  • Function은 반드시 리턴 값이 존재하는 Block 입니다.
  • 아래는 데이터베이스에 Function을 생성하는 방법입니다.
CREATE FUNCTION f_test -- 함수 명칭을 입력한다.
(
	파라미터1 IN 데이터타입,
	리턴값1 OUT 데이터타입,
	둘다가능 IN OUT 데이터타입
)
RETURN 데이터타입
IS
	변수1 데이터타입;

PL/SQL Block
  • 위에서 설명한 Procedure와 거의 동일합니다. 차이점은 반드시 리턴 값이 존재하므로 RETURN을 사용합니다. 그리고 리턴 값은 Block 문에서 Return 을 사용하여 리턴합니다.
  • Function은 일반적으로 알 고 있는 오라클 함수처럼 사용할 수 있습니다.
  • Procedure 와 Function은 자율적으로 트랜잭션을 처리할 수 있습니다.

Package

  • Package는 연관성이 있는 변수, 상수, PL/SQL BLOCK 등을 논리적으로 하나의 그룹으로 만드는 특수한 형태입니다. Package를 만들고 그 안에 여러가지 항목을 작성하여 사용하면 됩니다.
  • Package는 인터페이스와 같이 사용방법을 기술한 선언부와 실제 구현이 담겨 있는 바디부로 나뉩니다.
  • 아래는 데이터베이스에 Package를 생성하는 방법입니다.
-- 선언부
CREATE PACKAGE pack_test
IS
  상수명 CONSTANT 데이터타입;
  예외명 EXCEPTION;
  변수명 데이터타입;

  PROCEDURE 프로시저명칭 (변수타입들..);
  FUNCTION 함수명칭 (변수타입들..) RETURN 반환타입;
END pack_test;


-- 바디부
CREATE PACKAGE BODY pack_test
IS
  상수명 CONSTANT 데이터타입;
  예외명 EXCEPTION;
  변수명 데이터타입;

  PROCEDURE 프로시저명칭 -- 프로시저 생성구문
      ...
  END 프로시저명칭;

  FUNCTION 함수명칭 -- 함수 생성구문
      ...
  END 함수명칭;
END pack_test;
  • 호출하는 방법은 패키지명, 명칭처럼 사용하면 됩니다.

Trigger

  • Trigger는 특정 이벤트가발생할 때 자동으로 수행되는 Block을 의미합니다. 따라서 트리거는 직접적으로 실행할 수는 없습니다.
  • 자동으로 수행하게 하여 데이터의 무결성과 일관성을 유지하도록 합니다.
  • 트리거는 TCL을 이용하여 트랜잭션을 제어할 수 없습니다.
  • 특정 이벤트가 발생하기 전 (BEFORE EVENT), 특정 이벤트가 발생 후 (AFTER EVENT)등을 설정할 수 있습니다.
  • 이벤트에는 INSERT, UPDATE, DELETE, DDL, DROP, TRUNCATE 등을 사용할 수 있습니다.
  • DELETE ON TRIGGER의 경우, :OLD는 삭제 전 데이터를, :NEW는 삭제 후 데이터를 나타냅니다.
  • UPDATE TRIGGER 에서, :OLD 에는 수정 전, :NEW에는 수정 후 값이 들어갑니다.
  • 아래는 Trigger를 생성하는 방법입니다.
CREATE [OR REPLACE] TRIGGER trigger_name
BEFORE | AFTER
     trigger_event ON table_name
     [FOR EACH ROW]
     [WHEN (condition)]
     PL/SQL block
     
     
CREATE TRIGGER 인서트제약트리거
BEFORE UPDATE ON 사원연봉
BEGIN
	IF (:NEW.입력자) NOT IN ('대표') THEN
		RAISE_APPLICATION_ERROR(-9999,'대표만 수정할 수 있습니다.');
	END IF;
END
profile
Ja L
DB Engineer
이전 포스트

[PostgreSQL] Table Partitioning

다음 포스트

[Postgresql] Trigger

0개의 댓글

관련 채용 정보