Oracle存储过程及JDBC调用大全

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> call sp_add_stu(800,'李小龙',88);

 

Method called

 

SQL>

 

-----------------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<rmd.getColumnCount();i++){

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

}

System.out.println("");

}

rs.close();