jsp执行存储过程.docx

1、jsp执行存储过程jsp执行存储过程JAVA中对存储过程的调用方法一: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程序却不能去显示该存储过程的结果 因为上面的存

2、储过程的参数类型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.pr

3、epareCall(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

4、 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,);/创

5、建存储过程的对象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 Curso

6、rIntoProcedurepname 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-销毁游标de

7、allocate 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,);CallableS

8、tatement 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学生基

9、本信息表 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 intas

10、delete 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.od

11、bc.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 na

12、me=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调用存储

13、过程-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(

14、?,?);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)

15、;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.regist

16、erDriver(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.printl

17、n (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 E

18、xception/加载驱动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.prepareCal

19、l(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 pub

20、lic 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();