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;
/
评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值