在后台的日常开发工作中,总有很多解析字符串的操作,为了达到最大限度的复用,方便自己和大家调用。这里给出两个方法:
方法1,在已知参数结构的情况下返回指定位置的字符串,分隔符为‘|’。CREATE OR REPLACE FUNCTION GET_SUBSTR(PARAS VARCHAR2, POS NUMBER)
RETURN VARCHAR2 IS
PARAS_NEW VARCHAR2(8000);
BEGIN
PARAS_NEW := '|' || PARAS || '|';
RETURN SUBSTR(PARAS_NEW,
INSTR(PARAS_NEW, '|', 1, POS) + 1,
INSTR(PARAS_NEW, '|', 1, POS + 1) -
INSTR(PARAS_NEW, '|', 1, POS) - 1);
END GET_SUBSTR;
CREATE OR REPLACE FUNCTION GET_SUBSTR(PARAS VARCHAR2, POS NUMBER)
RETURN VARCHAR2 IS
PARAS_NEW VARCHAR2(8000);
BEGIN
PARAS_NEW := '|' || PARAS || '|';
RETURN SUBSTR(PARAS_NEW,
INSTR(PARAS_NEW, '|', 1, POS) + 1,
INSTR(PARAS_NEW, '|', 1, POS + 1) -
INSTR(PARAS_NEW, '|', 1, POS) - 1);
END GET_SUBSTR;
方法2,在未知参数结构,只知道分隔符是‘,’的情况下所做的处理。CREATE OR REPLACE FUNCTION fun_analyze(prm_id IN VARCHAR2) RETURN VARCHAR2 IS
TYPE t_tid IS TABLE OF VARCHAR2(200) INDEX BY PLS_INTEGER;
TYPE t_name IS TABLE OF VARCHAR2(200) INDEX BY PLS_INTEGER;
arr_id t_tid;
arr_name t_name;
v_tempid VARCHAR2(20);
v_temp VARCHAR2(20);
n_number INTEGER := 0;
v_name VARCHAR2(200);
BEGIN
FOR i IN 1 .. length(prm_id || ',') LOOP
v_temp := substr(prm_id || ',', i, 1);
IF v_temp != ',' THEN
v_tempid := v_tempid || v_temp;
ELSE
n_number := n_number + 1;
arr_id(n_number) := v_tempid;
v_tempid := '';
END IF;
END LOOP;
FOR j IN 1 .. arr_id.COUNT LOOP
SELECT zb_name INTO arr_name(j) FROM zb_code WHERE zb_id = arr_id(j);
v_name := v_name || ',' || arr_name(j);
END LOOP;
RETURN substr(v_name, 2);
END fun_analyze;
简单说明:
1.该过程中涉及到的表是在为一个网友解答问题时建立的,下面给出表的表结构:create table ZB_CODE
(
ZB_ID VARCHAR2(20),
ZB_NAME VARCHAR2(2000)
);
2.这个过程的意图是给出一个不定长的ID的字符串,但是知道这个字符串的分隔符‘,’,要求以这样的形式返回name的列表:name1,name2……
CREATE OR REPLACE FUNCTION fun_analyze(prm_id IN VARCHAR2) RETURN VARCHAR2 IS
TYPE t_tid IS TABLE OF VARCHAR2(200) INDEX BY PLS_INTEGER;
TYPE t_name IS TABLE OF VARCHAR2(200) INDEX BY PLS_INTEGER;
arr_id t_tid;
arr_name t_name;
v_tempid VARCHAR2(20);
v_temp VARCHAR2(20);
n_number INTEGER := 0;
v_name VARCHAR2(200);
BEGIN
FOR i IN 1 .. length(prm_id || ',') LOOP
v_temp := substr(prm_id || ',', i, 1);
IF v_temp != ',' THEN
v_tempid := v_tempid || v_temp;
ELSE
n_number := n_number + 1;
arr_id(n_number) := v_tempid;
v_tempid := '';
END IF;
END LOOP;
FOR j IN 1 .. arr_id.COUNT LOOP
SELECT zb_name INTO arr_name(j) FROM zb_code WHERE zb_id = arr_id(j);
v_name := v_name || ',' || arr_name(j);
END LOOP;
RETURN substr(v_name, 2);
END fun_analyze;
create table ZB_CODE
(
ZB_ID VARCHAR2(20),
ZB_NAME VARCHAR2(2000)
);