oracle储存过程

### Oracle存储过程详解：操作与应用实例 #### 存储过程概述 在Oracle数据库中，存储过程是一种预先编译并存储在数据库中的SQL程序块，它可以在数据库服务器上执行复杂的数据处理逻辑，如数据的增删改查、复杂的业务规则处理等。存储过程可以接受输入参数、返回输出参数，并且能够包含控制结构，如循环、条件判断等，从而实现更高级的数据处理功能。 #### 示例解析：员工解雇存储过程 以下是一个关于解雇员工的存储过程示例： ```sql CREATE OR REPLACE PROCEDURE FIRE_EMPLOYEE(p_empno IN NUMBER) AS v_ename EMP.ENAME%TYPE; BEGIN SELECT ename INTO v_ename FROM emp WHERE empno = p_empno; INSERT INTO FORMER_EMP(EMPNO, ENAME) VALUES (p_empno, v_ename); DELETE FROM emp WHERE empno = p_empno; UPDATE former_emp SET date_deleted = SYSDATE WHERE empno = p_empno; EXCEPTION WHEN NO_DATA_FOUND THEN DBMS_OUTPUT.PUT_LINE('Employee Number Not Found!'); END; ``` 这个存储过程实现了将指定员工从`emp`表中删除，并将其信息（包括编号和姓名）存入`FORMER_EMP`表中，同时记录该员工被解雇的时间。如果指定的员工号不存在于`emp`表中，则会抛出异常并提示“Employee Number Not Found!”。 #### 学生信息添加存储过程 接下来，我们看一个关于学生信息添加的存储过程： ```sql CREATE OR REPLACE PROCEDURE sp_add_stu ( p_stu_id t_stu.s_id%TYPE, p_s_name t_stu.s_name%TYPE, p_c_id t_class.c_id%TYPE ) AS num NUMBER; new_name t_stu.s_name%TYPE; BEGIN new_name := substr(trim(p_s_name), 1, 8); SELECT COUNT(*) INTO num FROM t_class c WHERE c.c_id = p_c_id; IF (num = 0) THEN INSERT INTO t_class(c_id, c_name) VALUES (p_c_id, 'New Class'); END IF; INSERT INTO t_stu(s_id, s_name, c_id, s_sex) VALUES (p_stu_id, new_name, p_c_id, '1'); COMMIT; DBMS_OUTPUT.PUT_LINE('执行成功'); END; ``` 此存储过程首先检查所给班级是否存在，如果不存在则创建一个新的班级。然后将学生信息插入到`t_stu`表中，其中学生的姓名会被截取前8个字符，性别默认设为“1”。提交事务并输出执行成功的消息。 #### 存储过程的调用与参数传递 存储过程可以通过多种方式调用，包括直接通过SQL语句或者通过编程语言（如Java）的JDBC接口。例如，在SQL中，可以使用`CALL`语句来调用存储过程： ```sql SQL> CALL sp_add_stu(800, '小明', 88); ``` 而在Java中，可以使用`CallableStatement`对象来调用存储过程： ```java Connection conn = null; CallableStatement cstmt = null; conn = DriverManager.getConnection(url, user, password); String procedure = "{call sp_add_stu(?, ?, ?)}"; cstmt = conn.prepareCall(procedure); cstmt.setString(1, "2000"); cstmt.setString(2, "小红"); cstmt.setString(3, "40"); cstmt.executeUpdate(); ``` 以上示例展示了如何在Java中调用一个带参数的存储过程。 #### 输出参数与游标的应用 存储过程还可以返回输出参数，这通常用于向调用者提供存储过程执行的结果。例如： ```sql CREATE OR REPLACE PROCEDURE sp_value( id1 IN NUMBER, id2 OUT NUMBER ) AS BEGIN id2 := id1 * 200; END; ``` 在Java中，可以通过`registerOutParameter`方法注册输出参数： ```java cstmt.registerOutParameter(2, java.sql.Types.INTEGER); cstmt.executeUpdate(); int value = cstmt.getInt(2); System.out.println("结果：" + value); ``` 此外，存储过程可以使用游标来返回查询结果集，这对于返回多行数据非常有用。例如： ```sql CREATE OR REPLACE PACKAGE TEST_PACKAGE AS TYPE Test_CURSOR IS REF CURSOR; END; CREATE OR REPLACE PROCEDURE sp_select_stu(p_c_id t_class.c_id%TYPE) AS cur_test TEST_PACKAGE.Test_CURSOR; BEGIN OPEN cur_test FOR SELECT * FROM t_stu WHERE c_id = p_c_id; END; ``` 在这个例子中，`sp_select_stu`存储过程使用了游标`cur_test`来返回满足条件的所有学生信息。 Oracle存储过程是数据库中一种强大的工具，它可以实现复杂的数据操作和业务逻辑处理。通过合理设计和使用存储过程，可以提高应用程序的性能、安全性和可维护性。