概述

在信创移植的SQL语句中，有来源于Oracle数据库的SQL语句。

在ORACLE PL/SQL包中，你可以使用TO_CLOB(character)函数将RAW、CHAR、VARCHAR、VARCHAR2、NCHAR、NVARCHAR2、CLOB值转换为CLOB。

因此在LightDB 23.3版本中实现了对TO_CLOB函数的支持。

案例演示

环境准备

create database test_oracle with lightdb_syntax_compatible_type  oracle;
\c test_oracle

使用TO_CLOB函数

lightdb@test_oracle=# CREATE TABLE testorafce_to_clob (
lightdb@test_oracle(# col_char CHAR(10),
lightdb@test_oracle(# col_varchar2 VARCHAR2(20),
lightdb@test_oracle(# col_varchar VARCHAR(20),
lightdb@test_oracle(# col_nchar NCHAR(10),
lightdb@test_oracle(# col_nvarchar2 NVARCHAR2(20),
lightdb@test_oracle(# col_smallint smallint,
lightdb@test_oracle(# col_integer integer,
lightdb@test_oracle(# col_bigint bigint,
lightdb@test_oracle(# col_decimal decimal,
lightdb@test_oracle(# col_numeric numeric,
lightdb@test_oracle(# col_real real,
lightdb@test_oracle(# col_double double precision,
lightdb@test_oracle(# col_clob CLOB,
lightdb@test_oracle(# col_raw raw(10)
lightdb@test_oracle(# );
CREATE TABLElightdb@test_oracle=# INSERT INTO testorafce_to_clob (col_char, col_varchar2, col_varchar, col_nchar, col_nvarchar2, col_smallint, col_integer, col_bigint, col_decimal, col_numeric, col_real, col_double, col_clob, col_raw)
lightdb@test_oracle-# VALUES ('ABC1', 'Hello', 'world', N'中文', N'こんにちは', 1, 2, 3, 4, 5, 6, 7, 'This is a CLOB', 'AB');
INSERT 0 1
lightdb@test_oracle=# INSERT INTO testorafce_to_clob (col_char, col_varchar2, col_varchar, col_nchar, col_nvarchar2, col_smallint, col_integer, col_bigint, col_decimal, col_numeric, col_real, col_double, col_clob, col_raw)
lightdb@test_oracle-# VALUES ('ABC2', 'Hello', 'world', N'中文', N'こんにちは', 1, 2, 3, 4, 5, 6, 7, 'This is a CLOB', '1');
INSERT 0 1
lightdb@test_oracle=# INSERT INTO testorafce_to_clob (col_char, col_varchar2, col_varchar, col_nchar, col_nvarchar2, col_smallint, col_integer, col_bigint, col_decimal, col_numeric, col_real, col_double, col_clob, col_raw)
lightdb@test_oracle-# VALUES ('ABC3', 'Hello', 'world', N'中文', N'こんにちは', 1, 2, 3, 4, 5, 6, 7, oracle.to_clob('This is a CLOB'), '1AB456789');
INSERT 0 1lightdb@test_oracle=# SELECT  oracle.to_clob(col_char) AS clob_char,
lightdb@test_oracle-# oracle.to_clob(col_varchar2) AS clob_varchar2,
lightdb@test_oracle-# oracle.to_clob(col_varchar) AS col_varchar,
lightdb@test_oracle-# oracle.to_clob(col_nchar) AS clob_nchar,
lightdb@test_oracle-# oracle.to_clob(col_nvarchar2) AS clob_nvarchar2,
lightdb@test_oracle-# oracle.to_clob(col_clob) AS clob_clob,
lightdb@test_oracle-# oracle.to_clob(col_smallint) AS col_smallint,
lightdb@test_oracle-# oracle.to_clob(col_integer) AS col_integer,
lightdb@test_oracle-# oracle.to_clob(col_bigint) AS col_bigint,
lightdb@test_oracle-# oracle.to_clob(col_decimal) AS col_decimal,
lightdb@test_oracle-# oracle.to_clob(col_numeric) AS col_numeric,
lightdb@test_oracle-# oracle.to_clob(col_real) AS col_real,
lightdb@test_oracle-# oracle.to_clob(col_double) AS col_double,
lightdb@test_oracle-# oracle.to_clob(col_raw) AS clob_nclob
lightdb@test_oracle-# FROM testorafce_to_clob order by col_char asc;clob_char  | clob_varchar2 | col_varchar |  clob_nchar  | clob_nvarchar2 |   clob_clob    | col_smallint | col_integer | col_bigint | col_decimal | col_numeric | col_real | col_double | clob_nclob 
------------+---------------+-------------+--------------+----------------+----------------+--------------+-------------+------------+-------------+-------------+----------+------------+------------ABC1       | Hello         | world       | 中文         | こんにちは     | This is a CLOB | 1            | 2           | 3          | 4           | 5           | 6        | 7          | ABABC2       | Hello         | world       | 中文         | こんにちは     | This is a CLOB | 1            | 2           | 3          | 4           | 5           | 6        | 7          | 01ABC3       | Hello         | world       | 中文         | こんにちは     | This is a CLOB | 1            | 2           | 3          | 4           | 5           | 6        | 7          | 01AB456789
(3 rows)

可以看出，在Oracle兼容模式下LightDB 23.3版本TO_CLOB函数支持了CLOB、字符型以及数值型到CLOB类型的转换。