模拟生产数据导入（二）

       接模拟生产数据导入（一），本节主要进行OracleXmltype 数据操作。

给学生表、成绩表、课程表、选课表加上extend表，存放clob类型数据

CreateTable temp_student_extend(stu_code Number,stu_xmlsys.Xmltype);

CreateTable temp_score_extend(sco_code Number,sco_xmlsys.Xmltype);

CreateTable temp_course_extend(cou_code Number,cou_xml sys.Xmltype);

CreateTable temp_option_course_extend(opt_code Number,opt_xmlsys.Xmltype);

 

(1).插入数据（暂时硬编码，后期肯定不是这样干的）

提示：中文还是需要给指定编码，这里指定为gbk

InsertInto temp_student_extend ts(ts.stu_code,ts.stu_xml) Select5,xmltype.createxml(       '<?xml version="1.0" encoding="GBK"?>'||        '<data>'||                                                                                '<student>'||                                                               '<stu_code>5</stu_code>'||                                                                                     '<stu_no>0151714203</stu_no>'||                                                                                     '<zh_name>赵丽盈</zh_name>'||                                                                                     '<sex>女</sex>'||                                                                                     '<age>23</age>'||                                                                                     '<school>北京大学</school>'||                                                         '</student>'||                                                                 '</data>'             Update  temp_student_extend tse Set tse.stu_xml= Updatexml(tse.stu_xml,'/data/student/age',Xmltype('<age>25</age>')) Where tse.stu_code=3; ) From dual;   InsertInto temp_score_extend tse(tse.sco_code,tse.sco_xml) Select seq_sco_code.nextval,xmltype.createxml(        '<data>'||              '<students>’|| <student></student>'|| '</students>'||              '</data>'      ) From dual;   InsertInto temp_course_extend tce(tce.cou_code,tce.cou_xml) Select1,xmltype.createxml(        '<data>'||                '<course>'||                    '<cou_code>1</cou_code>'||                      '<cou_no>XK2016090002</cou_no>'||                      '<cou_name>J2SE</cou_name>'||                      '<teach_code>2</teach_code>'||                '</course>'||              '</data>'      ) From dual;

 

(2).获取xmltype节点的值 extractvalue(xml_type_instance,node_xpath)

SelectExtractvalue(tse.stu_xml,'/data/students/student/zh_name') From temp_student_extend tse Where tse.stu_code=5;

 

(3).新增xmltype节点 appendchildxml(xml_type_instance,node_xpath,node_express_value)

Update temp_score_extend tse Set tse.sco_xml=Appendchildxml(tse.sco_xml,'/data/students',Xmltype('<student></student>')) Where tse.sco_code=40;

 

(4) .删除xmltype里面的节点deletexml(xml_type_instance,node_xpath)

Update temp_score_extend tse Set tse.sco_xml=Deletexml(tse.sco_xml,'/data/students/student') Where tse.sco_code=40;

 

(5).更新xmltype里面的数据updatexml(xml_type_instance,node_xpath,node_epress_value)

Update temp_student_extend tse Set tse.stu_xml= Updatexml(tse.stu_xml,'/data/student/age',Xmltype('<age>20</age>')) Where tse.stu_code=5; -- 下面这种等同updatexml 方法 Update  temp_score_extend tse Set tse.sco_xml= Appendchildxml(   Deletexml(tse.sco_xml,'/data/students/student'),     '/data/students',     Xmltype('<student></student>') ) Where tse.sco_code=40;

注：若xmltype_nodevalue text没有值就等于appendchildxml加一个无值的节点

 

问题：

         在temp_score_extend  xmltyp表里面怎么将temp_student_extend表里面的数据那过来，网上说的这种办法，但实践出错了。

Update temp_score_extend tse Set tse.sco_xml=xmltype.createxml((Select ts.stu_xml From temp_student_extend ts  Where ts.stu_code=1)) Where tse.sco_code=42; 错误：ORA-06553: PLS-306: 调用 'CREATEXML' 时参数个数或类型错误

 

INSERTCHILDXMLBEFORE  某个层次节点之前增加一个元素

INSERTCHILDXMLAFTER   某个层次节点之后增加一个元素

 

使用弱游标[动态刷值到xmltype table]

Declare  -- Cursor tmp_data Is Select *From temp_student;  --     Type tmp_data Is Ref Cursor;     Type up_data IsRefCursor;          tmp_data up_data;          t_score temp_score%Rowtype;          t_student temp_student%Rowtype;          t_course temp_course%Rowtype;          t_class temp_class%Rowtype;          v_num Number;          st_xml Varchar2(3000);          sc_xml Varchar2(3000);          c_xml Varchar2(3000);   Begin                   --Open tmp_data ;             Open tmp_data ForSelect *From temp_student;                       Loop                                 Fetch tmp_data Into t_student;                                                ExitWhen tmp_data%Notfound;                                                SelectCount(*) Into v_num From temp_student_extend t Where t.stu_code=t_student.stu_code;                                                If v_num =1Then                                                 DeleteFrom temp_student_extend t Where t.stu_code=t_student.stu_code;                                                 Else                                                  st_xml:='<?xml version="1.0" encoding="gbk"?>'||                                                          '<data>'||                                                                             '<student>'||                                                                                                        '<stu_code>'||t_student.stu_code||'</stu_code>'||                                                                                                        '<stu_no>'||t_student.stu_no||'</stu_no>'||                                                                                                        '<zh_name>'||t_student.zh_name||'</zh_name>'||                                                                                                        '<sex>'||t_student.sex||'</sex>'||                                                                                                        '<age>'||t_student.age||'</age>'||                                                                                                        '<school>'||t_student.school||'</school>'||                                                                             '</student>'||                                                          '</data>';                                                  InsertInto temp_student_extend values(t_student.stu_code,Xmltype.createxml(st_xml));                                                         Commit;                                                 EndIf;                                                                       EndLoop;                      Close tmp_data;                                             Open tmp_data ForSelect * From temp_score ;                                                         Loop                                                                  Fetch tmp_data Into t_score ;                                                                  ExitWhen tmp_data%Notfound;                                                                  SelectCount(*) Into v_num From temp_score_extend tse Where tse.sco_code=t_score.sco_code;                                                                                     If v_num =1Then                                                                                      DeleteFrom temp_score_extend tse Where tse.sco_code=t_score.sco_code;                                                                                                                                                            Else                                                                                              sc_xml:='<?xml version="1.0" encoding="gbk"?>'||                                                                                               '<data>'||                                                                                                                  '<score>'||                                                                                                                                             '<sco_code>'||t_score.sco_code||'</sco_code>'||                                                                                                                                             '<stu_code>'||t_score.stu_code||'</stu_code>'||                                                                                                                                             '<cou_code>'||t_score.cou_code||'</cou_code>'||                                                                                                                                             '<score>'||t_score.score||'</score>'||                                                                                                                                                                                                                                                      '</score>'||                                                                                               '</data>';                                                                                              InsertInto temp_score_extend values(t_score.sco_code,Xmltype.createxml(sc_xml));                                                                                              Commit;                                                                                      EndIf;                                                         EndLoop;                    Close tmp_data;                                       Open tmp_data ForSelect *From temp_class;                         Loop                                                                  Fetch tmp_data Into t_class ;                                                                  ExitWhen tmp_data%Notfound;                                                                  SelectCount(*) Into v_num From temp_class_extend tce Where tce.class_code=t_class.class_code;                                                                                     If v_num =1Then                                                                                      DeleteFrom temp_class_extend tce Where tce.class_code=t_class.class_code;                                                                                                                                                        Else                                                                                              c_xml:='<?xml version="1.0" encoding="gbk"?>'||                                                                                               '<data>'||                                                                                                                  '<score>'||                                                                                                                                             '<class_code>'||t_class.class_code||'</class_code>'||                                                                                                                                             '<class_no>'||t_class.class_no||'</class_no>'||                                                                                                                                             '<class_name>'||t_class.class_name||'</class_name>'||                                                                                                                                             '<class_desc>'||t_class.class_describe||'</class_desc>'||                                                                                                                                                                       '<class_hornor>'||t_class.class_hornor||'</class_hornor>'||                                                                                                                                                                  '<class_plan>'||t_class.class_plan||'</class_plan>'||                                                                                                                                                                                                                             '<stu_code>'||t_class.stu_code||'</stu_code>'||                                                                                                                                             '<stu_name>'||t_class.stu_name||'</stu_name>'||                                                                                                                  '</score>'||                                                                                               '</data>';                                                                                              InsertInto temp_class_extend values(t_class.class_code,Xmltype.createxml(c_xml));                                                                                              Commit;                                                                                      EndIf;                                                         EndLoop;                    Close tmp_data;                             SelectCount(*) Into v_num From temp_student_extend ;                    dbms_output.put_line('v_num:'||v_num);                    End ;