MySQL
저장된 루틴 (프로 시저 및 함수)

매개 변수

비고

함수 만들기

다음 (사소한) 예제 함수는 단순히 상수 INT 값 12 를 반환합니다.

DELIMITER ||
CREATE FUNCTION functionname()
RETURNS INT
BEGIN
    RETURN 12;
END;
||
DELIMITER ;

첫 번째 줄은 구분 문자 ( DELIMITER || )가 변경 될 것을 정의하며, 기본값으로두면 함수가 작성되기 전에 설정되어야합니다 ; 그 다음 첫 번째 ; 함수 본문에있는 함수는 일반적으로 원하는 것이 아닌 CREATE 문의 끝으로 간주됩니다.

CREATE FUNCTION 이 실행 된 후에 분리 문자를 기본값으로 다시 설정해야합니다 ; 위의 예제 ( DELIMITER ; )의 함수 코드 다음에 나와 있습니다.

이 기능의 실행은 다음과 같습니다 :

SELECT functionname();
+----------------+
| functionname() |
+----------------+
|             12 |
+----------------+

약간 더 복잡한 (그러나 여전히 사소한) 예제는 매개 변수를 사용하고 상수를 추가합니다.

DELIMITER $$
CREATE FUNCTION add_2 ( my_arg INT )
  RETURNS INT
BEGIN
  RETURN (my_arg + 2);
END;
$$
DELIMITER ;

SELECT add_2(12);
+-----------+
| add_2(12) |
+-----------+
|        14 |
+-----------+

DELIMITER 지시문에 다른 인수를 사용합니다. CREATE 문 본문에 나타나지 않는 문자 시퀀스를 실제로 사용할 수 있지만 일반적으로 \\ , || 와 같은 두 자리 비 영숫자 문자를 사용하는 것이 일반적입니다. 또는 $$ .

함수, 프로 시저 또는 트리거 작성 또는 갱신 전후에 항상 매개 변수를 변경하는 것이 바람직합니다. 일부 GUI에서는 분리.자를 변경하지 않아도되지만 명령 행을 통해 조회를 실행하려면 항 s 분리.자가 설정해야합니다.

생성 된 준비로 프로 시저 만들기

DROP PROCEDURE if exists displayNext100WithName;
DELIMITER $$
CREATE PROCEDURE displayNext100WithName
(    nStart int,
    tblName varchar(100)
)
BEGIN
    DECLARE thesql varchar(500); -- holds the constructed sql string to execute

    -- expands the sizing of the output buffer to accomodate the output (Max value is at least 4GB)
    SET session group_concat_max_len = 4096; -- prevents group_concat from barfing with error 1160 or whatever it is

    SET @thesql=CONCAT("select group_concat(qid order by qid SEPARATOR '%3B') as nums ","from (    select qid from ");
    SET @thesql=CONCAT(@thesql,tblName," where qid>? order by qid limit 100 )xDerived");
    PREPARE stmt1 FROM @thesql; -- create a statement object from the construct sql string to execute
    SET @p1 = nStart; -- transfers parameter passed into a User Variable compatible with the below EXECUTE
    EXECUTE stmt1 USING @p1;
     
    DEALLOCATE PREPARE stmt1; -- deallocate the statement object when finished
END$$
DELIMITER ;

저장 프로시 듀어를 작성하면 많은 클라이언트 도구에 필요한 DELIMITER가있는 랩핑이 표시됩니다.

호출 예제 :

call displayNext100WithName(1,"questions_mysql");

%3B (세미콜론) 구분 기호가있는 샘플 출력 :

IN, OUT, INOUT 매개 변수가있는 저장 프로 시저

DELIMITER $$
 
DROP PROCEDURE IF EXISTS sp_nested_loop$$
CREATE PROCEDURE sp_nested_loop(IN i INT, IN j INT, OUT x INT, OUT y INT, INOUT z INT)
BEGIN
    DECLARE a INTEGER DEFAULT 0;
    DECLARE b INTEGER DEFAULT 0;
    DECLARE c INTEGER DEFAULT 0;
    WHILE a < i DO
        WHILE b < j DO
            SET c = c + 1;
            SET b = b + 1;
        END WHILE;    
        SET a = a + 1;
        SET b = 0;        
    END WHILE;
    SET x = a, y = c;
    SET z = x + y + z;
END $$
DELIMITER ;

저장 프로 시저를 호출 ( 호출 )합니다.

SET @z = 30;
call sp_nested_loop(10, 20, @x, @y, @z);
SELECT @x, @y, @z;

결과:

+------+------+------+
|  @x  |  @y  |  @z  |
+------+------+------+
|  10  |  200 |  240 |
+------+------+------+

IN 매개 변수는 프로 시저에 값을 전달합니다. 프로 시저가 값을 수정할 수 있지만 프로 시저가 반환 될 때 호출자가 수정할 수 없습니다.

OUT 매개 변수는 프로 시저의 값을 호출자에게 다시 전달합니다. 초기 값은 프로 시저 내에서 NULL이며 프로 시저가 반환 될 때 해당 값이 호출자에게 표시됩니다.

INOUT 매개 변수는 호출자에 의해 초기화되고 프로 시저에 의해 수정 될 수 있으며 프로 시저가 리턴 할 때 프로 시저가 변경 한 사항은 호출자에게 표시됩니다.

참조 : http://dev.mysql.com/doc/refman/5.7/en/create-procedure.html

커서

커서를 사용하면 쿼리 결과를 한 줄씩 반복 할 수 있습니다. DECLARE 명령은 커서를 초기화하고 특정 SQL 쿼리와 연관시키는 데 사용됩니다.

DECLARE student CURSOR FOR SELECT name FROM studend;

우리가 어떤 유형의 제품을 판매한다고 가정 해 봅시다. 우리는 각 유형의 제품이 몇 개 있는지를 계산하려고합니다.

우리의 데이터 :

CREATE TABLE product
(
  id   INT(10) UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
  type VARCHAR(50)      NOT NULL,
  name VARCHAR(255)     NOT NULL

);
CREATE TABLE product_type
(
  name VARCHAR(50) NOT NULL PRIMARY KEY
);
CREATE TABLE product_type_count
(
  type  VARCHAR(50)      NOT NULL PRIMARY KEY,
  count INT(10) UNSIGNED NOT NULL DEFAULT 0
);

INSERT INTO product_type (name) VALUES
  ('dress'),
  ('food');

INSERT INTO product (type, name) VALUES
  ('dress', 'T-shirt'),
  ('dress', 'Trousers'),
  ('food', 'Apple'),
  ('food', 'Tomatoes'),
  ('food', 'Meat');

커서를 사용하여 저장 프로 시저를 사용하여 목표를 달성 할 수 있습니다.

DELIMITER //
DROP PROCEDURE IF EXISTS product_count;
CREATE PROCEDURE product_count()
  BEGIN
    DECLARE p_type VARCHAR(255);
    DECLARE p_count INT(10) UNSIGNED;
    DECLARE done INT DEFAULT 0;
    DECLARE product CURSOR FOR
      SELECT
        type,
        COUNT(*)
      FROM product
      GROUP BY type;
    DECLARE CONTINUE HANDLER FOR SQLSTATE '02000' SET done = 1;

    TRUNCATE product_type;

    OPEN product;
    
    REPEAT
      FETCH product
      INTO p_type, p_count;
      IF NOT done
      THEN
        INSERT INTO product_type_count
        SET
          type  = p_type,
          count = p_count;
      END IF;
    UNTIL done
    END REPEAT;
    
    CLOSE product;
  END //
DELIMITER ;

다음과 함께 절차를 요청할 수있을 때 :

CALL product_count();

결과는 product_type_count 테이블에 있습니다.

type   | count
----------------
dress  |   2
food   |   3

이것이 CURSOR 의 좋은 예이지만, 프로 시저의 전체 본문이

INSERT INTO product_type_count
        (type, count)
    SELECT type, COUNT(*)
        FROM product
        GROUP BY type;

이것은 훨씬 더 빨리 달릴 것입니다.

다중 ResultSet

SELECT 문과 달리 Stored Procedure 는 여러 결과 집합을 반환합니다. Perl, PHP 등에서 CALL 결과를 수집하는 데 사용되는 다른 코드가 필요합니다.

(여기 또는 다른 곳에서 특정 코드가 필요합니다!)

함수 만들기

DELIMITER $$
CREATE
    DEFINER=`db_username`@`hostname_or_IP`
    FUNCTION `function_name`(optional_param data_type(length_if_applicable))
    RETURNS data_type
BEGIN
    /*
    SQL Statements goes here
    */
END$$
DELIMITER ;

RETURNS data_type은 모든 MySQL 데이터 유형입니다.