Oracle函数
自定义函数
http://www.manongjc.com/oracle/pl-sql-functions.html
<function_name>是FUNCTION的名称;
<parameter_name>是要传递的参数的名称IN，OUT或INand OUT;
<parameter_data_type>是相应参数的PL / SQL数据类型;
<return_data_type>是FUNCTION完成执行时将返回的值的PL / SQL数据类型。
CREATE [OR REPLACE] FUNCTION <function_name> [(
<parameter_name_1> [IN] [OUT] <parameter_data_type_1>,
<parameter_name_2> [IN] [OUT] <parameter_data_type_2>,...
<parameter_name_N> [IN] [OUT] <parameter_data_type_N> )]
RETURN <return_data_type> IS
--the declaration section
BEGIN
-- the executable section
return <return_data_type>;
EXCEPTION
-- the exception-handling section
END; --;不能缺少
测试一
CREATE OR REPLACE FUNCTION to_number_or_null ( aiv_number IN varchar2 )
return number is
BEGIN
return to_number(aiv_number);
exception
when OTHERS then return NULL;
END;
select to_number_or_null ( '123') ,to_number_or_null ( '123p')from dual;
内置函数
分析函数
基本分析函数
create table students
(id number(15,0),
area varchar2(10),
stu_type varchar2(2),
score number(20,2));
INSERT ALL
into students values(1, '111', 'g', 80 )
into students values(1, '111', 'j', 80 )
into students values(1, '222', 'g', 89 )
into students values(1, '222', 'g', 68 )
into students values(2, '111', 'g', 80 )
into students values(2, '111', 'j', 70 )
into students values(2, '222', 'g', 60 )
into students values(2, '222', 'j', 65 )
into students values(3, '111', 'g', 75 )
into students values(3, '111', 'j', 58 )
into students values(3, '222', 'g', 58 )
into students values(3, '222', 'j', 90 )
into students values(4, '111', 'g', 89 )
into students values(4, '111', 'j', 90 )
into students values(4, '222', 'g', 90 )
into students values(4, '222', 'j', 89 )
SELECT 1 FROM dual;
例子1 GROUPING SETS
select a, b, c, sum( d ) from t
group by grouping sets ( a, b, c )
等效于
select * from (
select a, null, null, sum( d ) from t group by a
union all
select null, b, null, sum( d ) from t group by b
union all
select null, null, c, sum( d ) from t group by c
)
--测试
select id,area,stu_type,sum(score) score
from students
group by grouping sets((id,area,stu_type),(id,area),id)
order by id,area,stu_type;
摘自其中一部分，此函数按(id,area,stu_type)、(id,area)、id进行分组输出
| ID | AREA | STU_TYPE | SCORE |
|---|---|---|---|
| 2 | 111 | g | 80 |
| 2 | 111 | j | 70 |
| 2 | 111 | NULL | 150 |
| 2 | 222 | g | 60 |
| 2 | 222 | j | 65 |
| 2 | 222 | NULL | 125 |
| 2 | NULL | NULL | 275 |
例子2 ROLLUP
select a, b, c, sum( d )
from t
group by rollup(a, b, c);
等效于
select * from (
select a, b, c, sum( d ) from t group by a, b, c
union all
select a, b, null, sum( d ) from t group by a, b
union all
select a, null, null, sum( d ) from t group by a
union all
select null, null, null, sum( d ) from t
)
--测试
select id,area,stu_type,sum(score) score
from students
group by rollup(id,area,stu_type)
order by id,area,stu_type;
摘自其中一部分，此函数按(id,area,stu_type)、(id,area)、id进行分组输出，跟上一个例子一样
| ID | AREA | STU_TYPE | SCORE |
|---|---|---|---|
| 2 | 111 | g | 80 |
| 2 | 111 | j | 70 |
| 2 | 111 | NULL | 150 |
| 2 | 222 | g | 60 |
| 2 | 222 | j | 65 |
| 2 | 222 | NULL | 125 |
| 2 | NULL | NULL | 275 |
例子3 CUBE
select a, b, c, sum( d ) from t
group by cube( a, b, c)
等效于
select a, b, c, sum( d ) from t
group by grouping sets(
( a, b, c ),
( a, b ), ( a ), ( b, c ),
( b ), ( a, c ), ( c ),
() )
--测试
select id,area,stu_type,sum(score) score
from students
group by cube(id,area,stu_type)
order by id,area,stu_type;
| ID | AREA | STU_TYPE | SCORE |
|---|---|---|---|
| 2 | 111 | g | 80 |
| 2 | 111 | j | 70 |
| 2 | 111 | 150 | |
| 2 | 222 | g | 60 |
| 2 | 222 | j | 65 |
| 2 | 222 | 125 | |
| 2 | g | 140 | |
| 2 | j | 135 | |
| 2 | 275 |
例子4 GROUPING
grouping函数判断是否合计列
select
decode(grouping(id),1,'all id',id) id,
decode(grouping(area),1,'all area',to_char(area)) area,
decode(grouping(stu_type),1,'all_stu_type',stu_type) stu_type,
sum(score) score
from students
group by cube(id,area,stu_type)
order by id,area,stu_type;
| ID | AREA | STU_TYPE | SCORE |
|---|---|---|---|
| 2 | 111 | all_stu_type | 150 |
| 2 | 111 | g | 80 |
| 2 | 111 | j | 70 |
| 2 | 222 | all_stu_type | 125 |
| 2 | 222 | g | 60 |
| 2 | 222 | j | 65 |
| 2 | all area | all_stu_type | 275 |
| 2 | all area | g | 140 |
| 2 | all area | j | 135 |
OVER()函数
允许并列名次、名次不间断，DENSE_RANK()，结果如122344456
SELECT id, area, score,
DENSE_RANK() OVER(PARTITION BY id ORDER BY score DESC) 分组id排序,
DENSE_RANK() OVER(ORDER BY score DESC) 不分组排序
FROM students
ORDER BY id, area;
不并列名次ROW_NUMBER()
SELECT id, area, score,
ROW_NUMBER() OVER(PARTITION BY id ORDER BY score DESC) 分组id排序,
ROW_NUMBER() OVER(ORDER BY score DESC) 不分组排序
FROM students
ORDER BY id, area;
分组统计－－sum(),max(),avg(),RATIO_TO_REPORT()
select id,area,
sum(1) over() as 总记录数,
sum(1) over(partition by id) as 分组记录数,
sum(score) over() as 总计 ,
sum(score) over(partition by id) as 分组求和,
sum(score) over(order by id) as 分组连续求和,
sum(score) over(partition by id,area) as 分组ID和area求和,
sum(score) over(partition by id order by area) as 分组ID并连续按area求和,
max(score) over() as 最大值,
max(score) over(partition by id) as 分组最大值,
max(score) over(order by id) as 分组连续最大值,
max(score) over(partition by id,area) as 分组ID和area求最大值,
max(score) over(partition by id order by area) as 分组ID并连续按area求最大值,
avg(score) over() as 所有平均,
avg(score) over(partition by id) as 分组平均,
avg(score) over(order by id) as 分组连续平均,
avg(score) over(partition by id,area) as 分组ID和area平均,
avg(score) over(partition by id order by area) as 分组ID并连续按area平均,
RATIO_TO_REPORT(score) over() as "占所有%",
RATIO_TO_REPORT(score) over(partition by id) as "占分组%",
score from students;
连续求和分析函数
表建立
--创建表
CREATE TABLE SCOTT.EMP (
DEPTNO VARCHAR2(100),
ENAME VARCHAR2(100),
SAL INTEGER
)
TABLESPACE USERS;
INSERT INTO SCOTT.EMP(DEPTNO,ENAME,SAL) VALUES ('10','CLARK',2450);
INSERT INTO SCOTT.EMP(DEPTNO,ENAME,SAL) VALUES ('10','KING',5000);
INSERT INTO SCOTT.EMP(DEPTNO,ENAME,SAL) VALUES ('10','MILLER',1300);
INSERT INTO SCOTT.EMP(DEPTNO,ENAME,SAL) VALUES ('20','SMITH',800);
INSERT INTO SCOTT.EMP(DEPTNO,ENAME,SAL) VALUES ('20','ADAMS',1100);
INSERT INTO SCOTT.EMP(DEPTNO,ENAME,SAL) VALUES ('20','FORD',3000);
INSERT INTO SCOTT.EMP(DEPTNO,ENAME,SAL) VALUES ('20','SCOTT',3000);
INSERT INTO SCOTT.EMP(DEPTNO,ENAME,SAL) VALUES ('20','JONES',2975);
INSERT INTO SCOTT.EMP(DEPTNO,ENAME,SAL) VALUES ('30','ALLEN',1600);
INSERT INTO SCOTT.EMP(DEPTNO,ENAME,SAL) VALUES ('30','BLAKE',2850);
INSERT INTO SCOTT.EMP(DEPTNO,ENAME,SAL) VALUES ('30','MARTIN',1250);
INSERT INTO SCOTT.EMP(DEPTNO,ENAME,SAL) VALUES ('30','JAMES',950);
INSERT INTO SCOTT.EMP(DEPTNO,ENAME,SAL) VALUES ('30','TURNER',1500);
INSERT INTO SCOTT.EMP(DEPTNO,ENAME,SAL) VALUES ('30','WARD',1250);
员工连续求和
使用 sum(sal) over (order by ename)... 查询员工的薪水“连续”求和,
注意over (order by ename)如果没有order by 子句，求和就不是“连续”的，
select deptno,ename,sal,
sum(sal) over (order by ename) 连续求和,
sum(sal) over () 总和, -- 此处sum(sal) over () 等同于sum(sal)
100*round(sal/sum(sal) over (),4) "份额(%)" -- 此员工的销售份额
from emp;
| DEPTNO | ENAME | SAL | 连续求和 | 总和 | 份额(%) |
|---|---|---|---|---|---|
| 20 | ADAMS | 1,100 | 1,100 | 29,025 | 3.79 |
| 30 | ALLEN | 1,600 | 2,700 | 29,025 | 5.51 |
| 30 | BLAKE | 2,850 | 5,550 | 29,025 | 9.82 |
| 10 | CLARK | 2,450 | 8,000 | 29,025 | 8.44 |
| 20 | FORD | 3,000 | 11,000 | 29,025 | 10.34 |
| 30 | JAMES | 950 | 11,950 | 29,025 | 3.27 |
| 20 | JONES | 2,975 | 14,925 | 29,025 | 10.25 |
| 10 | KING | 5,000 | 19,925 | 29,025 | 17.23 |
| 30 | MARTIN | 1,250 | 21,175 | 29,025 | 4.31 |
| 10 | MILLER | 1,300 | 22,475 | 29,025 | 4.48 |
| 20 | SCOTT | 3,000 | 25,475 | 29,025 | 10.34 |
| 20 | SMITH | 800 | 26,275 | 29,025 | 2.76 |
| 30 | TURNER | 1,500 | 27,775 | 29,025 | 5.17 |
| 30 | WARD | 1,250 | 29,025 | 29,025 | 4.31 |
使用子分区
查出各部门薪水连续的总和。注意按部门分区。注意over(...)条件的不同，
select deptno,ename,sal,
sum(sal) over (partition by deptno order by ename) 部门连续求和,--各部门的薪水"连续"求和
sum(sal) over (partition by deptno) 部门总和, -- 部门统计的总和，同一部门总和不变
100*round(sal/sum(sal) over (partition by deptno),4) "部门份额(%)",
sum(sal) over (order by deptno,ename) 连续求和, --所有部门的薪水"连续"求和
sum(sal) over () 总和, -- 此处sum(sal) over () 等同于sum(sal)，所有员工的薪水总和
100*round(sal/sum(sal) over (),4) "总份额(%)"
from emp;
| DEPTNO | ENAME | SAL | 部门连续求和 | 部门总和 | 部门份额(%) | 连续求和 | 总和 | 总份额(%) |
|---|---|---|---|---|---|---|---|---|
| 10 | CLARK | 2,450 | 2,450 | 8,750 | 28 | 2,450 | 29,025 | 8.44 |
| 10 | KING | 5,000 | 7,450 | 8,750 | 57.14 | 7,450 | 29,025 | 17.23 |
| 10 | MILLER | 1,300 | 8,750 | 8,750 | 14.86 | 8,750 | 29,025 | 4.48 |
| 20 | ADAMS | 1,100 | 1,100 | 10,875 | 10.11 | 9,850 | 29,025 | 3.79 |
| 20 | FORD | 3,000 | 4,100 | 10,875 | 27.59 | 12,850 | 29,025 | 10.34 |
| 20 | JONES | 2,975 | 7,075 | 10,875 | 27.36 | 15,825 | 29,025 | 10.25 |
| 20 | SCOTT | 3,000 | 10,075 | 10,875 | 27.59 | 18,825 | 29,025 | 10.34 |
| 20 | SMITH | 800 | 10,875 | 10,875 | 7.36 | 19,625 | 29,025 | 2.76 |
| 30 | ALLEN | 1,600 | 1,600 | 9,400 | 17.02 | 21,225 | 29,025 | 5.51 |
| 30 | BLAKE | 2,850 | 4,450 | 9,400 | 30.32 | 24,075 | 29,025 | 9.82 |
| 30 | JAMES | 950 | 5,400 | 9,400 | 10.11 | 25,025 | 29,025 | 3.27 |
| 30 | MARTIN | 1,250 | 6,650 | 9,400 | 13.3 | 26,275 | 29,025 | 4.31 |
| 30 | TURNER | 1,500 | 8,150 | 9,400 | 15.96 | 27,775 | 29,025 | 5.17 |
| 30 | WARD | 1,250 | 9,400 | 9,400 | 13.3 | 29,025 | 29,025 | 4.31 |
求和规则有按部门分区的，有不分区的例子
select deptno,ename,sal,
sum(sal) over (partition by deptno order by sal) dept_sum, -- 部门分区
sum(sal) over (order by deptno,sal) sum --不分区
from emp;
| DEPTNO | ENAME | SAL | DEPT_SUM | SUM |
|---|---|---|---|---|
| 10 | MILLER | 1,300 | 1,300 | 1,300 |
| 10 | CLARK | 2,450 | 3,750 | 3,750 |
| 10 | KING | 5,000 | 8,750 | 8,750 |
| 20 | SMITH | 800 | 800 | 9,550 |
| 20 | ADAMS | 1,100 | 1,900 | 10,650 |
| 20 | JONES | 2,975 | 4,875 | 13,625 |
| 20 | FORD | 3,000 | 10,875 | 19,625 |
| 20 | SCOTT | 3,000 | 10,875 | 19,625 |
| 30 | JAMES | 950 | 950 | 20,575 |
| 30 | MARTIN | 1,250 | 3,450 | 23,075 |
| 30 | WARD | 1,250 | 3,450 | 23,075 |
| 30 | TURNER | 1,500 | 4,950 | 24,575 |
| 30 | ALLEN | 1,600 | 6,550 | 26,175 |
| 30 | BLAKE | 2,850 | 9,400 | 29,025 |
部门从大到小排列，部门里各员工的薪水从高到低排列，累计和的规则不变
select deptno,ename,sal,
sum(sal) over (partition by deptno order by deptno desc,sal desc) dept_sum,
sum(sal) over (order by deptno desc,sal desc) sum
from emp;
| DEPTNO | ENAME | SAL | DEPT_SUM | SUM |
|---|---|---|---|---|
| 30 | BLAKE | 2,850 | 2,850 | 2,850 |
| 30 | ALLEN | 1,600 | 4,450 | 4,450 |
| 30 | TURNER | 1,500 | 5,950 | 5,950 |
| 30 | WARD | 1,250 | 8,450 | 8,450 |
| 30 | MARTIN | 1,250 | 8,450 | 8,450 |
| 30 | JAMES | 950 | 9,400 | 9,400 |
| 20 | FORD | 3,000 | 6,000 | 15,400 |
| 20 | SCOTT | 3,000 | 6,000 | 15,400 |
| 20 | JONES | 2,975 | 8,975 | 18,375 |
| 20 | ADAMS | 1,100 | 10,075 | 19,475 |
| 20 | SMITH | 800 | 10,875 | 20,275 |
| 10 | KING | 5,000 | 5,000 | 25,275 |
| 10 | CLARK | 2,450 | 7,450 | 27,725 |
| 10 | MILLER | 1,300 | 8,750 | 29,025 |
重复的排序
在"... from emp;"后面不要加order by 子句，使用的分析函数的(partition by deptno order by sal)，里已经有排序的语句了。
select deptno,ename,sal,
sum(sal) over (partition by deptno order by sal) dept_sum,
sum(sal) over (order by deptno,sal) sum
from emp
order by deptno desc;
| DEPTNO | ENAME | SAL | DEPT_SUM | SUM |
|---|---|---|---|---|
| 30 | JAMES | 950 | 950 | 20,575 |
| 30 | MARTIN | 1,250 | 3,450 | 23,075 |
| 30 | WARD | 1,250 | 3,450 | 23,075 |
| 30 | TURNER | 1,500 | 4,950 | 24,575 |
| 30 | ALLEN | 1,600 | 6,550 | 26,175 |
| 30 | BLAKE | 2,850 | 9,400 | 29,025 |
| 20 | SMITH | 800 | 800 | 9,550 |
| 20 | ADAMS | 1,100 | 1,900 | 10,650 |
| 20 | JONES | 2,975 | 4,875 | 13,625 |
| 20 | SCOTT | 3,000 | 10,875 | 19,625 |
| 20 | FORD | 3,000 | 10,875 | 19,625 |
| 10 | MILLER | 1,300 | 1,300 | 1,300 |
| 10 | CLARK | 2,450 | 3,750 | 3,750 |
| 10 | KING | 5,000 | 8,750 | 8,750 |
ROW_NUMBER()
【语法】ROW_NUMBER() OVER (PARTITION BY COL1 ORDER BY COL2)
【功能】表示根据COL1分组，在分组内部根据 COL2排序，而这个值就表示每组内部排序后的顺序编号（组内连续的唯一的）
row_number() 返回的主要是“行”的信息，并没有排名
【说明】排序后顺序号分析函数
主要功能：用于取前几名，或者最后几名等
【示例】
表内容如下：
name | seqno | description
A | 1 | test
A | 2 | test
A | 3 | test
A | 4 | test
B | 1 | test
B | 2 | test
B | 3 | test
B | 4 | test
C | 1 | test
C | 2 | test
C | 3 | test
C | 4 | test
-- 希望获取每个分组前2名
A | 1 | test
A | 2 | test
B | 1 | test
B | 2 | test
C | 1 | test
C | 2 | test
--根据name分组，然后seqno升序排序
select name,seqno,description
from(
select name,seqno,description,row_number() over (partition by name order by seqno) id
from table_name
) where id<=3;
排序值分析函数
【语法】
RANK ( ) OVER ( [query_partition_clause] order_by_clause )
dense_RANK ( ) OVER ( [query_partition_clause] order_by_clause )
【功能】聚合函数RANK 和 dense_rank 主要的功能是计算一组数值中的排序值。
【参数】dense_rank与rank()用法相当，
【区别】dence_rank在并列关系是，相关等级不会跳过。rank则跳过
rank()是跳跃排序，有两个第二名时接下来就是第四名（同样是在各个分组内）
dense_rank()l是连续排序，有两个第二名时仍然跟着第三名。
【说明】Oracle分析函数
例子1
按col2分组，col1排序
with cte as(
select '1' COL1 ,'1' COL2 from dual union all
select '2' COL1 ,'1' COL2 from dual union ALL
select '3' COL1 ,'2' COL2 from dual union ALL
select '3' COL1 ,'1' COL2 from dual union ALL
select '4' COL1 ,'1' COL2 from dual union ALL
select '4' COL1 ,'2' COL2 from dual union ALL
select '5' COL1 ,'2' COL2 from dual union ALL
select '5' COL1 ,'2' COL2 from dual union ALL
select '6' COL1 ,'2' COL2 from dual
)
SELECT a.*,RANK() OVER(PARTITION BY col2 ORDER BY col1) "Rank" FROM cte a;
SELECT a.*,DENSE_RANK() OVER(PARTITION BY col2 ORDER BY col1) "Rank" FROM cte a;
相同的排序，rank相同，RANK()跳过了4，DENSE_RANK() 不会跳过
| COL1 | COL2 | Rank |
|---|---|---|
| 1 | 1 | 1 |
| 2 | 1 | 2 |
| 3 | 1 | 3 |
| 4 | 1 | 4 |
| 3 | 2 | 1 |
| 4 | 2 | 2 |
| 5 | 2 | 3 |
| 5 | 2 | 3 |
| 6 | 2 | 5 |
例子2取分组前两位
select * from (
--先分组排序
select rank() over(partition by COL2 order by COL1 asc) rk,a.* from cte a
) t
where t.rk<=2;
聚合函数
AVG
SUM
COUNT ：count(*) = sum(1) 空值不会累计
MAX
MIN
数值函数
mod
【功能】返回x除以y的余数
【参数】x,y，数字型表达式
【返回】数字
select mod(23,8),mod(24,8) from dual;
返回：7,0
power
【功能】返回x的y次幂
【参数】x，y 数字型表达式
【返回】数字
select power(2.5,2),power(1.5,0),power(20,-1) from dual;
返回：6.25,1,0.05
power(2.5,2) = (2/5)^2
round
round(x[,y])
【功能】返回四舍五入后的值
【参数】x,y，数字型表达式,如果y不为整数则截取y整数部分，如果y>0则四舍五入为y位小数，如果y小于0则四舍五入到小数点向左第y位。
【返回】数字
【相近】trunc(x[,y])
返回截取后的值，用法同round(x[,y]),只是不四舍五入
select round(5555.6666,2.1),round(5555.6666,-2.6),round(5555.6666),ROUND(-666.588,-2),round(5555.6666,2) from dual;
返回： 5555.67,5600,5556-700，5555.67
trunc
trunc(x[,y])
【功能】返回x按精度y截取后的值
【参数】x,y，数字型表达式,如果y不为整数则截取y整数部分，如果y>0则截取到y位小数，如果y小于0则截取到小数点向左第y位，小数前其它数据用0表示。
【返回】数字
select trunc(5555.66666,2.1),trunc(5555.66666,-2.6),trunc(5555.033333),trunc(5555.036333,2) from dual;
返回：5555.66 5500 5555 5555.03(没有四舍五入)
TRUNC(123.99,1)=123.9
TRUNC(-123.99,1)=-123.9
TRUNC(123.99,-1)=120
TRUNC(-123.99,-1)=-120
TRUNC(123.99)=123
字符函数
大小写
-- 首字符大写其他小写
select initcap('HELLO') upp from dual; --Hello
-- 全部转为小写/大写
select lower('AaBbCcDd') str,UPPER('AaBbCcDd') str2 from dual;
字符串拼接
用||连接字符串，mssql用+
CONCAT(c1,c2)
【功能】连接两个字符串
【参数】c1，c2 字符型表达式
SELECT CONCAT('a','b'),'a'||'b' FROM dual;--ab
wm_concat
返回逗号间隔的字符串,此函数用在查询语句将多行某列的值以逗号返回
SELECT wm_concat(m.ITEM_NO)
FROM UMS_INVOICE_DETAIL d INNER JOIN UMS_TAX_ITEM_MAPPING m
ON d.CORP_NO = m.CORP_NO AND d.INV_SN = m.HBBM
WHERE rownum<=5
--213,213,124,124,124
--改为 wm_concat(DISTINCT(m.ITEM_NO)) 则剔除其他重复数据
124,213
字符串分割
SELECT REGEXP_SUBSTR('字符串','[^特定字符]+',1,LEVEL,'i') AS str
FROM dual
CONNECT BY LEVEL <= LENGTHB(TRANSLATE('字符串','特定字符'||'字符串','特定字符')) +1
SELECT a.str FROM
(
SELECT REGEXP_SUBSTR('A,B,C','[^,]+',1,LEVEL,'i') AS str
FROM dual
CONNECT BY LEVEL <= LENGTHB(TRANSLATE('A,B,C',','||'A,B,C',',')) +1
)a
搜索字符串
INSTR(C1,C2[,I[,J]]) ：多字节符(汉字、全角符等)，按1个字符计算，找不到返回0，有两个以上字符则返回第一个的位置
INSTRB 返回字节位置，1个中文字符占两个字节，utf8中占三个字节 比较少用
-- 第一个B出现的位置 2
select instr('ABCBD','B') instring from dual;
-- 第二个B出现的位置 4
select instr('ABCBD','B',1,2) instring from dual;
-- instrb 函数多字节符(汉字、全角符等)，按2个字符计算
select
instr('重庆某软件公司','某',1,1) INSTR1, -- 3
instrb('重庆某软件公司','某',1,1) INSTR2 -- 7 返回字节位置
from dual;
字符串长度
LENGTH(c1)：多字节符(汉字、全角符等)，按1个字符计算，常用
LENGTHB 给出该字符串的byte
LENGTHC 使用纯Unicode
LENGTH2 使用UCS2
LENGTH4 使用UCS4
Select
LENGTH('你好'), -- 2
lengthB('你好'), -- 6
lengthC('你好'), -- 2
length2('你好'), -- 2
length4('你好') -- 2
from dual;
填充字符串
select
lpad('gao',5,'*'), -- **gao
lpad('gao',1,'*'), -- g
rpad('gao',5,'*'), -- gao**
rpad('gao',1,'*') -- g
from dual;
删除字符串
SELECT LTRIM('XXXXABC','X'),RTRIM('ABCDXXX','X'),LTRIM('AXXBC','X') FROM DUAL
-- ABC ABCD AXXBC
SELECT TRIM('X' FROM 'XABCDX') FROM DUAL --删除左右字符
ABCD
字符串替换
replace针对的是字符串，而translate针对的是单个字符。
select translate(ename,'SH','AB') from emp; --表示将ename中的'S'换成'A','H'换成'B';
select
replace('ABCD','C','') test, -- ABD
replace('ABCDC','C','-') test2 -- AB-D-
from dual;
SELECT
TRANSLATE('he love you','he','i') T1, -- i lov you h->i e->空 字符替换
TRANSLATE('itmyhome#163.com$is my* email', '#$ *', '@__') t2, -- itmyhome@163.com_is_my_email
TRANSLATE('A,B,C',','||'A,B,C',',') T3, -- ,, 返回字符串中逗号
TRANSLATE('A,B,C',',A,B,C',',') T3, --同上
replace('itmyhome#163%com', '#%', '@.') T4 -- itmyhome#163%com 没有找到#%字符串
from dual;
截取字符串
SUBSTR(字符串,开始位置,长度)，字符串位置从1开始，缺少第3个参数则返回开始位置后的全部字符串
substrb按字节计算
select
substr('ABCD',2,2) T1, -- BC
substr('ABCD',2) T2, -- BCD
substrb('ABCD测试EFG',2,6) T2 --BCD测
from dual;
日期函数
时间提取
SELECT
sysdate 当前日期,
EXTRACT(YEAR FROM sysdate ) 年,
EXTRACT(MONTH FROM sysdate ) 月,
EXTRACT(DAY FROM sysdate ) 日,
to_char(sysdate,'hh24') 时,
to_char(sysdate,'mi') 分,
to_char(sysdate,'ss') 秒
FROM dual;
间隔月数
【功能】：返回日期d1到日期d2之间的月数。
【参数】：d1，d2 日期型
【返回】：数字
如果d1>d2，则返回正数
如果d1<d2，则返回负数
SELECT
months_between(TO_DATE('2006-10-01','YYYY-MM-DD') , to_date('2006-01-01', 'YYYY-MM-DD')) d1,
months_between(TO_DATE('2006-10-20','YYYY-MM-DD') , to_date('2006-01-01', 'YYYY-MM-DD')) d2
FROM dual;
输入了天数，会导致返回是浮点数
9 9.61290322580645161290322580645161290323
返回日期相关时间
SELECT
sysdate, --2022-08-09 13:57:57.000
last_day(sysdate) T1, --2022-08-31 13:57:57.000
trunc(last_day(sysdate)) T1,--2022-08-31 00:00:00.000 常用
trunc(sysdate,'month') T2 --2022-08-01 00:00:00.000 常用
FROM dual;
select
sysdate 当时日期, --2022-08-09 13:58:38.000
round(sysdate) 最近0点日期, --2022-08-10 00:00:00.000 四舍五入时间
round(sysdate,'day') 最近星期日, --2022-08-07 00:00:00.000
round(sysdate,'month') 最近月初, --2022-08-01 00:00:00.000
round(sysdate,'q') 最近季初日期, --2022-07-01 00:00:00.000
round(sysdate,'year') 最近年初日期 --2023-01-01 00:00:00.000
from dual;
select
sysdate 当时日期, --2022-08-09 13:59:43.000
trunc(sysdate) 今天日期, --2022-08-09 00:00:00.000 没有四舍五入时间
trunc(sysdate,'day') 本周星期日, --2022-08-07 00:00:00.000
trunc(sysdate,'month') 本月初, --2022-08-01 00:00:00.000
trunc(sysdate,'q') 本季初日期, --2022-07-01 00:00:00.000
trunc(sysdate,'year') 本年初日期 --2022-01-01 00:00:00.000
from dual;
时间加减
与mssql的DATEADD函数不同
SELECT
SYSDATE "当前时间",
SYSDATE + 1 "加一天",
SYSDATE + (1 / 24) "加一小时",
SYSDATE + (1 / 24 / 60) "加一分钟",
SYSDATE + (1 / 24 / 60 / 60) "加一秒钟",
SYSDATE - 1 "减一天"
FROM
dual;
-----------------ADD_MONTHS-----------------------
SELECT
SYSDATE "当前时间",
ADD_MONTHS (SYSDATE, 1) "加一月",
ADD_MONTHS (SYSDATE, - 1) "减一月",
ADD_MONTHS (SYSDATE, 1 * 12) "加一年",
ADD_MONTHS (SYSDATE, - 1 * 12) "减一年"
FROM
dual;
----------------日期相减-----------------
--6 天
SELECT TO_DATE('2022-06-16','YYYY-MM-DD') - TO_DATE('2022-06-10','YYYY-MM-DD') FROM dual
--6.5天
SELECT
TO_DATE('2022-06-16 12:00:00','YYYY-MM-DD HH24:MI:SS') - TO_DATE('2022-06-10 00:00:00','YYYY-MM-DD HH24:MI:SS')
FROM dual
INTERVAL
INTERVAL '时间差数值' { YEAR | MONTH | DAY | HOUR | MINUTE | SECODE} (精度数值)
SELECT
SYSDATE "当前时间",
SYSDATE + INTERVAL '1' YEAR "加一年",
SYSDATE + INTERVAL '-1' YEAR "减一年",
SYSDATE + INTERVAL '1' MONTH "加一月",
SYSDATE + INTERVAL '1' DAY "加一天",
SYSDATE + INTERVAL '1' HOUR "加一小时",
SYSDATE + INTERVAL '1' MINUTE "加一分钟",
SYSDATE + INTERVAL '1' SECOND "加一秒",
to_char((SYSDATE+INTERVAL '5' MINUTE),'YYYY-MM-DD HH24:MI:SS') "加5分钟转换"
FROM
dual;
SELECT
SYSDATE 当前日期时间,
TRUNC(SYSDATE) 当前日期,
SYSDATE +(INTERVAL '01:02:03' HOUR TO SECOND) 小时到秒,
SYSDATE+(INTERVAL '01:02' MINUTE TO SECOND) 分钟到秒,
SYSDATE+(INTERVAL '01:02' HOUR TO MINUTE) 小时到分钟,
SYSDATE+(INTERVAL '2 01:02' DAY TO MINUTE) 天数到分钟
FROM dual;
当前系统日期/时间
sysdate：不带毫秒，SYSTIMESTAMP带毫秒
SELECT
SYSDATE T1, --2022-08-09 13:50:52.000
CURRENT_DATE T2, --2022-08-09 13:50:52.000
SYSTIMESTAMP T3, --2022-08-09 13:50:52.023 +0800
TO_CHAR(SYSTIMESTAMP, 'YYYY-MM-DD HH24:MI:SS.FF6') AS T4, --2022-08-09 13:50:52.023604
TO_CHAR(SYSDATE, 'YYYY-MM-DD HH24:MI:SS') AS T5, --2022-08-09 13:50:52
TO_CHAR(SYSTIMESTAMP, 'YYYY-MM-DD HH24:MI:SS.FF3') AS T6, --2022-08-09 13:50:52.023
LOCALTIMESTAMP T7, --当前日期时间 2022-08-09 13:50:52.000
TRUNC(SYSDATE) T8 --当前日期不带时间 2022-08-09 00:00:00.000
FROM DUAL;
转换函数
字符串转日期时间
SELECT
to_date('199912', 'yyyymm') d1,
to_date('2000.05.20', 'yyyy.mm.dd') d2,
(DATE '2008-12-31') d3,
to_date('2008-12-31 12:31:30', 'yyyy-mm-dd hh24:mi:ss') d4, --常用
(timestamp '2008-12-31 12:31:30.123') d5 --有毫秒数使用这个
FROM dual;
日期或数字转字符串
SELECT
TO_CHAR(12101.67,'9,999.9') T1, --格式的位数比实际的小返回########解析错误
TO_CHAR(12101.67,'999,999.9') T2, -- 12,101.7 四舍五入保留一位
TO_CHAR(12101.67,'999,999.99') T3, -- 12,101.67
TO_CHAR(123456789.67,'$999,999,999.99') T4 -- $123,456,789.67
FROM dual
SELECT
sysdate,
to_char(sysdate,'d') T1, --每周第几天
to_char(sysdate,'dd') T2, --每月第几天
to_char(sysdate,'ddd') T3, --每年第几天
to_char(sysdate,'ww') T4, --每年第几周
to_char(sysdate,'mm') T5, --每年第几月
to_char(sysdate,'q') T6, --每年第几季
to_char(sysdate,'yyyy') 年,-- 年
to_char(sysdate,'mm') 月,-- 年
to_char(sysdate,'dd') 日,-- 年
to_char(sysdate,'hh24') 时,-- 年
TO_CHAR(SYSTIMESTAMP ,'YYYY-MM-DD HH24:MI:SS.FF3') T11--2022-03-02 10:43:00.852
FROM dual
字符串转数字
select TO_NUMBER('199912'),TO_NUMBER('450.05') from dual;
进制转换
RAWTOHEX(c1) 【功能】将一个二进制构成的字符串转换为十六进制
HEXTORAW(c1)【功能】将一个十六进制构成的字符串转换为二进制
select
HEXTORAW('A123') ,
RAWTOHEX('A123')
from dual;
其他函数
decode条件取值
decode(条件,值1,翻译值1,值2,翻译值2,...值n,翻译值n,缺省值)
【功能】根据条件返回相应值
【参数】c1, c2, ...,cn,字符型/数值型/日期型，必须类型相同或null
注：值1……n 不能为条件表达式,这种情况只能用case when then end解决
·含义解释：
decode(条件,值1,翻译值1,值2,翻译值2,...值n,翻译值n,缺省值)
该函数的含义如下：
IF 条件=值1 THEN
RETURN(翻译值1)
ELSIF 条件=值2 THEN
RETURN(翻译值2)
......
ELSIF 条件=值n THEN
RETURN(翻译值n)
ELSE
RETURN(缺省值)
END IF
或：
when case 条件=值1 THEN
RETURN(翻译值1)
ElseCase 条件=值2 THEN
RETURN(翻译值2)
......
ElseCase 条件=值n THEN
RETURN(翻译值n)
ELSE
RETURN(缺省值)
END
PRODUCTINFO中产品数量多于100的就显示“充足”，少于或等于100则显示“不足”。脚本如下：
SELECT PRODUCTNAME,QUANTITY,
DECODE(SIGN(QUANTITY-100),1,'充足',-1,'不足',0,'不足') FROM PRODUCTINFO
实现行转列
create table SALE
(
month CHAR(6),
sell NUMBER(10,2)
)
tablespace USERS
pctfree 10
initrans 1
maxtrans 255
storage
(
initial 64K
minextents 1
maxextents unlimited
);
INSERT ALL
INTO sale(MONTH,sell) VALUES('200001',10000)
INTO sale(MONTH,sell) VALUES('200002',10001)
INTO sale(MONTH,sell) VALUES('200003',10002)
INTO sale(MONTH,sell) VALUES('200004',10003)
INTO sale(MONTH,sell) VALUES('200005',10004)
INTO sale(MONTH,sell) VALUES('200006',10005)
INTO sale(MONTH,sell) VALUES('200007',10006)
INTO sale(MONTH,sell) VALUES('200101',11000)
INTO sale(MONTH,sell) VALUES('200202',12000)
INTO sale(MONTH,sell) VALUES('200301',13000)
select 1 from dual;
--SELECT * FROM sale
-- 利用decode条件取值
SELECT
substr(month,1,4) "年",
sum(decode(substr(month,5,2),'01',sell,0)) "1月",
sum(decode(substr(month,5,2),'02',sell,0)) "2月",
sum(decode(substr(month,5,2),'03',sell,0)) "3月",
sum(decode(substr(month,5,2),'04',sell,0)) "4月",
sum(decode(substr(month,5,2),'05',sell,0)) "5月",
sum(decode(substr(month,5,2),'06',sell,0)) "6月",
sum(decode(substr(month,5,2),'07',sell,0)) "7月",
sum(decode(substr(month,5,2),'08',sell,0)) "8月",
sum(decode(substr(month,5,2),'09',sell,0)) "9月",
sum(decode(substr(month,5,2),'10',sell,0)) "10月",
sum(decode(substr(month,5,2),'11',sell,0)) "11月",
sum(decode(substr(month,5,2),'12',sell,0)) "12月"
from sale
group by substr(month,1,4);
--需要创建一个view
create or replace view
v_sale(year,month1,month2,month3,month4,month5,month6,
month7,month8,month9,month10,month11,month12)
as
SELECT
substrb(month,1,4),
sum(decode(substrb(month,5,2),'01',sell,0)),
sum(decode(substrb(month,5,2),'02',sell,0)),
sum(decode(substrb(month,5,2),'03',sell,0)),
sum(decode(substrb(month,5,2),'04',sell,0)),
sum(decode(substrb(month,5,2),'05',sell,0)),
sum(decode(substrb(month,5,2),'06',sell,0)),
sum(decode(substrb(month,5,2),'07',sell,0)),
sum(decode(substrb(month,5,2),'08',sell,0)),
sum(decode(substrb(month,5,2),'09',sell,0)),
sum(decode(substrb(month,5,2),'10',sell,0)),
sum(decode(substrb(month,5,2),'11',sell,0)),
sum(decode(substrb(month,5,2),'12',sell,0))
from sale
group by substrb(month,1,4);
--SELECT * FROM v_sale
NVL (expr1, expr2)
空值处理函数，【功能】若expr1为NULL，返回expr2；expr1不为NULL，返回expr1。
注意两者的类型要一致 ，注意空格
NVL(TC_CAH04,' ')!=' '
NVL2 (expr1, expr2, expr3)
【功能】expr1不为NULL，返回expr2；expr2为NULL，返回expr3。
expr2和expr3类型不同的话，expr3会转换为expr2的类型
NULLIF (expr1, expr2)
【功能】expr1和expr2相等返回NULL，不相等返回expr1
case...when
SELECT xqn,
CASE
WHEN xqn = 1 THEN '星期一'
WHEN xqn = 2 THEN '星期二'
WHEN xqn = 3 THEN '星期三'
else '星期三以后'
END 星期
FROM xqb
SELECT xqn,
CASE xqn
WHEN 1 THEN '星期一'
WHEN 2 THEN '星期二'
WHEN 3 THEN '星期三'
else '星期三以后'
END 星期
FROM xqb
返回当前行号
SELECT *,ROWNUM FROM dept; --错误
SELECT d.*,ROWNUM FROM dept d;--正确
获取guid
SYS_GUID 以16位RAW类型值形式返回一个全局唯一的标识符
hextoraw()：十六进制字符串转换为raw；
rawtohex()：将raw串转换为十六进制；
select
sys_guid(),
rawtohex(sys_guid()) --正确
from dual;
登陆用户与ID
SELECT USER,UID FROM dual;
COALESCE(c1, c2, ...,cn)
COALESCE(c1, c2, ...,cn)
【功能】返回列表中第一个非空的表达式，如果所有表达式都为空值则返回1个空值
【参数】c1, c2, ...,cn,字符型/数值型/日期型，必须类型相同或null
【返回】同参数类型
【说明】从Oracle 9i版开始，COALESCE函数在很多情况下就成为替代CASE语句的一条捷径
select COALESCE(null,3*5,44) hz from dual; --返回15
select COALESCE(0,3*5,44) hz from dual; --返回0
select COALESCE(null,'','AAA') hz from dual; --返回AAA
select COALESCE('','AAA') hz from dual; --返回AAA
greatest&least
【功能】返回表达式列表中值最大/最小的一个。如果表达式类型不同，会隐含转换为第一个表达式类型。
【参数】exp1……n，各类型表达式
【返回】exp1类型
SELECT
least(10, 32, '123', '2006'), --10
least('kdnf', 'dfd', 'a', '206'), --206
greatest(10, 32, '123', '2006'), --2006
greatest('kdnf', 'dfd', 'a', '206') --kdnf
FROM dual;
取得Internet中的主机名和IP地址
--如果查询失败，则提示系统错误
--查询www.qq.com的IP地址
select UTL_INADDR.get_host_address('www.qq.com') from dual;
--查询本机IP地址
select UTL_INADDR.get_host_address() from dual;
--查询局域网内yuechu的IP地址
select UTL_INADDR.get_host_address('yuechu') from dual;
--UTL_INADDR.get_host_name返回环境中主机名
--返回本机主机名
select UTL_INADDR.get_host_name() from dual;
--返回局域网内指定IP地址的主机名
select UTL_INADDR.get_host_name('192.168.0.156') from dual;
--返回intrenet中指定IP地址的网址
select UTL_INADDR.get_host_name('109.244.236.65') from dual;
浙公网安备 33010602011771号