JAVA中對預存程序的調用方法

一:Java如何?對預存程序的調用:
  A:不帶輸出參數的
   ---------------不帶輸出參數的----------------------------------
create procedure getsum
@n int =0<--此處為參數-->
as
declare @sum int<--定義變數-->
declare @i int
set @sum=0
set @i=0
while @i<=@n begin
set @sum=@sum+@i
set @i=@i+1
end
print '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 output
as
declare @sum int
declare @i int
set @sum=0
set @i=0
while @i<=@n begin
set @sum=@sum+@i
set @i=@i+1
end
set @result=@sum
   -------------------在查詢分析器中執行------------
  declare @myResult int
exec getsum 100,@myResult output
print @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) output
as
--定義遊標
declare cur cursor for select orderid from orders
--定義一個變數來接收遊標的值
declare @v varchar(5)
--開啟遊標
open cur
set @pname=''--給@pname初值
--提取遊標的值
fetch next from cur into @v
while @@fetch_status=0
  begin

set @pname=@pname+';'+@v
  fetch next from cur into @v
end
print @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 int
foreign key(StuID) references  學生基本資料表(StuID)
on delete cascade
on update cascade
)
insert into  學產生績表 values(1,99,100)
insert into  學產生績表 values(2,99,100)

--建立預存程序
create procedure delePro
@StuID int
as
delete from 學生基本資料表 where StuID=@StuID
--建立完畢
exec delePro 1  --執行預存程序
--建立預存程序
create procedure selePro
as
select * 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 ModPro
@StuID int,
@StuName varchar(10)
as
update 學生基本資料表 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 FindCusts
@cust varchar(10)
as
select 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 InsertPro
@StuID int,
@StuName varchar(10),
@StuAddress varchar(20)
as
insert 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();
  
}
}