jdbc调用数据库(函数,存储过程,包)

存储过程:
create or replace procedure inandoutpro(a in out number,b in out number) as
begin
a:=a+b;
b:=a-b;
a:=a-b;
end inandoutpro;

java类调用存储过程:
Class.forName("oracle.jdbc.driver.OracleDriver");
Connection conn = DriverManager.getConnection(
"jdbc:oracle:thin:@localhost:1521:orcl", "scott", "ronaldo");
String procedure = "{call inandoutpro(?,?)}";
CallableStatement cs = conn.prepareCall(procedure);
cs.setInt(1, 5);
cs.setInt(2, 7);
cs.registerOutParameter(1, java.sql.Types.INTEGER);
cs.registerOutParameter(2, java.sql.Types.INTEGER);

cs.executeUpdate();

int a = cs.getInt(1);
int b = cs.getInt(2);
System.out.println(a + "==" + b);


函数(行转列):
create or replace function rowToCol(QuerySql varchar2) return varchar2 as
begin
declare type cu_rtoc is ref cursor;
proscu cu_rtoc;
string_name varchar2(200);
row_cu product%rowtype;
begin
open proscu for QuerySql;
loop
fetch proscu into row_cu;
exit when proscu%notfound;
string_name:=string_name||row_cu.pname||',';
end loop;
return rtrim(string_name,',');--去掉最后的逗号
end;
end rowToCol;


java类调用函数:
Class.forName("oracle.jdbc.driver.OracleDriver");
Connection conn = DriverManager.getConnection(
"jdbc:oracle:thin:@localhost:1521:orcl", "scott", "ronaldo");
String procedure = "{?=call rowToCol(?)}";
CallableStatement cs = conn.prepareCall(procedure);
cs.registerOutParameter(1, Types.VARCHAR);// 获得返回的字符串
String sql = "select * from product";
cs.setString(2, sql);
cs.executeUpdate();
String names = cs.getString(1);
System.out.println(names);
conn.close();


包(游标):
--package包(接口)
create or replace package pro_package as
type cu_ref is ref cursor;
procedure pro_product(cur_ref out cu_ref);
end pro_package;

--实现包(实现类)
create or replace package body pro_package as
procedure pro_product(cur_ref out cu_ref) is
begin
open cur_ref for 'select * from product';
end pro_product;
end pro_package;


java类调用包里的过程返回游标:
Class.forName("oracle.jdbc.driver.OracleDriver");
Connection conn = DriverManager.getConnection(
"jdbc:oracle:thin:@localhost:1521:orcl", "scott", "ronaldo");
String callsql = "{call pro_package.pro_product(?)}";
OracleCallableStatement cs = (OracleCallableStatement) conn
.prepareCall(callsql);
cs.registerOutParameter(1, oracle.jdbc.OracleTypes.CURSOR);
cs.executeUpdate();
ResultSet rs = null;
rs = cs.getCursor(1);
while (rs.next()) {
System.out.println(rs.getString(1) + "==" + rs.getString(2) + "=="
+ rs.getString(3));
}
rs.close();
conn.close();
评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值