PHP CodeIgniter에서 foreach 루프로 저장 프로시저(Stored Procedure) 호출하는 방법

CodeIgniter에서 저장 프로시저를 호출할 때는 일반 쿼리와 달리 여러 개의 결과 집합(result set)이 반환될 수 있기 때문에, 모델(Model)과 컨트롤러(Controller) 코드를 적절하게 구성해야 합니다. 아래 예제는 헤더 데이터를 조회한 후, 각 항목마다 자식 데이터를 추가로 조회하여 배열로 조립하는 전형적인 패턴을 보여줍니다.

컨트롤러(Controller) 코드

컨트롤러에서는 모델이 반환한 결과를 foreach 루프로 순회하면서, 각 항목의 이름을 기준으로 자식 저장 프로시저를 다시 호출합니다.

$header = $this->model_name->call_head();
foreach($header as $item) {
    $name = $item['name'];
    $array['name'] = $name;
    $array['data'] = $item['data'];
    $child_val = $this->model_name->call_child($name);
    foreach($child_val as $value) {
        $array['child'] = array(
            'child_name' => $value['child_name'],
            'child_data' => $value['child_data']
        );
    }
}

모델(Model) 코드

모델에서는 CALL 구문으로 저장 프로시저를 실행하고, 결과를 배열로 변환하여 반환합니다. 특히 저장 프로시저 호출 후에는 반드시 next_result()와 free_result()를 호출해야 합니다.

public function call_head() {
    $query = "CALL PROCEDURE_HEAD()";
    $result = $this->db->query($query)->result_array();
    $query->next_result();
    $query->free_result();
    return $result;
}
public function call_child($name) {
    $query = "CALL PROCEDURE_CHILD($name)";
    $result = $this->db->query($query)->result_array();
    $query->next_result();
    $query->free_result();
    return $result;
}

핵심 포인트 정리

• next_result(): MySQL 드라이버는 저장 프로시저가 여러 결과 집합을 반환할 수 있으므로, 다음 쿼리를 실행하기 전에 남아 있는 결과 집합을 비워야 연결 오류(Commands out of sync)를 방지할 수 있습니다.
• free_result(): 메모리에 할당된 결과 리소스를 즉시 해제하여 불필요한 메모리 점유를 막습니다.
• SQL 인젝션 주의: 위 예제의 call_child($name)처럼 변수를 문자열에 직접 삽입하는 것은 위험합니다. 가능하면 $this->db->query("CALL PROCEDURE_CHILD(?)", array($name)) 형태의 쿼리 바인딩(Query Binding)을 사용하는 것이 안전합니다.

이 패턴을 활용하면 부모-자식 관계의 데이터를 저장 프로시저 두 개만으로 효율적으로 조회하고, 컨트롤러에서 원하는 구조의 배열로 가공해 뷰(View)에 전달할 수 있습니다.