使用 Oracle 和 PHP 执行事务
作者：Harry Fuecks

如何使用 Oracle 数据库的强大功能可靠地管理多对多关系。

本文相关下载： Oracle 数据库 10g Zend Core for Oracle Apache HTTP Server 1.3 和更高版本

2005 年 11 月发表

本“Oracle+PHP 指南”方法文档介绍了事务的必要性以及如何通过 PHP 的 OCI 扩展使用 Oracle 的事务服务，同时还着重介绍了某些潜在的“问题”。

CREATE SEQUENCE blog_id_seq
INCREMENT BY 1;
/

CREATE TABLE blogs (
id NUMBER PRIMARY KEY,
title VARCHAR2(200),
date_published DATE,
text CLOB
);
/

CREATE SEQUENCE category_id_seq
INCREMENT BY 1;
/

CREATE TABLE categories (
id NUMBER PRIMARY KEY,
name VARCHAR2(30) UNIQUE
);
/

CREATE TABLE blogs_to_categories (
blog_id INTEGER NOT NULL
REFERENCES blogs(id),
category_id INTEGER NOT NULL
REFERENCES categories(id),
PRIMARY KEY (blog_id, category_id)
);
/

-- Delete all existing category links to this blog entry
DELETE FROM blogs_to_categories WHERE blog_id = 5

-- Insert a single link between a blog and a category
INSERT INTO blogs_to_categories
(blog_id, category_id)
VALUES
    (5, 2)

• 原子性 - 事务要么必须彻底完成，要么根本不完成；不允许部分完成事务。
• 一致性 - 必须始终遵守数据库约束。
• 孤立性 - 多个事务不应影响彼此的数据。例如，如果事务 B 正在插入到 blogs_to_categories 表中，则不允许事务 A 删除类别。
• 持久性 - 如果事务已经完成（提交），则不会丢失数据（可以从故障中恢复）。

<?php 
$conn = oci_connect('SCOTT','TIGER') or die;

// Some categories
$categories = array(
'php',
'oracle',
'programming',
'web',
'this string is too long for the name column'
);

$sql = "INSERT INTO categories
(id, name)
VALUES
(category_id_seq.nextval,:category)
        ";

$stmt = oci_parse($conn,$sql);

foreach ( $categories as $category ) {
    
oci_bind_by_name($stmt,':category', $category,30);
    
// Note the OCI_DEFAULT parameter
if ( !oci_execute($stmt,OCI_DEFAULT) ) {
    
// If we have a problem, rollback then die
oci_rollback($conn);
die;
    }

}

// If we got this far, it's OK to commit
oci_commit($conn);// etc.
?>

                               
Warning: oci_execute() [
                              
function.oci_execute]:OCIStmtExecute: 
ORA-01461:can bind a LONG value only for insert into a LONG
column in  
                              
/home/harry/public_html/create_categories.php on  
                              
line
25
                            

<?php 
$conn = oci_connect('SCOTT','TIGER') or die;

/* Start by inserting a blog entry */

// An array representing a submitted blog entry
$blog = array(
'id'=>NULL,
'title'=>'Hello World!',
'text'=>'This is my first blog entry',
'categories' => array(1,999) // Note second ID does not exist
);

$sql = "INSERT INTO blogs
(id,title,date_published,text)
VALUES
(blog_id_seq.nextval,:title,SYSDATE,:text)
RETURNING
id INTO :id ";

$stmt = oci_parse($conn,$sql);

oci_bind_by_name($stmt,':title', $blog['title'],200);
oci_bind_by_name($stmt,':id', $blog['id'],32);
oci_bind_by_name($stmt,':text', $blog['text']);


// Note OCI_DEFAULT - begin a transaction
if ( !oci_execute($stmt,OCI_DEFAULT) ) {
oci_rollback($conn);
exit(1);
}

/* Now count the total number of blog entries */



$sql = "SELECT COUNT(*) AS num_entries FROM blogs";

$stmt = oci_parse($conn,$sql);

// Automatically populates the $num_entries variable
oci_define_by_name($stmt,"NUM_ENTRIES",$num_entries);

// Whoops forgot the OCI_DEFAULT!
oci_execute($stmt);

oci_fetch($stmt);

$result = "Blog added.There are now $num_entries blogs\n";

/* Now insert the categories related to the entry */

$sql = "INSERT INTO blogs_to_categories
(blog_id, category_id)
VALUES
(:blog_id, :category_id)";

$stmt = oci_parse($conn,$sql);

// Loop through the categories
foreach ( $blog['categories'] as $category_id ) {

oci_bind_by_name($stmt,':blog_id', $blog['id'],32);
oci_bind_by_name($stmt,':category_id', $category_id,32);
    
// Note OCI_DEFAULT again
if ( !oci_execute($stmt,OCI_DEFAULT) ) {
oci_rollback($conn);
exit(1);

    }
    
}

oci_commit($conn);
echo $result

?>

事务孤立和连接

<?php 
$conn = oci_connect("SCOTT", "TIGER") or die;

$categories = array(
'transactions',
'sessions',
);

$sql = "INSERT INTO categories
(id, name)
VALUES
(category_id_seq.nextval,:category)
        ";

$stmt = oci_parse($conn,$sql);

foreach ( $categories as $category ) {
    
oci_bind_by_name($stmt,':category', $category,30);
    
if ( !oci_execute($stmt,OCI_DEFAULT) ) {
oci_rollback($conn);
die();
    }

}

// Establish a new, temporary Oracle connection
$newSess = oci_new_connect("SCOTT", "TIGER") or die;

$sql = "SELECT COUNT(*) AS num_cats FROM categories";

$stmt = oci_parse($newSess,$sql);

oci_define_by_name($stmt,"NUM_CATS",$oldnum_cats);

// No need for OCI_DEFAULT here...
oci_execute($stmt);

oci_fetch($stmt);

// Logoff the session (technically not needed as it will be cleaned up anyway)
oci_close($newSess);

// Do the same for the primary session
$stmt = oci_parse($conn,$sql);

oci_define_by_name($stmt,"NUM_CATS",$newnum_cats);

// Here we do need OCI_DEFAULT
oci_execute($stmt,OCI_DEFAULT);

oci_fetch($stmt);

// If we got this far, it's OK to commit
oci_commit($conn);

echo "Before I had $oldnum_cats.Now I have $newnum_cats\n";
?>

将您的意见发送给我们