PLSQL分字符串转数组

最新推荐文章于 2023-07-21 16:37:56 发布

OO好久不见OO

最新推荐文章于 2023-07-21 16:37:56 发布

阅读量337

点赞数

分类专栏： SQL 文章标签： plsql

本文链接：https://blog.csdn.net/longtimetoc/article/details/126894439

版权

SQL 专栏收录该内容

2 篇文章 0 订阅

订阅专栏

以下是个笨办法将字符串转数组。

DECLARE
  l_var_temp        VARCHAR2(1000);
  l_split_char      VARCHAR2(10) := ',';
  l_invoice_id      NUMBER;
  p_invoice_id_list VARCHAR2(3000) := ',12,31412,3,,';
  n_length          NUMBER := lengthb(p_invoice_id_list);
  TYPE type_invoice_id_list IS TABLE OF NUMBER INDEX BY BINARY_INTEGER;
  l_invoice_id_list type_invoice_id_list;
BEGIN

  l_var_temp := p_invoice_id_list;
  l_invoice_id_list.delete;
  WHILE instr(l_var_temp, l_split_char) > 0 LOOP
  
    l_invoice_id := substrb(l_var_temp
                           ,1
                           ,instrb(l_var_temp, l_split_char) - 1);
  
    -- 每取完字符串里的分隔的字符，该字符将从原始字符串中剔除
    l_var_temp := substrb(l_var_temp, instrb(l_var_temp, l_split_char) + 1);
  
    IF l_invoice_id IS NOT NULL THEN
      l_invoice_id_list(l_invoice_id_list.count) := l_invoice_id;
    END IF;
  END LOOP;

  IF lengthb(l_var_temp) > 0 THEN
    l_invoice_id_list(l_invoice_id_list.count) := l_var_temp;
  END IF;

  dbms_output.put_line('l_invoice_id_list.count = ' ||
                       l_invoice_id_list.count);
  FOR i IN 1 .. l_invoice_id_list.count LOOP
    dbms_output.put_line(l_invoice_id_list(i - 1));
  END LOOP;
END;