Oracle函数

悠悠 2021-11-04 15:38 512阅读 0赞

# 1，case when用法 #

从表中根据时间点不同查询出活动的状态。

select t.*,(case when (t.start_date <= sysdate and t.end_date >= sysdate)then '报名中'
              when  t.fw_date_z < sysdate then '已结束'
              when (t.fw_date <= sysdate and t.fw_date_z >= sysdate)then '进行中'
              when (t.fw_date_z is null and t.fw_date < sysdate) then '已结束'
              else '进行中' end)as hdzt,
            (select b.fczs_id from zyfw_fczs b where b.zyfw_id = t.zyfw_id and rownum=1) as fczs_id
            from zyfw_main t,zyfw_zyz a
              where t.zyfw_id=a.zyfw_id  and t.status='2';

# 2，trunc函数的用法 #

select trunc(sysdate-1) from dual t;--昨天
    select trunc(sysdate+1) from dual t;--明天
    select trunc(sysdate, 'yy') from dual;--当年第一天
    select trunc(sysdate, 'year') from dual;--当年第一天
    select trunc(sysdate, 'yyyy') from dual;--当年第一天
    select trunc(sysdate, 'q') from dual;--当前时间所在的季度的第一天
    select trunc(sysdate, 'mm') from dual;--当月第一天
    select trunc(sysdate, 'month') from dual;--当月第一天
    select trunc(sysdate, 'd') from dual;--返回本周的第一天（周日为第一天）
    select trunc(sysdate,'day') from dual;--返回本周的第一天（周日为第一天）
    select trunc(sysdate, 'iw') from dual;--本周第二天（周日为第一天）
    select trunc(sysdate, 'hh') from dual; --当前时间，精确到小时
    select trunc(sysdate, 'hh24') from dual;--当前时间。精确到小时
    select trunc(sysdate, 'mi') from dual;--当前时间。精确到分钟 没有精确到秒的精度

# 3，substr函数 #

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个字符开始截取后面所有的字符串。

select substr(sysdate,0,10) from dual;	//截取当前日期（格式为2019-08-06）

# 4，extract函数 #

要从表格myTable中选取time字段中年份为2018年的所有数据

select title,play,time from myTable where extract(year from time) = 2018;
    select title,play,to_char(time, 'YYYY-MM-DD') as time from myTable where extract(year from time) = 2018;

要从表格myTable中选取time中月份为5的所有数据

select title,play,time from myTable extract(month from time) = 5;
    select title,play,to_char(time, 'YYYY-MM-DD') as time from myTable where extract(month from time) = 5;

从表格myTable中选取time中日期为6的所有数据

select title,play,time from myTable extract(day from time) = 6;
    slect title,play,to_char(time, 'YYYY-MM-DD') as time from myTable where extract(day from time) = 6;

## 5，NVL()函数 ##

### （1）NVL(X,VALUE) ###

如果X为空，返回value，否则返回X

该函数的目的是把一个空值（null）转换成一个实际的值。其表达式的值可以是数字型、字符型和日期型。但是表达式1和表达式2的数据类型必须为同一个类型。

例如：对工资是2000元以下的员工，如果没发奖金，每人奖金100元

SELECT ENAME,JOB,SAL,NVL(COMM,100) FROM EMP WHERE SAL<2000;

### （2）NVL2(x,value1,value2) ###

如果x非空，返回value1，否则返回value2。

例如：对EMP表中工资为2000元以下的员工，如果没有奖金，则奖金为200元，如果有奖金，则在原来的奖金基础上加100元

SELECT ENAME,JOB,SAL,NVL2(COMM,comm+100,200) "comm" FROM EMP WHERE SAL<2000;

## 6，with as 用法 ##

相当于建了个e临时表。

with e as (select * from scott.emp e where e.empno=7499)
    select * from e;

## 7，oracle批量插入 ##

Mapper接口

int insterZqyjList(List<YzsylgkZqyj> zqyjList);

Mapper.xml文件

<insert id="insterZqyjList" parameterType="List">
      insert into YZSYLGK_ZQYJ (YJ_ID, ID, YJ_NR,
      TCR_NAME, TCRDW, CREATEDATE,
      MODIFYDATE, USER_ID, ORG_ID
      ) select a.* from(
      <foreach collection="list" item="item" index="index" separator="union" open="(" close=")">
        select #{item.yjId,jdbcType=VARCHAR}, #{item.id,jdbcType=VARCHAR}, #{item.yjNr,jdbcType=VARCHAR},
        #{item.tcrName,jdbcType=VARCHAR}, #{item.tcrdw,jdbcType=VARCHAR}, #{item.createdate,jdbcType=TIMESTAMP},
        #{item.modifydate,jdbcType=TIMESTAMP}, #{item.userId,jdbcType=VARCHAR}, #{item.orgId,jdbcType=VARCHAR} from dual
      </foreach>
      )a
    </insert>

这里打断一下，增加一个`mysql`的批量插入操作。

<insert id="insertAll" parameterType="List">
        insert into kp_file_services_contend (contend_id, services_id, contend_type,
          model, specification, duration,
          price, num, total_price
          ) values
        <foreach collection="list" item="item" index="index" separator=",">
          (
          #{item.contendId,jdbcType=VARCHAR},#{item.servicesId,jdbcType=VARCHAR},#{item.contendType,jdbcType=VARCHAR},
          #{item.model,jdbcType=VARCHAR},#{item.specification,jdbcType=VARCHAR},#{item.duration,jdbcType=VARCHAR},
          #{item.price,jdbcType=DECIMAL},#{item.num,jdbcType=VARCHAR},#{item.totalPrice,jdbcType=DECIMAL}
          )
        </foreach>
      </insert>

### （1） oracle批量更新 ###

<update id="updateBatch" parameterType="java.util.List">
        <foreach collection="list" item="item" index="index" open="begin" close=";end;" separator=";">
            update T_CITY_INDEX t
            set
            t.city_name= #{item.cityName,jdbcType=VARCHAR} ,
            t.district_name= #{item.districtName,jdbcType=VARCHAR} ,
            where t.id = #{item.id,jdbcType=NUMERIC}
        </foreach>
    </update>

## 8，字符函数 ##

字符函数接受字符参数，这些参数可以是表中的列，也可以是一个字符串表达式。

常用的字符函数有：

<table> 
 <thead> 
  <tr> 
   <th>函数</th> 
   <th>说明</th> 
  </tr> 
 </thead> 
 <tbody> 
  <tr> 
   <td>concat(x,y)</td> 
   <td>连接字符串x和y</td> 
  </tr> 
  <tr> 
   <td>length(x)</td> 
   <td>返回x的长度</td> 
  </tr> 
  <tr> 
   <td>lower(X)</td> 
   <td>把X变成小写</td> 
  </tr> 
  <tr> 
   <td>upper(x)</td> 
   <td>把x变成大写</td> 
  </tr> 
  <tr> 
   <td>REPLACE(X,old,new)</td> 
   <td>在X中查找old，并替换成new</td> 
  </tr> 
  <tr> 
   <td>concat(x,y)</td> 
   <td>连接字符串x和y</td> 
  </tr> 
 </tbody> 
</table>

### （1） trim（）、ltrim（）、rtrim（）的用法 ###

**trim（string）**：去除指定字符串string的左右空格，当然，string中间有空格的时候是不会被去除。

SELECT trim(' aaa bbb ccc ')  trim FROM dual;
    aaa  bbb  ccc

**ltrim（string）、rtrim（string）**：分别去除指定字符串string左侧和右侧的空格。

SELECT ltrim(' aaa bbb ccc ') ltrim FROM dual;
    aaa  bbb  ccc     --（注意，此时返回的字符串右侧是有空格的）

SELECT rtrim(' aaa bbb ccc ') rtrim FROM dual;
       aaa  bbb  ccc  --（显然此时返回的字符串左侧是有空格的）

**trim( leading | trailing | both string1 FROM string2)**：从string2中去除左侧 | 右侧 | 左侧两侧（默认是both，即左右侧都去掉）的string1字符，**注意，string1只能是单个字符。**

SELECT trim(leading  ' '  from  ' aaa bbb ccc ') leadingtrim FROM dual;
    aaa  bbb  ccc   --（注意，此时返回的字符串右侧是有空格的）
    SELECT trim(trailing  ' '  from  ' aaa bbb ccc ') trailingtrim FROM dual;
        aaa  bbb  ccc  --（显然，此时返回的字符串左侧是有空格的）
    SELECT trim(both ' '  from  ' aaa bbb ccc ') bothtrim FROM dual; 
    aaa  bbb  ccc  --（返回的字符串两侧的空格都被去掉）

ltrim(string1,string2)，rtrim(string1,string2)：从字符串string1左侧（右侧）开始去除与string2字符集合中单个字符匹配的字符，直到在string1中遇上某个字符，该字符不在string2字符集合中。

SELECT ltrim('abcxcba','abc') ltrim FROM dual;
    xcba  --（结果并不是只剩一个“x”，而是包括stirng1中“x”以及右侧的字符）
    SELECT rtrim('abcxcba','abc') rtrim FROM dual;
    abcx  --（结果并不是只剩一个“x”，而是包括stirng1中“x”以及左侧的字符）

## 9，ROUND(X\[,Y\])和 TRUNC(x\[,y\])的用法 ##

*  ROUND(X\[,Y\])，四舍五入。
    
     *   在缺省 y 时，默认 y=0；比如：ROUND(3.56)=4。
     *  y 是正整数，就是四舍五入到小数点后 y 位。ROUND(5.654,2)=5.65。
     *  y 是负整数，四舍五入到小数点左边|y|位。ROUND(351.654,-2)=400。
 *  TRUNC(x\[,y\])，直接截取，不四舍五入。
    
     *  在缺省 y 时，默认 y=0；比如：TRUNC (3.56)=3。
     *  Y是正整数，就是四舍五入到小数点后 y 位。TRUNC (5.654,2)=5.65。
     *  y 是负整数，四舍五入到小数点左边|y|位。TRUNC (351.654,-2)=300。

## 10，日期函数 ##

### （1） ADD\_MONTHS(d,n) ###

在某一个日期 d 上，加上指定的月数 n，返回计算后的新日期。

d 表示日期，n 表示要加的月数。

SELECT SYSDATE,add_months(SYSDATE,5) FROM dual;

### （2） LAST\_DAY(d) ###

返回指定日期当月的最后一天。

SELECT SYSDATE,last_day(SYSDATE) FROM dual;

### （3）EXTRACT(fmt FROM d) ###

fmt 为：YEAR、MONTH、DAY、HOUR、SECOND。其中 YEAR、MONTH、DAY 可以为 DATA 类型匹配，也可以与 TIMESTAMP 类型匹配；但是 HOUR、MINUTE、SECOND 必须与 TIMESTAMP 类型匹配。

select to_char(sysdate,'yyyy/mm/dd hh24:mi:ss') "date",  
        extract(YEAR FROM SYSDATE) "year",  
        extract(MONTH FROM SYSDATE) "month",  
        extract(DAY FROM SYSDATE) "day",  
        extract(HOUR FROM SYSTIMESTAMP) "hour",  
        extract(MINUTE FROM SYSTIMESTAMP) "minute",  
        extract(SECOND FROM SYSTIMESTAMP) "second"  
        from dual;  
        
    date                      year   month     day    hour  minute     second  
    ------------------- ---------- ------- ------- ------- ------- ---------- 
    2012/04/03 18:53:42       2012       4       3   10         53      42.79

### （4）ROUND(d\[,fmt\]) ###

返回一个以fmt为格式的四舍五入日期值 。如果fmt为“YEAR”则舍入到某年的1月1日，即前半年舍去，后半年作为下 一年

如果fmt为“MONTH”则舍入到某月的1日，即前月舍去，后半月作为下一 月

默认为“DDD”，即月中的某一天，最靠近的天，前半天舍去，后半天作为第二天

如果fmt为“DAY”则舍入到最近的周的周日，即上半周舍去，下半周作为下 一周周日

select sysdate,round(sysdate),round(sysdate,'ddd'),
    round(sysdate,'day'),round(sysdate,'month'),round(sysdate,'year') from dual; 
    -----结果------
     2017-09-13 16:11:13 , 2017-09-14 00:00:00 , 2017-09-14 00:00:00 , 
     2017-09-17 00:00:00 , 2017-09-01 00:00:00 , 2018-01-01 00:00:00

## 11，转换函数 ##

### （1）TO\_CHAR(d|n\[,fmt\]) ###

to\_char函数的功能是将数值型或者日期型转化为字符型。

select to_char(ENDTIME,'YYYY-MM-DD') ENDTIME FROM tablename;

### （2）TO\_DATE(X,\[,fmt\]) ###

把一个字符串以fmt格式转换成一个日期类型。

to_date(sysdate,'yyyy-MM-dd HH24:mi:ss') --mi是分钟

## 12，聚合函数 ##

聚合函数同时对一组数据进行操作，返回一行结果，比如计算一组数据的总和，平均值等。

<table> 
 <tbody> 
  <tr> 
   <th>名称</th> 
   <th>作用</th> 
  </tr> 
  <tr> 
   <td>AVG</td> 
   <td>平均值</td> 
  </tr> 
  <tr> 
   <td>SUM</td> 
   <td>求和</td> 
  </tr> 
  <tr> 
   <td>MIN/MAX</td> 
   <td>最小值/最大值</td> 
  </tr> 
  <tr> 
   <td>COUNT</td> 
   <td>统计</td> 
  </tr> 
 </tbody> 
</table>

--求本月所有员工的基本工资总和
    SELECT SUM(sal) FROM emp;

--求不同部门的平均工资
    SELECT DEPTNO,AVG(SAL) FROM EMP GROUP BY DEPTNO;  
    ----结果------
    30    1566.66666
    20     2175
    10    2916.66666

## 13，Oracle wm\_concat（）函数 ##

wm\_concat()函数是oracle中独有的,mysql中有一个group\_concat()函数。这两个函数的作用是相同的，它们的功能是：实现行转列功能，即将查询出的某一列值使用逗号进行隔开拼接，成为一条数据。

例如：shopping表

u_id		goods		num
    1			苹果			2
    2			梨子			5
    1			西瓜			4
    3			葡萄			1

想要的结果为：

u_id		goodsnum
    1			苹果，西瓜
    2			梨子
    3			葡萄

就是下面的sql语句

select u\_id, wmsys.wm\_concat(goods) goods\_sum from shopping group by u\_id ;

想要的结果2为：

u_id					goodsnum
    1				苹果（2斤），西瓜（4斤）
    2						梨子（5斤）
    3						葡萄（1斤）

则是下面的sql语句

select u\_id, wmsys.wm\_concat(goods || ‘(’ || num || ‘斤)’ ) goods\_sum from shopping group by u\_id;

## 14，mybatis模糊查询 ##

<select id="findzzjf" parameterType="cn.com.qianlong.light.vo.zzjf.ScoreMainOrgVo" resultType="cn.com.qianlong.light.vo.zzjf.ScoreMainOrgVo">
        select org_id,org_name,scoreyear,score,orgplace from score_main_org
        <where>
            <if test="orgName != null and orgName != ''">
                and org_name like '%'||#{orgName,jdbcType=VARCHAR}||'%'
            </if>
        </where>
        </select>

## 15，oracle复制表（把b表的数据复制到a表） ##

如果两表字段相同，则可以直接这样用。

insert into table_a select * from table_b

如果两表字段不同，a表需要b中的某几个字段即可，则可以如下使用

insert into table_a(field_a1,field_a2,field_a3) select field_b1,field_b2,field_b3) from table_b

以上语句前提条件是每个字段对应的字段类型相同或可以自动转换。

## 16，find\_in\_set()函数的使用 ##

find\_in\_set的语法如下  
FIND\_IN\_SET(str,strlist);  
其中str为要查询的目标字符串, strlist为字符串的集合

INSERT INTO   tb_test   VALUES (1, 'name', 'mike,allen,jack,jay');
    INSERT INTO   tb_test   VALUES (2, 'name2', 'jay,pojo,jay');
    INSERT INTO   tb_test   VALUES (3, 'name3', 'allen,mike,yago');

使用FIND\_IN\_SET进行查询含有jay的list  
SELECT id,name,list from tb\_test WHERE FIND\_IN\_SET(‘jay’,list);

## 17，oracle Extract 函数 ##

oracle中extract()函数从oracle 9i中引入,用于从一个 date 或者interval类型中截取到特定的部分

//我们只可以从一个date类型中截取year,month,day ( date 日期的格式为yyyy-mm-dd); 
    //我们只可以从一个timestamp with time zone 的数据类型中截取TIMEZONE_HOUR和TIMEZONE_MINUTE; 
    select extract(year from date '2011-05-17') year from dual;   
              YEAR
    ---------- 
              2011   
    select extract( month from date '2011-05-17') month from dual;   
             MONTH
    ---------- 
                 5   
    select extract(day from date '2011-05-17') day from dual;   
               DAY
    ---------- 
                17

获取两个日期之间的具体时间间隔，extract函数是最好的选择

select extract(day from dt2-dt1) day
              ,extract(hour from dt2-dt1) hour
              ,extract(minute from dt2-dt1) minute
              ,extract(second from dt2-dt1) second
    from (select to_timestamp('2011-02-04 15:07:00','yyyy-mm-dd hh24:mi:ss') dt1 ,to_timestamp('2011-05-17 19:08:46','yyyy-mm-dd hh24:mi:ss') dt2 from dual) 
    ## 18，oracle中截取字符串
    `substr('ABCDEFG',1,4)`		截取字符串前四个  ABCD

## 18，case when和聚合函数count、sum的使用 ##

select 
    t3.region_id as 'regionId',
    t3.region_name as 'regionName',
    count(case when t1.tricolor = 1 then 1 else null end ) as 'greenTotal',
    count(case when t1.tricolor = 2 then 1 else null end ) as 'yellowTotal',
    count(case when t1.tricolor = 3 then 1 else null end )as 'redTotal'
    from pa_household_member t1
    left join pa_household t2 on t2.household_id = t1.household_id
    left join system_region t3 on t3.region_id = t2.area 
    group by t2.area

## 19，oracle中截取字符串 ##

`substr('ABCDEFG',1,4)` 截取字符串前四个 ABCD

## 20，trunc(sysdate,‘iw’) ##

trunc是截尾函数 TRUNC(SYSDATE,‘iw’)返回该星期中的星期一（本周第二天）