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();
页:
[1]