요약: 이번 강좌에서는, 다중 값을 반환하는 저장 프로시저를 개발하는 방법을 배울 것이다.


MySQL 저장 함수는 오직 하나의 값만 리턴한다. 다중 값을 반환하는 저장 프로그램을 개발하기 위해 INOUT 또는 OUT 매개변수을 사용하는 저장 프로시저를 사용해야할 필요가 있다.

만약 INOUT 또는 OUT 매개변수가 친숙하지 않다면, 자세한 정보가 있는 저장프로시저 매개변수 강좌를 읽고 오라.


다중 값을 반환하는 저장프로시저 예제

샘플 데이터베이스의 orders 테이블을 살펴보자.

아래의 저장 프로시저는 customerNumber를 받고 shipped, cancled, resolved, disputed의 총 갯수를 반환하고 있다.

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
DELIMITER $$
 
CREATE PROCEDURE get_order_by_cust(
 IN cust_no INT,
 OUT shipped INT,
 OUT canceled INT,
 OUT resolved INT,
 OUT disputed INT)
BEGIN
 -- shipped
SELECT count(*) INTO shipped
  FROM orders
  WHERE customerNumber = cust_no AND status = 'Shipped';
 
 -- canceled
  SELECT count(*) INTO canceled
  FROM orders
  WHERE customerNumber = cust_no AND status = 'Canceled';
 
 -- resolved
  SELECT count(*) INTO resolved
  FROM orders
  WHERE customerNumber = cust_no AND status = 'Resolved';
 
 -- disputed
 SELECT count(*) INTO disputed
  FROM orders
  WHERE customerNumber = cust_no AND status = 'Disputed';
 
END $$

DELIMITER ;
cs

IN 매개변수에 더하여, 저장프로시저는 4개의 OUT 매개변수 (shipped, canceled, resolved, disputed) 를 추가로 받는다(takes). 저장 프로시저 내부에서, 주문들의 상태로 부터 주문의 총 갯수를 얻는 COUNT함수와 SELECT 문 을 사용하고, 각각의 매개변수에 할당한다.

get_order_by_cust 저장 프로시저를 사용하기 위해서는 customer number와 결과 값을 얻기 위한 4개의 사용자 정의 변수를 전달 해야 한다.

저장 프로시저 실행 후에 변수 값을 출력하기 위해 SELECT 문을 사용한다.

1
2
CALL get_order_by_cust(141,@shipped,@canceled,@resolved,@disputed);
SELECT @shipped,@canceled,@resolved,@disputed;
cs


PHP에서 다중 값을 반환하는 저장 프로시저 호출하기

아래의 자투리 코드는 PHP에서 다중 값을 반환하는 저장 프로시저를 어떻게 호출하는지 보여준다.

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
<?php
/**
 * Call stored procedure that return multiple values
 * @param $customerNumber
 */
function call_sp($customerNumber)
{
    try {
        $pdo = new PDO("mysql:host=localhost;dbname=classicmodels", 'root', '');
 
        // execute the stored procedure
        $sql = 'CALL get_order_by_cust(:no,@shipped,@canceled,@resolved,@disputed)';
        $stmt = $pdo->prepare($sql);
 
        $stmt->bindParam(':no', $customerNumber, PDO::PARAM_INT);
        $stmt->execute();
        $stmt->closeCursor();
 
        // execute the second query to get values from OUT parameter
        $r = $pdo->query("SELECT @shipped,@canceled,@resolved,@disputed")
                  ->fetch(PDO::FETCH_ASSOC);
        if ($r) {
            printf('Shipped: %d, Canceled: %d, Resolved: %d, Disputed: %d',
                $r['@shipped'],
                $r['@canceled'],
                $r['@resolved'],
                $r['@disputed']);
        }
    } catch (PDOException $pe) {
        die("Error occurred:" . $pe->getMessage());
    }
}
 
call_sp(141);
cs

@ 기호가 붙어 있는 사용자 정의 변수들은 데이터베이스 커넥션과 함께 연관되어진다. 그러므로 호출 한 사이에서는 이용이 가능하다.

이번 강좌에서는 다중 값을 반환하는 저장 프로시저를 개발하는 방법과 php에서 프로시저를 호출하는 방법에 대해서 배워보았다.


출처 : http://www.mysqltutorial.org/mysql-stored-procedures-return-multiple-values/

이전 글 : [Stored Procedure] 4. MySQL Stored Procedure 매개변수 

다음 글 : [Stored Procedure] 6. MySQL IF 문법



출처: https://blog.duveen.me/15?category=657591 [Developer Duveen.]
이 게시물을...