oracle 对象管理 05_系统函数

最新推荐文章于 2025-08-05 17:20:40 发布

weixin_33958585

最新推荐文章于 2025-08-05 17:20:40 发布

阅读量141

点赞数

CC 4.0 BY-SA版权

文章标签： python 数据库

原文链接：https://my.oschina.net/peakfang/blog/2245336

本文全面介绍了SQL中的各种函数，包括字符串处理、数学运算、日期操作、聚合分析及正则表达式等，提供了丰富的示例帮助理解每个函数的具体用法。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

2019独角兽企业重金招聘Python工程师标准>>>

一、简单函数
1.lower():转为小写
select lower('ABCdef') from dual;
2.upper():转大写
select upper('Ttttttt') from dual;
3.initcap(x) 单词首字母转大写
select INITCAP('fanghuafeng') FROM dual;
4.length():字符长度
select length(ename) leng ,ename from scott.emp;
5.lengthb():字节长度，一个汉字占两字节，用于判断是否含有中文
select length('国家'),lengthb('国家') from dual;
6.VSIZE(c) 返回c的字节数。
select length('国家'),lengthb('国家),vsize('国家') from dual;
7.mod(a,b):取模,正负取被除数的符号
select mod(5,-2),mod(5,0),mod(-5,2) from dual;1
8.ROUND(a,n):对a从n位开始四舍五入,默认为取整
select ROUND(23.56),ROUND(23.56,1),ROUND(23.56,-1),ROUND(23.56,-2) FROM DUAL;
9.TRUNC(a,n):对a从n位开始截取小数点右边直接截取，左边截取后给0,默认从小数点前截取
select TRUNC(23.56),TRUNC(23.56,1),TRUNC(23.56,-1) FROM DUAL;
10.CEIL(n) 返回大于或等于n的最小的整数值
select ceil(3.16),ceil(-3.16) from dual;--4,-3
11.FLOOR(n) 返回小于等于n的最大整数值
select floor(3.76),floor(-3.16) from dual; --3,-4
12.GREATEST(n1,n2,...n) 返回序列中的最大值
select GREATEST(15,5,75,8) "Greatest",greatest('方','华','峰') FROM DUAL;
13.LEAST(n1,n2,n3..n)返回最小值
select LEAST(15,5,75,8) LEAST,least('方','华','峰') FROM DUAL;
14.随机函数dbms_random，返回随机数和随机字符串
小树点后38位长度的随机数，默认为0到1之间，可以指定范围
select dbms_random.value(),dbms_random.value(10,20) FROM dual;
   返回指定格式的随机字符串string('parameter',length)，字母：'a'不分大小写'u'大写'l'小写'x'大写加数字'p'所有字符
   select dbms_random.string('p',5),dbms_random.string('a',5),dbms_random.string('x',5) from dual;
15.trim函数
select trim(' aaaa ') aaa,ltrim(',aaabcd',',') bb from dual;
16.LPAD(c1,n[,c2]):n为负值为空，给c1左边补c2使得字符串长等于n,默认补空格,有截取子串的功能
select LPAD('WhaT',5),LPAD('WhaT',25),LPAD('WhaT',25,'-'),LPAD('WhaT',-4,'-') FROM DUAL;
17.RPAD(c1,n[,c2]):右补
select RPAD('WhaT',5),RPAD('WhaT',25),RPAD('WhaT',25,'-'),LPAD('WhaT',-4,'-') FROM DUAL;
18.NULLIF(c1,c2):逻辑等价于：CASE WHEN c1 = c2 THEN NULL ELSE c1 END
select NULLIF('a','b'),NULLIF('a','a') FROM DUAL;
19.返回第一个不为空的值，如果所有的都为空，则返回NULL。
select COALESCE(null,'','1','a') from dual;
20.CAST(c as newtype) 将指定字串转换为指定类型，基本只对字符类型有效
select cast('1001' as number(5,1)) from dual; --1001
21.DUMP:返回存储信息
select dump('abcd') from dual; --Typ=96 Len=4: 97,98,99,100
22.查询本地语言，会话的SID
select userenv('language'),userenv('sid') from dual;
23.user 当前回话的用户
select user from dual;
24.define返回数据库信息，用户均可执行sqlplus命令
define
25.英文字符集下的逆序函数 REVERSE()
select reverse('abcd'),reverse('123456') from dual;

二、字符串函数
1.substr(str,n1,l) 将str从n1位置开始，截取l长度的子串
2.instr(str,str1,n1,n2)返回str1在str中从n1开始，第n2次出现的位置，可用于判断字符包含性等同于like
3.replace(str,str1,str2)字符串级别的替换，将str中额str1替换为str2
4.translate(str,str1,str2)字符级别的替换，对应不上的替换为空，str1,str2任何一个为null结果就为null
select translate('abcb3add5esa6d','_1234567890','_') from dual;--计算长度判断是否含有指定字符
5.反向输出
中文外逆序系统函数 REVERSE()
中文：
create or replace function my_Reverse(s varchar2)
return varchar2
is
my_Result varchar2(1000);
v_s varchar2(1000);
begin
v_s:=substr(s,1,1);
for i in 2..length(s) loop
v_s:=substr(s,i,1)||v_s;
end loop;
my_Result:=v_s;
return(my_Result);
end;

三、格式转换函数
1.to_char(date,'format'):时间格式化输出显示，to_char(number,'xxxx')10进制转为16进制
select to_char(sysdate,'yyyy-mm-dd hh24:mi:ss') from dual;
select to_char(15,'xxxx') FROM dual;
   alter session set nls_date_format='yyyy-mm-dd hh24:mi:ss';
   insert into emp_a values(7777,'FHF','IT',7566,'1989-01-23 15:30:20',30,null,null);
2.to_date(string,'format'):将字符串转化为日期型，受客户端影响不一定可以格式化
select to_date ('2014-10-03 9:51:38','yyyy-mm-dd hh:mi:ss') from dual;
select to_date ('2003-10-17 21:15:37','yyyy-mm-dd hh24:mi:ss') from dual;
3.TO_NUMBER(str,format)明确的字符转数字，16进制转为10进制
select TO_NUMBER('-100.00') FROM DUAL;
select to_number('19f','xxxx') FROM dual;
4.nvl(string1,string2):string1为null则替换显示string2
select t.*,nvl(comm,0) new_comm from scott.emp t;
5.nvl2(string1,string2,string3):string1为null则取string3否则取string2
select t.*,nvl2(comm,comm+100,0) new_comm from scott.emp t;
6.DECODE(exp,s1,r1,s2,r2..s,r[,def])
select decode('a2','a1','true1','a2','true2','default') from dual;
7.case函数
case col when value1 then value1_1
when value2 then value2_2
else value3_3
end;
8.bin_to_num 2进制转换为10进制:
select bin_to_num(1,0,0,1),bin_to_num(1,1,0,1) from dual;
9.10进制转化为2进制
CREATE OR REPLACE FUNCTION NUMBER_TO_BIT(V_NUM NUMBER) RETURN VARCHAR IS
V_RTN VARCHAR(8); --注意返回列长度
V_N1 NUMBER;
V_N2 NUMBER;
BEGIN
V_N1 := V_NUM;
LOOP
V_N2 := MOD(V_N1, 2);
V_N1 := ABS(TRUNC(V_N1 / 2));
V_RTN := TO_CHAR(V_N2) || V_RTN;
EXIT WHEN V_N1 = 0;
END LOOP;
--返回二进制长度
SELECT LPAD(V_RTN, 8, 0) INTO V_RTN FROM DUAL;
RETURN V_RTN;
END;

四、日期函数
1.ADD_MONTHS() 返回指定日期月份+n之后的值，n可以为任何整数。时间的月份计算，只改变月份，其他时间同当
前年的改变可以通过月份*12运算
select ADD_MONTHS(sysdate,1),ADD_MONTHS(sysdate,-12) FROM DUAL;
2.current_date:当前系统时间，设置会话的日期格式
alter session set nls_date_format='yyyy-mm-dd';
select SYSDATE,CURRENT_DATE FROM DUAL;--CURRENT_DATE比sysdate快1秒
3.LAST_DAY(d) 返回指定时间所在“月”的最后一天,时分秒对应为当前时间
select last_day(SYSDATE) FROM DUAL;
select last_day(add_months(sysdate,-1)) FROM DUAL;
4.next_day(d,n):返回指定日期后最近的星期n日期
select next_day(sysdate,1) from dual;--1..7代表周日..周六
select NEXT_DAY(SYSDATE,'星期四') FROM DUAL;
5.MONTHS_BETWEEN(d1,d2) 返回d1与d2间的月份差，视d1,d2的值大小，结果可正可负，当然也有可能为0,d1-d2
select months_between(sysdate,add_months(sysdate,-3)) from dual;
6.ROUND(d[,fmt]):year按月，month按日，ddd按上下半天，day按周四舍五入，不能为时分秒，判断时间区间
select round(sysdate,'year'),round(sysdate,'month'),round(sysdate,'ddd'),round(sysdate,'day') from dual;
7.trunc(d,format):yyyy,Iy,month,q,WW,day,hh,hh24，找开始时间
select trunc(to_date('20080702 08:05:21','YYYYMMDD HH24:MI:SS'),'YYYY') FROM dual; --当年第一天
select trunc(to_date('20080702 08:05:21','YYYYMMDD HH24:MI:SS'),'IY') FROM dual;--去年年底
select trunc(sysdate,'Q') FROM dual--季度第一天
select trunc(sysdate,'month') FROM dual--月份第一天
select trunc(sysdate,'WW') FROM dual--周的第一天，不是完整的周***********
8.EXTRACT(fmt FROM d)：提取日期中的特定部分
select sysdate,extract(year from sysdate) year,extract(month from sysdate) months,
extract(day from sysdate) day,extract(hour from systimestamp ) hour,extract(minute from systimestamp ) minute,
extract(second from systimestamp ) second from dual;
select sysdate,extract(year from to_date('2014-10-11 19:44:35','yyyy-mm-dd hh24:mi:ss')) year,
extract(month from to_date('2014-10-11 19:44:35','yyyy-mm-dd hh24:mi:ss')) months,
extract(day from to_date('2014-10-11 19:44:35','yyyy-mm-dd hh24:mi:ss')) day,
extract(hour from to_timestamp('2014-10-11 19:44:35','yyyy-mm-dd hh24:mi:ss') ) hour,
extract(minute from to_timestamp('2014-10-11 19:44:35','yyyy-mm-dd hh24:mi:ss') ) minute,
extract(second from systimestamp ) second from dual;
select to_char(SYSDATE,'yyyy'),to_char(SYSDATE,'mm'),to_char(SYSDATE,'dd') FROM dual;
9.获得两个时间的相差单位时间，获得小时，分钟，秒，毫秒通过时间换算即可；
select CEIL (SYSDATE-to_date('2014-10-30 14:20:50','yyyy-mm-dd hh24:mi:ss')) FROM dual;--1天
select SYSDATE+1 FROM dual;--sysdate+n运算单位是天

五、分析聚合函数
1.开窗函数dense_rank(),rank(),row_number() over(partition by col1 order by col2)分类排序必须要有order by字句
select empno,deptno,ename,sal, dense_rank() over(partition by deptno order by sal desc) num
from scott.emp;
select empno,deptno,ename,sal, rank() over (partition by deptno order by sal desc ) num
from scott.emp;
select empno,deptno, ename, sal, row_number() over (partition by deptno order by sal desc ) num
from scott.emp order by deptno;
2.聚合分析，可以给表中记录均带上聚合值，同时可以显示未分组的列
a.聚合函数
SELECT deptno, AVG(sal) FROM scott.emp GROUP BY deptno ORDER BY 1;
b.聚合分析函数
SELECT deptno,ename,job, AVG(sal) OVER(PARTITION BY deptno ) num FROM scott.emp ORDER BY deptno;
3.指定字段排序累计，相同字段值累计值相同
select empno,ename,sal,sum(sal) over(order by sal desc) from emp;
   select empno,ename,sal,sum(sal) over (order by deptno) from emp;

六、正则表达式
1.REGEXP_LIKE --与LIKE的功能相似
2.REGEXP_INSTR --与INSTR的功能相似
3.REGEXP_SUBSTR --与SUBSTR的功能相似
4.REGEXP_REPLACE --与REPLACE的功能相似

七、利用系统函数搭建测试数据
通过一条 SQL快速生成大量的测试数据
create table myTestTable as
select rownum as id,to_char(sysdate + rownum/24/3600, 'yyyy-mm-dd hh24:mi:ss') as inc_datetime,
trunc(dbms_random.value(0, 100)) as random_id,dbms_random.string('x', 20) random_string
from dual connect by level <= 100000;

转载于:https://my.oschina.net/peakfang/blog/2245336