我们的文章会在微信公众号IT民工的龙马人生和博客网站( www.htz.pw )同步更新 ,欢迎关注收藏,也欢迎大家转载,但是请在文章开始地方标注文章出处,谢谢!
由于博客中有大量代码,通过页面浏览效果更佳。
本文为个人学习《Expert Oracle Database Architecture Techniques and Solutions for High Performance and Productivity(第四版本》一书过程中的笔记与理解分享,仅用于学习与交流,部分内容参考原书观点并结合>实际经验进行整理。若涉及版权问题,请联系删除或沟通处理。也请大家支持购买原版书籍。
关于Oracle里的“老古董”:LONG类型
简单说说LONG类型
在Oracle数据库里,LONG类型有两种形式:
- LONG文本:能存最多2GB的文本,存储时会像VARCHAR2那样进行字符集转换。
- LONG RAW:能存最多2GB的原始二进制数据(比如图片、文件),不进行字符集转换。
为什么劝你别用LONG了?
LONG类型是个“老古董”,很早就有了。虽然它后来升级也能存2GB了,但在Oracle 8之后,就有了更好的替代品——LOB类型(比如CLOB存大文本,BLOB存二进制数据)。
Oracle官方手册自己也说了:别再用LONG创建新表了!请用LOB类型! LONG只是为了兼容老版本才保留的。
LONG类型有哪些麻烦?
LONG用起来限制非常多,远不如LOB类型方便。来看几个主要的“坑”:
LONG的“坑” | LOB的优势 |
---|---|
一个表只能有一个LONG列 | 一个表最多可以有1000个LOB列 |
不能在WHERE子句里直接用LONG列进行筛选 | 可以在WHERE子句里用LOB列,还有很多专用函数(DBMS_LOB)帮你操作 |
不能用在GROUP BY, ORDER BY, DISTINCT等操作中 | 配合转换函数后,可以用 |
很多SQL函数(如SUBSTR)不能对它用 | 很多SQL函数可以对它用 |
PL/SQL过程不能直接拿LONG当参数 | PL/SQL完全支持LOB |
不支持分布式事务和高级复制 | 都支持 |
...还有很多其他限制 | ...基本上没这些烦恼 |
简单来说:只要你需要做任何复杂点的操作,LONG类型几乎都会跳出来说“不行”!而LOB类型灵活得多。
重要提示: 如果你还在用包含LONG列的老系统,强烈建议你规划把它转换成LOB类型。转换后代码通常不需要大改,但务必先在测试环境充分测试!
实战技巧:如何查询LONG列?
虽然不建议用LONG,但Oracle数据字典里确实还有很多系统视图用了LONG列(比如ALL_VIEWS
视图的TEXT
列存着视图定义)。直接想用WHERE TEXT LIKE '%HELLO%'
这种条件去查,会直接报错“ORA-00932: 数据类型不一致”。
那怎么办?我们可以写个PL/SQL工具函数来帮我们“偷看”LONG列的内容。
1. 创建一个工具包
这个包里的函数可以帮我们截取LONG列的一小段内容转换成VARCHAR2,这样我们就可以用WHERE子句了。
CREATE OR REPLACE PACKAGE long_help AUTHID CURRENT_USER ASFUNCTION substr_of(p_query IN VARCHAR2, -- 查询LONG列的SQL语句(必须只返回一行一列)p_from IN NUMBER, -- 从第几个字符开始截p_for IN NUMBER, -- 截多长(1-4000)p_name1 IN VARCHAR2 DEFAULT NULL, -- 以下是为查询语句绑定的变量名和值p_bind1 IN VARCHAR2 DEFAULT NULL,p_name2 IN VARCHAR2 DEFAULT NULL,p_bind2 IN VARCHAR2 DEFAULT NULL,p_name3 IN VARCHAR2 DEFAULT NULL,p_bind3 IN VARCHAR2 DEFAULT NULL,p_name4 IN VARCHAR2 DEFAULT NULL,p_bind4 IN VARCHAR2 DEFAULT NULL) RETURN VARCHAR2;
END long_help;
/
2. 创建包体(实现功能)
包体代码稍长,其核心思路是:动态执行你传入的SQL,然后用DBMS_SQL包里的特殊方法COLUMN_VALUE_LONG
把指定部分的LONG值切出来返回。
(包体代码略,可参考原文。关键是它只解析一次SQL,效率较高,且以调用者权限运行,安全。)
3. 怎么用这个工具?
比如,你想在用户自己的视图中查找定义里包含“INNER”关键字的视图:
SELECT *
FROM (SELECT owner, view_name,long_help.substr_of('SELECT text FROM dba_views WHERE owner = :owner AND view_name = :view_name',1, 4000, -- 从第1个字符开始,截取4000字节'owner', owner, -- 为:owner绑定变量赋值为当前行的owner'view_name', view_name -- 为:view_name绑定变量赋值为当前行的view_name) AS substr_of_view_textFROM dba_viewsWHERE owner = USER -- 当前用户
)
WHERE UPPER(substr_of_view_text) LIKE '%INNER%';
再比如,查找分区边界值中包含'2014'的分区(常用于管理分区表):
SELECT *
FROM (SELECT table_owner, table_name, partition_name,long_help.substr_of('SELECT high_value FROM all_tab_partitions WHERE table_owner = :o AND table_name = :n AND partition_name = :p',1, 4000,'o', table_owner,'n', table_name,'p', partition_name) AS high_valueFROM all_tab_partitionsWHERE table_owner = USER
)
WHERE high_value LIKE '%2014%';
注意事项
- 这个工具主要针对LONG文本类型。LONG RAW类型处理起来更麻烦,因为PL/SQL里没有直接分段访问它的好方法,通常建议用其他编程语言处理,或者干脆转成BLOB。
- 对于LONG RAW,还有一种临时办法:用
TO_LOB()
函数把它插入到一个临时表的BLOB列里再处理。但如果经常要这么干,还是彻底转成BLOB一劳永逸。
总结
核心就三句话:
- 新项目绝对不要用LONG类型。
- 老项目碰到LONG的限制,尽量找机会转换成LOB类型。
- 如果暂时不能转换,需要查询LONG列,可以参考上面的方法自己写工具函数绕过限制。
------------------作者介绍-----------------------
姓名:黄廷忠
个人博客: (http://www.htz.pw)
CSDN地址: (https://blog.csdn.net/wwwhtzpw)
博客园地址: (https://www.cnblogs.com/www-htz-pw)