MySQLi 저장 프로시저
MySQL 데이터베이스는 저장 프로시저를 지원합니다. 저장 프로시저는 데이터베이스 카탈로그에 저장된 서브루틴입니다. 응용 프로그램은 저장 프로시저를 호출하고 실행할 수 있습니다. CALL SQL 문은 저장 프로시저를 실행하는 데 사용됩니다.
Parameter
저장 프로시저는 MySQL 버전에 따라 IN, INOUT 및 OUT 매개변수를 가질 수 있습니다. mysqli 인터페이스에는 다른 종류의 매개변수에 대한 특별한 개념이 없습니다.
IN parameter
입력 매개변수는 CALL 문과 함께 제공됩니다. 값이 올바르게 이스케이프되었는지 확인하십시오.
예제 #1 저장 프로시저 호출
<?php
mysqli_report(MYSQLI_REPORT_ERROR | MYSQLI_REPORT_STRICT);
$mysqli = new mysqli("example.com", "user", "password", "database");
$mysqli->query("DROP TABLE IF EXISTS test");
$mysqli->query("CREATE TABLE test(id INT)");
$mysqli->query("DROP PROCEDURE IF EXISTS p");
$mysqli->query("CREATE PROCEDURE p(IN id_val INT) BEGIN INSERT INTO test(id) VALUES(id_val); END;");
$mysqli->query("CALL p(1)");
$result = $mysqli->query("SELECT id FROM test");
var_dump($result->fetch_assoc());
위의 예는 다음을 출력합니다.
array(1) {
["id"]=>
string(1) "1"
}
INOUT/OUT parameter
INOUT/OUT 매개변수의 값은 세션 변수를 사용하여 액세스됩니다.
예제 #2 세션 변수 사용
<?php
mysqli_report(MYSQLI_REPORT_ERROR | MYSQLI_REPORT_STRICT);
$mysqli = new mysqli("example.com", "user", "password", "database");
$mysqli->query("DROP PROCEDURE IF EXISTS p");
$mysqli->query('CREATE PROCEDURE p(OUT msg VARCHAR(50)) BEGIN SELECT "Hi!" INTO msg; END;');
$mysqli->query("SET @msg = ''");
$mysqli->query("CALL p(@msg)");
$result = $mysqli->query("SELECT @msg as _p_out");
$row = $result->fetch_assoc();
echo $row['_p_out'];
위의 예는 다음을 출력합니다.
Hi!
애플리케이션 및 프레임워크 개발자는 세션 변수와 데이터베이스 카탈로그 검사를 함께 사용하여 보다 편리한 API를 제공할 수 있습니다. 그러나 카탈로그 검사를 기반으로 하는 맞춤형 솔루션이 성능에 미칠 수 있는 영향에 유의하십시오.
Handling result sets
저장 프로시저는 결과 집합을 반환할 수 있습니다. 저장 프로시저에서 반환된 결과 집합은 mysqli::query()를 사용하여 올바르게 가져올 수 없습니다. mysqli::query() 함수는 명령문 실행과 첫 번째 결과 세트를 버퍼링된 결과 세트(있는 경우)로 가져오는 것을 결합합니다. 그러나 mysqli::query()가 사용자가 예상한 결과 집합을 반환하지 못하게 하는 추가 저장 프로시저 결과 집합이 사용자에게 숨겨져 있습니다.
저장 프로시저에서 반환된 결과 집합은 mysqli::real_query() 또는 mysqli::multi_query()를 사용하여 가져옵니다. 두 함수 모두 CALL과 같은 명령문에서 반환된 결과 집합을 원하는 수만큼 가져올 수 있습니다. 저장 프로시저에서 반환된 모든 결과 집합을 가져오지 못하면 오류가 발생합니다.
예제 #3 저장 프로시저에서 결과 가져오기
<?php
mysqli_report(MYSQLI_REPORT_ERROR | MYSQLI_REPORT_STRICT);
$mysqli = new mysqli("example.com", "user", "password", "database");
$mysqli->query("DROP TABLE IF EXISTS test");
$mysqli->query("CREATE TABLE test(id INT)");
$mysqli->query("INSERT INTO test(id) VALUES (1), (2), (3)");
$mysqli->query("DROP PROCEDURE IF EXISTS p");
$mysqli->query('CREATE PROCEDURE p() READS SQL DATA BEGIN SELECT id FROM test; SELECT id + 1 FROM test; END;');
$mysqli->multi_query("CALL p()");
do {
if ($result = $mysqli->store_result()) {
printf("---\n");
var_dump($result->fetch_all());
$result->free();
}
} while ($mysqli->next_result());
위의 예는 다음을 출력합니다.
---
array(3) {
[0]=>
array(1) {
[0]=>
string(1) "1"
}
[1]=>
array(1) {
[0]=>
string(1) "2"
}
[2]=>
array(1) {
[0]=>
string(1) "3"
}
}
---
array(3) {
[0]=>
array(1) {
[0]=>
string(1) "2"
}
[1]=>
array(1) {
[0]=>
string(1) "3"
}
[2]=>
array(1) {
[0]=>
string(1) "4"
}
}
준비된 명령문의 사용
위와 동일한 저장 프로시저에서 결과를 가져오기 위해 준비된 명령문 인터페이스를 사용할 때 특별한 처리가 필요하지 않습니다. 준비된 명령문과 준비되지 않은 명령문 인터페이스는 비슷합니다. 모든 MYSQL 서버 버전이 CALL SQL 문 준비를 지원하는 것은 아닙니다.
예제 #4 저장 프로시저 및 준비된 명령문
<?php
mysqli_report(MYSQLI_REPORT_ERROR | MYSQLI_REPORT_STRICT);
$mysqli = new mysqli("example.com", "user", "password", "database");
$mysqli->query("DROP TABLE IF EXISTS test");
$mysqli->query("CREATE TABLE test(id INT)");
$mysqli->query("INSERT INTO test(id) VALUES (1), (2), (3)");
$mysqli->query("DROP PROCEDURE IF EXISTS p");
$mysqli->query('CREATE PROCEDURE p() READS SQL DATA BEGIN SELECT id FROM test; SELECT id + 1 FROM test; END;');
$stmt = $mysqli->prepare("CALL p()");
$stmt->execute();
do {
if ($result = $stmt->get_result()) {
printf("---\n");
var_dump($result->fetch_all());
$result->free();
}
} while ($stmt->next_result());
위의 예는 다음을 출력합니다.
---
array(3) {
[0]=>
array(1) {
[0]=>
int(1)
}
[1]=>
array(1) {
[0]=>
int(2)
}
[2]=>
array(1) {
[0]=>
int(3)
}
}
---
array(3) {
[0]=>
array(1) {
[0]=>
int(2)
}
[1]=>
array(1) {
[0]=>
int(3)
}
[2]=>
array(1) {
[0]=>
int(4)
}
}
물론 fetch를 위한 bind API도 지원합니다.
예제 #5 바인드 API를 사용한 저장 프로시저 및 준비된 명령문
<?php
mysqli_report(MYSQLI_REPORT_ERROR | MYSQLI_REPORT_STRICT);
$mysqli = new mysqli("example.com", "user", "password", "database");
$mysqli->query("DROP TABLE IF EXISTS test");
$mysqli->query("CREATE TABLE test(id INT)");
$mysqli->query("INSERT INTO test(id) VALUES (1), (2), (3)");
$mysqli->query("DROP PROCEDURE IF EXISTS p");
$mysqli->query('CREATE PROCEDURE p() READS SQL DATA BEGIN SELECT id FROM test; SELECT id + 1 FROM test; END;');
$stmt = $mysqli->prepare("CALL p()");
$stmt->execute();
do {
if ($stmt->store_result()) {
$stmt->bind_result($id_out);
while ($stmt->fetch()) {
echo "id = $id_out\n";
}
}
} while ($stmt->next_result());
위의 예는 다음을 출력합니다.
id = 1
id = 2
id = 3
id = 2
id = 3
id = 4