Oracle兼容-函数-TO_CLOB()函数

1. 语法

1. TO_CLOB( expression )

2. 定义和用法

TO_CLOB() 函数的作用是把 expression 转换成 CLOB 类型的同时，其编码格式也会转换成当前所在数据库（Schema）的字符集 character_set_database，最后输出转换结果。

在GreatSQL中 CLOB 兼容类型有：CLOB、TINYTEXT、TEXT、MEDIUMTEXT、LONGTEXT 等。

3. Oracle兼容说明

  1. TO_CLOB() 函数在转换时，若出现字符集编码冲突，会有告警提示，同时返回结果可能为NULL。

例如：当数据库编码（character_set_database）为utf8mb4时，进行如下操作：

1. greatsql> SELECT @@character_set_database;
2. +--------------------------+
3. | @@character_set_database |
4. +--------------------------+
5. | utf8mb4                  |
6. +--------------------------+

8. greatsql> SELECT TO_CLOB(0x80) FROM DUAL;
9. +---------------+
10. | TO_CLOB(0x80) |
11. +---------------+
12. | NULL          |
13. +---------------+
14. 1 row in set, 1 warning (0.00 sec)

16. greatsql> SHOW WARNINGS;
17. +---------+------+----------------------------------------+
18. | Level   | Code | Message                                |
19. +---------+------+----------------------------------------+
20. | Warning | 1300 | Invalid utf8mb4 character string: '80' |
21. +---------+------+----------------------------------------+
  1. 由于GreatSQL和Oracle对某些数据类型的处理和显式本身就存在差异，例如日期时间、LOB等类型，这可能导致 TO_CLOB() 函数在GreatSQL中得到的结果和Oracle不一致。因此，在GreatSQL中 TO_CLOB() 函数的重点是 转换成CLOB兼容类型并设置编码格式，而不保证输出的内容和Oracle严格一致。

例1：日期类型转换

  • 在Oracle中的输出为：
1. SQL> SELECT SYSDATE, TO_CLOB(SYSDATE) FROM DUAL;

3. SYSDATE   TO_CLOB(SYSDATE)
4. --------- --------------------------------------------------------------------------------
5. 06-NOV-23 06-NOV-23
  • 在GreatSQL上输出为：
1. greatsql> SET sql_mode = ORACLE;
2. greatsql> SELECT SYSDATE, TO_CLOB(SYSDATE) FROM DUAL;
3. +----------------------------+----------------------------+
4. | SYSDATE                    | TO_CLOB(SYSDATE)           |
5. +----------------------------+----------------------------+
6. | 2023-11-06 10:56:41.121699 | 2023-11-06 10:56:41.121699 |
7. +----------------------------+----------------------------+

9. greatsql> SET sql_mode = DEFAULT;
10. greatsql> SELECT SYSDATE, TO_CLOB(SYSDATE) FROM DUAL;
11. +---------------------+---------------------+
12. | SYSDATE             | TO_CLOB(SYSDATE)    |
13. +---------------------+---------------------+
14. | 2023-11-06 10:57:32 | 2023-11-06 10:57:32 |
15. +---------------------+---------------------+

例2：LOB类型转换

  • 在Oracle中的输出为：
1. -- 初始化数据
2. SQL> CREATE TABLE t_clob(id number(4), a clob, b blob);
3. SQL> INSERT INTO t_clob(id,a,b) VALUES(1, 'ABC123', 'ABC123'),
4. (2, utl_raw.CAST_FROM_BINARY_INTEGER(123), utl_raw.CAST_FROM_BINARY_INTEGER(123)),
5. (3, utl_raw.CAST_TO_RAW('ABC123'), utl_raw.CAST_TO_RAW('ABC123'));

7. -- 执行如下SQL
8. SQL> SELECT * FROM t_clob;

10. ID A                                                       B
11. ---------- ------------------------------------------------------- -------------------------
12. 1 ABC123                                                  ABC123
13. 2 0000007B                                                0000007B
14. 3 414243313233                                            414243313233

16. SQL> SELECT id,TO_CLOB(a),TO_CLOB(b) FROM t_clob;

18. ID TO_CLOB(A)                                              TO_CLOB(B)
19. ---------- ------------------------------------------------------- -------------------------
20. 1 ABC123                                                  ����#
21. 2 0000007B                                                   {
22. 3 414243313233                                            ABC123
  • 在GreatSQL上输出为：
1. -- 初始化数据
2. greatsql> SET sql_mode = ORACLE;
3. greatsql> CREATE TABLE t_clob(id INT NOT NULL PRIMARY KEY, a CLOB, b BLOB);
4. greatsql> INSERT INTO t_clob(id,a,b) VALUES(1, 'ABC123', 'ABC123'),
5. (2, utl_raw.CAST_FROM_BINARY_INTEGER(123), utl_raw.CAST_FROM_BINARY_INTEGER(123)),
6. (3, utl_raw.CAST_TO_RAW('ABC123'), utl_raw.CAST_TO_RAW('ABC123'));

8. -- 执行如下SQL
9. -- 执行下面的SQL之前，GreatSQL客户端工具需要先设置 "--binary-as-hex" 参数，例如：mysql --binary-as-hex
10. greatsql> SELECT * FROM t_clob;
11. +------+--------+----------------+
12. | id   | a      | b              |
13. +------+--------+----------------+
14. |    1 | ABC123 | 0x414243313233 |
15. |    2 |    {   | 0x0000007B     |
16. |    3 | ABC123 | 0x414243313233 |
17. +------+--------+----------------+

19. greatsql> SELECT id,TO_CLOB(a),TO_CLOB(b) FROM t_clob;
20. +------+------------+------------+
21. | id   | TO_CLOB(a) | TO_CLOB(b) |
22. +------+------------+------------+
23. |    1 | ABC123     | ABC123     |
24. |    2 |    {       |    {       |
25. |    3 | ABC123     | ABC123     |
26. +------+------------+------------+
  1. 当参数 expression中包含转义字符（例如：\0 \‘ ‘’ \“ \等），不是当做2个字符，而是当做1个字符处理。

如下例所示，执行 SELECT TO_CLOB('\\') FROM DUAL;，得到结果分别不同：

  • 在oracle上输出为：
1. SQL> SELECT TO_CLOB('\\') FROM DUAL;

3. TO_CLOB('\\')
4. --------------------------------------------------------------------------------
5. \\
  • 在GreatSQL上输出为：
1. greatsql> SET sql_mode = ORACLE;

3. greatsql> SELECT TO_CLOB('\\') FROM DUAL;
4. +---------------+
5. | TO_CLOB('\\') |
6. +---------------+
7. | \             |
8. +---------------+

4. 示例

1. greatsql> SELECT TO_CLOB(123), TO_CLOB('ABC123\\'), TO_CLOB(123.456), TO_CLOB(1+1) FROM DUAL;
2. +--------------+---------------------+------------------+--------------+
3. | TO_CLOB(123) | TO_CLOB('ABC123\\') | TO_CLOB(123.456) | TO_CLOB(1+1) |
4. +--------------+---------------------+------------------+--------------+
5. | 123          | ABC123\             | 123.456          | 2            |
6. +--------------+---------------------+------------------+--------------+

问题反馈

联系我们

扫码关注微信公众号

greatsql-wx