여러번 사용할 목적으로 이름을 지정하여 오라클에 저장해 두는 PL/SQL 프로그램을 저장 서브프로그램(stored subprogram)이라고 한다.
익명 블록과 달리 저장 서브프로그램은 오라클에 저장하여 공유할 수 있으므로 메모리∙성능∙재사용성 등 여러 면에서 장점이 있다.
익명 블록 | 저장 서브프로그램 | |
---|---|---|
이름 | 이름 없음 | 이름 지정 |
오라클 저장 | 저장할 수 없음 | 저장함 |
컴파일 | 실행할 때마다 컴파일 | 저장할 때 한 번 컴파일 |
공유 | 공유할 수 없음 | 공유하여 사용 가능 |
다른 응용 프로그램에서의 호출 가능 여부 | 호출할 수 없음 | 호출 가능 |
서브프로그램 | 용도 |
---|---|
저장 프로시저 (stored procedure) | 일반적으로 특정 처리 작업 수행을 위한 서브프로그램으로 SQL문에서는 사용할 수 없습니다. |
저장 함수 (stored function) | 일반적으로 특정 연산을 거친 결과 값을 반환하는 서브프로그램으로 SQL문에서 사용할 수 있습니다. |
패키지 (package) | 저장 서브프로그램을 그룹화하는 데 사용합니다. |
트리거 (trigger) | 특정 상황(이벤트)이 발생할 때 자동으로 연달아 수행할 기능을 구현하는 데 사용합니다. |
파라미터를 사용하지 않는 프로시저
// 기본 형식
CREATE 1.[OR REPLACE] PROCEDURE 2.프로시저 이름
IS | AS 3.
선언부
BEGIN
실행부
EXCEPTION 4.
예외 처리부
END [프로시저 이름]; 5.
번호 | 설명 |
---|---|
1. | 지정한 프로시저 이름을 가진 프로시저가 이미 존재하는 경우에 현재 작성한 내용으로 대체합니다. 즉 덮어 쓴다는 뜻이며 생략 가능한 옵션입니다. |
2. | 저장할 프로시저의 고유 이름을 지정합니다. 같은 스키마 내에서 중복될 수 없습니다. |
3. | 선언부를 시작하기 위해 IS 또는 AS 키워드를 사용합니다. 선언부가 존재하지 않더라도 반드시 명시합니다. DECLARE 키워드는 사용하지 않습니다. |
4. | 예외 처리부는 생략 가능합니다. |
5. | 프로지서 생성의 종료를 뜻하며 프로시저 이름은 생략 가능합니다. |
프로시저 생성하기
프로시저 실행하기
EXECUTE 프로시저 이름;
USER_SOURCE의 열 | 설명 |
---|---|
NAME | 서브프로그램(생성 객체) 이름 |
TYPE | 서브프로그램 종류(PROCEDURE, FUNCTION 등) |
LINE | 서브프로그램에 작성한 줄 번호 |
TEXT | 서브프로그램에 작성한 소스 코드 |
// USER_SOURCE를 통해 프로시저 확인하기
SELECT *
FROM USER_SOURCE
WHERE NAME = 'PRO_NOPARAM';
파라미터를 사용하는 프로시저
// 기본 형식
CREATE 1.[OR REPLACE] PROCEDURE 2.프로시저 이름
[(파라미터 이름1 [modes] 자료형 [ := | DEFAULT 기본값], 3.
파라미터 이름2 [modes] 자료형 [ := | DEFAULT 기본값],
...
파라미터 이름N [modes] 자료형 [ := | DEFAULT 기본값]
)]
IS | AS 4.
선언부
BEGIN
실행부
EXCEPTION 5.
예외 처리부
END [프로시저 이름]; 6.
번호 | 설명 |
---|---|
1. | 지정한 프로시저 이름을 가진 프로시저가 이미 존재하는 경우에 현재 작성한 내용으로 대체합니다. 즉 덮어 쓴다는 뜻이며 생략 가능한 옵션입니다. |
2. | 저장할 프로시저의 고유 이름을 지정합니다. 같은 스키마 내에서 중복될 수 없습니다. |
3. | 실행에 필요한 파라미터를 정의합니다. 파라미터는 쉼표(,)로 구분하여 여러 개 지정할 수 있습니다. 기본값과 모드(modes)는 생략 가능합니다. 자료형은 자리수 지정 및 NOT NULL 제약 조건 사용이 불가능합니다. |
4. | 선언부를 시작하기 위해 IS 또는 AS 키워드를 사용합니다. 선언부가 존재하지 않더라도 반드시 명시합니다. DECLARE 키워드는 사용하지 않습니다. |
5. | 예외 처리부는 생략 가능합니다. |
6. | 프로시저 생성의 종료를 뜻하며 프로시저 이름은 생략 가능합니다. |
파라미터 모드 | 설명 |
---|---|
IN | 지정하지 않으면 기본값으로 프로시저를 호출할 때 값을 입력받습니다. |
OUT | 호출할 때 값을 반환합니다. |
IN OUT | 호출할 때 값을 입력받은 후 실행 결과 값을 반환합니다. |
프로시저에 파라미터 지정하기
파라미터를 입력하여 프로시저 사용하기
기본값이 지정된 파라미터 입력을 제외하고 프로시저 사용하기
실행에 필요한 개수보다 적은 파라미터를 입력하여 프로시저 실행하기
파라미터 이름을 활용하여 프로시저에 값 입력하기
종류 | 설명 |
---|---|
위치 지정 | 지정한 파라미터 순서대로 값을 지정하는 방식 |
이름 지정 | => 연산자로 파라미터 이름을 명시하여 값을 지정하는 방식 |
혼합 지정 | 일부 파라미터는 순서대로 값만 지정하고 일부 파라미터는 => 연산자로 값을 지정하는 방식(11g부터 사용 가능) |
OUT 모드 파라미터 정의하기
OUT 모드 파라미터 사용하기
IN OUT 모드 파라미터 정의하기
IN OUT 모드 파라미터 사용하기
프로시저 오류 정보 확인하기
생성할 때 오류가 발생하는 프로시저 알아보기
▶︎ 서브프로그램을 만들 때 발생한 오류는 SHOW ERRORS 명령어와 USER_ERRORS 데이터 사전을 조회하여 확인할 수 있다.
SHOW ERRORS 명령어로 오류 확인하기
▶︎ SHOW ERRORS 명령어는 줄여서 SHOW ERR로 사용할 수도 있다.
// 최근에 발생한 프로그램 오류가 아니라 특정 프로그램의 오류 정보를 확인하기
SHOW ERR 프로그램 종류 프로그램 이름;
SHOW ERR PROCEDURE pro_err;
// USER_ERRORS로 오류 확인하기
SELECT *
FROM USER_ERRORS
WHERE NAME = 'PRO_ERR';