php 5, mysql에서 Stored Procedure 사용하기이 페이지 작성에 참여하기

php 5, mysql에서 Stored Procedure 사용하기이 페이지 작성에 참여하기

 

 

 우선 짚고 넘어갈 점. PDO (PHP Data Objects) = 버그덩어리.

특히나 Windows 환경에서 뭘 만드는 경우 PDO 버그에 치여서 의욕을 상실할 수 있으므로 주의. 대표적으로 SP 한번 호출하고 두번째 호출하려는데, 앞에 호출한 것의 레코드셋이 정리가 제대로 안되었다며 호출할 수가 없더군효. 특히 Windows의 PDO는 한번 호출 / 쿼리에 여러 레코드셋을 반환하는 경우 제대로 처리하지 못합니다.

PHP의 mysqli 확장도 같은(SP 호출 후 정리해야 하는) 문제가 있는데 (이쯤되면 문제인지 의도된 동작인지) 이쪽은 Windows 환경에서도 다중 레코드셋 처리가 잘 되므로 사용이 가능합니다. 

output parameter (저장 프로시저 내에서 계산하여 밖으로 값을 보내는 것) 사용할 수 없음. 이는 MySQL C 라이브러리가 애초에 output parameter를 제대로 지원하지 않아서라고 합디다. 어쩐지 php + PDO의 예제도 IBM에서 올린 DB2 연동 예제만 제대로 잡히더라니. 뭐 오라클 연동 예제도 있긴 합니다만.. 아무튼 구글에서 검색해봐도 힘빠지는게 많이 잡히지요. 그러나 방법이 없는 것은 아닙니다.

프로시저에서 밖으로 결과값을 보낼때는 select를 해줍니다.

즉 프로시저에서 결과값 변수를 먼저 select를 하거나 뒤에 select를 하게 해서, 원래 select를 해서 레코드셋을 밖으로 내보내는 것과 함께 두개의 레코드셋을 리턴하게 하고 둘 다 받아서 처리하면 됩니다. mysqli에서는 가능합니다. 구버전은 저도 모르지만. (해볼 생각도 없음)

단 저장 프로시저 내에서 저장 프로시저를 호출한다든지 할 때는 물론 output parameter를 문제없이 사용할 수 있습니다. 퉤퉤. 

이쯤에서 페이징의 예를 들어보죠. 보통 페이징을 위해서는 글 전체 수와 페이지 분량의 레코드셋이 필요합니다. 그걸 적어보면

DELIMETER $$

CREATE PROCEDURE 'get_article' ( page int, pagesize int )

BEGIN

    set @size = pagesize;

    set @pageStart = (page - 1) * @size;

    SELECT COUNT(*) FROM articles; -- 글 전체 수

    -- limit 에 파라미터를 쓰기 위한 팁: prepared statement로 실행하기

    prepare stmt from "select * from articles limit ?, ? ";

    execute stmt using @pageStart, @size;

END$$

DELIMETER ; 

 

이걸 호출하고 받아주는 php쪽의 코드는 다음과 같습니다.

$page = 1; $pageSize = 20;

// host, account, pass, db 순서

$db = new mysqli ('localhost', 'reader', 'Ejrtlftls', 'example');

// sp 호출할때는 mysqli_prepare 쓰지 않슴다. 이유가 복잡한 듯.

$query = "call get_article($page, $pageSize)";

$ret = array(); $tot = 0;

if ($result = $db->query($query)) {

    $row = $result->fetch_row();

    // 페이징을 위해 글 개수 얻어오기

    $tot = $row[0];

    $db->next_result(); // 두번째 레코드셋으로 ㄱㄳ

    $result = $db->store_result();

    // 원하는 레코드셋을 받아온다. 부라는 개체로 받는 것을 선호해효

    while ($row = $result->fetch_object()) {

        $ret[] = $row;

    }

    $db->next_result(); // 여기서 정리해줘야 다음 sp 호출이 가능

}

// 이 아래는 개체 배열인 $ret를 적절하게 쓰면 되겠지..

foreach ($ret as $eachObj) { ... } 

만약 문자열을 파라미터로 넘겨야 하는데 SQL Injection Attack 등이 걱정된다면 텍스트를 mysqli_escape_string로 한번 감싸주고 call 뭐시기('$text')와 같이 하면 되겠슴미다.

마지막에 $db->next_result() 하는 것이 중요함다. 실제로 다음 레코드셋이 없더라도 마지막 호출 한번 해줘서 정리하는게 핵심.

기타 페이징을 위해 limit 문에 파라미터를 주려면 저장 프로시저 내에서 prepared statement를 사용하면 된다는 말씀. 

사용자 정의 함수 (stored functions - MS SQL Server의 User Defined Function) 에서는 prepared statements를 못 쓴다는게 재미있네요. SQL Server도 같은 제한이 있어서 핵심 코드를 SP로 빼고 함수에서는 SP를 호출하는 방법을 씁니다. 

사실 남들이 안한 짓을 한다는 것은 어찌보면 뻘짓인데, DB쪽에서 처리할 수 있는 코드는 DB에 맡긴다는 것이 제 생각이라, 제 경우 쿼리에 판단이 들어가고 데이터가 테이블에 들어가기 전에 가공되어야 한다 싶으면 저장 프로시저를 쓰는 것을 당연하게 생각합니다. 물론 위와 같이 mysqli 쓰려면 php 4와는 빠이빠이, 저장 프로시저나 저장 함수를 쓰려면 mysql 4와는 빠이빠이.

재미있는 것은 ADO.NET의 SqlCommand 등도 위와 비슷하게 동작합니다. 프로필러로 뽑아보면 변수 선언 → 값 대입 → sp 호출 → 리턴값 select로 반환하는 것이 보이지효.