分类： Oracle
2010-02-23 22:35:32
create or replace TYPE T_VARCHAR2_LIST AS TABLE OF VARCHAR2(200);
/
create or replace
FUNCTION F_SPLIT_STRING(
p_source VARCHAR2, p_delimiter VARCHAR2
) RETURN T_VARCHAR2_LIST
AS
v_layer_rate_list T_VARCHAR2_LIST := T_VARCHAR2_LIST();
var_tmp VARCHAR2(4000);
var_element VARCHAR2(4000);
BEGIN
var_tmp := p_source;
WHILE instr(var_tmp, p_delimiter)>0 LOOP
var_element := substr(var_tmp, 1, instr(var_tmp, p_delimiter)-1);
var_tmp := substr(var_tmp,instr(var_tmp, p_delimiter) + length(p_delimiter),length(var_tmp));
IF (var_element IS NOT NULL) AND (var_element != p_delimiter) THEN
v_layer_rate_list.extend(1);
v_layer_rate_list(v_layer_rate_list.count) := var_element;
END IF;
END LOOP;
IF (var_tmp IS NOT NULL) AND (var_tmp != p_delimiter) THEN
v_layer_rate_list.extend(1);
v_layer_rate_list(v_layer_rate_list.count) := var_tmp;
END IF;
RETURN v_layer_rate_list;
END F_SPLIT_STRING;
/
create or replace
TYPE T_STRING_MAP AS TABLE OF T_STRING_ENTRY;
/
create or replace
TYPE T_STRING_ENTRY AS OBJECT (
c_key VARCHAR2(200), c_value VARCHAR2(200)
);
/
create or replace
FUNCTION f_PARSE_STRING_TO_MAP(
p_oringal_string VARCHAR2, p_entry_delimiter VARCHAR2, p_key_delimeter VARCHAR2
) RETURN T_STRING_MAP
AS
v_entry_list T_VARCHAR2_LIST;
v_string_map T_STRING_MAP := T_STRING_MAP();
v_string_entry T_STRING_ENTRY := T_STRING_ENTRY(NULL,NULL);
v_int NUMBER(4);
BEGIN
v_entry_list := F_SPLIT_STRING(p_oringal_string, p_entry_delimiter);
FOR l_entry IN v_entry_list.first..v_entry_list.last
LOOP
v_int := instr(v_entry_list(l_entry), p_key_delimeter);
v_string_entry.c_key := substr(v_entry_list(l_entry), 0, v_int-1);
v_string_entry.c_value := substr(v_entry_list(l_entry), v_int+length(p_key_delimeter));
v_string_map.extend;
v_string_map(v_string_map.count) := v_string_entry;
END LOOP;
RETURN v_string_map;
END f_PARSE_STRING_TO_MAP;
/
create or replace
FUNCTION F_GET_UPDATED_KV_LIST(
p_older_kv_list IN VARCHAR2, p_newer_kv_list VARCHAR2,
p_entry_delimiter VARCHAR2 DEFAULT '|||', p_kv_delimiter VARCHAR2 DEFAULT '='
) RETURN VARCHAR2 AS
v_older_kvs T_STRING_MAP;
v_newer_kvs T_STRING_MAP;
v_newer_key_value VARCHAR2(500);
v_older_key_value VARCHAR2(500);
v_modified_kv_list VARCHAR2(2000);
BEGIN
IF p_newer_kv_list IS NULL THEN
v_modified_kv_list := p_entry_delimiter||p_older_kv_list||p_entry_delimiter;
ELSIF p_older_kv_list IS NULL THEN
v_modified_kv_list := p_entry_delimiter||p_newer_kv_list||p_entry_delimiter;
ELSE
v_modified_kv_list := p_entry_delimiter||p_older_kv_list||p_entry_delimiter;
v_older_kvs := f_parse_string_to_map(p_older_kv_list, p_entry_delimiter,p_kv_delimiter);
v_newer_kvs := f_parse_string_to_map(p_newer_kv_list, p_entry_delimiter,p_kv_delimiter);
FOR l_new_kv_int IN v_newer_kvs.first..v_newer_kvs.last
LOOP
IF v_newer_kvs(l_new_kv_int).c_key IS NOT NULL THEN
FOR l_older_kv_int IN v_older_kvs.first..v_older_kvs.last
LOOP
IF v_older_kvs(l_older_kv_int).c_key IS NOT NULL THEN
IF v_older_kvs(l_older_kv_int).c_key = v_newer_kvs(l_new_kv_int).c_key THEN
v_older_key_value := p_entry_delimiter||v_older_kvs(l_older_kv_int).c_key||p_kv_delimiter||v_older_kvs(l_older_kv_int).c_value;
EXIT;
END IF;
END IF;
END LOOP;
IF v_newer_kvs(l_new_kv_int).c_value IS NULL THEN
v_newer_key_value :='';
ELSE
v_newer_key_value := p_entry_delimiter||v_newer_kvs(l_new_kv_int).c_key||p_kv_delimiter||v_newer_kvs(l_new_kv_int).c_value;
END IF;
IF v_older_key_value IS NULL THEN
IF p_older_kv_list IS NULL THEN
v_modified_kv_list := p_entry_delimiter;
END IF;
v_modified_kv_list := v_modified_kv_list ||v_newer_key_value|| p_entry_delimiter;
ELSE
v_modified_kv_list := replace(v_modified_kv_list, v_older_key_value, v_newer_key_value);
END IF;
END IF;
END LOOP;
END IF;
IF v_modified_kv_list IS NULL OR v_modified_kv_list = p_entry_delimiter||p_entry_delimiter THEN
v_modified_kv_list := NUll;
ELSE
v_modified_kv_list :=replace(v_modified_kv_list, p_entry_delimiter||p_entry_delimiter,p_entry_delimiter);
END IF;
RETURN v_modified_kv_list;
END F_GET_UPDATED_KV_LIST;
/
