oracle,查詢做簡繁體?
- 2022-11-22
很高興回答你的問題。
做繁簡體轉換其實是為了實現國際化的一種方式。我就從資料庫方面來說一下如何實現繁簡體轉換。
新建一個儲存簡繁體字對應的表 chineseword(simpleword, traditionword)
寫一個儲存過程向chineseword表中插入資料。
create or replace PROCEDURE insertchineseword IS
s_str1 VARCHAR2(4000);
s_str2 VARCHAR2(4000);
i INT := 0;
BEGIN
s_str1 := ‘擬好’;
t_str1 := ‘中國’;
for i in 1。。 length(s_str1)
loop
insert into chineseword values(substr(s_str1,i,1), substr(t_str1, i, 1));
end loop;
for i in 1。。 length(s_str2)
loop
insert into chineseword values(substr(s_str2,i,1), substr(t_str2, i, 1));
end loop;
commit;
EXCEPTION
WHEN NO_DATA_FOUND THEN
NULL;
WHEN OTHERS THEN
—— Consider logging the error and then re-raise
RAISE;
END insertchineseword;
3。 寫一個函式進行簡繁體字的替換。
create or replace FUNCTION TRANS_CHINESE(p_inputStr IN VARCHAR2,
p_inputType IN NUMBER)
RETURN VARCHAR2
IS
v_ret VARCHAR2(300) := ‘’;
i INT := 0;
v_str VARCHAR2(3);
v_count INT := 0;
BEGIN
if p_inputStr is null or p_inputStr = ‘’ then
return null;
end if;
if p_inputType = 0 then
for i in 1 。。 length(p_inputStr)
loop
select count(*)
into v_count
from chineseword
where simpleword = substr(p_inputStr, i, 1);
if v_count > 0 then
select traditionword
into v_str
from chineseword
where simpleword = substr(p_inputStr, i, 1);
v_ret := v_ret || v_str;
else
v_ret := v_ret || substr(p_inputStr, i, 1);
end if;
end loop;
else
for i in 1 。。 length(p_inputStr)
loop
select count(*)
into v_count
from chineseword
where traditionword = substr(p_inputStr, i, 1);
if v_count > 0 then
select simpleword
into v_str
from chineseword
where traditionword = substr(p_inputStr, i, 1);
v_ret := v_ret || v_str;
else
v_ret := v_ret || substr(p_inputStr, i, 1);
end if;
end loop;
end if;
RETURN v_ret;
EXCEPTION
WHEN NO_DATA_FOUND THEN
NULL;
WHEN OTHERS THEN
—— Consider logging the error and then re-raise
RAISE;
END TRANS_CHINESE;
4。 執行查詢
select trans_chinese(‘你好’,0) from dual