Oracle procedures:
create or replace procedure zcccgc(name_out out PKG_UIREPORT_DEPTTRANSTABLE.MY_CURSOR,v_count out int,likes in nvarchar2, starts in int, limits in int) is
str varchar2(1000);
begin
insert into ttable ("name1", "name2", "name3") select "name11","name22","name3" from "table"; str:='select "name1", "name2", "name3" from ( select row_.*, rownum rownum_ from (SELECT * FROM ttable '||likes||') row_ where rownum <= '||limits||') where rownum_ > '||starts;
open name_out for str;
str:='SELECT count(*) FROM ttable '||likes;
execute immediate str into v_count;
end zcccgc; jdbc:
import Java.sql.CallableStatement;
import Java.sql.Connection;
import Java.sql.ResultSet;
import Java.sql.DriverManager; import oracle.jdbc.OracleTypes;
public class ProcedureTest {
// public static void main(String[] args) { // try { // String dbDriver = "oracle.jdbc.driver.OracleDriver";
// String dbURL = "jdbc:Oracle:thin:@localhost:1521:orcl";
// Class.forName(dbDriver);
// Connection conn = DriverManager.getConnection(dbURL, "hr", "zaylqn");
// conn.setAutoCommit(false);
// CallableStatement stmt = conn.prepareCall("{call Oracleccgc(?,?)}");
// stmt.registerOutParameter(1,OracleTypes.CURSOR);
// stmt.setString(2, "1");
// stmt.execute();
// ResultSet rs =(ResultSet)stmt.getObject(1);
// while(rs.next()){
// System.out.println(rs.getString(1));
// System.out.println(rs.getString(2));
// System.out.println("________________________________________________"); // } catch (Exception e) {
// // TODO Auto-generated catch block
// e.printStackTrace(); // }
public static void main(String[] args) { try { String dbDriver = "oracle.jdbc.driver.OracleDriver";
String dbURL = "jdbc:Oracle:thin:@localhost:1521:orcl";
Class.forName(dbDriver);
Connection conn = DriverManager.getConnection(dbURL, "hr", "zaylqn");
conn.setAutoCommit(false);
CallableStatement stmt = conn.prepareCall("{call zcccgc(?,?,?,?,?)}");
stmt.registerOutParameter(1,OracleTypes.CURSOR);
stmt.registerOutParameter(2, OracleTypes.INTEGER);
stmt.setString(3, " where lower(\"name2\") like lower ('%xx%')");
stmt.setString(4, "0");
stmt.setString(5, "30");
stmt.execute();
ResultSet rs =(ResultSet)stmt.getObject(1);
System.out.println(stmt.getInt(2));
while(rs.next()){
System.out.println(rs.getString(1));
System.out.println(rs.getString(2));
System.out.println("________________________________________________"); } catch (Exception e) {
// TODO Auto-generated catch block
e.printStackTrace(); }