[20251123]oracle SYS_OP_C2C函数.txt

--//优化sql语句中出现SYS_OP_C2C函数，开始我的理解就是将varchar2类型转换位nvarchar2类型。
--//但是我在优化两边都使用，也没有报错，也就是转换是双向的。对SYS_OP_C2C函数不是非常了解，测试看看。

1.环境：
SCOTT@book01p> @ ver2
==============================
PORT_STRING                   : x86_64/Linux 2.4.xx
VERSION                       : 21.0.0.0.0
BANNER                        : Oracle Database 21c Enterprise Edition Release 21.0.0.0.0 - Production
BANNER_FULL                   : Oracle Database 21c Enterprise Edition Release 21.0.0.0.0 - Production
Version 21.3.0.0.0
BANNER_LEGACY                 : Oracle Database 21c Enterprise Edition Release 21.0.0.0.0 - Production
CON_ID                        : 0
PL/SQL procedure successfully completed.

2.测试：

SCOTT@book01p> select dump(SYS_OP_C2C('SALES'),16) from dual ;
DUMP(SYS_OP_C2C('SALES'),16)
---------------------------------------
Typ=96 Len=10: 0,53,0,41,0,4c,0,45,0,53

SCOTT@book01p> select dump(SYS_OP_C2C(U'SALES'),16) from dual ;
DUMP(SYS_OP_C2C(U'SALES'),16
----------------------------
Typ=96 Len=5: 53,41,4c,45,53

--//说明是双向转换的。不过类型都是96.
--//后记：终于明白为什么是Typ=96，对于常量在与char类型等值计算时，结尾可以补充空格。

SCOTT@book01p> select dump(dname,16) c30 ,dname,dump('SALES',16) c40  from dept where deptno=30;
C30                            DNAME                          C40
------------------------------ ------------------------------ ----------------------------------------
Typ=1 Len=5: 53,41,4c,45,53    SALES                          Typ=96 Len=5: 53,41,4c,45,53
--//正常varchar2类型转储Typ=1，带入常量转储typ=96，这个96表示什么？

--//查看DBA_TAB_COLS_V$ 视图定义：
 ,DECODE (
          c.type#
         ,1, DECODE (c.charsetform, 2, 'NVARCHAR2', 'VARCHAR2')
         ,2, DECODE (c.scale, NULL, DECODE (c.precision#, NULL, 'NUMBER', 'FLOAT'), 'NUMBER')
         ,8, 'LONG'
         ,9, DECODE (c.charsetform, 2, 'NCHAR VARYING', 'VARCHAR')
         ,12, 'DATE'
         ,23, 'RAW'
         ,24, 'LONG RAW'
         ,58, NVL2 (
                 ac.synobj#
                , (SELECT o.name
                     FROM obj$ o
                    WHERE o.obj# = ac.synobj#)
                ,ot.name)
         ,69, 'ROWID'
         ,96, DECODE (c.charsetform, 2, 'NCHAR', 'CHAR')
         ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
         ,100, 'BINARY_FLOAT'
         ,101, 'BINARY_DOUBLE'
         ,105, 'MLSLABEL'
         ,106, 'MLSLABEL'
         ,111, NVL2 (
                  ac.synobj#
                 , (SELECT o.name
                      FROM obj$ o
                     WHERE o.obj# = ac.synobj#)
                 ,ot.name)
         ,112, DECODE (c.charsetform, 2, 'NCLOB', 'CLOB')
         ,113, 'BLOB'
         ,114, 'BFILE'
         ,115, 'CFILE'
         ,119, 'JSON'
         ,121, NVL2 (
                  ac.synobj#
                 , (SELECT o.name
                      FROM obj$ o
                     WHERE o.obj# = ac.synobj#)
                 ,ot.name)
         ,122, NVL2 (
                  ac.synobj#
                 , (SELECT o.name
                      FROM obj$ o
                     WHERE o.obj# = ac.synobj#)
                 ,ot.name)
         ,123, NVL2 (
                  ac.synobj#
                 , (SELECT o.name
                      FROM obj$ o
                     WHERE o.obj# = ac.synobj#)
                 ,ot.name)
         ,178, 'TIME(' || c.scale || ')'
         ,179, 'TIME(' || c.scale || ')' || ' WITH TIME ZONE'
         ,180, 'TIMESTAMP(' || c.scale || ')'
         ,181, 'TIMESTAMP(' || c.scale || ')' || ' WITH TIME ZONE'
         ,231, 'TIMESTAMP(' || c.scale || ')' || ' WITH LOCAL TIME ZONE'
         ,182, 'INTERVAL YEAR(' || c.precision# || ') TO MONTH'
         ,183, 'INTERVAL DAY(' || c.precision# || ') TO SECOND(' || c.scale || ')'
         ,208, 'UROWID'
         ,'UNDEFINED')

--//视图中的c对于表sys.col$.
1, DECODE (c.charsetform, 2, 'NVARCHAR2', 'VARCHAR2')
--//oracle该视图定义很奇葩，如果c.charsetform=2，type=1可以是NVARCHAR2类型。
--//按照上面的定义typ=96，对应是char类型。

SCOTT@book01p> variable a char(14)
SCOTT@book01p> exec :a := 'SALES'
PL/SQL procedure successfully completed.

SCOTT@book01p> select dump(:a ,16) c60 from dual ;
C60
------------------------------------------------------------
Typ=96 Len=14: 53,41,4c,45,53,20,20,20,20,20,20,20,20,20
--//如果定义char(14)，在赋值以后结尾会补充空格。

SCOTT@book01p> select * from dual where :a = 'SALES';
D
-
X

SCOTT@book01p> select * from dual where :a = 'SALES '; --//有空格。
D
-
X

--//上面2个都能查询到结果,oracle会根据数据类型char类型字段补充后面的空格。

SCOTT@book01p> variable b varchar2(14)
SCOTT@book01p> exec :b := 'SALES'
PL/SQL procedure successfully completed.

SCOTT@book01p> select * from dual where :b = 'SALES';
D
-
X

SCOTT@book01p> select * from dual where :b = 'SALES ';
no rows selected

--//这样的情况查询没有返回。
--//想起以前一套生产系统遇到的情况，开发定义字符串类型全部使用char类型，而带入的变量都是varchar2类型，通过绑定变量的方式
--//没有返回的信息，只能通过拼接的方式执行sql语句或者使用trunc函数删除字段的尾部的空格。导致大量的文字变量语句以及全表
--//扫描，后面花了大量时间精力更正这个错误。
--//现在再来看那套系统，解决这些问题后，那套系统运行还是很稳定的，可惜最后淘汰了那套系统。