1、只有输入参数而没有返回结果的存储过程。
sql:
1 create or replace procedure prc_1(deptno in number,dname in varchar2,loc in varchar2)
2 is
3 begin
4 insert into dept values(deptno,dname,loc)
5 end prc_1
java:
1 static void test1(){
2 Connection conn=null
3 CallableStatement csmt=null
4 try {
5 conn=JDBCUtils.getConnection()
6 conn.setAutoCommit(false)
7 csmt=conn.prepareCall("call prc_1(?,?,?)")
8 csmt.setInt(1,80)
9 csmt.setString(2,"ioc")
10 csmt.setString(3,"fhp")
11 csmt.execute()
12 conn.commit()
13 System.out.println("success insert data")
14 } catch (SQLException e) {
15 e.printStackTrace()
16 }
17 }
2、有输入参数且有一个返回值的存储过程。
sql:
1 create or replace procedure prc_2(p_deptno in number,p_loc out varchar2) is
2 begin
3 select loc into p_loc from dept where deptno=p_deptno
4 end prc_2
java:
1 static void test2(){
2 Connection conn=null
3 CallableStatement csmt=null
4 try {
5 conn=JDBCUtils.getConnection()
6 conn.setAutoCommit(false)
7 csmt=conn.prepareCall("call prc_2(?,?)")
8 csmt.setInt(1,70)
9 csmt.registerOutParameter(2,Types.VARCHAR)
10 csmt.execute()
11 conn.commit()
12 System.out.println("MIS位置:"+csmt.getString(2))
13 } catch (SQLException e) {
14 e.printStackTrace()
15 }
16 }
3、返回多行记录(游标)的存储过程。
sql:
首先要建立一个返回游标,以便接收返回结果。
1 create or replace package testpackage is
2 type test_cursor is ref cursor
3 end testpackage
4
5 create or replace procedure prc_3(p_cursor out testpackage.test_cursor)is
6 begin
7 open p_cursor for
8 select * from dept order by deptno
9 end prc_3
java:
1 static void test3(){
2 Connection conn=null
3 CallableStatement csmt=null
4 ResultSet rs=null
5 try {
6 conn=JDBCUtils.getConnection()
7 conn.setAutoCommit(false)
8 csmt=conn.prepareCall("call prc_3(?)")
9 csmt.registerOutParameter(1,oracle.jdbc.OracleTypes.CURSOR)
10 csmt.execute()
11 rs=(ResultSet) csmt.getObject(1)
12 while(rs.next()){
13 System.out.println(rs.getString("deptno")+'\t'+rs.getString("dname")+'\t'+rs.getString("loc"))
14 }
15 } catch (SQLException e) {
16 // TODO Auto-generated catch block
17 e.printStackTrace()
18 }finally{
19 JDBCUtils.free(rs, csmt, conn)
20 }
21 }
方法如下:importjava.sql.Connectionimportjava.sql.DriverManagerimportjava.sql.ResultSetimportjava.sql.SQLExceptionimportjava.sql.Statement/***LoadJDBCDriver*最基本的方法通过JDBC连接数据库*@authorJacob**/publicclassLoadByPrimary{publicstaticvoidmain(String[]args)throwsClassNotFoundException{Stringdriver="oracle.jdbc.OracleDriver"Connectioncn=null/***Class.forName手动加载一个类到方法区,Driver类中包含自动注册驱动的静态代码块*会自动在DriverManager中注册驱动*/Class.forName(driver)Stringurl="jdbc:oracle:thin:@localhost:1521:ORACLE"//1521代表端口号,默认的Stringuser="用户名"Stringpwd="密码"try{/**Connection是接口,返回值是一个引用对象,是Oracle驱动提供实现类ojdbc7.jar*使用JDBCAPI接口,实际上是驱动实现类*/cn=DriverManager.getConnection(url,user,pwd)Statementstmt=cn.createStatement()Stringsql="SELECT*FROMstu_empWHEREdeptno=10"ResultSetrs=stmt.executeQuery(sql)while(rs.next()){System.out.println(rs.getInt("empno")+""+rs.getString("ename")+""+rs.getString("job"))}rs.close()stmt.close()}catch(SQLExceptione){e.printStackTrace()}finally{try{if(cn!=null){cn.close()}}catch(SQLExceptione2){e2.printStackTrace()}}}}这是通过preparedstatement实现更新数据,这里我把连接数据库的方法进行了封装,每次直接调用了。publicstaticvoidmain(String[]args){PreparedStatementDemopsd=newPreparedStatementDemo()psd.updateSalary("JACOB",3000)psd.selectSalary("JACOB")}publicvoidupdateSalary(Stringename,doublesal){Stringsql="Updatestu_empsetsal=?Whereename=?"Connectioncn=nullPreparedStatementps=nulltry{cn=DBPUtil.getConnection()ps=cn.prepareStatement(sql)ps.setDouble(1,sal)ps.setString(2,ename)intnum=ps.executeUpdate()System.out.println("提示:总共有"+num+"条数据已经更新!")}catch(SQLExceptione){e.printStackTrace()}finally{DBUtil.stmtClose(ps)DBUtil.connClose(cn)}}publicvoidselectSalary(Stringname){Stringsql="Select*Fromstu_empWhereename=?"Connectioncn=nullPreparedStatementps=nullResultSetrs=nulltry{cn=DBPUtil.getConnection()ps=cn.prepareStatement(sql)ps.setString(1,name)rs=ps.executeQuery()while(rs.next()){System.out.println(rs.getString("ename")+"的工资是:"+rs.getInt("sal"))}}catch(SQLExceptione){e.printStackTrace()}finally{DBUtil.stmtClose(ps)DBUtil.rsClose(rs)DBUtil.connClose(cn)}}}