MySQL與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項目時,盡量用唯一來關聯,少用自增,因為太麻煩了

服務器租用/服務器托管中國五強！虛擬主機域名注冊頂級提供商！15年品質保障！--億恩科技[ENKJ.COM]