Oracle存储过程及调用

好的！接下来我将详细介绍Oracle存储过程及调用的完整攻略，包括定义存储过程、调用存储过程以及两条示例说明。

定义存储过程

在Oracle中定义存储过程，主要有以下几个步骤：

1. 创建存储过程

使用CREATE PROCEDURE命令创建存储过程，格式如下：

CREATE PROCEDURE 存储过程名([IN/OUT] 参数名 数据类型)
IS
BEGIN
-- 存储过程代码
END;

其中，[]内的IN/OUT表示参数的传值方式，IN表示输入参数，OUT表示输出参数，可以省略。例如：

CREATE PROCEDURE hello_world
IS
BEGIN
DBMS_OUTPUT.PUT_LINE('Hello World!');
END;

2. 定义输入参数

在创建存储过程时，还可以定义输入参数，格式如下：

CREATE PROCEDURE 存储过程名(输入参数名 IN 数据类型)
IS
BEGIN
-- 存储过程代码
END;

例如：

CREATE PROCEDURE get_employee_info(ename IN VARCHAR2)
IS
BEGIN
SELECT * FROM employee WHERE ename = get_employee_info.ename;
END;

3. 定义输出参数

在创建存储过程时，还可以定义输出参数，格式如下：

CREATE PROCEDURE 存储过程名(输出参数名 OUT 数据类型)
IS
BEGIN
-- 存储过程代码
END;

例如：

CREATE PROCEDURE get_employee_count(count OUT INT)
IS
BEGIN
SELECT COUNT(*) INTO count FROM employee;
END;

4. 定义输入输出参数

在创建存储过程时，还可以定义输入输出参数，格式如下：

CREATE PROCEDURE 存储过程名(输入参数名 IN/OUT 数据类型)
IS
BEGIN
-- 存储过程代码
END;

例如：

CREATE PROCEDURE add_employee_salary(ename IN VARCHAR2, salary IN/OUT INT)
IS
BEGIN
UPDATE employee SET salary = salary + 1000 WHERE ename = add_employee_salary.ename;
SELECT salary INTO add_employee_salary.salary FROM employee WHERE ename = add_employee_salary.ename;
END;

调用存储过程

调用存储过程，主要有以下几个步骤：

1. 直接调用存储过程

可以使用EXECUTE命令或CALL命令来直接调用存储过程，格式如下：

EXECUTE 存储过程名;

或

CALL 存储过程名;

例如：

EXECUTE hello_world;

或

CALL hello_world;

2. 调用带输入参数的存储过程

调用带输入参数的存储过程，需要为输入参数赋值，格式如下：

EXECUTE 存储过程名(参数值);

或

CALL 存储过程名(参数值);

例如：

EXECUTE get_employee_info('张三');

或

CALL get_employee_info('张三');

3. 调用带输出参数的存储过程

调用带输出参数的存储过程，需要使用变量来接收输出参数的值，格式如下：

DECLARE
变量名 数据类型;
BEGIN
存储过程名(变量名);
END;

例如：

DECLARE
count INT;
BEGIN
get_employee_count(count);
DBMS_OUTPUT.PUT_LINE('员工总数为：'|| count);
END;

4. 调用带输入输出参数的存储过程

调用带输入输出参数的存储过程，需要为输入参数赋值，并使用变量来接收输出参数的值，格式如下：

DECLARE
变量名 输入参数数据类型;
变量名 输出参数数据类型;
BEGIN
存储过程名(输入参数名 => 输入参数值, 输出参数名 => 输出参数变量);
END;

例如：

DECLARE
ename VARCHAR2(20) := '张三';
salary INT;
BEGIN
add_employee_salary(ename => ename, salary => salary);
DBMS_OUTPUT.PUT_LINE('员工'||ename||'的工资为：'|| salary);
END;

示例说明

下面来看两个示例说明。

示例1

需求：创建一个存储过程，统计某个部门下的员工数。

CREATE PROCEDURE get_dept_emp_count(dname IN VARCHAR2, count OUT INT)
IS
BEGIN
SELECT COUNT(*) INTO count FROM employee WHERE dept_name = get_dept_emp_count.dname;
END;

调用该存储过程，并输出员工数：

DECLARE
count INT;
BEGIN
get_dept_emp_count('销售部', count);
DBMS_OUTPUT.PUT_LINE('销售部下的员工数为：'|| count);
END;

示例2

需求：创建一个存储过程，将某个部门下的员工工资全部加1千。

CREATE PROCEDURE add_dept_salary(dname IN VARCHAR2)
IS
BEGIN
UPDATE employee SET salary = salary + 1000 WHERE dept_name = add_dept_salary.dname;
COMMIT;
END;

调用该存储过程，并输出修改记录数：

DECLARE
cnt NUMBER;
BEGIN
add_dept_salary('销售部');
cnt := SQL%ROWCOUNT;
DBMS_OUTPUT.PUT_LINE('修改记录数为：'|| cnt);
END;

至此，该完整攻略讲解完毕。