oracle 字符串分割 去重、排序

--创建存储类型
CREATE OR REPLACE TYPE t_ret_table IS TABLE OF VARCHAR2(20);

--创建 字符串去重复、排序函数
CREATE OR REPLACE FUNCTION F_SPLIT_STRING(VAR_STR   IN STRING,
                                          VAR_SPLIT IN STRING)
  RETURN VARCHAR2 IS
  VAR_OUT     T_RET_TABLE;
  VAR_TMP     VARCHAR2(4000);
  VAR_ELEMENT VARCHAR2(4000);
  V_RS        VARCHAR2(4000);
BEGIN
  VAR_TMP := VAR_STR;
  VAR_OUT := T_RET_TABLE();
  --如果存在匹配的分割符
  WHILE INSTR(VAR_TMP, VAR_SPLIT) > 0 LOOP
    VAR_ELEMENT := SUBSTR(VAR_TMP, 1, INSTR(VAR_TMP, VAR_SPLIT) - 1);
    VAR_TMP     := SUBSTR(VAR_TMP,
                          INSTR(VAR_TMP, VAR_SPLIT) + LENGTH(VAR_SPLIT),
                          LENGTH(VAR_TMP));
    VAR_OUT.EXTEND(1);
    VAR_OUT(VAR_OUT.COUNT) := VAR_ELEMENT;
  END LOOP;
  VAR_OUT.EXTEND(1);
  VAR_OUT(VAR_OUT.COUNT) := VAR_TMP;
  FOR V_ROW IN (SELECT DISTINCT (COLUMN_VALUE) COLUMN_VALUE
                  FROM TABLE(VAR_OUT)
                 ORDER BY COLUMN_VALUE) LOOP
    EXIT WHEN V_ROW.COLUMN_VALUE IS NULL;
    IF V_RS IS NOT NULL THEN
      V_RS := V_RS || ',' || V_ROW.COLUMN_VALUE;
    ELSE
      V_RS := V_ROW.COLUMN_VALUE;
    END IF;
  END LOOP;
  --DBMS_OUTPUT.put_line(V_RS);
  RETURN V_RS;
END F_SPLIT_STRING;


-- 测试查询
SELECT (F_SPLIT_STRING('003,001,004,001' ,',')) FROM DUAL;-- 结果  ：001,003,004  （去重复、排序）


-- 创建测试表
CREATE TABLE TEST_AA
(
  dic_name VARCHAR2(50)
)
;
-- 触发器语法  详解：(http://www.cnblogs.com/huyong/archive/2011/04/27/2030466.html)
/**
CREATE [OR REPLACE] TRIGGER trigger_name
{BEFORE | AFTER }
{INSERT | DELETE | UPDATE [OF column [, column …]]}
[OR {INSERT | DELETE | UPDATE [OF column [, column …]]}...]
ON [schema.]table_name | [schema.]view_name 
[REFERENCING {OLD [AS] old | NEW [AS] new| PARENT as parent}]
[FOR EACH ROW ]
[WHEN condition]
PL/SQL_BLOCK | CALL procedure_name;
**/
--创建触发器
CREATE OR REPLACE TRIGGER TRG_UPDATE_DEAL
  BEFORE UPDATE  OF DIC_NAME ON TEST_AA
  FOR EACH ROW
  WHEN (OLD.DIC_NAME IS NOT NULL)
BEGIN
  --dbms_output.put_line(:new.dic_name);
  :NEW.DIC_NAME := F_SPLIT_STRING(:NEW.DIC_NAME, ',');
END;

-- 插入测试数据
INSERT INTO TEST_AA(DIC_NAME,Col_t) VALUES('003','ttt');

UPDATE TEST_AA SET DIC_NAME = '003,001,004' WHERE col_t='ttt';

SELECT * FROM TEST_AA;

当然你还可以简单的扩展下函数，传入 排序方式、是否去重复等，就到这里了。

转载于:https://www.cnblogs.com/ourh/archive/2012/10/25/2738214.html