Oracle调用存储过程总结

### Oracle调用存储过程详解 在Oracle数据库管理与开发中，存储过程是一种非常重要的数据库对象，它可以被看作是一组SQL语句与控制流语句的集合，预先编译并存储于数据库中，用于实现特定的功能。通过调用存储过程，可以有效地提高应用程序的执行效率，并增强数据的一致性和安全性。本文将详细介绍如何在Oracle数据库中创建和调用存储过程，包括输入参数、输出参数以及游标类型的处理。 #### 创建存储过程 我们需要定义两个存储过程：`TESTA` 和 `TESTB`。 ##### TESTA 存储过程 该存储过程接受两个输入参数（`PARA1` 和 `PARA2`），并将它们插入到表 `HYQ.B_ID` 中。 ```sql CREATE OR REPLACE PROCEDURE TESTA(PARA1 IN VARCHAR2, PARA2 IN VARCHAR2) AS BEGIN INSERT INTO HYQ.B_ID (I_ID, I_NAME) VALUES (PARA1, PARA2); END TESTA; ``` 这里使用了`CREATE OR REPLACE`关键字来创建或替换存储过程，如果已存在同名存储过程，则会被新的定义所替换。`IN`表示参数是只读的输入参数。 ##### TESTB 存储过程 此存储过程接收一个输入参数 `PARA1` 并返回一个输出参数 `PARA2`，它从表 `TESTTB` 中根据输入的ID查询相应的名称。 ```sql CREATE OR REPLACE PROCEDURE TESTB(PARA1 IN VARCHAR2, PARA2 OUT VARCHAR2) AS BEGIN SELECT I_NAME INTO PARA2 FROM TESTTB WHERE I_ID = PARA1; END TESTB; ``` 同样地，`OUT` 表示该参数为输出参数，其值可以在存储过程内部进行修改，并在调用后返回给调用者。 #### 调用存储过程 接下来介绍如何使用Java语言来调用以上定义的存储过程。 ##### 输入参数调用 TESTA ```java public static void main(String[] args) { String driver = "oracle.jdbc.driver.OracleDriver"; String strUrl = "jdbc:oracle:thin:@127.0.0.1:1521:hyq"; Connection conn = null; CallableStatement cstmt = null; try { Class.forName(driver); conn = DriverManager.getConnection(strUrl, "hyq", "hyq"); cstmt = conn.prepareCall("{call HYQ.TESTA(?,?)}"); cstmt.setString(1, "100"); // 设置第一个输入参数 cstmt.setString(2, "TestOne"); // 设置第二个输入参数 cstmt.execute(); // 执行存储过程 } catch (Exception e) { e.printStackTrace(); } finally { if (cstmt != null) { cstmt.close(); } if (conn != null) { conn.close(); } } } ``` 这段代码中，我们通过`prepareCall`方法准备调用存储过程，并通过`setString`方法设置输入参数的值。 ##### 输出参数调用 TESTB ```java public static void main(String[] args) { String driver = "oracle.jdbc.driver.OracleDriver"; String strUrl = "jdbc:oracle:thin:@127.0.0.1:1521:hyq"; Connection conn = null; CallableStatement proc = null; try { Class.forName(driver); conn = DriverManager.getConnection(strUrl, "hyq", "hyq"); proc = conn.prepareCall("{call HYQ.TESTB(?,?)}"); proc.setString(1, "100"); // 设置输入参数 proc.registerOutParameter(2, Types.VARCHAR); // 注册输出参数类型 proc.execute(); String testPrint = proc.getString(2); // 获取输出参数的值 System.out.println("查询结果为: " + testPrint); } catch (Exception e) { e.printStackTrace(); } finally { if (proc != null) { proc.close(); } if (conn != null) { conn.close(); } } } ``` 在此例中，我们同样使用`prepareCall`方法准备调用，但需要注意的是，对于输出参数，我们使用`registerOutParameter`方法来注册输出参数类型，并在执行后通过`getString`方法获取输出参数的值。 #### 使用游标作为输出参数 除了基本的数据类型，Oracle存储过程还可以使用游标作为输出参数，这在需要返回多行记录时特别有用。 ##### 创建包含游标的存储过程 TESTC ```sql CREATE OR REPLACE PACKAGE TESTPACKAGE AS TYPE Test_CURSOR IS REF CURSOR; END TESTPACKAGE; CREATE OR REPLACE PROCEDURE TESTC(p_CURSOR OUT TESTPACKAGE.Test_CURSOR) IS BEGIN OPEN p_CURSOR FOR SELECT * FROM HYQ.TESTTB; END TESTC; ``` 这里定义了一个名为`TESTPACKAGE`的包，其中包含一个游标类型`Test_CURSOR`，并定义了一个名为`TESTC`的存储过程，该过程打开一个游标，返回`HYQ.TESTTB`表中的所有记录。 ##### 调用包含游标的存储过程 ```java public static void main(String[] args) { String driver = "oracle.jdbc.driver.OracleDriver"; String strUrl = "jdbc:oracle:thin:@127.0.0.1:1521:hyq"; Connection conn = null; CallableStatement proc = null; ResultSet rs = null; try { Class.forName(driver); conn = DriverManager.getConnection(strUrl, "hyq", "hyq"); proc = conn.prepareCall("{call hyq.testc(?)}"); proc.registerOutParameter(1, oracle.jdbc.OracleTypes.CURSOR); proc.execute(); rs = (ResultSet) proc.getObject(1); // 获取游标返回的结果集 while (rs.next()) { System.out.println("ID: " + rs.getString(1) + ", Name: " + rs.getString(2)); } } catch (Exception e) { e.printStackTrace(); } finally { if (rs != null) { rs.close(); } if (proc != null) { proc.close(); } if (conn != null) { conn.close(); } } } ``` 在调用过程中，我们使用`registerOutParameter`注册游标类型，并通过`getObject`方法获取返回的结果集。 ### 总结 通过上述示例可以看出，在Oracle中创建和调用存储过程是非常灵活且强大的。存储过程不仅可以接收和返回各种类型的数据，还可以使用复杂的逻辑处理，如游标等高级特性，极大地提高了应用程序的性能和维护性。在实际应用中，合理利用这些特性可以显著提升数据库操作的效率和程序的可维护性。