下面是一個java連接配接Oracle 執行一個沒有傳回值的存儲過程的小例程。
package com.test;
import java.sql.*;
public class procedure {
public static void main(String[] args) {
Connection ct=null;
CallableStatement cs=null;
try {
Class.forName("oracle.jdbc.driver.OracleDriver");
ct=DriverManager.getConnection("jdbc:oracle:thin:@10.8.2.73:1521:orcl",
"scott","123456");
cs=ct.prepareCall("{call pro_book(?,?,?)}");
cs.setInt(1, 10001);
cs.setString(2, "華爾街之狼");
cs.setString(3, "中信出版社");
cs.execute();
}
catch (ClassNotFoundException e) {
e.printStackTrace();
}
catch (SQLException e) {
e.printStackTrace();
}
finally{
try {
cs.close();
ct.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
}
}
下面是java執行一個有傳回值的存儲過程的小程式。
package com.test;
import java.sql.*;
public class procedure {
public static void main(String[] args) {
Connection ct=null;
CallableStatement cs=null;
try {
Class.forName("oracle.jdbc.driver.OracleDriver");
ct=DriverManager.getConnection("jdbc:oracle:thin:@10.8.2.73:1521:orcl",
"scott","123456");
cs=ct.prepareCall("{call pro_book_getname(?,?)}");
cs.setInt(1, 10001);
cs.registerOutParameter(2, oracle.jdbc.OracleTypes.VARCHAR);
cs.execute();
String name=cs.getString(2);
System.out.println(name);
}
catch (ClassNotFoundException e) {
e.printStackTrace();
}
catch (SQLException e) {
e.printStackTrace();
}
finally{
try {
cs.close();
ct.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
}
}
一個取得一個集合的java程式:
首先先寫一個oracle包,定義一個cursor類型,PL/SQL如下:
create package pack_cursor is
type type_cursor is ref cursor;
end;
/
再寫一個存儲過程:
create or replace procedure pro_emp_cursor(no in number,cur out pack_cursor.type_cursor) is
begin
open cur for select * from emp where deptno=no;
end;
/
package com.test;
import java.sql.*;
public class procedure {
public static void main(String[] args) {
Connection ct=null;
CallableStatement cs=null;
try {
Class.forName("oracle.jdbc.driver.OracleDriver");
ct=DriverManager.getConnection("jdbc:oracle:thin:@10.8.2.73:1521:orcl",
"scott","123456");
cs=ct.prepareCall("{call pro_emp_cursor(?,?)}");
cs.setInt(1, 10);
cs.registerOutParameter(2, oracle.jdbc.OracleTypes.CURSOR);
cs.execute();
ResultSet rs=(ResultSet)cs.getObject(2);
while(rs.next())
{
System.out.println("EMPNO:"+rs.getInt(1)+" NAME:"+rs.getString(2)+" JOB:"+rs.getString(3));
}
}
catch (ClassNotFoundException e) {
e.printStackTrace();
}
catch (SQLException e) {
e.printStackTrace();
}
finally{
try {
cs.close();
ct.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
}
}
create or replace procedure pro_page(tablename in varchar2, --輸入表名
page_size in number, --每頁多少行
pagenow in number, -- 想要第幾行
row_num out number, --輸出總共的行數
pagenum out number, --總共多少行
cur out pack_cursor.type_cursor) --遊标
is
v_begin number(5):=(pagenow-1)*page_size+1; --起始的行數
v_end number(5):=pagenow*page_size; --結束的行數
v_sql varchar2(1000):='select * from
(select v1.*,rownum rn from (select * from '||tablename||') v1 where rownum<='||v_end||') v2 where rn>='||v_begin;--定義的select分頁語句
begin
select count(ename) into row_num from emp; --總行數
if mod(row_num,page_size)=0 then --計算總頁數
pagenum:=row_num/page_size;
else
pagenum:=row_num/page_size+1;
end if;
open cur for v_sql; --打開遊标
end;