17,078
社区成员
发帖
与我相关
我的任务
分享
FUNCTION ETL_FUN_CLEAR(i_table_name IN VARCHAR2,
i_column_name IN VARCHAR2,
i_value IN VARCHAR2) RETURN VARCHAR2 IS
v_method_count number;
v_value varchar2(200);
v_method varchar2(100);
v_tablename varchar2(100);
v_columnname varchar2(100);
BEGIN
v_value := i_value;
v_value := ETL_PAC_CharConverterUtil.toHalfAngle(v_value);
return v_value;
exception
when others then
ETL_PAC_BASE.ETL_PRO_ADDLOG('ETL_PAC_CLEAR',
'ETL_FUN_CLEAR',
'',
SQLCODE || '-' || v_method || '-' ||
v_tablename || '-' || v_columnname || '-' || v_value,
SQLERRM);
return v_value;
END ETL_FUN_CLEAR;
create or replace package body ETL_PAC_CharConverterUtil is
-- Private type declarations
--type <TypeName> is <Datatype>;
-- Private constant declarations
--<ConstantName> constant <Datatype> := <Value>;
-- Private variable declarations
--<VariableName> <Datatype>;
-- Function and procedure implementations
function toHalfAngle(v_value varchar2) return varchar2 AS
language java name 'ClearCharUtil.toHalfAngle(java.lang.String) return java.lang.String';
function removeAllChar(v_value varchar2) return varchar2 as
language java name 'ClearCharUtil.removeAllChar(java.lang.String) return java.lang.String';
function removeBlank(v_value varchar2) return varchar2 as
language java name 'ClearCharUtil.removeBlank(java.lang.String) return java.lang.String';
function removeSpareBlank(v_value varchar2) return varchar2 as
language java name 'ClearCharUtil.removeSpareBlank(java.lang.String) return java.lang.String';
--begin
-- Initialization
--<Statement>;
end ETL_PAC_CharConverterUtil;
create or replace and compile java source named "ClearCharUtil" as
import java.util.List;
import java.util.regex.Pattern;
public class ClearCharUtil {
/**
* 全角转半角 半角字符串
*
* @return String
*/
public static String toHalfAngle(String str) {
char c[] = str.toCharArray();
for (int i = 0; i < c.length; i++) {
if (c[i] == '\\u3000') {
c[i] = ' ';
} else if (c[i] > '\\uFF00' && c[i] < '\\uFF5F') {
c[i] = (char) (c[i] - 65248);
}
}
String outStr = new String(c);
return outStr;
}
}
public static String toHalfAngle(String str) {
char c[] = str.toCharArray();
for (int i = 0; i < c.length; i++) {
if (c[i] == '\\u3000') {
c[i] = ' ';
} else if (c[i] > '\\uFF00' && c[i] < '\\uFF5F') {
c[i] = (char) (c[i] - 65248);
}
}
String outStr = new String(c);
return outStr;
}
SQL> select to_single_byte('计算.()机') from dual;
TO_SINGLE
---------
计算.()机
SQL> select to_multi_byte('计算.()机') from dual;
TO_MULTI_BYT
------------
计算.()机
update Temp_PrpDcstP
set IdentifyType = ETL_FUN_CLEAR('PRPDCSTP',
'IDENTIFYTYPE',
cur.IdentifyType),
IdentifyNumber = ETL_FUN_CLEAR('PRPDCSTP',
'IDENTIFYNUMBER',
cur.IdentifyNumber),
DateValid = ETL_FUN_CLEAR('PRPDCSTP',
'DATEVALID',
cur.DateValid),
Gender = ETL_FUN_CLEAR('PRPDCSTP',
'GENDER',
cur.Gender),
BirthDate = ETL_FUN_CLEAR('PRPDCSTP',
'BIRTHDATE',
cur.BirthDate),
DeathDate = ETL_FUN_CLEAR('PRPDCSTP',
'DEATHDATE',
cur.DeathDate),
Health = ETL_FUN_CLEAR('PRPDCSTP',
'HEALTH',
cur.Health),
Stature = ETL_FUN_CLEAR('PRPDCSTP',
'STATURE',
cur.Stature),
Weight = ETL_FUN_CLEAR('PRPDCSTP',
'WEIGHT',
cur.Weight),
Marriage = ETL_FUN_CLEAR('PRPDCSTP',
'MARRIAGE',
cur.Marriage),
Nationality = ETL_FUN_CLEAR('PRPDCSTP',
'NATIONALITY',
cur.Nationality),
HouseProperty = ETL_FUN_CLEAR('PRPDCSTP',
'HOUSEPROPERTY',
cur.HouseProperty),
RegionCode = ETL_FUN_CLEAR('PRPDCSTP',
'REGIONCODE',
cur.RegionCode),
FamilySumQuantity = ETL_FUN_CLEAR('PRPDCSTP',
'FAMILYSUMQUANTITY',
cur.FamilySumQuantity),
BloodType = ETL_FUN_CLEAR('PRPDCSTP',
'BLOODTYPE',
cur.BloodType),
Favourite = ETL_FUN_CLEAR('PRPDCSTP',
'FAVOURITE',
cur.Favourite),
Interest = ETL_FUN_CLEAR('PRPDCSTP',
'INTEREST',
cur.Interest),
SelfMonthIncome = ETL_FUN_CLEAR('PRPDCSTP',
'SELFMONTHINCOME',
cur.SelfMonthIncome),
SmIncomeCurrency = ETL_FUN_CLEAR('PRPDCSTP',
'SMINCOMECURRENCY',
cur.SmIncomeCurrency),
FamilyMonthIncome = ETL_FUN_CLEAR('PRPDCSTP',
'FAMILYMONTHINCOME',
cur.FamilyMonthIncome),
FmIncomeCurrency = ETL_FUN_CLEAR('PRPDCSTP',
'FMINCOMECURRENCY',
cur.FmIncomeCurrency)
where CustomerCode = cur.CustomerCode;