ThinkPHP执行Oracle存储过程
- WBOY原创
- 2016-06-07 11:43:441543浏览
ThinkPHP Oracle执行存储过程。
一、首先修改ThinkPHP3.0的Oracle驱动(ThinkPHPLibDriverDbDbOracle.class.php),在DbOracle类中增加以下方法： /**<br>
----------------------------------------------------------<br>
* 执行存储过程<br>
----------------------------------------------------------<br>
* @author hongping <hongping626@qq.com><br>
----------------------------------------------------------<br>
* @param string $pName 存储过程名称 <br>
* @param array $pValue 传入参数值<br>
----------------------------------------------------------<br>
* @return array 返回执行结果<br>
----------------------------------------------------------<br>
*/<br>
public function execProcedure($pName,$pValue) {<br>
$pValue = array_change_key_case($pValue, CASE_LOWER);//强制下标为小写<br>
$this->initConnect(true);<br>
if ( !$this->_linkID ) return false;<br>
//更改事务模式<br>
$this->mode = OCI_COMMIT_ON_SUCCESS;<br>
//释放前次的查询结果<br>
if ( $this->queryID ) $this->free();<br>
N('db_write',1);<br>
// 记录开始执行时间<br>
G('queryStartTime'); <br>
$argSql = "SELECT ARGUMENT_NAME,IN_OUT FROM USER_ARGUMENTS WHERE OBJECT_NAME = '".strtoupper($pName)."' ORDER BY SEQUENCE"; <br>
$argRow = $this->query($argSql);<br>
$ParStr = '';<br>
$x = 0; <br>
foreach($argRow as $key=>$value){<br>
if($x==0){<br>
$ParStr .= ":".$value['argument_name']; <br>
}else{<br>
$ParStr .= ",:".$value['argument_name']; <br>
}<br>
$x ;<br>
}<br>
$this->queryID = oci_parse($this->_linkID, 'BEGIN '.$pName.'('.$ParStr.');END;');<br>
foreach($argRow as $key=>$value){<br>
$parmName = strtolower($value['argument_name']);<br>
$parmType = strtolower($value['in_out']);<br>
if($parmType==='out'){<br> oci_bind_by_name($this->queryID,":".$parmName,$OutData[$parmName],2048);<br>
}其他{<br>
$OutData[$parmName] = $pValue[$parmName];<br>
oci_bind_by_name($this->queryID,":".$parmName,$OutData[$parmName],2048);<br>
}<br>
} <br>
oci_execute($this->queryID); <br>
$this->debug();<br>
if (!$this->queryID) {<br>
$this->error();<br>
返回 $this->queryID;<br>
}否则{<br>
返回 $OutData;<br>
}<br>
}二、然后在ThinkPHPLibCoreModel.class.php加入以下(1185行左右):/** <code class="prettyprint linenums lang-php">/** <br>
---------------------------------------------------------- <br>
* 执行oracle存儲過程 <br>
---------------------------------------------------------- <br>
* @access public <br>
---------------------------------------------------------- <br>
* @param string $sql SQL指令 <br>
* @param array $parr 传入传出参数值 <br>
* @param boolean $parse 是否需要解析SQL <br>
---------------------------------------------------------- <br>
* @return false | integer <br>
---------------------------------------------------------- <br>
*/ <br>
public function execProcedure($sql,$parr,$parse=false) { <br>
$sql = $this->parseSql($sql,$parse); <br>
return $this->db->execProcedure($sql,$parr); <br>
}
-------------------------------------------------- -------- public function testPub(){ <br>
$data['name'] = $_POST['name']; //存储过程传入参数<br>
$data['value'] = $_POST['value']; //存储过程传入参数<br>
<br>
$pdName = "PUB_TEST_KT";/*存储过程名*/ <br>
$result =M() ->execProcedure($pubName,$pubParm);//执行<br>
$this->success($result['res']);//返回结果
* 执行oracle存储过程
-------------------------------------------------- --------
* @access public -------------------------------------------------- -------- * @param string $sql SQL指令 * @param array $parr 传入传出参数值 * @param boolean $parse 是否需要解析SQL -------------------------------------------------- -------- * @return false | integer -------------------------------------------------- -------- */ 公共函数 execProcedure($sql,$parr,$parse=false) { $sql = $this->parseSql($sql,$parse); 返回 $this->db->execProcedure($sql,$parr); } 使用示例：public function testPub(){
$data['name'] = $_POST['name']; // 存储过程形成参数
$data['value'] = $_POST['value']; // 存储过程形成参数
$pdName = "PUB_TEST_KT";/*存储过程名*/
$result =M() ->execProcedure($pubName,$pubParm);//执行
$this->success($result['res']);//返回结果 AD：真正免费，域名虚机企业邮箱=0元