oracle从入门到精通 之 六.函数，动态sql，游标在实际中的应用

----取扩展字段函数——————————————————————————————
create or replace function BRField(end2guid   varchar2,
                          name       varchar2,
                          vclassduid varchar2) return varchar2 as
  v_value     varchar2(600);
  s_field varchar2(200);
  v_fieldname varchar2(20);
begin
  select t.COLUMNNAME
    into v_fieldname
    from MA_FIELD t
   where classguid = vclassduid
     and fieldname = name;
  s_field := 'select   ' || v_fieldname ||
             '  from ma_foundation m,
                product_rg n
 where m.guid = n.foundationfk  and  m.guid ='''||end2guid||'''';
  execute immediate s_field
    into v_value;
  return v_value;
end BRField; 

               
---------------------------创建临时表----------------------------------------------------------------------------------------------------------------
   begin
      execute immediate 'drop table z_mid_report';
      execute immediate 'create table z_test_t (col clob)';
    
      execute immediate 'create table z_mid_report
               (ID                     varchar2(128), REVISIONID           varchar2(128), drawno                varchar2(128), NAME           varchar2(128),
                Specification          varchar2(128), Material             varchar2(128), BOMStructure_Quantity varchar2(128), surface        varchar2(128),
                Weight                 varchar2(128), SourceType           varchar2(128), StockUnit             varchar2(128), OWNERUSER      varchar2(128),
                twod_end2_id           varchar2(128), twod_end2_REVISIONID varchar2(128), twod_end2_filename    varchar2(128), threed_end2_id varchar2(128),
                threed_end2_REVISIONID varchar2(128), threed_end2_filename varchar2(128), brand                 varchar2(128), CLASSIFICATION varchar2(128),
                status                 varchar2(128), end1_instantstring   varchar2(128), rmarks                varchar2(128)
               )';
    exception
      when others then
        null;
    end;
--------------------------------------------------------------------------------------------------------------------------------------------------

declare
  v_classguid       char(32);
  v_guid            char(32);
  v_bomname         varchar2(128) := 'BOM';
  cur_bomreport_row z_mid_report%rowtype;

  cursor cur_bomreport(classguid varchar2,
                       tguid     varchar2,
                       bomname   varchar2) is
    select f.id id,
           f.revisionid revisionid,
           f.name name,
           c.quantity quantity,
           f.owneruser owneruser,
           f.classification classification,
           f.oobsoletetime oobsoletetime,
           f.effectivetime effectivetime,
           f.status status,
           d.name end1name,
           BRField(tguid, 'drawno', classguid) drawno,
           BRField(tguid, 'material', classguid) material,
           BRField(tguid, 'surface', classguid) surface,
           BRField(tguid, 'weight', classguid) weight,
           BRField(tguid, 'sourcetype', classguid) sourcetype,
           BRField(tguid, 'stockunit', classguid) stockunit,
           BRField(tguid, 'brand', classguid) brand,
           BRField(tguid, 'rmarks', classguid) rmarks
      from bomview_r b
     inner join bomstructure c
        on c.viewfk = b.foundationfk
     inner join ma_foundation d
        on d.guid = b.end1
       and d.obsoletetime is null
     inner join ma_foundation e
        on e.guid = b.foundationfk
     inner join ma_foundation f
        on (f.guid = c.end2 or
           (f.masterfk = c.end2masterguid and f.latestrevision like '%m%'))
     start with d.guid = tguid
            and e.name = bomname
    connect by d.guid = prior f.guid;
begin
  select classguid, guid
    into v_classguid, v_guid
    from ma_foundation
   where guid = 'B76FF3DBF4C041C79271E525EB74FE1F';
  open cur_bomreport(v_classguid, v_guid, v_bomname);
  loop
    fetch cur_bomreport
      into cur_bomreport_row;
    if cur_bomreport%found then
      insert into z_mid_report
        (id,
         revisionid,
         name,
         bomstructure_quantity,
         owneruser,
         classification,
         status,
         end1_instantstring,
         drawno,
         specification,
         material,
         surface,
         weight,
         sourcetype,
         stockunit,
         brand,
         rmarks)
      values
        (cur_bomreport_row.id,
         cur_bomreport_row.revisionid,
         cur_bomreport_row.name,
         cur_bomreport_row.quantity,
         (select p.name
            from sa_user p
           where p.guid = cur_bomreport_row.owneruser),
         cur_bomreport_row.classification,
         cur_bomreport_row.status,
         cur_bomreport_row.end1name,
         cur_bomreport_row.drawno,
         cur_bomreport_row.specification,
         cur_bomreport_row.material,
         cur_bomreport_row.surface,
         cur_bomreport_row.weight,
         cur_bomreport_row.sourcetype,
         cur_bomreport_row.stockunit,
         cur_bomreport_row.brand,
         cur_bomreport_row.rmarks);
    else
      dbms_output.put_line('已取出所有数据！共' || cur_bomreport%rowcount || '条记录');
      exit;
    end if;
  end loop;
end;