下面是一个完整的 PHP 操作 Oracle 数据库命令指南，涵盖了从连接到基本操作的实用代码示例：
1. 连接数据库
<?php
// 配置连接信息
$db_host = "localhost";
$db_port = "1521";
$db_service = "ORCL"; // 服务名
$db_user = "PLM_TEST";
$db_pass = "your_password"; // 实际密码
// 创建连接
$conn = oci_connect(
$db_user,
$db_pass,
"(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=$db_host)(PORT=$db_port))(CONNECT_DATA=(SERVICE_NAME=$db_service)))"
);
if (!$conn) {
$e = oci_error();
die("连接失败: " . $e['message']);
} else {
echo "Oracle 数据库连接成功!";
}
?>2. 执行查询
// 准备SQL语句
$sql = "SELECT employee_id, first_name, last_name FROM employees WHERE department_id = :dept_id";
// 创建语句句柄
$stmt = oci_parse($conn, $sql);
// 绑定参数（防止SQL注入）
$dept_id = 50;
oci_bind_by_name($stmt, ":dept_id", $dept_id);
// 执行查询
oci_execute($stmt);
// 获取结果
echo "<table border='1'>";
echo "<tr><th>ID</th><th>名字</th><th>姓氏</th></tr>";
while ($row = oci_fetch_array($stmt, OCI_ASSOC+OCI_RETURN_NULLS)) {
echo "<tr>";
echo "<td>" . $row['EMPLOYEE_ID'] . "</td>";
echo "<td>" . $row['FIRST_NAME'] . "</td>";
echo "<td>" . $row['LAST_NAME'] . "</td>";
echo "</tr>";
}
echo "</table>";
// 释放资源
oci_free_statement($stmt);3. 插入数据
$sql = "INSERT INTO employees (employee_id, first_name, last_name, email, hire_date, job_id)
VALUES (employees_seq.nextval, :fname, :lname, :email, SYSDATE, :job_id)";
$stmt = oci_parse($conn, $sql);
// 绑定参数
$fname = "张";
$lname = "三";
$email = "zhangsan@example.com";
$job_id = "IT_PROG";
oci_bind_by_name($stmt, ":fname", $fname);
oci_bind_by_name($stmt, ":lname", $lname);
oci_bind_by_name($stmt, ":email", $email);
oci_bind_by_name($stmt, ":job_id", $job_id);
// 执行并检查结果
if (oci_execute($stmt)) {
echo "新记录插入成功!";
oci_commit($conn); // 提交事务
} else {
$e = oci_error($stmt);
echo "插入失败: " . $e['message'];
}
oci_free_statement($stmt);4. 更新数据
$sql = "UPDATE employees SET salary = :salary WHERE employee_id = :emp_id";
$stmt = oci_parse($conn, $sql);
$salary = 8000;
$emp_id = 100;
oci_bind_by_name($stmt, ":salary", $salary);
oci_bind_by_name($stmt, ":emp_id", $emp_id);
if (oci_execute($stmt)) {
echo "记录更新成功!";
oci_commit($conn);
} else {
$e = oci_error($stmt);
echo "更新失败: " . $e['message'];
}
oci_free_statement($stmt);5. 删除数据
$sql = "DELETE FROM employees WHERE employee_id = :emp_id";
$stmt = oci_parse($conn, $sql);
$emp_id = 207;
oci_bind_by_name($stmt, ":emp_id", $emp_id);
if (oci_execute($stmt)) {
echo "记录删除成功!";
oci_commit($conn);
} else {
$e = oci_error($stmt);
echo "删除失败: " . $e['message'];
}
oci_free_statement($stmt);6. 调用存储过程
// 假设有存储过程: get_employee_name(emp_id IN NUMBER, emp_name OUT VARCHAR2)
$sql = "BEGIN get_employee_name(:emp_id, :emp_name); END;";
$stmt = oci_parse($conn, $sql);
$emp_id = 100;
$emp_name = ""; // 用于接收输出
oci_bind_by_name($stmt, ":emp_id", $emp_id);
oci_bind_by_name($stmt, ":emp_name", $emp_name, 100); // 100是缓冲区大小
oci_execute($stmt);
echo "员工姓名: " . $emp_name;
oci_free_statement($stmt);7. 事务处理
// 关闭自动提交
oci_set_autocommit($conn, false);
try {
// 执行多个操作...
$stmt1 = oci_parse($conn, "UPDATE accounts SET balance = balance - 100 WHERE id = 1");
oci_execute($stmt1);
$stmt2 = oci_parse($conn, "UPDATE accounts SET balance = balance + 100 WHERE id = 2");
oci_execute($stmt2);
// 提交事务
oci_commit($conn);
echo "事务执行成功!";
} catch (Exception $e) {
// 回滚事务
oci_rollback($conn);
echo "事务失败: " . $e->getMessage();
}
oci_free_statement($stmt1);
oci_free_statement($stmt2);8. 错误处理最佳实践
function execute_query($conn, $sql, $params = []) {
$stmt = oci_parse($conn, $sql);
if (!$stmt) {
$e = oci_error($conn);
throw new Exception("SQL解析失败: " . $e['message']);
}
// 绑定参数
foreach ($params as $key => $value) {
oci_bind_by_name($stmt, $key, $params[$key]);
}
if (!oci_execute($stmt)) {
$e = oci_error($stmt);
throw new Exception("查询执行失败: " . $e['message']);
}
return $stmt;
}
// 使用示例
try {
$stmt = execute_query($conn,
"SELECT * FROM products WHERE category = :cat",
[":cat" => "Electronics"]
);
// 处理结果...
} catch (Exception $e) {
echo "错误: " . $e->getMessage();
}9. 关闭连接
// 关闭连接
oci_close($conn);📌 重要注意事项：
安全第一：
- 始终使用
oci_bind_by_name()防止SQL注入 - 不要在代码中硬编码密码（使用环境变量或配置文件）
- 最小权限原则：使用普通用户而非SYSTEM
- 始终使用
性能优化：
// 设置预取行数（提高查询性能） oci_set_prefetch($stmt, 100); // 使用持久连接（高并发场景） $conn = oci_pconnect($user, $pass, $connection_string);数据类型处理：
// 处理CLOB类型 $clob = oci_new_descriptor($conn, OCI_D_LOB); oci_bind_by_name($stmt, ":clob_data", $clob, -1, OCI_B_CLOB); // 处理日期 $date = date('d-M-Y'); oci_bind_by_name($stmt, ":hire_date", $date);替代连接方式（PDO）：
$dbh = new PDO("oci:dbname=//localhost:1521/ORCL;charset=UTF8", $user, $pass); $stmt = $dbh->prepare("SELECT * FROM employees WHERE id = :id"); $stmt->execute([':id' => 100]);
这些示例覆盖了PHP操作Oracle数据库的常见场景，建议从简单查询开始练习，逐步掌握事务处理和存储过程调用等高级功能。
评论