반응형
CREATE PROCEDURE sp_name ([proc_parameter[,...]])
[characteristic ...] routine_body
CREATE 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:
LANGUAGE SQL | [NOT] DETERMINISTIC | { CONTAINS SQL | NO SQL | READS SQL DATA | MODIFIES SQL DATA } | SQL SECURITY { DEFINER | INVOKER }| COMMENT 'string'
routine_body:
Valid SQL procedure statement
이 명령문들은 스토어드 루틴을 생성한다. MySQL 5.0.3에서 보면, 이러한 명령문을 사용하기 위해서는,
CREATE ROUTINE 에 대한 권한이 필요하게 된다. 만일 바이너리 로깅이 가능하다면, 이러한 명령문들은 SUPER 권한을 요청할 수도 있게 되는데, 이점에 대해서는 Section 17.4, “스토어드 루틴 및 트리거에 바이너리로 로깅” 에서 언급하였다. MySQL은 자동으로 ALTER ROUTINE 과 EXECUTE 권한을 루틴 생성자에게 부여한다. 루틴은 디폴트 데이터 베이스와 디폴트로 연결된다. 주어진 데이터
베이스와 루틴을 연결하기 위해서는, 루틴을 생성할 때 db_name.sp_name 과 같이 이름을 명확히 지정해야 한다.
만일 루틴의 이름이 SQL 함수 내부에 있는 이름과 일치하게 되면, 루틴을 정의할 때 이름과 따라오는 괄호 사이에 스페이스를 사용해야 신텍스 에러를 피할 수 있다. 이것은 또한 나중에 루틴을 호출할 때에도 적용된다. 이러한 이유로 인해, 작성하는 스토어드 루틴에 대해서는 SQL내부의 함수 이름을 사용하지 말 것을 권장한다.
IGNORE_SPACE SQL 모드는 내부에 탑재된 함수에 적용되며, 스토어드 루틴에는 적용되지 않는다. IGNORE_SPACE의 활성화에 상관 없이 루틴의 이름 후에 스페이스를 사용하는 것은 언제든지 가능하다.괄호 안에는 파라미터 리스트가 반드시 항상 존재해야 한다. 만일 파라이터가 없으면, ()의 빈 파라미터가 쓰이게 된다. 각 파라미터는 디폴트로
IN 파라미터가 된다. 파라미터에 다른 것을 지정하고자 할 경우에는, 파라미터 이름 앞에 키워드 OUT 또는 INOUT를 사용하면 된다. 노트 : Specifying a parameter as
IN, OUT, 또는 INOUT으로 파라미터를 지정할 경우는 PROCEDURE에 대해서만 가능하다. (FUNCTION 파라미터는 항상 IN 파라미터로 간주된다.) 각 파라미터는 모든 가능한 데이터 타입을 사용하기 위해 선언될 수 있으나,
COLLATE 속성은 사용할 수 없다. RETURNS 구문은 FUNCTION 에 대해서만 명기할 수도 있는데, 이것이 필수적인 경우에만 해당된다. 이러한 경우, RETURN 구문은 함수의 리턴 타입을 지정하며, 함수에는 반드시 RETURN value 명령문이 있어야 한다. routine_body 는 유용한 SQL 프로시저 명령문으로 구성된다. 이것은 단순히 SELECT 또는 INSERT가 될 수도 있으며, 또는 BEGIN 과 END로 구성된 복합 명령문이 될 수도 있다. 복합 명령문 신텍스는 Section 17.2.7, “
BEGIN ... END 복합 명령문 신텍스”.에서 다루기로 한다. 복합 명령문은 선언문, 루프 및 다른 제어 구조 명령문을 포함할 수 있다. 이러한 명령문에 대한 신텍스는 나중에 다루기로 다루기로 한다. 예를 들어, Section 17.2.8, “DECLARE 명령문 신텍스”, 및 Section 17.2.12, “Flow Control Constructs”를 참조 하면 된다. MySQL의 이전 버전에서 UDF(user-defined functions)를 지원하기 위해서
CREATE FUNCTION 명령문을 사용했다. Section 24.2, “MySQL에 새로운 함수 추가”를 참조. UDF는 스토어드 함수가 지원되는 현 버전에도 계속 사용 가능하며, 이때 UDF는 외부 스토어드 함수로 간주된다. 하지만, 스토어드 함수는 UDF와 함께 자신의 이름 공간(namespace)를 공유한다는 점을 유의한다. 하나의 프로시저 또는 함수는 동일 입력 파라미터에 대해 항상 같은 결과를 만들어 낼 경우에는 “확정적(deterministic)”로 간주되며, 그렇지 않을 경우에는 “비확정적(not deterministic)”으로 간주된다. 루틴 정의에서
DETERMINISTIC 또는 NOT DETERMINISTIC 이 정해지지 않을 경우, 디폴트는 NOT DETERMINISTIC 이 된다. 리플리케이션을 위해서는, 비확정적 루틴을 만들기 위해서
NOW() 함수 (또는 동의어) 또는 RAND() 함수를 사용할 필요는 없다. NOW() 함수를 위해서는, 바이너리 로그가 타임 스탬프(timestamp) 및 복사본을 정확히 포함한다. RAND() 함수 또한 루틴 내에서 한번 호출 되어지면 정확히 복사본을 포함하게 된다. (루틴 실행 타임스탬프 및 랜덤 숫자 시드(random number seed)를 마스터 및 슬레이브에서 동일하게 여겨지는 임플리시트(implicit) 입력치로 생각할 수 있다)현재의 버전에서는,
DETERMINISTIC 특성은 사용 가능하지만, 옵티마이져에 의한 사용은 아직 불가능하다. 하지만, 바이너리 로깅이 가능할 경우에는, 이러한 특성은 MySQL이 수용하는 루틴 정의에 영향을 미치게 된다. Section 17.4, “스토어드 루틴 및 트리거에 대한 바이너리 로깅” 참조. 몇몇 특성들은 루틴이 사용하는 데이터의 속성에 대한 정보를 제공한다.
CONTAINS SQL 은 데이터를 읽거나 쓰는 명령문을 갖지 않는 루틴을 의미 한다. NO SQL 은 아무런 SQL명령문이 없는 루틴을 나타낸다. READS SQL DATA 은 데이터를 읽기는 하지만 쓰지는 않는 명령문을 갖는 루틴을 표시하며, MODIFIES SQL DATA 는 데이터를 쓸 수도 있는 명령문을 갖는 루틴을 나타낸다. CONTAINS SQL 은 확정적으로 이러한 특성이 주어지지 않았을 경우 디폴트가 된다. 이런 특성들은 단지 권고 사항일 뿐이다. 데이터 베이스 서버는 이러한 명령문들을 루틴이 실행될 수 있는 명령문의 종류를 한정하는데 시용하지는 않는다.SQL SECURITY 특성은 루틴을 실행하는 권한이 있는 사용자가 루틴의 생성자인지 아니면 루틴을 호출한 사용자인지를 명기할 때 사용될 수 있다. 디폴트 값은 DEFINER이다. 이러한 특성은 SQL:2003에 새롭게 추가된 것이다. 생성자 또는 호출자는 반드시 해당 루틴과 연관되어 있는 데이터 베이스에 접근할 수 있는 권한이 있어야 한다. MySQL 5.0.3버전에서는, 루틴을 실행하기 위해서는 EXECUTE 권한이 필요하다. 이 권한을 가져야만 하는 사용자는 루틴 정의자 또는 호출자중 하나이어야 하고, 이것은 SQL SECURITY특성이 어떻게 셋팅 되어지는지에 따라서 결정된다.MySQL은 루틴이 생성될 때, 그리고 이 셋팅 값을 갖는 루틴이 제대로 실행될 때에는 언제나 유효한
sql_mode 시스템 변수 셋팅 값을 저장한다. 루틴이 호출되면, 임플리시트(implicit)
USE db_name 이 실행된다(그리고 루틴이 종료되면 이것도 종료됨). 스토어드 루틴 안에 있는 USE 명령문은 사용할 수 없다. MySQL 5.0.18까지는, 루틴 파라미터의 데이터 타입 또는 함수의 리턴 값을 아래와 같이 사용한다. 이 법칙은 DECLARE명령문을 갖는 로컬 루틴 변수에도 동일하게 적용된다. (Section 17.2.9.1, “
DECLARE 로컬 변수”) 참조. - 할당(Assignments)은 데이터 타입이 일치하지 않고 오버 플로우가 발생하는지를 체크함. 컨버젼(Conversion) 및 오버 플로우 문제는 시스템 경고 또는 스트릭트 모드(strict mode)에서 에러의 원인이 됨.
- 데이터 타입의 문자에 대해서는, 만일 정의문안에
CHARACTER SET구문이 있다면, 명기된 문자 셋 및 그 디폴트 값이 사용된다. 만일, 그러한 구문이 없는 경우에는, 데이터 베이스 문자 셋 및 정의문(collation)이 사용된다. (character_set_database및collation_database시스템 변수의 값에서 이것을 제공함.) - 스칼라값만이 파라미터 또는 변수로 할당될 수 있다. 예를 들면,
SET x = (SELECT 1, 2)와 같은 명령문은 사용될 수 없다.
MySQL 5.0.18 이전에는, 파라미터, 리턴값, 그리고 로컬 변수는 프로그래밍 식(expression)에 있는 아이템으로 간주되었고, 또한 자동(silent)변환 및 자동 잘림(truncation)의 대상으로 간주되었다. 스토어드 함수는
sql_mode 셋팅을 무시한다. COMMENT 구문은 MySQL의 확장자이며, 스토어드 루틴을 설명할 때 사용할 수 있다. 이러한 정보는 SHOW CREATE PROCEDURE 및 SHOW CREATE FUNCTION 명령문으로 볼 수 있다. MySQL 은 루틴이 CREATE 및 DROP과 같은 DDL명령문을 포함할 수 있도록 한다. 또한, MySQL은 스토어드 프로시저(스토어드 함수가 아님) 가 COMMIT과 같은 SQL 트랜젝션을 갖는 것을 허용한다. 스토어드 함수는 익스플리시트(explicit) 또는 임플리시트(implicit) 커밋(commit) 또는 롤백(rollback)을 수행하는 명령문을 포함할 수 없다. 이런 명령문을 지원하는 것은 SQL표준안의 요구 사항이 아니며, 각 DBMS업체의 선택에 달려 있다.
스토어드 루틴은
LOAD DATA INFILE를 사용할 수 없다.결과 셋을 리턴하는 명령문은 스토어드 함수 내에서는 사용할 수 없다. 이러한 것에는 컬럼 값을 변수 안으로 인출(fetch)하기 위한 INTO를 사용하지 않는
SELECT 명령문, SHOW명령문, 그리고 EXPLAIN과 같은 명령문 등이 해당된다. 결과 셋을 리턴하는 함수 정의 시점을 결정할 수 있는 명령문에 대해서는, ‘ Not allowed to return a result set from a function error’ (ER_SP_NO_RETSET_IN_FUNC)가 발생한다. 결과 셋을 리턴하기 위해 런타임만을 결정하는 명령문에 대해서는, ‘ PROCEDURE %s can't return a result set in the given context error’ (ER_SP_BADSELECT)가 발생한다. Note: MySQL 5.0.10이전에는,
CREATE FUNCTION 과 함께 생성된 스토어드 프로시저는 제한적인 예외 사항이 있기는 하지만 테이블에 대한 참조를 가질 수 없었다. 이러한 스토어드 프로시저는 테이블 참조를 갖는 SET과 같은 명령문을 포함할 수 있는데, 예를 들면 SET a:= (SELECT MAX(id) FROM t) 이며, 또한 값을 직접 변수 안으로 집어 넣는 SELECT 명령문, 예를 들면 SELECT i INTO var1 FROM t이 될 수 있다. 아래의 것은OUT 파라미터를 갖고 있는 단순한 스토어드 프로시저의 경우에 해당하는 예문이다. 이 예문은 mysql 클라이언트
delimiter 명령어를 사용해서 프로시저가 정의되는 동안 구분자를 ; 로부터 //로 변경하는 것을 나타낸다. 이것은 프로시저 몸체에서 사용되어진 ; 구분 문자가 mysql 자체에서 해석되지 않고 서버로 전달될 수 있도록 하게 해준다. 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)
delimiter 명령어를 사용할 때에는, 백슬레쉬(‘\’)를 사용하면 안 되는데, 이 문자는 MySQL에서는 escape문자로 사용되기 때문이다 아래의 문장은 파라미터를 가져오고, SQL함수를 사용해서 동작을 하고, 결과치를 리턴하는 함수의 예문이다. 이 경우, delimiter를 사용할 필요가 없는데, 함수 정의는 내부 ; 명령문 구분 문자를 갖고 있지 않기 때문이다:
mysql> CREATE FUNCTION hello (s CHAR(20)) RETURNS CHAR(50)
-> 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)
스토어드 함수는 RETURNS 구문에서 명기한 데이터의 타입 값을 리턴한다. 만일,
RETURN 명령문이 다른 타입의 값을 리턴할 경우에는, 올바른 타입으로 치환된다. 예를 들면, 함수가 ENUM 또는 SET 값을 리턴한다 하더라도, RETURN 명령문은 정수 값을 리턴하게 되며, 함수로부터 리턴된 값은 SET 멤버의 셋의 ENUM멤버에 상응하는 스트링이 된다.| :::MySQL Korea::: |  |
|
http://www.welog.net/gbbs/bbs/board.php?bo_table=mysql&wr_id=54&page=0&sca=&sfl=&stx=&spt=0&page=0&cwin=#c_145
반응형
'DB > mysql' 카테고리의 다른 글
| Mysql DB 시간 보기 (0) | 2010.04.08 |
|---|---|
| Mysql 5.1 version IP 추가하기 (0) | 2010.04.08 |