데브러너 (DevRunner)

27.10 Stored Program에 대한 제한 사항

이러한 제한 사항은
Chapter 27, Stored Objects에 설명된 기능들에 적용됩니다.

여기에 언급된 제한 사항 중 일부는 모든 stored routine에 적용됩니다. 즉, stored procedure와 stored function 둘 다에 해당합니다.

또한, stored procedure에는 적용되지 않지만 stored function에만 특화된 제한 사항들도 있습니다.

stored function에 대한 제한 사항은 trigger에도 적용됩니다. 또한 trigger에만 특화된 제한 사항들도 있습니다.

stored procedure에 대한 제한 사항은 Event Scheduler event 정의의 DO 절에도 적용됩니다. 또한 event에만 특화된 제한 사항도 있습니다.

Stored Routine에서 허용되지 않는 SQL Statements

Stored routine은 임의의 SQL statement를 포함할 수 없습니다. 다음 statements는 허용되지 않습니다:

locking statements인 LOCK TABLES 및 UNLOCK TABLES.
ALTER VIEW.
LOAD DATA 및 LOAD XML.
SQL prepared statement ( PREPARE, EXECUTE, DEALLOCATE PREPARE)는 stored procedure에서 사용할 수 있지만, stored function이나 trigger에서는 사용할 수 없습니다. 따라서 stored function과 trigger에서는 (동적 SQL) (statement를 문자열로 구성한 뒤 실행하는 방식)을 사용할 수 없습니다.
일반적으로 SQL prepared statement에서 허용되지 않는 statement는 stored program에서도 허용되지 않습니다. prepared statement로서 지원되는 statement 목록은 Section 15.5, “Prepared Statements”를 참조하십시오. 예외로, SIGNAL, RESIGNAL, GET DIAGNOSTICS는 prepared statement로는 허용되지 않지만 stored program에서는 허용됩니다.
local variable은 stored program이 실행되는 동안에만 scope 내에 있으므로, stored program 내에서 생성된 prepared statement에서는 local variable에 대한 참조가 허용되지 않습니다. Prepared statement의 scope는 stored program이 아니라 현재 세션이므로, statement는 program이 종료된 이후에 실행될 수도 있고, 그 시점에는 variable이 더 이상 scope에 있지 않게 됩니다.

예를 들어 SELECT ... INTO local_var는 prepared statement로 사용할 수 없습니다. 이 제한은 stored procedure 및 function parameter에도 적용됩니다. 자세한 내용은 Section 15.5.1, “PREPARE Statement”를 참조하십시오.

모든 stored program (stored procedure 및 function, trigger, event) 내에서는 파서가 BEGIN [WORK]를 BEGIN ... END 블록의 시작으로 처리합니다.

stored procedure나 event 내에서 트랜잭션을 시작하려면 START TRANSACTION을 사용하십시오.

START TRANSACTION은 stored function이나 trigger 내에서는 사용할 수 없습니다.

Stored Function에 대한 제한 사항

다음과 같은 추가 statement 또는 연산은 stored function 내에서는 허용되지 않습니다. 이들은 stored procedure 내에서는 허용되지만, stored function이나 trigger 내부에서 호출되는 stored procedure에서는 허용되지 않습니다. 예를 들어 stored procedure에서 FLUSH를 사용하는 경우, 그 stored procedure는 stored function이나 trigger에서 호출될 수 없습니다.

명시적 또는 암시적 commit 또는 rollback을 수행하는 statement. 이러한 statement에 대한 지원은 SQL 표준에서 요구되지 않으며, 각 DBMS 벤더가 이를 허용할지 여부를 직접 결정하도록 되어 있습니다.
결과 집합을 반환하는 statement. 여기에는 INTO var_list 절이 없는 SELECT statement와 SHOW, EXPLAIN, CHECK TABLE과 같은 다른 statement가 포함됩니다. function은 SELECT ... INTO var_list를 사용하거나 cursor와 FETCH statement를 사용하여 결과 집합을 처리할 수 있습니다. Section 15.2.13.1, “SELECT ... INTO Statement” 및 Section 15.6.6, “Cursors”를 참조하십시오.
FLUSH statements.
Stored function은 재귀적으로 사용할 수 없습니다.
stored function 또는 trigger는 해당 function 또는 trigger를 호출한 statement가 이미 사용 중인 테이블(읽기 또는 쓰기)을 수정할 수 없습니다.
stored function에서 하나의 임시 테이블을 서로 다른 alias로 여러 번 참조하는 경우, 비록 function 내의 서로 다른 statement에서 발생하더라도 Can't reopen table: 'tbl_name' 오류가 발생합니다.
stored function을 호출하는 HANDLER ... READ statement는 replication 오류를 유발할 수 있으므로 허용되지 않습니다.

Trigger에 대한 제한 사항

Trigger에는 다음과 같은 추가 제한 사항이 적용됩니다:

Trigger는 외래 키 동작에 의해 활성화되지 않습니다.
row-based replication을 사용하는 경우, source에서 유래한 statement에 의해 replica의 trigger는 활성화되지 않습니다. statement-based replication을 사용할 때에만 replica의 trigger가 활성화됩니다. 자세한 내용은 Section 19.5.1.37, “Replication and Triggers”를 참조하십시오.
RETURN statement는 값을 반환할 수 없는 trigger에서는 허용되지 않습니다. trigger를 즉시 종료하려면 LEAVE statement를 사용하십시오.
mysql 데이터베이스의 테이블에는 trigger가 허용되지 않습니다. INFORMATION_SCHEMA나 performance_schema 테이블에도 trigger가 허용되지 않습니다. 이들 테이블은 실제로 view이며, view에는 trigger가 허용되지 않습니다.
trigger 캐시는 기반 객체의 메타데이터가 변경되었는지를 감지하지 못합니다. trigger에서 사용하는 테이블이 있고, 해당 테이블이 trigger가 캐시에 로드된 이후에 변경되었다면, trigger는 변경 전의 오래된 메타데이터를 사용하여 동작합니다.

Stored Routine 내의 이름 충돌

동일한 identifier가 routine parameter, local variable, 테이블 컬럼에 대해 사용될 수 있습니다. 또한 중첩 블록 내에서 동일한 local variable 이름을 사용할 수도 있습니다. 예:

1CREATE PROCEDURE p (i INT)
2BEGIN
3  DECLARE i INT DEFAULT 0;
4  SELECT i FROM t;
5  BEGIN
6    DECLARE i INT DEFAULT 1;
7    SELECT i FROM t;
8  END;
9END;

이러한 경우 identifier는 모호하며, 다음의 우선순위 규칙이 적용됩니다:

local variable은 routine parameter나 테이블 컬럼보다 우선합니다.
routine parameter는 테이블 컬럼보다 우선합니다.
inner 블록의 local variable은 outer 블록의 local variable보다 우선합니다.

variable이 테이블 컬럼보다 우선하는 이 동작은 비표준입니다.

Replication 관련 고려 사항

Stored routine의 사용은 replication 문제를 야기할 수 있습니다. 이 문제는 Section 27.9, “Stored Program Binary Logging”에서 추가로 설명합니다.

--replicate-wild-do-table=db_name.tbl_name 옵션은 테이블, view, trigger에 적용됩니다. stored procedure, stored function, event에는 적용되지 않습니다. 후자의 객체들에 대해 동작하는 statement를 필터링하려면 --replicate-*-db 옵션을 하나 이상 사용하십시오.

Debugging 관련 고려 사항

Stored routine에 대한 디버깅 기능은 제공되지 않습니다.

SQL:2003 Standard에서 지원되지 않는 Syntax

MySQL stored routine syntax는 SQL:2003 표준을 기반으로 합니다. 이 표준의 항목 중 다음은 현재 지원되지 않습니다:

UNDO handler
FOR loop

Stored Routine 동시성 관련 고려 사항

세션 간 상호 작용 문제를 방지하기 위해, 클라이언트가 statement를 발행하면 서버는 해당 statement 실행 시 사용 가능한 routine 및 trigger의 스냅샷을 사용합니다. 즉, 서버는 statement 실행 동안 사용될 수 있는 procedure, function, trigger 목록을 계산하고, 이를 로드한 다음 statement 실행을 진행합니다. statement가 실행되는 동안, 다른 세션에 의해 수행되는 routine 변경 사항은 보이지 않습니다.

최대 동시성을 위해 stored function은 부작용을 최소화해야 합니다. 특히, stored function 내에서 테이블을 갱신하는 것은 해당 테이블에 대한 동시 작업을 감소시킬 수 있습니다. stored function은 실행 전에 테이블 락을 획득하여, statement가 실행되는 순서와 binary log에 나타나는 순서가 일치하지 않아 binary log에 불일치가 발생하는 것을 방지합니다.

statement 기반 바이너리 로깅이 사용되는 경우, function을 호출하는 statement가 기록되며, function 내에서 실행되는 statement는 기록되지 않습니다. 따라서 동일한 기반 테이블을 갱신하는 stored function들은 병렬로 실행되지 않습니다.

반대로 stored procedure는 테이블 수준 락을 획득하지 않습니다. statement 기반 바이너리 로깅에서도 stored procedure 내에서 실행되는 모든 statement는 binary log에 기록됩니다. 자세한 내용은 Section 27.9, “Stored Program Binary Logging”을 참조하십시오.

Event Scheduler 제한 사항

다음 제한 사항은 Event Scheduler에만 특화된 것입니다:

Event 이름은 대소문자를 구분하지 않고 처리됩니다. 예를 들어 동일한 데이터베이스 내에 anEvent와 AnEvent라는 이름의 두 event를 둘 다 가질 수 없습니다.
Stored program 내부에서 event를 생성할 수 없습니다. event 이름이 variable로 지정되는 경우, stored program 내부에서 event를 변경하거나(drop 포함) 할 수 없습니다. event는 stored routine이나 trigger를 생성, 변경, 또는 drop할 수도 없습니다.
LOCK TABLES statement가 효력을 발휘하는 동안에는 event에 대한 DDL statement가 금지됩니다.
YEAR, QUARTER, MONTH, YEAR_MONTH interval을 사용하는 event timing은 월 단위로 처리되며, 그 외의 interval을 사용하는 경우 초 단위로 처리됩니다. 동일한 초에 실행되도록 예약된 event를 특정 순서로 실행되게 하는 방법은 없습니다. 또한 반올림, 스레드 기반 애플리케이션의 특성, event를 생성하고 실행을 signal하는 데 필요한 비영(非零) 길이의 시간 때문에, event는 1~2초까지 지연될 수 있습니다.

그러나 Information Schema의 EVENTS 테이블의 LAST_EXECUTED 컬럼에 표시되는 시간은 실제 event 실행 시각으로부터 항상 최대 1초 이내의 정확도를 가집니다. (Bug #16522도 참조하십시오.)

event body에 포함된 statement의 각 실행은 새로운 커넥션에서 이루어집니다. 따라서 이들 statement는 서버의 Com_select, Com_insert와 같이 SHOW STATUS에 의해 표시되는 statement count에 대해, 특정 사용자 세션에는 아무런 영향을 미치지 않습니다. 그러나 이러한 count는 글로벌 스코프에서는 _갱신_됩니다. (Bug #16422)
Event는 Unix Epoch가 끝난 이후 시점의 time은 지원하지 않습니다. 이는 대략 2038년 초입니다. 이러한 date는 Event Scheduler에서 명시적으로 허용되지 않습니다. (Bug #16396)
CREATE EVENT 및 ALTER EVENT statement의 ON SCHEDULE 절에서 stored function, loadable function, 테이블에 대한 참조는 지원되지 않습니다. 이러한 종류의 참조는 허용되지 않습니다. (자세한 내용은 Bug #22830을 참조하십시오.)

NDB Cluster에서의 Stored routine과 Trigger

Stored procedure, stored function, trigger, scheduled event는 모두 NDB 스토리지 엔진을 사용하는 테이블에서 지원되지만, 이들이 MySQL Server 간(Cluster SQL node로 동작하는) 사이에 자동으로 전파되지는 않는다는 점을 기억해야 합니다. 이는 stored routine 및 trigger 정의가 Cluster node 간에 복사되지 않는 InnoDB 테이블을 사용하여 mysql 시스템 데이터베이스의 테이블에 저장되기 때문입니다.

MySQL Cluster 테이블과 상호 작용하는 모든 stored routine이나 trigger는, 해당 stored routine이나 trigger를 사용하려는 클러스터에 참여하는 각 MySQL Server에서 적절한 CREATE PROCEDURE, CREATE FUNCTION, CREATE TRIGGER statement를 실행하여 다시 생성해야 합니다. 마찬가지로 기존 stored routine이나 trigger에 대한 모든 변경 사항은 클러스터에 접근하는 모든 Cluster SQL node에서 적절한 ALTER 또는 DROP statement를 사용하여 명시적으로 수행해야 합니다.

주의

위에서 설명한 문제를 우회하기 위해 어떠한 mysql 데이터베이스 테이블이라도 NDB 스토리지 엔진을 사용하도록 변환하려고 시도하지 마십시오. mysql 데이터베이스의 시스템 테이블을 변경하는 것은 지원되지 않으며, 원치 않는 결과를 초래할 매우 높은 가능성이 있습니다.

27.10 Restrictions on Stored Programs