ITPub博客

首页 > Linux操作系统 > Linux操作系统 > 问存储过程一个自定义类型JAVA调用的问题《已解决》

问存储过程一个自定义类型JAVA调用的问题《已解决》

原创 Linux操作系统 作者:colin_cat 时间:2011-07-20 18:11:38 0 删除 编辑
在java中如何用啊?百度,google了一下 试着都不对呢。
我的代码

CODE:

   CallableStatement proc = conn.prepareCall("{ call ListAllDeliverAddress(?,?,?,?,?) }");
   proc.setInt(1, Integer.valueOf(uid));
   proc.registerIndexTableOutParameter(2,100,OracleTypes.VARCHAR,100 );
   proc.registerIndexTableOutParameter(3,100,OracleTypes.VARCHAR,100 );
   proc.registerIndexTableOutParameter(4,100,OracleTypes.VARCHAR,100 );
   proc.registerIndexTableOutParameter(5,100,OracleTypes.VARCHAR,100 );
       proc.execute();
        int num =   proc.getInt(2);
        String[] daddress =   proc.getPlsqlIndexTable(3, java.lang.String.TYPE);
        String[] dname =   proc.getPlsqlIndexTable(4, java.lang.String.TYPE);
        int[] tel =   proc.getPlsqlIndexTable(5, java.lang.String.TYPE);

存储过程:

CODE:

create or replace package B2CPackage as
    TYPE t_DeliverAddArray IS TABLE OF varchar2(150) INDEX BY BINARY_INTEGER;
    TYPE t_dName IS TABLE OF  t_DeliveryAddress.dName%type INDEX BY BINARY_INTEGER;
    TYPE t_dTelephone IS TABLE OF t_DeliveryAddress.dTelephone%type  INDEX BY BINARY_INTEGER;
   procedure ListAllDeliverAddress(p_UserID t_UserInfo.autoID%type, p_NumAddress OUT BINARY_INTEGER, p_DeliverAddArray OUT t_DeliverAddArray,
p_dName OUT t_dName, p_dTelephone OUT t_dTelephone);
End B2CPackage;

Create or replace package body B2CPackage
as
procedure ListAllDeliverAddress(p_UserID t_UserInfo.autoID%type, p_NumAddress OUT BINARY_INTEGER, p_DeliverAddArray OUT t_DeliverAddArray, p_dName OUT t_dName, p_dTelephone OUT t_dTelephone)
as
    cursor c_DeliveryAddress is
      select dName, dTelephone, cID, dRegion from t_DeliveryAddress where userID=p_UserID;
    v_dName t_DeliveryAddress.dName%type;
    v_dTelephone t_DeliveryAddress.dTelephone%type;
    v_cID t_DeliveryAddress.cID%type;
    v_dRegion t_DeliveryAddress.dRegion%type;
    v_pID t_City.pID%type;
    v_cName t_City.cName%type;
    v_pName t_Province.pName%type;
    v_FinalAddress varchar2(150);
Begin
    p_NumAddress := 0;
   loop
          p_NumAddress := p_NumAddress +1;
          fetch c_DeliveryAddress into v_dName, v_dTelephone, v_cID, v_dRegion;
          EXIT WHEN c_DeliveryAddress%NOTFOUND;
          select cName, pID into v_cName, v_pID from t_City where cID=v_cID;
          select pName into v_pName from t_Province where pID=v_pID;
          v_FinalAddress:=v_pName ||v_cName || v_dRegion;
          p_DeliverAddArray( p_NumAddress) := v_FinalAddress;
          p_dName(p_NumAddress) := v_dName;
          p_dTelephone(p_NumAddress) := v_dTelephone;
    end loop;
End ListAllDeliverAddress;
End B2CPackage;解决方案:

CODE:

OracleCallableStatement proc = (OracleCallableStatement) conn.prepareCall("{ call ListAllDeliverAddress(?,?,?,?,?) }");
   proc.setInt(1, Integer.valueOf(uid));
   
  
   // 注册返回参数
   
   
   [/color]
[color=red]   int num =proc.getInt(2);
   proc.registerOutParameter(3,100,OracleTypes.VARCHAR ,100);
   proc.registerOutParameter(4,100,OracleTypes.VARCHAR,100 );
   proc.registerOutParameter(5,100,OracleTypes.INTEGER,100 );
       proc.execute(); [/color]
[color=red]       //把索引存入数组中
        String[] daddress =  (String[]) proc.getPlsqlIndexTable(3);
        String[] dname =   (String[]) proc.getPlsqlIndexTable(4);
        int[] dtel =   (int[]) proc.getPlsqlIndexTable(5);


[ 本帖最后由 colin_cat 于 2011-7-17 14:34 编辑 ]

来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/24678025/viewspace-702684/,如需转载,请注明出处,否则将追究法律责任。

请登录后发表评论 登录
全部评论

注册时间:2010-10-20

  • 博文量
    6
  • 访问量
    12634