oracle中ascii函式及to_char函式使用及編碼間的轉換

一、ascii->char

SQL> CREATE OR REPLACE PROCEDURE pasc_to_char(one IN VARCHAR2)
2 --RETURN VARCHAR2
3 AS
4 i NUMBER:=1;
5 two VARCHAR2(200):='';
6
7 BEGIN
8 FOR i IN 1..length(one)-1
9 LOOP
10 two:=two||TO_CHAR(ASCII(SUBSTR(one,i,1)));
11 DBMS_OUTPUT.put_line(two);
12 END LOOP;
13 --RETURN two;
14 -- DBMS_OUTPUT.put_line(two);
15 END;
16 /

Procedure created.

SQL> exec pasc_to_char('guojun');
103
103117
103117111
103117111106
103117111106117

PL/SQL procedure successfully completed.

二、

SQL> select to_char(ascii('nihao'),'XXXX') from dual;

TO_CH
-----
6E

SQL> select to_char('nihao','XXXX') from dual;
select to_char('nihao','XXXX') from dual
*
ERROR at line 1:
ORA-01722: invalid number

得出結論：只有ASCII編碼和十六進位制間能相互轉換，而VARCHA2與十六進位制間不能轉換

[@more@]