13.1.15 CREATE PROCEDURE 및 CREATE FUNCTION 구문

CREATE
    [DEFINER = { user | CURRENT_USER }]
    PROCEDURE sp_name ([proc_parameter[,...]])
    [characteristic ...] routine_body

CREATE
    [DEFINER = { user | CURRENT_USER }]
    FUNCTION sp_name ([func_parameter[,...]])
    RETURNS type
    [characteristic ...] routine_body

proc_parameter:
    [ IN | OUT | INOUT ] param_name type

func_parameter:
    param_name type

type:
    Any valid MySQL data type

characteristic:
    COMMENT 'string'
  | LANGUAGE SQL
  | [NOT] DETERMINISTIC
  | { CONTAINS SQL | NO SQL | READS SQL DATA | MODIFIES SQL DATA }
  | SQL SECURITY { DEFINER | INVOKER }

routine_body:
    Valid SQL routine statement

이 문은 스토어드 루틴을 만듭니다. 기본적으로 루틴은 기본 데이터베이스에 연결됩니다. 루틴을 명시 적으로 특정 데이터베이스에 연결하려면 루틴을 작성할 때 해당 이름을 db_name.sp_name 로 지정합니다.

CREATE FUNCTION 문은 또한 UDF (사용자 정의 함수)을 지원하기 위해 MySQL에서도 사용됩니다. 섹션 24.3 "MySQL에 새로운 기능 추가" 를 참조하십시오. UDF는 외부 스토어드 함수로 간주 될 수 있습니다. 스토어드 함수는 네임 스페이스를 UDF와 공유합니다. 각종 함수에 대한 참조를 서버가 해석하는 방법을 설명하는 규칙 내용은 섹션 9.2.4 "함수 이름의 구문 분석과 해결" 을 참조하십시오.

저장 프로 시저를 호출하려면 CALL 문을 사용합니다 ( 섹션 13.2.1 "CALL 구문" 을 참조하십시오). 스토어드 함수를 호출하는 식으로 함수를 참조합니다. 그 함수는 식의 평가 중에 값을 반환합니다.

CREATE PROCEDURE 및 CREATE FUNCTION 에는 CREATE ROUTINE 권한이 있어야합니다. 이 섹션의 나머지 부분에서 설명 된 바와 같이, DEFINER 값에 따라 SUPER 권한이 필요할 수 있습니다. 바이너리 로깅이 활성화되어있는 경우 섹션 20.7 "저장 프로그램의 바이너리 로깅" 에서 설명 된 바와 같이, CREATE FUNCTION 에 SUPER 권한이 필요할 수 있습니다.

기본적으로 MySQL은 루틴 작성자에게 ALTER ROUTINE 및 EXECUTE 권한을 자동으로 부여합니다. 이 동작은 automatic_sp_privileges 시스템 변수를 비활성화하여 변경할 수 있습니다. 섹션 20.2.2 "스토어드 루틴과 MySQL 권한" 을 참조하십시오.

DEFINER 및 SQL SECURITY 절은이 섹션의 나머지 부분에서 설명 된대로 루틴 실행시 액세스 권한을 확인하는 데 사용되는 보안 컨텍스트를 지정합니다.

루틴 이름이 내장 SQL 함수의 이름과 동일한 경우에는 그 루틴을 정의하거나 나중에 호출 할 때 이름과 거기에 계속되는 괄호 사이에 공백을 사용하지 않는 한, 구문 오류가 발생합니다. 따라서 사용자 고유의 스토어드 루틴에 기존의 SQL 함수의 이름을 사용하는 것은 피하십시오.

IGNORE_SPACE SQL 모드는 스토어드 루틴이 아닌 내장 함수에 적용됩니다. 스토어드 루틴 이름 뒤의 공간은 IGNORE_SPACE 가 활성화되어 있는지 여부에 관계없이 항상 허용됩니다.

괄호로 둘러싸인 파라미터리스트는 항상 존재해야합니다. 매개 변수가없는 경우는 () 의 빈 파라미터리스트를 사용하도록하십시오. 매개 변수 이름은 대소 문자를 구분하지 않습니다.

각 매개 변수는 기본적으로 IN 매개 변수입니다. 그렇지 매개 변수를 지정하려면 매개 변수 이름 앞에 키워드 OUT 또는 INOUT 를 사용합니다.

IN 매개 변수는 프로 시저에 값을 전달합니다. 프로 시저가 해당 값을 변경할 수 있지만 프로 시저에서 돌아 왔을 때, 그 변경은 호출자에게 표시되지 않습니다. OUT 매개 변수는 프로 시저에서 호출자에게 값을 전달합니다. 초기 값은 프로 시저에서 NULL 이며, 프로 시저에서 돌아 왔을 때, 그 값은 호출자에게 표시됩니다. INOUT 매개 변수는 호출자에 의해 초기화 된 프로 시저에서 변경할 수 있습니다. 프로 시저에서 돌아 왔을 때 프로 시저에서 변경된 모든 호출자에 표시됩니다.

OUT 또는 INOUT 매개 변수에 대해 프로 시저를 호출 CALL 문에서 사용자 정의 변수를 전달하여 프로 시저에서 돌아 왔을 때 그 가치를 얻을 수 있도록합니다. 프로 시저를 다른 저장 프로 시저 또는 저장 함수 내에서 호출하는 경우, IN 또는 INOUT 매개 변수로 루틴 파라미터 또는 로컬 루틴 변수를 전달할 수 있습니다.

루틴 내에 준비된 문에서 루틴 매개 변수를 참조 할 수 없습니다. 섹션 D.1 "저장 프로그램 제한 사항" 을 참조하십시오.

다음의 예는 OUT 매개 변수를 사용하는 간단한 저장 프로 시저를 보여줍니다.

mysql> delimiter //

mysql> CREATE PROCEDURE simpleproc (OUT param1 INT)
    -> BEGIN
    ->   SELECT COUNT(*) INTO param1 FROM t;
    -> END//
Query OK, 0 rows affected (0.00 sec)

mysql> delimiter ;

mysql> CALL simpleproc(@a);
Query OK, 0 rows affected (0.00 sec)

mysql> SELECT @a;
+------+
| @a   |
+------+
| 3    |
+------+
1 row in set (0.00 sec)

이 예에서는 프로 시저 정의 중에 mysql 클라이언트 delimiter 명령을 사용하여 문 구분자를 ; 로부터 // 로 변경하고 있습니다. 그러면 프로 시저 본문에서 사용되는 ; 구분자를 mysql 자체가 해석하는 것이 아니라 서버에 전달할 수 있습니다. 섹션 20.1 "저장 프로그램의 정의" 를 참조하십시오.

RETURNS 절은 FUNCTION (여기에는 필수입니다)에만 사용할 수 있습니다. 이 함수의 반환 형식을 나타내는 것이며, 함수 본체에는 RETURN value 명령문이 포함되어 있어야합니다. RETURN 문이 다른 형태의 값을 돌려 주었을 경우, 그 값은 올바른 형식으로 강제로 변경됩니다. 예를 들어, 함수가 RETURNS 절에 ENUM 또는 SET 값을 지정하고 있지만, RETURN 문이 정수를 반환하면 그 함수에서 반환되는 값은 SET 멤버 세트의 해당 ENUM 멤버를 나타내는 문자열입니다 합니다.

다음의 함수 예는 매개 변수를 받아 SQL 함수를 사용하여 작업을 수행 한 후 결과를 반환합니다. 이 경우 함수 정의 내부의 ; 문 구분자가 포함되어 있지 않기 때문에, delimiter 를 사용할 필요가 없습니다.

mysql> CREATE FUNCTION hello (s CHAR(20))
mysql> RETURNS CHAR(50) DETERMINISTIC
    -> RETURN CONCAT('Hello, ',s,'!');
Query OK, 0 rows affected (0.00 sec)

mysql> SELECT hello('world');
+----------------+
| hello('world') |
+----------------+
| Hello, world!  |
+----------------+
1 row in set (0.00 sec)

매개 변수 유형과 함수의 반환 형식은 유효한 데이터 형식을 사용하도록 선언 ​​할 수 있습니다. 전에 CHARACTER SET 속성이있는 경우는 COLLATE 속성을 사용할 수 있습니다.

routine_body 은 유효한 SQL 루틴 문으로 구성됩니다. 이것은 SELECT 및 INSERT 등의 간단한 명령문은 BEGIN 과 END 를 사용하여 작성된 복합 문이 될 수 있습니다. 복합 문은 선언, 루프 및 기타 제어 구조 문을 포함 할 수 있습니다. 이 문의 구문은 섹션 13.6 "MySQL 복합 문"부분 에 설명되어 있습니다.

MySQL은 루틴 CREATE 및 DROP 과 같은 DDL 문을 포함 할 수 있도록합니다. MySQL은 또한 저장 프로 시저에 COMMIT 같은 SQL 트랜젝션을 갖는 것도 허용됩니다 (단, 스토어드 함수는 허용되지 않습니다). 스토어드 함수는 명시 적 또는 암시 적 커밋 또는 롤백을 수행하는 문을 포함 할 수 없습니다. 이러한 진술의 지원은 SQL 표준에서는 필요하지 않습니다. SQL 표준에서는 각 DBMS 벤더들이 문을 허용할지 여부를 결정할 수있는 규정하고 있습니다.

결과 집합을 반환하는 문은 저장 프로 시저에서 사용할 수 있지만, 스토어드 함수 내에서는 사용할 수 없습니다. 이 금지는 INTO var_list 절이없는 SELECT 문과 SHOW , EXPLAIN , CHECK TABLE 과 같은 다른 문이 포함됩니다. 결과 집합을 반환하는 함수를 정의 할 때 판정 할 수있는 문의 경우는 Not allowed to return a result set from a function 오류가 발생합니다 ( ER_SP_NO_RETSET ). 결과 집합을 반환하는 것을 런타임에만 결정할 수없는 문의 경우 PROCEDURE %s can't return a result set in the given context 오류가 발생합니다 ( ER_SP_BADSELECT ).

스토어드 루틴 내에서 USE 문은 허용되지 않습니다. 루틴이 호출 될 때 암시 적 USE db_name 이 실행됩니다 (또한 그 루틴이 종료하면 원래대로 복구됩니다). 그러면 그 루틴은 실행중인 특정 기본 데이터베이스가 할당됩니다. 루틴의 기본 데이터베이스가 아닌 데이터베이스의 개체에 대한 참조 적절한 데이터베이스 이름으로 규정하도록하십시오.

스토어드 루틴 내에서 허용되지 않는 문 자세한 내용은 섹션 D.1 "저장 프로그램 제한 사항" 을 참조하십시오.

MySQL 인터페이스를 갖는 언어로 작성된 프로그램 내에서 저장 프로 시저 호출에 대해서는 섹션 13.2.1 "CALL 구문" 을 참조하십시오.

MySQL은 루틴이 작성 또는 변경되었을 때 유효한 sql_mode 시스템 변수의 설정을 저장하고 루틴이 실행을 시작했을 때 현재의 서버 SQL 모드에 관계없이 항상 루틴을 강제로이 설정 에서 실행합니다.

호출 SQL 모드에서 루틴의 SQL 모드로 전환 인자를 평가하고 결과 값을 루틴 매개 변수에 할당 한 후에 실행됩니다. 루틴을 엄격한 SQL 모드에서 정의했지만, 그 호출을 비 엄밀 모드로 행한 경우는 인수의 루틴 파라미터에 할당 엄격 모드에서 실행되지 않습니다. 루틴에 전달되는 식을 엄격한 SQL 모드에서 할당해야하는 경우에는 그 루틴을 엄격 모드가 활성화 된 상태에서 호출하도록하십시오.

COMMENT 특성은 MySQL 확장이며, 그 스토어드 루틴의 설명을 위해 사용할 수 있습니다. 이 정보는 SHOW CREATE PROCEDURE 및 SHOW CREATE FUNCTION 문에 의해 표시됩니다.

LANGUAGE 특성은 루틴이 작성되는 언어를 나타냅니다. 서버는이 특성을 무시합니다. SQL 루틴 만 지원되고 있습니다.

루틴은 같은 입력 매개 변수에 대해 항상 동일한 결과를 생성하는 경우 "결정적"이라고 보여져 그 이외의 경우는 "확정되지 않은"것으로 간주됩니다. 루틴 정의에서 DETERMINISTIC 와 NOT DETERMINISTIC 을 모두 지정되지 않은 경우, 디폴트는 NOT DETERMINISTIC 됩니다. 함수가 결정적임을 선언에는 명시 적으로 DETERMINISTIC 을 지정해야합니다.

루틴의 성격 평가는 작성자의 「성실」에 근거하고 있습니다. MySQL은 DETERMINISTIC 으로 선언 된 루틴에 비 결정적인 결과를 생성하는 문이 포함되어 있는지 여부를 확인하지 않습니다. 그러나 일상의 잘못된 선언은 그 결과와 성능에 영향을 미칠 수 있습니다. 비 결정적 루틴을 DETERMINISTIC 으로 선언하면 최적화가 잘못된 실행 계획을 선택하기 위해 예기치 않은 결과를 초래할 수 있습니다. 결정적인 루틴을 NONDETERMINISTIC 로 선언하면 사용 가능한 최적화를 사용하지 않기 때문에 성능이 저하 될 수 있습니다.

바이너리 로깅이 활성화되어있는 경우 DETERMINISTIC 특성은 MySQL이 어떤 루틴 정의를 받아 들일지에 영향을 미칩니다. 섹션 20.7 "저장 프로그램의 바이너리 로깅" 을 참조하십시오.

NOW() 함수 (또는 동의어) 또는 RAND() 를 포함 루틴은 비 결정적이지만, 계속 복제에 대해 안전하다 할 수 있습니다. NOW() 의 경우, 바이너리 로그는 타임 스탬프를 포함하고 올바르게 복제됩니다. RAND() 도 또한 루틴 중에 한 번만 호출되는 한, 제대로 복제됩니다. (루틴 실행 타임 스탬프 및 난수 시드 마스터와 슬레이브에서 동일한 암시 적 입력으로 볼 수 있습니다.)

몇 가지 특성에 의해 루틴에 의한 데이터 사용의 성질에 대한 정보가 제공됩니다. MySQL에서는 이러한 특성은 권고에 지나지 않습니다. 서버가 이들을 사용하여 루틴에 어떤 종류의 문 실행을 허용 할 것인지를 제약하는 것은 아닙니다.

SQL SECURITY 특성은 보안 컨텍스트를 지정하는 DEFINER 또는 INVOKER 중 하나입니다. 이것은 루틴이 루틴 DEFINER 절에 지정된 계정 또는 루틴을 호출하는 사용자의 어떤 권한을 사용하여 실행되는지를 보여줍니다. 이 계정에는 그 루틴이 연결된 데이터베이스에 액세스하기위한 권한이 필요합니다. 기본값은 DEFINER 입니다. 그 루틴을 호출하는 사용자는 그에 대한 EXECUTE 권한이 필요합니다. 또한 루틴이 정의의 보안 컨텍스트에서 실행되는 경우 DEFINER 계정에이 권한이 필요합니다.

DEFINER 절은 SQL SECURITY DEFINER 특성을 가진 루틴 루틴 실행시 액세스 권한을 확인하는 데 사용되는 MySQL 계정을 지정합니다.

DEFINER 절에 user 값을 지정하려면 ' user_name '@' host_name ' ( GRANT 문에 사용되는 것과 같은 형식), CURRENT_USER 또는 CURRENT_USER() 로 지정된 MySQL 계정하도록하십시오. DEFINER 의 기본값은 CREATE PROCEDURE 또는 CREATE FUNCTION 문을 실행하는 사용자입니다. 이것은 명시 적으로 DEFINER = CURRENT_USER 를 지정하는 것과 동일합니다.

DEFINER 절을 지정한 경우 다음의 규칙에 따라 유효한 DEFINER 사용자 값이 결정됩니다.

스토어드 루틴의 보안 자세한 내용은 섹션 20.6 "저장 프로그램 및 뷰의 액세스 제어" 를 참조하십시오.

SQL SECURITY DEFINER 특성을 사용하여 정의 된 스토어드 루틴 내에서 CURRENT_USER 그 루틴 DEFINER 값을 반환합니다. 스토어드 루틴의 사용자 감사 내용은 섹션 6.3.13 "SQL 기반의 MySQL 계정 활동 감사" 를 참조하십시오.

mysql.user 테이블에 나열되어있는 MySQL 계정의 수를 표시하는 다음 프로 시저를 생각해 보겠습니다.

 CREATE DEFINER = 'admin'@ 'localhost'PROCEDURE account_count ()
 BEGIN
   SELECT 'Number of accounts', COUNT (*) FROM mysql.user;
 END;

이 프로시 저는 그것이 어떤 사용자가 정의되어있는 경우에도 'admin'@'localhost' 의 DEFINER 계정이 할당됩니다. 또한 그것이 어떤 사용자로부터 불려 갔을 경우에도 해당 계정의 권한으로 실행됩니다 (기본 보안 특성은 DEFINER 이기 때문에). 이 프로시 저는 호출자에게 그에 대한 EXECUTE 권한이 돈까스 'admin'@'localhost' 에 mysql.user 테이블에 대한 SELECT 권한이 있는지 여부에 따라 성공 또는 실패합니다.

여기에서이 프로 시저가 SQL SECURITY INVOKER 특성을 사용하여 정의되어 있다고합니다.

 CREATE DEFINER = 'admin'@ 'localhost'PROCEDURE account_count ()
 SQL SECURITY INVOKER
 BEGIN
   SELECT 'Number of accounts', COUNT (*) FROM mysql.user;
 END;

이 프로시 저는 여전히 'admin'@'localhost' 의 DEFINER 를 가지고 있습니다 만,이 경우 호출 사용자의 권한으로 실행됩니다. 따라서이 프로시 저는 호출자에게 그에 대한 EXECUTE 권한과 mysql.user 테이블에 대한 SELECT 권한이 있는지 여부에 따라 성공 또는 실패합니다.

서버는 루틴 매개 변수 DECLARE 를 사용하여 만든 로컬 루틴 변수 또는 함수의 반환 값의 데이터 유형을 다음과 같이 처리합니다.