列表中具有指定参数的多个例程

Question

把代码贴上看看，光看错误怎么知道？

Answer 1

create or replace package body RMS_XML_IMPORT is
  procedure IMPORT (table_name in varchar2,
                    premary_key in varchar2,
                    pk_index in number,
                    task_kind in number,
                    fields in varchar2,
                    data in varchar2)  
                    is
                    --pk_index要大于零
  rowFieldPos integer;
  rowDataPos integer; 
  --rowFields nvarchar2(2000);
  rowData varchar2(4000);
  tempFields varchar2(2000);
  tempData varchar2(4000);
  startPos integer;
  endPos integer;
  pkData varchar2(2000);
  tempStr varchar2(4000);
  isExist integer; --零 为不存在，1存在 
  colPos integer;
  type cur_col is ref cursor;
  mydata cur_col;
  begin
     tempFields:=fields;  --所有行都是同一种模式既字段组
     tempStr:='';
     tempData:=data;     
     rowDataPos:=INSTRB(tempData,';',1,1);
     while (rowDataPos>0) loop 
           begin                 
                 --取字段
                 --rowFields:=SUBSTRB(tempFields,1,rowFieldPos-1);
                 --取一行数据
                 rowData:=SUBSTRB(tempData,1,rowDataPos-1);
                 --error_info:=rowData;   
                 --exit;              
                 pkData:='';                 
                 --表示是否有重复的数据              
                 isExist:=0;
                 
                 --获取主键值 pk_index其始值为1
                 endPos:=INSTRB(rowData,',',1,pk_index);
                 --主见在第一项
                 if(pk_index-1<=0) then
                    pkData:=SUBSTRB(rowData,1,endPos-1);               
                 else                 
                    startPos:=INSTRB(rowData,',',1,pk_index-1); 
                    --主见在第一和最后之间
                    if(startPos>0 and endPos>0) then
                       pkData:=SUBSTRB(rowData,startPos+1,endPos-startPos-1);  
                    end if;  
                    --主见在最后一项                
                    if (startPos>0 and endPos<0) then
                       pkData:=SUBSTRB(rowData,startPos+1); 
                    end if; 
                 end if;
                 --检查是否有重复的数据          
                 if (length(pkData)>0) then                       
                   tempStr:='select '||premary_key||' from '||table_name ||' where '||premary_key||'='||pkData;
                   open mydata for tempStr;
                   tempStr:='';
                   fetch mydata into tempStr;   
                   close mydata;
                  --有重复编号的值
                   if (length(tempStr)>0) then
                      isExist:=1;
                   end if;                          
                 end if;
                 --根据是否有重复的值及任务类型确定处理任务的方式
                 --如果任务类型是保留已有数据，代码层回为改行数据自动产生唯一编号，即主键值
                 if(task_kind=1 and isExist=0) then 
                    EXECUTE IMMEDIATE  'insert into '|| table_name||'('||tempFields||')'||' values('||rowData||')';
                 end if;
                 --覆盖已有数据
                 if(task_kind=0) then
                   if (isExist=0) then
                      EXECUTE IMMEDIATE  'insert into '|| table_name||'('||tempFields||')'||' values('||rowData||')';
                   else
                     begin 
                        rowFieldPos:=INSTRB(tempFields,','); 
                        startPos:=INSTRB(rowData,',');
                        colPos:=0;
                        tempStr:='';
                        while (rowFieldPos>0 and startPos>0) loop
                            --要修改的字段及值
                            if(colPos=0) then
                               tempStr:=tempStr || SUBSTRB(tempFields,1,rowFieldPos-1)||'='||SUBSTRB(rowData,1,startPos-1);                    
                            else
                               tempStr:=tempStr ||','|| SUBSTRB(tempFields,1,rowFieldPos-1)||'='||SUBSTRB(rowData,1,startPos-1);
                            end if;
                            colPos:=colPos+1;
                            tempFields:=SUBSTRB(tempFields,rowFieldPos+1);
                            rowData:=SUBSTRB(rowData,startPos+1);
                            rowFieldPos:=INSTRB(tempFields,','); 
                            startPos:=INSTRB(rowData,','); 
                        end loop;
                        --处理最后一节
                        if (length(tempFields)>0) then
                           tempStr:=tempStr||','|| SUBSTRB(tempFields,1)||'='||SUBSTRB(rowData,1);
                        end if;                    
                        EXECUTE IMMEDIATE 'update '||table_name||' set '||tempStr||' where '||premary_key||'='||pkData; 
                     end;
                   end if;
                 end if;
                 
                 tempFields:=fields;
                 tempData:=SUBSTRB(tempData,rowDataPos+1);                                  
                 if(length(tempData)>0) then
                   rowDataPos:=INSTRB(tempData,';');
                 else
                   rowDataPos:=0;
                 end if;
           end;
     end loop;
  end IMPORT;
end RMS_XML_IMPORT;