oracle常用函数

最新推荐文章于 2024-07-24 18:54:32 发布

不码不成才

最新推荐文章于 2024-07-24 18:54:32 发布

阅读量296

点赞数

分类专栏：数据库文章标签： oracle 函数 java

本文链接：https://blog.csdn.net/g123ggf/article/details/69666372

版权

数据库专栏收录该内容

5 篇文章 0 订阅

订阅专栏

字符串函数

CONCAT(c1,c2)
连接字符串

SELECT CONCAT(ename,sal)
FROM emp

SELECT CONCAT(CONCAT(ename,’,’),sal)
FROM emp

“||”也可以连接字符串
SELECT ename||’,’||sal
FROM emp

LENGTH(c): 求字符串长度
SELECT ename,LENGTH(ename)
FROM emp

UPPER,LOWER,INITCAP
将字符串转换为全大写，全小写，首字母大写

伪表:DUAL,当查询的内容与任何表没有关系时
可以在FROM子句中查询该表，会查询出一条记录
SELECT UPPER(‘helloworld’),
LOWER(‘HELLOWORLD’),
INITCAP(‘hello world’)
FROM dual

*TRIM,LTRIM,RTRIM去除字符串两边的指定字符*

SELECT TRIM(‘e’ FROM ‘eeeeliteee’)
FROM dual

SELECT LTRIM(‘eeesesliteee’,’se’)
FROM dual

LPAD,RPAD补位函数

允许将指定字符串显示指定位数，不足时
补充若干指定字符达到该长度。
SELECT ename,RPAD(sal,5,’$’)
FROM emp

SUBSTR(str,m[,n])
将给定字符串从m出开始连接截取n个字符
n不指定则是截取到末尾，n超过可以截取
的字符数量也是截取到末尾。
m可以是负数，负数则是从倒数位置处开始
数据库中的下标从1开始！！

SELECT
SUBSTR(‘thinking in java’,-7,2)
FROM dual

INSTR(c1,c2,m,n)
查看c2在c1中的位置
m用来指定从哪里开始检索，不写
默认从第一个字符开始
n表示第几次出现，不写默认为1
SELECT
INSTR(‘thinking in java’,’in’,4,2)
FROM dual

数值函数

ROUND(m,n):四舍五入
n为保留到小数点后的位数，
若n为负数则是10位以上的数字
SELECT ROUND(55.678,1) FROM dual
SELECT ROUND(55.678,0) FROM dual
SELECT ROUND(55.678,-2) FROM dual

TRUNC(m,n)函数:截取数字
SELECT TRUNC(55.678,1) FROM dual
SELECT TRUNC(55.678,0) FROM dual
SELECT TRUNC(55.678,-2) FROM dual

MOD(m,n)求余数
SELECT ename,MOD(sal,1000)
FROM emp

CEIL,FLOOR向上取整和向下取整
SELECT CEIL(45.678) FROM dual?
SELECT FLOOR(45.678) FROM dual

日期相关函数:
和日期相关的关键字
SYSDATE:返回一个Date类型数据，表示当前系统时间
SYSTIMESTAMP:返回一个时间戳类型数据，表示当前系统时间

SELECT SYSDATE FROM dual
SELECT SYSTIMESTAMP FROM dual

将当前系统时间插入某个DATE类型字段
INSERT INTO emp
(empno,ename,hiredate)
VALUES
(1001,’JACK’,SYSDATE)

TO_DATE函数
将一个字符串按照指定的日期格式解析为DATE
SELECT
TO_DATE(
‘1992-08-03 14:22:15’,
‘YYYY-MM-DD HH24:MI:SS’
)
FROM
dual

在日期格式字符串中，除英文，数字，符号外的
其他字符都要使用双引号括起来
SELECT
TO_DATE(
‘1992年08月03日 14时22分15秒’,
‘YYYY”年”MM”月”DD”日” HH24”时”MI”分”SS”秒”’
)
FROM
dual

TO_CHAR函数
可以将一个DATE按照给定的日期格式转换为字符串

SELECT
TO_CHAR(SYSDATE,’YYYY-MM-DD HH24:MI:SS’)
FROM
dual

日期类型是可以进行计算的:
1:对一个日期加减一个数字等同于加减天数
2:两个日期相减，差为相差的天数
同样，日期间也可以比较大小，越晚的越大。

查看每个员工入职到今天共多少天了?
SELECT ename,SYSDATE-hiredate
FROM emp

SELECT SYSDATE+1
FROM dual

LAST_DAY(date):
返回给定日期所在月的月底日期

当月月底是哪天?
SELECT LAST_DAY(SYSDATE)
FROM dual

ADD_MONTHS(date,i)
计算指定日期加上指定的月后的日期
若i是负数，则是计算减法。

查看每个员工入职20周年纪念日?
SELECT
ename,ADD_MONTHS(hiredate,12*20)
FROM emp

MONTHS_BETWEEN(d1,d2)
计算两个日期之间相差的月

SELECT
ename,MONTHS_BETWEEN(SYSDATE,hiredate)
FROM emp

NEXT_DAY(date,i)
返回给定日期之后一周内的指定周几的日期

SELECT
NEXT_DAY(SYSDATE,1)
FROM
dual

LEAST、GREATEST函数
求最小值与最大值

SELECT
LEAST(SYSDATE,TO_DATE(‘2008-08-08’,’YYYY-MM-DD’))
FROM
dual

EXTRACT函数，提取指定日期指定时间分量的值
SELECT EXTRACT(YEAR FROM SYSDATE)
FROM dual

查看1980年入职的员工?
SELECT ename,hiredate
FROM emp
WHERE
EXTRACT(YEAR FROM hiredate)=1981

空值操作

CREATE TABLE student(
id NUMBER(4),
name CHAR(20),
gender CHAR(1)
)

INSERT INTO student VALUES(1000, ‘李莫愁’, ‘F’);

INSERT INTO student VALUES(1001, ‘林平之’, NULL);

INSERT INTO student(id, name) VALUES(1002, ‘张无忌’);

SELECT * FROM student

UPDATE student
SET gender = NULL
WHERE id=1000

使用NULL做为判断条件
将性别为NULL的记录删除
DELETE FROM student
WHERE gender IS NOT NULL

NULL的运算
NULL与字符串连接等于什么都没做
NULL与数字运算结果还是NULL
查看每个员工的收入(工资+绩效)
SELECT ename,sal,comm,sal+comm
FROM emp

空值函数
NVL(f1,f2)
当f1为NULL时，函数返回f2的值，否则
返回f1自身
所以NVL函数的作用是将NULL值替换为非NULL

查看每个员工的收入(工资+绩效)
SELECT ename,sal,comm,sal+NVL(comm,0)
FROM emp

查看绩效情况，有绩效的显示”有绩效”
为NULL的则显示”没有绩效”
NVL2(f1,f2,f3)函数
当f1不为NULL时函数返回f2
当f1为NULL时，函数返回f3

SELECT
ename,comm,
NVL2(comm,’有绩效’,’没有绩效’)
FROM
emp

SELECT
ename,sal,comm,
NVL2(comm,sal+comm,sal)
FROM
emp

排序函数

排序函数允许对结果集按照指定字段分组
然后在组内按照指定字段排序，之后为每
组的记录编行号

ROW_NUMBER():生成组内连续且唯一的数字
（1,2,3,4,5）
查看每个部门的工资排名:
SELECT
ename,sal,deptno,
ROW_NUMBER() OVER(
PARTITION BY deptno
ORDER BY sal DESC
) rank
FROM
emp

RANK函数:组内不连续也不唯一的数字
（1,2,2,4,5）
SELECT
ename,sal,deptno,
RANK() OVER(
PARTITION BY deptno
ORDER BY sal DESC
) rank
FROM
emp

DENSE_RANK:生成组内连续但不唯一的数字
（1,2,2,3,4,5）
SELECT
ename,sal,deptno,
DENSE_RANK() OVER(
PARTITION BY deptno
ORDER BY sal DESC
) rank
FROM
emp

销售表
create table tb_sales(
year_id number not null,
month_id number not null,
day_id number not null,
sales_value number(10,2) not null
);

insert into tb_sales
select trunc(dbms_random.value(2010,2012)) as year_id,
trunc(dbms_random.value(1,13)) as month_id,
trunc(dbms_random.value(1,32)) as day_id,
round(dbms_random.value(1,100),2) as sales_value
from dual
connect by level <= 1000;

select * from tb_sales;

查看每天的营业额?
SELECT
year_id,month_id,
day_id,SUM(sales_value)
FROM
sales_tab
GROUP BY
year_id,month_id,day_id
ORDER BY
year_id,month_id,day_id

查看每月的营业额?
SELECT
year_id,month_id,SUM(sales_value)
FROM
sales_tab
GROUP BY
year_id,month_id
ORDER BY
year_id,month_id

查看每年的营业额?
SELECT
year_id,SUM(sales_value)
FROM
sales_tab
GROUP BY
year_id
ORDER BY
year_id

查看总收入?
SELECT
SUM(sales_value)
FROM
sales_tab

SELECT
year_id,month_id,day_id,SUM(sales_value)
FROM
sales_tab
GROUP BY
year_id,month_id,day_id
UNION ALL
SELECT
year_id,month_id,NULL,SUM(sales_value)
FROM
sales_tab
GROUP BY
year_id,month_id
UNION ALL
SELECT
year_id,NULL,NULL,SUM(sales_value)
FROM
sales_tab
GROUP BY
year_id
UNION ALL
SELECT
NULL,NULL,NULL,SUM(sales_value)
FROM
sales_tab