oracle 带参数的存储过程

什么是带参数的存储过程？

存储过程是一种存储在数据库中的预编译代码块。而带参数的存储过程指的是可以接受参数的存储过程。通过传递参数，可以让存储过程更加灵活和通用。

创建带参数的存储过程

在Oracle中，可以通过CREATE PROCEDURE命令来创建存储过程。下面是一个简单的带参数的存储过程：

CREATE PROCEDURE get_employee (employee_id NUMBER)

IS 
BEGIN 
   SELECT * FROM employees WHERE employee_id = id; 
END;

在上述代码中，get_employee是存储过程的名称，employee_id是传递给存储过程的参数。存储过程使用IS和END关键字之间的语句执行指定的任务。

存储过程的参数类型

在Oracle中，存储过程的参数可以是IN，OUT或IN OUT类型。

IN类型参数

IN类型参数是一种传递给存储过程的参数类型。它表示只能从外部传递值给存储过程。存储过程在执行时可以引用这些值，但不允许修改它们。

OUT类型参数

OUT类型参数允许存储过程将一个单一值或码流返回给调用程序。调用程序应该声明用于检索返回值的变量。

IN OUT类型参数

IN OUT类型参数允许存储过程将传入的值修改，并在存储过程执行完成后返回给调用程序。存储过程可以读取和写入这些值，并根据需要进行修改。

带参数的存储过程调用

通过调用存储过程名和传入参数的值，可以执行存储过程。下面是一个带参数的存储过程调用：

EXEC get_employee(100);

上述命令将运行名称为get_employee的存储过程，传递参数值100作为传递给存储过程的employee_id参数值。

带参数的存储过程示例

下面是一个更复杂的带参数的存储过程示例，可以检索符合特定要求的雇员列表：

CREATE PROCEDURE get_employees_by_salary (

   salary       IN NUMBER,
   commission   IN NUMBER,
   employees   OUT SYS_REFCURSOR
)
IS 
BEGIN 
   OPEN employees FOR
      SELECT * FROM employees
      WHERE salary = salary AND commission_pct = commission;
END;

在上面的存储过程示例中，get_employees_by_salary是存储过程的名称。在被调用时，传递3个参数：salary和commission作为输入参数，employees作为输出参数。

存储过程中的代码使用OPEN...FOR SELECT语句，以检索符合特定要求的雇员列表。在此示例中，此语句使用输入参数salary和commission进行筛选，然后将结果包含在输出参数employees中。

可以使用以下代码执行上述存储过程：

DECLARE 

   emp_list SYS_REFCURSOR;
BEGIN 
   get_employees_by_salary(5000, 0.2, emp_list);
END;

上述代码将运行存储过程get_employees_by_salary，将输入参数分别设置为5000和0.2，然后将结果读取到名为emp_list的SYS_REFCURSOR类型变量中。

总结

带参数的存储过程使得数据库的编程更加灵活，可以更容易地重用代码和修改数据访问方式。在Oracle中，可以使用CREATE PROCEDURE命令来创建存储过程，通过IN，OUT和IN OUT参数类型来定义传递给存储过程的参数。可以通过简单的EXEC命令调用存储过程，并传递所需的参数。带参数的存储过程还可以为调用程序返回结果，以使其更具可用性。