---
title: "Oracle存储过程及JDBC调用大全"
author: "java1573"
date: "2009-06-09T00:48:49Z"
tags: []
slug: "oracle-jdbc"
draft: false
lastmod: "2009-06-09T00:48:49Z"
id: 404615
url: "https://java1573.iteye.com/blog/404615"
---

**Oracle存储过程及JDBC调用大全(原创)**

**-----------------无返回值示例------------------------**

**存储过程：**

**·添加学生,如果班级不存在,则先添加班级信息,再添加学生。**

**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 into*

   **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,'新班');

    **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**;

 **-----------------在Oracle中调用(无返回值)-----------------**

**SQL&gt; call sp\_add\_stu(800,'李小龙',88);**

 

**Method called**

 

**SQL&gt;**

 

**-----------------JDBC调用(无返回值)-----------------**

**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();**

**-----------------返回结果值示例------------------------**

**·指定参数为out类型即可返回值**

*/\*\**

*存储过程与函数的区别：存储过程可以增删改,可以提交或回滚,而函数不能。*

*函数只能计算*

 

*如处理某个单据(进货单),如输入用户帐号,经过复杂处理以后,*

*返回此用户的欠费单据号*

*\*/*

**create** **or** **replace** **procedure** sp\_value(

id1 **in** **number**,

id2 **out** **number**

)

**as**

**begin**

  id2 := id1\*200;

**end**;

 

**-----------------调用(有简单返回值)----------------**

**Connection conn = null;**

**CallableStatement cstmt = null;**

**conn = DriverManager.getConnection(url, user, password);**

**String procedure = "{call sp\_value(?,?)}";**

**cstmt = conn.prepareCall(procedure);**

**--------------------JDBC获取存储过程中的值-------------------**

**//学号、姓名、班级编号**

**cstmt.setString(1, "2000");**

**//注册输出参数**

**cstmt.registerOutParameter(2, java.sql.Types.INTEGER);**

**cstmt.executeUpdate();**

**//从输出参数中获取值**

**int value = cstmt.getInt(2);**

**System.out.println("返回： "+value);**

 

 

**-----------------返回结果集示例------------------------**

**·建包,包中定义游标类型变量**

**CREATE OR REPLACE PACKAGE TESTPACKAGE  AS**

    **TYPE Test\_CURSOR IS REF CURSOR; --自定义一个引用游标类型**

**end;**

 

**·存储过程返回指定游标类型变量**

 

**CREATE OR REPLACE PROCEDURE sp\_select\_stu**

**(**

**p\_c\_id t\_class.c\_id%type,**

**p\_cursor       out TESTPACKAGE.Test\_CURSOR**

**自定义变量名   输出  变量所对应的数据类型(在包中自定义的)**    

**) IS**

**BEGIN**

**OPEN p\_CURSOR FOR**

   **SELECT s.stu\_id,s.s\_name,to\_char(s.s\_birthday,'yy.mm') FROM t\_stu s**

   **where s.c\_id=p\_c\_id**

   **order by s.s\_name;**

**END;**

 

**-----------------JDBC获取存储过程中的结果集---------------**

**Connection conn = null;**

**CallableStatement cstmt = null;**

**conn = DriverManager.getConnection(url, user, password);**

**String procedure = "{call sp\_select\_stu(?,?)}";**

**cstmt = conn.prepareCall(procedure);**

**//学号、姓名、班级编号**

**cstmt.setString(1, "C01");**

**//注册输出参数**

**cstmt.registerOutParameter(2, oracle.jdbc.OracleTypes.CURSOR);**

**cstmt.execute();**

**//从输出参数中获取值**

**ResultSet rs = (ResultSet)cstmt.getObject(2);//第2个?所代表的参数**

 

**ResultSetMetaData rmd =rs.getMetaData();**

**while(rs.next()){**

**for(int i=0;i&lt;rmd.getColumnCount();i++){**

  **System.out.print(rs.getString(i+1)+" ");**

**}**

**System.out.println("");**

**}**

**rs.close();**