REM
REM建立汉语拼音库，根据汉字查拼音
REM应用：如商品或者货物名称可以根据拼音去查，数据可以按拼音排序
REM作者：朱伟民 时间： 12:24 2001-10-07
REM
--
-- 汉字拼音编码表 , 可以用程序读汉字库获得记录
--
DROP TABLE chinese_code;
CREATE TABLE chinese_code(
ascii_code INTEGER,-- 汉字的 ASCII 码
chinese_Word VARCHAR2(2),-- 汉字
chinese_anno VARCHAR2(5),-- 汉语拼音
CONSTRAINT PK_chinese_code PRIMARY KEY(ascii_code),
CONSTRAINT CK_chinese_code1 CHECK("CHINESE_WORD" IS NOT NULL))
PCTFREE 5
PCTUSED 75
TABLESPACE users
STORAGE(INITIAL 50K
NEXT 50K
MINEXTENTS 2
MAXEXTENTS 100
PCTINCREASE 75)
ENABLE PRIMARY KEY USING INDEX
TABLESPACE INDX
PCTFREE 5;
ALTER TABLE chinese_code DISABLE PRIMARY KEY;
INSERT INTO chinese_code VALUES(ASCII('中'),'中','zhong');
INSERT INTO chinese_code VALUES(ASCII('华'),'华','hua');
INSERT INTO chinese_code VALUES(ASCII('人'),'人','ren');
INSERT INTO chinese_code VALUES(ASCII('民'),'民','ming');
INSERT INTO chinese_code VALUES(ASCII('共'),'共','gong');
INSERT INTO chinese_code VALUES(ASCII('和'),'和','he');
INSERT INTO chinese_code VALUES(ASCII('国'),'国','guo');
COMMIT;
ALTER TABLE chinese_code ENABLE PRIMARY KEY;
SELECT * FROM chinese_code;
--
-- 根据汉字查询拼音函数
--
CREATE OR REPLACE FUNCTION query_anno(
inputStr IN VARCHAR2)
RETURN VARCHAR2 AS
outputStr chinese_code.chinese_anno%TYPE;
BEGIN
SELECT chinese_anno INTO outputStr FROM chinese_code
WHERE ascii_code = ASCII(inputStr);
RETURN outputStr;
EXCEPTION
WHEN OTHERS THEN
RETURN '';
END;
/