刷卡pos机怎么输入字母(oracle如何将姓名转换成首字母)

create or replace type spell_code as object(spell varchar2(10),code number);

create or replace type t_spellcode is table of spell_code;

--返回拼音与代码的对应关系create or replace function f_getSpellcode return t_spellcode PipelinedisBegin PIPE Row(spell_code('a', -)); PIPE Row(spell_code('ai', -)); PIPE Row(spell_code('an', -)); PIPE Row(spell_code('ang', -)); PIPE Row(spell_code('ao', -)); PIPE Row(spell_code('ba', -)); PIPE Row(spell_code('bai', -)); PIPE Row(spell_code('ban', -)); PIPE Row(spell_code('bang', -)); PIPE Row(spell_code('bao', -)); PIPE Row(spell_code('bei', -)); PIPE Row(spell_code('ben', -)); PIPE Row(spell_code('beng', -)); PIPE Row(spell_code('bi', -)); PIPE Row(spell_code('bian', -)); PIPE Row(spell_code('biao', -)); PIPE Row(spell_code('bie', -)); PIPE Row(spell_code('bin', -)); PIPE Row(spell_code('bing', -)); PIPE Row(spell_code('bo', -)); PIPE Row(spell_code('bu', -)); PIPE Row(spell_code('ca', -)); PIPE Row(spell_code('cai', -)); PIPE Row(spell_code('can', -)); PIPE Row(spell_code('cang', -)); PIPE Row(spell_code('cao', -)); PIPE Row(spell_code('ce', -)); PIPE Row(spell_code('ceng', -)); PIPE Row(spell_code('cha', -)); PIPE Row(spell_code('chai', -)); PIPE Row(spell_code('chan', -)); PIPE Row(spell_code('chang', -)); PIPE Row(spell_code('chao', -)); PIPE Row(spell_code('che', -)); PIPE Row(spell_code('chen', -)); PIPE Row(spell_code('cheng', -)); PIPE Row(spell_code('chi', -)); PIPE Row(spell_code('chong', -)); PIPE Row(spell_code('chou', -)); PIPE Row(spell_code('chu', -)); PIPE Row(spell_code('chuai', -)); PIPE Row(spell_code('chuan', -)); PIPE Row(spell_code('chuang', -)); PIPE Row(spell_code('chui', -)); PIPE Row(spell_code('chun', -)); PIPE Row(spell_code('chuo', -)); PIPE Row(spell_code('ci', -)); PIPE Row(spell_code('cong', -)); PIPE Row(spell_code('cou', -)); PIPE Row(spell_code('cu', -)); PIPE Row(spell_code('cuan', -)); PIPE Row(spell_code('cui', -)); PIPE Row(spell_code('cun', -)); PIPE Row(spell_code('cuo', -)); PIPE Row(spell_code('da', -)); PIPE Row(spell_code('dai', -)); PIPE Row(spell_code('dan', -)); PIPE Row(spell_code('dang', -)); PIPE Row(spell_code('dao', -)); PIPE Row(spell_code('de', -)); PIPE Row(spell_code('deng', -)); PIPE Row(spell_code('di', -)); PIPE Row(spell_code('dian', -)); PIPE Row(spell_code('diao', -)); PIPE Row(spell_code('die', -)); PIPE Row(spell_code('ding', -)); PIPE Row(spell_code('diu', -)); PIPE Row(spell_code('dong', -)); PIPE Row(spell_code('dou', -)); PIPE Row(spell_code('du', -)); PIPE Row(spell_code('duan', -)); PIPE Row(spell_code('dui', -)); PIPE Row(spell_code('dun', -)); PIPE Row(spell_code('duo', -)); PIPE Row(spell_code('e', -)); PIPE Row(spell_code('en', -)); PIPE Row(spell_code('er', -)); PIPE Row(spell_code('fa', -)); PIPE Row(spell_code('fan', -)); PIPE Row(spell_code('fang', -)); PIPE Row(spell_code('fei', -)); PIPE Row(spell_code('fen', -)); PIPE Row(spell_code('feng', -)); PIPE Row(spell_code('fo', -)); PIPE Row(spell_code('fou', -)); PIPE Row(spell_code('fu', -)); PIPE Row(spell_code('ga', -)); PIPE Row(spell_code('gai', -)); PIPE Row(spell_code('gan', -)); PIPE Row(spell_code('gang', -)); PIPE Row(spell_code('gao', -)); PIPE Row(spell_code('ge', -)); PIPE Row(spell_code('gei', -)); PIPE Row(spell_code('gen', -)); PIPE Row(spell_code('geng', -)); PIPE Row(spell_code('gong', -)); PIPE Row(spell_code('gou', -)); PIPE Row(spell_code('gu', -)); PIPE Row(spell_code('gua', -)); PIPE Row(spell_code('guai', -)); PIPE Row(spell_code('guan', -)); PIPE Row(spell_code('guang', -)); PIPE Row(spell_code('gui', -)); PIPE Row(spell_code('gun', -)); PIPE Row(spell_code('guo', -)); PIPE Row(spell_code('ha', -)); PIPE Row(spell_code('hai', -)); PIPE Row(spell_code('han', -)); PIPE Row(spell_code('hang', -)); PIPE Row(spell_code('hao', -)); PIPE Row(spell_code('he', -)); PIPE Row(spell_code('hei', -)); PIPE Row(spell_code('hen', -)); PIPE Row(spell_code('heng', -)); PIPE Row(spell_code('hong', -)); PIPE Row(spell_code('hou', -)); PIPE Row(spell_code('hu', -)); PIPE Row(spell_code('hua', -)); PIPE Row(spell_code('huai', -)); PIPE Row(spell_code('huan', -)); PIPE Row(spell_code('huang', -)); PIPE Row(spell_code('hui', -)); PIPE Row(spell_code('hun', -)); PIPE Row(spell_code('huo', -)); PIPE Row(spell_code('ji', -)); PIPE Row(spell_code('jia', -)); PIPE Row(spell_code('jian', -)); PIPE Row(spell_code('jiang', -)); PIPE Row(spell_code('jiao', -)); PIPE Row(spell_code('jie', -)); PIPE Row(spell_code('jin', -)); PIPE Row(spell_code('jing', -)); PIPE Row(spell_code('jiong', -)); PIPE Row(spell_code('jiu', -)); PIPE Row(spell_code('ju', -)); PIPE Row(spell_code('juan', -)); PIPE Row(spell_code('jue', -)); PIPE Row(spell_code('jun', -)); PIPE Row(spell_code('ka', -)); PIPE Row(spell_code('kai', -)); PIPE Row(spell_code('kan', -)); PIPE Row(spell_code('kang', -)); PIPE Row(spell_code('kao', -)); PIPE Row(spell_code('ke', -)); PIPE Row(spell_code('ken', -)); PIPE Row(spell_code('keng', -)); PIPE Row(spell_code('kong', -)); PIPE Row(spell_code('kou', -)); PIPE Row(spell_code('ku', -)); PIPE Row(spell_code('kua', -)); PIPE Row(spell_code('kuai', -)); PIPE Row(spell_code('kuan', -)); PIPE Row(spell_code('kuang', -)); PIPE Row(spell_code('kui', -)); PIPE Row(spell_code('kun', -)); PIPE Row(spell_code('kuo', -)); PIPE Row(spell_code('la', -)); PIPE Row(spell_code('lai', -)); PIPE Row(spell_code('lan', -)); PIPE Row(spell_code('lang', -)); PIPE Row(spell_code('lao', -)); PIPE Row(spell_code('le', -)); PIPE Row(spell_code('lei', -)); PIPE Row(spell_code('leng', -)); PIPE Row(spell_code('li', -)); PIPE Row(spell_code('lia', -)); PIPE Row(spell_code('lian', -)); PIPE Row(spell_code('liang', -)); PIPE Row(spell_code('liao', -)); PIPE Row(spell_code('lie', -)); PIPE Row(spell_code('lin', -)); PIPE Row(spell_code('ling', -)); PIPE Row(spell_code('liu', -)); PIPE Row(spell_code('long', -)); PIPE Row(spell_code('lou', -)); PIPE Row(spell_code('lu', -)); PIPE Row(spell_code('lv', -)); PIPE Row(spell_code('luan', -)); PIPE Row(spell_code('lue', -)); PIPE Row(spell_code('lun', -)); PIPE Row(spell_code('luo', -)); PIPE Row(spell_code('ma', -)); PIPE Row(spell_code('mai', -)); PIPE Row(spell_code('man', -)); PIPE Row(spell_code('mang', -)); PIPE Row(spell_code('mao', -)); PIPE Row(spell_code('me', -)); PIPE Row(spell_code('mei', -)); PIPE Row(spell_code('men', -)); PIPE Row(spell_code('meng', -)); PIPE Row(spell_code('mi', -)); PIPE Row(spell_code('mian', -)); PIPE Row(spell_code('miao', -)); PIPE Row(spell_code('mie', -)); PIPE Row(spell_code('min', -)); PIPE Row(spell_code('ming', -)); PIPE Row(spell_code('miu', -)); PIPE Row(spell_code('mo', -)); PIPE Row(spell_code('mou', -)); PIPE Row(spell_code('mu', -)); PIPE Row(spell_code('na', -)); PIPE Row(spell_code('nai', -)); PIPE Row(spell_code('nan', -)); PIPE Row(spell_code('nang', -)); PIPE Row(spell_code('nao', -)); PIPE Row(spell_code('ne', -)); PIPE Row(spell_code('nei', -)); PIPE Row(spell_code('nen', -)); PIPE Row(spell_code('neng', -)); PIPE Row(spell_code('ni', -)); PIPE Row(spell_code('nian', -)); PIPE Row(spell_code('niang', -)); PIPE Row(spell_code('niao', -)); PIPE Row(spell_code('nie', -)); PIPE Row(spell_code('nin', -)); PIPE Row(spell_code('ning', -)); PIPE Row(spell_code('niu', -)); PIPE Row(spell_code('nong', -)); PIPE Row(spell_code('nu', -)); PIPE Row(spell_code('nv', -)); PIPE Row(spell_code('nuan', -)); PIPE Row(spell_code('nue', -)); PIPE Row(spell_code('nuo', -)); PIPE Row(spell_code('o', -)); PIPE Row(spell_code('ou', -)); PIPE Row(spell_code('pa', -)); PIPE Row(spell_code('pai', -)); PIPE Row(spell_code('pan', -)); PIPE Row(spell_code('pang', -)); PIPE Row(spell_code('pao', -)); PIPE Row(spell_code('pei', -)); PIPE Row(spell_code('pen', -)); PIPE Row(spell_code('peng', -)); PIPE Row(spell_code('pi', -)); PIPE Row(spell_code('pian', -)); PIPE Row(spell_code('piao', -)); PIPE Row(spell_code('pie', -)); PIPE Row(spell_code('pin', -)); PIPE Row(spell_code('ping', -)); PIPE Row(spell_code('po', -)); PIPE Row(spell_code('pu', -)); PIPE Row(spell_code('qi', -)); PIPE Row(spell_code('qia', -)); PIPE Row(spell_code('qian', -)); PIPE Row(spell_code('qiang', -)); PIPE Row(spell_code('qiao', -)); PIPE Row(spell_code('qie', -)); PIPE Row(spell_code('qin', -)); PIPE Row(spell_code('qing', -)); PIPE Row(spell_code('qiong', -)); PIPE Row(spell_code('qiu', -)); PIPE Row(spell_code('qu', -)); PIPE Row(spell_code('quan', -)); PIPE Row(spell_code('que', -)); PIPE Row(spell_code('qun', -)); PIPE Row(spell_code('ran', -)); PIPE Row(spell_code('rang', -)); PIPE Row(spell_code('rao', -)); PIPE Row(spell_code('re', -)); PIPE Row(spell_code('ren', -)); PIPE Row(spell_code('reng', -)); PIPE Row(spell_code('ri', -)); PIPE Row(spell_code('rong', -)); PIPE Row(spell_code('rou', -)); PIPE Row(spell_code('ru', -)); PIPE Row(spell_code('ruan', -)); PIPE Row(spell_code('rui', -)); PIPE Row(spell_code('run', -)); PIPE Row(spell_code('ruo', -)); PIPE Row(spell_code('sa', -)); PIPE Row(spell_code('sai', -)); PIPE Row(spell_code('san', -)); PIPE Row(spell_code('sang', -)); PIPE Row(spell_code('sao', -)); PIPE Row(spell_code('se', -)); PIPE Row(spell_code('sen', -)); PIPE Row(spell_code('seng', -)); PIPE Row(spell_code('sha', -)); PIPE Row(spell_code('shai', -)); PIPE Row(spell_code('shan', -)); PIPE Row(spell_code('shang', -)); PIPE Row(spell_code('shao', -)); PIPE Row(spell_code('she', -)); PIPE Row(spell_code('shen', -)); PIPE Row(spell_code('sheng', -)); PIPE Row(spell_code('shi', -)); PIPE Row(spell_code('shou', -)); PIPE Row(spell_code('shu', -)); PIPE Row(spell_code('shua', -)); PIPE Row(spell_code('shuai', -)); PIPE Row(spell_code('shuan', -)); PIPE Row(spell_code('shuang', -)); PIPE Row(spell_code('shui', -)); PIPE Row(spell_code('shun', -)); PIPE Row(spell_code('shuo', -)); PIPE Row(spell_code('si', -)); PIPE Row(spell_code('song', -)); PIPE Row(spell_code('sou', -)); PIPE Row(spell_code('su', -)); PIPE Row(spell_code('suan', -)); PIPE Row(spell_code('sui', -)); PIPE Row(spell_code('sun', -)); PIPE Row(spell_code('suo', -)); PIPE Row(spell_code('ta', -)); PIPE Row(spell_code('tai', -)); PIPE Row(spell_code('tan', -)); PIPE Row(spell_code('tang', -)); PIPE Row(spell_code('tao', -)); PIPE Row(spell_code('te', -)); PIPE Row(spell_code('teng', -)); PIPE Row(spell_code('ti', -)); PIPE Row(spell_code('tian', -)); PIPE Row(spell_code('tiao', -)); PIPE Row(spell_code('tie', -)); PIPE Row(spell_code('ting', -)); PIPE Row(spell_code('tong', -)); PIPE Row(spell_code('tou', -)); PIPE Row(spell_code('tu', -)); PIPE Row(spell_code('tuan', -)); PIPE Row(spell_code('tui', -)); PIPE Row(spell_code('tun', -)); PIPE Row(spell_code('tuo', -)); PIPE Row(spell_code('wa', -)); PIPE Row(spell_code('wai', -)); PIPE Row(spell_code('wan', -)); PIPE Row(spell_code('wang', -)); PIPE Row(spell_code('wei', -)); PIPE Row(spell_code('wen', -)); PIPE Row(spell_code('weng', -)); PIPE Row(spell_code('wo', -)); PIPE Row(spell_code('wu', -)); PIPE Row(spell_code('xi', -)); PIPE Row(spell_code('xia', -)); PIPE Row(spell_code('xian', -)); PIPE Row(spell_code('xiang', -)); PIPE Row(spell_code('xiao', -)); PIPE Row(spell_code('xie', -)); PIPE Row(spell_code('xin', -)); PIPE Row(spell_code('xing', -)); PIPE Row(spell_code('xiong', -)); PIPE Row(spell_code('xiu', -)); PIPE Row(spell_code('xu', -)); PIPE Row(spell_code('xuan', -)); PIPE Row(spell_code('xue', -)); PIPE Row(spell_code('xun', -)); PIPE Row(spell_code('ya', -)); PIPE Row(spell_code('yan', -)); PIPE Row(spell_code('yang', -)); PIPE Row(spell_code('yao', -)); PIPE Row(spell_code('ye', -)); PIPE Row(spell_code('yi', -)); PIPE Row(spell_code('yin', -)); PIPE Row(spell_code('ying', -)); PIPE Row(spell_code('yo', -)); PIPE Row(spell_code('yong', -)); PIPE Row(spell_code('you', -)); PIPE Row(spell_code('yu', -)); PIPE Row(spell_code('yuan', -)); PIPE Row(spell_code('yue', -)); PIPE Row(spell_code('yun', -)); PIPE Row(spell_code('za', -)); PIPE Row(spell_code('zai', -)); PIPE Row(spell_code('zan', -)); PIPE Row(spell_code('zang', -)); PIPE Row(spell_code('zao', -)); PIPE Row(spell_code('ze', -)); PIPE Row(spell_code('zei', -)); PIPE Row(spell_code('zen', -)); PIPE Row(spell_code('zeng', -)); PIPE Row(spell_code('zha', -)); PIPE Row(spell_code('zhai', -)); PIPE Row(spell_code('zhan', -)); PIPE Row(spell_code('zhang', -)); PIPE Row(spell_code('zhao', -)); PIPE Row(spell_code('zhe', -)); PIPE Row(spell_code('zhen', -)); PIPE Row(spell_code('zheng', -)); PIPE Row(spell_code('zhi', -)); PIPE Row(spell_code('zhong', -)); PIPE Row(spell_code('zhou', -)); PIPE Row(spell_code('zhu', -)); PIPE Row(spell_code('zhua', -)); PIPE Row(spell_code('zhuai', -)); PIPE Row(spell_code('zhuan', -)); PIPE Row(spell_code('zhuang', -)); PIPE Row(spell_code('zhui', -)); PIPE Row(spell_code('zhun', -)); PIPE Row(spell_code('zhuo', -)); PIPE Row(spell_code('zi', -)); PIPE Row(spell_code('zong', -)); PIPE Row(spell_code('zou', -)); PIPE Row(spell_code('zu', -)); PIPE Row(spell_code('zuan', -)); PIPE Row(spell_code('zui', -)); PIPE Row(spell_code('zun', -)); PIPE Row(spell_code('zuo', -)); Return;end;--hanjs,07-10-24,返回拼音与代码的对应关系

--此函数默认返回汉字拼音的首字母，第二个参数不为空则返回全拼。create or replace function f_getFirstOrFullSpell(p_cnStr In varchar2,p_sign In number default null) return varchar2aslv_spell varchar2(200);lv_temp Varchar2(10);lv_char varchar2(10); --lv_bytes varchar2(100); li_bytes Integer; --li_pos Integer;begin if p_cnStr is null then return ''; end if; for i In 1..length(p_cnStr) loop lv_char:=substr(p_cnStr,i,1); if lengthb(lv_char) = 1 then lv_spell:=lv_spell||lv_char; elsif lengthb(lv_char) = 2 then --Select replace(substrb(dump(lv_char,1010),instrb(dump(lv_char,1010),'ZHS16GBK:')),'ZHS16GBK: ','') Into lv_bytes from dual; --li_pos:=instr(lv_bytes,',');--li_bytes:=substr(lv_bytes,1,li_pos-1)*256+substr(lv_bytes,li_pos+1)-256*256; Select ascii(lv_char)-256*256 Into li_bytes From dual; select max(spell) Into lv_temp from table(f_getSpellcode) where code<=li_bytes; if p_sign is null then lv_spell:=lv_spell||substr(lv_temp,1,1); else lv_spell:=lv_spell||lv_temp;end if;elsif lengthb(lv_char) = 3 then --Select replace(substrb(dump(convert(lv_char,'ZHS16GBK','UTF8'),1010),instrb(dump(convert(lv_char,'ZHS16GBK','UTF8'),1010),'UTF8:')),'UTF8: ','') --Into lv_bytes from dual; --li_pos:=instr(lv_bytes,','); --li_bytes:=substr(lv_bytes,1,li_pos-1)*256+substr(lv_bytes,li_pos+1)-256*256; Select ascii(lv_char)-256*256 Into li_bytes From dual; select max(spell) Into lv_temp from table(f_getSpellcode) where code<=li_bytes; if p_sign is null then lv_spell:=lv_spell||substr(lv_char,1,1); else lv_spell:=lv_spell||lv_char;end if; end if; end loop;return lv_spell;end;--hanjs,07-10-24,此函数默认返回汉字拼音的首字母，第二个参数不为空则返回全拼。

--测试--------------------------------------------------------------------------------SQL> select f_getFirstOrFullSpell('张三') from dual;--------------------------------------------------------------------------------zs

select f_getFirstOrFullSpell('张三',1) from dual;--------------------------------------------------------------------------------zhangsan