Oracle存储过程创建及调用 java调用oracle存储过程的示例总结

为什么80%的码农都做不了架构师？>>>   

在大型数据库系统中，有两个很重要作用的功能，那就是存储过程和触发器。在数据库系统中无论是存储过程还是触发器，都是通过SQL 语句和控制流程语句的集合来完成的。相对来说，数据库系统中的触发器也是一种存储过程。存储过程在数据库中运算时自动生成各种执行方式，因此，大大提高了对其运行时的执行速度。在大型数据库系统如Oracle、SQL Server中都不仅提供了用户自定义存储过程的功能，同时也提供了许多可作为工具进行调用的系统自带存储过程。 所谓存储过程(Stored Procedure)，就是一组用于完成特定数据库功能的SQL 语句集，该SQL语句集经过编译后存储在数据库系统中。在使用时候，用户通过指定已经定义的存储过程名字并给出相应的存储过程参数来调用并执行它，从而完成一个或一系列的数据库操作。 由于J2EE体系一般建立大型的企业级应用系统，而一般都配备大型数据库系统如Oracle或者SQL Server，在本文《JAVA与Oracle存储过程》中将介绍JAVA跟Oracle存储过程之间的相互应用跟相互间的各种调用。 一、JAVA调用Oracle存储过程 JAVA跟Oracle之间最常用的是JAVA调用Oracle的存储过程，以下简要说明下JAVA如何对Oracle存储过程进行调用。 Ⅰ、不带输出参数情况 过程名称为pro1，参数个数1个，数据类型为整形数据 import java.sql. * ; public class ProcedureNoArgs { public static void main(String args[]) throws Exception { // 加载Oracle驱动 DriverManager.registerDriver( new oracle.jdbc.driver.OracleDriver()); // 获得Oracle数据库连接 Connection conn = DriverManager.getConnection( " jdbc:oracle:thin:@MyDbComputerNameOrIP:1521:ORCL", sUsr, sPwd " ); // 创建存储过程的对象 CallableStatement c = conn.divpareCall( " {call pro1(?)} " ); // 给Oracle存储过程的参数设置值 ，将第一个参数的值设置成188 c.setInt( 1 , 188 ); // 执行Oracle存储过程 c.execute(); conn.close(); } } Ⅱ、带输出参数的情况 过程名称为pro2，参数个数2个，数据类型为整形数据，返回值为整形类型 import java.sql.*; public class ProcedureWithArgs { public static void main(String args[]) throws Exception { //加载Oracle驱动 DriverManager.registerDriver(new oracle.jdbc.driver.OracleDriver()); //获得Oracle数据库连接 Connection conn = DriverManager.getConnection("jdbc:oracle:thin:@MyDbComputerNameOrIP:1521:ORCL", sUsr, sPwd "); //创建Oracle存储过程的对象，调用存储过程 CallableStatement c=conn.divpareCall("{call pro2(?,?)}"); //给Oracle存储过程的参数设置值 ，将第一个参数的值设置成188 c.setInt(1,188); //注册存储过程的第二个参数 c.registerOutParameter(2,java.sql.Types.INTEGER); //执行Oracle存储过程 c.execute(); //得到存储过程的输出参数值并打印出来 System.out.println (c.getInt(2)); conn.close(); } } Oracle存储过程包含三部分：过程声明，执行过程部分，存储过程异常。 Oracle存储过程可以有无参数存储过程和带参数存储过程。 一、无参程序过程语法 1 create or replace procedure NoParPro 2 as ; 3 begin 4 ; 5 exception //存储过程异常 6 ; 7 end; 8 二、带参存储过程实例 1 create or replace procedure queryempname(sfindno emp.empno%type) as 2 sName emp.ename%type; 3 sjob emp.job%type; 4 begin 5 .... 7 exception .... 14 end; 15 三、 带参数存储过程含赋值方式 1 create or replace procedure runbyparmeters (isal in emp.sal%type, sname out varchar,sjob in out varchar) 2 as icount number; 3 begin 4 select count(*) into icount from emp where sal>isal and job=sjob; 5 if icount=1 then 6 .... 9 else 10 .... 12 end if; 13 exception 14 when too_many_rows then 15 DBMS_OUTPUT.PUT_LINE('返回值多于1行'); 16 when others then 17 DBMS_OUTPUT.PUT_LINE('在RUNBYPARMETERS过程中出错！'); 18 end; 19 四、在Oracle中对存储过程的调用 过程调用方式一 1 declare 2 realsal emp.sal%type; 3 realname varchar(40); 4 realjob varchar(40); 5 begin //存储过程调用开始 6 realsal:=1100; 7 realname:=''; 8 realjob:='CLERK'; 9 runbyparmeters(realsal,realname,realjob); －－必须按顺序 10 DBMS_OUTPUT.PUT_LINE(REALNAME||' '||REALJOB); 11 END; //过程调用结束 12 过程调用方式二 1 declare 2 realsal emp.sal%type; 3 realname varchar(40); 4 realjob varchar(40); 5 begin //过程调用开始 6 realsal:=1100; 7 realname:=''; 8 realjob:='CLERK'; 9 runbyparmeters(sname=>realname,isal=>realsal,sjob=>realjob); －－指定值对应变量顺序可变 10 DBMS_OUTPUT.PUT_LINE(REALNAME||' '||REALJOB); 11 END; //过程调用结束 12 至此，有关ORACLE的基本存储过程以及对Oracle存储过程的调用方式介绍完毕。

 

 

 

 

学生在学习jdbc的时候，会问到怎么调用存储过程，现在将java调用oracle存储过程的示例总结如下。（关于调用sqlserver的存储过程将在下次进行小结请关注）

一：无返回值的存储过程

存储过程为：

? create or replace procedure adddept(deptno number,dname varchar2,loc varchar2)

? as

? begin

? insert into dept values(deptno,dname,loc);

? end;

然后呢，在java里调用时就用下面的代码：

? public class TestProcedure {

? Connection conn=null ;

? CallableStatement cstmt=null ;

? PreparedStatement pstmt=null ;

? String url="jdbc:oracle:thin:@localhost:1521:mydb";

? String driver="oracle.jdbc.driver.OracleDriver";

? String name="";

? public TestProcedure() {

? try {

? Class.forName(driver);

? conn=DriverManager.getConnection(url,"scott","tiger");

? cstmt=conn.prepareCall("{call adddept(?,?,?)}");

? cstmt.setInt(1,13);

? cstmt.setString(2,"间谍部2");

? cstmt.setString(3,"ningbo2");

 cstmt.executeUpdate();

?

? System.out.println("success");

? }

? catch (Exception e){e.printStackTrace();}

? finally{

? cstmt.close();

? conn.close();

? }

? }

? }

dept表为oracle数据库方案scott中的一个表

二：有返回值的存储过程（非列表）

存储过程为：

CREATE OR REPLACE PROCEDURE TESTB(PARA1 IN VARCHAR2,PARA2 OUT VARCHAR2) AS

BEGIN

SELECT INTO PARA2 FROM TESTTB WHERE I_ID= PARA1;

END TESTB;

在java里调用时就用下面的代码：

package com.hyq.src;

public class TestProcedureTWO {

public TestProcedureTWO() {

}

public static void main(String[] args ){

String driver = "oracle.jdbc.driver.OracleDriver";

String strUrl = "jdbc:oracle:thin:@127.0.0.1:1521:hyq";

Statement stmt = null;

ResultSet rs = null;

Connection conn = null;

try {

Class.forName(driver);

conn = DriverManager.getConnection(strUrl, " hyq ", " hyq ");

CallableStatement proc = null;

proc = conn.prepareCall("{ call HYQ.TESTB(?,?) }");

proc.setString(1, "100");

proc.registerOutParameter(2, Types.VARCHAR);

proc.execute();

String testPrint = proc.getString(2);

System.out.println("=testPrint=is="+testPrint);

}

catch (SQLException ex2) {

ex2.printStackTrace();

}

catch (Exception ex2) {

ex2.printStackTrace();

}

finally{

try {

if(rs != null){

rs.close();

if(stmt!=null){

stmt.close();

}

if(conn!=null){

conn.close();

}

}

}

catch (SQLException ex1) {

}

}

}

}

}

注意，这里的proc.getString(2)中的数值2并非任意的，而是和存储过程中的out列对应的，如果out是在第一个位置，那就是proc.getString(1)，如果是第三个位置，就是proc.getString(3)，当然也可以同时有多个返回值，那就是再多加几个out参数了。

三：返回列表

由于oracle存储过程没有返回值，它的所有返回值都是通过out参数来替代的，列表同样也不例外，但由于是集合，所以不能用一般的参数，必须要用pagkage了.所以要分两部分，

1， 建一个程序包。如下：

CREATE OR REPLACE PACKAGE TESTPACKAGE AS

TYPE Test_CURSOR IS REF CURSOR;

end TESTPACKAGE;

2，建立存储过程，存储过程为：

CREATE OR REPLACE PROCEDURE TESTC(p_CURSOR out TESTPACKAGE.Test_CURSOR) IS

BEGIN

OPEN p_CURSOR FOR SELECT * FROM HYQ.TESTTB;

END TESTC;

可以看到，它是把游标（可以理解为一个指针），作为一个out 参数来返回值的。

在java里调用时就用下面的代码：

package com.hyq.src;

import java.sql.*;

import java.io.OutputStream;

import java.io.Writer;

import java.sql.PreparedStatement;

import java.sql.ResultSet;

import oracle.jdbc.driver.*;

 

public class TestProcedureTHREE {

public TestProcedureTHREE() {

}

public static void main(String[] args ){

String driver = "oracle.jdbc.driver.OracleDriver";

String strUrl = "jdbc:oracle:thin:@127.0.0.1:1521:hyq";

Statement stmt = null;

ResultSet rs = null;

Connection conn = null;

try {

Class.forName(driver);

conn = DriverManager.getConnection(strUrl, "hyq", "hyq");

CallableStatement proc = null;

proc = conn.prepareCall("{ call hyq.testc(?) }");

proc.registerOutParameter(1,oracle.jdbc.OracleTypes.CURSOR);

proc.execute();

rs = (ResultSet)proc.getObject(1);

while(rs.next())

{

System.out.println("<tr><td>" + rs.getString(1) + "</td><td>"+rs.getString(2)+"</td></tr>");

}

}

catch (SQLException ex2) {

ex2.printStackTrace();

}

catch (Exception ex2) {

ex2.printStackTrace();

}

finally{

try {

if(rs != null){

rs.close();

if(stmt!=null){

stmt.close();

}

if(conn!=null){

conn.close();

}

}

}

catch (SQLException ex1) {

}

}

}

}

 

转载于:https://my.oschina.net/ldm95/blog/746109