--獲取漢字拼音和拼音首字母的功能函數
--作者：david.liu
--email/msn: david2068@gmail.com
--日期：2006-10-06 19:00--23:59
--select GetHzFullPY('中華人民共和國') from dual;
--select GetHzPYCAP('中華人民共和國') from dual;

--函數GetHzFullPY(string)用于獲取漢字字符串的拼音
--select GetHzFullPY('中華人民共和國') from dual;
--返回：ZhongHuaRenMinGongHeGuo

--函數GetHzPYCAP(string)用于獲取拼音首字母
--select GetHzPYCAP('中華人民共和國') from dual;
--返回ZHRMGHG

CREATE OR REPLACE TYPE THZPY_LIST AS VARRAY (526) OF  VARCHAR2(6);

CREATE OR REPLACE TYPE TROMA_NUM_LIST AS VARRAY (94) OF  VARCHAR2(2);

CREATE OR REPLACE TYPE TGREECE_ALPHABET_LIST AS VARRAY (24) OF  VARCHAR2(2);

DROP TYPE TPYIndex_list;

CREATE OR REPLACE TYPE TPYIndex_191_list IS VARRAY(191) OF NUMBER;

CREATE OR REPLACE TYPE TPYIndex_list IS VARRAY(10) OF TPYIndex_191_list;
/

CREATE OR REPLACE FUNCTION GetHzPY_by_index(
  p_PY_Index number)
 RETURN VARCHAR2 IS
 v_PY_List THZPY_LIST :=  THZPY_LIST(
    'a',      'aes',    'ai',     'an',     'ang',    'ao',     'ba',     'bai',    'baike',  'baiwa',
    'ban',    'bang',   'bao',    'be',     'bei',    'ben',    'beng',   'bi',     'bia',    'bian',
    'biao',   'bie',    'bin',    'bing',   'bo',     'bu',     'ca',     'cai',    'cal',    'can',
    'cang',   'cao',    'ce',     'cen',    'ceng',   'ceok',   'ceom',   'ceon',   'ceor',   'cha',
    'chai',   'chan',   'chang',  'chao',   'che',    'chen',   'cheng',  'chi',    'chong',  'chou',
    'chu',    'chua',   'chuai',  'chuan',  'chuang', 'chui',   'chun',   'chuo',   'ci',     'cis',
    'cong',   'cou',    'cu',     'cuan',   'cui',    'cun',    'cuo',    'da',     'dai',    'dan',
    'dang',   'dao',    'de',     'defa',   'dei',    'deli',   'dem',    'den',    'deng',   'deo',
    'di',     'dia',    'dian',   'diao',   'die',    'dim',    'ding',   'diu',    'dong',   'dou',
    'du',     'duan',   'dug',    'dui',    'dul',    'dun',    'duo',    'e',      'ei',     'en',
    'eng',    'eo',     'eol',    'eom',    'eos',    'er',     'fa',     'fan',    'fang',   'fei',
    'fen',    'feng',   'fenwa',  'fiao',   'fo',     'fou',    'fu',     'fui',    'ga',     'gad',
    'gai',    'gan',    'gang',   'gao',    'ge',     'gei',    'gen',    'geng',   'geo',    'geu',
    'gib',    'go',     'gong',   'gongli', 'gou',    'gu',     'gua',    'guai',   'guan',   'guang',
    'gui',    'gun',    'guo',    'ha',     'hai',    'hal',    'han',    'hang',   'hao',    'haoke',
    'he',     'hei',    'hem',    'hen',    'heng',   'heui',   'ho',     'hol',    'hong',   'hou',
    'hu',     'hua',    'huai',   'huan',   'huang',  'hui',    'hun',    'huo',    'hwa',    'hweong',
    'i',      'ji',     'jia',    'jialun', 'jian',   'jiang',  'jiao',   'jie',    'jin',    'jing',
    'jiong',  'jiu',    'jou',    'ju',     'juan',   'jue',    'jun',    'ka',     'kai',    'kal',
    'kan',    'kang',   'kao',    'ke',     'keg',    'kei',    'kem',    'ken',    'keng',   'keo',
    'keol',   'keop',   'keos',   'keum',   'ki',     'kong',   'kos',    'kou',    'ku',     'kua',
    'kuai',   'kuan',   'kuang',  'kui',    'kun',    'kuo',    'kweok',  'kwi',    'la',     'lai',
    'lan',    'lang',   'lao',    'le',     'lei',    'lem',    'len',    'leng',   'li',     'lia',
    'lian',   'liang',  'liao',   'lie',    'lin',    'ling',   'liu',    'liwa',   'lo',     'long',
    'lou',    'lu',     'luan',   'lue',    'lun',    'luo',    'lv',     'm',      'ma',     'mai',
    'man',    'mang',   'mangmi', 'mao',    'mas',    'me',     'mei',    'men',    'meng',   'meo',
    'mi',     'mian',   'miao',   'mie',    'min',    'ming',   'miu',    'mo',     'mol',    'mou',
    'mu',     'myeo',   'myeon',  'myeong', 'n',      'na',     'nai',    'nan',    'nang',   'nao',
    'ne',     'nei',    'nem',    'nen',    'neng',   'neus',   'ng',     'ngag',   'ngai',   'ngam',
    'ni',     'nian',   'niang',  'niao',   'nie',    'nin',    'ning',   'niu',    'nong',   'nou',
    'nu',     'nuan',   'nue',    'nun',    'nung',   'nuo',    'nv',     'nve',    'o',      'oes',
    'ol',     'on',     'ou',     'pa',     'pai',    'pak',    'pan',    'pang',   'pao',    'pei',
    'pen',    'peng',   'peol',   'phas',   'phdeng', 'phoi',   'phos',   'pi',     'pian',   'piao',
    'pie',    'pin',    'ping',   'po',     'pou',    'ppun',   'pu',     'q',      'qi',     'qia',
    'qian',   'qiang',  'qianke', 'qianwa', 'qiao',   'qie',    'qin',    'qing',   'qiong',  'qiu',
    'qu',     'quan',   'que',    'qun',    'ra',     'ram',    'ran',    'rang',   'rao',    're',
    'ren',    'reng',   'ri',     'rong',   'rou',    'ru',     'rua',    'ruan',   'rui',    'run',
    'ruo',    'sa',     'saeng',  'sai',    'sal',    'san',    'sang',   'sao',    'se',     'sed',
    'sei',    'sen',    'seng',   'seo',    'seon',   'sha',    'shai',   'shan',   'shang',  'shao',
    'she',    'shei',   'shen',   'sheng',  'shi',    'shike',  'shiwa',  'shou',   'shu',    'shua',
    'shuai',  'shuan',  'shuang', 'shui',   'shun',   'shuo',   'shw',    'si',     'so',     'sol',
    'song',   'sou',    'su',     'suan',   'sui',    'sun',    'suo',    'ta',     'tae',    'tai',
    'tan',    'tang',   'tao',    'tap',    'te',     'tei',    'teng',   'teo',    'teul',   'teun',
    'ti',     'tian',   'tiao',   'tie',    'ting',   'tiu',    'tol',    'ton',    'tong',   'tou',
    'tu',     'tuan',   'tui',    'tun',    'tuo',    'uu',     'wa',     'wai',    'wan',    'wang',
    'wei',    'wen',    'weng',   'wie',    'wo',     'wu',     'xi',     'xia',    'xian',   'xiang',
    'xiao',   'xie',    'xin',    'xing',   'xiong',  'xiu',    'xu',     'xuan',   'xue',    'xun',
    'ya',     'yan',    'yang',   'yao',    'ye',     'yen',    'yi',     'yin',    'ying',   'yo',
    'yong',   'you',    'yu',     'yuan',   'yue',    'yug',    'yun',    'za',     'zad',    'zai',
    'zan',    'zang',   'zao',    'ze',     'zei',    'zen',    'zeng',   'zha',    'zhai',   'zhan',
    'zhang',  'zhao',   'zhe',    'zhei',   'zhen',   'zheng',  'zhi',    'zhong',  'zhou',   'zhu',
    'zhua',   'zhuai',  'zhuan',  'zhuang', 'zhui',   'zhun',   'zhuo',   'zi',     'zo',     'zong',
    'zou',    'zu',     'zuan',   'zui',    'zun',    'zuo'
    );
BEGIN
    IF (p_PY_Index>0) AND (p_PY_Index<527) THEN
       RETURN  INITCAP(v_PY_List(p_PY_Index));
    ELSE
      RETURN '';
    END IF;
END GetHzPY_by_index;
/

CREATE OR REPLACE FUNCTION get_greece_alphabet_py(p_Index NUMBER)
RETURN NUMBER IS
  v_greece_alphabet_list TGREECE_ALPHABET_LIST := TGREECE_ALPHABET_LIST(
    'a','b','g','d','e','z','e','th','i','k','l','m','n','x','o','p','r',
    's','t','u','ph','kh','ps','o'
  );
BEGIN
  IF (p_Index>0) AND (p_Index<95) THEN
    RETURN v_greece_alphabet_list(p_Index);
  ELSE
    RETURN '';
  END IF;
end get_greece_alphabet_py;
/

CREATE OR REPLACE FUNCTION get_roma_num_py(p_Index NUMBER)
RETURN NUMBER IS
  v_rom_num_list TROMA_NUM_LIST := TROMA_NUM_LIST(
    '1','2','3','4','5','6','7','8','9','10','','','','','','',
    '1','2','3','4','5','6','7','8','9','10','11','12','13','14','15','16','17','18','19','20',
    '1','2','3','4','5','6','7','8','9','10','11','12','13','14','15','16','17','18','19','20',
    '1','2','3','4','5','6','7','8','9','10','','',
    '1','2','3','4','5','6','7','8','9','10','','',
    '1','2','3','4','5','6','7','8','9','10','11','12','',''
  );
BEGIN
  IF (p_Index>0) AND (p_Index<95) THEN
    RETURN v_rom_num_list(p_Index);
  ELSE
    RETURN '';
  END IF;
end get_roma_num_py;
/

CREATE OR REPLACE FUNCTION get_py_index_01(p_Index1 NUMBER, p_Index NUMBER)
RETURN NUMBER IS
  v_list01 TPYIndex_list := TPYIndex_list(
    TPYIndex_191_list(483,389,458,273,262, 50,395, 88,350,232,482, 24,182,172,178,213, 42,517,144,180,
     117,477,477,456,182,157,508,161,394,478,471,121,182,146,158, 90,395,279,190,201,
     437,269,311, 29,469,472,326,386,276,341,410,103, 65, 39,507,141,122,243,235,477,
     186,249,507,  0,483,408,415,128,471,499,471, 68,475,460,180,475,482,500,231, 97,
     451,172,355,456,  7, 24,115,423,102,459,503,159,147, 25, 44,501,389,361,108,263,
     341,455,474,112, 55,450, 81,508,320,483, 84, 96,456,477,463,172,  3,478,328,393,
     117,422,522,487,184,459,470,463,494,459,301,291,462,467,509,522, 17,328,477,408,
     477,506,147,250,510, 26,351, 18,502, 59,473,500, 18,459,351,395, 13,166,151,460,
     125,107,266, 24,155,168,141,352, 59,464,393,445,145,220,477,140,478,261,467,  4,
     242,106,245, 40, 48,470,509,366,175,408, 69),
    TPYIndex_191_list(418,297,179,181,435,505,526, 50,247,184,399,435,393,445, 25,278,461,443,483,457,
     467,140,209,456,477,117,232,167,479,459,376,320,457,262,458,466, 81,184,507,220,
     408,168,461,175, 21,431,110,471, 15,483,463,161,506,507, 24,182,474,522,232,449,
     234, 55,520,  0,125,432,399,258,421,515,464,333,339,122,232,415,346,109,507,520,
     245,411,236,167, 89,518, 16,456,184,277, 28,175,475,386,346,479, 47,341,368,508,
      57,451,483, 24,431,472,112,422,455, 98, 45,394,191, 81, 40, 15,498,165,474,500,
     521,472,482,467,498, 59,117,117,507,262,172,477,462,470,408, 92,499,505,440, 15,
     491,346,451,412,507,413,458,484,364,301,487,176,249, 83,422,149,178,457,388,341,
     353, 46, 51,376, 15,461,481,474,421,417,473,107, 24,460,490,136,376,225,481,493,
     520,322,411,513,483,499,522,389, 55,180,147),
    TPYIndex_191_list(501,348,478, 81,462,241, 15,330,179,231,242,251,341,459,421,479, 89,525,388,345,
     181,443,525,337,223, 43,140,339,427,513,451,172, 25,166, 57,434,388,474,111,459,
     483, 98,235, 25,136,459,459,265,475,179,340,345,112,509,  3,374,477,187,299,421,
     477, 71,211,  0,175, 51,177,386,490, 30, 23,  4,420, 72, 41,221,477,179,341,259,
     456,297,349,291, 43,234,247,213, 13,483, 21,491,507,408,482,149,348,347,229,427,
     451,240, 51, 42,460,433,462,229,246,491,306,422,472,246,279,491,465,369,369,441,
      43,291,291,179,472,395,396,343,  0,150,393, 90,  9,134,165,456,369,232,483,147,
     432,336,172,477,254,357,472,254,498,181,137,181,254,509,135,467,482,191,477,261,
     395,259,184,208,265,117,462,261,420,123,161,317,117,265,340,175,412,257,441,136,
     180,348, 89,122,478,  3,229, 31,266,516, 65),
    TPYIndex_191_list(408, 97,179,235,457, 91,108,108,184, 51,506,112,271,507,112,112,189,122,333,211,
     147,361, 55,172,341, 66,172, 70,449,186,229,117,351, 84,265,236,508, 22,178,178,
     388, 42,128, 55,214, 97,106,178, 59,180, 90,246,494,484, 67,194,386, 55, 67,229,
     110, 42,339,  0, 55,518,123,337, 97,348,517,175,172,472,168, 97,507,456,137,394,
     175,498,189,342, 54, 42,513,242,229,322,388,208,137,162,498,517,231,184,237,141,
     177,141,175,175,439,172,175,175,507, 42,523,268,229,510,471,180,199,462,507,477,
     510,268,223,185,208,473,447,461,270,213,178,234,194,180,124,265, 48,222,481,194,
     185,348,242, 26,220,189,262, 89,467,456,477,470,473,394,233,242,330,395,172,342,
     177,352,460,477,469,108,185,439,184, 70,250,470,470,247,229, 45,460,352,487,182,
      13,253, 18,121,121,477,322,184,474,125, 98),
    TPYIndex_191_list(133, 68,182,133,280,182,477,176,192,161,351,108,346,492,213,161,483,141,166, 70,