oracle - 如何在plsql中拆分参数

Question

嗨，伙计们，我如何在 plsql purun_kod 中拆分参数就像 100,150,255,777，我想拆分它并分配给 v_urun_kod，
谢谢您的帮助。我的代码有点像：

function police_ara( purun_kod       in acente.levha_no%type,
                      ppolice_no      in varchar2) return sys_refcursor is
    vret sys_refcursor;
    v_urun_kod varchar(1000); 
  begin
    v_urun_kod := purun_kod;
  open vret for
      with policeler as
       (select distinct ph.police_hareket_id ph_police_hareket_id,
                        p.urun_kod
                        (select max(pho.police_hareket_id) from police_hareket_otr pho
            where pho.police_hareket_id = ph.police_hareket_id
           and (pho.durum_kod1 = 0 or pho.durum_kod2 = 0 or pho.durum_kod3 = 0 or pho.durum_kod4 = 0)) pho_police_hareket_id,

                        ph.odeme_arac_kod,
                        ph.police_id
         from police p
         inner join police_hareket ph
           and (ph.ekbelge_no>0 or (select count(1)
                  from police_kotasyon pk
                 where pk.police_hareket_id = ph.police_hareket_id 
                   and pk.kotasyon_seviyesi = 3
                   and rownum = 1)>0)
          left join police_prim pp
            on (pp.police_hareket_id = ph.police_hareket_id and pp.para_birim_kod = ph.para_birim_kod)
          left join musteri_rol mr
            on (mr.musteri_rol_id = pa.acente_id)
         where (p.urun_kod = purun_kod or purun_kod is null)
         and(p.police_no = ppolice_no or ppolice_no is null)

      select urun_kod,
             acente_kod,
             brut_prim
        from policeler
        left join police_musteri pm_sg
          on pm_sg.police_hareket_id = ph_police_hareket_id
         and pm_sg.rol_id = pck_const_rol.sigortali
         and pm_sg.sira_no = 1
        left join musteri m_sg
          on (m_sg.musteri_id = pm_sg.musteri_id)
       order by police_id;
    return vret;
  end;

标签： oracleplsqlsplit

Answer 1

这是如何将逗号分隔值字符串拆分为行。这是一个 SQL*Plus 示例，只是为了展示它的工作原理：

SQL> var purun_kod varchar2(100);
SQL> exec :purun_kod := '100,150,255,777';

PL/SQL procedure successfully completed.

SQL> select regexp_substr(:purun_kod, '[^,]+', 1, level) urun_kod
  2  from dual
  3  connect by level <= regexp_count(:purun_kod, ',') + 1;

URUN_KOD
----------------------------------------------------------------------
100
150
255
777

SQL>

---

目前，您发布的代码没有多大意义，因为您从不使用v_urun_kod. 由于函数返回 ref 游标，因此为每个单独的值运行该代码没有多大意义；您可能会循环执行此操作，但这不会像您希望的那样工作（至少，我认为是这样）。

看着它，我想这样的事情可能会做你正在寻找的东西：

SQL> create or replace
  2  function police_ara( purun_kod       in varchar2,
  3                       ppolice_no      in varchar2)
  4  return sys_refcursor is
  5    vret          sys_refcursor;
  6    -- v_urun_kod varchar(1000);   --> no need for that variable any more
  7  begin
  8    open vret for
  9      with policeler as
 10        (select distinct ph.police_hareket_id ph_police_hareket_id,
 11                         p.urun_kod,
 12                         (select max(pho.police_hareket_id)
 13                          from police_hareket_otr pho
 14                          where pho.police_hareket_id = ph.police_hareket_id
 15                            and (   pho.durum_kod1 = 0
 16                                 or pho.durum_kod2 = 0
 17                                 or pho.durum_kod3 = 0
 18                                 or pho.durum_kod4 = 0
 19                                )
 20                         ) pho_police_hareket_id,
 21                         --
 22                         ph.odeme_arac_kod,
 23                         ph.police_id
 24           from police p
 25           --> this is new
 26           join (select regexp_substr(purun_kod, '[^,]+', 1, level) urun_kod
 27                 from dual
 28                 connect by level <= regexp_count(purun_kod, ',') + 1
 29                ) x on x.urun_kod = p.urun_kod
 30           --> end of "this is new"
 31           inner join police_hareket ph on 1 = 1 --> you're missing join condition here
 32             and (   ph.ekbelge_no > 0
 33                  or (select count(1)
 34                      from police_kotasyon pk
 35                      where pk.police_hareket_id = ph.police_hareket_id
 36                        and pk.kotasyon_seviyesi = 3
 37                        and rownum = 1
 38                     ) > 0
 39                 )
 40            left join police_prim pp
 41              on (    pp.police_hareket_id = ph.police_hareket_id
 42                  and pp.para_birim_kod = ph.para_birim_kod
 43                 )
 44            left join musteri_rol mr
 45              on (mr.musteri_rol_id = pa.acente_id)
 46           where (   p.urun_kod = purun_kod
 47                  or purun_kod is null
 48                 )
 49             and (   p.police_no = ppolice_no
 50                  or ppolice_no is null
 51                 )
 52        )
 53        select urun_kod,
 54               acente_kod,
 55               brut_prim
 56          from policeler
 57          left join police_musteri pm_sg
 58            on pm_sg.police_hareket_id = ph_police_hareket_id
 59           and pm_sg.rol_id = pck_const_rol.sigortali
 60           and pm_sg.sira_no = 1
 61          left join musteri m_sg
 62            on (m_sg.musteri_id = pm_sg.musteri_id)
 63         order by police_id;
 64    return vret;
 65  end;
 66  /

Warning: Function created with compilation errors.

请注意以下事项：

• 第 6 行：不再需要v_urun_kod变量
• 第 25 - 30 行：这是一段新代码，它利用了上面发布的示例。SELECT用作内联视图并与police表的urun_kod列连接（至少，我认为你应该这样做）
• 第 31 行：您在ON这里缺少该子句；自己解决，不知道应该是什么

• 由于我没有您的表，因此创建该函数时出现编译错误；除此之外，它可能没问题：

  SQL> show err
  Errors for FUNCTION POLICE_ARA:
  
  LINE/COL ERROR
  -------- -----------------------------------------------------------------
  8/5      PL/SQL: SQL Statement ignored
  43/21    PL/SQL: ORA-00942: table or view does not exist
  SQL>