MySQL與Oracle一些小區(qū)別

前幾天做一個(gè)項(xiàng)目,本來(lái)是用MySQL的,但是項(xiàng)目需要Oracle庫(kù),后來(lái)我經(jīng)過(guò)一個(gè)月時(shí)間,把整個(gè)程序全部換成了Oracle庫(kù),在操作過(guò)程中發(fā)現(xiàn)MySQL與Oracle的一些不同之處.

1.MySQL的大文本可以直接進(jìn)行讀寫(xiě),Oracle的不可以,Oracle的大文本數(shù)據(jù)類(lèi)型是clob,更新和讀寫(xiě)不能直接像MySQL一樣,用insert 和 update.相關(guān)語(yǔ)句,我貼到下面.
-
 

入庫(kù)時(shí):$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();
更新時(shí):$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在表里可以設(shè)一個(gè)自增,而Oracle里只能用序列和觸發(fā)器來(lái)實(shí)現(xiàn).如果要做Oracle項(xiàng)目時(shí),盡量用唯一來(lái)關(guān)聯(lián),少用自增,因?yàn)樘�麻煩�?/p>
本文出自：億恩科技【www.cmtents.com】

服務(wù)器租用/服務(wù)器托管中國(guó)五強(qiáng)！虛擬主機(jī)域名注冊(cè)頂級(jí)提供商！15年品質(zhì)保障！--億恩科技[ENKJ.COM]