Oracle——各种问题的“踩坑”、“填坑”与“面壁思过”
序言
写在最前,此博客会不断更新,整理总结开发工作(实战过程)中的sql问题。欢迎大家指出问题。
1.order by
关于多次order by,结果集的顺序问题:链接: Oracle——分页查询出现重复数据问题的分析与解决.
2.union all
用法说明
UNION 操作符用于合并两个或多个 SELECT 语句的结果集,这里需要注意的是:
- UNION 内部的 SELECT 语句必须拥有相同数量的列;
- 同时,每条 SELECT 语句中列的顺序必须相同。
补充:union与union all的区别
union:对两个结果集进行并集操作,不包括重复行,相当于distinct,同时会按照默认规则排序;union all:对两个结果集进行并集操作, 包括重复行, 即所有的结果全部显示, 不管是不是重复,不会对获取的结果进行排序操作。
union all只是合并查询结果,并不会进行去重和排序操作,在没有去重的前提下,使用union all的执行效率要比union高。(友情提示,能用sql处理数据,就用sql处理,要不然后台压力会很大)
union all 字段具有不同数量的列
如果union连接的结果集,结果列数量不一致,数据库会直接报错。
原理:union的时候,数据库会先判断前后两个结果集的colSize(),size不一致会直接返回error。
union all 字段顺序不一致
union的多个结果集,如果顺序不一致,并不会报错,同时,数据库是按照字段顺序匹配,而不是按照字段名称匹配。
原理:数据库union的时候,如果按照字段名称进行匹配,一般会用嵌套循环——原理如下(如果多个结果集,嵌套层数更多,数据库很容易溢出)。如果按照顺序匹配,直接顺序输出即可。
//按照字段名称匹配——嵌套循环
for(int i=0;i<result1;i++)
{
for(int j=0;j<result2;j++)
{
///
}
}
3.PLSQL本地插入数据乱码
问题描述
在使用PLSQL工具,批量向数据库中插入数据,查询所有插入的数据,发现出现中文乱码的情况,所有的中文变成???。
原因
这个问题是由于oracle服务器端字符编码 和 Oracle 客户端 字符编码不一致引起的。分析到本机是因为plsql从 utf-8 字符集的数据库导出来的sql, 我本机oracle是默认zh-gbk的字符集。所以, 用sqlplus导入一直是乱码。
解决方法
- 查询服务器的字符编码:
select userenv('LANGUAGE') from dual;
--或者
select * from v$nls_parameters a where a.PARAMETER = 'NLS_CHARACTERSET';
USERENV(‘LANGUAGE’):SIMPLIFIED CHINESE_CHINA.AL32UTF8
- 修改本地环境变量——用户变量
新增用户变量NLS_LANG,将查询的字符编码设为值,如下:
- 保存修改,重新启动PLSQL,并重新插入数据。
plsql导出数值前面的0被过滤
问题描述
从plsql将结果集导成csv格式,但是数值型的数据(数据库存储为varchar类型),前面的0会被过滤掉。
解决方法
实际上,很多情况下,这些被过滤的零是有意义的,所以需要在SQL的时候处理一下,将它转为Excel能够识别的文本类型,那么导出来的数据才不会把这些零过滤掉:
--length:字符串长度,一般以字段里的最大长度
--col:字段名
SQL:select to_char(chr(length)||t.col) col1 from table t;