[20251123]oracle SYS_OP_C2C函数.txt

[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函数删除字段的尾部的空格。导致大量的文字变量语句以及全表 --//扫描，后面花了大量时间精力更正这个错误。 --//现在再来看那套系统，解决这些问题后，那套系统运行还是很稳定的，可惜最后淘汰了那套系统。