前几天做一个项目,本来是用MySQL的,但是项目需要Oracle库,后来我经过一个月时间,把整个程序全部换成了Oracle库,在操作过程中发现
前几天做一个项目,本来是用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项目时,尽量用唯一来关联,少用自增,因为太麻烦了
