从java写的存储过程转换到DB2的SQL存储过程的笔记

这几天被一个脑残的存储过程搞得很烦。昨天找了一天的资料都没办法搞好。由于项目开始是在DB2的开发中心，用java写的存储过程。由于客户升级的需要，数据库迁移到更高版本的数据库中去了，迁移后，这个存储过程就用不了了。于是我打开DB2的开发中心，打开该存储过程的项目，打算部署到新的DB2上，无论怎么部署，始终都提示下面的错误：

Call SQLJ.DB2_INSTALL_JAR (<<C:\Documents and Settings\jinghua\Application Data\IBM\DB2\DC\项目\bld1309245830936\SQL110528115458880.jar>>, 'GDZJ.SQL110528115458880', 0)
[IBM][CLI Driver][DB2/AIX64] SQL4304N 具有特定名称 "SQL110628152507130" 的 Java 存储过程或用户定义的函数 "sqlejReadJar"不能装入 Java 类 "COM/ibm/db2/app/sqlejProcs"，原因码为 "5"。  SQLSTATE=42724

在google中无论搜索中文还是英文的资料，都找不到解决的方案，于是，把这脑残的问题，从java中转换到db2的sql中去。下面先看看该存储过程的代码：

/**
 * SQLJ 存储过程 GDZJ.BaseCodeGen
 *  @param  enprName 企业名称
 *  @param  enprAddr 企业地址
 *  @param  enprLeader 法人
 *  @param  enprPhone 联系电话
 *  @param  baseCode 返回的基准码
  */
package  com.excellence.basecodesrv;

import  java.io.FileWriter;
import  java.io.PrintWriter;

import  java.sql. * ;            //  JDBC 类
import  java.util.Map;
import  java.util.HashMap;
import  java.util.Date;
import  sqlj.runtime. * ;
import  sqlj.runtime.ref. * ;

#sql context SPContext;
#sql iterator BaseCodeGen_Cursor1 ( String curBaseCode );
#sql iterator BaseCodeGen_Cursor2 ( String curBaseCode );

public   class  BaseCodeGen
{
     /**
     *  @param   enprName
     *  @param   enprAddr
     *  @param   enprLeader
     *  @param   enprPhone
     *  @param   baseCode
     *  @exception   SQLException
     *  @exception   Exception
      */
     public   static   void  baseCodeGen ( String enprName,
                                     String enprAddr,
                                     String enprLeader,
                                     String enprPhone,
                                     String[] baseCode )  throws  SQLException, Exception
    {
        BaseCodeGen_Cursor1 cursor1  =   null ;
        BaseCodeGen_Cursor2 cursor2  =   null ;
        SPContext ctx  =   null ;
         try
        {
             if (enprName == null   ||  enprName.trim().equals( "" )){
                 //  设置返回参数
                baseCode[ 0 ]  =   null ;
                 return ;
            }
            ctx  =   new  SPContext(  " jdbc:default:connection " ,  false  );

            #sql [ctx] cursor1  =
            {
                select max(enpr_basecode) as curBaseCode from T_PUB_BASECODE where enpr_name like concat(concat( ' % ' ,:enprName), ' % ' )
            };
             //  用来存取结果
              // ResultSet rs1 = cursor1.getResultSet();
             String curBaseCode  =   "" ;
              while  (cursor1.next())
             {
                curBaseCode  =  cursor1.curBaseCode();
             }
             //  关闭打开资源
             if  (cursor1  !=   null ) cursor1.close();

             if (curBaseCode != null   &&   ! curBaseCode.trim().equals( "" )){
                 //  设置返回参数
                baseCode[ 0 ]  =  curBaseCode;
                 return ;
            } else {
                #sql [ctx] cursor2  =
                    {
                        select max(enpr_basecode) as curBaseCode from T_PUB_BASECODE
                    };
                  while  (cursor2.next())
                 {
                    curBaseCode  =  cursor2.curBaseCode();
                }
                     //  关闭打开资源
                 if  (cursor2  !=   null ) cursor2.close();
            }

             // 权重
            Map weight  =   new  HashMap();
            weight.put( new  Integer( " 3 " ),  new  Integer( " 9 " ));
            weight.put( new  Integer( " 4 " ),  new  Integer( " 10 " ));
            weight.put( new  Integer( " 5 " ),  new  Integer( " 5 " ));
            weight.put( new  Integer( " 6 " ),  new  Integer( " 8 " ));
            weight.put( new  Integer( " 7 " ),  new  Integer( " 4 " ));
            weight.put( new  Integer( " 8 " ),  new  Integer( " 2 " ));

            String domain  =   " GZ " ;  //  主体码
            String entityCode  =   "" ;  //  本体码
            String checkCode  =   "" ;  //  校验码
            String rtnBaseCode  =   "" ;  //  返回的基准码

            entityCode  =  curBaseCode == null ? " 333333 " :curBaseCode.substring( 2 , 8 );
             if  (entityCode.equals( " ZZZZZZ " )) {
                 throw   new  Exception( " 本体码分配已经用完，无法再生成新的企业基准码！ " );
            }
            
             //  本体码 begin
            String newEntityCode  =   "" ;
             char  c  =   ' 0 ' ;
             for ( int  i = 5 ; i >= 0 ;  -- i){            
                c  =  entityCode.charAt(i);

                 if (c  ==   ' 9 ' ){
                    c  =    ' A ' ;
                } else   if (c  ==   ' Z ' ){
                    c  =    ' 0 ' ;
                } else {
                     ++ c;
                }

                newEntityCode  =  c + newEntityCode;
                 if (c < ' Z '   &&  c != ' 0 ' ){
                    newEntityCode  =  entityCode.substring( 0 ,i) + newEntityCode;
                     break ;
                }
            }
            entityCode  =  newEntityCode;
             //  本体码 end

             //  校验码 begin
             int  charWeight  =   0 ;  //  本体码‘位’的权重
             int  entityCharNum  =   0 ;  //  本体码‘位’的数值
             int  sum  =   0 ;  //  本体码‘位’的权重 与 本体码‘位’的数值 的乘积
             char  sglChar  =   ' 0 ' ;
             for ( int  i = 0 ; i < 6 ; i ++ ){
                sglChar  =  entityCode.charAt(i);
                 if (sglChar  >   ' 9 ' ){
                    entityCharNum  =  sglChar  -   55 ;
                } else {
                    entityCharNum  =  sglChar  -   48 ;
                }            
                charWeight  =  ((Integer)weight.get( new  Integer(i + 3 ))).intValue();
                sum  +=  entityCharNum * charWeight;
            }
            
             int  cc  =   11   -  sum % 11 ;    
            
             switch (cc){        
                 case   10 :
                    checkCode  =   " X " ;
                     break ;
                 case   11 :
                    checkCode  =   " 0 " ;
                     break ;
                 default  :
                    checkCode  =  String.valueOf(cc);
                     break ;
            }
             //  校验码 end

            rtnBaseCode  =  domain + entityCode + checkCode;

            Date genTime  =   new  java.sql.Date( new  Date().getTime());
            Date giveTime  =   new  java.sql.Date( new  Date().getTime());
            #sql
            {
                insert into T_PUB_BASECODE(enpr_name,charger,address,phone,enpr_basecode,gen_bc_time,give_bc_time) values(:enprName,:enprLeader,:enprAddr,:enprPhone,:rtnBaseCode,:genTime,:giveTime)
            };

             //  设置返回参数
            baseCode[ 0 ]  =  rtnBaseCode;
        }
         catch  (Exception e)
        {
             //  关闭打开资源
             try
            {
                 if  (cursor1  !=   null ) cursor1.close();
                 if  (cursor2  !=   null ) cursor2.close();
                 if  (ctx  !=   null ) ctx.close();
            }  catch  (SQLException e2) {  /*  忽略  */  };

             /*
            try{
                PrintWriter pwx = new PrintWriter(new FileWriter("c:/db2ps.txt"),true);
                e.printStackTrace(pwx);
            }catch(Exception e3){ }
             */          

             throw  e;
        }
    }
    
} 

转换后的DB2存储过程为：

 

--  Start of generated script for server226-DB2-GDZJ (gdzj)
--   Jun-29-2011 at 10:51:42

SET   SCHEMA  GDZJ    ;

SET   CURRENT  PATH  =  "SYSIBM","SYSFUN","SYSPROC","GDZJ";
drop   PROCEDURE  GDZJ.BASECODEGEN_ZGW;
CREATE   PROCEDURE  GDZJ.BASECODEGEN_ZGW
 ( IN  ENPRNAME  VARCHAR ( 256 ), 
   IN  ENPRADDR  VARCHAR ( 256 ), 
   IN  ENPRLEADER  VARCHAR ( 64 ), 
   IN  ENPRPHONE  VARCHAR ( 64 ), 
  OUT BASECODE  VARCHAR ( 32 )
 ) 
  LANGUAGE SQL
   NOT  DETERMINISTIC
  CALLED  ON   NULL  INPUT
  MODIFIES SQL DATA
  INHERIT SPECIAL REGISTERS
  l1: begin
   declare  curBaseCode  varchar ( 16 )  default   '' ;
   declare  domain  varchar ( 16 )  default   ' GZ ' ; -- 主体码
   declare  entityCode  varchar ( 16 )  default   '' ; --  本体码
   declare  checkCode  varchar ( 16 )  default   '' ; --  校验码
   declare  rtnBaseCode   varchar ( 16 )  default   '' ; --  返回的基准码
   declare  newEntityCode  varchar ( 16 )  default   '' ;
   declare  V_COUNT  int   default   0 ;
   declare  V_INDEX  int   default   0 ;
   declare  cc  int   default   0 ;
   declare  c  char ( 1 )  default   ' 0 ' ;
  
   declare  charWeight  int   default   0 ;  --  本体码‘位’的权重
   declare  entityCharNum  int   default   0 ;  --  本体码‘位’的数值
   declare   sum   int   default   0 ;  --  本体码‘位’的权重 与 本体码‘位’的数值 的乘积
   declare  sglChar  char ( 1 )  default   ' 0 ' ;
      
   if  ENPRNAME  is   null   or   rtrim (ENPRNAME) = ''   then
        set  BASECODE  =   null ;
      return ;
   end   if ;
   set  curBaseCode = ( select   max (enpr_basecode)  from  T_PUB_BASECODE  where  enpr_name  like   ' % ' || ENPRNAME || ' % '  );
   if  curBaseCode  is   not   null   and   rtrim (curBaseCode) != ''   then
     set  BASECODE  = curBaseCode;  
     return ;
   else
     set  curBaseCode = ( select   max (enpr_basecode)  from  T_PUB_BASECODE);
   end   if ;
   if  curBaseCode  is   null   then
     set  entityCode = ' 333333 ' ;
   else
     set  entityCode  =  SUBSTR(curBaseCode, 3 , 6 );
   end   if ;
   if  entityCode = ' ZZZZZZ '   then
      set  BASECODE  = null ;
      return ;
   end   if ;
  
   -- 本体码 begin
   SET  V_COUNT  =  LENGTH(entityCode);
   set  V_INDEX = V_COUNT;
  
WHILE  V_INDEX  > 0  DO
set  c  =  SUBSTR(entityCode,V_INDEX, 1 );

if  c  =   ' 9 '   then
   set  c  =    ' A ' ;
elseif c = ' Z '   then
   set  c = ' 0 ' ;
else
   set  c = chr(DEC_TO_TEN(HEX(c), 16 ) + 1 );
end   if ;

set  newEntityCode = c || newEntityCode;

if  c < ' Z '   and  c != ' 0 '   then
   set  newEntityCode  =  SUBSTR(entityCode, 1 ,V_INDEX - 1 ) || newEntityCode;
   set  V_INDEX = 0 ;
end   if ;
set  V_INDEX = V_INDEX - 1 ;

END   WHILE ;
set  entityCode  =  newEntityCode;
-- 本体码 end
-- 校验码 begin
   set  V_INDEX = 0 ;
WHILE  V_INDEX < 6  DO
   set  sglChar  =  SUBSTR(entityCode,V_INDEX + 1 , 1 );
   if  sglChar > ' 9 '   then
     set  entityCharNum = DEC_TO_TEN(HEX(sglChar), 16 ) - 55 ;
   else
     set  entityCharNum = DEC_TO_TEN(HEX(sglChar), 16 ) - 48 ;
   end   if ;
   -- 设置权重值
   case  V_INDEX
   when   0   then   set  charWeight = 9 ;
   when   1   then   set  charWeight = 10 ;
   when   2   then   set  charWeight = 5 ;
   when   3   then   set  charWeight = 8 ;
   when   4   then   set  charWeight = 4 ;
   when   5   then   set  charWeight = 2 ;
   else
     set  charWeight = 0 ;
   end   case ;
   set   sum = sum + entityCharNum * charWeight;
  
   set  V_INDEX = V_INDEX + 1 ;
END   WHILE ;

set  cc = 11   -  mod( sum , 11 );    
            
case  cc
   when   10   then   set  checkCode  =   ' X ' ;
   when   11   then   set  checkCode  =   ' 0 ' ;
   else
     set  checkCode  =   char (cc);
   end   case ;

-- 校验码 end
   set  rtnBaseCode  =  domain || entityCode || checkCode;
   insert   into  T_PUB_BASECODE(enpr_name,charger,address,phone,enpr_basecode,gen_bc_time,give_bc_time) 
   values (enprName,enprLeader,enprAddr,enprPhone,rtnBaseCode, current  date, current  date);
  
   set  BASECODE  = rtnBaseCode;
  
return ;

end  l1;
#SYNC  10 ;



--  End of generated script for server226-DB2-GDZJ (gdzj)

其中，该存储过程中使用的一个自定义函数：DEC_TO_TEN（16进制转换成10进制），其代码如下（该代码非原创，在网络上搜索到的）：

 

--  Start of generated script for server226-DB2-GDZJ (gdzj)
--   Jun-29-2011 at 13:01:54

SET   SCHEMA  GDZJ    ;

SET   CURRENT  PATH  =  "SYSIBM","SYSFUN","SYSPROC","GDZJ";

CREATE   FUNCTION  GDZJ.DEC_TO_TEN
 (pStr  VARCHAR ( 25 ),
  p_from_base  INTEGER
 ) 
   RETURNS   INTEGER
  LANGUAGE SQL
   NOT  DETERMINISTIC
  READS SQL DATA
  STATIC DISPATCH
  CALLED  ON   NULL  INPUT
  EXTERNAL ACTION
  INHERIT SPECIAL REGISTERS
   BEGIN  ATOMIC
  
   DECLARE  l_num  int   default   0 ; 
DECLARE  l_hex  varchar ( 16 )  default   ' 0123456789ABCDEF ' ; 
DECLARE  k  int   default   1 ; 
DECLARE  kcount  int ; 
DECLARE  posNum  int   default   0 ; 


set  kcount  =  length(pStr); 
WHILE  k  <= kcount DO 
set  posNum  =  locate( UPPER (SUBSTR(pStr,k, 1 )),l_hex) - 1 ; 
set  l_num  =  l_num  *  p_from_base  +  posNum; 
set  k = k + 1 ; 
END   WHILE ; 
RETURN  l_num;
   END ;
#SYNC  10 ;



--  End of generated script for server226-DB2-GDZJ (gdzj)

 

其中，来总结一下经验：

1：DB2本身没有16进制转换成10进制的函数，得自己搞一个。

2：DB2的函数用法，如SUBSTR,HEX,char,以及||等的用法，其实，很多java中有的基本方法，DB2中也有的。

3：java中的循环，DB2其实也有的。

4：能把该java的代码转换到DB2中的sql来，主要是由于该存储过程只是一个字符串的处理，没涉及到java中复杂的东西，这才是成功的关键。遇到不会的，多上google搜索下，总有解决的方法的。但我部署这个存储过程的错误，真的实在没找到解决的方法.......比较郁闷。

 

原创作品出自努力偷懒，转载请说明文章出处：http://www.cnblogs.com/kfarvid/

转载于:https://www.cnblogs.com/kfarvid/archive/2011/06/29/2093564.html