java存储过程.doc

一:Java如何实现对存储过程的调用: A:不带输出参数的 -不带输出参数的-create procedure getsumn int =0asdeclare sum intdeclare i intset sum=0set i=0while i=n beginset sum=sum+iset i=i+1endprint the sum is +ltrim(rtrim(str(sum)-在SQL中执行:- exec getsum 100-在JAVA中调用:- JAVA可以调用 但是在JAVA程序却不能去显示该存储过程的结果 因为上面的存储 过程的参数类型int 传递方式是in(按值)方式 import java.sql.*;public class ProcedureTest public static void main(String args) throws Exception /加载驱动 new sun.jdbc.odbc.JdbcOdbcDriver() 是sun提供的jdbc-odbc桥的驱动类。这个是类的名字。Class.forName(new sun.jdbc.odbc.JdbcOdbcDriver(); /获得连接 Connection conn=DriverManager.getConnection(jdbc:odbc:mydata,sa,); /创建存储过程的对象 CallableStatement c=conn.prepareCall(call getsum(?); /给存储过程的参数设置值 c.setInt(1,100); /将第一个参数的值设置成100 /执行存储过程 c.execute(); conn.close(); B:带输出参数的 1:返回int -带输出参数的-alter procedure getsumn int =0,result int outputasdeclare sum intdeclare i intset sum=0set i=0while i=n beginset sum=sum+iset i=i+1endset result=sum -在查询分析器中执行- declare myResult intexec getsum 100,myResult outputprint myResult -在JAVA中调用-import java.sql.*;public class ProcedureTest public static void main(String args) throws Exception /加载驱动 DriverManager.registerDriver(new sun.jdbc.odbc.JdbcOdbcDriver(); /获得连接 Connection conn=DriverManager.getConnection(jdbc:odbc:mydata,sa,); /创建存储过程的对象 CallableStatement c=conn.prepareCall(call getsum(?,?); /给存储过程的第一个参数设置值 c.setInt(1,100); /注册存储过程的第二个参数 c.registerOutParameter(2,java.sql.Types.INTEGER); /执行存储过程 c.execute(); /得到存储过程的输出参数值 System.out.println (c.getInt(2); conn.close(); 2:返回varchar -存储过程带游标-在存储过程中带游标 使用游标不停的遍历orderidcreate procedure CursorIntoProcedurepname varchar(8000) outputas-定义游标declare cur cursor for select orderid from orders-定义一个变量来接收游标的值declare v varchar(5)-打开游标open curset pname=-给pname初值-提取游标的值fetch next from cur into vwhile fetch_status=0 beginset pname=pname+;+v fetch next from cur into vendprint pname-关闭游标close cur-销毁游标deallocate cur -执行存储过程-exec CursorIntoProcedure -JAVA调用-import java.sql.*;public class ProcedureTest public static void main(String args) throws Exception /加载驱动 DriverManager.registerDriver(new sun.jdbc.odbc.JdbcOdbcDriver(); /获得连接 Connection conn=DriverManager.getConnection(jdbc:odbc:mydata,sa,); CallableStatement c=conn.prepareCall(call CursorIntoProcedure(?); c.registerOutParameter(1,java.sql.Types.VARCHAR); c.execute(); System.out.println (c.getString(1); conn.close(); C:删除数据的存储过程 -存储过程-drop table 学生基本信息表create table 学生基本信息表(StuID int primary key,StuName varchar(10),StuAddress varchar(20)insert into 学生基本信息表 values(1,三毛,wuhan)insert into 学生基本信息表 values(2,三毛,wuhan)create table 学生成绩表(StuID int,Chinese int,PyhSics intforeign key(StuID) references 学生基本信息表(StuID)on delete cascadeon update cascade) insert into 学生成绩表 values(1,99,100)insert into 学生成绩表 values(2,99,100)-创建存储过程 create procedure deleProStuID intasdelete from 学生基本信息表 where StuID=StuID-创建完毕exec delePro 1 -执行存储过程-创建存储过程create procedure seleProasselect * from 学生基本信息表-创建完毕exec selePro -执行存储过程 -在JAVA中调用-import java.sql.*;public class ProcedureTest public static void main(String args) throws Exception /加载驱动 DriverManager.registerDriver(new sun.jdbc.odbc.JdbcOdbcDriver(); /获得连接 Connection conn=DriverManager.getConnection(jdbc:odbc:mydata,sa,); /创建存储过程的对象 CallableStatement c=conn.prepareCall(call delePro(?); c.setInt(1,1); c.execute(); c=conn.prepareCall(call selePro); ResultSet rs=c.executeQuery(); while(rs.next() String Stu=rs.getString(StuID); String name=rs.getString(StuName); String add=rs.getString(StuAddress); System.out.println (学号:+ +姓名:+ +地址); System.out.println (Stu+ +name+ +add); c.close(); D:修改数据的存储过程-创建存储过程- create procedure ModProStuID int,StuName varchar(10)asupdate 学生基本信息表 set StuName=StuName where StuID=StuID -执行存储过程-exec ModPro 2,四毛 -JAVA调用存储过程-import java.sql.*;public class ProcedureTest public static void main(String args) throws Exception /加载驱动 DriverManager.registerDriver(new sun.jdbc.odbc.JdbcOdbcDriver(); /获得连接 Connection conn=DriverManager.getConnection(jdbc:odbc:mydata,sa,); /创建存储过程的对象 CallableStatement c=conn.prepareCall(call ModPro(?,?); c.setInt(1,2); c.setString(2,美女); c.execute(); c=conn.prepareCall(call selePro); ResultSet rs=c.executeQuery(); while(rs.next() String Stu=rs.getString(StuID); String name=rs.getString(StuName); String add=rs.getString(StuAddress); System.out.println (学号:+ +姓名:+ +地址); System.out.println (Stu+ +name+ +add); c.close(); E:查询数据的存储过程(模糊查询) -存储过程-create procedure FindCustscust varchar(10)asselect customerid from orders where customerid like %+cust+% -执行-execute FindCusts alfki -在JAVA中调用-import java.sql.*;public class ProcedureTest public static void main(String args) throws Exception /加载驱动 DriverManager.registerDriver(new sun.jdbc.odbc.JdbcOdbcDriver(); /获得连接 Connection conn=DriverManager.getConnection(jdbc:odbc:mydata,sa,); /创建存储过程的对象 CallableStatement c=conn.prepareCall(call FindCusts(?); c.setString(1,Tom); ResultSet rs=c.executeQuery(); while(rs.next() String cust=rs.getString(customerid); System.out.println (cust); c.close(); F:增加数据的存储过程-存储过程-create procedure InsertProStuID int,StuName varchar(10),StuAddress varchar(20)asinsert into 学生基本信息表 values(StuID,StuName,StuAddress)-调用存储过程-exec InsertPro 5,555,555-在JAVA中执行-import java.sql.*;public class ProcedureTest public static void main(String args) throws Exception /加载驱动 DriverManager.registerDriver(new sun.jdbc.odbc.JdbcOdbcDriver(); /获得连接 Connection conn=DriverManager.getConnection(jdbc:odbc:mydata,sa,); /创建存储过程的对象 CallableStatement c=conn.prepareCall(call InsertPro(?,?,?); c.setInt(1,6); c.setString(2,Liu); c.setString(3,wuhan); c.execute(); c=conn.prepareCall(call selePro); ResultSet rs=c.executeQuery(); while(rs.next() String stuid=rs.getString(StuID); String name=rs.getString(StuName); String address=rs.getString(