맨위로가기

PL/SQL

"오늘의AI위키"는 AI 기술로 일관성 있고 체계적인 최신 지식을 제공하는 혁신 플랫폼입니다.
"오늘의AI위키"의 AI를 통해 더욱 풍부하고 폭넓은 지식 경험을 누리세요.
목차

1. 개요

PL/SQL은 SQL 명령어로 처리하기 어려운 복잡한 데이터 저장, 프로시저, 트리거 등을 작성하기 위해 사용되는 절차적 프로그래밍 언어이다. 에이다 프로그래밍 언어를 기반으로 하며, 오라클 데이터베이스에서 주로 사용된다. PL/SQL은 프로시저, 함수, 패키지, 트리거 등 다양한 프로그램 단위를 지원하며, 블록 구조를 통해 코드의 구조화와 예외 처리를 용이하게 한다. PL/SQL은 데이터 형식, 조건문, 배열 처리, 커서, 루프, 동적 SQL 등을 제공하며, 다른 데이터베이스 언어와 비교하여 특징을 가진다.

더 읽어볼만한 페이지

  • 데이터 중심 프로그래밍 언어 - 마이크로소프트 액세스
    마이크로소프트 액세스는 1992년 출시된 데이터베이스 관리 시스템으로, 테이블, 쿼리, 폼 등을 생성하고 VBA를 통해 솔루션을 개발하며, 윈도우에서 사용 가능하고 다양한 데이터 형식과 통합된다.
  • 데이터 중심 프로그래밍 언어 - SQL
    SQL은 관계형 데이터베이스 관리 시스템에서 데이터를 관리하고 조작하기 위해 설계된 표준 프로그래밍 언어로서, 데이터 정의어, 데이터 조작어, 데이터 제어어를 포함하는 다양한 명령어 문법을 제공하며 ANSI와 ISO에 의해 표준으로 채택되었다.
  • 오라클 코퍼레이션 - 오라클 파크
    오라클 파크는 샌프란시스코 자이언츠의 홈 구장으로, 맥코비 코브와 인접한 독특한 입지 조건과 스플래시 히트 존으로 유명하며 야구 외 다양한 스포츠 및 문화 행사가 개최되는 장소이다.
  • 오라클 코퍼레이션 - Btrfs
    Btrfs는 내결함성, 복구 기능, 쉬운 관리를 특징으로 하는 카피-온-라이트 방식의 파일 시스템으로, 동적 아이노드 할당, 쓰기 가능한 스냅샷, 서브볼륨, 압축, 온라인 조각 모음 등의 기능을 제공하며 데이터 무결성 보장 및 오류 자동 수정이 가능하다.
  • 오라클 소프트웨어 - 자바 데스크톱 시스템
  • 오라클 소프트웨어 - 스타오피스
    스타오피스는 1985년 스타라이터에서 시작하여 다양한 플랫폼으로 확장되고 썬 마이크로시스템즈에 인수되어 OpenOffice.org의 기반이 되었던 오피스 스위트였으나, 오라클에 인수 후 오라클 오픈 오피스로 변경되었다가 판매가 중단되었으며, 워드 프로세서, 스프레드시트, 프레젠테이션 프로그램 등을 포함하고 있었다.
PL/SQL
개요
종류데이터베이스 프로그래밍 언어
개발자오라클
첫 공개1991년
구현체오라클 데이터베이스
영향 받은 언어에이다
델파이
특징SQL의 절차적 확장
세부 정보
자료형BLOB
문자열
날짜
숫자
확장자.pll, .pld, .sql

2. 특징

PL/SQL은 SQL 명령문만으로 처리하기 어려운 복잡한 데이터 저장, 프로시저, 트리거 등을 작성하는 데 사용된다. 에이다 프로그래밍 언어의 구조를 본떠 만들어졌으며, 파스칼의 구문과 유사하다.[16] C, C++, 파스칼, 포트란 등 범용 프로그래밍 언어와는 달리 오라클 관계형 데이터베이스(RDBMS)에서만 사용된다.

SQL은 비절차적 언어이기 때문에 제어문(결정 또는 반복 제어)을 사용할 수 없지만, PL/SQL은 의사 결정, 반복 등과 같은 절차적 프로그래밍 언어의 기능을 제공한다.[4] PL/SQL 프로그램 유닛에는 PL/SQL 익명 블록, 프로시저, 함수, 패키지 사양, 패키지 본문, 트리거, 유형 사양, 유형 본문, 라이브러리 등이 있다.[4]

다음은 다른 관계형 데이터베이스의 확장 언어들이다.[4]

관계형 데이터베이스확장 언어
마이크로소프트 SQL 서버트랜잭트 SQL(Transact-SQL; T-SQL)
PostgreSQLPL/pgSQL
IBM Db2SQL Procedural (SQL/PSM 표준 준수)
MariaDBPL/SQL (호환성 파서 포함)[14]



PL/SQL은 오브젝트 지향 프로그래밍 언어인 오브젝트 파스칼, C++, Java처럼 클래스 정의와 객체로 인스턴스화하는 것을 허용한다. PL/SQL에서 클래스는 "추상 데이터 타입"(ADT) 또는 "사용자 정의 타입"(UDT)이라고 불리며, 오라클 SQL 데이터 타입으로 정의되어 오라클 SQL 엔진과 PL/SQL 엔진 모두에서 사용될 수 있다.[16]

PL/SQL은 Transact-SQL과 문법이 비슷해 보이지만, 기능 집합[17], 오라클과 SQL Server의 동시성 및 잠금 처리 방식의 차이 등으로 인해 코드 이식에는 상당한 작업이 필요하다.[17]

데이터베이스 언어 SQL의 확장으로서, Sybase ASE나 Microsoft SQL Server에서 구현된 Transact-SQL과 비교되기도 하지만, 문법 및 구문의 차이뿐만 아니라 명령어의 SQL 엔진에서의 처리 방식도 다르다. Transact-SQL은 모든 구문을 SQL 엔진만으로 처리하지만, PL/SQL의 경우 PL/SQL 구문은 PL/SQL 엔진에서 분석 및 실행되며, 데이터 조작 언어(DML; SELECT/INSERT/UPDATE/DELETE) 구문은 기존 SQL 엔진에서 분석 및 실행되도록 내부적으로 분기된다.[20]

3. PL/SQL 프로그램 단위

PL/SQL 프로그램 단위는 다음과 같다.


  • PL/SQL 익명 블록
  • 함수
  • 프로시저
  • 패키지
  • * 패키지 사양
  • * 패키지 바디
  • 트리거


SQL(비절차적)은 제어문(결정 또는 반복 제어)을 사용할 수 없다는 단점이 있다. PL/SQL은 이러한 기능을 제공하여 절차적 프로그래밍을 가능하게 한다.[4] 프로그램 유닛은 개발, 컴파일 및 데이터베이스에서 실행되는 PL/SQL 소스 코드이다.[4]

PL/SQL 소스 프로그램의 기본 단위는 블록이며, 선언과 문을 함께 묶는다. PL/SQL 블록은 DECLARE, BEGIN, EXCEPTION, END 키워드로 정의되며, 각각 선언부, 실행부, 예외 처리부로 나뉜다. 선언부는 선택 사항이며 변수를 정의하고 초기화하는 데 사용된다. 변수가 초기화되지 않으면 기본적으로 NULL 값이 된다. 실행부는 필수이며, 최소 하나의 실행문을 포함해야 한다. 예외 처리부는 런타임 오류를 처리하며 선택 사항이다. 블록은 레이블을 가질 수 있다.[5]

```PLpgSQL

<

4. 자료형

PL/SQL의 주요 데이터 형식에는 NUMBER, CHAR, VARCHAR2, DATE, TIMESTAMP 등이 있다.

PL/SQL은 기본적인 자료형 외에도 다음과 같은 다양한 자료형을 지원한다.


  • 중첩된 표 (배열형): 첨자가 연속되지 않아도 되며, 리스트 구조와 같이 접근할 수 있다.
  • 레코드형: 구조체와 유사하다.
  • 레코드형의 배열: 레코드 형을 배열로 묶은 형태이다.
  • 인덱스된 표 (연관 배열): 해시 테이블과 유사하다.


PL/SQL에서는 "XX 테이블의 YY 열의 형식"이나 "XX 테이블의 레코드의 형식"과 같이 간접적인 형식 지정이 가능하다. 이러한 특징 덕분에 테이블 등 의존하는 데이터베이스 객체가 변경되어도 재분석(리컴파일)만으로 대응할 수 있다. 리컴파일 시 문제가 발생하지 않으면, 실행 시점에 PL/SQL 엔진이 자동으로 리컴파일을 시도한다.

4. 1. 수치 변수

PL/SQL에서 수치형 변수를 정의하려면 변수 이름 정의에 `NUMBER` 유형을 추가해야 한다. 선택 사항인 정밀도(P)와 소수점 이하 자릿수(S)를 괄호 안에 쉼표로 구분하여 추가할 수 있다. 정밀도는 변수가 가질 수 있는 숫자의 수를 의미하고, 소수점 이하 자릿수는 소수점 뒤에 올 수 있는 숫자의 수를 의미한다.

```text

variable_name number([P, S]) := 0;

```

숫자형 변수를 위한 다른 데이터 유형은 다음과 같다.

binary_float, binary_double, dec, decimal, double precision, float, integer, int, numeric, real, small-int, binary_integer.

4. 2. 문자 변수

PL/SQL에서 문자 변수를 정의할 때는 일반적으로 `VARCHAR2` 타입을 사용한다. 변수 이름 뒤에 `VARCHAR2`를 붙이고 괄호 안에 변수가 저장할 수 있는 최대 문자 수를 지정한다. 예를 들어, `address`라는 변수에 최대 20자까지 저장할 수 있도록 하려면 다음과 같이 정의한다.[1]

```text

address varchar2(20) := '호반 길';

```

`VARCHAR2` 외에도 문자 변수에 사용할 수 있는 다른 데이터 타입으로는 `VARCHAR`, `CHAR`, `LONG`, `RAW`, `LONG RAW`, `NCHAR`, `NVARCHAR2`, `CLOB`, `BLOB`, `BFILE` 등이 있다.[1]

4. 3. 날짜 변수

PL/SQL에서 Date 변수는 날짜와 시간을 포함할 수 있다. 시간은 생략될 수 있지만, 시간만 포함하는 변수를 정의할 방법은 없다. DATETIME 타입은 없으며, TIME 타입은 있지만, 밀리초 또는 나노초까지 세밀한 타임스탬프를 포함할 수 있는 TIMESTAMP 타입은 없다.

`TO_DATE` 함수는 문자열을 날짜 값으로 변환하는 데 사용할 수 있다. 이 함수는 첫 번째 따옴표로 묶인 문자열을 두 번째 따옴표로 묶인 문자열을 정의로 사용하여 날짜로 변환한다. 예를 들면 다음과 같다.

```plsql

to_date('31-12-2004', 'dd-mm-yyyy')

```

또는

```plsql

to_date ('31-Dec-2004', 'dd-mon-yyyy', 'NLS_DATE_LANGUAGE = American')

```

날짜를 문자열로 변환하려면 `TO_CHAR (date_string, format_string)` 함수를 사용한다.

PL/SQL은 ANSI 날짜 및 간격 리터럴의 사용 또한 지원한다.[11] 다음은 18개월 범위를 제공하는 예시이다.

```plsql

WHERE dateField BETWEEN DATE '2004-12-30' - INTERVAL '1-6' YEAR TO MONTH

AND DATE '2004-12-30'

4. 4. 예외

코드 실행 중 오류인 예외에는 사용자 정의 예외와 미리 정의된 예외, 두 종류가 있다.[1]

사용자 정의 예외는 프로그래머가 `RAISE` 또는 `RAISE_APPLICATION_ERROR` 명령을 사용하여 정상적인 실행을 계속할 수 없다고 판단하는 모든 상황에서 항상 명시적으로 발생시킨다. `RAISE` 명령의 구문은 다음과 같다.[1]

```plsql

RAISE ;

```

오라클사는 `NO_DATA_FOUND`, `TOO_MANY_ROWS` 등과 같은 몇 가지 예외를 미리 정의했다.[1]

각 예외에는 관련 SQL 오류 번호와 SQL 오류 메시지가 있다. 프로그래머는 `SQLCODE` 및 `SQLERRM` 함수를 사용하여 이에 접근할 수 있다.[1]

4. 5. 특정 열을 위한 자료형

`Variable_name .%type;` 형태로 사용자 정의 데이터 타입을 지정할 수 있다. 이 문법은 참조 테이블의 참조 열 종류의 변수를 정의한다.

프로그래머는 다음 구문을 사용하여 사용자 정의 데이터 유형을 지정한다.

```text

type data_type is record (field_1 type_1 := xyz, field_2 type_2 := xyz, ..., field_n type_n := xyz);

```

예시는 다음과 같다.

```text

declare

type t_address is record (

name address.name%type,

street address.street%type,

street_number address.street_number%type,

postcode address.postcode%type);

v_address t_address;

begin

select name, street, street_number, postcode into v_address from address where rownum = 1;

end;

```

이 예제 프로그램은 't_address'라는 자체 데이터 유형을 정의하며, 이 데이터 유형에는 'name', 'street', 'street_number' 및 'postcode' 필드가 포함된다.

따라서 이 예제에 따르면 데이터베이스에서 프로그램의 필드로 데이터를 복사할 수 있다.

이 데이터 유형을 사용하여 프로그래머는 'v_address'라는 변수를 정의하고 ADDRESS 테이블의 데이터로 채웠다.

프로그래머는 점 표기법을 사용하여 이러한 구조체의 개별 속성에 접근할 수 있다. 예를 들어 `v_address.street := 'High Street';` 와 같이 사용할 수 있다.

데이터 형식과 관련하여, 변수는 문자열 등 기본적인 형식 외에, 중첩된 표(배열형. 단, 첨자는 연속되지 않아도 되며, 리스트 구조와 같은 접근이 가능), 레코드형(구조체와 같은 것), 레코드형의 배열, 인덱스된 표(연관 배열, 해시 테이블과 같은 것)가 가능하다. 그 외에 PL/SQL에서는, "XX 테이블의 YY 열의 형식"이나 "XX 테이블의 레코드의 형식"과 같은 간접적인 형식 지정이 가능하므로, 테이블 등 의존하는 데이터베이스 객체의 변경에 대해서도 재분석(리컴파일)만으로 대응할 수 있으며, 또한, 리컴파일로 문제가 발생하지 않는 경우에 한해, 실행 시 PL/SQL 엔진이 자동으로 리컴파일을 시도한다는 장점이 있다.

5. 조건문

PL/SQL은 IF-THEN-ELSIF-ELSE 구문을 지원한다. 다음은 IF-THEN-ELSIF 구조의 예시이다.

```PLpgSQL

IF x = 1 THEN

sequence_of_statements_1;

ELSIF x = 2 THEN

sequence_of_statements_2;

ELSIF x = 3 THEN

sequence_of_statements_3;

ELSIF x = 4 THEN

sequence_of_statements_4;

ELSIF x = 5 THEN

sequence_of_statements_5;

ELSE

sequence_of_statements_N;

END IF;

```

ELSIF 및 ELSE 부분은 선택 사항이므로, 더 간단한 IF-THEN 또는 IF-THEN-ELSE 구문을 만들 수 있다.

CASE 문을 사용하면 일부 커다란 IF-THEN-ELSIF-ELSE 구조를 단순하게 만들 수 있다. 다음은 CASE 문의 예시이다.

```PLSQL

CASE

WHEN x = 1 THEN sequence_of_statements_1;

WHEN x = 2 THEN sequence_of_statements_2;

WHEN x = 3 THEN sequence_of_statements_3;

WHEN x = 4 THEN sequence_of_statements_4;

WHEN x = 5 THEN sequence_of_statements_5;

ELSE sequence_of_statements_N;

END CASE;

```

CASE 문은 미리 정의된 선택자와 함께 사용할 수 있다. 다음은 미리 정의된 선택자와 함께 사용하는 CASE 문의 예시이다.

```PLSQL

CASE x

WHEN 1 THEN sequence_of_statements_1;

WHEN 2 THEN sequence_of_statements_2;

WHEN 3 THEN sequence_of_statements_3;

WHEN 4 THEN sequence_of_statements_4;

WHEN 5 THEN sequence_of_statements_5;

ELSE sequence_of_statements_N;

END CASE;

6. 배열 처리

PL/SQL은 배열을 "컬렉션"이라고 부른다. 이 언어는 세 가지 유형의 컬렉션을 제공한다.

종류설명
연관 배열 (인덱스-바 테이블)숫자 또는 문자열로 인덱싱할 수 있다. 자바의 맵(Map)과 유사하게 키-값 쌍으로 구성된다. 차원은 하나이며 제한이 없다. PL/SQL에서 아커만 함수의 메모 함수 예시와 같이 연관 배열을 시뮬레이션하는 데 사용할 수 있다.
중첩 테이블프로그래머는 중첩된 항목이 무엇인지 이해해야 한다. 여러 구성 요소로 구성될 수 있는 새로운 유형이 생성된다. 그런 다음 해당 유형을 사용하여 테이블에 열을 만들 수 있으며, 해당 열 내에는 해당 구성 요소가 중첩된다.
Varray (가변 크기 배열)프로그래머가 Varray에 대한 상한을 지정해야 한다. 배열이 선언될 때 사용되는 크기는 고정되어 있다. 배열의 요소 수는 선언된 크기까지 가변적이다.



이 언어에는 컬렉션 요소를 조작하는 데 사용되는 여러 컬렉션 메서드 (FIRST, LAST, NEXT, PRIOR, EXTEND, TRIM, DELETE 등)가 포함되어 있다.

7. 커서

커서는 DML 문(INSERT, UPDATE, DELETE, MERGE)이나 SELECT 문의 정보가 저장된 사유(private) SQL 영역의 포인터이자 메커니즘이다.[12] 커서는 SQL 문에서 반환된 하나 이상의 행을 저장하며, 커서가 보유한 행 집합을 활성 집합이라고 한다.[12]

커서는 명시적이거나 암시적일 수 있다. FOR 루프에서 쿼리를 재사용할 경우 명시적 커서를 사용해야 하며, 그렇지 않으면 암시적 커서를 사용하는 것이 좋다. 루프 내에서 커서를 사용하는 경우, 대량 수집하거나 동적 SQL이 필요한 경우 FETCH를 사용하는 것이 좋다.

8. 루프

PL/SQL은 절차적 프로그래밍 언어이므로, 기본 LOOP 문, WHILE 루프, FOR 루프, 커서 FOR 루프를 포함한 여러 반복문 구조를 제공한다.[13]

기본 LOOP 문은 `EXIT` 키워드를 사용하거나 예외를 발생시켜 종료할 수 있다.[13]

```plpgsql

<<상위_루프>>

LOOP

명령문

<<하위_루프>>

loop

명령문

exit 상위_루프 when <조건>; -- 두 루프 모두 종료

exit when <조건>; -- 상위_루프로 제어 반환

end loop 하위_루프;

if <조건> then

continue; -- 다음 반복으로 진행

end if;

exit when <조건>;

END LOOP 상위_루프;

```

FOR 루프는 지정된 범위 내에서 반복을 수행한다.

```plpgsql

DECLARE

var NUMBER;

BEGIN

/* 참고: PL/SQL의 FOR 루프 변수는 루프 내에서만 유효한 새로운 선언입니다. */

FOR var IN 0 .. 10 LOOP

DBMS_OUTPUT.PUT_LINE(var);

END LOOP;

IF var IS NULL THEN

DBMS_OUTPUT.PUT_LINE('var is null');

ELSE

DBMS_OUTPUT.PUT_LINE('var is not null');

END IF;

END;

```

위 코드의 출력 결과는 다음과 같다.

```text

0

1

2

3

4

5

6

7

8

9

10

var is null

```

커서 FOR 루프는 커서를 자동으로 열고, 데이터를 읽어 들인 후 커서를 다시 닫는다.

```plpgsql

FOR RecordIndex IN (SELECT person_code FROM people_table)

LOOP

DBMS_OUTPUT.PUT_LINE(RecordIndex.person_code);

END LOOP;

```

PL/SQL 프로그래머는 커서의 SELECT 문을 미리 정의하여 재사용성을 높이거나 코드를 더 이해하기 쉽게 만들 수 있다.

```plpgsql

DECLARE

CURSOR cursor_person IS

SELECT person_code FROM people_table;

BEGIN

FOR RecordIndex IN cursor_person

LOOP

DBMS_OUTPUT.PUT_LINE(recordIndex.person_code);

END LOOP;

END;

```

FOR 루프 내의 `person_code` 개념은 점 표기(".")로 표현된다.

```plpgsql

RecordIndex.person_code

9. 동적 SQL

PL/SQL 코드에서 데이터 정의 언어(DDL) 문을 사용하려면 "동적 SQL" 문을 사용해야 한다. 초기 버전의 오라클 데이터베이스에서는 `DBMS_SQL` 패키지 라이브러리를 사용했지만, 최신 버전에서는 `EXECUTE IMMEDIATE` 구문을 사용하는 "네이티브 동적 SQL"을 지원한다.[1]

10. 다른 데이터베이스 언어와의 비교

PL/SQL은 다른 관계형 데이터베이스의 내장 프로시저 언어와 유사하게 작동한다. 예를 들어, 사이베이스 ASE와 마이크로소프트 SQL Server는 Transact-SQL을 사용하고, PostgreSQL은 PL/pgSQL(PL/SQL을 어느 정도 에뮬레이션)을 사용한다.[14]

PL/SQL과 Transact-SQL은 표면적인 유사점에도 불구하고 근본적으로 다르다. 두 언어의 기능 집합 차이[17]뿐만 아니라, 오라클과 SQL Server가 동시성 및 잠금을 처리하는 방식이 매우 다르기 때문에 한 언어에서 다른 언어로 코드를 이식하는 것은 대개 상당한 작업이 필요하다.

데이터베이스 언어 SQL의 확장으로, Sybase ASE나 Microsoft SQL Server에서 구현된 Transact-SQL과 비교되기도 하지만, 문법 및 구문의 차이뿐만 아니라 명령어의 SQL 엔진에서의 처리 방식도 다르다. Transact-SQL은 모든 구문을 SQL 엔진만으로 처리하지만, PL/SQL의 경우 PL/SQL 구문은 PL/SQL 엔진에서 분석 및 실행되며, 데이터 조작 언어(DML; SELECT/INSERT/UPDATE/DELETE) 구문은 기존 SQL 엔진에서 분석 및 실행되도록 내부적으로 분기된다. 이 때문에, Transact-SQL에서 SELECT의 결과는 저장 프로시저에서 결과 집합을 직접 호출 측으로 반환하는 것이 가능하지만, PL/SQL에서는 그럴 수 없으므로 패키지 정의 커서를 경유한 전달이나 배열 변수를 사용한 데이터 반환 등으로 회피해야 한다.

PostgreSQL의 PL/pgSQL은 PL/SQL을 참고하여 구현되었지만, 데이터베이스 관리 시스템(DBMS)의 구현에 의존하는 부분(트랜잭션 관리 개념이 다름)이나, 출력 인수의 취급이 다른 등 완전한 호환성은 없다.

IBM의 DB2는 Oracle로부터의 전환을 노리고 V9.7부터 PL/SQL을 지원한다. 또한 MariaDB도 10.3부터 Oracle Database 호환 기능으로서 PL/SQL을 지원하고 있다[20]。

11. 문법

PL/SQL 문법은 Ada 언어를 기반으로 한다.[4] 코드는 `BEGIN ~ END` 또는 `DECLARE ~ BEGIN ~ END` 블록 안에 작성한다.[5] 변수, 상수, 커서 등은 `DECLARE ~ BEGIN`에 정의한다.

PL/SQL 블록 내 예외(Exception) 처리는 `BEGIN`과 `END` 사이에 `EXCEPTION` 구문을 추가하고, `END` 구문까지의 사이에 예외 처리를 작성한다. 이는 Java의 `try 〜 catch`와 유사하다.[5]

```PLpgSQL

<


  • - 선택 사항

number1 NUMBER(2);

number2 number1%TYPE := 17; -- 기본값

text1 VARCHAR2(12) := ' Hello world ';

text2 DATE := SYSDATE; -- 현재 날짜 및 시간

BEGIN

  • - 필수, 최소 하나의 실행문 포함

SELECT street_number

INTO number1

FROM address

WHERE name = 'INU';

EXCEPTION

  • - 선택 사항

WHEN OTHERS THEN

DBMS_OUTPUT.PUT_LINE('Error Code is ' || TO_CHAR(sqlcode));

DBMS_OUTPUT.PUT_LINE('Error Message is ' || sqlerrm);

END;

```

`:=`는 변수에 값을 저장하는 할당 연산자이다.[5]

예외에는 사용자 정의 예외와 미리 정의된 예외가 있다. 사용자 정의 예외는 `RAISE` 또는 `RAISE_APPLICATION_ERROR` 명령으로 발생시킨다. 오라클은 `NO_DATA_FOUND`, `TOO_MANY_ROWS` 등을 미리 정의해 두었다. 각 예외에는 관련 SQL 오류 번호와 메시지가 있으며, `SQLCODE` 및 `SQLERRM` 함수로 접근할 수 있다.[5]

한 줄 주석은 `--`를 사용한다. 여러 줄 주석은 `/* ~ */`를 사용한다.[5]

11. 1. Ada 언어와의 관계

PL/SQL의 문법과 구문은 Ada 언어를 기반으로 한다.[4] 오라클 CEO인 래리 엘리슨은 오라클을 개발하기 전에 Ada 언어로 국방부용 시스템 개발을 수행했다.[4] 비록 해당 시스템 계획은 실패했지만, 이러한 경위로 인해 PL/SQL이 Ada 언어를 기반으로 하는 것을 채택한 것으로 추측된다.[4] 참고로 해당 시스템의 계획명은 '''“Oracle”'''이었다.[4]

11. 2. 블록과 예외

PL/SQL 코드는 `BEGIN ~ END` 또는 `DECLARE ~ BEGIN ~ END` 블록 안에 기술한다.[5] 변수, 상수, 커서 등의 정의는 `DECLARE ~ BEGIN`에 기술한다.

PL/SQL 블록 내에서 발생하는 예외(Exception)를 처리하기 위해서는 `BEGIN`과 `END` 사이에 `EXCEPTION` 구문을 추가하고, `END` 구문까지의 사이에 예외 처리를 기술한다. 이 블록은 중첩될 수 있어, Java의 `try 〜 catch`와 유사하게 사용 가능하다. 이는 Java가 Ada, C++ 언어의 예외 처리 방식을 차용한 결과이다.[5]

```PLpgSQL

<

12. 저장 프로시저 및 저장 함수

저장 프로시저 및 저장 함수는 PL/SQL 코드에 이름을 부여하여, 구문 분석된 코드를 서버에 등록하고 호출 가능한 상태로 만들 수 있는 기능이다. 프로시저는 반환 값이 없고, 함수는 반환 값이 있다는 차이만 있다.[9]

일반적인 SQL 문에 비해 저장 함수 및 프로시저는 실행 시점에 이미 구문 분석되어 있으므로 처리가 빠르지만, 참조하는 데이터베이스 스키마를 변경하면 (테이블 등의 구성을 변경하면) 재구문 분석 (컴파일)이 필요하다.

저장 함수는 일반 SQL 문의 함수로 사용할 수 있다.[9]

PL/SQL 함수의 목적은 일반적으로 단일 값을 계산하고 반환하는 데 사용된다. 반환된 값은 단일 스칼라 값(예: 숫자, 날짜 또는 문자열)이거나 단일 컬렉션(예: 중첩 테이블 또는 배열)일 수 있다. 사용자 정의 함수는 오라클(Oracle Corporation)에서 제공하는 내장 함수를 보완한다.[6]

PL/SQL 함수의 형식은 다음과 같다.

```syntaxhighlight

CREATE OR REPLACE FUNCTION [(입력/출력 변수 선언)] RETURN return_type

[AUTHID ] -- 머리 부분

amount number; -- 선언 블록

BEGIN -- 실행 블록



RETURN ;

[Exception

none]

RETURN ;

END;

```

파이프라인 테이블 함수는 컬렉션을 반환하며[7] 다음과 같은 형식을 사용한다.

```syntaxhighlight

CREATE OR REPLACE FUNCTION [(입력/출력 변수 선언)] RETURN return_type

[AUTHID ] []

[선언 블록]

BEGIN



PIPE ROW ;

RETURN;

[Exception

exception block]

PIPE ROW ;

RETURN;

END;

```

함수는 기본 IN 유형의 매개변수만 사용해야 한다. 함수에서 유일한 OUT 값은 함수가 반환하는 값이어야 한다.

프로시저는 반복적으로 호출될 수 있는 명명된 프로그램 단위라는 점에서 함수와 유사하다. 주요 차이점은 '''함수는 SQL 문에서 사용될 수 있지만 프로시저는 그렇지 않다'''는 것이다. 또 다른 차이점은 프로시저는 여러 값을 반환할 수 있지만 함수는 단일 값만 반환해야 한다는 것이다.[8]

프로시저는 프로시저 이름과 선택적으로 프로시저 매개변수 목록을 포함하는 필수 헤더 부분으로 시작한다. 다음은 PL/SQL 익명 블록과 마찬가지로 선언부, 실행부 및 예외 처리부가 나온다. 간단한 프로시저는 다음과 같다.

```syntaxhighlight lang="PLpgSQL">

CREATE PROCEDURE create_email_address ( -- 프로시저 헤더 부분 시작

name1 VARCHAR2,

name2 VARCHAR2,

company VARCHAR2,

email OUT VARCHAR2

) -- 프로시저 헤더 부분 종료

AS


  • - 선언부 시작 (선택 사항)

error_message VARCHAR2(30) := 'Email address is too long.';

BEGIN -- 실행부 시작 (필수)

email := name1 || '.' || name2 || '@' || company;

EXCEPTION -- 예외 처리부 시작 (선택 사항)

WHEN VALUE_ERROR THEN

DBMS_OUTPUT.PUT_LINE(error_message);

END create_email_address;



위의 예는 독립형 프로시저를 보여준다. 이 유형의 프로시저는 CREATE PROCEDURE 문을 사용하여 데이터베이스 스키마에 생성되어 저장된다. 프로시저는 PL/SQL 패키지에서 생성될 수도 있다. 이를 패키지 프로시저라고 한다. PL/SQL 익명 블록에서 생성된 프로시저는 중첩 프로시저라고 한다. 데이터베이스에 저장된 독립형 또는 패키지 프로시저를 "저장 프로시저"라고 한다.

프로시저는 IN, OUT 및 IN OUT의 세 가지 유형의 매개변수를 가질 수 있다.

  • IN 매개변수는 입력으로만 사용된다. IN 매개변수는 참조로 전달되지만 비활성 프로그램에 의해 변경될 수 있다.
  • OUT 매개변수는 처음에 NULL이다. 프로그램은 매개변수 값을 할당하고 해당 값이 호출 프로그램으로 반환된다.
  • IN OUT 매개변수는 초기 값을 가질 수도 있고 그렇지 않을 수도 있다. 해당 초기 값은 호출된 프로그램에 의해 수정될 수도 있고 그렇지 않을 수도 있다. 매개변수에 대한 모든 변경 사항은 기본적으로 복사하여 호출 프로그램으로 반환되지만 - NO-COPY 힌트를 사용하면 - 참조로 전달될 수 있다.

13. 데이터베이스 트리거

데이터베이스 트리거는 등록된 테이블에 대해 행 추가, 갱신, 삭제 전후의 타이밍에 자동으로 처리를 실행하는 PL/SQL 블록이다.[1] 참조 무결성 관리, 비정규화된 테이블 동기화 등에 사용될 수 있다.[1]

트리거는 다음의 목적으로 작성될 수 있다.[1]


  • 일부 파생 열 값을 자동으로 생성
  • 참조 무결성 강제
  • 이벤트 로깅 및 테이블 접근 정보 저장
  • 감사
  • 테이블의 동기식 복제
  • 보안 권한 부여
  • 잘못된 트랜잭션 방지


처리되는 데이터 1건씩 처리할 수 있으며, 변경 전과 변경 후의 값을 사용할 수 있다. 기동 조건을 세부적으로 설정하는 것도 가능하다.[1] 본래는 "제약" 정의로 관리할 수 없는 참조 무결성을 관리하기 위해 사용한다.[1] 업무 처리를 암묵적으로 너무 많이 통합하면, 업무 처리 변경 시 다른 응용 프로그램의 동작에 영향을 주기 쉬우므로 주의해서 사용해야 한다.[1]

오류 처리 등 고려해야 할 점이 많지만, 비정규화된 테이블의 동기화에 사용하면 편리하다.[1]

14. 패키지

패키지는 개념적으로 연결된 함수, 프로시저, 변수, PL/SQL 테이블 및 레코드 TYPE 문, 상수, 커서 등을 그룹화한 것이다. 패키지를 사용하면 코드 재사용을 촉진한다. 패키지는 패키지 명세와 선택적인 패키지 본문으로 구성된다. 명세는 애플리케이션의 인터페이스이며, 사용 가능한 유형, 변수, 상수, 예외, 커서 및 서브프로그램을 선언한다. 본문은 커서와 서브프로그램을 완전히 정의하므로 명세를 구현한다.[10]

패키지의 두 가지 장점은 다음과 같다.[10]


  • 모듈식 접근 방식, 비즈니스 로직의 캡슐화/숨김, 보안, 성능 향상, 재사용성. 객체 지향 프로그래밍 기능, 함수 오버로딩 및 캡슐화를 지원한다.
  • 패키지 변수를 사용하면 패키지 명세에 선언된 변수가 세션 범위를 가지므로 세션 수준(범위 지정) 변수를 선언할 수 있다.


PL/SQL이 Ada 언어의 특징을 뚜렷하게 나타내는 부분은 "패키지"의 개념을 도입했다는 점이다. 이는 C 언어의 정적 변수나 헤더 파일을 통한 접근 제어 기능과 유사하며, 영속성을 가진 변수, 다른 패키지에서 참조를 허용하지 않는 변수, 저장 프로시저 등을 만들 수 있다.

그러나 인스턴스화가 불가능하므로 객체 지향 프로그래밍에서의 클래스와는 본질적으로 다르다. 다만, 해당 패키지가 처음 호출될 때만 실행되는 "자동 초기 코드(INIT 블록)"를 기술할 수 있다는 점은 C 언어 등에서는 찾아볼 수 없는 특징이다.

15. 빌트인/유틸리티 패키지

데이터베이스 정의 완료 후 실행되는 SQL 스크립트인 `catproc.sql`은 유틸리티 패키지(DBMS_XXX) 및 표준 SQL 함수를 정의한다.[1] 패키지 STANDARD로 정의된 프로시저와 함수는 패키지 이름 없이 호출할 수 있다.[1] 유틸리티 패키지에는 DBMS_OUTPUT, UTL_FILE, DBMS_PIPE, DBMS_ALERT, DBMS_SQL 등이 있다.[1] 사용자가 새 패키지를 생성할 때는 DBMS_ 또는 UTL_로 시작하는 이름을 피해야 한다.[1]

자세한 내용은 다음과 같다.


  • `catproc.sql`: 데이터베이스 정의(CREATE DATABASE문) 완료 후 실행되는 SQL 스크립트로, 유틸리티 패키지(DBMS_XXX)와 표준 SQL 함수(COUNT() 등)를 정의한다. 일반적으로 데이터베이스 카탈로그를 정의하는 스크립트 `catalog.sql` 실행 후 실행된다.[1]
  • 패키지 STANDARD: 이 패키지에 정의된 프로시저와 함수는 호출 시 패키지 이름을 명시하지 않아도 된다.[1]
  • 유틸리티 패키지:
  • DBMS_OUTPUT: 버퍼 출력을 위한 패키지.[1]
  • UTL_FILE: 파일 출력을 위한 패키지.[1]
  • DBMS_PIPE, DBMS_ALERT: 응용 프로그램 간의 동기 통신에 사용되는 패키지.[1]
  • DBMS_SQL: 동적 SQL을 구현하기 위한 패키지.[1]
  • 패키지 이름 규칙: 사용자가 새 패키지를 생성할 때 DBMS_ 또는 UTL_로 시작하는 이름은 피해야 한다.[1]
  • `catproc.sql` 실행 시점: OUI(Oracle Universal Installer)로 제품 설치와 함께 데이터베이스 정의를 수행하거나, DBCA(Database Configuration Assistant)로 데이터베이스를 정의하는 경우, DBCA에서 생성된 스크립트에 포함되어 실행된다.[1]
  • `catproc.sql` 재실행: 정의된 데이터베이스의 동작이 이상하거나 PSR(Patch Set Release, 누적 패치)을 적용한 경우 `catproc.sql` 스크립트를 다시 실행해야 할 수 있다.[1]

16. 예제

plsql

DECLARE

var NUMBER;

BEGIN

/* 참고: PL/SQL의 FOR 루프 변수는 루프 내에서만 유효한 새로운 선언입니다. */

FOR var IN 0 .. 10 LOOP

DBMS_OUTPUT.PUT_LINE(var);

END LOOP;

IF var IS NULL THEN

DBMS_OUTPUT.PUT_LINE('var is null');

ELSE

DBMS_OUTPUT.PUT_LINE('var is not null');

END IF;

END;

```

출력:

```text

0

1

2

3

4

5

6

7

8

9

10

var is null

```
주의: DBMS_OUTPUT의 출력 대상을 SQL*Plus라는 SQL 대화형 인터페이스로 설정하려면 `SET SERVEROUTPUT ON` SQL*Plus 설정을 수행해야 한다. 터미널 등 표준 출력에 대한 조작은 PL/SQL 엔진이 네이티브로 갖는 기능이 아니라 유틸리티 도구의 기능에 의존하기 때문이다.[5]

'''익명 블록 예시'''

```plsql

DECLARE

hw VARCHAR2(100) := 'Hello World!';

BEGIN

DBMS_OUTPUT.PUT_LINE('Hello World!');

DBMS_OUTPUT.PUT_LINE('VARIABLE hw = ' || hw);

END;

/

```

'''저장 프로시저 예시'''

```plsql

CREATE OR REPLACE PROCEDURE helloworld (str IN VARCHAR2)

AS

/* 정의 부분은 AS부터 BEGIN으로 시작한다. */

hw VARCHAR2(100) := 'Hello World!';

BEGIN

DBMS_OUTPUT.PUT_LINE('Hello World!');

DBMS_OUTPUT.PUT_LINE('VARIABLE hw = ' || hw);

DBMS_OUTPUT.PUT_LINE('Parameter str = ' || str);

END;

/

```

'''예외 처리 예시'''

```plsql

DECLARE

FNAME EMP.ename%TYPE;

BEGIN

SELECT ename INTO fname FROM emp;

EXCEPTION

WHEN TOO_MANY_ROWS THEN -- 정의된 예외 식별자

DBMS_OUTPUT.PUT_LINE('행이 너무 많습니다');

WHEN NO_DATA_FOUND THEN

DBMS_OUTPUT.PUT_LINE('레코드가 1건도 없습니다');

WHEN OTHERS THEN -- 기타 예외는 모두 여기서 캐치

DBMS_OUTPUT.PUT_LINE('Oracle 에러 :'||SQLERRM);

END;

/

```

'''커서 처리 예시(제어 구조도 약간)'''

```plsql

DECLARE

CURSOR c1 IS

SELECT ename FROM EMP;

FNAME EMP.ename%TYPE;

BEGIN

/* 커서 c1을 열고 모든 레코드를 페치할 때까지 루프한다

페치할 때마다 테이블 emp의 열 ename을 가져와 표시한다 */

OPEN c1;

LOOP

FETCH c1 INTO fname;

EXIT WHEN c1%NOTFOUND; -- 커서 상태를 체크

DBMS_OUTPUT.PUT_LINE(fname);

IF length(fname) < 5 THEN -- 문자열 길이 체크

DBMS_OUTPUT.PUT_LINE('→ TOO SHORT');

ELSE

DBMS_OUTPUT.PUT_LINE('→ TOO LONG!');

END IF;

END LOOP;

CLOSE c1;

EXCEPTION

WHEN OTHERS THEN -- 기타 예외는 모두 여기서 캐치

DBMS_OUTPUT.PUT_LINE('Oracle 에러 :'||SQLERRM);

END;

/

참조

[1] 웹사이트 DB2 10: Run Oracle applications on DB2 10 for Linux, UNIX, and Windows http://www.ibm.com/d[...] Ibm.com 2012-07-26
[2] 서적 Oracle PL/SQL Programming
[3] 웹사이트 Oracle Compliance with SQL/PSM https://docs.oracle.[...]
[4] 웹사이트 Oracle Server Features https://docs.oracle.[...] Oracle 2020-09-21
[5] 웹사이트 PL/SQL Anonymous Blocks https://livesql.orac[...] Oracle 2020-09-21
[6] 웹사이트 Database SQL Reference https://docs.oracle.[...] Oracle 2020-09-21
[7] 서적 Oracle PL/SQL for DBAs https://books.google[...] O'Reilly Media, Inc. 2011-01-11
[8] 웹사이트 Database SQL Reference https://docs.oracle.[...] Oracle 2020-09-21
[9] 서적 Advanced Oracle PL/SQL Developer's Guide https://books.google[...] Packt Publishing Ltd 2017-06-08
[10] 웹사이트 Coding PL/SQL Procedures and Packages https://docs.oracle.[...] Oracle 2020-09-21
[11] 웹사이트 Literals http://download.orac[...] Oracle 2009-03-20
[12] 웹사이트 Working with Cursors http://www.oracle.co[...]
[13] 웹사이트 Database PL/SQL User's Guide and Reference http://download.orac[...]
[14] 웹사이트 What's New in MariaDB Server 10.3 https://mariadb.org/[...] MariaDB 2018-08-21
[15] 웹사이트 SQL PL http://publib.boulde[...] Publib.boulder.ibm.com 2012-07-26
[16] 문서 Private and public items in PL/SQL http://docs.oracle.c[...] Oracle documentation
[17] 웹사이트 Migrating from Oracle to SQL Server - T-SQL, PL/SQL differences: Narayana Vyas Kondreddi's home page http://vyaskn.tripod[...]
[18] 웹사이트 "@humanications We didn't re-implement the SQLite API, we include a version of SQLite which uses Berkeley DB for storage (replacing btree.c)." https://twitter.com/[...] 2010-03-24
[19] 웹사이트 Official Berkeley DB FAQ http://www.oracle.co[...] Oracle Corporation 2010-03-30
[20] 웹사이트 MariaDB Server 10.3/MariaDB TX 3.0リリース。Oracle Database互換機能を搭載し、同じデータ型やPL/SQLのストアドプロシジャをサポート https://www.publicke[...] Publickey 2018-05-30
[21] 웹인용 DB2 10: Run Oracle applications on DB2 10 for Linux, UNIX, and Windows http://www.ibm.com/d[...] Ibm.com 2012-07-26
[22] 문서 Syntax http://download.orac[...]


본 사이트는 AI가 위키백과와 뉴스 기사,정부 간행물,학술 논문등을 바탕으로 정보를 가공하여 제공하는 백과사전형 서비스입니다.
모든 문서는 AI에 의해 자동 생성되며, CC BY-SA 4.0 라이선스에 따라 이용할 수 있습니다.
하지만, 위키백과나 뉴스 기사 자체에 오류, 부정확한 정보, 또는 가짜 뉴스가 포함될 수 있으며, AI는 이러한 내용을 완벽하게 걸러내지 못할 수 있습니다.
따라서 제공되는 정보에 일부 오류나 편향이 있을 수 있으므로, 중요한 정보는 반드시 다른 출처를 통해 교차 검증하시기 바랍니다.

문의하기 : help@durumis.com