从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;
}
}
}
* 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)
-- 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)
-- 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/
