CREATE PROCEDURE (SQL) 문

CREATE PROCEDURE(SQL)문은 현재 서버에서 SQL 프로시저를 정의합니다.

호출

이 명령문은 애플리케이션 프로그램에 임베드되거나 동적 SQL문을 사용하여 실행할 수 있습니다. 이는 DYNAMICRULES 실행 동작이 패키지에 영향을 주는 경우에만 동적으로 준비될 수 있는 실행문입니다(SQLSTATE 42509).

권한 부여

명령문의 권한 부여 ID가 보유한 특권은 다음 중 하나 이상을 포함해야 합니다.

프로시저의 내재적 또는 명시적 스키마 이름이 존재하지 않는 경우 데이터베이스에 대한 IMPLICIT_SCHEMA 권한.
프로시저의 스키마 이름이 기존 스키마를 참조하는 경우 스키마에 대한 CREATEIN 특권.
프로시저의 스키마 이름이 기존 스키마를 참조하는 경우 스키마에 대한 SCHEMAADM 권한.
DBADM 권한

명령문의 권한 부여 ID가 보유한 특권에는 프로시저 본문에 지정된 SQL문을 호출하는 데 필요한 모든 특권도 포함되어야 합니다.

기존 프로시저를 대체하려면 명령문의 권한 부여 ID가 기존 프로시저의 소유자여야 합니다(SQLSTATE 42501).

그룹 특권은 CREATE PROCEDURE(SQL)문에 지정되는 테이블 또는 뷰에 대해 고려되지 않습니다.

설명

OR REPLACE

현재 서버에 존재하는 경우 프로시저에 대한 정의를 대체하도록 지정합니다. 기존 정의는 새 정의가 카탈로그에서 대체되기 전에 효과적으로 삭제되고, 프로시저에 권한 부여된 특권은 영향 받지 않는 점은 예외입니다. 이 옵션은 오브젝트의 소유자만 지정할 수 있습니다. 프로시저에 대한 정의가 현재 서버에 없는 경우 이 옵션은 무시됩니다. 기존 프로시저를 대체하려면 새 정의의 특정 이름과 프로시저 이름은 이전 정의의 특정 이름과 프로시저 이름과 동일해야 하거나 새 정의의 서명이 이전 정의의 서명과 일치해야 합니다. 그렇지 않은 경우 새 프로시저가 작성됩니다.

절차 이름정의되는 프로시저의 이름을 지정합니다. 이는 프로시저를 지정하는 규정되거나 규정되지 않은 이름입니다. procedure-name의 규정되지 않은 양식이 SQL ID입니다. 동적 SQL문에서 CURRENT SCHEMA 특수 레지스터는 규정되지 않은 오브젝트 이름의 규정자로서 사용됩니다. 정적 SQL문의 경우, QUALIFIER 프리컴파일 또는 바인드 옵션은 내재적으로 규정되지 않은 오브젝트 이름의 규정자를 지정합니다. 규정된 양식은 마침표와 SQL ID가 뒤따르는 schema-name입니다.

매개변수의 수와 함께 내재적 또는 명시적 규정자를 포함하는 이름은 카탈로그에서 설명된 프로시저를 식별하지 않아야 합니다(SQLSTATE 42723). 규정되지 않은 이름과 매개변수 수의 조합은 해당 스키마에서는 고유하지만 여러 스키마 사이에서는 고유하지 않아도 됩니다.

두 파트 이름이 지정되면, schema-name은 'SYS'로 시작될 수 없습니다. 따라서, 오류가 리턴됩니다(SQLSTATE 42939).

(IN | OUT | INOUT parameter-name data-type default-clause, ...)

프로시저의 매개변수를 식별하고 각 매개변수의 모드, 이름, 데이터 유형, 선택적 기본값을 지정합니다. 목록의 하나의 항목은 프로시저가 예상하는 각 매개변수에 대해 지정되어야 합니다.

매개변수가 없는 프로시저를 등록할 수 있습니다. 이 경우, 데이터 유형 개입 없이 괄호가 코드화되어야 합니다. 예를 들면, 다음과 같습니다.

   CREATE PROCEDURE SUBWOOFER() ...

스키마 내에서 동일하게 이름 지정된 두 개의 프로시저가 정확하게 동일한 수의 매개변수를 갖도록 허용되지 않습니다. 중복 시그니처로 인해 SQL 오류가 발생합니다(SQLSTATE 42723).

예를 들어, 다음 명령문이 제공됩니다.

   CREATE PROCEDURE PART (IN NUMBER INT, OUT PART_NAME CHAR(35)) ...
   CREATE PROCEDURE PART (IN COST DECIMAL(5,3), OUT COUNT INT) ...

두 번째 명령문은 데이터 유형이 아닐지라도 프로시저의 매개변수 수가 동일하므로 실패합니다.

IN | OUT | INOUT

매개변수의 모드를 지정합니다.

오류가 프로시저에 의해 리턴되는 경우 OUT 매개변수가 정의되지 않고 INOUT 매개변수가 변경되지 않습니다.

IN: 프로시저에 대한 입력 매개변수로 매개변수를 식별합니다. 프로시저 내의 매개변수에 작성된 변경사항은 제어가 리턴될 때 호출 SQL 애플리케이션에 사용될 수 없습니다. 기본값은 IN입니다.
OUT: 프로시저에 대한 출력 매개변수로 매개변수를 식별합니다.
INOUT: 프로시저에 대한 입력 및 출력 매개변수 모두로서 매개변수를 식별합니다.

parameter-name매개변수 이름을 지정합니다. 매개변수 이름은 프로시저에 대해 고유해야 합니다(SQLSTATE 42734).

data-type매개변수의 데이터 유형을 지정합니다. 구조화된 유형 또는 참조 유형을 지정할 수 없습니다(SQLSTATE 429BB).

내장 유형내장 데이터 유형을 지정합니다. BOOLEAN 및 CURSOR를 제외하고 테이블에 지정될 수 없는 각 내장 데이터 유형의 완전한 추가 설명은 "CREATE TABLE"을 참조하십시오.

BOOLEAN: 부울의 경우.
CURSOR: 기본 커서에 대한 참조의 경우.

앵커된 데이터 유형데이터 유형을 정의하기 위해 사용되는 다른 오브젝트를 식별합니다. 앵커 오브젝트의 데이터 유형에는 직접적으로 데이터 유형을 지정하거나 행의 경우 행 유형을 작성하는 데 적용하는 동일한 제한사항이 있습니다.

ANCHOR DATA TYPE TO

앵커된 데이터 유형이 데이터 유형을 식별하기 위해 사용된다는 것을 표시합니다.

변수 이름전역 변수를 식별합니다. 전역 변수의 데이터 유형은 parameter-name에 대한 데이터 유형으로 사용됩니다.

table-name.column-name기존 테이블 또는 뷰의 컬럼 이름을 식별합니다. 컬럼의 데이터 유형은 parameter-name에 대한 데이터 유형으로 사용됩니다.

ROW OF table-name 또는 view-name

table-name에 의해 식별된 테이블 또는 view-name에 의해 식별된 뷰의 컬럼 데이터 유형 및 컬럼 이름을 기반으로 하는 데이터 유형 및 이름으로 필드 행을 지정합니다. parameter-name의 데이터 유형은 unnamed row 자료형입니다.

ROW OF 커서 변수 이름

cursor-variable-name에 의해 식별되는 커서 변수의 필드 데이터 유형 및 필드 이름을 기반으로 하는 데이터유형 및 이름으로 필드의 행을 지정합니다. 지정된 커서 변수는 다음 요소 중 하나여야 합니다(SQLSTATE 428HS):

명백하게 유형 지정된 커서 데이터 유형을 가지는 전역 변수
모든 결과 컬럼이 이름 지정된 select-statement 를 지정하는 CONSTANT절로 작성되거나 선언되는 약하게 유형 지정된 커서 데이터 유형을 가지는 전역 변수.

커서 변수의 커서 유형이 이름 지정된 행 유형을 사용하여 명백하게 유형 지정되지 않는 경우, parameter-name의 데이터 유형은 unnamed row 자료형입니다.

배열 유형 이름

사용자 정의 배열 유형의 이름을 지정합니다. array-type-name이 스키마 이름 없이 지정되는 경우 배열 유형이 SQL 경로에서 스키마를 검색하여 해결됩니다.

커서 유형 이름

커서 유형의 이름을 지정합니다. cursor-type-name이 스키마 이름 없이 지정된 경우에는 커서 유형은 SQL 경로에서 스키마를 검색하여 해결됩니다.

구별 유형 이름

구별 유형의 이름을 지정합니다. 매개변수의 길이, 정밀도, 스케일은 각각 구별 유형의 소스 유형의 길이, 정밀도, 스케일입니다. 구별 유형 매개변수는 구별 유형의 소스 유형으로 전달됩니다. distinct-type-name이 스키마 이름 없이 지정된 경우에는 구별 유형은 SQL 경로에서 스키마를 검색하여 해결됩니다.

행 유형 이름

사용자 정의 행 유형의 이름을 지정합니다. 매개변수의 필드는 행 유형의 필드입니다. row-type-name이 스키마 이름 없이 지정된 경우 SQL 경로의 스키마를 검색하여 행 유형이 분석됩니다.

기본값

매개변수의 기본값을 지정합니다. 기본값은 상수, 특수 레지스터, 전역 변수, 표현식 또는 키워드 NULL이 될 수 있습니다. 기본값으로 지정될 수 있는 특수 레지스터는 컬럼 기본값에 지정될 수 있는 값과 동일합니다(CREATE TABLE문에서 default-clause 참조). 기타 특수 레지스터는 표현식을 사용하여 기본값으로 지정될 수 있습니다.

expression은 표현식에 설명된 유형의 표현식일 수 있습니다. 기본값이 지정되지 않으면, 매개변수에는 기본값이 없고 해당 인수는 프로시저의 호출에서 생략될 수 없습니다. 표현식의 최대 크기는 64K 바이트입니다.

기본 표현식은 SQL 데이터를 수정하지 않아야 합니다(SQLSTATE 428FL 또는 SQLSTATE 429BL). 표현식은 매개변수 데이터 유형에 호환 가능한 지정이어야 합니다(SQLSTATE 42821).

기본값은 다음 상황에 지정될 수 없습니다.

INOUT 또는 OUT 매개변수의 경우(SQLSTATE 42601)
ARRAY, ROW 또는 CURSOR 유형의 매개변수의 경우(SQLSTATE 429BB).

SPECIFIC 특정 이름

정의되는 프로시저의 인스턴스에 대한 고유 이름을 제공합니다. 이 특정 이름은 프로시저에서 변경, 삭제 또는 설명할 때 사용될 수 있습니다. 이는 프로시저를 호출하기 위해 사용될 수 없습니다. specific-name의 규정되지 않은 양식은 SQL ID입니다. 규정된 양식은 마침표와 SQL ID가 뒤따르는 schema-name입니다. 내재적 또는 명시적 규정자를 포함하는 이름은 애플리케이션 서버에 존재하는 다른 프로시저 인스턴스를 식별하지 않아야 합니다. 그렇지 않으면, 오류(SQLSTATE 42710)가 발생합니다.

specific-name은 기존 procedure-name과 같을 수 있습니다.

규정자가 지정되지 않은 경우에는 procedure-name에 사용된 규정자가 사용됩니다. 규정자가 지정되면 이는 procedure-name에 대한 명시적 또는 내재적 규정자와 동일하며, 그렇지 않으면 오류(SQLSTATE 42882)가 발생합니다.

specific-name이 지정되지 않은 경우에는 고유한 이름이 데이터베이스 관리자에 의해 생성됩니다. 고유 이름은 문자 시간소인 'SQLyymmddhhmmssxxx'이 뒤따르는 'SQL'입니다.

GET ROUTINE 명령을 사용하여 프로시저를 아카이브하려는 경우, 특정 이름에 최대 18자 길이가 있다는 것을 확인하십시오.

동적 결과 세트 integer

프로시저에 대한 리턴된 결과 세트의 추정되는 상한을 표시합니다.

MODIFIES SQL DATA, CONTAINS SQL, READS SQL DATA

이 프로시저에 의해 실행될 수 있거나 이 프로시저에 의해 호출되는 SQL문의 분류를 지정합니다. 데이터베이스 관리자는 프로시저 및 프로시저가 호출하는 모든 루틴에 의해 실행되는 SQL문이 이 스펙과 일관됨을 확인합니다.

각 명령문의 분류는 루틴 및 트리거에서 실행할 수 있는 SQL문을 참조하십시오.

기본값은 MODIFIES SQL DATA입니다.

MODIFIES SQL DATA: 프로시저에서 지원되지 않는 명령문을 제외하고 프로시저가 SQL문을 실행하는 것을 지정합니다(SQLSTATE 38003 또는 42985).
CONTAINS SQL: 프로시저가 CONTAINS SQL의 데이터 액세스 분류로만 명령문을 실행함을 지정합니다(SQLSTATE 38003 또는 38004 또는 42985).
READS SQL DATA: 프로시저는 READS SQL DATA 또는 CONTAINS SQL의 데이터 액세스 분류로 명령문을 실행함을 지정합니다(SQLSTATE 38002 또는 38003 또는 42985).

BEGIN ATOMIC절이 복합 SQL 프로시저에 사용되는 경우, MODIFIES SQL DATA로 정의되는 경우에만 프로시저가 작성될 수 있습니다.

DETERMINISTIC 또는 NOT DETERMINISTIC

이 절은 프로시저가 제공된 인수 값(DETERMINISTIC)에 대한 동일한 결과를 리턴하는지 여부 또는 프로시저가 결과에 영향을 주는 일부 상태 값(NOT DETERMINISTIC)에 따라 다른지 여부를 지정합니다. 즉, DETERMINISTIC 프로시저는 동일한 입력으로 성공적인 호출에서 동일한 결과를 리턴해야 합니다.

이 절은 현재 프로시저의 처리에 영향을 주지 않습니다.

CALLED ON NULL INPUT

CALLED ON NULL INPUT은 항상 프로시저에 적용됩니다. 이는 인수가 널(NULL)인지 여부와 상관 없이 프로시저가 호출됨을 의미합니다. OUT 또는 INOUT 매개변수는 널(NULL) 값 또는 일반(널이 아님) 값을 리턴할 수 있습니다. 널(NULL) 인수 값을 테스트하기 위한 의미가 프로시저에 있습니다.

COMMIT ON RETURN

커미트가 프로시저의 리턴에서 실행되는지 여부를 표시합니다. 기본값은 NO입니다.

아니오: 커미트는 프로시저가 리턴될 때 실행되지 않습니다.
예: 커미트는 양수 SQLCODE가 CALL문에 의해 리턴되는 경우 프로시저가 리턴될 때 실행됩니다.

커미트 조작에는 호출 애플리케이션 프로세스 및 프로시저에 의해 수행되는 작업이 포함됩니다.

프로시저가 결과 세트를 리턴하는 경우, 결과 세트와 연관되는 커서는 커미트 이후 사용할 수 있는 WITH HOLD로 정의됩니다.

AUTONOMOUS

프로시저가 자율 트랜잭션 범위에서 실행됨을 지정합니다.

INHERIT SPECIAL REGISTERS

이 선택적 절은 프로시저의 갱신 가능한 특수 레지스터가 호출 명령문의 환경에서 초기값을 상속한다는 것을 지정합니다. 중첩된 오브젝트에서 호출되는 루틴의 경우(예: 트리거 또는 뷰), 초기값은 런타임 환경에서 상속됩니다(오브젝트 정의에서 상속되지 않음).

프로시저의 호출자로 다시 전달되는 특수 레지스터에 대한 변경사항이 없습니다.

갱신할 수 없는 특수 레지스터(예: 날짜 및 시간 특수 레지스터)가 현재 실행 중인 명령문의 특성을 반영하므로 해당 기본값으로 설정됩니다.

OLD SAVEPOINT LEVEL 또는 NEW SAVEPOINT LEVEL

이 프로시저가 세이브포인트 이름 또는 효과에 대한 새 세이브포인트 레벨을 설정하는지 여부를 지정합니다. OLD SAVEPOINT LEVEL은 기본 동작입니다. 세이브포인트 레벨에 대한 자세한 정보는 SAVEPOINT에서 규칙을 참조하십시오.

LANGUAGE SQL

이 절은 프로시저 본문이 SQL 언어로 작성된다는 것을 지정하기 위해 사용됩니다.

EXTERNAL ACTION 또는 NO EXTERNAL ACTION

프로시저에서 데이터베이스 관리자가 관리하지 않는 오브젝트의 상태를 변경하는 일부 조치를 수행하는지(EXTERNAL ACTION) 또는 수행하지 않는지(NO EXTERNAL ACTION) 여부를 지정합니다. 기본값은 EXTERNAL ACTION입니다. NO EXTERNAL ACTION이 지정되는 경우, 시스템은 프로시저에 외부 영향이 없다고 가정하는 특정 최적화를 사용할 수 있습니다.

PARAMETER CCSID

프로시저 간에 전달되는 모든 문자열 데이터에 사용하기 위한 인코딩 체계를 지정합니다. PARAMETER CCSID절이 지정되지 않은 경우, 기본값은 유니코드 데이터베이스의 경우 PARAMETER CCSID UNICODE이고 기타 모든 데이터베이스의 경우 PARAMETER CCSID ASCII입니다.

ASCII: 문자열 데이터가 데이터베이스 코드 페이지에서 인코딩됨을 지정합니다. 데이터베이스가 유니코드 데이터베이스인 경우, PARAMETER CCSID ASCII는 지정할 수 없습니다(SQLSTATE 56031).
UNICODE: 문자 데이터가 UTF-8이고 그래픽 데이터가 UCS-2임을 지정합니다. 데이터베이스가 유니코드 데이터베이스가 아닌 경우, PARAMETER CCSID UNICODE는 지정될 수 없습니다(SQLSTATE 56031).

SQL-procedure-body

SQL 프로시저의 본문인 SQL문을 지정합니다.

복합 SQL (컴파일된) 문의 SQL-procedure-statement 를 참조하십시오.

규칙

자율 루틴 제한사항: 자율 루틴은 결과 세트를 리턴할 수 없고 다음 데이터 유형을 지원하지 않습니다(SQLSTATE 428H2).
- 사용자 정의 커서 유형
- 사용자 정의 구조화 유형
- IN, OUT 및 INOUT 매개변수로 XML
커서 유형의 세션 변수는 자율 범위 내에서 참조될 수 없습니다.
앵커된 데이터 유형의 사용: 앵커된 데이터 유형은 별칭, 유형이 지정된 테이블, 유형이 지정된 뷰, 표현식 기반 인덱스와 연관되는 통계 뷰, 선언된 임시 테이블, 약하게 유형 지정된 커서와 연관되는 행 정의, 데이터베이스 코드 페이지 또는 데이터베이스 데이터 정렬과 다른 데이터 정렬이나 코드 페이지를 가지고 있는 오브젝트를 참조할 수 없습니다(SQLSTATE 428HS).
커서 및 행 유형의 사용: 매개변수의 커서 유형 또는 행 유형을 사용하는 프로시저는 복합 SQL문(컴파일된 명령문)에서만 호출할 수 있습니다(SQLSTATE 428H2). 커서 유형이 있는 OUT 매개변수를 사용하여 프로시저를 호출할 수 있는 JDBC를 사용한 Java 애플리케이션의 경우는 예외입니다. Java 외부 프로시저로부터의 호출은 지원되지 않습니다.

참고

명령문의 권한 부여 ID에 IMPLICIT_SCHEMA 권한이 있는 경우, 존재하지 않는 스키마 이름으로 프로시저를 작성하면 스키마가 내재적으로 작성됩니다. 스키마 소유자는 SYSIBM입니다. 스키마에 대한 CREATEIN 특권이 PUBLIC에 부여됩니다.
복합 SQL(인라인된)문에서 호출되는 프로시저는 프로시저가 작성될 때 OLD SAVEPOINT LEVEL이 지정되거나 기본값으로 지정된다고 해도 NEW SAVEPOINT LEVEL 지정을 작성한 것처럼 실행됩니다.
초기에 유효하지 않은 프로시저 작성: 프로시저 본문에 참조된 오브젝트가 존재하지 않거나 유효하지 않은 것으로 표시되거나 정의자가 임시로 오브젝트에 액세스할 수 있는 특권이 없고 데이터베이스 구성 매개변수 auto_reval이(가) DISABLED로 설정되지 않은 경우 프로시저는 여전히 작성됩니다. 프로시저는 유효하지 않은 것으로 표시되고 다음 번에 호출될 때 다시 유효성 검증됩니다.
기본값의 설정: 값이 해당 인수에 제공되지 않거나 프로시저가 호출될 때 DEFAULT로 지정되는 경우에만 기본값으로 정의되는 프로시저의 매개변수는 프로시저가 호출될 때 기본값으로 설정됩니다.
특권: 프로시저의 정의자는 프로시저를 삭제하기 위한 권리뿐만 아니라 프로시저에 대한 EXECUTE 특권 WITH GRANT OPTION을 수신합니다.
종속 패키지 리바인딩: 모든 SQL 프로시저에는 종속 패키지가 있습니다. 패키지는 REBIND_ROUTINE_PACKAGE 프로시저를 실행하여 언제든지 리바인드될 수 있습니다. 종속 패키지를 명시적으로 리바인드하면 유효하지 않은 프로시저를 재확인하지 않습니다. 유효하지 않은 프로시저는 자동 재확인 또는 ADMIN_REVALIDATE_DB_OBJECTS 프로시저를 명시적으로 실행하여 재확인되어야 합니다. 프로시저 재확인은 종속 패키지를 자동으로 리바인드합니다.
구문 대체: 다음 구문 대체는 이전 버전의 Db2® 및 기타 데이터베이스 제품과의 호환성을 위해 지원됩니다. 이러한 대체는 비표준이므로 사용하지 않아야 합니다.
- RESULT SETS는 DYNAMIC RESULT SETS 대신 지정될 수 있습니다.
- NULL CALL은 CALLED ON NULL INPUT 대신 지정될 수 있습니다.
다음 구문은 기본 동작으로 허용됩니다.
- ASUTIME NO LIMIT
- NO COLLID
- STAY RESIDENT NO

예

중앙 스태프 급여를 리턴하는 SQL 프로시저를 작성하십시오. 중앙 급여 이상을 획득하는 모든 직원의 이름, 위치 및 급여를 포함하는 결과 세트를 리턴하십시오.

   CREATE PROCEDURE MEDIAN_RESULT_SET (OUT medianSalary DOUBLE)
     RESULT SETS 1
     LANGUAGE SQL
   BEGIN
     DECLARE v_numRecords INT DEFAULT 1;
     DECLARE v_counter INT DEFAULT 0;

     DECLARE c1 CURSOR FOR
       SELECT CAST(salary AS DOUBLE)
         FROM staff
         ORDER BY salary;
     DECLARE c2 CURSOR WITH RETURN FOR
       SELECT name, job, CAST(salary AS INTEGER)
         FROM staff
         WHERE salary > medianSalary
         ORDER BY salary;

     DECLARE EXIT HANDLER FOR NOT FOUND
       SET medianSalary = 6666;

     SET medianSalary = 0;
     SELECT COUNT(*) INTO v_numRecords
       FROM STAFF;
     OPEN c1;
     WHILE v_counter < (v_numRecords / 2 + 1)
     DO
       FETCH c1 INTO medianSalary;
       SET v_counter = v_counter + 1;
     END WHILE;
     CLOSE c1;
     OPEN c2;
   END

CREATE PROCEDURE (SQL) 문

호출

권한 부여

구문

설명

규칙

참고

예