前几天做一个项目,本来是用mysql的,但是项目需要oracle库,后来我经过一个月时间,把整个程序全部换成了oracle库,在操作过程中发现mysql与oracle的一些不同之处.
1.mysql的大文本可以直接进行读写,oracle的不可以,oracle的大文本数据类型是clob,更新和读写不能直接像mysql一样,用insert 和 update.相关语句,我贴到下面.
入库时:$query=oci_parse($conn,insert into cms_article (title,subhead,abstract,keyword,source,author,releasedate,rate,article_type,siteid,publisher,domurl3,website,lock_state,article_state,article_sort,hits,content) values ('.$_request['title'].','.$_request['subhead'].','.$_request['abstract'].','.$_request['keyword'].','.$_request['source'].','.$_request['author'].','.$releasedate.','.$_request['rate'].','.$col.','.$siteid.','.$_session['username'].','.$_request['domurl3'].','.$_request['website'].',1,3,1,1,empty_clob()) returning content into :mylob_loc);
$mylob = oci_new_descriptor($conn, oci_d_lob);
oci_bind_by_name($query, :mylob_loc, $mylob, -1, oci_b_clob);
$result=oci_execute($query,oci_default);
if ( !$mylob->save($_request['content'])) {
//if ( !$mylob->save('insert: '.date('h:i:s',time())) ) {
// on error, rollback the transaction
oci_rollback($conn);
} else {
// on success, commit the transaction
oci_commit($conn);
}
// free resources
oci_free_statement($query);
$mylob->free();
更新时:$exec=oci_parse($conn,update cms_article set title = '.$_request['title'].',subhead = '.$_request['subhead'].',abstract = '.$_request['abstract'].',keyword = '.$_request['keyword'].',source = '.$_request['source'].',author = '.$_request['author'].',releasedate = '.$releasedate.',rate = '.$_request['rate'].',article_state = 2,modified_name = '.$_session['username'].',domurl3 = '.$_request['domurl3'].',website = '.$_request['website'].' where id_article=.$_request['id'].);
$result=oci_execute($exec);
$sql = select content from cms_article where id_article = .$_request['id']. for update ;
$stmt = oci_parse($conn, $sql);
// execute the statement using oci_default (begin a transaction)
oci_execute($stmt, oci_default)
or die (unable to execute query\n);
// fetch the selected row
if ( false === ($row = oci_fetch_assoc($stmt) ) ) {
oci_rollback($conn);
die (unable to fetch row\n);
}
// discard the existing lob contents
if ( !$row['content']->truncate() ) {
oci_rollback($conn);
die (failed to truncate lob\n);
}
// now save a value to the lob
if ( !$row['content']->save($_request['content']) ) {
// on error, rollback the transaction
oci_rollback($conn);
} else {
// on success, commit the transaction
oci_commit($conn);
}
// free resources
oci_free_statement($stmt);
$row['content']->free();
2.mysql在表里可以设一个自增,而oracle里只能用序列和触发器来实现.如果要做oracle项目时,尽量用唯一来关联,少用自增,因为太麻烦了
，