CREATE OR REPLACE FUNCTION FUN_GET_PYCODE(p_str IN VARCHAR2, p_flag NUMBER DEFAULT NULL) RETURN VARCHAR2 AS v_compare VARCHAR2(4); v_return VARCHAR2(4000); v_length INT := 0; v_substr VARCHAR2(4); FUNCTION fn_nlssort(p_word IN VARCHAR2) RETURN VARCHAR2 AS BEGIN RETURN SUBSTR(NLSSORT(p_word, 'nls_sort=schinese_pinyin_m'), 1, 4); END fn_nlssort; BEGIN IF p_str IS NULL THEN RETURN ''; END IF; v_length := LENGTH(p_str); CASE p_flag WHEN 1 THEN --首字母小写 FOR i IN 1 .. v_length LOOP v_substr := SUBSTR(p_str, i, 1); v_compare := fn_nlssort(v_substr); CASE WHEN v_compare BETWEEN '3B29' AND '3C82' THEN v_return := v_return || 'a'; WHEN v_compare BETWEEN '3C84' AND '427C' THEN v_return := v_return || 'b'; WHEN v_compare BETWEEN '427D' AND '4BA9' THEN v_return := v_return || 'c'; WHEN v_compare BETWEEN '4BAD' AND '5200' THEN v_return := v_return || 'd'; WHEN v_compare BETWEEN '5205' AND '5332' THEN v_return := v_return || 'e'; WHEN v_compare BETWEEN '5334' AND '5739' THEN v_return := v_return || 'f'; WHEN v_compare BETWEEN '573C' AND '5CB6' THEN v_return := v_return || 'g'; WHEN v_compare BETWEEN '5CB8' AND '63FA' THEN v_return := v_return || 'h'; WHEN v_compare = 'B359' THEN v_return := v_return || 'i'; WHEN v_compare BETWEEN '63FE' AND '6E85' THEN v_return := v_return || 'j'; WHEN v_compare BETWEEN '5BC8' AND '7226' THEN v_return := v_return || 'k'; WHEN v_compare BETWEEN '722C' AND '7C98' THEN v_return := v_return || 'l'; WHEN v_compare BETWEEN '569D' AND '821E' THEN v_return := v_return || 'm'; WHEN v_compare BETWEEN '8224' AND '8574' THEN v_return := v_return || 'n'; WHEN v_compare BETWEEN '8575' AND '85AE' THEN v_return := v_return || 'o'; WHEN v_compare BETWEEN '85B1' AND '89C4' THEN v_return := v_return || 'p'; WHEN v_compare BETWEEN '7E0C' AND '90AA' THEN v_return := v_return || 'q'; WHEN v_compare BETWEEN '90B0' AND '92E4' THEN v_return := v_return || 'r'; WHEN v_compare BETWEEN '92E5' AND '9BA9' THEN v_return := v_return || 's'; WHEN v_compare BETWEEN '5160' AND 'A1AE' THEN v_return := v_return || 't'; WHEN v_compare BETWEEN '9AF0' AND 'A5D4' THEN v_return := v_return || 'w'; WHEN v_compare BETWEEN 'A5D6' AND 'AF1E' THEN v_return := v_return || 'x'; WHEN v_compare BETWEEN 'AF20' AND 'BC58' THEN v_return := v_return || 'y'; WHEN v_compare BETWEEN 'BC59' AND 'C7B4' THEN v_return := v_return || 'z'; ELSE v_return := v_return || v_substr; END CASE; END LOOP; WHEN 2 THEN --首字母大写 FOR i IN 1 .. v_length LOOP v_substr := SUBSTR(p_str, i, 1); v_compare := fn_nlssort(v_substr); CASE WHEN v_compare BETWEEN '3B29' AND '3C82' THEN v_return := v_return || 'A'; WHEN v_compare BETWEEN '3C84' AND '427C' THEN v_return := v_return || 'B'; WHEN v_compare BETWEEN '427D' AND '4BA9' THEN v_return := v_return || 'C'; WHEN v_compare BETWEEN '4BAD' AND '5200' THEN v_return := v_return || 'D'; WHEN v_compare BETWEEN '5205' AND '5332' THEN v_return := v_return || 'E'; WHEN v_compare BETWEEN '5334' AND '5739' THEN v_return := v_return || 'F'; WHEN v_compare BETWEEN '573C' AND '5CB6' THEN v_return := v_return || 'G'; WHEN v_compare BETWEEN '5CB8' AND '63FA' THEN v_return := v_return || 'H'; WHEN v_compare = 'B359' THEN v_return := v_return || 'I'; WHEN v_compare BETWEEN '63FE' AND '6E85' THEN v_return := v_return || 'J'; WHEN v_compare BETWEEN '5BC8' AND '7226' THEN v_return := v_return || 'K'; WHEN v_compare BETWEEN '722C' AND '7C98' THEN v_return := v_return || 'L'; WHEN v_compare BETWEEN '569D' AND '821E' THEN v_return := v_return || 'M'; WHEN v_compare BETWEEN '8224' AND '8574' THEN v_return := v_return || 'N'; WHEN v_compare BETWEEN '8575' AND '85AE' THEN v_return := v_return || 'O'; WHEN v_compare BETWEEN '85B1' AND '89C4' THEN v_return := v_return || 'P'; WHEN v_compare BETWEEN '7E0C' AND '90AA' THEN v_return := v_return || 'Q'; WHEN v_compare BETWEEN '90B0' AND '92E4' THEN v_return := v_return || 'R'; WHEN v_compare BETWEEN '92E5' AND '9BA9' THEN v_return := v_return || 'S'; WHEN v_compare BETWEEN '5160' AND 'A1AE' THEN v_return := v_return || 'T'; WHEN v_compare BETWEEN '9AF0' AND 'A5D4' THEN v_return := v_return || 'W'; WHEN v_compare BETWEEN 'A5D6' AND 'AF1E' THEN v_return := v_return || 'X'; WHEN v_compare BETWEEN 'AF20' AND 'BC58' THEN v_return := v_return || 'Y'; WHEN v_compare BETWEEN 'BC59' AND 'C7B4' THEN v_return := v_return || 'Z'; ELSE v_return := v_return || v_substr; END CASE; END LOOP; ELSE --首字母小写 FOR i IN 1 .. v_length LOOP v_substr := SUBSTR(p_str, i, 1); v_compare := fn_nlssort(v_substr); CASE WHEN v_compare BETWEEN '3B29' AND '3C82' THEN v_return := v_return || 'a'; WHEN v_compare BETWEEN '3C84' AND '427C' THEN v_return := v_return || 'b'; WHEN v_compare BETWEEN '427D' AND '4BA9' THEN v_return := v_return || 'c'; WHEN v_compare BETWEEN '4BAD' AND '5200' THEN v_return := v_return || 'd'; WHEN v_compare BETWEEN '5205' AND '5332' THEN v_return := v_return || 'e'; WHEN v_compare BETWEEN '5334' AND '5739' THEN v_return := v_return || 'f'; WHEN v_compare BETWEEN '573C' AND '5CB6' THEN v_return := v_return || 'g'; WHEN v_compare BETWEEN '5CB8' AND '63FA' THEN v_return := v_return || 'h'; WHEN v_compare = 'B359' THEN v_return := v_return || 'i'; WHEN v_compare BETWEEN '63FE' AND '6E85' THEN v_return := v_return || 'j'; WHEN v_compare BETWEEN '5BC8' AND '7226' THEN v_return := v_return || 'k'; WHEN v_compare BETWEEN '722C' AND '7C98' THEN v_return := v_return || 'l'; WHEN v_compare BETWEEN '569D' AND '821E' THEN v_return := v_return || 'm'; WHEN v_compare BETWEEN '8224' AND '8574' THEN v_return := v_return || 'n'; WHEN v_compare BETWEEN '8575' AND '85AE' THEN v_return := v_return || 'o'; WHEN v_compare BETWEEN '85B1' AND '89C4' THEN v_return := v_return || 'p'; WHEN v_compare BETWEEN '7E0C' AND '90AA' THEN v_return := v_return || 'q'; WHEN v_compare BETWEEN '90B0' AND '92E4' THEN v_return := v_return || 'r'; WHEN v_compare BETWEEN '92E5' AND '9BA9' THEN v_return := v_return || 's'; WHEN v_compare BETWEEN '5160' AND 'A1AE' THEN v_return := v_return || 't'; WHEN v_compare BETWEEN '9AF0' AND 'A5D4' THEN v_return := v_return || 'w'; WHEN v_compare BETWEEN 'A5D6' AND 'AF1E' THEN v_return := v_return || 'x'; WHEN v_compare BETWEEN 'AF20' AND 'BC58' THEN v_return := v_return || 'y'; WHEN v_compare BETWEEN 'BC59' AND 'C7B4' THEN v_return := v_return || 'z'; ELSE v_return := v_return || v_substr; END CASE; END LOOP; END CASE; RETURN v_return; END FUN_GET_PYCODE;