java如何写存储过程

Java中对存储过程的调用一:Java如何实现对存储过程的调用:A:不带输出参数的---------------不带输出参数的----------------------------------create procedure getsum@n int =0<--此处为参数-->asdeclare @sum int<--定义变量-->declare @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{//加载驱动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); //将第一个参数的值设置成100//执行存储过程c.execute();conn.close();}}B:带输出参数的1:返回int-------------------------带输出参数的----------------alter procedure getsum@n 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----------------存储过程带游标-------------------在存储过程中带游标使用游标不停的遍历orderid create procedure CursorIntoProcedure@pname 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=0beginset @pname=@pname+';'+@vfetch 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 delePro@StuID 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:修改数据的存储过程---------------------创建存储过程---------------------@StuID 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:查询数据的存储过程(模糊查询)-----------------存储过程---------------------@cust varchar(10)asselect customerid from orders where customeridlike '%'+@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 InsertPro@StuID 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("StuAddress");System.out.println (stuid+" "+name+" "+address);}c.close();}}G:在JAVA中创建存储过程并且在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","");Statement stmt=conn.createStatement();//在JAVA中创建存储过程stmt.executeUpdate("create procedure OOP as select * from 学生成绩表"); CallableStatement c=conn.prepareCall("{call OOP}");ResultSet rs=c.executeQuery();while(rs.next()){String chinese=rs.getString("Chinese");System.out.println (chinese);}conn.close();}}。