直接上干货
Ifnull 函数
CREATE OR REPLACE INTERNAL FUNCTION PUBLIC.IFNULL(CHARACTER VARYING, CHARACTER VARYING) RETURNS CHARACTER VARYING LANGUAGE PLSQL IMMUTABLE STRICT AS $function$ DECLARE BEGIN RETURN ISNULL($1,$2); END; $function$;
FIND_IN_SET 函数
CREATE OR REPLACE FUNCTION CEX2_OA_423.FIND_IN_SET(piv_str1 CHARACTER VARYING, piv_str2 CHARACTER VARYING) RETURNS INTEGER LANGUAGE PLSQL AS DECLARE l_idx NUMBER := 0; tmp text[]; BEGIN SELECT regexp_split_to_array(ISNULL(piv_str2,''), ',') INTO tmp; FOR i IN 0..array_length(tmp, 1) LOOP IF piv_str1 = tmp[i] THEN l_idx = i EXIT; END IF; END LOOP; RETURN l_idx ; END;
JSON_EXTRACT 函数
CREATE OR REPLACE INTERNAL FUNCTION PUBLIC.JSON_EXTRACT(CHARACTER VARYING, CHARACTER VARYING) RETURNS CHARACTER VARYING LANGUAGE PLSQL IMMUTABLE STRICT AS $function$ DECLARE BEGIN RETURN sys_catalog.json_extract_path($1, sys_catalog.replace($2,'$.','')); END; $function$;
substring_index 函数
CREATE OR REPLACE INTERNAL FUNCTION PUBLIC.substring_index(CHARACTER VARYING, CHARACTER VARYING, INTEGER) RETURNS CHARACTER VARYING LANGUAGE PLSQL IMMUTABLE STRICT AS $function$ DECLARE tokens varchar[]; length integer ; indexnum integer; BEGIN tokens := sys_catalog.string_to_array($1, $2); length := sys_catalog.array_upper(tokens, 1); indexnum := length - ($3 * -1) + 1; IF $3 >= 0 THEN RETURN sys_catalog.array_to_string(tokens[1:$3], $2); ELSE RETURN sys_catalog.array_to_string(tokens[indexnum:length], $2); END IF; END; $function$;
instr函数
CREATE OR REPLACE FUNCTION CEX2_OA_423.instr(string CHARACTER VARYING, string_to_search CHARACTER VARYING, beg_index INTEGER, occur_index INTEGER) RETURNS INTEGER LANGUAGE PLSQL AS DECLARE pos integer NOT NULL DEFAULT 0; occur_number integer NOT NULL DEFAULT 0; temp_str varchar; beg integer; i integer; length integer; ss_length integer; BEGIN IF beg_index > 0 THEN beg := beg_index; temp_str := substring(string FROM beg_index); FOR i IN 1..occur_index LOOP pos := position(string_to_search IN temp_str); IF i = 1 THEN beg := beg + pos - 1; ELSE beg := beg + pos; END IF; temp_str := substring(string FROM beg + 1); END LOOP; IF pos = 0 THEN RETURN 0; ELSE RETURN beg; END IF; ELSIF beg_index < 0 THEN ss_length := char_length(string_to_search); length := char_length(string); beg := length + beg_index - ss_length + 2; WHILE beg > 0 LOOP temp_str := substring(string FROM beg FOR ss_length); pos := position(string_to_search IN temp_str); IF pos > 0 THEN occur_number := occur_number + 1; IF occur_number = occur_index THEN RETURN beg; END IF; END IF; beg := beg - 1; END LOOP; RETURN 0; ELSE RETURN 0; END IF; END;
FIELD函数
CREATE OR REPLACE FUNCTION CEX2_OA_423.FIELD(value TEXT, VARIADIC arr TEXT[]) RETURNS INT AS $$ DECLARE i INT; BEGIN FOR i IN 1 .. array_length(arr, 1) LOOP IF arr[i] = value THEN RETURN i; END IF; END LOOP; RETURN 0; END; $$ LANGUAGE plpgsql IMMUTABLE;
本文由 admin 创作,采用 知识共享署名4.0
国际许可协议进行许可
本站文章除注明转载/出处外,均为本站原创或翻译,转载前请务必署名
最后编辑时间为:2024-03-08 11:16:35