存储过程调用 - 调试易

存储过程调用

//first and second parameter in,
//third parameter out
String sql = "call sp_XXX(?,?,?)";
java.sql.CallableStatement cs = conn.prepareCall(sql);
cs.setString(1,"hello");
cs.setInt(2,12);
cs.registerOutParameter(3,Types.INTEGER);
cs.execute();
int out = cs.getInt(3);

解决方案 »

免费领取超大流量手机卡，每月29元包185G流量+100分钟通话, 中国电信官方发货

<! 文件名：unitnew1.jsp
创建时间：2002-01-03 by chen
修改时间：
说明:
-->
<%@page contentType="text/html;charset=gb2312"%>
<%@page import="java.sql.*"%><html>
<head>
<title>Untitled Document</title>
<meta http-equiv="Content-Type" content="text/html; charset=gb2312">
<link rel="stylesheet" href="file:///E|/Auditwebsite/css/main.css" type="text/css"></head>
<jsp:useBean id="ChangeCharacter" scope="page" class="com.bitc.ChangeCharacter"/>
<body bgcolor="#FFFFFF" text="#000000" leftmargin="0" topmargin="0">
<%
String Id=request.getParameter("id");
String UnitName=ChangeCharacter.ChangeCharacter(request.getParameter("unitname"));
String UnitPro=ChangeCharacter.ChangeCharacter(request.getParameter("unitpro"));
String UnitIndus=ChangeCharacter.ChangeCharacter(request.getParameter("unitindus"));
String MainScope=ChangeCharacter.ChangeCharacter(request.getParameter("main"));
String FaRen=ChangeCharacter.ChangeCharacter(request.getParameter("faren"));
String SourceMoney=ChangeCharacter.ChangeCharacter(request.getParameter("sourcemoney"));
String CapitalReg=ChangeCharacter.ChangeCharacter(request.getParameter("capitalreg"));
String CapitalPre=ChangeCharacter.ChangeCharacter(request.getParameter("capitalpre"));
String UnitAddress=ChangeCharacter.ChangeCharacter(request.getParameter("unitaddress"));
String MailCode=request.getParameter("mailcode");
String TelPh=ChangeCharacter.ChangeCharacter(request.getParameter("telph"));
String DeptMa=ChangeCharacter.ChangeCharacter(request.getParameter("deptma"));
String JianGuan=ChangeCharacter.ChangeCharacter(request.getParameter("jianguan"));
String UnderEnter=ChangeCharacter.ChangeCharacter(request.getParameter("underenter"));
String FileDept=ChangeCharacter.ChangeCharacter(request.getParameter("filedept"));
String FiPricipal=ChangeCharacter.ChangeCharacter(request.getParameter("fipricipal"));
String Indept=ChangeCharacter.ChangeCharacter(request.getParameter("indept"));
String HumCount=ChangeCharacter.ChangeCharacter(request.getParameter("humcount"));
String cMessage=null;%>
<jsp:useBean id="NewAuUnit" scope="page" class="com.bitc.NewAuUnit"/>
<%
NewAuUnit.BuildConnection();
NewAuUnit.getParameter(UnitName,Id,UnitPro,UnitIndus,MainScope,FaRen,SourceMoney,CapitalReg,CapitalPre,UnitAddress,MailCode,TelPh,DeptMa,JianGuan,UnderEnter,FileDept,FiPricipal,Indept,HumCount);
cMessage=NewAuUnit.getResult();
%>
<FONT SIZE="" COLOR="#FF00CC"><CENTER><%=cMessage%></CENTER></FONT></body>
</html>
/*
*文件名：NewAuUnit.java
*创建时间：2002-01-03 by chen
*修改时间：
*功能描述：
*/package com.bitc;import java.sql.*;
import com.bitc.*;
import java.io.*;import java.util.*;public class NewAuUnit extends DBCon2
{

    CallableStatement stmt=null;
String sql="{call Au_NewUnit(?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)}";
String sql2="{call Au_ModifyUnit(?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)}";
String Id="";
String UnitName="";
String UnitPro="";
String UnitIndus="";
String TradeBound="";
String Principald="";
    String OutLayOrigin="";
String Regist="";
String Paic="";
String UnitAddress="";
String UnitMailCode="";
String ContactTel="";
String CharDept="";
String StaDept="";
String UnderEnter="";
String FiliDept="";
String FiPricipal="";
String InAuDept="";
String HumCount="";
public NewAuUnit()
{
super();
}

public void getParameter(String UnitName,
String Id,
                         String UnitPro,
String UnitIndus,
String TradeBound,
String Principald,
String OutLayOrigin,
String Regist,
String Paic,
String UnitAddress,
String UnitMailCode,
String ContactTel,
String CharDept,
String StaDept,
String UnderEnter,
String FiliDept,
String FiPricipal,
String InAuDept,
String HumCount
)
{
this.UnitName=UnitName;
this.Id=Id;
this.UnitPro=UnitPro;
this.UnitIndus=UnitIndus;
this.TradeBound=TradeBound;
this.Principald=Principald;
this.OutLayOrigin=OutLayOrigin;
this.Regist=Regist;
this.Paic=Paic;
this.UnitAddress=UnitAddress;
this.UnitMailCode=UnitMailCode;
this.ContactTel=ContactTel;
this.CharDept=CharDept;
this.StaDept=StaDept;
this.UnderEnter=UnderEnter;
this.FiliDept=FiliDept;
this.FiPricipal=FiPricipal;
this.InAuDept=InAuDept;
this.HumCount=HumCount;
}

public String getResult() throws Exception
    {

  stmt=con.prepareCall(sql);

  stmt.setString(1,Id);
  stmt.setString(2,UnitName);
  stmt.setString(3,UnitPro);
  stmt.setString(4,UnitIndus);
  stmt.setString(5,TradeBound);
  stmt.setString(6,Principald);
  stmt.setString(7,OutLayOrigin);
  stmt.setString(8,Regist);
  stmt.setString(9,Paic);
  stmt.setString(10,UnitAddress);
  stmt.setString(11,UnitMailCode);
  stmt.setString(12,ContactTel);
  stmt.setString(13,CharDept);
  stmt.setString(14,StaDept);
  stmt.setString(15,UnderEnter);
  stmt.setString(16,FiliDept);
  stmt.setString(17,FiPricipal);
  stmt.setString(18,InAuDept);
  stmt.setString(19,HumCount);

  stmt.registerOutParameter(20,Types.VARCHAR);
  stmt.executeUpdate();
  //clearParameters();
  String cMessage=stmt.getString(20);
  return cMessage;
    }

     public String getResultModifyUnit() throws Exception
    {

  stmt=con.prepareCall(sql2);

  stmt.setString(1,Id);
  stmt.setString(2,UnitName);
  stmt.setString(3,UnitPro);
  stmt.setString(4,UnitIndus);
  stmt.setString(5,TradeBound);
  stmt.setString(6,Principald);
  stmt.setString(7,OutLayOrigin);
  stmt.setString(8,Regist);
  stmt.setString(9,Paic);
  stmt.setString(10,UnitAddress);
  stmt.setString(11,UnitMailCode);
  stmt.setString(12,ContactTel);
  stmt.setString(13,CharDept);
  stmt.setString(14,StaDept);
  stmt.setString(15,UnderEnter);
  stmt.setString(16,FiliDept);
  stmt.setString(17,FiPricipal);
  stmt.setString(18,InAuDept);
  stmt.setString(19,HumCount);

  stmt.registerOutParameter(20,Types.VARCHAR);
  stmt.executeUpdate();
  //clearParameters();
  String cMessage=stmt.getString(20);
  return cMessage;
    }
/**********************************************************************************************************
  存储过程名称：Au_NewUnit
  存储过程功能：
  建立时间：2002-01-03 by chen
  修改时间：
  修改说明：
  入口参数：  vId
            vUnitName
            vUnitPro
    vUnitInd
    vTradeBound
    vPrincipal
    vOutLayOrigin
    nRegist
    nPaic
    vUnitAddress
    vUnitMailCode
    vContactTel
    vCharDept
    vStaDept
    vUnderEnter
    vFiliDept
    vFiPricipal
    vInAuDept
    nHumCount  出口参数：cMessage       --返回信息
  调用举例：
***********************************************************************************************************/
CREATE OR REPLACE PROCEDURE Au_NewUnit
(

   vId                      IN      VARCHAR2,
   vUnitName                IN      VARCHAR2,
   vUnitPro     IN      VARCHAR2,
   vUnitInd     IN      VARCHAR2,
   vTradeBound     IN      VARCHAR2,
   vPrincipal     IN      VARCHAR2,
   vOutLayOrigin     IN      VARCHAR2,
   nRegist     IN      VARCHAR2,
   nPaic     IN      VARCHAR2,
   vUnitAddress             IN      VARCHAR2,
   vUnitMailCode            IN      VARCHAR2,
   vContactTel     IN      VARCHAR2,
   vCharDept     IN      VARCHAR2,
   vStaDept             IN      VARCHAR2,
   vUnderEnter             IN      VARCHAR2,
   vFiliDept     IN      VARCHAR2,
   vFiPricipal             IN      VARCHAR2,
   vInAuDept                IN      VARCHAR2,
   nHumCount                IN      VARCHAR2,
   cMessage                 OUT     VARCHAR2
)
ASBEGIN

      INSERT INTO Au_BasicObject(V_UnitName,V_UnitId,V_UnitProNa,V_IndusSortNa,V_TradeBound,V_Principal,V_OutLayOrigin,V_RegisCapital,V_PaiclUpCapital,V_UnitAddress,V_UnitMailCode,V_ContactTel,V_CharDept,V_StaManDept,V_UnderEnterPri,V_FiliDept,V_FiPrincipal,V_InAuDeptPrin,V_UnHumCount)
  VALUES(vUnitName,vId,vUnitPro,vUnitInd,vTradeBound,vPrincipal,vOutLayOrigin,nRegist,nPaic,vUnitAddress,vUnitMailCode,vContactTel,vCharDept,vStaDept,vUnderEnter,vFiliDept,vFiPricipal,vInAuDept,nHumCount);
   cMessage :='成功新建单位基本信息!';  EXCEPTION

  WHEN dup_val_on_index THEN
  cMessage:='该序号已被占用，请重新指定新序号!';
  ROLLBACK;
  WHEN others THEN
  cMessage :='执行存储过程[Au_NewUnit]发生异常,事务已回滚!';

  ROLLBACK;
  END Au_NewUnit;
/    public void cleanup() throws Exception
   {

   if(stmt!=null)stmt.close();
   }
}