一、任务提出
JAVA操作oracle11g存储过程实验需要完成以下几个实例:
1.调用没有返回参数的过程(插入记录、更新记录)
2.有返回参数的过程
3.返回列表的过程
4.返回带分页的列表的过程。
二、建立表和相应的存储过程
create table student (sno int ,sname varchar2(20),sage int);
--创建存储过程testa1
create or replace procedure testa1(para1 in int,para2 in varchar2,para3 in int)
is
begin
insert into student(sno,sname,sage) values(para1,para2,para3);
commit;
end;
/
--创建存储过程testa2
create or replace procedure testa2(para1 in int,para2 in int)
is
begin
update student set sage=para2 where sno=para1;
commit;
end;
/
--创建有返回参数的存储过程testb
create or replace procedure testb(para1 in int ,para2 out varchar2,para3 out int)
is
begin
select sname,sage into para2,para3 from student where sno=para1;
end;
--创建返回集合的存储过程:
--在oracle中,如果要返回集合必须是返回游标,不能是一张二维表。所以,要先建立包。
create or replace package testpack
is
type test_cursor is ref cursor;
end testpack;
/
create or replace procedure testc(p_cursor out testpack.test_cursor)
is
begin
open p_cursor for select * from student;
end;
/
--实现分页的存储过程
---ps 每页几个记录,cs第几页
create or replace procedure testd(ps int ,cs int ,p_cursor out testpack.test_cursor)
is
begin
open p_cursor for
select * from (select student.*,rownum rn from student) where rn>ps*(cs-1) and rn<=ps*cs;
end;
/
三、java操作调用上述存储过程
package com.zhwy;
import java.sql.*;
public class Test {
String driver = "oracle.jdbc.driver.OracleDriver";
String strUrl = "jdbc:oracle:thin:@localhost:1521:orcl";
ResultSet rs = null;
Connection conn = null;
CallableStatement cstmt = null;
public static void main(String[] args) {
new Test().testPageSet(3, 1);
}
/**
* 没有返回参数的存储过程
* @param inputeSno
* @param inputSage
*/
public void testNoOutParameterUpdate(int inputeSno, int inputSage) {
try {
Class.forName(driver);
conn = DriverManager.getConnection(strUrl, "scott", "scott");
cstmt = conn.prepareCall("{ call scott.testa2(?,?)}");
cstmt.setInt(1, inputeSno);
cstmt.setInt(2, inputSage);
cstmt.execute();
System.out.println("执行成功!");
} catch (SQLException ex) {
ex.printStackTrace();
} catch (Exception ex) {
ex.printStackTrace();
} finally {
try {
if (cstmt != null)
cstmt.close();
if (conn != null) {
conn.close();
conn = null;
}
} catch (SQLException ex) {
ex.printStackTrace();
}
}
}
/**
* 没有返回参数的存储过程
* @param inputeSno
* @param inputSage
*/
public void testNoOutParameterInsert(int a, String b, int c) {
try {
Class.forName(driver);
conn = DriverManager.getConnection(strUrl, "scott", "scott");
cstmt = conn.prepareCall("{ call scott.testa1(?,?,?)}");
cstmt.setInt(1, a);
cstmt.setString(2, b);
cstmt.setInt(3, c);
cstmt.execute();
} catch (SQLException ex) {
ex.printStackTrace();
} catch (Exception ex) {
ex.printStackTrace();
} finally {
try {
if (cstmt != null)
cstmt.close();
if (conn != null) {
conn.close();
conn = null;
}
} catch (SQLException ex) {
ex.printStackTrace();
}
}
}
/**
* 有返回参数的存储过程
* @param inputeSno
* @param inputSage
*/
public void testOutParameter(int inputSno) {
try {
Class.forName(driver);
conn = DriverManager.getConnection(strUrl, "scott", "scott");
cstmt = conn.prepareCall("{ call scott.testb(?,?,?)}");
cstmt.setInt(1, inputSno);
cstmt.registerOutParameter(2, Types.VARCHAR);
cstmt.registerOutParameter(3, Types.INTEGER);
cstmt.execute();
String name = cstmt.getString(2);
int age = cstmt.getInt(3);
System.out.print("学号是:" + inputSno + "的学生的名字是:" + name + ",年龄是:" + age);
} catch (SQLException ex) {
ex.printStackTrace();
} catch (Exception ex) {
ex.printStackTrace();
} finally {
try {
if (cstmt != null)
cstmt.close();
if (conn != null) {
conn.close();
conn = null;
}
} catch (SQLException ex) {
ex.printStackTrace();
}
}
}
/**
* 返回列表的存储过程
* @param inputeSno
* @param inputSage
*/
public void testOutResult() {
try {
Class.forName(driver);
conn = DriverManager.getConnection(strUrl, "scott", "scott");
cstmt = conn.prepareCall("{ call scott.testc(?)}");
cstmt.registerOutParameter(1, oracle.jdbc.OracleTypes.CURSOR);// 指定是oracle里规定的类型
cstmt.execute();
rs = (ResultSet) cstmt.getObject(1);
while (rs.next()) {
System.out.print("学号是:" + rs.getInt(1) + "的学生的名字是:" + rs.getString(2) + ",年龄是:" + rs.getInt(3) + "\r\n");
}
} catch (SQLException ex) {
ex.printStackTrace();
} catch (Exception ex) {
ex.printStackTrace();
} finally {
try {
if (cstmt != null)
cstmt.close();
if (conn != null) {
conn.close();
conn = null;
}
} catch (SQLException ex) {
ex.printStackTrace();
}
}
}
/**
* 分页返回列表的存储过程
* @param inputeSno
* @param inputSage
*/
public void testPageSet(int recordPerPage, int currentPage) {
try {
Class.forName(driver);
conn = DriverManager.getConnection(strUrl, "scott", "scott");
cstmt = conn.prepareCall("{ call scott.testd(?,?,?)}");
cstmt.registerOutParameter(3, oracle.jdbc.OracleTypes.CURSOR);// 指定是oracle里规定的类型
cstmt.setInt(1, recordPerPage);
cstmt.setInt(2, currentPage);
cstmt.execute();
rs = (ResultSet) cstmt.getObject(3);
while (rs.next()) {
System.out.print("学号是:" + rs.getInt(1) + "的学生的名字是:" + rs.getString(2) + ",年龄是:" + rs.getInt(3) + "\r\n");
}
} catch (SQLException ex) {
ex.printStackTrace();
} catch (Exception ex) {
ex.printStackTrace();
} finally {
try {
if (cstmt != null)
cstmt.close();
if (conn != null) {
conn.close();
conn = null;
}
} catch (SQLException ex) {
ex.printStackTrace();
}
}
}
}
没有高深的知识,没有进阶的技巧,万丈高楼平地起~!