Oracle数据库和Mysql数据库不同,它没有Mysql的Auto_increment自动增长的属性。所以我们需要建立Oracle的Sequence序列来实现自动增长字段。
这边就以表tb_user为例,实现其字段id的自动增长方法,这里在配置文件config.php中增加如下几项设置:
针对版本:ThinkPHP3.2.3
修改文件:Db\Driver\Oracle.class.php
找到 execute方法,
找到:$this->initConnect(true); 这句
前面加上 $bind = $this->bind; 这句:
public function execute($str,$fetchSql=false) { $bind = $this->bind; //新增这句 $this->initConnect(true);
找到:foreach ($this->bind as $key => $val) { 这句
前面加上 $this->bind = $this->bind ? $this->bind : $bind; 这句:
$this->bind = $this->bind ? $this->bind : $bind; //新增这句 foreach ($this->bind as $key => $val) {
找到 $this->lastInsID = $this->_linkID->lastInsertId(); 这句
将其修改为:
//修改:
//$this->lastInsID = $this->_linkID->lastInsertId(); $this->lastInsID = $this->lastInsertId($this->table);
Oracle.class.php文件中新增以下代码:
/** * 取得Oracle最近插入的ID * @access public */ public function lastInsertId($sequence = '') { try { $lastInsID = $this->_linkID->lastInsertId(); } catch(\PDOException $e) { //对于驱动不支持PDO::lastInsertId()的情况 try { $lastInsID = 0; $seqPrefix = C("DB_SEQUENCE_PREFIX") ? C("DB_SEQUENCE_PREFIX") : 'seq_'; $sequence = $sequence ? $sequence : $seqPrefix.$this->table; $q = $this->query("SELECT {$sequence}.CURRVAL as t FROM DUAL"); if($q) { $lastInsID = $q[0]['t']; } } catch(\Exception $e) { //print "Error!: " . $e->getMessage() . "</br>"; //exit; } } return $lastInsID; }
调用方法:
1.数据库配置:
‘DB_PREFIX’=>’tb_’,//表名前缀
‘DB_SEQUENCE_PREFIX’ => ‘seq_’,//序列名前缀
‘DB_TRIGGER_PREFIX’ => ‘tig_’,//触发器名前缀
2.先创建user数据表
表字段:id, username, password
3.然后创建[序列+触发器]
—-创建序列
create sequence seq_user
increment by 1
start with 1
nomaxvalue
nominvalue
nocache;
—-创建触发器
create or replace trigger “tig_user”
before insert on tb_user
for each row when(new.id is null)
begin
select seq_user.nextval into :new.id from dual;
end;
4.最后一步,在UserAction中写插入数据代码如下:
$data = array( 'phone'=>$phone, 'password'=>md5($password) ); $r = M('user')->field(true)->add($data); //执行插入并返回上次插入Id if($r){ //$r = M('user')->getLastInsID(); //获取上次插入Id echo '上次插入记录:'.$r; }else{ $this->error('操作失败'); }