Oracle SQL输入参数如何定义与使用？

1. 核心概念：什么是输入参数？
2. 在 PL/SQL 块中使用参数
   • 过程
   • 函数
3. 在 SQL 语句中使用绑定变量
   • 在 PL/SQL 中使用
   • 在应用程序中 (Java, Python, JDBC 等)
4. 最佳实践：为什么使用输入参数？

核心概念：什么是输入参数？

输入参数是传递给一个程序单元（如存储过程、函数或 SQL 语句）的值，程序单元接收这个值，并在其内部逻辑中使用它，但不会修改这个原始值。

它就像一个占位符，让程序可以处理不同的数据,而无需修改程序本身的代码。

• PL/SQL 参数：定义在 PROCEDURE 或 FUNCTION 的头部,用于接收调用者传入的值。
• SQL 绑定变量：在 SQL 语句中用冒号 开头的标识符，用于在语句执行时接收一个具体的值，这是 Oracle 优化 SQL 执行和防止 SQL 注入的关键机制。

在 PL/SQL 块中使用参数

这是最常见的使用场景，通常用于创建存储过程和函数,将业务逻辑封装在数据库中。

A. 存储过程

存储过程执行一个操作,可以有一个或多个输入参数。

语法：

CREATE OR REPLACE PROCEDURE procedure_name (
    parameter1_name IN data_type,
    parameter2_name IN data_type,
    ...
)
IS
    -- 声明变量
BEGIN
    -- 过程逻辑，使用 parameter_name
    -- ...
END procedure_name;
/

示例： 创建一个根据员工 ID 查询员工姓名和薪资的过程。

CREATE OR REPLACE PROCEDURE get_employee_info (
    p_emp_id IN NUMBER
)
IS
    v_emp_name employees.first_name%TYPE;
    v_salary employees.salary%TYPE;
BEGIN
    -- 使用输入参数 p_emp_id 查询数据
    SELECT first_name, salary
    INTO v_emp_name, v_salary
    FROM employees
    WHERE employee_id = p_emp_id;
    -- 输出结果
    DBMS_OUTPUT.PUT_LINE('员工ID: ' || p_emp_id);
    DBMS_OUTPUT.PUT_LINE('姓名: ' || v_emp_name);
    DBMS_OUTPUT.PUT_LINE('薪资: ' || v_salary);
EXCEPTION
    WHEN NO_DATA_FOUND THEN
        DBMS_OUTPUT.PUT_LINE('错误：未找到 ID 为 ' || p_emp_id || ' 的员工。');
END get_employee_info;
/

如何调用：

-- 调用过程，传入参数 101
EXEC get_employee_info(101);
-- 调用过程，传入参数 999 (不存在的ID)
EXEC get_employee_info(999);

B. 函数

函数与过程类似，但它必须返回一个值，函数的参数通常也是 IN 类型。

语法：

CREATE OR REPLACE FUNCTION function_name (
    parameter1_name IN data_type
) RETURN return_data_type
IS
    -- 声明变量
BEGIN
    -- 函数逻辑
    -- ...
    RETURN value; -- 必须有 RETURN 语句
END function_name;
/

示例： 创建一个根据部门 ID 返回部门名称的函数。

CREATE OR REPLACE FUNCTION get_department_name (
    p_dept_id IN NUMBER
) RETURN VARCHAR2
IS
    v_dept_name departments.department_name%TYPE;
BEGIN
    SELECT department_name
    INTO v_dept_name
    FROM departments
    WHERE department_id = p_dept_id;
    RETURN v_dept_name;
EXCEPTION
    WHEN NO_DATA_FOUND THEN
        RETURN '部门不存在';
END get_department_name;
/

如何调用：

-- 在 SQL 语句中调用函数
SELECT get_department_name(10) AS department_name FROM DUAL;
-- 在 PL/SQL 中调用
DECLARE
    v_name VARCHAR2(50);
BEGIN
    v_name := get_department_name(20);
    DBMS_OUTPUT.PUT_LINE('部门名称是: ' || v_name);
END;
/

在 SQL 语句中使用绑定变量

绑定变量是 Oracle SQL 的精髓之一，它让 SQL 语句可以重复执行，只需改变绑定变量的值,从而极大地提升性能。

语法： 在 SQL 字符串中，用 后跟一个变量名作为占位符。

A. 在 PL/SQL 中使用

这是在 PL/SQL 代码中执行动态 SQL的标准方式。

示例： 使用 EXECUTE IMMEDIATE 动态执行 SQL。

DECLARE
    v_sql VARCHAR2(1000);
    v_emp_id NUMBER := 102; -- 输入参数
    v_emp_name VARCHAR2(100);
BEGIN
    -- 1. 定义包含绑定变量的 SQL 语句
    v_sql := 'SELECT first_name FROM employees WHERE employee_id = :emp_id';
    -- 2. 使用 EXECUTE IMMEDIATE 执行，并通过 USING 子句传递值
    --    INTO 子句用于接收查询结果
    EXECUTE IMMEDIATE v_sql
    INTO v_emp_name
    USING v_emp_id;
    DBMS_OUTPUT.PUT_LINE('员工姓名: ' || v_emp_name);
END;
/

B. 在应用程序中使用 (Java, Python, JDBC 等)

这是最常见、最高效的用法，应用程序通过 JDBC、ODP.NET 或其他驱动程序向数据库发送 SQL 语句和参数值。

Java (JDBC) 示例：

// 1. 定义带有 ? 占位符的 SQL 语句
String sql = "SELECT salary FROM employees WHERE employee_id = ?";
// 2. 创建 PreparedStatement
PreparedStatement pstmt = connection.prepareStatement(sql);
// 3. 设置参数 (索引从1开始)
int empId = 101;
pstmt.setInt(1, empId); // 将 empId 的值绑定到第一个 ? 占位符
// 4. 执行查询
ResultSet rs = pstmt.executeQuery();
if (rs.next()) {
    double salary = rs.getDouble("salary");
    System.out.println("员工薪资: " + salary);
}
// 5. 关闭资源
rs.close();
pstmt.close();

Python (cx_Oracle) 示例：

import cx_Oracle
# 1. 定义带有 :name 占位符的 SQL 语句
sql = "SELECT first_name FROM employees WHERE employee_id = :emp_id"
# 2. 创建字典来绑定变量
emp_id_to_find = 101
bind_vars = {'emp_id': emp_id_to_find}
# 3. 执行查询
cursor.execute(sql, bind_vars) # cx_Oracle 自动处理绑定
# 4. 获取结果
result = cursor.fetchone()
if result:
    print("员工姓名:", result[0])
cursor.close()

最佳实践：为什么使用输入参数？

使用输入参数（无论是 PL/SQL 参数还是绑定变量）是 Oracle 数据库开发的黄金法则,主要有三大好处：

1. 性能优化 (共享游标)

   • 不使用绑定变量：SELECT * FROM emp WHERE id = 101 和 SELECT * FROM emp WHERE id = 102 会被 Oracle 认为是两条完全不同的 SQL 语句，需要分别进行硬解析，生成两个执行计划,这非常消耗资源。
   • 使用绑定变量：SELECT * FROM emp WHERE id = :id 这条 SQL 语句只会被解析一次，生成一个通用的执行计划，之后，无论传入 101、102 还是其他任何值，数据库都会复用这个已解析的游标，只需进行软解析，速度极快,这对于高并发的应用至关重要。

2. 安全性 (防止 SQL 注入)

   • 不使用绑定变量：如果将用户输入直接拼接到 SQL 字符串中，会产生巨大的安全风险。
     • 坏例子：sql = "SELECT * FROM users WHERE username = '" + userInput + "'";
     • userInput 是 ' OR '1'='1，SQL 就变成了 SELECT * FROM users WHERE username = '' OR '1'='1',可能导致整个用户表被泄露。
   • 使用绑定变量：数据库驱动程序会将对参数的处理与 SQL 语句本身分开，传入的值会被当作纯数据处理，而不会被解释为 SQL 代码的一部分，从而从根本上杜绝了 SQL 注入。

3. 代码可维护性和灵活性

   • PL/SQL 参数：将业务逻辑封装在数据库中，使应用程序代码更简洁，调用一个 update_order_status 过程比在应用中写一长串 UPDATE 语句要清晰得多。
   • 绑定变量：使得同一份代码可以处理不同的数据值，无需为每种情况都写一个硬编码的 SQL 语句,大大提高了代码的复用性和灵活性。

在任何 Oracle 数据库开发中，都应该有意识地使用输入参数，无论是编写存储过程，还是在应用程序中执行 SQL 查询，这是写出高性能、高安全性、易维护代码的基础。