SQL_Oracle操作

ゞ浴缸里的玫瑰 2022-01-22 13:27 356阅读 0赞

#### decode 关键字 oracle独有 ####

需求  
假设我们想给智星职员加工资，其标准是：工资在8000元以下的将加20％；工资在8000元以上的加15％

sql中sign（x）什么意思
    符号函数。若x>0，则返回1；若x=0，则返回0；若x<0，则返回-1。
    SQL:
    
    select decode(sign(salary - 8000),1,salary*1.15,-1,salary*1.2,salary) from employee

#### nvl 函数 ####

red\_flag为空或者其他，只要不是1 。就把 red\_flag 的值查出来

nvl(spo_out_bill_head.red_flag, '0') <> '1'

#### round 函数 ####

这个 2 是 保留多少位

round(t3.sumFqty / t3.sumQty,2) * 100 || '%' 命中率

#### 关于时间的比较 ####

and exists (
                  select 1 from spo_order 
    			    where spo_out_bill_head.refer_code = spo_order.order_no 
    				  and (
    				       spo_order.PUB_COMMIT_PERSON_DATE between to_date('${refer_pub_commit_date}','yyyy-mm-dd ') 
    					   and nvl(to_date('${refer_pub_commit_date_end}','yyyy-mm-dd '),sysdate+1)
    					   )
    			)

#### decode 函数 ####

主要作用：  
将查询结果翻译成其他值（即以其他形式表现出来，以下举例说明）；

使用方法：

Select decode（columnname，值1,翻译值1,值2,翻译值2,…值n,翻译值n,缺省值）
    From talbename
    Where …

其中columnname为要选择的table中所定义的column，

现定义一table名为output，其中定义两个column分别为monthid（var型）和sale（number型），  
若sale值=1000时翻译为D，=2000时翻译为C，=3000时翻译为B，=4000时翻译为A，如是其他值则翻译为Other；  
SQL如下：

Select monthid,decode(sale,1000,'D',2000,'C',3000,'B',4000,'A',’Other’) sale from output

另：decode中可使用其他函数，如nvl函数或sign（）函数等；  
NVL(EXPR1,EXPR2)  
若EXPR1是NULL,则返回EXPR2,否则返回EXPR1.

select monthid,decode(nvl(sale,6000),6000,'NG','OK') from output

sign()函数根据某个值是0、正数还是负数，分别返回0、1、-1，  
如果取较小值就是

select monthid,decode(sign(sale-6000),-1,sale,6000) from output

即达到取较小值的目的。

使用decode比较大小

select decode(sign(var1-var2),-1,var 1,var2) from dual
    sign()函数根据某个值是0、正数还是负数，分别返回0、1、-1
    sql测试
    select decode(sign(100-90),-1,100,90) from dual
    输出结果
    90
    100-90=10>0 则会返回1，所以decode函数最终取值为90
    反正
    select decode(sign(100-90),1,100,90) from dual
    输出结果
    100
    100-90=10>0返回1,判断结果为1，返回第一个变量100，最终输出结果为100

在 Oracle 中查时间

WHERE 1 = 1
      AND H.CREATION_DATE >= to_date('2019-6-12 00:00:00','yyyy-mm-dd hh24:mi:ss')
      AND H.CREATION_DATE < to_date('2019-6-13 00:00:00','yyyy-mm-dd hh24:mi:ss') + 1;

##### 关于 NVL 函数 #####

因为使用NVL导致索引失效，需要去NVL：
    
    改造范围范围：java，xml
    where条件，nvl(字段)
    
    任何与null比较的结果都是false
    stat != null ==> 恒等于false
    stat = null  ==> 恒等于false
    stat IN (NULL, 1) ==> 只能查出1的数据
    stat NOT IN (NULL, 1) ==> 没有数据
    
    所以跟null比较只能使用：
    stat is not null
    stat is null
    
    场景：
    nvl(stat,0) = 0  ==> stat is null or stat = 0
    nvl(stat,0) = 1  ==> stat = 1
    
    nvl(stat,0) != 0 ==> stat is not null and stat != 0
    nvl(stat,0) != 1 ==> stat is null or stat != 1
    
    nvl(stat,0) in (1, 2)  ==> stat in (1,2)
    nvl(stat,0) in (0, 1, 2) ==> stat is null or stat in (0, 1, 2) 
    
    nvl(stat,0) not in (1, 2) ==> stat is null or stat not in (1, 2) 
    nvl(stat,0) not in (0, 1, 2) ==> stat is not null and stat not in (0, 1, 2)
    
    nvl(stat,0) <= 5 ==> stat is null or stat <= 5
    nvl(stat,5) < 5 ==> stat < 5
    
    nvl(stat,0) > 0  ==> stat > 0
    nvl(stat,5) >= 5  ==> stat is null or stat >= 5
    
    结论：
    is null 必然是 or
    is not null 必然是 and

##### 关于 sql的 between … and 的边界问题 #####

mysql：  
select \* from user where userId between 5 and 7;  
查询userId为5、6，7的user，userId范围是包含边界值的。  
另外 not between的范围是不包含边界值。

Oracle ：  
Oracle的BETWEEN…AND…前后都是闭区间，也就是说包含两个端的数。  
另外 not between的范围是不包含边界值。

### Oracle数据库-trunc函数的用法 ###

trunc 函数可用于截取日期时间
    用法：trunc（字段名，精度）
    具体实例：
    在表table1中，有一个字段名为sysdate，该行id=123，日期显示：2016/10/28 15:11:58
    1、截取时间到年时，sql语句如下：
    select trunc(sysdate,'yyyy') from table1 where id=123;  --yyyy也可用year替换
    显示：2016/1/1
    2、截取时间到月时，sql语句：
    select trunc(sysdate,'mm') from table1 where id=123;
    显示：2016/10/1
    3、截取时间到日时，sql语句：
    select trunc(sysdate,'dd') from table1 where id=123;
    显示：2016/10/28
    4、截取时间到小时时，sql语句：
    select trunc(sysdate,'hh') from table1 where id=123;
    显示：2016/10/28 15:00:00
    5、截取时间到分钟时，sql语句：
    select trunc(sysdate,'mi') from table1 where id=123;
    显示：2016/10/28 15:11:00
    6、截取时间到秒暂时不知道怎么操作
    7、不可直接用trunc(sysdate,'yyyy-mm-dd')，会提示“精度说明符过多”
    
    
    
    
    
    --Oracle trunc()函数的用法
    /**************日期********************/
    1.select trunc(sysdate) from dual --2013-01-06 今天的日期为2013-01-06
    2.select trunc(sysdate, 'mm') from dual --2013-01-01 返回当月第一天.
    3.select trunc(sysdate,'yy') from dual --2013-01-01 返回当年第一天
    4.select trunc(sysdate,'dd') from dual --2013-01-06 返回当前年月日
    5.select trunc(sysdate,'yyyy') from dual --2013-01-01 返回当年第一天
    6.select trunc(sysdate,'d') from dual --2013-01-06 (星期天)返回当前星期的第一天
    7.select trunc(sysdate, 'hh') from dual --2013-01-06 17:00:00 当前时间为17:35 
    8.select trunc(sysdate, 'mi') from dual --2013-01-06 17:35:00 TRUNC()函数没有秒的精确
    /***************数字********************/
    /*
    TRUNC（number,num_digits） 
    Number 需要截尾取整的数字。 
    Num_digits 用于指定取整精度的数字。Num_digits 的默认值为 0。
    TRUNC()函数截取时不进行四舍五入
    */
    9.select trunc(123.458) from dual --123
    10.select trunc(123.458,0) from dual --123
    11.select trunc(123.458,1) from dual --123.4
    12.select trunc(123.458,-1) from dual --120
    13.select trunc(123.458,-4) from dual --0
    14.select trunc(123.458,4) from dual --123.458
    15.select trunc(123) from dual --123
    16.select trunc(123,1) from dual --123
    17.select trunc(123,-1) from dual --120