在CI框架下执行存储的方法

我直接把代码摆在这里分享哈

<?php
/**
 *
 * Created by JetBrains PhpStorm.
 * User: lsl
 * Date: 14-1-8
 * Time: 下午2:55
 * To change this template use File | Settings | File Templates.
 */

define("QUERY_KEY", 1);
define("QUERY_INDEX", 2);
define("QUERY_BOTH", 3);

/**
 * 自定义数据库工具类，为了满足CI框架不能
 * 在CI里面调用方式如下
 * 1、在model的构造函数里面加载该类 $this->load->library('pdb');
 * 2、在任一方法内调用方式如下
 *  $sqlParams[0] = new SqlParam('参数1',参数值1);
    $sqlParams[1] = new SqlParam('参数2',参数2);
    $sqlParams[2] = new SqlParam('@returnValue');参数返回值
    $sqlParams[2]->Direction = Direction::$ReturnValue;
    $procname = '存储过程名称';
    $dt = $this->pdb->setDatabase("db_account")->runProcReturnTable($procname,$sqlParams,QUERY_KEY);
 *  $result = $sqlParams[2]->ParamValue;
 * Class Pdb
 */
class Pdb  {

    public  $db_handler = array();
    private $db_name;

    /**
     * 构造函数
     */
    function __construct(){
        //TODO 这里暂时不需要做任何事情
    }

    function &setDatabase($db_name){
        $this->db_name = $db_name;
        return $this;
    }

    /**
     * 析构函数
     */
    function __destruct() {

        if(!is_null($this->db_handler[$this->db_name])){
            $this->db_handler[$this->db_name]->close();//断开数据库连接
            unset($this->db_handler[$this->db_name]);
        }
    }

    public function getDb(){
        $db = array();
        //加载配置文件
        if (  file_exists($file_path = APPPATH.'config/'.'database.php'))
        {
            include($file_path);
        } else {
            show_error('The configuration file database.php does not exist.');
        }
        if(is_null($this->db_handler[$this->db_name])){
            $this->db_handler[$this->db_name] = mysqli_connect($db[$this->db_name]['hostname'], $db[$this->db_name]['username'],$db[$this->db_name]['password'],$db[$this->db_name]['database']) or die("Could not connect: " . mysql_error() . "<br/>");
            $this->db_handler[$this->db_name]->query("SET names ".$db[$this->db_name]['char_set']);
        }
        return $this->db_handler[$this->db_name];
    }

    /**
     * 获取单行结果集
     * @param $sql
     * @return mixed
     */
    public function getRow($sql){
        $db = $this->getDb();
        $result = $db->query($sql);
        $row = $result->fetch_array();
        return $row;
    }

    /**
     * 执行sql语句，返回结果集
     * @param $sql  sql语句
     * @return  $result 返回结果集
     */
    public function getResult($sql){
        $db = $this->getDb();
        $result = $db->query($sql);
        return $result;
    }

    /**
     * 执行sql语句，返回结果集
     * @param $sql  sql语句
     * @return  $datatable array(0=>array("id"=>1),1=>array("id"=>2))
     */
    public function returnDataTable($sql,$type = 3){
        $db = $this->getDb();
        $result = $db->query($sql);
        if($result){
            $row = $result->fetch_array($type);
            $i=0;
            while ($row){
                $datatable[$i] = $row;
                $i++;
                $row = $result->fetch_array($type);
            }
        }
        $datatable = $datatable?$datatable:array();
        @mysqli_free_result($result);
        return $datatable;
    }

    /**
     * 执行sql同时将结果集已多维数组形式返回
     * @param $sql
     * @param int $type
     * @return mixed
     */
    public function returnDataTables($sql,$type = 3){
        $db = $this->getDb();
        if($db->multi_query($sql)){
            $j=0;
            do{
                $result = $db->store_result();
                if ($result){
                    //获取第一个结果集
                    $row = $result->fetch_array($type);
                    $i=0;
                    while ($row){
                        $datatable[$j][$i] = $row;
                        $i++;
                        $row = $result->fetch_array($type);
                    }
                    $result->close();               //关闭一个打开的结果集
                }
                $j++;
            } while($db->next_result());
        }
        @mysqli_free_result($result);
        return $datatable;
    }


    /**
     * 通过存储参数获得sql语句
     * @param $procname
     * @param null $params
     * @return string
     */
    public function getProcSql($procname,$params=NULL){
        $sql = "call ".$procname."(";
        if($params){
            $sqlOutPut = "select ";
            for($i=0;$i<count($params);$i++){
                if($params[$i]->Direction == Direction::$Output){
                    $sql .= $params[$i]->SqlParamName;
                    $sqlOutPut = $sqlOutPut.$params[$i]->SqlParamName.",";
                    $sql .= ",";
                }  else if($params[$i]->Direction == Direction::$Intput){
                    $sql .= "'".$params[$i]->ParamValue."',";
                }
            }
            if(count($params)>0){
                $sql = substr($sql, 0,  strlen($sql)-1).");";
                $sqlOutPut = substr($sqlOutPut, 0,  strlen($sqlOutPut)-1).";";
            }
        }else {
            $sql .= ");";
        }
        if(strlen($sqlOutPut)>7){
            $sql .= $sqlOutPut;
        }
        return $sql;
    }

    /**
     * 执行存储同时返回结果集
     * @param $procname
     * @param null $params
     * @param int $type
     * @return array
     */
    public function runProcReturnTable($procname,$params=NULL,$type = 3){
        $db = $this->getDb();
        //构建存储过程语句
        $sql = $this->serializationProc($procname, $params,$db);
        $result = $db->query($sql);
        if($result){
            $row = $result->fetch_array($type);
            $i=0;
            while ($row){
                $datatable[$i] = $row;
                $i++;
                $row = $result->fetch_array($type);
            }
        }
        $datatable = $datatable?$datatable:array();
        @mysqli_free_result($result);
        return $datatable;
    }

    /**
     * 执行存储过程
     * @param string     存储过程名称
     * @param array       参数数组 array(0=>SqlParam)
     * @return string         返回构建的sql语句，用于调试
     */
    public function runProc($procname,$params=NULL){
        //执行存储过程，取回返回值与输出参数
        $db = $this->getDb();
        //构建存储过程语句
        $sql = $this->serializationProc($procname, $params, $db);
        if($db->multi_query($sql)){
            $result = $db->store_result();
            if($result){
                $row = $result->fetch_array(2);
                if($row){
                    for($i=0;$i<count($params);$i++){
                        if($params[$i]->Direction == Direction::$ReturnValue){
                            $params[$i]->ParamValue = $row[0];
                        }
                    }
                }
            }
            do{
                $result = $db->store_result();
                if ($result) {
                    //获取第一个结果集
                    $row = $result->fetch_array(1);
                    for($i=0;$i<count($params);$i++){
                        if($params[$i]->Direction == Direction::$Output){
                            $params[$i]->ParamValue = $row[$params[$i]->SqlParamName];
                        }
                    }
                    $result->close();               //关闭一个打开的结果集
                }
            } while($db->next_result());
        }
        @mysqli_free_result($result);
        return true;
    }

    /**
     * 序列号存储过程，将参数转换成sql的形式
     * @param string     存储过程名称
     * @param array       参数数组 array(0=>SqlParam)
     * @param db           存储连接DB
     * @return string      返回构建的sql语句
     */
    private function serializationProc($procname,$params,&$db){
        $sql = "call ".$procname."(";
        if(count($params)>0){
            $sqlOutPut = "select ";
            foreach ($params as $v) {
                if($v->Direction == Direction::$ReturnValue){
                    continue;
                }
                if(strpos($v->SqlParamName, "@") === FALSE){
                    $v->SqlParamName = "@".$v->SqlParamName;
                }
                $db->query("set ".$v->SqlParamName."='".$v->ParamValue."';");
                $sql .= $v->SqlParamName;
                $sql .= ",";
                if($v->Direction == Direction::$Output){
                    $sqlOutPut .= $v->SqlParamName.",";
                }
            }
            if(count($params)>0){
                $sql = substr($sql, 0,  strlen($sql)-1).");";
                $sqlOutPut = substr($sqlOutPut, 0,  strlen($sqlOutPut)-1).";";
            }
        }else {
            $sql .= ");";
        }
        if(strlen($sqlOutPut)>7){
            $sql .= $sqlOutPut;
        }
        return $sql;
    }

}

/**
 * 定义存储参数类型
 * Class Direction
 */
class Direction{
    public static $Intput = 1;
    public static $Output = 2;
    public static $ReturnValue = 3;
}

/**
 *
 * Class SqlDBType
 */
class SqlDBType{
    public static $Int = 1;
    public static $Varchar = 2;
    public static $DateTime = 3;
}

/**
 * 存储过程参数定义
 * Class SqlParam
 */
class SqlParam{
    public $Direction = 1;
    public $SqlDBType = 1;
    public $SqlParamName;
    public $ParamValue;
    public function SqlParam($ParamName = null,$ParamValue = null){
        $this->SqlParamName = $ParamName;
        if(!is_numeric($ParamName)){
            $this->ParamValue = addslashes($ParamValue);
        }else{
            $this->ParamValue = $ParamValue;
        }
    }
    public function setDirection($SqlDirection){
        $this->Direction = $SqlDirection;
    }
    public function setSqlDBType($SqlDBType){
        $this->SqlDBType=$SqlDBType;
    }
    public function setParamName($ParamName){
        $this->ParamName=$ParamName;
    }
    public function setParamValue($ParamValue){
        $this->ParamValue=$ParamValue;
    }
}