PHP连接Oracle数据库及存储过程调用简单实例

一、本文以Windows Sever 2012 R2环境下32位PHP5.6.27连接Oracle 12c为例。

二、连接数据库

1.准备工作

2.打开PHP操作Oracle拓展，分别为php_pdo_oci.dll、php_oci8_12c.dll、php_oci8.dll

          ①、 配置安装好Oracle 12c客户端，Oracle客户端及SDK自行百度官网下

          ②、Oracle官方对应的Instant Client版本。建议下载Basic

3.打印phpinfo()

                    ①、务必根据PHP版本下载对应的Oracle客户端版本

                    ②、所标记目录为存放Oracle SDK目录，将上图标记目录复制下来；打开CMD，执行mkdir c:\php-sdk\oracle\x86\instantclient_12_1\sdk，建好目录

4.将准备好的Instant Client解压，然后把根目录下的所有文件，拷贝到上面建好的sdk目录和Apache的bin目录

5.创建环境变量

          Path中，添加c:\php-sdk\oracle\x86\instantclient_12_1\sdk（即上面建好的目录），

          ORACLE_HOME = C:\php-sdk\oracle\x86\instantclient_12_1\sdk

          TNS_ADMIN = C:\php-sdk\oracle\x86\instantclient_12_1\sdk

6.重启Apache

三、调用Oracle存储过程示例

//以下为某充值存储过程
public function recharge($payType, $jinE, $fillDate, $qsrq, $orderId)
{
    header("Content-type: text/html; charset=utf-8");
    $tns = "(DESCRIPTION =
                (ADDRESS_LIST =
                  (ADDRESS = (PROTOCOL = TCP)(HOST = 10.2.2.23)(PORT = 1521))
                )
                (CONNECT_DATA =
                  (SERVICE_NAME = fydb)
                )
              )";
    $db_username = "zhik";
    $db_password = "zhik";
    try {
        $conn = new PDO("oci:dbname=" . $tns, $db_username, $db_password);
        if (!$conn) {
            echo "Connection could not be established he.<br />";
            print_r($conn->errorInfo(), true);
        }
        $ls_bankId = iconv('UTF-8', 'GBK', $payType);
        $ll_jinE = iconv('UTF-8', 'GBK', $jinE);
        $ldt_fillDate = iconv('UTF-8', 'GBK', $fillDate);
        $ls_qsrq = iconv('UTF-8', 'GBK', $qsrq);
        $ls_orderId = iconv('UTF-8', 'GBK', $orderId);

        $errmsg = "";
        $pzhm = "";

        $sql_sp = "BEGIN InsertFillMoneyByZYHM(:bankId, round(:jinE,2),to_date(:fillDate,'YYYY/MM/DD hh:mi:ss'),to_date(:qsrq,'YYYY/MM/DD hh:mi:ss'),:orderid,:errmsg,:pzhm); END;"; // Parse

        $temp = $conn->prepare($sql_sp);

        $temp->bindParam('bankId', $ls_bankId);
        $temp->bindParam('jinE', $ll_jinE);
        $temp->bindParam(6, $ldt_fillDate);
        $temp->bindParam(12, $ls_qsrq);
        $temp->bindParam('orderid', $ls_orderId);

        $temp->bindParam('errmsg', $errmsg, PDO::PARAM_STR, 64);
        $temp->bindParam('pzhm', $pzhm, PDO::PARAM_INT, 64);
        $temp->execute();
        // var_dump($conn->errorInfo());
        $errmsg = iconv("gbk", "utf-8", $errmsg);
        //errmsg,pzhm两个参数为存储过程出参
        $res = array(
            "errmsg" => $errmsg,
            "pzhm"   => $pzhm
        );
        $result = Zend_Json::encode($res);
    } catch (PDOException $e) {
        $result = $e->getMessage();
    }
    return $result;
}