我直接把代码摆在这里分享哈
<?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;
}
}