jsp执行存储过程.docx
《jsp执行存储过程.docx》由会员分享,可在线阅读,更多相关《jsp执行存储过程.docx(9页珍藏版)》请在冰豆网上搜索。
![jsp执行存储过程.docx](https://file1.bdocx.com/fileroot1/2022-11/25/1c57e3cd-a1e6-43af-b09d-7f2329aec999/1c57e3cd-a1e6-43af-b09d-7f2329aec9991.gif)
jsp执行存储过程
jsp执行存储过程
JAVA中对存储过程的调用方法
一:
Java如何实现对存储过程的调用:
A:
不带输出参数的
---------------不带输出参数的----------------------------------
createproceduregetsum
@nint=0<--此处为参数-->
as
declare@sumint<--定义变量-->
declare@iint
set@sum=0
set@i=0
while@i<=@nbegin
set@sum=@sum+@i
set@i=@i+1
end
print'thesumis'+ltrim(rtrim(str(@sum)))
--------------在SQL中执行:
--------------------
execgetsum100
------------在JAVA中调用:
---------------------
JAVA可以调用 但是在JAVA程序却不能去显示该存储过程的结果因为上面的存储
过程的参数类型int传递方式是in(按值)方式
importjava.sql.*;
publicclassProcedureTest
{
publicstaticvoidmain(Stringargs[])throwsException
{
//加载驱动
DriverManager.registerDriver(newsun.jdbc.odbc.JdbcOdbcDriver());
//获得连接
Connectionconn=DriverManager.getConnection("jdbc:
odbc:
mydata","sa","");
//创建存储过程的对象
CallableStatementc=conn.prepareCall("{callgetsum(?
)}");
//给存储过程的参数设置值
c.setInt(1,100); //将第一个参数的值设置成100
//执行存储过程
c.execute();
conn.close();
}
}
B:
带输出参数的
1:
返回int
-------------------------带输出参数的----------------
alterproceduregetsum
@nint=0,
@resultintoutput
as
declare@sumint
declare@iint
set@sum=0
set@i=0
while@i<=@nbegin
set@sum=@sum+@i
set@i=@i+1
end
set@result=@sum
-------------------在查询分析器中执行------------
declare@myResultint
execgetsum100,@myResultoutput
print@myResult
------------在JAVA中调用---------------------
importjava.sql.*;
publicclassProcedureTest
{
publicstaticvoidmain(Stringargs[])throwsException
{
//加载驱动
DriverManager.registerDriver(newsun.jdbc.odbc.JdbcOdbcDriver());
//获得连接
Connectionconn=DriverManager.getConnection("jdbc:
odbc:
mydata","sa","");
//创建存储过程的对象
CallableStatementc=conn.prepareCall("{callgetsum(?
?
)}");
//给存储过程的第一个参数设置值
c.setInt(1,100);
//注册存储过程的第二个参数
c.registerOutParameter(2,java.sql.Types.INTEGER);
//执行存储过程
c.execute();
//得到存储过程的输出参数值
System.out.println(c.getInt
(2));
conn.close();
}
}
2:
返回varchar
----------------存储过程带游标----------------
---在存储过程中带游标 使用游标不停的遍历orderid
createprocedureCursorIntoProcedure
@pnamevarchar(8000)output
as
--定义游标
declarecurcursorforselectorderidfromorders
--定义一个变量来接收游标的值
declare@vvarchar(5)
--打开游标
opencur
set@pname=''--给@pname初值
--提取游标的值
fetchnextfromcurinto@v
while@@fetch_status=0
begin
set@pname=@pname+';'+@v
fetchnextfromcurinto@v
end
print@pname
--关闭游标
closecur
--销毁游标
deallocatecur
------------执行存储过程--------------
execCursorIntoProcedure''
--------------JAVA调用------------------
importjava.sql.*;
publicclassProcedureTest
{
publicstaticvoidmain(Stringargs[])throwsException
{
//加载驱动
DriverManager.registerDriver(newsun.jdbc.odbc.JdbcOdbcDriver());
//获得连接
Connectionconn=DriverManager.getConnection("jdbc:
odbc:
mydata","sa","");
CallableStatementc=conn.prepareCall("{callCursorIntoProcedure(?
)}");
c.registerOutParameter(1,java.sql.Types.VARCHAR);
c.execute();
System.out.println(c.getString
(1));
conn.close();
}
}
C:
删除数据的存储过程
------------------存储过程--------------------------
droptable学生基本信息表
createtable学生基本信息表
(
StuIDintprimarykey,
StuNamevarchar(10),
StuAddressvarchar(20)
)
insertinto 学生基本信息表values(1,'三毛','wuhan')
insertinto 学生基本信息表values(2,'三毛','wuhan')
createtable学生成绩表
(
StuIDint,
Chineseint,
PyhSicsint
foreignkey(StuID)references 学生基本信息表(StuID)
ondeletecascade
onupdatecascade
)
insertinto 学生成绩表values(1,99,100)
insertinto 学生成绩表values(2,99,100)
--创建存储过程
createproceduredelePro
@StuIDint
as
deletefrom学生基本信息表whereStuID=@StuID
--创建完毕
execdelePro1 --执行存储过程
--创建存储过程
createprocedureselePro
as
select*from学生基本信息表
--创建完毕
execselePro --执行存储过程
------------------在JAVA中调用----------------
importjava.sql.*;
publicclassProcedureTest
{
publicstaticvoidmain(Stringargs[])throwsException
{
//加载驱动
DriverManager.registerDriver(newsun.jdbc.odbc.JdbcOdbcDriver());
//获得连接
Connectionconn=DriverManager.getConnection("jdbc:
odbc:
mydata","sa","");
//创建存储过程的对象
CallableStatementc=conn.prepareCall("{calldelePro(?
)}");
c.setInt(1,1);
c.execute();
c=conn.prepareCall("{callselePro}");
ResultSetrs=c.executeQuery();
while(rs.next())
{
StringStu=rs.getString("StuID");
Stringname=rs.getString("StuName");
Stringadd=rs.getString("StuAddress");
System.out.println("学号:
"+" "+"姓名:
"+" "+"地址");
System.out.println(Stu+" "+name+" "+add);
}
c.close();
}
}
D:
修改数据的存储过程
---------------------创建存储过程---------------------
createprocedureModPro
@StuIDint,
@StuNamevarchar(10)
as
update学生基本信息表setStuName=@StuNamewhereStuID=@StuID
-------------执行存储过程-------------------------
execModPro2,'四毛'
---------------JAVA调用存储过程--------------------
importjava.sql.*;
publicclassProcedureTest
{
publicstaticvoidmain(Stringargs[])throwsException
{
//加载驱动
DriverManager.registerDriver(newsun.jdbc.odbc.JdbcOdbcDriver());
//获得连接
Connectionconn=DriverManager.getConnection("jdbc:
odbc:
mydata","sa","");
//创建存储过程的对象
CallableStatementc=conn.prepareCall("{callModPro(?
?
)}");
c.setInt(1,2);
c.setString(2,"美女");
c.execute();
c=conn.prepareCall("{callselePro}");
ResultSetrs=c.executeQuery();
while(rs.next())
{
StringStu=rs.getString("StuID");
Stringname=rs.getString("StuName");
Stringadd=rs.getString("StuAddress");
System.out.println("学号:
"+" "+"姓名:
"+" "+"地址");
System.out.println(Stu+" "+name+" "+add);
}
c.close();
}
}
E:
查询数据的存储过程(模糊查询)
-----------------存储过程---------------------
createprocedureFindCusts
@custvarchar(10)
as
selectcustomeridfromorderswherecustomerid
like'%'+@cust+'%'
---------------执行---------------------------
executeFindCusts'alfki'
-------------在JAVA中调用--------------------------
importjava.sql.*;
publicclassProcedureTest
{
publicstaticvoidmain(Stringargs[])throwsException
{
//加载驱动
DriverManager.registerDriver(newsun.jdbc.odbc.JdbcOdbcDriver());
//获得连接
Connectionconn=DriverManager.getConnection("jdbc:
odbc:
mydata","sa","");
//创建存储过程的对象
CallableStatementc=conn.prepareCall("{callFindCusts(?
)}");
c.setString(1,"Tom");
ResultSetrs=c.executeQuery();
while(rs.next())
{
Stringcust=rs.getString("customerid");
System.out.println(cust);
}
c.close();
}
}
F:
增加数据的存储过程
------------存储过程--------------------
createprocedureInsertPro
@StuIDint,
@StuNamevarchar(10),
@StuAddressvarchar(20)
as
insertinto学生基本信息表values(@StuID,@StuName,@StuAddress)
-----------调用存储过程---------------
execInsertPro5,'555','555'
-----------在JAVA中执行-------------
importjava.sql.*;
publicclassProcedureTest
{
publicstaticvoidmain(Stringargs[])throwsException
{
//加载驱动
DriverManager.registerDriver(newsun.jdbc.odbc.JdbcOdbcDriver());
//获得连接
Connectionconn=DriverManager.getConnection("jdbc:
odbc:
mydata","sa","");
//创建存储过程的对象
CallableStatementc=conn.prepareCall("{callInsertPro(?
?
?
)}");
c.setInt(1,6);
c.setString(2,"Liu");
c.setString(3,"wuhan");
c.execute();
c=conn.prepareCall("{callselePro}");
ResultSetrs=c.executeQuery();
while(rs.next())
{
Stringstuid=rs.getString("StuID");
Stringname=rs.getString("StuName");
Stringaddress=rs.getString("StuAddress");
System.out.println(stuid+" "+name+" "+address);
}
c.close();
}
}
G:
在JAVA中创建存储过程 并且在JAVA中直接调用
importjava.sql.*;
publicclassProcedureTest
{
publicstaticvoidmain(Stringargs[])throwsException
{
//加载驱动
DriverManager.registerDriver(newsun.jdbc.odbc.JdbcOdbcDriver());
//获得连接
Connectionconn=DriverManager.getConnection("jdbc:
odbc:
mydata","sa","");
Statementstmt=conn.createStatement();
//在JAVA中创建存储过程
stmt.executeUpdate("createprocedureOOPasselect*from学生成绩表");
CallableStatementc=conn.prepareCall("{callOOP}");
ResultSetrs=c.executeQuery();
while(rs.next())
{
Stringchinese=rs.getString("Chinese");
System.out.println(chinese);
}
conn.close();
}
}