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;
/
