oracle xmltype导入并解析Excel数据 (三)解析Excel数据

2022-02-22 22:32:15

包声明

create or replace package PKG_EXCEL_UTILS is

-- Author: zkongbai
-- Create at: 2016-07-06
-- Action: 解析xmlType 类型的 Excel 数据
--============================================================================================
TYPE t_table_varchar IS TABLE OF VARCHAR2(4000);
TYPE cur IS ref cursor;
TYPE t_varry IS VARRAY(100) OF VARCHAR2(60);
TYPE rule_record IS RECORD(--注意,该记录类型取自2表
ID T_EXCEL_IMPORT_RULES.id%TYPE,
COLUMNCNNAME T_EXCEL_IMPORT_RULES.COLUMNNAME%TYPE,
RULE_EMPTY T_EXCEL_IMPORT_RULES.RULE_EMPTY%TYPE,
RULE_RANGE T_EXCEL_IMPORT_RULES.RULE_RANGE%TYPE,
RULE_DATETIME T_EXCEL_IMPORT_RULES.RULE_DATETIME%TYPE,
RULE_DATE_CUSTOMIZE T_EXCEL_IMPORT_RULES.RULE_DATE_CUSTOMIZE%TYPE,
RULE_UNIQUE T_EXCEL_IMPORT_RULES.RULE_UNIQUE%TYPE,
Rule_Phone T_EXCEL_IMPORT_RULES.Rule_Phone%TYPE,
RULE_NUMBER T_EXCEL_IMPORT_RULES.RULE_NUMBER%TYPE,
RULE_FUNC_CUSTOMIZE T_EXCEL_IMPORT_RULES.RULE_FUNC_CUSTOMIZE%TYPE,
RULE_FUNC_CUSTOMIZE_MSG T_EXCEL_IMPORT_RULES.RULE_FUNC_CUSTOMIZE_MSG%TYPE,
COLUMNENNAME T_EXCEL_IMPORT_MAPCOLUMN.COLUMNENNAME%TYPE,
Rule_Idcard T_EXCEL_IMPORT_RULES.Rule_Idcard%TYPE
);

-- Purpose: oracle 切割字符串函数
FUNCTION f_split(p_string IN VARCHAR2, p_delimiter IN VARCHAR2:=',') RETURN t_table_varchar PIPELINED;

-- Purpose: 判断某个字符串是否为日期格式,是则返回1,否则返回0
FUNCTION f_is_date(p_data IN VARCHAR2, p_format IN VARCHAR2:='yyyy-MM-dd hh24:mi:ss') RETURN NUMBER;

-- Purpose: 判断是否是数字格式
FUNCTION f_is_number(p_data IN VARCHAR2) RETURN NUMBER ;

-- Purpose: 判断一个值大于(大于或等于)另一个值
FUNCTION f_greater_than (p_str IN VARCHAR2,p_str2 IN VARCHAR2,p_equals IN NUMBER := 1) RETURN NUMBER;

-- Purpose: 判断是否是身份证号码
FUNCTION f_checkidcard (p_idcard IN VARCHAR2) RETURN NUMBER;

-- Purpose: 根据Id解析表T_EXCEL_IMPORT_DATASRC的xmltype数据
PROCEDURE p_parse_xmlData(p_id in number,p_insert_table in number := 0);

-- Purpose: 返回sheet的个数,需要在解析之后调用(准确的说需要生成规则类型后)
FUNCTION f_getSheetCount(p_id number) RETURN NUMBER ;

-- Purpose: 列名映射
PROCEDURE p_map_columnName(p_id number,p_sheet number,p_businesstype varchar2,p_deleteFirst number := 0);

-- Purpose: 规则校验之非唯一性规则验证
PROCEDURE p_check_rule(p_id number,p_sql varchar2,p_busnesstype_sheet varchar2);

--- Purpose: 规则验证之唯一性验证
PROCEDURE p_check_rule_unique(p_busnesstype_sheet in varchar2,p_businessid in number);

-- Purpose: 中间表数据插入具体业务数据表 (该存储过程只是总入口,真实插表入库不在此处)
-- 说明: 为防止修改编译困难问题,所有的真实业务需要在外部定义存储过程,并在规则表 T_EXCEL_IMPORT_RULES 添加对应businesstype_sheet添加该存储过程的名字
PROCEDURE p_insert_to_tables(p_businessid number,p_businesstype_sheet varchar2,p_insert_table number := 0);

-- Purpose: 错误日志记录表
PROCEDURE p_log(p_businessid number,p_errorMsg varchar2,p_lineNum VARCHAR2 := '-1',p_errorType varchar2 := 'INFO',p_errorCode varchar2 := '-1');
--=============================================================================================
/*--Excel 中xmlType格式如下
<?xml version="1.0"?>
<Root>
<Statistics>
<SheetCount>1</SheetCount>
<CellCount sheet="Sheet1">12</CellCount>

</Statistics>
<Sheet1>
<Header>
<Cell>单元名称</Cell>
<Cell>单元编号</Cell>
<Cell>单元状态</Cell>
<Cell>单元类型</Cell>
<Cell>合同开始时间</Cell>
<Cell>合同结束时间</Cell>
<Cell>套内面积(㎡)</Cell>
<Cell>建筑面积(㎡)</Cell>
<Cell>单元位置</Cell>
<Cell>合同编号</Cell>
<Cell>所在楼层</Cell>
<Cell>详细地址</Cell>
</Header>
<Datalist>
<Row>
<Cell1></Cell1>
<Cell2>单元编号1</Cell2>
<Cell3>单元状态1</Cell3>
<Cell4>单元类型1</Cell4>
<Cell5>合同开始时间1</Cell5>
<Cell6>合同结束时间2</Cell6>
<Cell7>套内面积(㎡)1</Cell7>
<Cell8>建筑面积(㎡)1</Cell8>
<Cell9>单元位置1</Cell9>
<Cell10>合同编号1</Cell10>
<Cell11>所在楼层1</Cell11>
<Cell12>详细地址1</Cell12>
</Row>
<Row>
<Cell1>12</Cell1>
<Cell2></Cell2>
<Cell3>3s</Cell3>
<Cell4>xn</Cell4>
<Cell5>2016-05-04</Cell5>
<Cell6>x</Cell6>
<Cell7>23</Cell7>
<Cell8>ss</Cell8>
<Cell9>uu</Cell9>
<Cell10>123</Cell10>
<Cell11>1</Cell11>
<Cell12>sd</Cell12>
</Row>
</Datalist>
</Sheet1>

</Root>

end PKG_EXCEL_UTILS;

包体

create or replace package body PKG_EXCEL_UTILS is
-- Author: zkongbai
-- Create at: 2016-07-06
-- Action: 解析xmlType 类型的 Excel 数据
-- 思路: 将Excel数据按一定的XML格式存储在表中,通过存储过程解析表记录数据
--============================================================================================

-- Purpose: oracle 切割字符串函数
-- 使用方法: select * from table(pkg_excel_utils.f_split('123,23'))
FUNCTION f_split(p_string IN VARCHAR2, p_delimiter IN VARCHAR2:=',') RETURN t_table_varchar PIPELINED IS
v_length NUMBER := LENGTH(p_string);
v_start NUMBER := 1;
v_index NUMBER;
BEGIN
WHILE (v_start <= v_length) LOOP
v_index := INSTR(p_string, p_delimiter, v_start);
IF v_index = 0
THEN
PIPE ROW(SUBSTR(p_string, v_start));
v_start := v_length + 1;
ELSE
PIPE ROW(SUBSTR(p_string, v_start, v_index - v_start));
v_start := v_index + 1;
END IF;
END LOOP;
RETURN;
END f_split;

-- Purpose: 判断是否是日期格式
FUNCTION f_is_date(p_data IN VARCHAR2, p_format IN VARCHAR2:='yyyy-MM-dd hh24:mi:ss') RETURN NUMBER IS
v_date Date;
Begin
execute immediate 'select to_date(:p_data,:p_format) from dual' into v_date using p_data,p_format;
return 1;
Exception
when others then
return 0;
End;

-- Purpose: 判断是否是数字格式
FUNCTION f_is_number(p_data IN VARCHAR2) RETURN NUMBER IS
v_temp number;
Begin
execute immediate 'select to_number(:p_data) from dual' into v_temp using p_data;
return 1;
Exception
when others then
return 0;
End;

-- Purpose: 判断一个值大于(大于或等于)另一个值,
-- 特别注意 1): (因业务需要)当任意一个参数为空的时候,返回真: 1
-- 参数说明: p_str,p_str2:待判定的字符串,p_equals=1表示两值相同也返回1
FUNCTION f_greater_than (p_str IN VARCHAR2,p_str2 IN VARCHAR2,p_equals IN NUMBER := 1) RETURN NUMBER IS
v_temp number := 0;
v_count number(1) := 0;
Begin
if p_str is null or p_str2 is null then
return 1;
end if;
if PKG_EXCEL_UTILS.f_is_date(p_str) = 1 and PKG_EXCEL_UTILS.f_is_date(p_str2) = 1 then --传入参数符合日期格式
v_temp := to_date(p_str,'yyyy-MM-dd hh24:mi:ss')-to_date(p_str2,'yyyy-MM-dd hh24:mi:ss');
elsif PKG_EXCEL_UTILS.f_is_number(p_str) = 1 and PKG_EXCEL_UTILS.f_is_number(p_str2) = 1 then--传入参数符合数字格式
v_temp := to_number(p_str)-to_number(p_str2);
else
--v_temp := ascii(p_str)-ascii(p_str);--该方法只比较第一个字符
if p_equals = 1 then
select case when p_str >= p_str2 then 1 else 0 end xxx into v_count from dual;
else
select case when p_str > p_str2 then 1 else 0 end xxx into v_count from dual;
end if;
return v_count;
end if;
if p_equals = 1 then
if v_temp >= 0 then return 1;
else return 0;end if;
else
if v_temp > 0 then return 1;
else return 0;end if;
end if;
Exception
when others then
dbms_output.put_line('-------比较错误啦------');
return 0;
End;
-- Purpose: 判断是否是身份证号码
FUNCTION f_checkidcard (p_idcard IN VARCHAR2) RETURN NUMBER IS
v_regstr VARCHAR2 (2000);
v_sum NUMBER;
v_mod NUMBER;
v_checkcode CHAR (11) := '10X98765432';
v_checkbit CHAR (1);
v_areacode VARCHAR2 (2000) := '11,12,13,14,15,21,22,23,31,32,33,34,35,36,37,41,42,43,44,45,46,50,51,52,53,54,61,62,63,64,65,71,81,82,91,';
BEGIN
CASE LENGTHB (p_idcard)
WHEN 15
THEN -- 15位
IF INSTRB (v_areacode, SUBSTR (p_idcard, 1, 2) || ',') = 0 THEN
RETURN 0;
END IF;
IF MOD (TO_NUMBER (SUBSTRB (p_idcard, 7, 2)) + 1900, 400) = 0 OR
(
MOD (TO_NUMBER (SUBSTRB (p_idcard, 7, 2)) + 1900, 100) <> 0
AND
MOD (TO_NUMBER (SUBSTRB (p_idcard, 7, 2)) + 1900, 4) = 0
)
THEN -- 闰年
v_regstr := '^[1-9][0-9]{5}[0-9]{2}((01|03|05|07|08|10|12)(0[1-9]|[1-2][0-9]|3[0-1])|(04|06|09|11)(0[1-9]|[1-2][0-9]|30)|02(0[1-9]|[1-2][0-9]))[0-9]{3}$';
ELSE
v_regstr := '^[1-9][0-9]{5}[0-9]{2}((01|03|05|07|08|10|12)(0[1-9]|[1-2][0-9]|3[0-1])|(04|06|09|11)(0[1-9]|[1-2][0-9]|30)|02(0[1-9]|1[0-9]|2[0-8]))[0-9]{3}$';
END IF;

IF REGEXP_LIKE (p_idcard, v_regstr) THEN
RETURN 1;
ELSE
RETURN 0;
END IF;
WHEN 18
THEN -- 18位
IF INSTRB (v_areacode, SUBSTRB (p_idcard, 1, 2) || ',') = 0 THEN
RETURN 0;
END IF;
IF MOD (TO_NUMBER (SUBSTRB (p_idcard, 7, 4)), 400) = 0 OR
(
MOD (TO_NUMBER (SUBSTRB (p_idcard, 7, 4)), 100) <> 0
AND
MOD (TO_NUMBER (SUBSTRB (p_idcard, 7, 4)), 4) = 0
)
THEN -- 闰年
v_regstr := '^[1-9][0-9]{5}(19|20)[0-9]{2}((01|03|05|07|08|10|12)(0[1-9]|[1-2][0-9]|3[0-1])|(04|06|09|11)(0[1-9]|[1-2][0-9]|30)|02(0[1-9]|[1-2][0-9]))[0-9]{3}[0-9Xx]$';
ELSE
v_regstr := '^[1-9][0-9]{5}(19|20)[0-9]{2}((01|03|05|07|08|10|12)(0[1-9]|[1-2][0-9]|3[0-1])|(04|06|09|11)(0[1-9]|[1-2][0-9]|30)|02(0[1-9]|1[0-9]|2[0-8]))[0-9]{3}[0-9Xx]$';
END IF;
IF REGEXP_LIKE (p_idcard, v_regstr) THEN
v_sum :=
( TO_NUMBER (SUBSTRB (p_idcard, 1, 1))+ TO_NUMBER (SUBSTRB (p_idcard, 11, 1))) * 7
+ ( TO_NUMBER (SUBSTRB (p_idcard, 2, 1))+ TO_NUMBER (SUBSTRB (p_idcard, 12, 1))) * 9
+ ( TO_NUMBER (SUBSTRB (p_idcard, 3, 1))+ TO_NUMBER (SUBSTRB (p_idcard, 13, 1))) * 10
+ ( TO_NUMBER (SUBSTRB (p_idcard, 4, 1))+ TO_NUMBER (SUBSTRB (p_idcard, 14, 1))) * 5
+ ( TO_NUMBER (SUBSTRB (p_idcard, 5, 1))+ TO_NUMBER (SUBSTRB (p_idcard, 15, 1))) * 8
+ ( TO_NUMBER (SUBSTRB (p_idcard, 6, 1))+ TO_NUMBER (SUBSTRB (p_idcard, 16, 1))) * 4
+ ( TO_NUMBER (SUBSTRB (p_idcard, 7, 1))+ TO_NUMBER (SUBSTRB (p_idcard, 17, 1))) * 2
+ TO_NUMBER (SUBSTRB (p_idcard, 8, 1)) * 1
+ TO_NUMBER (SUBSTRB (p_idcard, 9, 1)) * 6
+ TO_NUMBER (SUBSTRB (p_idcard, 10, 1))* 3;
v_mod := MOD (v_sum, 11);
v_checkbit := SUBSTRB (v_checkcode, v_mod + 1, 1);
IF v_checkbit = upper(substrb(p_idcard,18,1)) THEN
RETURN 1;
ELSE
RETURN 0;
END IF;
ELSE
RETURN 0;
END IF;
ELSE
RETURN 0;-- 身份证号码位数不对
END CASE;
EXCEPTION
WHEN OTHERS
THEN
RETURN 0;
END f_checkidcard;

FUNCTION f_getRangeColumn(p_splitstr varchar2,p_columnEnName varchar2,p_delimiter IN VARCHAR2:=',') RETURN VARCHAR2 IS
v_cur cur;
v_temp_result varchar2(50);
v_result varchar2(500) := '';
v_sql varchar2(100) := 'select column_value from table(PKG_EXCEL_UTILS.f_split(:p_splitstr,:p_delimiter))';
BEGIN
OPEN v_cur FOR v_sql using p_splitstr,p_delimiter;
Loop
Fetch v_cur into v_temp_result;
Exit When v_cur%NOTFOUND;
If v_temp_result is not null Then
v_temp_result := replace(v_temp_result,'''','');--去除所有单引号
If instr(v_temp_result,'-')>0 Then
v_result := v_result || ' OR '|| p_columnEnName || ' between '''||replace(v_temp_result,'-',''' and ''')||'''';
ElsIf instr(v_temp_result,'>=')>0 Then
v_result := v_result || ' OR '|| p_columnEnName || '>= '''||replace(v_temp_result,'>=','')||'''';
ElsIf instr(v_temp_result,'>')>0 Then
v_result := v_result || ' OR '|| p_columnEnName || '> '''||replace(v_temp_result,'>','')||'''';
ElsIf instr(v_temp_result,'<=')>0 Then
v_result := v_result || ' OR '|| p_columnEnName || '<= '''||replace(v_temp_result,'<=','')||'''';
ElsIf instr(v_temp_result,'<')>0 Then
v_result := v_result || ' OR '|| p_columnEnName || '< '''||replace(v_temp_result,'<','')||'''';
Else
v_result := v_result || ' OR '|| p_columnEnName || '= '''||replace(v_temp_result,'=','')||'''';
End if;
End if;
End Loop;
dbms_output.put_line(v_result);
Return v_result;
END f_getRangeColumn;

-- Purpose: 返回sheet的个数,需要在解析之后调用(准确的说需要生成规则类型后)
FUNCTION f_getSheetCount(p_id number) RETURN NUMBER IS
v_count number(2) := 0;
v_sql varchar2(500);
Begin
select count(*) into v_count FROM T_EXCEL_IMPORT_DATASRC t where id = p_id;
if v_count>0 then
v_sql := 'Select extractValue(t.xmldata,''/Root/Statistics/SheetCount/text()'') SheetCount FROM T_EXCEL_IMPORT_DATASRC t where t.id = :id';
execute immediate v_sql into v_count using p_id;--Excel中Sheet个数
end if;
return v_count;
End;

-- Purpose: 根据Id解析表T_EXCEL_IMPORT_DATASRC的xmltype数据
-- 参数说明 : p_id : 表T_EXCEL_IMPORT_DATASRC主键
-- p_insert_table: 生成的数据是否立即插入业务表,1表示立即插入,其余表示只插入中间表
PROCEDURE p_parse_xmlData(p_id in number,p_insert_table in number := 0) IS
v_count Number;
v_sheetCount Number;--sheet个数
v_column_count Number;--每个sheet中列的数量
v_busnesstype varchar2(50);--业务类型
v_from varchar2(100) := ' FROM T_EXCEL_IMPORT_DATASRC t where t.id = :id';
v_sql varchar2(4000);
v_sql_xmlTable varchar2(4000) := '';
Begin
execute immediate 'select count(*) '||v_from into v_count using p_id;
if v_count > 0 then
v_sql := 'Select t.BUSINESSTYPE, extractValue(t.xmldata,''/Root/Statistics/SheetCount/text()'') SheetCount' || v_from;
execute immediate v_sql into v_busnesstype,v_sheetCount using p_id;--Excel中Sheet个数
for sheetIndex$ in 1..v_sheetCount loop
p_map_columnName(p_id,sheetIndex$,v_busnesstype);--列名映射
v_sql := 'Select extractValue(t.xmldata,''/Root/Statistics/CellCount[@sheet="Sheet'||sheetIndex$||'"]/text()'') CellCount'||v_from;
execute immediate v_sql into v_column_count using p_id;
for columnIndex$ in 1..v_column_count loop
if columnIndex$ != v_column_count then
v_sql_xmlTable := v_sql_xmlTable || ' Cell'||columnIndex$||' VARCHAR2(4000) PATH ''Cell'||columnIndex$||''', ';
else
v_sql_xmlTable := v_sql_xmlTable || ' Cell'||columnIndex$||' VARCHAR2(4000) PATH ''Cell'||columnIndex$||''' ';
end if;
end loop;
v_sql := 'Select count(*) FROM T_EXCEL_IMPORT_DATASRC t, XMLTABLE (''/Root/Sheet'||sheetIndex$||'/Datalist/Row'' PASSING t.xmldata COLUMNS '||v_sql_xmlTable|| ' ) x Where t.id = :id';
execute immediate v_sql into v_count using p_id;
if v_count > 0 then
-- 表格展示xmltype数据
v_sql_xmlTable := 'Select t.id,t.businesstype||''_Sheet'||sheetIndex$||''',t.expandtype,x.* FROM T_EXCEL_IMPORT_DATASRC t, XMLTABLE (''/Root/Sheet'||sheetIndex$||'/Datalist/Row'' PASSING t.xmldata COLUMNS '||v_sql_xmlTable|| ' ) x Where t.id = :id';
dbms_output.put_line('xmltype数据: '||v_sql_xmlTable);
delete from T_EXCEL_IMPORT_GENERATION m where m.businessid = p_id and m.businesstype = v_busnesstype||'_Sheet'||sheetIndex$;
v_sql := 'insert into T_EXCEL_IMPORT_GENERATION(id,businessid,businesstype,expandtype';
for xi in 1..v_column_count loop
v_sql := v_sql||',CELL'||xi;
end loop;
v_sql := v_sql||') select SEQ_EXCEL_IMPROT.NEXTVAL,'||substr(v_sql_xmlTable,7);
execute immediate v_sql using p_id;
commit;
v_sql := 'select m.* from T_EXCEL_IMPORT_GENERATION m where m.businessid = :id and m.businesstype= '''||v_busnesstype||'_Sheet'||sheetIndex$||'''';
p_check_rule(p_id,v_sql,v_busnesstype||'_Sheet'||sheetIndex$);--规则判断
p_insert_to_tables(p_id,v_busnesstype||'_Sheet'||sheetIndex$,p_insert_table);
else
dbms_output.put_line('Excel没有数据体');
end if;
end loop;
end if;
Exception
when others then
dbms_output.put_line('解析xml数据失败:'||SQLCODE||' '||SQLERRM);
dbms_output.put_line('解析xml数据失败(sql语句): '||v_sql_xmlTable);
dbms_output.put_line('解析xml数据失败(插入中间表sql语句): '||v_sql);
dbms_output.put_line('原始错误信息: 行号:'||dbms_utility.format_error_backtrace()||' '||sqlerrm);
p_log(p_id,'解析xml数据失败(sql语句): '||v_sql_xmlTable);
p_log(p_id,'解析xml数据失败(插入中间表sql语句) '||v_sql);
p_log(p_id,sqlerrm,dbms_utility.format_error_backtrace(),'ERROR',SQLCODE);
raise;
END p_parse_xmlData;

-- Purpose: 规则校验之非唯一性规则验证
PROCEDURE p_check_rule(p_id number,p_sql varchar2,p_busnesstype_sheet varchar2) is
v_count number := 0;
v_str varchar2(100);
v_columnCnName varchar2(50);
v_cur cur;
--v_rules T_EXCEL_IMPORT_RULES%Rowtype;
v_rules rule_record;
v_sql varchar2(4000);
v_query varchar2(4000) := '';
v_where varchar2(4000) := '';
Begin
select count(*) into v_count from T_EXCEL_IMPORT_RULES t,T_EXCEL_IMPORT_MAPCOLUMN m where t.COLUMNNAME = m.COLUMNCNNAME and t.businesstype_sheet = m.BUSINESSTYPE_SHEET and t.businesstype_sheet = p_busnesstype_sheet;
if v_count>0 then --如果含有规则类型
v_sql := 'select t.id,t.COLUMNNAME as COLUMNCNNAME,t.RULE_EMPTY,t.RULE_RANGE,t.RULE_DATETIME,t.RULE_DATE_CUSTOMIZE,t.RULE_UNIQUE,t.Rule_Phone,t.RULE_NUMBER,t.RULE_FUNC_CUSTOMIZE,t.RULE_FUNC_CUSTOMIZE_MSG,m.COLUMNENNAME,t.Rule_Idcard from T_EXCEL_IMPORT_RULES t,T_EXCEL_IMPORT_MAPCOLUMN m where t.COLUMNNAME = m.COLUMNCNNAME and t.businesstype_sheet = m.BUSINESSTYPE_SHEET and t.businesstype_sheet = :p_busnesstype_sheet';
open v_cur for v_sql using p_busnesstype_sheet;
Loop
Fetch v_cur into v_rules;
Exit When v_cur%NOTFOUND;
if v_rules.RULE_EMPTY = '1' then --非空验证if;
--采用case when 方式比decode 效率更高,而且书写方便
v_query := v_query || 'when '||v_rules.COLUMNENNAME||' is null then ''【'||v_rules.COLUMNCNNAME||'】不能为空'' ';
v_where := v_where|| ' OR A.'||v_rules.COLUMNENNAME||' is null';
end if;
if v_rules.RULE_RANGE is not null then--大于另一列值的判断
--f_getRangeColumn(v_rules.RULE_RANGE,v_rules.COLUMNENNAME);
if instr(v_rules.RULE_RANGE,'>=')>0 then
v_columnCnName := substr(v_rules.RULE_RANGE,3);
select count(*) into v_count from T_EXCEL_IMPORT_MAPCOLUMN t where t.businesstype_sheet = p_busnesstype_sheet and t.columncnname = v_columnCnName;
if v_count = 1 then
select t.columnenname into v_str from T_EXCEL_IMPORT_MAPCOLUMN t where t.businesstype_sheet = p_busnesstype_sheet and t.columncnname = v_columnCnName;
v_query := v_query||'when PKG_EXCEL_UTILS.f_greater_than('||v_rules.COLUMNENNAME||','||v_str||')=0 then ''【'||v_rules.COLUMNCNNAME||'】需要大于【'||v_columnCnName||'】'' ';
v_where := v_where||' OR PKG_EXCEL_UTILS.f_greater_than('||v_rules.COLUMNENNAME||','||v_str||')=0';
end if;
elsif instr(v_rules.RULE_RANGE,'>')>0 then
v_columnCnName := substr(v_rules.RULE_RANGE,2);
select count(*) into v_count from T_EXCEL_IMPORT_MAPCOLUMN t where t.businesstype_sheet = p_busnesstype_sheet and t.columncnname = v_columnCnName;
if v_count = 1 then
select t.columnenname into v_str from T_EXCEL_IMPORT_MAPCOLUMN t where t.businesstype_sheet = p_busnesstype_sheet and t.columncnname = v_columnCnName;
v_query := v_query||'when PKG_EXCEL_UTILS.f_greater_than('||v_rules.COLUMNENNAME||','||v_str||',0)=0 then ''【'||v_rules.COLUMNCNNAME||'】需要大于【'||v_columnCnName||'】'' ';
v_where := v_where||' OR PKG_EXCEL_UTILS.f_greater_than('||v_rules.COLUMNENNAME||','||v_str||',0)=0';
end if;
end if;
end if;
if v_rules.RULE_DATETIME = '1' then
v_query := v_query||' when PKG_EXCEL_UTILS.f_is_date('||v_rules.COLUMNENNAME||')=0 then ''【'||v_rules.COLUMNCNNAME||'】不是合法的日期格式'' ';
v_where := v_where||' OR PKG_EXCEL_UTILS.f_is_date('||v_rules.COLUMNENNAME||')=0';
end if;
if v_rules.RULE_DATE_CUSTOMIZE is not null then
v_query := v_query||'when PKG_EXCEL_UTILS.f_is_date('||v_rules.COLUMNENNAME||','''||v_rules.RULE_DATE_CUSTOMIZE||''')=0 then ''【'||v_rules.COLUMNCNNAME||'】不是合法的日期格式'' ';
v_where := v_where||' OR PKG_EXCEL_UTILS.f_is_date('||v_rules.COLUMNENNAME||','''||v_rules.RULE_DATE_CUSTOMIZE||''')=0';
end if;
if v_rules.Rule_Phone is not null then
if v_rules.Rule_Phone = '1' then--手机号码
v_query := v_query||'when not REGEXP_LIKE(nvl('||v_rules.COLUMNENNAME||',''#''),''^#$|^1[3458]\d{9}$'') then ''【'||v_rules.COLUMNCNNAME||'】不是合法的手机号码格式'' ';
v_where := v_where||' OR not REGEXP_LIKE(nvl('||v_rules.COLUMNENNAME||',''#''),''^#$|^1[3458]\d{9}$'')';
elsif v_rules.Rule_Phone = '2' then--固话
v_query := v_query||'when not REGEXP_LIKE(nvl('||v_rules.COLUMNENNAME||',''#''),''^#$|^(010|02\d|0[3-9]\d{2})?\d{6,8}$'') then ''【'||v_rules.COLUMNCNNAME||'】不是合法的固定号码格式'' ';
v_where := v_where||' OR not REGEXP_LIKE(nvl('||v_rules.COLUMNENNAME||',''#''),''^#$|^(010|02\d|0[3-9]\d{2})?\d{6,8}$'')';
elsif v_rules.Rule_Phone = '3' then--手机或固话
v_query := v_query||'when not REGEXP_LIKE(nvl('||v_rules.COLUMNENNAME||',''#''),''^#$|^1[3458]\d{9}$|^(010|02\d|0[3-9]\d{2})?\d{6,8}$'') then ''【'||v_rules.COLUMNCNNAME||'】不是合法的手机或固话号码格式'' ';
v_where := v_where||' OR not REGEXP_LIKE(nvl('||v_rules.COLUMNENNAME||',''#''),''^#$|^1[3458]\d{9}$|^(010|02\d|0[3-9]\d{2})?\d{6,8}$'')';
end if;
end if;
if v_rules.RULE_NUMBER is not null then
if v_rules.RULE_NUMBER = '1' then--整数
v_query := v_query||'when not REGEXP_LIKE(nvl('||v_rules.COLUMNENNAME||',''#''),''^#$|^-?[1-9]\d*$'') then ''【'||v_rules.COLUMNCNNAME||'】不是合法的数字格式'' ';
v_where := v_where||' OR not REGEXP_LIKE(nvl('||v_rules.COLUMNENNAME||',''#''),''^#$|^-?[1-9]\d*$'')';
end if;
if v_rules.RULE_NUMBER = '2' then--小数
v_query := v_query||'when not REGEXP_LIKE(nvl('||v_rules.COLUMNENNAME||',''#''),''^#$|^-?[1-9]\d*$|^-?([1-9]\d*\.\d*|0\.\d*[1-9]\d*|0?\.0+|0)$'') then ''【'||v_rules.COLUMNCNNAME||'】不是合法的数字格式'' ';
v_where := v_where||' OR not REGEXP_LIKE(nvl('||v_rules.COLUMNENNAME||',''#''),''^#$|^-?[1-9]\d*$|^-?([1-9]\d*\.\d*|0\.\d*[1-9]\d*|0?\.0+|0)$'')';
end if;
end if;
if v_rules.Rule_Idcard = '1' then
v_query := v_query||'when PKG_EXCEL_UTILS.f_checkidcard('||v_rules.COLUMNENNAME||')=0 then ''【'||v_rules.COLUMNCNNAME||'】不是合法的身份证格式'' ';
v_where := v_where||' OR PKG_EXCEL_UTILS.f_checkidcard('||v_rules.COLUMNENNAME||')=0';
end if;
if v_rules.RULE_FUNC_CUSTOMIZE is not null then --调用用户自定义函数
select count(*) into v_count from user_source t where t.TYPE = 'FUNCTION' AND t.name = upper(v_rules.RULE_FUNC_CUSTOMIZE) and t.line = 1 and instr(upper(t.TEXT),'NUMBER')>0;
if v_count >0 then --函数合法性验证
v_query := v_query||'when '||v_rules.RULE_FUNC_CUSTOMIZE||'('||v_rules.COLUMNENNAME||')=0 then ''【'||v_rules.COLUMNCNNAME||'】'||v_rules.RULE_FUNC_CUSTOMIZE_MSG||''' ';
v_where := v_where||' OR '||v_rules.RULE_FUNC_CUSTOMIZE||'('||v_rules.COLUMNENNAME||')=0';
end if;
end if;
End Loop;
close v_cur;
if length(v_query)>0 then
v_query := 'case '|| v_query||' end errmsg';
v_where := ' AND '||substr(v_where,4);--将第一个OR替换成AND
v_sql := 'with A as ('||p_sql||') select '||v_query||',A.* from A where 1=1 '||v_where;
dbms_output.put_line('规则验证语句'||length(v_sql)||': '||v_sql);
v_sql := 'update T_EXCEL_IMPORT_GENERATION k set k.errormsg = (select p.errmsg from ('||v_sql||') p where p.ID = k.id) where k.businesstype=:businesstype_sheet and k.businessid = :businessid';
execute immediate v_sql using p_id,p_busnesstype_sheet,p_id;
commit;
end if;
--判断唯一性规则
p_check_rule_unique(p_busnesstype_sheet,p_id);
end if;
Exception
when others then
dbms_output.put_line('规则校验失败:'||SQLCODE||' '||SQLERRM);
dbms_output.put_line('规则校验失败(更新字段语句): '||v_sql);
dbms_output.put_line('原始错误信息: 行号:'||dbms_utility.format_error_backtrace()||' '||sqlerrm);
p_log(p_id,'规则校验失败(更新字段语句):) '||v_sql);
p_log(p_id,sqlerrm,dbms_utility.format_error_backtrace(),'ERROR',SQLCODE);
raise;
End p_check_rule;

--- Purpose: 规则验证之唯一性验证
PROCEDURE p_check_rule_unique(p_busnesstype_sheet in varchar2,p_businessid in number) is
v_key varchar2(100);
v_varray t_varry := t_varry();--空数组
v_cur cur;
v_sql varchar2(500);
v_count number(3);
begin
open v_cur for 'select t.columnenname||'',''||t.columnname from T_EXCEL_IMPORT_RULES t where t.businesstype_sheet = :businesstype_sheet and t.rule_unique = 1' using p_busnesstype_sheet;
Loop
Fetch v_cur into v_key;
Exit When v_cur%NOTFOUND;
--dbms_output.put_line(v_cur%rowcount||': '||v_key);
v_varray.EXTEND(1);
v_varray(v_cur%rowcount) := v_key;
End loop;
close v_cur;
v_count := v_varray.COUNT();
if v_count>0 then
v_sql := 'update T_EXCEL_IMPORT_GENERATION p set p.errormsg =''数据重复[';
for i in 1..v_count loop
--列中文名: substr(v_varray(i),instr(v_varray(i),',')+1),
--列名: substr(v_varray(i),1,instr(v_varray(i),',')-1)
v_sql := v_sql||substr(v_varray(i),instr(v_varray(i),',')+1)|| ' ';
end loop;
v_sql := v_sql||'确定唯一数据]'' where p.id in ( select t.id from T_EXCEL_IMPORT_GENERATION t where exists(select ';
for i in 1..v_count loop
v_sql := v_sql||'m.'||substr(v_varray(i),1,instr(v_varray(i),',')-1)||',';
end loop;
v_sql := substr(v_sql,0,length(v_sql)-1);--除去最后一个逗号
v_sql := v_sql||' from T_EXCEL_IMPORT_GENERATION m where 1=1 ';
for i in 1..v_count loop
v_sql := v_sql||' and t.'||substr(v_varray(i),1,instr(v_varray(i),',')-1)||'=m.'||substr(v_varray(i),1,instr(v_varray(i),',')-1);
end loop;
v_sql := v_sql||' and m.businessid = :businessid group by ';
for i in 1..v_count loop
v_sql := v_sql||'m.'||substr(v_varray(i),1,instr(v_varray(i),',')-1)||',';
end loop;
v_sql := substr(v_sql,0,length(v_sql)-1);--除去最后一个逗号
v_sql := v_sql||' having count(*)>1) and t.businessid = :businessid) and p.businessid = :businessid';
dbms_output.put_line(v_sql);
execute immediate v_sql using p_businessid,p_businessid,p_businessid;
commit;
end if;
/*
update T_EXCEL_IMPORT_GENERATION p set p.errormsg = '数据重复[Cell1中文,Cell3中文确定唯一数据]'
where p.id in (
select t.id from T_EXCEL_IMPORT_GENERATION t where exists(
select m.cell1,m.cell3 from T_EXCEL_IMPORT_GENERATION m
where 1=1 and t.cell1 = m.cell1 and t.cell3 = m.cell3 and m.businessid = 1
group by m.cell1,m.cell3
having count(*)>1) and t.businessid = 1
) and p.businessid = 1
*/
Exception
when others then
dbms_output.put_line('规则(唯一性)校验失败:'||SQLCODE||' '||SQLERRM);
dbms_output.put_line('规则唯一性)校验失败(更新字段语句): '||v_sql);
dbms_output.put_line('原始错误信息: 行号:'||dbms_utility.format_error_backtrace()||' '||sqlerrm);
p_log(p_businessid,'规则唯一性)校验失败(更新字段语句):'||v_sql);
p_log(p_businessid,sqlerrm,dbms_utility.format_error_backtrace(),'ERROR',SQLCODE);
raise;
end;

-- Purpose: 列名映射 : 将Excel表头中文名映射出列名,并存储在表T_EXCEL_IMPORT_MAPCOLUMN中
-- 参数说明 : 表T_EXCEL_IMPORT_DATASRC主键,p_businesstype: 业务类型,p_sheet: Excel第几个sheet,p_deleteFirst: 是否先删除数据,1:表示先删除数据后操作
PROCEDURE p_map_columnName(p_id number,p_sheet number,p_businesstype varchar2,p_deleteFirst number := 0) is
v_count number;
v_sql varchar2(4000);
v_temp varchar2(500);
Begin
select count(*) into v_count from T_EXCEL_IMPORT_MAPCOLUMN t where t.businesstype_sheet = p_businesstype||'_Sheet'||p_sheet;
if p_deleteFirst = 1 and v_count>0 then
delete from T_EXCEL_IMPORT_MAPCOLUMN t where t.businesstype_sheet = p_businesstype||'_Sheet'||p_sheet;
v_count := 0;
end if;
if v_count = 0 then
--使用业务类型+ _Sheet数作为规则的判断条件
v_sql := 'insert into T_EXCEL_IMPORT_MAPCOLUMN select SEQ_EXCEL_IMPROT.NEXTVAL,t.businesstype||'''||'_Sheet'||p_sheet||''',''Cell''||rownum ColumnEnName,extractValue(value(i),''/Cell'') ColumnCnName FROM T_EXCEL_IMPORT_DATASRC t,table(XMLSequence(extract(t.xmldata,''/Root/Sheet'||p_sheet||'/Header/Cell''))) i where id=:id';
dbms_output.put_line('列名映射: '||v_sql);
execute immediate v_sql using p_id;
commit;
end if;
select count(*) into v_count from T_EXCEL_IMPORT_RULES t where t.businesstype_sheet = p_businesstype||'_Sheet'||p_sheet;
if v_count = 0 then
v_temp := 'p_'||p_businesstype||'_Sheet'||p_sheet;
select count(*) into v_count from user_source t where t.TYPE = 'PROCEDURE' and t.line = 1 and t.name = upper(v_temp);
if v_count >0 then
v_temp := 'pz'||v_temp;--自动生成存储过程的名字
end if;

insert into T_EXCEL_IMPORT_RULES(id,BUSINESSTYPE_SHEET,COLUMNENNAME,COLUMNNAME,PROCEDURE_NAME)
select SEQ_EXCEL_IMPROT.NEXTVAL,BUSINESSTYPE_SHEET,COLUMNENNAME,COLUMNCNNAME,v_temp from T_EXCEL_IMPORT_MAPCOLUMN t
where t.businesstype_sheet = p_businesstype||'_Sheet'||p_sheet;
end if;
commit;
End p_map_columnName;

-- Purpose: 中间表数据插入具体业务数据表 (该存储过程只是总入口,真实插表入库不在此处)
-- 说明: 为防止修改编译困难问题,所有的真实业务需要在外部定义存储过程,并在规则表 T_EXCEL_IMPORT_RULES 添加对应businesstype_sheet添加该存储过程的名字
-- 参数说明 : p_businessid 业务id, p_insert_table插入业务表标识: [1表示插入与业务数据表]
-- businesstype_sheet第几个sheet的业务类型(格式例子: 业务类型_Sheet1),
PROCEDURE p_insert_to_tables(p_businessid number,p_businesstype_sheet varchar2,p_insert_table number := 0) IS
v_count number(3);
v_str varchar2(200);
no_procedure_exception exception;--未定义合法的插入数据的存储过程异常
pragma exception_init(no_procedure_exception, -20000);
Begin
if p_insert_table is not null and p_insert_table = 1 then --插入业务数据表
/*
特别说明:
1) 中间表: T_EXCEL_IMPORT_GENERATION, errormsg 为空说明格式是合法的格式
2) 即使cell单元格为空,to_date(),也不会报错,前提是该cell单元格字符串不为空的元素符合日期/数字格式--小技巧:如果不可以插入,则考虑nvl()默认值后,update该默认值
3) 使用Merge语句来更新与插入数据到业务表

insert into test_date_num(t_d,t_n)
select to_date(cell5,'yyyy-MM-dd hh24:mi:ss'),to_number(cell11) from T_EXCEL_IMPORT_GENERATION t;
*/
select count(*) into v_count from T_EXCEL_IMPORT_RULES t where t.businesstype_sheet = p_businesstype_sheet and t.procedure_name is not null;
if v_count >0 then
select t.procedure_name into v_str from T_EXCEL_IMPORT_RULES t where t.businesstype_sheet = p_businesstype_sheet and t.procedure_name is not null and rownum <2 ;
select count(*) into v_count from user_source t where t.TYPE = 'PROCEDURE' and t.line = 1 and t.name = upper(v_str);
if v_count>0 then
execute immediate 'call '||v_str||'(:p_businessid)' using p_businessid;
commit;
else
raise no_procedure_exception;
end if;
else
raise no_procedure_exception;
end if;
end if;
Exception
when no_procedure_exception then
dbms_output.put_line('没有为业务类型:'||p_businesstype_sheet||' 定义合法的插入数据的存储过程.请在规则表 T_EXCEL_IMPORT_RULES 添加该类型的存储过程并实现存储过程(该过程只有一个number类型参数)');
dbms_output.put_line('原始错误信息: 行号:'||dbms_utility.format_error_backtrace()||' '||sqlerrm);
p_log(p_businessid,'没有为业务类型:'||p_businesstype_sheet||' 定义合法的插入数据的存储过程.请在规则表 T_EXCEL_IMPORT_RULES 添加该类型的存储过程并实现存储过程(该过程只有一个number类型参数)');
p_log(p_businessid,sqlerrm,dbms_utility.format_error_backtrace(),'ERROR',SQLCODE);
--raise;
when others then
dbms_output.put_line('插入中间表数据执行异常存储过程: '||v_str);
dbms_output.put_line('原始错误信息: 行号:'||dbms_utility.format_error_backtrace()||' '||sqlerrm);
p_log(p_businessid,'插入中间表数据执行异常存储过程:'||v_str);
p_log(p_businessid,sqlerrm,dbms_utility.format_error_backtrace(),'ERROR',SQLCODE);
raise;
END p_insert_to_tables;

-- Purpose: 错误日志记录表
-- 参数说明: p_businessid:业务主键,p_errorMsg:错误描述,p_lineNum:错误行号
PROCEDURE p_log(p_businessid number,p_errorMsg varchar2,p_lineNum VARCHAR2 := '-1',p_errorType varchar2 := 'INFO',p_errorCode varchar2 := '-1') IS
BEGIN
insert into T_EXCEL_IMPORT_LOG(id,BUSINESSID,ERRORCODE,ERRORMSG,ERRORTYPE,LINE_NO)
values
(SEQ_EXCEL_LOG.NEXTVAL,p_businessid,p_errorCode,p_errorMsg,p_errorType,p_lineNum);
commit;
END P_LOG;

Begin
null;
end PKG_EXCEL_UTILS;

码农公寓

相关文章