程序師世界是廣大編程愛好者互助、分享、學習的平台,程序師世界有你更精彩!
首頁
編程語言
C語言|JAVA編程
Python編程
網頁編程
ASP編程|PHP編程
JSP編程
數據庫知識
MYSQL數據庫|SqlServer數據庫
Oracle數據庫|DB2數據庫
 程式師世界 >> 數據庫知識 >> Oracle數據庫 >> Oracle數據庫基礎 >> oracle存儲過程創建與jdbc調用

oracle存儲過程創建與jdbc調用

編輯:Oracle數據庫基礎
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(); }
  1. 上一頁:
  2. 下一頁:
Copyright © 程式師世界 All Rights Reserved