关于oracle的rowtype类型的对象放到for循环的问题

来源：互联网发布：python 算法导论3 编辑：程序博客网时间：2024/04/28 18:37

关于rowtype类型的对象放在游标里面进行赋值，如果某个字段上一次有值，这次没有值就会造成当前这条数据的这个字段本来没有值的会得到上次的值，所以每次在insert完以后把

rowtype对象清空eg: main_row %名 insert完以后，main_row :=NULL,这样就会把上次的数据全部清除掉，去掉数据的紊乱

PROCEDURE insert_l_pm_projectitem IS    CURSOR cur_data IS      SELECT * FROM l_firstvalueplanningdet;    l_main pm_projectitem%ROWTYPE;    l_num  NUMBER(10) DEFAULT 0;  BEGIN    FOR l_data IN cur_data LOOP      l_main.id                := sys_guid();      l_main.salescontractcode := l_data.hetongcode;      l_main.salescontractname := l_data.hetongname;      l_main.projectitemcode   := l_data.xiangmucode;      l_main.projectitemname   := l_data.xiangmuname;      l_main.serialno          := l_num + 1;      l_main.contractmaintypes := getudf2bycode(l_data.xiangmudaleicode,                                                'PM_PROJECT_TYPE');      l_main.designcode        := l_data.shejicode;      l_main.designname        := l_data.shejiname;      l_main.projectstatus     := 'RUNNING';      l_main.projectcategory   := l_data.xiangmudaleicode;      l_main.businesstype      := l_data.yewuleiixngcode;      l_main.buildtype         := l_data.jianzhuleixingcode;      l_main.projectstage      := l_data.xiangmujieduancode;      l_main.begintime         := to_date(l_data.kaishishijian,                                          'yyyy-MM-dd  hh24:mi:ss');      l_main.endtime           := to_date(l_data.xiangmujieshushijian,                                          'yyyy-MM-dd  hh24:mi:ss');      --根据合同编号查询客户id      l_main.clientsid   := getidbycode(l_data.hetongcode);      l_main.clientscode := getcodebycode(l_data.hetongcode);      l_main.clientsname := getnamebycode(l_data.hetongcode);          l_main.priprojectmanagerid   := getmainidbycode(l_data.hetongcode);      l_main.priprojectmanagercode := getmaincodebycode(l_data.hetongcode);      l_main.priprojectmanagername := getmainnamebycode(l_data.hetongcode);          IF (l_data.kehu IS NOT NULL) THEN        l_main.projectclientsid   := get_clientidbyname(l_data.kehu);        l_main.projectclientscode := get_clientsbyname(l_data.kehu); --根据名称获取code        l_main.projectclientsname := l_data.kehu;      END IF;      IF (l_data.diyigongchengcode IS NOT NULL) THEN        l_main.proprincipalid   := get_satffidbycode(l_data.diyigongchengcode);        l_main.proprincipalcode := l_data.diyigongchengcode;        l_main.proprincipalname := l_data.diyigongchengname;      END IF;      IF (l_data.diyixiangmujinglicode IS NOT NULL) THEN        l_main.projectmanagerid   := get_satffidbycode(l_data.diyixiangmujinglicode);        l_main.projectmanagercode := l_data.diyixiangmujinglicode;        l_main.projectmanagername := l_data.diyixingmujingliname;      END IF;          IF (l_data.dierxiangmujinglicode IS NOT NULL) THEN        l_main.seprojectmanagerid   := get_satffidbycode(l_data.dierxiangmujinglicode);        l_main.seprojectmanagercode := l_data.dierxiangmujinglicode;        l_main.seprojectmanagername := l_data.dierxiangmujingliname;      END IF;      IF (l_data.dierxiangmujinglicode IS NOT NULL) THEN        l_main.seprincipalid   := get_satffidbycode(l_data.dierxiangmujinglicode);        l_main.seprincipalcode := l_data.diergongchengcode;        l_main.seprincipalname := l_data.diergongchengname;      END IF;          l_main.secondstatus   := 'COMPLETED';      l_main.whetherresitem := 'N';      ------------------------华丽的分割线------------------------------      l_main.createddate    := SYSDATE;      l_main.redatedate     := SYSDATE;      l_main.dataversion    := '0';      l_main.status         := l_status;      l_main.creatorcode    := '102009';      l_main.redactorcode   := '102009';      l_main.createdorgcode := '0201';      l_main.redateorgcode  := '0201';      l_main.orgcode        := '0';      ------------------------华丽的分割线------------------------------      INSERT INTO pm_projectitem VALUES l_main;

     ---这个还是关键点所在------------      l_main :=NULL;

        END LOOP;  END insert_l_pm_projectitem;

0 0