存储过程:
java类调用存储过程:
函数(行转列):
java类调用函数:
包(游标):
java类调用包里的过程返回游标:
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();