ORACLE一些函数说明及用法

1、exist(): 一般用在where条件中，当符合条件时where条件成立

where exists(select * from wrap_fee we where we.investor_id = da.investor_id and aum_date > trunc(created_date) and aum_date < nvl(end_date, sysdate + 1)

2、nvl(a,b)：取值

--nvl(a,b) a==null,返回b,否则返回a
--nvl2(a,b,c) a==null,返回c,否则返回b
select nvl(1,2) from dual;--1
select nvl2(1,2,3) from dual;--2

3、trunc()：一般用于数字和日期时间的截取
数字处理

--默认截取小数点后面的部分
select trunc(21.99) from dual;
--表示将小数点左边指定位数后面的部分截去，即均以0记;
select trunc(21.99, -2) from dual;--0
--表示将小数点左边指定位数后面的部分截去，即均以0记;
select trunc(21.99, -1) from dual;--20
--默认截取小数点后面的部分
select trunc(21.99, 0) from dual;--21
--截取小数点后一位
select trunc(21.99, 1) from dual;--21.9
--截取小数点后2位
select trunc(21.99, 2) from dual;--21.99
--截取小数点后4位
select trunc(21.99, 4) from dual;--21.99

日期处理

select trunc(sysdate,'d') from dual;--当前该周的第一天，周日起(2024-01-07)
select trunc(sysdate,'dd') from dual;--当天(2024-01-11)
select trunc(sysdate) from dual;--当天(2024-01-11)
select sysdate from dual;--当时(2024-01-11 15:05:19)
select trunc(sysdate,'yyyy') from dual;--当年第一天(2024-01-01)
select trunc(sysdate,'q') from dual;--当季度的第一天(2024-01-01)
select trunc(sysdate, 'mm') from dual;--当月第一天(2024-01-01)
select trunc(sysdate,'d') from dual;--当前该周的第一天，周日起(2024-01-07)
select trunc(sysdate, 'hh') from dual;--当前小时时间的整点时间，不是确切的时间(2024-01-11 15:00:00)
select trunc(sysdate, 'hh24') from dual;--同上(2024-01-11 15:00:00)
select trunc(sysdate, 'mi') from dual;--当前分钟，不包含秒(2024-01-11 15:13:00)

4、merge into：同时更新或者插入数据，也可删除

--给A表数据插入B表的数据,当A表id==B表id时，就更新A表的name为B的name,否则就给A表插入这条B表的数据
merge into A a using (select * from B a) on (A.ID=B.ID) WHEN MATCHED THEN UPDATE SET A.NAME=B.NAME WHEN NOT MERCHED WHETN INSERT (ID,NAME) VALUES(B.ID,B.NAME);

5、MONTHS_BETWEEN():

--两个时间月差
select MONTHS_BETWEEN(to_date('2024-01-01','yyyy-MM-dd'),to_date('2023-01-01','yyyy-MM-dd')) from dual;--12

6、to_date()：字符串转日期

select to_date('2024-01-01','yyyy-MM-dd') from dual;--2024-01-01

7、to_timestamp()：字符串转时间

select to_timestamp('2024-03-01 12:12:09','yyyy-MM-dd hh24:mi:ss') from dual;--2024-03-01 12:12:09.000000000

8、lag()，查询满足条件的前面记录

--查询开始时间，就是按investorId、endDate排序后，每一条满足条件的值的前一条数据，这里的joined_date是当irr.end_date为null的时候就已i.joined_date数据为默认值去查数据
select i.investor_id,
       nvl(iir.end_date, sysdate + 1)                                                                                    end_date,
       lag(iir.end_date, 1, i.joined_date) over (partition by iir.investor_id order by iir.investor_id, iir.end_date) as start_date
from INVESTOR i
         join ifa_investor_relation iir on i.INVESTOR_ID = iir.INVESTOR_ID;

9、lead()和lag()相反，查询满足条件的后面记录

--条件：用一个SQL语句（注意是一个）的求出所有人（不区分人员）每个月及上月和下月的总收入，要求列表输出为：月份、当月收入、上月收入、下月收入 
SELECT MONTHS, MAX(INCOMES), MAX(PREV_MONTHS), MAX(NEXT_MONTHS)
FROM (
         SELECT MONTHS '月份',
                INCOMES '当月收入',
                DECODE(LAG(MONTHS) OVER (ORDER BY MONTHS), TO_CHAR(ADD_MONTHS(TO_DATE(MONTHS, 'YYYYMM'), -1), 'YYYYMM'),
                       LAG(INCOMES) OVER (ORDER BY MONTHS), 0)  AS PREV_MONTHS '上月收入',
                DECODE(LEAD(MONTHS) OVER (ORDER BY MONTHS), TO_CHAR(ADD_MONTHS(TO_DATE(MONTHS, 'YYYYMM'), 1), 'YYYYMM'),
                       LEAD(INCOMES) OVER (ORDER BY MONTHS), 0) AS NEXT_MONTHS '下月收入'
         FROM (SELECT MONTHS, SUM(INCOME) AS INCOMES FROM A GROUP BY MONTHS) AA) AAA
GROUP BY MONTHS;

10、decode(a,b,c,d) 若a==b,则返回c，否则返回d，d未指定的时候，默认为null。类似与case when的简写，但是没有case when的条件和取值多，这里只有两种取值即c和d

select decode(1,1,1,0) from dual;--1

11、按条件查询后插入数据

--查询出等于-100的数据，然后吧这些数据都插入给为50的这个渠道
insert into product_fund_enable_setup (ifa_company_id, product_code_id, product_enable_matrix_id)
select 50, product_code_id, product_enable_matrix_id
from product_fund_enable_setup
where ifa_company_id = -100;

12、substr():截取字符串

--截取字符串
select substr('sd2223dddfsd',0,5) from dual;--sd222

13、rank()：排名

--返回员工的薪水排名
SELECT name,salary,
  RANK() OVER (ORDER BY salary DESC) AS salary_rank
FROM employees;

14、round():四舍五入

select round(1234.56,1) from dual;--1234.6
select round(1234.23,1) from dual;--1234.2

15、ceil():向上取整

select ceil(1234.23) from dual;--1235

16、mod():取模-余数

select mod(1234,6) from dual;--4
select mod(1234.23,1) from dual;--0.23

17、floor():向下取整

select floor(1234.69) from dual;--1234

18、to_number():转数字

select TO_NUMBER('0036') from dual;--36

19、fetch的用法：用于分页查询

---获取前10条数据
SELECT column1, column2, ...
FROM table_name
WHERE condition
ORDER BY column_name
FETCH FIRST 10 ROWS ONLY;

--获取第4条到第6条的数据
SELECT column1, column2, ...
FROM table_name
WHERE condition
ORDER BY column_name
OFFSET 4 ROWS FETCH NEXT 6 ROWS ONLY;

for update：FOR UPDATE语句在SQL中用于锁定表中的记录，以防止其他事务同时修改这些记录。

当一个事务执行SELECT … FOR UPDATE语句时，它会获取对指定表的锁，以确保在该事务期间，其他事务无法修改被选中的记录。这样可以确保数据的一致性和完整性。

具体的原理是，数据库管理系统使用一种称为"行级锁"的技术来实现锁定。当一个事务开始执行SELECT … FOR UPDATE语句时，它会为每个被选中的记录设置一个锁。这个锁可以防止其他事务在当前事务完成之前访问或修改这些记录。

当其他事务尝试修改被选中的记录时，它们会等待当前事务完成并释放锁。只有当当前事务成功完成并提交后，其他事务才能再次访问和修改这些记录。

通过使用FOR UPDATE语句，可以确保在并发环境中的数据一致性和完整性。例如，当多个用户同时读取和更新同一个数据项时，可以使用FOR UPDATE来避免竞态条件和脏读等问题。

--对sto==001的数据进行锁定，行级锁，防止其他地方修改它
select * from Student where sto='001' for update ;

extract用法：
Oracle数据库中的EXTRACT函数用于从日期或时间戳中提取特定的部分，如年、月、日等。它的语法如下：

EXTRACT(field FROM source)

其中，field是要提取的部分，可以是以下之一：

YEAR：年份
MONTH：月份
DAY：日期
HOUR：小时
MINUTE：分钟
SECOND：秒
TIMEZONE_HOUR：时区小时
TIMEZONE_MINUTE：时区分钟
source是要从中提取部分的日期或时间戳。

以下是一些示例：

提取年份：

//这将返回date_column列中每个日期的年份。
SELECT EXTRACT(YEAR FROM date_column) FROM table_name;

提取月份：

//这将返回date_column列中每个日期的月份。
SELECT EXTRACT(MONTH FROM date_column) FROM table_name;

提取日期：

//这将返回date_column列中每个日期的日期。
SELECT EXTRACT(DAY FROM date_column) FROM table_name;

提取小时：

//这将返回timestamp_column列中每个时间戳的小时部分。
SELECT EXTRACT(HOUR FROM timestamp_column) FROM table_name;

提取分钟：

//这将返回timestamp_column列中每个时间戳的分钟部分。
SELECT EXTRACT(MINUTE FROM timestamp_column) FROM table_name;

提取秒：

//这将返回timestamp_column列中每个时间戳的秒部分。
SELECT EXTRACT(SECOND FROM timestamp_column) FROM table_name;

提取时区小时：

//这将返回timestamp_with_timezone_column列中每个带时区的
SELECT EXTRACT(TIMEZONE_HOUR FROM timestamp_with_timezone_column) FROM table_name;

先写到这，有了再加