ThinkPHP3.0执行Oracle存储过程

一、首先修改ThinkPHP3.0的Oracle驱动(ThinkPHP\Lib\Driver\Db\DbOracle.class.php),在DbOracle类中增加以下方法：

 /**
     +----------------------------------------------------------
     * 执行存儲過程 
     +----------------------------------------------------------
     * @access public add by hongping at 2012.06.26
     +----------------------------------------------------------
     * @param  string $pubName  存储过程名称  
     +----------------------------------------------------------
     * @param  array  $valueArr 传入传出参数,必須与存储过程定义参数顺序一致,如传出参数则为false
     +----------------------------------------------------------
     * @throws ThinkExecption
     +----------------------------------------------------------
     */
     public function execpub($pubName,$valueArr) {               
        $this->initConnect(true);
        if ( !$this->_linkID ) return false;
        //更改事务模式
        $this->mode = OCI_COMMIT_ON_SUCCESS;
        //释放前次的查询结果
        if ( $this->queryID ) $this->free();
        N('db_write',1);
        // 记录开始执行时间
        G('queryStartTime');        
        $argSql = "SELECT ARGUMENT_NAME,IN_OUT FROM USER_ARGUMENTS WHERE OBJECT_NAME = '".strtoupper($pubName)."' ORDER BY SEQUENCE";        
        $argRow = $this->query($argSql);
        $pubParm = array(); 
        $ParStr = '';          
        foreach($argRow as $key=>$value){
                $ParStr .= ",:".$value['argument_name'];
                $pubParm[] = $value['argument_name'].','.$value['in_out'];
        }        
        $ParStr = substr($ParStr,1);
        $BindArr = array_combine($pubParm,$valueArr);
        $this->queryID = oci_parse($this->_linkID, 'BEGIN '.$pubName.'('.$ParStr.');END;');
        foreach($BindArr as $key=>$value){
          $EIO = explode(',',$key);
          $EParm = strtolower($EIO[0]);/*Param*/
          $IO = strtolower($EIO[1]);/*IN OUT TYPE{IN/OUT/IN_OUT}}*/
          if($IO==='out'){
            oci_bind_by_name($this->queryID, ":".$EParm, $OutData[$EParm],2048);
          }else{
			$OutData[$EParm] = $value;
            oci_bind_by_name($this->queryID, ":".$EParm, $OutData[$EParm],2048);
          }
        }         
        oci_execute($this->queryID);             
        $this->debug();
        if (!$this->queryID) {
            $this->error();
            return $this->queryID;
        }else {
            return $OutData;
        }
    }

二、然后在ThinkPHP\Lib\Core\Model.class.php加入以下方法(1185行左右加入为佳):

/**
     +----------------------------------------------------------
     * 执行oracle存儲過程
     +----------------------------------------------------------
     * @access public
     +----------------------------------------------------------
     * @param string  $sql    SQL指令
     * @param array   $parr   传入传出参数值
     * @param boolean $parse  是否需要解析SQL
     +----------------------------------------------------------
     * @return false | integer
     +----------------------------------------------------------
     */
    public function execpub($sql,$parr,$parse=false) {
        $sql  =   $this->parseSql($sql,$parse);
        return $this->db->execpub($sql,$parr);
    }

三、以下为使用例子:

Action:

// 执行存储过程测试
class PubDemoAction extends CommonAction {    
    public function pubAjaxTest(){
         $dbt = M();         
         $pSn =  $_POST['sn'];
         $pStation =  $_POST['station'];     
         
         if($pSn!='' && $pStation!=''){
            $pubParm = array($pSn,$pStation,false);
            $pubName = "PUB_CHECK_SN_KT";/*存儲過程名字*/
            $result = $dbt->execpub($pubName,$pubParm);
         }
         $this->success($result['res']);
    }
}

View:

<div class="page">
    <div class="pageContent">    
    <form method="post" action="__URL__/pubAjaxTest/" class="pageForm required-validate" οnsubmit="return validateCallback(this,navTabAjaxDone);">
        Oracle存儲過程({$pubName})測試：<br /><br /><br /><br />
        Serial Number:<input type="text" name="sn" class="medium" value="A112EV0012" ><br />
        Station Number:<input type="text" name="station" class="medium" value="25" ><br />
        提示信息：{$rowPub.RES}<br /><br />
        <div class="buttonActive">
          <div class="buttonContent">
            <button type="submit">執行Pub</button>
            </div>
        </div>    
    </form>
    </div>
</div>

(转载本文请注明出处、作者)