存储过程:
- 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();