[오라클] Oracle® Database Concepts 챕터 8 한글 번역

torch·2024년 7월 21일

db oracle

Oracle

목록 보기

7/13

Reference : 8. Server-Side Programming: PL/SQL and Java

*GPT 번역 기반으로 내용이 정확하지 않을 수 있습니다.

8 서버-사이드 프로그래밍: PL/SQL과 Java

SQL은 구조화된 질의 언어(SQL)와 데이터베이스가 SQL 문을 처리하는 방법을 설명합니다. 이 장에서는 데이터베이스에 저장된 PL/SQL(절차적 언어/SQL) 또는 Java 프로그램이 SQL을 어떻게 사용할 수 있는지 설명합니다.

이 장에는 다음 주제가 포함됩니다:

서버-사이드 프로그래밍 소개
PL/SQL 개요
Oracle 데이터베이스에서의 Java 개요
트리거 개요

참고:

"SQL"에서 SQL 언어 개요 확인

서버-사이드 프로그래밍 소개

SQL과 같은 비절차적 언어에서는 데이터를 조작할 집합을 지정하지만, 수행할 작업이나 수행 방법은 지정하지 않습니다.

절차적 언어 프로그램에서는 대부분의 문 실행이 이전 또는 이후의 문과 루프나 조건 분기와 같은 SQL에서는 사용할 수 없는 제어 구조에 따라 달라집니다. 절차적 언어와 비절차적 언어의 차이를 설명하기 위해, 다음 SQL 문이 직원 테이블을 질의한다고 가정해봅시다:

SELECT employee_id, department_id, last_name, salary FROM employees;

위의 문은 데이터를 요청하지만, 데이터에 논리를 적용하지는 않습니다. 그러나 각 데이터 세트의 직원이 급여 인상을 받을 자격이 있는지 결정하는 애플리케이션을 원한다고 가정해봅시다. 급여 인상의 필수 조건은 직원이 지난 5년 동안 세 번 이상의 인상을 받지 않았다는 것입니다. 인상이 필요하다면 애플리케이션은 급여를 조정하고 매니저에게 이메일을 보내야 합니다. 그렇지 않으면 애플리케이션은 보고서를 업데이트해야 합니다.

조건부 논리와 프로그램 흐름 제어가 필요한 절차적 데이터베이스 애플리케이션이 SQL을 사용할 수 있는 방법이 문제입니다. 기본 개발 접근 방식은 다음과 같습니다:

클라이언트-사이드 프로그래밍을 사용하여 절차적 언어(C, C++ 또는 Java와 같은)로 작성된 애플리케이션에 SQL 문을 포함시키기
- 소스 코드에 SQL 문을 포함시키고 컴파일 전에 전처리기 또는 Java 번역기에 제출합니다. 또는 전처리 단계 없이 Java Database Connectivity (JDBC) 또는 Oracle Call Interface (OCI)와 같은 API를 사용하여 애플리케이션이 데이터베이스와 상호 작용할 수 있게 합니다.
서버-사이드 프로그래밍을 사용하여 데이터 논리를 데이터베이스에 저장하기
- 애플리케이션이 PL/SQL(PL 시퀄로 발음) 또는 Java로 작성된 저장 서브프로그램(프로시저 및 함수)을 명시적으로 호출할 수 있습니다. 또한, 지정된 이벤트에 응답하여 호출되는 데이터베이스에 저장된 명명된 프로그램 단위인 트리거를 생성할 수 있습니다.

이 장에서는 두 번째 접근 방식을 설명합니다. 서버-사이드 프로그래밍의 주요 이점은 데이터베이스에 내장된 기능이 어디서든 배포될 수 있다는 점입니다. 데이터베이스가 애플리케이션이 아닌 주어진 운영 체제에서 작업을 수행하는 최적의 방법을 결정합니다. 또한, 서브프로그램은 서버에서 애플리케이션 처리를 중앙 집중화하여 확장성을 높이고 클라이언트가 코드를 재사용할 수 있게 합니다. 서브프로그램 호출은 빠르고 효율적이므로 단일 호출로 계산 집약적인 저장 서브프로그램을 시작하여 네트워크 트래픽을 줄일 수 있습니다.

다음 언어를 사용하여 Oracle Database에 데이터 논리를 저장할 수 있습니다:

PL/SQL
- PL/SQL은 SQL에 대한 Oracle Database 절차 확장입니다. PL/SQL은 데이터베이스와 통합되어 모든 Oracle SQL 문, 함수 및 데이터 유형을 지원합니다. 데이터베이스 API로 작성된 애플리케이션은 PL/SQL 저장 서브프로그램을 호출하고 PL/SQL 코드 블록을 데이터베이스에 보내 실행할 수 있습니다.
Java
- Oracle Database는 Java 애플리케이션을 개발, 저장 및 배포하는 지원도 제공합니다. Java 저장 서브프로그램은 데이터베이스에서 실행되며, 중간 계층에서 실행되는 프로그램과 독립적입니다. Java 저장 서브프로그램은 PL/SQL과 유사한 실행 모델을 사용하여 SQL과 인터페이스합니다.

참고:

"클라이언트-사이드 데이터베이스 프로그래밍"에서 전처리기 및 API를 사용한 SQL 포함 방법 학습
Oracle Database 2 Day Developer's Guide에서 Oracle Database 애플리케이션 개발 입문서
Oracle Database Development Guide에서 프로그래밍 환경 선택 방법 학습

PL/SQL 개요

PL/SQL은 사용하기 쉽고, SQL과 매끄럽게 연동되며, 강력하고, 이식 가능하며, 안전한 서버-사이드 저장 절차 언어를 제공합니다. PL/SQL 유닛이라 불리는 절차적 객체를 사용하여 데이터베이스 데이터를 접근하고 조작할 수 있습니다.

PL/SQL 유닛은 일반적으로 다음과 같이 분류됩니다:

PL/SQL 서브프로그램은 데이터베이스에 저장되고 애플리케이션에서 이름으로 호출할 수 있는 PL/SQL 블록입니다. 서브프로그램을 생성하면 데이터베이스는 서브프로그램을 구문 분석하고 구문 분석된 표현을 데이터베이스에 저장합니다. 서브프로그램은 프로시저나 함수로 선언할 수 있습니다.

PL/SQL 익명 블록은 애플리케이션에 나타나며 데이터베이스에 이름이 지정되거나 저장되지 않은 PL/SQL 블록입니다. 많은 애플리케이션에서 PL/SQL 블록은 SQL 문이 나타날 수 있는 모든 곳에 나타날 수 있습니다.

PL/SQL 컴파일러와 인터프리터는 Oracle SQL Developer에 내장되어 있어 개발자가 클라이언트와 서버에서 일관된 개발 모델을 사용할 수 있게 합니다. 또한, PL/SQL 저장 프로시저는 Pro*C, JDBC, ODBC, OCI와 같은 여러 데이터베이스 클라이언트나 Oracle Reports 및 Oracle Forms에서 호출할 수 있습니다.

참고:

"Tools for Database Developers"
PL/SQL에 대한 완전한 정보를 제공하는 Oracle Database PL/SQL Language Reference, 패키지 포함

PL/SQL 서브프로그램

PL/SQL 서브프로그램은 호출자가 입력 전용, 출력 전용 또는 입력 및 출력 값을 제공할 수 있는 매개변수를 허용하는 이름이 있는 PL/SQL 블록입니다.

서브프로그램은 특정 문제를 해결하거나 관련 작업을 수행하며 모듈식이고 유지 보수 가능한 데이터베이스 애플리케이션의 구성 요소로 사용됩니다. 서브프로그램은 PL/SQL 프로시저나 PL/SQL 함수 중 하나입니다. 프로시저와 함수는 동일하지만, 함수는 항상 호출자에게 단일 값을 반환하고, 프로시저는 그렇지 않습니다. 이 장에서는 PL/SQL 프로시저라는 용어가 프로시저나 함수를 모두 지칭합니다.

참고:

저장 프로시저에 대해 알아보려면 ProC/C++ Programmer's Guide 및 ProCOBOL Programmer's Guide
Oracle Database PL/SQL Language Reference

PL/SQL 서브프로그램의 장점

서버-사이드 프로그래밍은 클라이언트-사이드 프로그래밍에 비해 많은 장점이 있습니다.

장점에는 다음이 포함됩니다:

향상된 성능

애플리케이션이 네트워크를 통해 전송해야 하는 정보량이 개별 SQL 문을 실행하거나 전체 PL/SQL 블록의 텍스트를 Oracle Database에 전송하는 것에 비해 적습니다. 정보는 한 번만 전송되고 이후 사용할 때 호출되기 때문입니다.

프로시저의 컴파일된 형태는 데이터베이스에 쉽게 사용할 수 있으므로 실행 시에 컴파일이 필요하지 않습니다.

프로시저가 SGA의 공유 풀에 존재하면 데이터베이스는 디스크에서 가져올 필요 없이 즉시 실행을 시작할 수 있습니다.

메모리 할당

저장 프로시저는 Oracle Database의 공유 메모리 기능을 활용하므로 여러 사용자가 실행할 수 있도록 프로시저의 단일 복사본만 메모리에 로드하면 됩니다. 사용자 간 코드를 공유하면 애플리케이션의 데이터베이스 메모리 요구량이 크게 줄어듭니다.

향상된 생산성

저장 프로시저는 개발 생산성을 높입니다. 공통 프로시저 집합을 중심으로 애플리케이션을 설계하면 중복 코딩을 피할 수 있습니다. 예를 들어, 직원 테이블의 행을 조작하는 프로시저를 작성할 수 있습니다. 어떤 애플리케이션이든 이 프로시저를 호출할 수 있으며, SQL 문을 다시 작성할 필요가 없습니다. 데이터 관리 방법이 변경되면 프로시저만 수정하면 되며, 프로시저를 사용하는 애플리케이션은 수정할 필요가 없습니다.

저장 프로시저는 코드 재사용을 달성하는 최고의 방법일 수 있습니다. 데이터베이스에 연결하는 모든 언어로 작성된 클라이언트 애플리케이션은 저장 프로시저를 호출할 수 있으므로 모든 환경에서 최대한의 코드 재사용을 제공합니다.

무결성

저장 프로시저는 애플리케이션의 무결성과 일관성을 향상시킵니다. 공통 그룹의 프로시저를 중심으로 애플리케이션을 개발하면 코딩 오류의 가능성을 줄일 수 있습니다.

예를 들어, 서브프로그램이 정확한 결과를 반환하는지 보장하기 위해 테스트할 수 있으며, 검증된 후에는 어떤 애플리케이션에서든 재사용할 수 있습니다. 프로시저에서 참조하는 데이터 구조가 변경되면 프로시저만 다시 컴파일하면 됩니다. 프로시저를 호출하는 애플리케이션은 반드시 수정할 필요가 없습니다.

정의자의 권한으로 실행되는 프로시저를 통한 보안 강화

저장 프로시저는 데이터 보안을 강화하는 데 도움이 될 수 있습니다. 정의자의 권한으로 실행되는 PL/SQL 프로시저는 현재 사용자가 아닌 소유자의 권한으로 실행됩니다. 따라서 사용자가 정의자의 권한으로 실행되는 프로시저와 함수에만 접근할 수 있게 함으로써 데이터베이스 작업을 제한할 수 있습니다.

예를 들어, 테이블 업데이트 프로시저에 대한 액세스 권한을 사용자에게 부여하고, 테이블 자체에 대한 액세스 권한은 부여하지 않을 수 있습니다. 사용자가 프로시저를 호출하면 소유자의 권한으로 실행됩니다. 프로시저 실행 권한만 있는 사용자는 테이블 데이터에 다른 방식으로는 접근할 수 없습니다.

호출자의 권한으로 실행되는 프로시저를 통한 상속된 권한 및 스키마 컨텍스트

호출자의 권한으로 실행되는 PL/SQL 프로시저는 현재 사용자의 스키마와 현재 사용자의 권한으로 실행됩니다. 즉, 호출자의 권한으로 실행되는 프로시저는 특정 사용자나 스키마에 묶여 있지 않습니다. 호출자의 권한으로 실행되는 프로시저는 기본 데이터가 사용자 스키마에 나누어져 있을 때도 애플리케이션 로직을 중앙 집중화하기 쉽게 합니다.

예를 들어, hr_manager 사용자가 hr.employees 테이블의 업데이트 프로시저를 실행하면 급여를 업데이트할 수 있지만, hr_clerk 사용자는 동일한 프로시저를 실행해도 주소 데이터만 업데이트할 수 있습니다.

참고:

"Overview of Database Security"
PL/SQL 서브프로그램 개요에 대해 알아보려면 Oracle Database PL/SQL Language Reference
정의자 및 호출자의 권한에 대해 더 알아보려면 Oracle Database Security Guide

PL/SQL 서브프로그램 생성

독립형 저장 서브프로그램은 CREATE PROCEDURE 또는 CREATE FUNCTION 문을 사용하여 스키마 레벨에서 생성된 서브프로그램입니다. 패키지에 정의된 서브프로그램은 패키지 서브프로그램이라 하며, 패키지의 일부로 간주됩니다.

데이터베이스는 데이터 사전에서 서브프로그램을 스키마 객체로 저장합니다. 서브프로그램은 매개변수 설명을 포함한 명세와 본체를 가집니다.

예제 8-1 PL/SQL 프로시저

이 예제는 독립형 PL/SQL 프로시저 hire_employees의 생성 문 일부를 보여줍니다. 이 프로시저는 employees 테이블에 행을 삽입합니다.

CREATE PROCEDURE hire_employees 
  (p_last_name VARCHAR2, p_job_id VARCHAR2, p_manager_id NUMBER, 
   p_hire_date DATE, p_salary NUMBER, p_commission_pct NUMBER, 
   p_department_id NUMBER)
IS 
BEGIN
  INSERT INTO employees (employee_id, last_name, job_id, manager_id, hire_date, 
    salary, commission_pct, department_id)
  VALUES (emp_sequence.NEXTVAL, p_last_name, p_job_id, p_manager_id, 
          p_hire_date, p_salary, p_commission_pct, p_department_id);
END;

참고:

서브프로그램 생성 방법에 대해 배우려면 Oracle Database 2 Day Developer's Guide
CREATE PROCEDURE 문에 대해 배우려면 Oracle Database PL/SQL Language Reference

PL/SQL 서브프로그램의 실행

사용자는 여러 가지 방법으로 서브프로그램을 인터랙티브하게 실행할 수 있습니다.

옵션은 다음과 같습니다:

SQL*Plus 또는 SQL Developer와 같은 Oracle 도구 사용
Oracle Forms 또는 전처리기 애플리케이션과 같은 데이터베이스 애플리케이션의 코드에서 명시적으로 호출
다른 프로시저나 트리거의 코드에서 명시적으로 호출

다음 그래픽은 다양한 데이터베이스 애플리케이션이 hire_employees를 호출하는 것을 보여줍니다.

그림 8-1 저장 프로

시저의 다중 실행

그림 8-1에 대한 설명
그림 8-1 "저장 프로시저의 다중 실행"에 대한 설명

또한, 권한 있는 사용자는 Oracle Enterprise Manager 또는 SQL*Plus를 사용하여 다음과 같은 문을 사용하여 hire_employees 프로시저를 실행할 수 있습니다:

EXECUTE hire_employees ('TSMITH', 'CLERK', 1037, SYSDATE, 500, NULL, 20);

위의 문은 employees 테이블에 TSMITH의 새 레코드를 삽입합니다.

저장 프로시저는 본체에서 참조하는 객체에 종속됩니다. 데이터베이스는 이러한 종속성을 자동으로 추적하고 관리합니다. 예를 들어, hire_employees 프로시저가 참조하는 employees 테이블의 정의를 변경하면 프로시저가 설계된 대로 작동하는지 확인하기 위해 프로시저를 다시 컴파일해야 합니다. 일반적으로 데이터베이스는 이러한 종속성 관리를 자동으로 수행합니다.

참고:

SQL*Plus 및 SQL Developer에 대해 더 알아보려면 "Tools for Database Developers"
전처리기에 대해 더 알아보려면 "Client-Side Database Programming"
PL/SQL 서브프로그램 사용 방법에 대해 배우려면 Oracle Database PL/SQL Language Reference
EXECUTE 명령에 대해 배우려면 SQL*Plus User's Guide and Reference

PL/SQL 패키지

PL/SQL 패키지는 관련된 서브프로그램과 해당 서브프로그램이 사용하는 커서와 변수를 그룹으로 묶어 데이터베이스에 저장하여 단위로 계속 사용할 수 있게 한 것입니다. 패키지화된 서브프로그램은 애플리케이션이나 사용자가 명시적으로 호출할 수 있습니다.

Oracle Database는 데이터베이스 기능을 확장하고 SQL 기능에 PL/SQL 접근을 제공하는 많은 제공 패키지를 포함하고 있습니다. 예를 들어, UTL_HTTP 패키지는 PL/SQL과 SQL에서 HTTP 호출을 통해 인터넷의 데이터를 접근하거나 Oracle Web Server Cartridges를 호출할 수 있게 합니다. 제공 패키지를 애플리케이션 생성 시 사용할 수 있으며, 자체 저장 프로시저를 생성할 때 아이디어의 출처로 사용할 수 있습니다.

PL/SQL 패키지의 장점

PL/SQL 패키지는 애플리케이션 개발자에게 여러 가지 장점을 제공합니다.

장점에는 다음이 포함됩니다:

캡슐화

패키지를 사용하면 저장 프로시저, 변수, 데이터 타입 등을 이름이 있는 저장 단위로 그룹화할 수 있습니다. 캡슐화는 개발 중 더 나은 조직화를 제공하며, 또한 더 많은 유연성을 제공합니다. 명세를 생성하고 패키지 본체를 실제로 생성하지 않고도 공용 프로시저를 참조할 수 있습니다. 캡슐화는 권한 관리를 단순화합니다. 패키지에 대한 권한을 부여하면 패키지 구성 요소가 권한 수령자에게 접근 가능하게 됩니다.

데이터 보안

패키지 정의 방법을 사용하면 변수, 커서 및 프로시저가 공용인지 비공용인지 지정할 수 있습니다. 공용은 패키지 사용자가 직접 접근할 수 있음을 의미합니다. 비공용은 패키지 사용자에게 숨겨져 있음을 의미합니다.

예를 들어, 패키지에는 10개의 프로시저가 포함될 수 있습니다. 패키지를 정의할 때 3개의 프로시저만 공용으로 정의하여 패키지 사용자가 실행할 수 있도록 하고, 나머지 프로시저는 비공용으로 정의하여 패키지 내의 프로시저만 접근할 수 있도록 할 수 있습니다. 공용 및 비공용 패키지 변수를 PUBLIC에 대한 권한 부여와 혼동하지 마십시오.

더 나은 성능

패키지의 프로시저가 처음 호출될 때 패키지 전체가 메모리에 작은 청크로 로드됩니다. 이 로드는 단일 작업으로 완료되며, 독립형 프로시저에 필요한 별도의 로드와는 다릅니다. 관련 패키지 프로시저를 호출할 때는 메모리에 있는 컴파일된 코드를 실행하기 위해 디스크 I/O가 필요하지 않습니다.

패키지 본체는 명세에 영향을 주지 않고 교체 및 재컴파일할 수 있습니다. 따라서 패키지의 구성 요소를 참조하는 스키마 객체는 패키지 명세도 교체되지 않는 한 재컴파일할 필요가 없습니다. 패키지를 사용하면 불필요한 재컴파일을 최소화하여 전체 데이터베이스 성능에 미치는 영향을 줄일 수 있습니다.

PL/SQL 패키지 생성

패키지를 두 부분으로 나누어 생성합니다: 명세와 본체. 패키지 명세는 패키지의 모든 공용 구성 요소를 선언하며, 패키지 본체는 패키지의 모든 구성 요소(공용 및 비공용)를 정의합니다.

다음 예제는 여러 직원 관리 서브프로그램을 캡슐화하는 employees_management 패키지 명세 생성 문 일부를 보여줍니다. 패키지의 각 부분은 다른 문으로 생성됩니다.

CREATE PACKAGE employees_management AS 
  FUNCTION hire_employees (last_name VARCHAR2, job_id VARCHAR2, manager_id NUMBER, 
    salary NUMBER, commission_pct NUMBER, department_id NUMBER) RETURN NUMBER; 
  PROCEDURE fire_employees(employee_id NUMBER); 
  PROCEDURE salary_raise(employee_id NUMBER, salary_incr NUMBER); 
  no_sal EXCEPTION;
END employees_management;

명세는 hire_employees 함수, fire_employees 및 salary_raise 프로시저, no_sal 예외를 선언합니다. 이러한 모든 공용 프로그램 객체는 패키지에 접근 권한이 있는 사용자에게 사용 가능합니다.

CREATE PACKAGE BODY 문은 명세에서 선언한 객체를 정의합니다. 패키지 본체는 패키지와 동일한 스키마에 생성되어야 합니다. 패키지를 생성한 후에는 공용 프로시저나 함수를 호출하거나 패키지의 공용 예외를 발생시키는 애플리케이션을 개발할 수 있습니다.

참고:

CREATE PACKAGE 문에 대해 배우려면 Oracle Database PL/SQL Language Reference

PL/SQL 패키지 서브프로그램 실행

데이터베이스 트리거, 저장 서브프로그램, 3GL 애플리케이션 프로그램 및 Oracle 도구는 패키지 내용을 참조할 수 있습니다.

다음 그래픽은 employees_management 패키지의 프로시저와 함수를 호출하는 데이터베이스 애플리케이션을 보여줍니다.

그림 8-2 PL/SQL 패키지에서 서브프로그램 호출

그림 8-2에 대한 설명
그림 8-2 "PL/SQL 패키지에서 서브프로그램 호출"에 대한 설명

데이터베이스 애플리케이션은 필요에 따라 패키지화된 프로시저를 명시적으로 호출합니다. employees_management 패키지에 대한 권한이 부여된 후, 사용자는 패키지에 포함된 모든 프로시저를 명시적으로 실행할 수 있습니다. 예를 들어, SQL*Plus는 다음 문을 사용하여 hire_employees 패키지 프로시저를 실행할 수 있습니다:

EXECUTE employees_management.hire_employees ('TSMITH', 'CLERK', 1037, SYSDATE, 500, NULL, 20);

참고:

PL/SQL 패키지 입문서에 대해 배우려면 Oracle Database PL/SQL Language Reference
PL/SQL 패키지 코딩 방법에 대해 배우려면 Oracle Database Development Guide

PL/SQL 익명 블록

PL/SQL 익명 블록은 이름이 없는 비영구적인 PL/SQL 유닛입니다.

익명 블록의 일반적인 용도는 다음과 같습니다:

서브프로그램 및 패키지 구성 요소 호출 시작
예외 처리 격리
다른 PL/SQL 블록 내에 코드를 중첩하여 제어 관리

익명 블록은 저장 서브프로그램의 코드 재사용 장점을 가지고 있지 않습니다. 표 8-1은 두 가지 유형의 PL/SQL 유닛 간의 차이점을 요약합니다.

표 8-1 익명 블록과 서브프로그램의 차이점

PL/SQL 유닛	익명 블록	서브프로그램
이름으로 지정됩니까?	아니요	예
재사용 시마다 컴파일됩니까?	아니요	아니요
데이터베이스에 저장됩니까?	아니요	예
다른 애플리케이션에서 호출할 수 있습니까?	아니요	예
바인드 변수 값을 반환할 수 있습니까?	예	예
함수 값을 반환할 수 있습니까?	아니요	예
매개변수를 허용합니까?	아니요	예

익명 블록은 선택적인 선언 부분, 실행 부분 및 하나 이상의 선택적인 예외 처리기로 구성됩니다. 다음 샘플 익명 블록은 변수에 직원의 성을 선택하고 이름을 출력합니다.

DECLARE
  v_lname VARCHAR2(25);
BEGIN
  SELECT last_name 
    INTO v_lname
 

 FROM employees
  WHERE employee_id = 101;
  DBMS_OUTPUT.PUT_LINE('Employee last name is '||v_lname);
END;

Oracle Database는 PL/SQL 블록을 컴파일하고 SGA의 공유 풀에 저장하지만, 소스 코드나 컴파일된 버전을 데이터베이스에 저장하여 현재 인스턴스를 넘어 재사용할 수 없습니다. 트리거와 달리 익명 블록은 메모리에 로드될 때마다 컴파일됩니다. 공유 SQL은 익명 PL/SQL 블록이 공유 풀에서 재사용되고 공유될 수 있도록 하여 공유 풀에서 플러시될 때까지 재사용될 수 있게 합니다.

참고:

익명 PL/SQL 블록에 대해 더 배우려면 Oracle Database Development Guide

PL/SQL 언어 구성 요소

PL/SQL 블록에는 다양한 PL/SQL 언어 구성 요소가 포함될 수 있습니다.

이러한 구성 요소는 다음과 같습니다:

변수와 상수

이러한 구성 요소는 프로시저, 함수 또는 패키지 내에서 선언할 수 있습니다. 변수나 상수를 SQL 또는 PL/SQL 문에서 사용하여 값이 필요할 때 값을 캡처하거나 제공할 수 있습니다.

커서

커서는 프로시저, 함수 또는 패키지 내에서 명시적으로 선언하여 Oracle Database 데이터의 레코드 지향 처리를 촉진할 수 있습니다. PL/SQL 엔진은 커서를 암시적으로 선언할 수도 있습니다.

예외

PL/SQL은 PL/SQL 코드 처리 중 발생하는 내부 및 사용자 정의 오류 조건인 예외를 명시적으로 처리할 수 있습니다.

PL/SQL은 실행 시까지 전체 텍스트를 알 수 없는 동적 SQL 문을 실행할 수 있습니다. 동적 SQL 문은 실행 시 프로그램에 입력되거나 생성된 문자 문자열에 저장됩니다. 이 기술을 사용하면 범용 프로시저를 만들 수 있습니다. 예를 들어, 실행 시까지 이름이 알려지지 않은 테이블에서 작동하는 프로시저를 만들 수 있습니다.

참고:

동적 SQL에 대한 자세한 내용은 Oracle Database PL/SQL Packages and Types Reference
DBMS_SQL 패키지에서 동적 SQL 사용 방법에 대해 배우려면 Oracle Database PL/SQL Packages and Types Reference

PL/SQL 컬렉션 및 레코드

많은 프로그래밍 기법은 배열, 가방, 목록, 중첩 테이블, 집합 및 트리와 같은 컬렉션 유형을 사용합니다.

데이터베이스 애플리케이션에서 컬렉션 기법을 지원하기 위해 PL/SQL은 TABLE 및 VARRAY 데이터 유형을 제공합니다. 이러한 유형은 연관 배열, 중첩 테이블 및 가변 크기 배열을 선언할 수 있게 합니다.

컬렉션

PL/SQL 컬렉션은 동일한 유형의 요소로 구성된 순서 있는 그룹입니다.

각 요소는 컬렉션 내의 위치를 결정하는 고유한 서브스크립트를 가집니다. 컬렉션을 생성하려면 먼저 컬렉션 유형을 정의한 다음 해당 유형의 변수를 선언합니다.

컬렉션은 대부분의 3세대 프로그래밍 언어에서 발견되는 배열처럼 작동합니다. 또한, 컬렉션은 매개변수로 전달할 수 있습니다. 따라서 컬렉션을 사용하여 데이터베이스 테이블의 열이나 클라이언트-사이드 애플리케이션과 저장 서브프로그램 간에 데이터를 이동할 수 있습니다.

레코드

PL/SQL 레코드는 C, C++ 또는 Java의 struct 유형과 유사한 다른 유형의 데이터 값을 저장할 수 있는 복합 변수입니다. 레코드는 테이블 행 또는 테이블 행의 특정 열 데이터를 보유하는 데 유용합니다.

예를 들어, 이름, 급여 및 입사 날짜와 같은 직원 데이터가 있다고 가정해 봅시다. 이러한 항목은 유형이 다르지만 논리적으로 관련되어 있습니다. 각 항목에 대한 필드를 포함하는 레코드는 데이터를 논리 단위로 처리할 수 있게 합니다.

%ROWTYPE 속성을 사용하여 테이블 행이나 커서에서 가져온 행을 나타내는 레코드를 선언할 수 있습니다. 사용자 정의 레코드를 사용하면 자신만의 필드를 선언할 수 있습니다.

참고:

PL/SQL 레코드 사용 방법에 대해 배우려면 Oracle Database PL/SQL Language Reference

PL/SQL 실행 방식

PL/SQL은 인터프리터 실행과 네이티브 실행을 모두 지원합니다.

인터프리터 실행에서는 PL/SQL 소스 코드가 바이트코드 표현으로 컴파일됩니다. Oracle Database의 일부로 구현된 휴대용 가상 컴퓨터가 이 바이트코드를 실행합니다.

네이티브 실행은 계산 집약적 유닛에서 최고의 성능을 제공합니다. 이 경우 PL/SQL 유닛의 소스 코드는 주어진 플랫폼에 대한 오브젝트 코드로 직접 컴파일됩니다. 이 오브젝트 코드는 Oracle Database에 링크됩니다.

PL/SQL 엔진은 PL/SQL 유닛을 정의, 컴파일 및 실행합니다. 이 엔진은 Oracle 제품의 특별한 구성 요소이며, Oracle Database에도 포함되어 있습니다. 많은 Oracle 제품이 PL/SQL 구성 요소를 가지고 있지만, 이 주제는 Oracle Database에 저장되고 Oracle Database PL/SQL 엔진을 사용하여 처리할 수 있는 PL/SQL 유닛에 대해 다룹니다. 각 Oracle 도구의 문서는 해당 PL/SQL 기능을 설명합니다.

다음 그래픽은 Oracle Database에 포함된 PL/SQL 엔진을 보여줍니다.

그림 8-3 PL/SQL 엔진 및 Oracle Database

그림 8-3에 대한 설명
그림 8-3 "PL/SQL 엔진 및 Oracle Database"에 대한 설명

PL/SQL 유닛은 데이터베이스에 저장됩니다. 애플리케이션이 저장 프로시저를 호출하면 데이터베이스는 컴파일된 PL/SQL 유닛을 시스템 전역 영역(SGA)의 공유 풀에 로드합니다. PL/SQL 및 SQL 문 실행기가 함께 작동하여 프로시저의 문을 처리합니다.

저장 프로시저는 다른 PL/SQL 블록에서 호출할 수 있습니다. 이는 익명 블록이거나 다른 저장 프로시저일 수 있습니다. 예를 들어, Oracle Forms에서 저장 프로시저를 호출할 수 있습니다.

Oracle Database에서 실행되는 PL/SQL 프로시저는 공유 라이브러리에 저장된 C 프로그래밍 언어로 작성된 외부 프로시저나 함수를 호출할 수 있습니다. C 루틴은 Oracle Database와 별도의 주소 공간에서 실행됩니다.

참고:

공유 풀에 대해 더 알아보려면 "Shared Pool"
PL/SQL 아키텍처에 대해 배우려면 Oracle Database PL/SQL Language Reference
외부 프로시저에 대해 더 알아보려면 Oracle Database Development Guide

Oracle Database에서의 Java 개요

Java는 객체 지향 프로그래밍 언어로 자리 잡았습니다.

Java는 다음과 같은 기능을 포함합니다:

플랫폼 독립성을 제공하는 자바 가상 머신(JVM)
가비지 컬렉션과 같은 자동 저장 관리 기법
C에서 차용한 언어 문법과 강력한 타입을 강제하는 구문

참고: 이 장은 Java 언어에 대한 기본적인 지식이 있다고 가정합니다.
데이터베이스는 Java 프로그램에 동적 데이터 처리 엔진을 제공하여 복잡한 쿼리와 여러 데이터 뷰를 지원합니다. 클라이언트 요청은 즉시 처리하기 위한 데이터 쿼리로 조립됩니다. 쿼리 결과는 동적으로 생성됩니다.

Java와 Oracle Database의 조합은 비즈니스 요구가 변경될 때 쉽게 업데이트할 수 있는 구성 요소 기반 네트워크 중심 애플리케이션을 만들 수 있게 합니다. 또한, 애플리케이션과 데이터 저장소를 데스크톱에서 제거하여 지능형 네트워크와 네트워크 중심 서버로 이동할 수 있습니다. 더 중요한 것은, 이러한 애플리케이션과 데이터 저장소에 어떤 클라이언트 장치에서든 접근할 수 있다는 점입니다.

다음 그림은 클라이언트가 PL/SQL 서브프로그램을 호출하는 것과 동일한 방식으로 Java 저장 프로시저를 호출하는 전통적인 2계층 클라이언트/서버 구성을 보여줍니다.

그림 8-4 이계층 클라이언트/서버 구성

그림 8-4에 대한 설명
"그림 8-4 이계층 클라이언트/서버 구성"에 대한 설명

참고:

Oracle Database 2 Day + Java Developer's Guide: Oracle Database와 함께 Java를 사용하는 방법에 대한 입문서

Java 가상 머신(JVM) 개요

JVM은 컴파일된 Java 코드를 실행하는 가상 프로세서입니다.

Java 소스 코드는 플랫폼 독립적인 바이트코드로 알려진 저수준 기계 명령어로 컴파일됩니다. Java 바이트코드는 JVM을 통해 플랫폼 의존적 작업으로 해석됩니다.

Oracle JVM 개요

Oracle JVM은 순수 Java 애플리케이션을 실행하는 표준 Java 호환 환경입니다. 이는 JLS 및 JVM 사양과 호환됩니다.

Oracle JVM은 표준 Java 바이너리 형식과 API를 지원합니다. 또한, Oracle Database는 런타임에 동적 클래스 로딩을 포함한 표준 Java 언어 의미론을 준수합니다.

다음 그림은 Oracle Java 애플리케이션이 Oracle JVM 상단에 있는 Java 코어 클래스 라이브러리 위에 위치하는 구조를 보여줍니다. Oracle Java 지원 시스템은 데이터베이스 내에 위치하므로 JVM은 운영 체제와 직접 상호 작용하는 대신 데이터베이스 라이브러리와 상호 작용합니다.

그림 8-5 Java 구성 요소 구조

그림 8-5에 대한 설명
"그림 8-5 Java 구성 요소 구조"에 대한 설명

다른 Java 환경과 달리, Oracle JVM은 Oracle Database에 내장되어 있습니다. Oracle JVM과 일반적인 클라이언트 JVM 간에는 중요한 차이가 있습니다. 예를 들어, 표준 Java 환경에서는 다음과 같은 명령을 명령줄에 입력하여 인터프리터를 통해 Java 애플리케이션을 실행합니다. 여기서 classname은 JVM이 먼저 해석하는 클래스의 이름입니다.

java classname

위의 명령은 애플리케이션을 운영 체제의 프로세스 내에서 실행하게 합니다. 그러나 명령줄 인터페이스를 사용하지 않는 경우 애플리케이션을 데이터베이스에 로드하고 인터페이스를 공개한 다음 데이터베이스 데이터 사전 내에서 애플리케이션을 실행해야 합니다.

참고:

Oracle Database Java Developer’s Guide: Oracle JVM과 일반적인 클라이언트 JVM의 다른 점에 대한 설명

Oracle JVM의 주요 구성 요소

Oracle JVM은 데이터베이스 커널과 동일한 프로세스 공간과 주소 공간에서 실행되며, 메모리 힙을 공유하고 관계형 데이터를 직접 액세스합니다. 이러한 설계는 메모리 사용을 최적화하고 처리량을 증가시킵니다.

Oracle JVM은 Java 객체에 대한 런타임 환경을 제공합니다. Java 데이터 구조, 메서드 디스패치, 예외 처리 및 언어 수준 스레드를 완전히 지원합니다. 또한, java.lang, java.io, java.net, java.math 및 java.util을 포함한 모든 핵심 Java 클래스 라이브러리를 지원합니다.

다음 그림은 Oracle JVM의 주요 구성 요소를 보여줍니다.

그림 8-6 Oracle JVM의 주요 구성 요소

그림 8-6에 대한 설명
"그림 8-6 Oracle JVM의 주요 구성 요소"에 대한 설명

Oracle JVM은 표준 Java 네임스페이스를 데이터베이스 스키마에 내장합니다. 이 기능을 통해 Java 프로그램은 Oracle Database와 엔터프라이즈 전체의 애플리케이션 서버에 저장된 Java 객체에 접근할 수 있습니다.

또한, Oracle JVM은 데이터베이스의 확장 가능한 공유 메모리 아키텍처와 긴밀하게 통합되어 있습니다. Java 프로그램은 사용자 개입 없이 호출, 세션 및 객체 수명을 효율적으로 사용합니다. 따라서 Oracle JVM 및 중간 계층 Java 비즈니스 객체는 세션 단위 상태를 가지고 있어도 확장 가능합니다.

참고:

Oracle Database Java Developer’s Guide: Oracle JVM의 주요 구성 요소 설명

Java 프로그래밍 환경

Oracle은 엔터프라이즈 애플리케이션 개발자에게 Java 애플리케이션을 생성, 배포 및 관리하기 위한 종합적인 Java 솔루션을 제공합니다.

이 솔루션은 클라이언트-사이드 및 서버-사이드 프로그래밍 인터페이스, Java 개발을 지원하는 도구 및 Oracle Database와 통합된 Java Virtual Machine으로 구성됩니다. 이 모든 제품은 Java 표준과 호환됩니다.

Java 프로그래밍 환경은 다음과 같은 추가 기능을 포함합니다:

PL/SQL의 동등물 및 동반자인 Java 저장 프로시저. Java 저장 프로시저는 PL/SQL과 긴밀하게 통합됩니다. Java 저장 프로시저에서 PL/SQL 패키지와 프로시저를 호출할 수 있습니다.
SQL 데이터 접근을 위한 JDBC 및 SQLJ 프로그래밍 인터페이스.
클래스 개발, 로딩 및 관리를 지원하는 도구와 스크립트.

Java 저장 프로시저

Java 저장 프로시저는 SQL에 게시되고 데이터베이스에 저장된 Java 메서드입니다.

PL/SQL 서브프로그램과 마찬가지로, Java 프로시저는 SQL*Plus와 같은 제품으로 직접 호출하거나 트리거로 간접적으로 호출할 수 있습니다. Oracle Net 클라이언트—OCI, 전처리기 또는 JDBC—를 통해 접근할 수 있습니다.

Java 메서드를 게시하려면 Java 메서드 이름, 매개변수 유형 및 반환 유형을 SQL 대응 항목에 매핑하는 호출 사양을 작성합니다. 클라이언트 애플리케이션에서 호출되면, Java 저장 프로시저는 인수를 받아들이고, Java 클래스를 참조하며, Java 결과 값을 반환할 수 있습니다.

애플리케이션은 호출 사양의 이름을 참조하여 Java 메서드를 호출합니다. 런타임 시스템은 Oracle 데이터 사전에서 호출 사양 정의를 찾아 해당 Java 메서드를 실행합니다.

또한, PL/SQL과 독립적으로 강력한 프로그램을 개발할 수 있도록 Java를 사용할 수 있습니다. Oracle Database는 Java 프로그래밍 언어 및 JVM의 완전한 구현을 제공합니다.

참고:

Oracle Database Java Developer’s Guide: Java로 저장 프로시저를 작성하는 방법, PL/SQL에서 접근하는 방법 및 Java에서 PL/SQL 기능에 접근하는 방법 설명

Java와 PL/SQL 통합

기존 PL/SQL 프로그램을 Java에서 호출할 수 있으며, Java 프로그램을 PL/SQL에서 호출할 수 있습니다. 이 솔루션은 PL/SQL과 Java 코드를 보호하고 활용합니다.

Oracle Database는 Java에서 SQL 데이터를 접근하는 두 가지 접근 방식을 제공합니다: JDBC와 SQLJ. JDBC는 클라이언트와 서버 모두에서 사용할 수 있으며, SQLJ는 클라이언트에서만 사용할 수 있습니다.

JDBC 드라이버

JDBC는 데이터베이스에 연결하고 SQL 문과 쿼리를 실행할 수 있게 하는 데이터베이스 접근 프로토콜입니다.

핵심 Java 클래스 라이브러리는 단 하나의 JDBC API인 java.sql만 제공합니다. 그러나 JDBC는 공급업체가 특정 데이터베이스에 필요한 특수화를 제공하는 드라이버를 제공할 수 있게 설계되었습니다. Oracle은 다음 표에 나열된 독특한 JDBC 드라이버를 제공합니다.

표 8-2 JDBC 드라이버

드라이버	설명
JDBC Thin 드라이버	JDBC Thin 드라이버를 사용하여 Oracle SQL 데이터에 접근하는 순수 Java 애플리케이션 및 애플릿을 작성할 수 있습니다. JDBC Thin 드라이버는 웹 기반 애플리케이션 및 애플릿에 특히 적합합니다. 웹 페이지에서 다른 Java 애플릿처럼 동적으로 다운로드할 수 있기 때문입니다.
JDBC OCI 드라이버	JDBC OCI 드라이버는 Oracle 특정 네이티브 코드, 즉 비-Java 코드 및 라이브러리에 접근합니다. 이는 성능 향상을 제공하지만, JDBC Thin 드라이버보다 크기가 상당히 크고 클라이언트-사이드 설치가 필요합니다.
JDBC 서버-사이드 내부 드라이버	Oracle Database는 Java 코드가 서버에서 실행될 때 서버-사이드 내부 드라이버를 사용합니다. 이는 서버에서 Oracle JVM에서 실행되는 Java 애플리케이션이 동일한 시스템 및 동일한 프로세스의 로컬 정의 데이터를 JDBC로 접근할 수 있게 합니다. 이는 Java 코드와 SQL 데이터 간의 네트워크 연결 간의 오버헤드 없이 기본 Oracle RDBMS 라이브러리를 직접 사용할 수 있으므로 성능 향상을 제공합니다. 서버에서 동일한 Java-SQL 인터페이스를 지원하여 코드를 배포할 때 재작업이 필요 없습니다.

참고:

"ODBC 및 JDBC"
Oracle Database 2 Day + Java Developer's Guide 및 Oracle Database JDBC Developer’s Guide

SQLJ

SQLJ는 Java 프로그램에 SQL 문을 포함시키기 위한 ANSI 표준입니다. 클라이언트-사이드 SQLJ 프로그램을 사용할 수 있습니다. 또한, SQLJ 프로그램을 JDBC와 결합할 수 있습니다.

참고: Oracle Database 12c 릴리스 2 (12.2)부터 Oracle Database는 데이터베이스 환경에서 서버-사이드 SQLJ 코드 실행을 지원하지 않습니다. 여기에는 저장 프로시저, 함수 및 트리거 실행이 포함됩니다.
SQLJ는 Java에서 데이터베이스에 접근하는 클라이언트-사이드 및 중간 계층 애플리케이션을 개발하는 간단하지만 강력한 방법을 제공합니다. 개발자는 SQLJ를 사용하여 프로그램을 작성한 다음 SQLJ 번역기를 사용하여 임베디드 SQL을 순수 JDBC 기반 Java 코드로 번역합니다. 런타임 시 프로그램은 표준 JDBC 드라이버를 사용하여 다중 공급업체 데이터베이스와 통신할 수 있습니다.

다음 예제는 간단한 SQLJ 실행 문을 보여줍니다:

String name;
#sql  { SELECT first_name INTO :name FROM employees WHERE employee_id=112 };
System.out.println("Name is " + name + ", employee number = " + employee_id);

참고:

"SQLJ"
Oracle Database SQLJ Developer’s Guide

트리거 개요

데이터베이스 트리거는 PL/SQL 또는 Java로 작성된 컴파일된 저장 프로그램 유닛으로, 특정 상황에서 Oracle Database가 자동으로 호출("발생")합니다.

다음 작업이 발생할 때마다 트리거가 발생합니다:

특정 테이블 또는 뷰에 대해 모든 사용자가 발행한 DML 문

DML 문은 스키마 객체의 데이터를 수정합니다. 예를 들어, 행 삽입 및 삭제는 DML 작업입니다.

특정 사용자 또는 모든 사용자가 발행한 DDL 문

DDL 문은 스키마 객체를 정의합니다. 예를 들어, 테이블 생성 및 열 추가는 DDL 작업입니다.

데이터베이스 이벤트

사용자 로그인 또는 로그오프, 오류, 데이터베이스 시작 또는 종료는 트리거를 호출할 수 있는 이벤트입니다.

트리거는 서브프로그램과 유사한 스키마 객체이지만 호출 방식이 다릅니다. 서브프로그램은 사용자, 애플리케이션 또는 트리거에 의해 명시적으로 실행됩니다. 트리거는 트리거 이벤트가 발생하면 데이터베이스에 의해 암시적으로 호출됩니다.

참고:

DML 및 DDL에 대해 배우려면 "Overview of SQL Statements"
"Overview of Database Instance Startup and Shutdown"

트리거의 장점

트리거를 올바르게 사용하면 더 견고하고 데이터베이스를 효과적으로 사용하는 애플리케이션을 구축 및 배포할 수 있습니다.

트리거를 사용하여 다음을 할 수 있습니다:

파생된 열 값을 자동으로 생성
유효하지 않은 트랜잭션 방지
감사 및 이벤트 로깅 제공
테이블 접근에 대한 정보 기록

트리거를 사용하여 모든 클라이언트 애플리케이션에 공통된 저수준 비즈니스 규칙을 강제할 수 있습니다. 예를 들어, 여러 애플리케이션이 직원 테이블에 접근할 수 있습니다. 이 테이블에 대한 트리거가 삽입된 데이터의 형식을 보장하면, 이 비즈니스 로직을 각 클라이언트에서 재현할 필요가 없습니다. 애플리케이션이 트리거를 우회할 수 없기 때문에 트리거의 비즈니스 로직이 자동으로 사용됩니다.

트리거와 무결성 제약 조건을 모두 사용하여 모든 유형의 무결성 규칙을 정의하고 강제할 수 있습니다. 그러나 Oracle은 복잡한 비즈니스 규칙을 강제하기 위해서만 트리거를 사용하고 무결성 제약 조건으로 정의할 수 없는 규칙을 정의하도록 강력히 권장합니다.

트리거를 과도하게 사용하면 유지 관리하기 어려운 복잡한 상호 의존성이 발생할 수 있습니다. 예를 들어, 트리거가 호출되면 트리거 동작 내의 SQL 문이 다른 트리거를 호출할 수 있으며, 이는 예상치 못한 결과를 초래할 수 있습니다.

참고:

"Introduction to Data Integrity"
Oracle Database 2 Day Developer's Guide
애플리케이션 트리거 계획 시 가이드라인 및 제한 사항에 대해서는 Oracle Database PL/SQL Language Reference

트리거의 유형

트리거는 호출 방식과 수행하는 작업 유형에 따라 분류할 수 있습니다.

Oracle Database는 다음과 같은 유형의 트리거를 지원합니다:

행 트리거

행 트리거는 트리거 문이 테이블에 영향을 미칠 때마다 발생합니다. 예를 들어, 문이 여러 행을 업데이트하면, 행 트리거는 UPDATE로 영향을 받는 각 행마다 한 번씩 발생합니다. 트리거 문이 영향을 미치지 않으면 행 트리거는 실행되지 않습니다. 행 트리거는 트리거 동작의 코드가 트리거 문이나 영향을 받는 행에서 제공하는 데이터에 의존할 때 유용합니다.

문 트리거

문 트리거는 트리거 문을 대신하여 한 번 실행되며, 트리거 문에 의해 영향을 받는 행 수와 관계없이 발생합니다. 예를 들어, 문이 테이블에서 100개의 행을 삭제하면, 문 수준 DELETE 트리거는 한 번만 발생합니다. 문 트리거는 트리거 동작의 코드가 트리거 문이나 영향을 받는 행에서 제공하는 데이터에 의존하지 않을 때 유용합니다.

INSTEAD OF 트리거

INSTEAD OF 트리거는 트리거 문을 실행하는 대신 Oracle Database에 의해 발생합니다. 이러한 트리거는 DML 문을 통해 직접 수정할 수 없는 뷰를 투명하게 수정하는 데 유용합니다.

이벤트 트리거

트리거를 사용하여 데이터베이스 이벤트에 대한 정보를 구독자에게 게시할 수 있습니다. 이벤트 트리거는 다음과 같은 카테고리로 나뉩니다:

시스템 이벤트 트리거는 데이터베이스 인스턴스 시작 및 종료 또는 오류 메시지와 같은 이벤트로 인해 발생할 수 있습니다.
사용자 이벤트 트리거는 사용자 로그인 및 로그오프, DDL 문 및 DML 문과 관련된 이벤트로 인해 발생합니다.

참고:

Oracle Database 2 Day Developer's Guide
Oracle Database PL/SQL Language Reference

트리거의 타이밍

트리거 동작이 트리거 문 이전에 실행될지 이후에 실행될지를 트리거 타이밍으로 정의할 수 있습니다.

간단한 트리거는 단일 테이블에 대해 하나의 타이밍 지점에 대한 동작을 지정할 수 있는 단일 트리거입니다:

트리거 문 이전
트리거 문에 의해 영향을 받는 각 행 이전
트리거 문에 의해 영향을 받는 각 행 이후
트리거 문 이후

문 트리거와 행 트리거의 경우, BEFORE 트리거는 데이터베이스 변경 전 보안 강화를 위해 사용되며 AFTER 트리거는 작업 로그에 이상적입니다.

복합 트리거는 여러 타이밍 지점에서 발생할 수 있습니다. 복합 트리거는 다양한 타이밍 지점에 구현한 동작이 공통 데이터를 공유하는 접근 방식을 프로그래밍하는 데 도움이 됩니다.

참고:

복합 트리거에 대해 배우려면 Oracle Database PL/SQL Language Reference

트리거 생성

CREATE TRIGGER 문은 데이터베이스 트리거를 생성하거나 대체합니다.

PL/SQL 트리거는 다음과 같은 일반적인 구문 형식을 가집니다:

CREATE TRIGGER trigger_name
  triggering_statement
  [trigger_restriction]
BEGIN
 triggered_action;
END;

PL/SQL 트리거는 다음과 같은 기본 구성 요소를 가집니다:

트리거 이름

이름은 동일한 스키마의 다른 트리거 이름과 고유해야 합니다. 예를 들어, 이름은 part_reorder_trigger일 수 있습니다.

트리거 이벤트 또는 문

트리거 이벤트 또는 문은 트리거를 호출하는 SQL 문, 데이터베이스 이벤트 또는 사용자 이벤트입니다. 예를 들어, 사용자가 테이블을 업데이트합니다.

트리거 제한

트리거 제한은 트리거가 발생하기 위한 부울 식을 지정합니다. 예를 들어, 사용 가능한 부품 수가 설정된 재주문 수량보다 적지 않으면 트리거가 호출되지 않습니다.

트리거 동작

트리거 동작은 트리거 문이 발행되고 트리거 제한이 참으로 평가될 때 실행되는 SQL 문과 코드를 포함하는 프로시저입니다. 예를 들어, 사용자가 보류 중인 주문 테이블에 행을 삽입합니다.

참고:

트리거 생성 방법에 대해 배우려면 Oracle Database 2 Day Developer's Guide 및 Oracle Database PL/SQL Language Reference
CREATE TRIGGER 문에 대해 배우려면 Oracle Database SQL Language Reference

예제: CREATE TRIGGER 문

이 예제는 line items 테이블에서 INSERT, UPDATE 또는 DELETE 문이 실행될 때 발생하는 트리거를 생성합니다.

다음 문을 사용하여 orders 및 lineitems 테이블을 생성한다고 가정합니다. orders 테이블에는 고유한 각 주문에 대한 행이 포함되고, lineitems 테이블에는 주문의 각 항목에 대한 행이 포함됩니다.

CREATE TABLE orders
( order_id NUMBER PRIMARY KEY,
  /* other attributes */
  line_items_count NUMBER DEFAULT 0 );

CREATE TABLE lineitems
( order_id REFERENCES orders,
  seq_no   NUMBER,
  /* other attributes */
  CONSTRAINT lineitems PRIMARY KEY(order_id,seq_no) );

다음 문은 주문의 항목 수로 orders 테이블을 자동으로 업데이트하는 샘플 트리거를 생성합니다:

CREATE OR REPLACE TRIGGER lineitems_trigger
  AFTER INSERT OR UPDATE OR DELETE ON lineitems 
  FOR EACH ROW
BEGIN
  IF (INSERTING OR UPDATING)
  THEN
    UPDATE orders SET line_items_count = NVL(line_items_count,0)+1
    WHERE order_id = :new.order_id;
  END IF;
  IF (DELETING OR UPDATING)
  THEN
    UPDATE orders SET line_items_count = NVL(line_items_count,0)-1
    WHERE order_id = :old.order_id;
  END IF;
END;
/

lineitems_trigger에서 트리거 문은 lineitems 테이블의 INSERT, UPDATE 또는 DELETE입니다. 트리거 제한은 없습니다. 트리거는 변경된 각 행에 대해 호출됩니다. 트리거는 트리거 문에 의해 영향을 받는 현재 행의 이전 및 새로운 열 값에 접근할 수 있습니다. 수정된 테이블의 각 열에는 두 개의 상관 이름이 있습니다: 이전 값(:old)과 새 값(:new). 세션이 주문에 대한 lineitems에 행을 업데이트하거나 삽입하면, 동작 후 트리거는 이 주문의 항목 수를 계산하고 orders 테이블을 해당 수로 업데이트합니다.

예제: 행 수준 트리거 호출

이 시나리오에서는 고객이 두 개의 주문을 시작하고 주문에서 항목을 추가 및 제거합니다.

이 시나리오는 예제: CREATE TRIGGER 문에서 생성한 트리거를 기반으로 합니다.

표 8-3 행 수준 트리거 시나리오

SQL 문	트리거된 SQL 문	설명
SQL> INSERT INTO orders (order_id) VALUES (78); 1 row created.		고객이 ID 78인 주문을 생성합니다. 이 시점에서 고객은 주문에 항목이 없습니다. lineitems 테이블에서 작업이 수행되지 않았으므로 트리거가 호출되지 않습니다.
SQL> INSERT INTO orders (order_id) VALUES (92); 1 row created.		고객이 ID 92인 별도의 주문을 생성합니다. 이 시점에서 고객은 주문에 항목이 없습니다. lineitems 테이블에서 작업이 수행되지 않았으므로 트리거가 호출되지 않습니다.
SQL> INSERT INTO lineitems (order_id, seq_no) VALUES (78,1); 1 row created.	UPDATE orders SET line_items_count = NVL(NULL,0)+1 WHERE order_id = 78;	고객이 주문 78에 항목을 추가합니다. INSERT는 트리거를 호출합니다. 트리거된 문은 주문 78의 항목 수를 0에서 1로 증가시킵니다.
SQL> INSERT INTO lineitems (order_id, seq_no) VALUES (78,2); 1 row created.	UPDATE orders SET line_items_count = NVL(1,0)+1 WHERE order_id = 78;	고객이 주문 78에 추가 항목을 추가합니다. INSERT는 트리거를 호출합니다. 트리거된 문은 주문 78의 항목 수를 1에서 2로 증가시킵니다.
SQL> SELECT * FROM orders; ORDER_ID LINE_ITEMS_COUNT --------- ---------------- 78 2 92 0		고객이 두 주문의 상태를 조회합니다. 주문 78에는 두 개의 항목이 포함됩니다. 주문 92에는 항목이 포함되지 않습니다.
SQL> SELECT * FROM lineitems; ORDER_ID SEQ_NO ---------- ---------- 78 1 78 2		고객이 line items의 상태를 조회합니다. 각 항목은 주문 ID와 순서 번호로 고유하게 식별됩니다.
SQL> UPDATE lineitems SET order_id = 92; 2 rows updated.	UPDATE orders SET line_items_count = NVL(NULL,0)+1 WHERE order_id = 92; UPDATE orders SET line_items_count = NVL(2,0)-1 WHERE order_id = 78; UPDATE orders SET line_items_count = NVL(1,0)+1 WHERE order_id = 92; UPDATE orders SET line_items_count = NVL(1,0)-1 WHERE order_id = 78;	고객이 주문 78의 항목을 주문 92로 이동합니다. UPDATE 문은 lineitems 테이블에서 2개의 행을 변경하여 각 행에 대해 한 번씩 트리거를 호출합니다. 트리거가 호출될 때마다 트리거의 두 조건이 모두 충족됩니다. 첫 번째 조건은 주문 92의 수를 증가시키고, 두 번째 조건은 주문 78의 수를 감소시킵니다. 따라서 총 네 개의 UPDATE 문이 실행됩니다.
SQL> SELECT * FROM orders; ORDER_ID LINE_ITEMS_COUNT --------- ---------------- 78 0 92 2		고객이 두 주문의 상태를 조회합니다. 최종 결과는 주문 92의 항목 수가 0에서 2로 증가하고, 주문 78의 항목 수가 2에서 0으로 감소합니다.
SQL> SELECT * FROM lineitems; ORDER_ID SEQ_NO ---------- ---------- 92 1 92 2		고객이 line items의 상태를 조회합니다. 각 항목은 주문 ID와 순서 번호로 고유하게 식별됩니다.
SQL> DELETE FROM lineitems; 2 rows deleted.	UPDATE orders SET line_items_count = NVL(2,0)-1 WHERE order_id = 92; UPDATE orders SET line_items_count = NVL(1,0)-1 WHERE order_id = 92;	고객이 모든 주문에서 모든 항목을 제거합니다. DELETE 문은 lineitems 테이블에서 2개의 행을 변경하여 각 행에 대해 한 번씩 트리거를 호출합니다. 각 트리거 호출 시 트리거의 한 조건만 충족됩니다. 각 조건은 주문 92의 수를 1씩 감소시킵니다. 따라서 총 두 개의 UPDATE 문이 실행됩니다.
SQL> SELECT * FROM orders; ORDER_ID LINE_ITEMS_COUNT --------- ---------------- 78 0 92 0		고객이 두 주문의 상태를 조회합니다. 어떤 주문에도 line items가 포함되지 않습니다. 고객이 line items의 상태도 조회합니다. 항목이 존재하지 않습니다.

트리거 실행

Oracle Database는 트리거를 서브프로그램 실행과 동일한 단계로 내부적으로 실행합니다.

유일한 미묘한 차이점은 사용자가 트리거 문을 실행할 권한이 있는 경우 트리거를 호출할 권리가 있다는 것입니다. 이 예외를 제외하고, 데이터베이스는 저장된 서브프로그램과 동일한 방식으로 트리거를 검증하고 실행합니다.

참고:

트리거 실행에 대해 더 배우려면 Oracle Database PL/SQL Language Reference

트리거 저장

Oracle Database는 PL/SQL 트리거를 컴파일된 형태로 데이터베이스 스키마에 저장하며, 이는 PL/SQL 저장 프로시저와 동일합니다.

CREATE TRIGGER 문이 커밋되면, 컴파일된 PL/SQL 코드는 데이터베이스에 저장됩니다. 공유 풀은 PL/SQL 트리거의 소스 코드를 제거합니다.

다음 그래픽은 SQL 문이 암시적으로 PL/SQL 트리거를 호출하는 데이터베이스 애플리케이션을 보여줍니다. 트리거는 관련 테이블과 별도로 저장됩니다.

그림 8-7 트리거

그림 8-7에 대한 설명
"그림 8-7 트리거"에 대한 설명

Java 트리거는 PL/SQL 트리거와 동일한 방식으로 저장됩니다. 그러나 Java 트리거는 CALL 문으로 별도로 컴파일된 Java 코드를 참조합니다. 따라서 Java 트리거를 생성하려면 Java 코드를 생성하고 이 Java 코드를 참조하는 트리거를 생성해야 합니다.

참고:

트리거 컴파일 및 저장에 대해 배우려면 Oracle Database PL/SQL Language Reference

torch

비전공 개발 공부 이야기

이전 포스트

[오라클] Oracle® Database Concepts 챕터 7 한글 번역

다음 포스트