--换算单位并保存两位小数
select to_char(crlim/100,'9999999999999990.99') as crlim from A where cust='0000000610079545330' and rg=242
--获取当前日期
select to_char(sysdate,'YYYY-MM-DD') from dual
select b.NAME, sum(a.BYTES/1024/1024/1024) from v$datafile a, v$tablespace b where a.TS#=b.TS# group by b.NAME
---联机时间转换
to_date(substr(to_char(a.date_online),0,13),'yyyydddhh24miss')
--日期范围查询
select a.date_created from ot_data_amb a where a.date_created between to_date('20140901','YYYYmmDD') and to_date('20150901','YYYYmmDD')
--查询date类型的day、month、year是符合条件的数据
SELECT DATE_LAST_STMT FROM OT_DATA_AMB WHERE extract( DAY FROM DATE_LAST_STMT) IN 11
SELECT DATE_LAST_STMT FROM OT_DATA_AMB WHERE extract( MONTH FROM DATE_LAST_STMT) IN 11
SELECT DATE_LAST_STMT FROM OT_DATA_AMB WHERE extract( YEAR FROM DATE_LAST_STMT) IN 11
select cust_nbr,billing_cycle,acct,date_last_cycle from ot_data_amb where block_code_1=' ' and block_code_2=' ' and acct in (select post_to_acct from ot_data_amed where block_code=' ' and curr_first_usage_flag='N') and extract(day from date_last_cycle) not in (11,12,29,30,31) and acct in (select account_no from ot_data_t_bill_tran_info )
--四舍五入函数 ROUND()
select ROUND(3.1415926,4) from dual; --3.1416
--截取函数TRUNC
select TRUNC(3.1415926,4) from dual;--3.1415
--FLOOR对指定数字取整数位
select FLOOR(3.1415926) from dual;--3
--CEIL返回大于或等于给出数字的最小整数
select CEIL(3.1415926) from dual; --4
--根据账单日推送还款日
select case
when (extract(day from last_cycle ) in(1,2,3,4,5,6,7,8,9,10)) then last_cycle+18
else (extrace(day from last_cycle) in(13,14,15,14,17,18,19,20,21,22,23,24,25,26,27,28)) then last_cycle -12
end as repay_day
from ot_data_amb where extract(day from last_cycle ) not in (11,12,29,30,31);