Does thinkphp5 support oracle?

First of all, we know that php fully supports oracle, then thinkphp5 as a php framework can also fully support oracle.

How does thinkphp5 connect to oracle?

Database: ray

Table structure: ray_user

CREATE TABLE IF NOT EXISTS ray_user (
user_id int(11) unsigned NOT NULL AUTO_INCREMENT,
user_name varchar(10) NOT NULL,
user_pwd varchar(40) NOT NULL,
PRIMARY KEY (user_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 AUTO_INCREMENT=18 ;

Transfer data in the table: ray_user

INSERT INTO ray_user (user_id, user_name, user_pwd) VALUES
(1, ‘updatename’, ‘ray’),
(2, ‘testname’, ‘testpwd’),

1. CURD operation in mysql environment

Database configuration database.php

<?php
// +----------------------------------------------------------------------
// | ThinkPHP [ WE CAN DO IT JUST THINK ]
// +----------------------------------------------------------------------
// | Copyright (c) 2006~2018 http://thinkphp.cn All rights reserved.
// +----------------------------------------------------------------------
// | Licensed ( http://www.apache.org/licenses/LICENSE-2.0 )
// +----------------------------------------------------------------------
// | Author: liu21st <liu21st@gmail.com>
// +----------------------------------------------------------------------

return [
   // 数据库类型
   &#39;type&#39;            => &#39;mysql&#39;,
   // 服务器地址
   &#39;hostname&#39;        => &#39;127.0.0.1&#39;,
   // 数据库名
   &#39;database&#39;        => &#39;ray&#39;,
   // 用户名
   &#39;username&#39;        => &#39;root&#39;,
   // 密码
   &#39;password&#39;        => &#39;&#39;, // 你的密码
   // 端口
   &#39;hostport&#39;        => &#39;3306&#39;,
   // 连接dsn
   &#39;dsn&#39;             => &#39;&#39;,
   // 数据库连接参数
   &#39;params&#39;          => [],
   // 数据库编码默认采用utf8
   &#39;charset&#39;         => &#39;utf8&#39;,
   // 数据库表前缀
   &#39;prefix&#39;          => &#39;ray_&#39;,
   // 数据库调试模式
   &#39;debug&#39;           => true,
   // 数据库部署方式:0 集中式(单一服务器),1 分布式(主从服务器)
   &#39;deploy&#39;          => 0,
   // 数据库读写是否分离 主从式有效
   &#39;rw_separate&#39;     => false,
   // 读写分离后 主服务器数量
   &#39;master_num&#39;      => 1,
   // 指定从服务器序号
   &#39;slave_no&#39;        => &#39;&#39;,
   // 是否严格检查字段是否存在
   &#39;fields_strict&#39;   => true,
   // 数据集返回类型
   &#39;resultset_type&#39;  => &#39;array&#39;,
   // 自动写入时间戳字段
   &#39;auto_timestamp&#39;  => false,
   // 时间字段取出后的默认时间格式
   &#39;datetime_format&#39; => &#39;Y-m-d H:i:s&#39;,
   // 是否需要进行SQL性能分析
   &#39;sql_explain&#39;     => false,
];

Controller User.php

<?php
namespace app\index\controller;

use think\Controller;
use app\index\model\User as US;

class User extends Controller
{
public function index() {
    $obj_user = new US;
    // 查找
    $data = $obj_user->operateUser("find",null,"1");
    var_dump($data);
    // 更新
    $updateData = [
        &#39;user_name&#39; => &#39;updatename&#39;
    ];
    $result = $obj_user->operateUser("update",$updateData,"1");
    var_dump($result);
    // 新增
    $insertData = [
        &#39;user_name&#39; => &#39;testname&#39;,
        &#39;user_pwd&#39; => &#39;testpwd&#39;
    ];
    $result = $obj_user->operateUser("insert",$insertData);
    var_dump($result);
    // 删除
    $result = $obj_user->operateUser("delete",null,&#39;2&#39;);
    var_dump($result);
}
}

Model User.php

<?php
namespace app\index\model;

use think\Model;

class User extends Model
{
public function operateUser($directive,$data = null,$user_id = null) {
    if($directive == "find" && $user_id != null) {
        return User::where(&#39;user_id&#39;,$user_id)->find();
    } else if($directive == "insert" && $data != null) {
        return User::save($data) ? 1 : 0;
    } else if($directive == "update" && $data != null && $user_id != null) {
        return User::where(&#39;user_id&#39;,$user_id)->find()->save($data) ? 1 : 0;
    } else if($directive == "delete" && $user_id != null) {
        return User::where(&#39;user_id&#39;,$user_id)->delete() ? 1 : 0;
    } else {
        return null;
    }
}
}

2. CURD operation in oracle environment

Database configuration file database.php

<?php
// +----------------------------------------------------------------------
// | ThinkPHP [ WE CAN DO IT JUST THINK ]
// +----------------------------------------------------------------------
// | Copyright (c) 2006~2018 http://thinkphp.cn All rights reserved.
// +----------------------------------------------------------------------
// | Licensed ( http://www.apache.org/licenses/LICENSE-2.0 )
// +----------------------------------------------------------------------
// | Author: liu21st <liu21st@gmail.com>
// +----------------------------------------------------------------------

return [
   // 数据库类型
   &#39;type&#39;            => &#39;\think\oracle\Connection&#39;,
   // 服务器地址
   &#39;hostname&#39;        => &#39;127.0.0.1&#39;,
   // 数据库名
   &#39;database&#39;        => &#39;orcl&#39;,
   // 用户名
   &#39;username&#39;        => &#39;Scott&#39;,
   // 密码
   &#39;password&#39;        => &#39;&#39;, // 你的密码
   // 端口
   &#39;hostport&#39;        => &#39;1521&#39;,
   // 连接dsn
   &#39;dsn&#39;             => &#39;&#39;,
   // 数据库连接参数
   &#39;params&#39;          => [],
   // 数据库编码默认采用utf8
   &#39;charset&#39;         => &#39;utf8&#39;,
   // 数据库表前缀
   &#39;prefix&#39;          => &#39;ray_&#39;,
   // 数据库调试模式
   &#39;debug&#39;           => true,
   // 数据库部署方式:0 集中式(单一服务器),1 分布式(主从服务器)
   &#39;deploy&#39;          => 0,
   // 数据库读写是否分离 主从式有效
   &#39;rw_separate&#39;     => false,
   // 读写分离后 主服务器数量
   &#39;master_num&#39;      => 1,
   // 指定从服务器序号
   &#39;slave_no&#39;        => &#39;&#39;,
   // 是否严格检查字段是否存在
   &#39;fields_strict&#39;   => true,
   // 数据集返回类型
   &#39;resultset_type&#39;  => &#39;array&#39;,
   // 自动写入时间戳字段
   &#39;auto_timestamp&#39;  => false,
   // 时间字段取出后的默认时间格式
   &#39;datetime_format&#39; => &#39;Y-m-d H:i:s&#39;,
   // 是否需要进行SQL性能分析
   &#39;sql_explain&#39;     => false,
];

3. Query records based on the specified ID

Since Oracle table names and field names need to be added with double quotes, rewrite them The parseSqlTable and parseWhereItem methods in thinkphp\library\db\Builder.php. After the rewriting is completed, query the record based on the ID and OK.

   ...
/**
    * 将SQL语句中的__TABLE_NAME__字符串替换成带前缀的表名（小写）
    * @access protected
    * @param string $sql sql语句
    * @return string
    */
   protected function parseSqlTable($sql)
   {
        return '"'. strtoupper($this->query->parseSqlTable($sql)).'"'; //// 前后加上双引号并将表明设置为大写
   }

......
     // where子单元分析
   protected function parseWhereItem($field, $val, $rule = '', $options = [], $binds = [], $bindName = null)
   {
       // 字段分析
       $key = $field ? '"'. $this->parseKey($field, $options, true) .'"' : ''; ////前后加上双引号

       // 查询规则和条件
       if (!is_array($val)) {
           $val = is_null($val) ? ['null', ''] : ['=', $val];
       }
       list($exp, $value) = $val;
       ...

Rewritten the controller and model layer methods:

##Controller Users.php

<?php
namespace app\index\controller;

use think\Controller;
use app\index\model\Users as US;

class Users extends Controller
{
    public function index() {
        // 查询
        $obj_users = new US;
        $data = $obj_users->operateUser("find",null,"1");
        var_dump($data);
        // 更新
        $updateData = [
            &#39;NAME&#39; => "updateora",
            &#39;PWD&#39; => "newpwd"
        ];
        $result = $obj_users->operateUser("update",$updateData,"1");
        var_dump($result);
        // 插入
        $insertData = [
            &#39;NAME&#39; => &#39;testname&#39;,
            &#39;PWD&#39; => &#39;testpwd&#39;
        ];
        $result = $obj_users->operateUser("insert",$insertData);
        var_dump($result);
        // 删除
        $result = $obj_users->operateUser("delete",null,&#39;18&#39;);
        var_dump($result);
    }
}

Model Users.php

<?php
namespace app\index\model;

use think\Model;

class Users extends Model
{
    public function operateUser($directive,$data = null,$ID = null) {
        if($directive == "find" && $ID != null) {
            return Users::where(&#39;ID&#39;,$ID)->find();
        } else if($directive == "insert" && $data != null) {
            /*$id = Users::getLastInsID(&#39;SEQUSERS&#39;)-2;
            var_dump($id);
            $data[&#39;ID&#39;] = $id;*/
            return Users::save($data,[],&#39;SEQUSERS&#39;) ? 1 : 0; // 注意这里传参
        } else if($directive == "update" && $data != null && $ID != null) {
            return Users::where(&#39;ID&#39;,$ID)->find()->save($data) ? 1 : 0;
        } else if($directive == "delete" && $ID != null) {
            return Users::where(&#39;ID&#39;,$ID)->delete() ? 1 : 0;
        } else {
            return null;
        }
    }
}

After testing and updating the data, the next step is the most troublesome addition. Because the MySQL primary key can be auto-incremented by adding the A-I attribute to the PK, while Oracle needs to achieve it through a trigger. A simple implementation method is used below.

Trigger, sequence realizes Oracle primary key auto-increment.

CREATE OR REPLACE TRIGGER TRIUSERS BEFORE
INSERT ON SCOTT.USERS FOR EACH ROW
WHEN (
new.id is null
      )
begin
select SEQUSERS.nextval into:new.id from dual;
end;

create sequence SEQUSERS
minvalue 1
maxvalue 999999999999999999999999999
start with 1
increment by 1
nocache;

Need to rewrite the getLastInsId() method in think-oracle\src\Connection.php

/**
     * 获取最近插入的ID
     * @access public
     * @param string  $sequence     自增序列名
     * @return string
     */
    public function getLastInsID($sequence = null)
    {
        $pdo    = $this->linkID->query("select {$sequence}.nextval as id from dual");
        $pdo    = $this->linkID->query("select {$sequence}.currval as id from dual");
        $result = $pdo->fetchColumn();
        $pdo    = $this->linkID->query("alter sequence {$sequence} increment by -1");
        $pdo    = $this->linkID->query("select {$sequence}.nextval as id from dual");
        $pdo    = $this->linkID->query("alter sequence {$sequence} increment by 1");
        return $result;
    }

The above content is for reference only!

Recommended tutorial:

thinkphp tutorial

The above is the detailed content of Does thinkphp5 support oracle?. For more information, please follow other related articles on the PHP Chinese website!