oracle存储过程wms_concat bug记录

最新推荐文章于 2023-12-08 08:00:00 发布

我心依依旧

最新推荐文章于 2023-12-08 08:00:00 发布

阅读量231

点赞数

分类专栏： # oracle

本文链接：https://blog.csdn.net/a200822146085/article/details/114686169

版权

oracle 专栏收录该内容

21 篇文章 5 订阅

订阅专栏

    --校验导入数据是否存在重复记录
    begin
    execute immediate 'select dbms_lob.substr(wm_concat(distinct t1.LOCATIONID),4000,1) from UDFLoad_tmp12345 t1 group by t1.LOCATIONID having count(1) > 1'
      into r_ErrMeg;
    If r_ErrMeg is not null Then
      Out_Return_Code := '999导入文件存在重复库位' || r_ErrMeg;
      goto RETURN_LOC_IMPORT_ENDING;
    End If;
    EXCEPTION
      when others then
        null;
    end;

如果有多条记录重复，则into r_ErrMeg永远报错，得不到正确结果，正确代码：

    --校验导入数据是否存在重复记录
    begin
    execute immediate 'select dbms_lob.substr(wm_concat(LOCATIONID),4000,1) from (select distinct t1.LOCATIONID from UDFLoad_U_LOC_IMPORT_01395465 t1 group by t1.LOCATIONID having count(1) > 1)'
      into r_ErrMeg;
    If r_ErrMeg is not null Then
      Out_Return_Code := '999导入文件存在重复库位' || r_ErrMeg;
      goto RETURN_LOC_IMPORT_ENDING;
    End If;
    EXCEPTION
      when others then
        null;
    end;