3,490
社区成员
发帖
与我相关
我的任务
分享
PROCEDURE proc_import_xml_info
(
p_seqno IN VARCHAR2,
p_o_retmsg OUT VARCHAR2
) IS
v_tmp_msg VARCHAR2(4000);
xml_parser xmlparser.parser;
vs_xml dat_aas_xml_text.text%TYPE;
vd_doc xmldom.domdocument;
vn_docnode xmldom.domnode;
vs_sql VARCHAR2(8000);
vs_vsql VARCHAR2(8000);
vi_depest_fno INTEGER;
BEGIN
--获取需要处理的数据
BEGIN
SELECT t.text
INTO vs_xml
FROM A t
WHERE t.id=p_seqno ;
EXCEPTION
WHEN OTHERS THEN
v_tmp_msg := '获取XML文本出错';
ROLLBACK;
END;
--解析XML
xml_parser := xmlparser.newparser;
xmlparser.setvalidationmode(xml_parser, FALSE);
xmlparser.parseclob(xml_parser, vs_xml);
vd_doc := xmlparser.getdocument(xml_parser);
xmlparser.freeparser(xml_parser);
vn_docnode := xmldom.makenode(xmldom.getdocumentelement(vd_doc));
--解析完成,按XML内字段处理
IF NOT xmldom.isnull(vd_doc) THEN
SELECT MAX(t.forest_no)
INTO vi_depest_fno
FROM par_aas_xml_model t
WHERE t.fcode = p_fcode;
BEGIN
r_split_xml(p_fcode, '', vn_docnode, vd_doc, vi_depest_fno, vs_sql, vs_vsql);
EXCEPTION
WHEN OTHERS THEN
p_o_retmsg := 'xml解析异常' || SQLERRM;
RETURN;
END;
COMMIT;
EXCEPTION
WHEN OTHERS THEN
ROLLBACK;
END;
PROCEDURE r_split_xml
(
is_fcode IN VARCHAR2, --文件类型
ii_pforest_no IN INTEGER, --当前结点层次序号
in_node IN xmldom.domnode, --当前结点
id_doc IN xmldom.domdocument, --XML文档对象
ii_depest_fno IN INTEGER, --最末层次序号
vs_sql IN OUT VARCHAR2, --缓存SQL语句1
vs_value_sql IN OUT VARCHAR2 --缓存SQL语句2
) AS
vn_node xmldom.domnode;
len INTEGER := 0;
vn_nlist xmldom.domnodelist;
vs_node_name VARCHAR2(200);
vi_count INTEGER;
vs_column_name config_table_sub.table_column%TYPE;
vs_this_sql VARCHAR2(8000) := vs_sql;
vs_this_vsql VARCHAR2(8000) := vs_value_sql;
BEGIN
FOR vr_splits IN (SELECT *
FROM config_table
WHERE fcode = is_fcode
AND (parent_forest = ii_pforest_no OR (parent_forest IS NULL AND ii_pforest_no IS NULL)))
LOOP
IF ii_pforest_no IS NULL THEN
vs_this_sql := 'INSERT INTO ' || vr_splits.recieve_table_name || '(';
vs_this_vsql := ' VALUES(';
vs_sql := vs_this_sql;
vs_value_sql := vs_this_vsql;
END IF;
--获取当前层次的子节点集合
vn_nlist := xmldom.getchildnodes(in_node);
len := xmldom.getlength(vn_nlist);
--如果存在该层子结点的数据,则按config_table_sub中该层的NODE_NAME解析
IF len > 0 THEN
FOR i IN 0 .. len - 1
LOOP
vn_node := xmldom.item(vn_nlist, i);
vs_node_name := xmldom.getnodename(vn_node);
SELECT COUNT(1)
INTO vi_count
FROM config_table t
WHERE t.parent_forest = vr_splits.forest_no
AND t.node_name = vs_node_name;
IF vi_count >= 1 THEN
--如果子节点名,以该节点循环处理该节点下的子结点
r_split_xml(is_fcode, nvl(ii_pforest_no, 0) + 1, vn_node, id_doc, ii_depest_fno, vs_sql, vs_value_sql);
ELSE
--如果是普通结点,判断是否需要插入表中。
vs_column_name := '';
BEGIN
SELECT t.table_column
INTO vs_column_name
FROM config_table_sub t
WHERE t.f_code = is_fcode
AND t.forest_no = vr_splits.forest_no
AND t.node_name = vs_node_name;
EXCEPTION
WHEN no_data_found THEN
NULL;
WHEN OTHERS THEN
ROLLBACK;
RAISE;
END;
IF vs_column_name IS NOT NULL THEN
vs_this_sql := vs_this_sql || vs_column_name || ',';
vs_this_vsql := vs_this_vsql || '''' || xmldom.getnodevalue(xmldom.getfirstchild(vn_node)) || ''',';
END IF;
IF vr_splits.forest_no < ii_depest_fno THEN
--如果还没到最末节点,缓存
vs_sql := vs_this_sql;
vs_value_sql := vs_this_vsql;
END IF;
END IF;
END LOOP;
END IF;
IF vr_splits.forest_no = ii_depest_fno THEN
--如果到达最末节点,则拼接完成SQL语句,执行操作。
--接收表都定义了以下字段,所以需要处理
vs_this_sql := vs_this_sql || 'DATA_DATE,DATA_WAY,DATA_TIME,IS_DEALED )';
vs_this_vsql := vs_this_vsql || 'to_char(sysdate,''YYYYMMDD''),''1'',sysdate,''1'')';
dbms_output.put_line('insert_sql :' || vs_this_sql || vs_this_vsql);
BEGIN
EXECUTE IMMEDIATE vs_this_sql || vs_this_vsql;
EXCEPTION
WHEN OTHERS THEN
ROLLBACK;
RAISE;
END;
END IF;
END LOOP;
EXCEPTION
WHEN OTHERS THEN
ROLLBACK;
RAISE;
END r_split_xml;