Oracle遇到问题总结

userzyc

于 2021-07-06 09:40:44 发布

阅读量196

点赞数

分类专栏：技术总结文章标签：数据库 sql

本文链接：https://blog.csdn.net/userzyc/article/details/118513115

版权

技术总结专栏收录该内容

1 篇文章 0 订阅

订阅专栏

文章为原创，有很多是从网上看来的，但是时间过长已经忘记从哪里找的了

文章目录

sql加上分页突然效率下降很多

原因是因为没有加上排序，分页的时候会默认排序,加上排序就不会出现这样的问题了。

select *
  FROM (select a.*, ROWNUM rn
          FROM (
          		--查询sql
                 ) a
         WHERE ROWNUM <= 10)
 WHERE rn > 0;

Orcal数据库中的函数

case when then else end

–case语句的种类:
1.简单case语句
语法:
case exp when comexp then returnvalue
…
when comexp then returnvalue
else returnvalue
end

case到end之间相当于一个具体的值，可以做运算，取别名，嵌套case 等等。
只要把case到end当作一个运算结果的表达式就可以了。

举例:
select cust_last_name,
case credit_limit when 100 then ‘low’
when 5000 then ‘high’
else ‘medium’
end
from customers;

2.搜索case语句
语法:
case when boolean then return value
…
when boolean then return value
else retur nvalue
end

举例:select case when id between 1 and 10 then ‘low’
when id between 20 and 30 then ‘mid’
when id between 40 and 50 then ‘high’
else ‘unknow’
endfrom product;
–简单case和搜索case之间的区别：

简单case只能是when后面的表达式完全匹配case后的表达式，相当于 =，所以也不能匹配null。2. searched case可以作为比较条件，那么可以使用like、!=、between …and、<、=、is null、is not null等，比简单case的使用更加广泛，完全可以替代简单case。
–注意事项:
1.case 表达式返回的是一个确定的value，若前面的都不匹配，则返回else中的项.
2.简单case 中的表达式，when 后面的表达式类型应该全部保持一致.
3.所有的then 后面的return_value类型要保持一致.
4.对于简单case 表达式，也就是case 表达式 when…那么when null 总是取不到。也就是case 后面的表达式如果值为null，不会与when null 匹配，只会与else匹配.
5.对于searched case来说，有自动类型转换，只要条件成立就可以。
如:select case when 1=‘1’ then 1 end from dual; 其中1='1’条件成立

值得一提的是: sql中的case语句与pl/sql中的case语句的不同之处:
前者的else不是必须的，如果没有匹配就返回null;后者的else不写，则报case_not_found异常.
–case中嵌套子查询Case语句中可以使用子查询，但是必须返回一行，不可以是多行.
如：select case (select count() as s1 from t1 where a = 1)
when (select count() as s2 from t1, t2 where t1.a = t2.a and t2.a = 1) then ‘相等’
else ‘不相等’
end
from dual;

此段为引用，时间过长忘记引用谁的了

union 和union all区别

Union：对两个结果集进行并集操作，不包括重复行，同时进行默认规则的排序；
Union All：对两个结果集进行并集操作，包括重复行，不进行排序；

grouping 函数

GROUPING函数可以接受一列，返回0或者1。如果列值为空，那么GROUPING()返回1；如果列值非空，那么返回0。GROUPING只能在使用ROLLUP或CUBE的查询中使用。当需要在返回空值的地方显示某个值时，GROUPING()就非常有用。
1、在ROLLUP中对单列使用GROUPING()

SQL> select division_id,sum(salary)
  2  from employees2
  3  group by rollup(division_id)
  4  order by division_id;

DIV SUM(SALARY)

BUS 1610000
OPE 1320000
SAL 4936000
SUP 1015000
8881000
加上GROUPING来看看

SQL> select grouping(division_id),division_id,sum(salary)
  2  from employees2
  3  group by rollup(division_id)
  4  order by division_id;

GROUPING(DIVISION_ID) DIV SUM(SALARY)

                0 BUS     1610000
                0 OPE     1320000
                0 SAL     4936000
                0 SUP     1015000
                1         8881000

可以看到，为空的地方返回1，非空的地方返回0。

decode函数的用发

1.decode(条件,值1,返回值1,值2,返回值2,…值n,返回值n,缺省值)
该函数的含义如下：
IF 条件=值1 THEN
　　　　RETURN(翻译值1)
ELSIF 条件=值2 THEN
　　　　RETURN(翻译值2)
　　　　…
ELSIF 条件=值n THEN
　　　　RETURN(翻译值n)
ELSE
　　　　RETURN(缺省值)
END IF

示例：
SELECT ID,DECODE(inParam,‘para1’,‘值1’ ,‘para2’,‘值2’,‘para3’,‘值3’,‘para4’,‘值4’,‘para5’,‘值5’) name FROM bank
#如果第一个参数inParam=='para1’那么那么select得到的那么显示为值1；
#如果第一个参数inParam=='para2’那么那么select得到的那么显示为值2；
#如果第一个参数inParam=='para3’那么那么select得到的那么显示为值3；
#如果第一个参数inParam==‘para4’那么那么select得到的那么显示为值4；
#如果第一个参数inParam==‘para5’那么那么select得到的那么显示为值5；
#都不相等就为’’

2.decode(字段或字段的运算，值1，值2，值3）
这个函数运行的结果是，当字段或字段的运算的值等于值1时，该函数返回值2，否则返回值3，当然值1，值2，值3也可以是表达式，这个函数使得某些sql语句简单了许多

示例：
SELECT ID,DECODE(inParam,‘beComparedParam’,‘值1’ ,‘值2’) name FROM bank
#如果第一个参数inParam==‘beComparedParam’，则select得到的name显示为值1，
#如果第一个参数inParam!=‘beComparedParam’，则select得到的name显示为值2

select decode(sign(变量1-变量2),-1,变量1,变量2) from dual; --取较小值
sign()函数根据某个值是0、正数还是负数，分别返回0、1、-1
例如：
变量1=10，变量2=20
则sign(变量1-变量2)返回-1，decode解码结果为“变量1”，达到了取较小值的目的。

Substr字符截取

1、substr函数格式 (俗称：字符截取函数)
　　格式1： substr(string string, int a, int b);

格式2：substr(string string, int a) ;

解析：

格式1：
    1、string 需要截取的字符串
    2、a 截取字符串的开始位置（注：当a等于0或1时，都是从第一位开始截取）
    3、b 要截取的字符串的长度

格式2：
    1、string 需要截取的字符串
    2、a 可以理解为从第a个字符开始截取后面所有的字符串。包括a

2、实例解析
格式1：

复制代码
1、select substr(‘HelloWorld’,0,3) value from dual; //返回结果：Hel，截取从“H”开始3个字符
2、select substr(‘HelloWorld’,1,3) value from dual; //返回结果：Hel，截取从“H”开始3个字符
3、select substr(‘HelloWorld’,2,3) value from dual; //返回结果：ell，截取从“e”开始3个字符
4、select substr(‘HelloWorld’,0,100) value from dual; //返回结果：HelloWorld，100虽然超出预处理的字符串最长度，但不会影响返回结果，系统按预处理字符串最大数量返回。
5、select substr(‘HelloWorld’,5,3) value from dual; //返回结果：oWo
6、select substr(‘Hello World’,5,3) value from dual; //返回结果：o W (中间的空格也算一个字符串，结果是：o空格W)
7、select substr(‘HelloWorld’,-1,3) value from dual; //返回结果：d （从后面倒数第一位开始往后取1个字符，而不是3个。原因：下面红色第三个注解）
8、select substr(‘HelloWorld’,-2,3) value from dual; //返回结果：ld （从后面倒数第二位开始往后取2个字符，而不是3个。原因：下面红色第三个注解）
9、select substr(‘HelloWorld’,-3,3) value from dual; //返回结果：rld （从后面倒数第三位开始往后取3个字符）
10、select substr(‘HelloWorld’,-4,3) value from dual; //返回结果：orl （从后面倒数第四位开始往后取3个字符）
复制代码
（注：当a等于0或1时，都是从第一位开始截取(如：1和2)）
（注：假如HelloWorld之间有空格，那么空格也将算在里面(如：5和6)）
（注：虽然7、8、9、10截取的都是3个字符，结果却不是3 个字符；只要 |a| ≤ b,取a的个数(如：7、8、9)；当 |a| ≥ b时，才取b的个数，由a决定截取位置(如：9和10)）

格式2：

复制代码
11、select substr(‘HelloWorld’,0) value from dual; //返回结果：HelloWorld，截取所有字符
12、select substr(‘HelloWorld’,1) value from dual; //返回结果：HelloWorld，截取所有字符
13、select substr(‘HelloWorld’,2) value from dual; //返回结果：elloWorld，截取从“e”开始之后所有字符
14、select substr(‘HelloWorld’,3) value from dual; //返回结果：lloWorld，截取从“l”开始之后所有字符
15、select substr(‘HelloWorld’,-1) value from dual; //返回结果：d，从最后一个“d”开始往回截取1个字符
16、select substr(‘HelloWorld’,-2) value from dual; //返回结果：ld，从最后一个“d”开始往回截取2个字符
17、select substr(‘HelloWorld’,-3) value from dual; //返回结果：rld，从最后一个“d”开始往回截取3个字符
复制代码
（注：当只有两个参数时；不管是负几，都是从最后一个开始往回截取(如：15、16、17)）

Date数据类型比较大小

to_char(account_date,‘yyyy-MM-dd hh24:mi:ss’) > ‘2015-06-06’

查询100-150记录

比如想取出100-150条记录,按照tname排序
select tname,tabtype from (
select tname,tabtype,row_number() over ( order by tname ) rn from tab
) where rn between 100 and 150;

将一个表数据恢复到某个时间段

delete from user;
commit;

insert into userselect * from user as of timestamp to_timestamp(‘2021-05-10 09:30:00’, ‘yyyy-MM-dd HH:mi:ss’);

两个日期中有那些月

主要应用于查询连个月的数据，然后根据月分组

SELECT TO_CHAR(ADD_MONTHS(TO_DATE(#{model.起始日期}, 'yyyy-MM'), ROWNUM - 1),'yyyy-MM') TIAN_BAO_RI_QI
    FROM DUAL
    CONNECT BY ROWNUM <=
    MONTHS_BETWEEN(TO_DATE(#{model.结束日期}, 'yyyy-MM'),
TO_DATE(#{model.起始日期}, 'yyyy-MM')) + 1

查询表结构

select t.COLUMN_ID 序号,
t.COLUMN_NAME 代码,
t.DATA_TYPE||’(’||t.DATA_LENGTH||’)’ 数据类型 ,
c.COMMENTS 注释,
case when t.column_name=t2.column_name then ‘TRUE’ else ‘FALSE’ end 主键,
case when t.NULLABLE = ‘N’ then ‘TRUE’
WHEN t.NULLABLE = ‘Y’ THEN ‘FALSE’ END AS 非空
from user_tab_columns t
LEFT JOIN user_col_comments c ON t.table_name = c.table_name and t.column_name = c.column_name
left join (select b.table_name,b.column_name from user_cons_columns b
join user_constraints c on c.CONSTRAINT_NAME=b.CONSTRAINT_NAME
where c.constraint_type =‘P’) t2
on t.table_name=t2.table_name
where
t.table_name = ‘表名’
order by t.COLUMN_ID;

在这里插入图片描述