oracle record table 一个实例

感觉不错的实例，记录一下

set serveroutput on;

DECLARE
  TYPE Company IS RECORD(
    companyId varchar2(10),
    companyName varchar2(300),
    address varchar2(300),
    LegalRepresentative varchar2(100)
  );
  TYPE Companys IS TABLE OF Company INDEX BY BINARY_INTEGER;
  v_Companys Companys;
  
  v_companyId varchar2(10);
  v_companyName varchar2(300);
  v_address varchar2(300);
  v_LegalRepresentative varchar2(100);
  
  v_rowNumber number(2);
  v_count number(2);
  
BEGIN
  
  v_rowNumber := 1;
  v_Companys(v_rowNumber).companyId := '7084';
  v_Companys(v_rowNumber).companyName := '深圳市有限公司1';
  v_Companys(v_rowNumber).address := '深圳市福田场（二期）西座1602';
  v_Companys(v_rowNumber).LegalRepresentative := '张三';
  
  v_rowNumber := v_rowNumber + 1;
  v_Companys(v_rowNumber).companyId := '7827;
  v_Companys(v_rowNumber).companyName := '深圳市有限公司2';
  v_Companys(v_rowNumber).address := '深圳市蛇口南海小筑C7';
  v_Companys(v_rowNumber).LegalRepresentative := '李四';
  
  v_rowNumber := v_rowNumber + 1;
  v_Companys(v_rowNumber).companyId := '7586';
  v_Companys(v_rowNumber).companyName := '深圳光汇石油“
  v_Companys(v_rowNumber).LegalRepresentative := '张在';
  
 
  
  --dbms_output.put_line(v_rowNumber);
  UPDATE Bas_ManageCompany SET AdminOrgCode=NULL WHERE AdminOrgCode='140000';
  UPDATE Bas_ManageCompany SET AuditOrgCode=NULL WHERE AuditOrgCode='140000';
  
  FOR i IN 1 .. v_Companys.COUNT LOOP
    v_companyId := v_Companys(i).companyId;
    v_companyName := v_Companys(i).companyName;
    v_address := v_Companys(i).address;
    v_LegalRepresentative := v_Companys(i).LegalRepresentative;
    
    select count(1) into v_count from Bas_Company where CompanyId=v_companyId;
    
    if(v_count =0) then
       INSERT INTO Bas_Company(CompanyId,OrganizationCode,LegalRepresentative,CompanyName,NationalityCode,ProvinceCode,CityCode,Address,NatureCode)
       VALUES(v_companyId,v_companyId,v_LegalRepresentative,v_companyName,'125','440000','440300',v_address,'01');
       
       INSERT INTO Bas_ManageCompany(CompanyId,AdminOrgCode,IfSystem)
       VALUES(v_companyId,'190000',0);
    else
       UPDATE Bas_ManageCompany SET AdminOrgCode='190000' WHERE CompanyId=v_companyId;
       UPDATE Bas_ManageCompany SET AuditOrgCode='190000' WHERE CompanyId=v_companyId AND IfSystem=1;
    end if;
    
  END LOOP;
  
  COMMIT;
  
END;
/