Oracle六个单行函数

合集下载

ORACLE_分析函数大全

ORACLE_分析函数大全Oracle分析函数是一种高级SQL函数，它可以在查询中实现一系列复杂的分析操作。

这些函数可以帮助我们在数据库中执行各种数据分析和报表生成任务。

本文将介绍Oracle数据库中的一些常用分析函数。

1.ROW_NUMBER函数：该函数为查询结果中的每一行分配一个唯一的数字。

可以用它对结果进行排序或分组。

例如，可以使用ROW_NUMBER函数在结果集中为每个员工计算唯一的编号。

2.RANK和DENSE_RANK函数：这两个函数用于计算结果集中每个行的排名。

RANK函数返回相同值的行具有相同的排名，并且下一个排名值将被跳过。

DENSE_RANK函数类似，但是下一个排名值不会被跳过。

G和LEAD函数：LAG函数返回结果集中指定列的前一个（上一个）行的值，而LEAD函数返回后一个（下一个）行的值。

这些函数通常用于计算增长率或发现趋势。

4.FIRST和LAST函数：这两个函数用于返回结果集中分组的第一个和最后一个行的值。

可以与GROUPBY子句一起使用。

5.CUME_DIST函数：该函数用于计算给定值的累积分布。

它返回值的累积分布在结果集中的位置（百分比）。

6.PERCENT_RANK函数：该函数用于计算结果集中每个行的百分位数排名。

它返回值的百分位数排名（0到1之间的小数）。

7. NTILE函数：该函数用于将结果集分成指定数量的桶（Bucket），并为每个行分配一个桶号。

通常用于将数据分组为更小的块。

8.LISTAGG函数：该函数将指定列的值连接成一个字符串，并使用指定的分隔符分隔每个值。

可以用它将多个值合并在一起形成一个字符串。

9.AVG、SUM、COUNT和MAX/MIN函数：这些是常见的聚合函数，可以在分析函数中使用。

它们用于计算结果集中的平均值、总和、计数和最大/最小值。

以上只是Oracle数据库中的一些常用分析函数。

还有其他一些分析函数，如PERCENTILE_CONT、PERCENTILE_DISC等可以用于更高级的分析计算。

Orale数据库必备知识

Oracle 教程记录：1·OracleOraDb10/11g_home1TNSListener:表示监听服务，如果客户端想连接数据库，次服务必须打开，在日后开发中次服务起作用。

2·OracleServeiceMLDN,表示数据库主服务，命名规则：OracleService数据库名称。

次服务必须启动。

负责Oracle根本无法使用。

3·直接通过命令行方式进入SQLPlus的控制中心，进行命令的输入。

4·也可以通过窗口的形式启动命令行工具，及sqlplusw工具。

5·查询、修改、插入、删除等语句必须以分号结束语句及“; ”。

6·进入环境以后首先进行环境设置：<1>设置每行显示的长度：set linesize 长度值；<2>设置每页显示记录的长度:set pagesize 行数值；7·在sqlplusw中因为没法修改输入内容，所以一般会使用本机的记事本程序进行编辑，直接在命令窗口输入“ed 文件名即可”，输入完成之后，会提示用户是否要创建文件，编辑完成以后可以通过@文件名的方式执行命令，除了可以直接简历文件以外也可以通过@找到磁盘上的文件如“@ d:\demo·txt”效果是一样的，如果后缀名是“*·sql”,在查找是可以不注入后缀，系统默认的是“·sql”文件。

8·在sqlplusw/sqlplus中可以使用其他用户连接·sys 是超级管理员·system 是普通管理员·scott 是普通用户*连接格式：conn 用户名/密码[as sysdba|sysoper]·如果现在连接的是超级管理员(sys),则在连接的最后必须写上AS SYSDBA。

以系统管理员的身份进行登陆。

例如：conn sys/xuli564335 as sysdba;注意：如果连接用户下查询某一个用户下表而其连接用户没用此表则必须加上该表所在的用户名，格式：”用户名·表名“9·显示当前连接的用户是那个：show user;10·查看标的完整结构：desc 表名称;11·SQL可以分为以下几组：DML:数据操作语言——用户检索或者修改数据。

oracle函数

Ø显示Blake的雇员编号、姓名和部门编号。
SQL> SELECT empno, ename, deptno
2 FROM
emp
3 WHERE ename = 'blake';
no rows selected
SQL> SELECT 2 FROM 3 WHERE
empno, ename, deptno emp ename = UPPER('blake');
学习改变命运,知识创造未来
oracle函数
数值型函数
使用ceil函数： SELECT ceil(2.35) FROM dual; 执行结果： CEIL(2.35) ------------3 说明：该函数求得大于等于2.35的最小整数，结果
为3。
学习改变命运,知识创造未来
oracle函数
数值型函数
--------------- -------------- -----------------
45.92
46
50
学习改变命运,知识创造未来
oracle函数
使用 TRUNC函数
SQL> SELECT TRUNC(45.923,2), TRUNC(45.923),
2
TRUNC(45.923,-1)
45.92
ØMOD: 取余数 MOD(1600, 300) 100
学习改变命运,知识创造未来
oracle函数
使用 ROUND 函数
SQL> SELECT ROUND(45.923,2), ROUND(45.923,0),
2
ROUND(45.923,-1)
3 FROM DUAL;

ORACLE统计函数大全

【一】、Oracle常用的统计函数Avg(x):求一组行中列x值的平均值count(x):求一组行中列x值的非空行数count(*):求一组行的总行数max(x):求一组行中列x值的最大值min(x):求一组行中列x值的最小值stddev(x):求一组行中列x值的标准差sum(x):求一组行中列x值的总和variance(x):求一组行中列x值的方差【二】、group by与统计函数使用上面介绍的函数时可以使用也可以不使用group by ,但在使用group by时，未在group by部分用到的列在select 部分出现时必须使用统计函数，如按角色统计平均年龄Select user_name,avg(age) from usersGroup by role_id; ×Select count(user_name),avg(age) from usersGroup by role_id√【三】、用having字句规定统计条件having 子句的作用类似于where子句，只不过where 子句针对单个行，而having子句针对的是统计结果，一般和统计的函数搭配使用。

Having子句后必须为前面select后面的子部分，或是group by 后面的字段select count(uer_name),avg(age) from users group by role_id having role_id>20; ×select count(uer_name),avg(age) from users group by role_id having avg(age)>20; √【四】其他oracle常用函数Decode(column1,value1,output1,value2,output2,…..)如果column1 有一个值为value1那么将会用output1 来代替当前值,如果column1 的值为value2 那么就用OUTPUT2 来代替当前值,如果column1 中哪两个值都不是,那么就会用OUTPUT3 来代替当前值Select decode(age,10,7,9,6,3),user_name from users;SQL中的单记录函数1.ASCII返回与指定的字符对应的十进制数;SQL> select ascii(’A’) A,ascii(’a’) a,ascii(’0’) zero,ascii(’ ’) space from dual;A A ZERO SPACE--------- --------- --------- ---------65 97 48 322.CHR给出整数,返回对应的字符;SQL> select chr(54740) zhao,chr(65) chr65 from dual;ZH C-- -赵 A3.CONCAT连接两个字符串;SQL> select concat(’010-’,’88888888’)||’转23’ 高乾竞电话from dual;高乾竞电话----------------************转234.INITCAP返回字符串并将字符串的第一个字母变为大写;SQL> select initcap(’smith’) upp from dual;UPP-----Smith5.INSTR(C1,C2,I,J)在一个字符串中搜索指定的字符,返回发现指定的字符的位置;C1 被搜索的字符串C2 希望搜索的字符串I 搜索的开始位置,默认为1J 出现的位置,默认为1SQL> select instr(’oracle traning’,’ra’,1,2) instring from dual;INSTRING---------96.LENGTH返回字符串的长度;SQL> select name,length(name),addr,length(addr),sal,length(to_char(sal)) from gao.nchar_tst; NAME LENGTH(NAME) ADDR LENGTH(ADDR) SAL LENGTH(TO_CHAR(SAL))------ ------------ ---------------- ------------ --------- --------------------高乾竞 3 北京市海锭区6 9999.99 77.LOWER返回字符串,并将所有的字符小写SQL> selec t lower(’AaBbCcDd’)AaBbCcDd from dual;AABBCCDDaabbccdd8.UPPER返回字符串,并将所有的字符大写SQL> select upper(’AaBbCcDd’) upper from dual;UPPER--------AABBCCDD9.RPAD和LPAD(粘贴字符)RPAD 在列的右边粘贴字符LPAD 在列的左边粘贴字符SQL> select lpad(rpad(’gao’,10,’*’),17,’*’)from dual;LPAD(RPAD(’GAO’,1-----------------*******gao*******不够字符则用*来填满10.LTRIM和RTRIMLTRIM 删除左边出现的字符串RTRIM 删除右边出现的字符串SQL> select ltrim(rtrim(’ gao qian jing ’,’ ’),’ ’) from dual;LTRIM(RTRIM(’-------------gao qian jing11.SUBSTR(string,start,count)取子字符串,从start开始,取count个SQL> select substr(’130****8888’,3,8)from dual;SUBSTR(’--------0888888812.REPLACE(’string’,’s1’,’s2’)string 希望被替换的字符或变量s1 被替换的字符串s2 要替换的字符串SQL> select replace(’he love you’,’he’,’i’) from d ual;REPLACE(’H----------i love you13.SOUNDEX返回一个与给定的字符串读音相同的字符串SQL> create table table1(xm varchar(8));SQL> insert into table1 values(’weather’);SQL> insert into table1 values(’wether’);SQL> insert into table1 values(’gao’);SQL> select xm from table1 where soundex(xm)=soundex(’weather’);--------weatherwether14.TRIM(’s’ from ’string’)LEADING 剪掉前面的字符TRAILING 剪掉后面的字符如果不指定,默认为空格符15.ABS返回指定值的绝对值SQL> select abs(100),abs(-100) from dual; ABS(100) ABS(-100)--------- ---------100 10016.ACOS给出反余弦的值SQL> select acos(-1) from dual;ACOS(-1)---------3.141592717.ASIN给出反正弦的值SQL> select asin(0.5) from dual;ASIN(0.5)---------.5235987818.ATAN返回一个数字的反正切值SQL> select atan(1) from dual;ATAN(1)---------.7853981619.CEIL返回大于或等于给出数字的最小整数SQL> select ceil(3.1415927) from dual; CEIL(3.1415927)---------------420.COS返回一个给定数字的余弦SQL> select cos(-3.1415927) from dual; COS(-3.1415927)----------------121.COSH返回一个数字反余弦值SQL> select cosh(20) from dual;COSH(20)---------24258259822.EXP返回一个数字e的n次方根SQL> select exp(2),exp(1) from dual;EXP(2) EXP(1)--------- ---------7.3890561 2.718281823.FLOOR对给定的数字取整数SQL> select floor(2345.67) from dual;FLOOR(2345.67)--------------234524.LN返回一个数字的对数值SQL> select ln(1),ln(2),ln(2.7182818) from dual;LN(1) LN(2) LN(2.7182818)--------- --------- -------------0 .69314718 .9999999925.LOG(n1,n2)返回一个以n1为底n2的对数SQL> select log(2,1),log(2,4) from dual;LOG(2,1) LOG(2,4)--------- ---------0 226.MOD(n1,n2)返回一个n1除以n2的余数SQL> select mod(10,3),mod(3,3),mod(2,3) from dual; MOD(10,3) MOD(3,3) MOD(2,3)--------- --------- ---------1 0 227.POWER返回n1的n2次方根SQL> select power(2,10),power(3,3) from dual; POWER(2,10) POWER(3,3)----------- ----------1024 2728.ROUND和TRUNC按照指定的精度进行舍入SQL> select round(55.5),round(-55.4),trunc(55.5),trunc(-55.5) from dual; ROUND(55.5) ROUND(-55.4) TRUNC(55.5) TRUNC(-55.5)----------- ------------ ----------- ------------56 -55 55 -5529.SIGN取数字n的符号,大于0返回1,小于0返回-1,等于0返回0SQL> select sign(123),sign(-100),sign(0) from dual;SIGN(123) SIGN(-100) SIGN(0)--------- ---------- ---------1 -1 030.SIN返回一个数字的正弦值SQL> select sin(1.57079) from dual;SIN(1.57079)------------131.SIGH返回双曲正弦的值SQL> select sin(20),sinh(20) from dual;SIN(20) SINH(20)--------- ---------.91294525 24258259832.SQRT返回数字n的根SQL> select sqrt(64),sqrt(10) from dual;SQRT(64) SQRT(10)--------- ---------8 3.162277733.TAN返回数字的正切值SQL> select tan(20),tan(10) from dual;TAN(20) TAN(10)--------- ---------2.2371609 .6483608334.TANH返回数字n的双曲正切值SQL> select tanh(20),tan(20) from dual;TANH(20) TAN(20)--------- ---------1 2.237160935.TRUNC按照指定的精度截取一个数SQL> select trunc(124.1666,-2) trunc1,trunc(124.16666,2) from dual;TRUNC1 TRUNC(124.16666,2)--------- ------------------100 124.1636.ADD_MONTHS增加或减去月份SQL> select to_char(add_months(to_date(’199912’,’yyyymm’),2),’yyyymm’) from dual;TO_CHA------200002SQL> s elect to_char(add_months(to_date(’199912’,’yyyymm’),-2),’yyyymm’) from dual;TO_CHA------199910ST_DAY返回日期的最后一天SQL> select to_char(sysdate,’yyyy.mm.dd’),to_char((sysdate)+1,’yyyy.mm.dd’) from dual;TO_CHAR(SY TO_CHAR((S---------- ----------2004.05.09 2004.05.10SQL> select last_day(sysdate) from dual;LAST_DAY(S----------31-5月-0438.MONTHS_BETWEEN(date2,date1)给出date2-date1的月份SQL> select months_between(’19-12月-1999’,’19-3月-1999’) mon_between from dual;MON_BETWEEN-----------9SQL>selectmonths_between(to_date(’2000.05.20’,’yyyy.mm.dd’),to_date(’2005.05.20’,’yyyy.mm.dd’)) mon_betw from dual; MON_BETW----------6039.NEW_TIME(date,’this’,’that’)给出在this时区=other时区的日期和时间SQL> select to_char(sysdate,’yyyy.mm.dd hh24:mi:ss’) bj_time,to_char(new_time2 (sysdate,’PDT’,’GMT’),’yyyy.mm.dd hh24:mi:ss’) los_angles from dual;BJ_TIME LOS_ANGLES------------------- -------------------2004.05.09 11:05:32 2004.05.09 18:05:32给出日期date和星期x之后计算下一个星期的日期S QL> select next_day(’18-5月-2001’,’星期五’) next_day from dual; NEXT_DAY----------25-5月-0141.SYSDATE用来得到系统的当前日期SQL> select to_char(sysdate,’dd-mm-yyyy day’) from dual;TO_CHAR(SYSDATE,’-----------------09-05-2004 星期日trunc(date,fmt)按照给出的要求将日期截断,如果fmt=’mi’表示保留分,截断秒SQL> select to_char(trunc(sysdate,’hh’),’yyyy.mm.dd hh24:mi:ss’) hh,2 to_char(trunc(sysdate,’mi’),’yyyy.mm.dd hh24:mi:ss’) hhmm from dual; HH HHMM------------------- -------------------2004.05.09 11:00:00 2004.05.09 11:17:0042.CHARTOROWID将字符数据类型转换为ROWID类型SQL> select rowid,rowidtochar(rowid),ename from scott.emp;ROWID ROWIDTOCHAR(ROWID) ENAME------------------ ------------------ ----------AAAAfKAACAAAAEqAAA AAAAfKAACAAAAEqAAA SMITH AAAAfKAACAAAAEqAAB AAAAfKAACAAAAEqAAB ALLEN AAAAfKAACAAAAEqAAC AAAAfKAACAAAAEqAAC WARD AAAAfKAACAAAAEqAAD AAAAfKAACAAAAEqAAD JONES43.CONVERT(c,dset,sset)将源字符串sset从一个语言字符集转换到另一个目的dset字符集SQL> select convert(’strutz’,’we8hp’,’f7dec’) "conversion" from dual; conver------strutz44.HEXTORAW将一个十六进制构成的字符串转换为二进制45.RAWTOHEXT将一个二进制构成的字符串转换为十六进制46.ROWIDTOCHAR将ROWID数据类型转换为字符类型47.TO_CHAR(date,’format’)SQL> select to_char(sysdate,’yyyy/mm/dd hh24:mi:ss’) from dual;TO_CHAR(SYSDATE,’YY-------------------2004/05/09 21:14:41将字符串转化为ORACLE中的一个日期49.TO_MULTI_BYTE将字符串中的单字节字符转化为多字节字符SQL> select to_multi_byte(’高’) from dual;TO--高50.TO_NUMBER将给出的字符转换为数字SQL> select to_number(’1999’) year from dual;YEAR---------199951.BFILENAME(dir,file)指定一个外部二进制文件SQL>insert into file_tb1 values(bfilename(’lob_dir1’,’image1.gif’));52.CONVERT(’x’,’desc’,’source’)将x字段或变量的源source转换为descSQL> select sid,serial#,username,decode(command,2 0,’none’,3 2,’insert’,4 3,5 ’select’,6 6,’update’,7 7,’delete’,8 8,’drop’,9 ’other’) cmd from v$session where type!=’background’;SID SERIAL# USERNAME CMD--------- --------- ------------------------------ ------1 1 none2 1 none3 1 none4 1 none5 1 none6 1 none7 1275 none8 1275 none9 20 GAO select10 40 GAO none53.DUMP(s,fmt,start,length)DUMP函数以fmt指定的内部数字格式返回一个VARCHAR2类型的值SQL> col global_name for a30SQL> col dump_string for a50SQL> set lin 200SQL> select global_name,dump(global_name,1017,8,5) dump_string from global_name; GLOBAL_NAME DUMP_STRING------------------------------ --------------------------------------------------ORACLE.WORLD Typ=1 Len=12 CharacterSet=ZHS16GBK: W,O,R,L,D54.EMPTY_BLOB()和EMPTY_CLOB()这两个函数都是用来对大数据类型字段进行初始化操作的函数55.GREATEST返回一组表达式中的最大值,即比较字符的编码大小.SQL> select greatest(’AA’,’AB’,’AC’) from dual;GR--ACSQL> select greatest(’啊’,’安’,’天’) from dual;GR--天56.LEAST返回一组表达式中的最小值SQL> select least(’啊’,’安’,’天’) from dual;LE--啊57.UID返回标识当前用户的唯一整数SQL> show userUSER 为"GAO"SQL> select username,user_id from dba_users where user_id=uid;USERNAME USER_ID------------------------------ ---------GAO 25ER返回当前用户的名字SQL> select user from dual;USER------------------------------GAOEREVN返回当前用户环境的信息,opt可以是:ENTRYID,SESSIONID,TERMINAL,ISDBA,LABLE,LANGUAGE,CLIENT_INFO,LANG,VSIZE ISDBA 查看当前用户是否是DBA如果是则返回trueSQL> select userenv(’isdba’) from dual;USEREN------FALSESQL> select userenv(’isdba’) from dual; USEREN------TRUESESSION返回会话标志SQL> select userenv(’sessionid’) from dual; USERENV(’SESSIONID’)--------------------152ENTRYID返回会话人口标志SQL> select userenv(’entryid’) from dual; USERENV(’ENTRYID’)------------------INSTANCE返回当前INSTANCE的标志SQL> select userenv(’instance’) from dual; USERENV(’INSTANCE’)-------------------1LANGUAGE返回当前环境变量SQL> select userenv(’language’) from dual; USERENV(’LANGUAGE’)---------------------------------------------------- SIMPLIFIED CHINESE_CHINA.ZHS16GBK LANG返回当前环境的语言的缩写SQL> select userenv(’lang’) from dual; USERENV(’LANG’)----------------------------------------------------ZHSTERMINAL返回用户的终端或机器的标志SQL> select userenv(’terminal’) from dual; USERENV(’TERMINA----------------GAOVSIZE(X)返回X的大小(字节)数SQL> select vsize(user),user from dual; VSIZE(USER) USER----------- ------------------------------6 SYSTEM60.AVG(DISTINCT|ALL)all表示对所有的值求平均值,distinct只对不同的值求平均值SQLWKS> create table table3(xm varchar(8),sal number(7,2));语句已处理。

oracle常用的分析函数

oracle常⽤的分析函数常⽤的分析函数如下所列:row_number() over(partition by ... order by ...)rank() over(partition by ... order by ...)dense_rank() over(partition by ... order by ...)count() over(partition by ... order by ...)max() over(partition by ... order by ...)min() over(partition by ... order by ...)sum() over(partition by ... order by ...)avg() over(partition by ... order by ...)first_value() over(partition by ... order by ...)last_value() over(partition by ... order by ...)lag() over(partition by ... order by ...)lead() over(partition by ... order by ...)⼀、Oracle分析函数简介：在⽇常的⽣产环境中，我们接触得⽐较多的是OLTP系统(即Online Transaction Process)，这些系统的特点是具备实时要求，或者⾄少说对响应的时间多长有⼀定的要求；其次这些系统的业务逻辑⼀般⽐较复杂，可能需要经过多次的运算。

⽐如我们经常接触到的电⼦商城。

在这些系统之外，还有⼀种称之为OLAP的系统(即Online Aanalyse Process)，这些系统⼀般⽤于系统决策使⽤。

通常和数据仓库、数据分析、数据挖掘等概念联系在⼀起。

这些系统的特点是数据量⼤，对实时响应的要求不⾼或者根本不关注这⽅⾯的要求，以查询、统计操作为主。

oracle函数用法

Oracle函数用法1.sql函数包括单行函数和多行函数，其中单行函数是指输入一行输出也是一行的函数；多行函数也被称为分组函数，它会根据输入的多行数据输出一个结果。

一数字函数1.CEIL(n):该函数用于返回大于等于数字n的最小整数，示例如下：2.FLOOR(n):该函数用于返回小于等于数字n的最大整数，示例如下：3.MOD(m,n):该函数用于取得两个数字相除后的余数，如果数字n 为0，则返回结果为m，示例如下：4.ROUND(n,[m]):该函数用于执行四舍五入运算，示例如下：如果省略m，则四舍五入至整数位:如果m是负数，则四舍五入到小数点前m位:如果m是正数，则四舍五入至小数点后m位:5.TRUNC(n,[m]):该函数用于截取数字，如果省略数字m，则将数字n的小数部分截去：如果数字m是正数，则将数字n截取至小数点后的第m位：如果数字m是负数，则将数字n截取至小数点的前m位：6.SIGN(n):该函数用于检测数字的正负，如果数字n小于0，则函数的返回值为-1；如果数字n等于0，则函数的返回值为0；如果数字n大于0，则函数的返回值为17.ABS(n):该函数用于返回数字n的绝对值，请看示例：8.SIN(n):该函数用于返回数字n(以弧度表示的角)的正弦值，示例如下：9.COS(n):该函数用于返回数字n(以弧度表示的角度值)的余弦值。

10.ASIN(n):该函数用于返回数字n的反正弦值，输入值的范围是-1～1,输出值的单位为弧度，示例如下：11.ACOS(n):该函数用于返回数字n的反余弦值，输入值的范围是-1～1,输出值的单位为弧度，示例如下：12.SINH(n):该函数用于返回数字n的双曲正弦值，示例如下：13.COSH(n):该函数用于返回数字n的双曲余弦值，示例如下：14.TAN(n):该函数用于返回数字n(以弧度表示的角)的正切值，示例如下：15.A TAN(n):该函数用于返回数字n的反正切值，输入值可以是任何数字，输出值的单位为弧度，示例如下：16.A TAN2(n,m):该函数用于返回数字n除以数字m的反正切值。

单行函数——精选推荐

单⾏函数所谓的单⾏函数指的就是完成某⼀具体功能的操作函数，例如：转⼤⼩写等。

⼀般⽽⾔，单⾏函数的格式：返回值函数名称(参数)。

单⾏函数分为以下⼏种：字符串函数、数值函数、⽇期函数、转换函数、通⽤函数。

1.字符串函数字符串函数是处理字符串数据的(对于字符串的数据有可能是从列上找到的，或者是直接设置的字符串常量)。

为了⽅便⽤户进⾏⼀些验证或者是⼀些不需要查询表的查询操作，专门提供了⼀个dual的虚拟表。

1.转⼤写⼩写操作　1)转⼤写SELECT UPPER('hello') FROM dual;　2).将所有姓名转⼩写SELECT LOWER(ename) FROM emp;⼀般在⼀些不区分数据⼤⼩写的情况都会同意的将所有的内容转成⼤写或⼩写的形式处理。

2.⾸字母⼤写其他字母⼩写　1)将所有的雇员姓名以⾸字母⼤写的形式保存SELECT INITCAP(ename) FROM emp;3.取得字符串的长度　1）基础操作SELECT LENGTH('ddffgg') FROM dual;　2）查询雇员姓名长度为5的全部雇员信息SELECT * FROM emp WHERE LENGTH(ename)=5;4.字符串截取　1）验证函数SQL> SELECT SUBSTR('helloworld',6) FROM dual;SUBSTR('HE----------worldSQL> SELECT SUBSTR('helloworld',0,5) FROM dual;SUBSTR('HE----------hello 在程序之中所有的字符串的⾸字母的索引都是0，但是在Oracle⾥⾯，所有的字符串的⾸字母索引都是1，如果设置的是0 ，那么它也会按照1的⽅式进⾏处理。

oracle常用函数使用大全_最新整理

oracle常用函数使用大全
单值函数在查询中返回单个值，可被应用到 select， where子句，start with以及 connect by 子句和having 子句。 (一).数值型函数 (Number Functions ) 数值型函数输入数字型参数并返回数值型的值。多数该类函数的返回值支持38位小数点，诸如：COS, COSH, EXP, LN, LOG, SIN, SINH, SQRT, TAN, and TANH 支持36位小数点。 ACOS, ASIN, ATAN, and ATAN2支持30位小
n1<0，则 oracle从右向左数确认起始位置例如： SELECT SUBSTR( 'What is this',-5,3) FROM DUAL;
n1>c1.len gth则返回空例如： SELECT SUBSTR( 'What is this',50,3) FROM DUAL; 然后再请你猜猜，如果 n2<1，会如何返回值呢
3、 TRUNC(n 1[,n2] 返回截尾到 n2位小数的n1的值，n2缺省设置为 0，当n2为缺省设置时会将n1 截尾为整数，如果 n2为负值，就截尾在小数点左边相应的位上。例如： SELECT TRUNC(2 3.56),TRU NC(23.56, 1),TRUNC (23.56,-1) FROM DUAL;
例如： SELECT REPLAC E('WWhhh hhaT is tHis w W','W','-') FROM
9、 SOUNDE X(c) 神奇的函数啊，该函数返回字符串参数的语音表示形式，对于比较一些读音相同，但是拼写不同的单词非常有用。计算语音的算法如下：

1、下载文档前请自行甄别文档内容的完整性，平台不提供额外的编辑、内容补充、找答案等附加服务。
2、"仅部分预览"的文档,不可在线预览部分如存在完整性等问题,可反馈申请退款(可完整预览的文档不适用该条件!)。
3、如文档侵犯您的权益，请联系客服反馈,我们会尽快为您处理(人工客服工作时间：9:00-18:30)。

1. 数字函数
ABS 取绝对值 POWER 乘方 LN 10为底数取0
SQRT 平方根 EXP e的n次乘方 LOG(m,n) m为底数n取0
数学运算函数:ACOS ATAN ATAN2 COS COSH SIGN SIN SINH TAN TANH
CEIL 大于或等于取整数
FLOOR 小于或等于取整数
MOD 取余数
ROUND(n,m) 按m的位数取四舍五入值如果round(日期): 中午12以后将是明天的日期. round(sysdate,'Y')是年的第一天
TRUNC(n,m) 按m的位数取小数点后的数值如果trunc(日期), 确省的是去掉时间
6
J2EE @ zxw
2. 字符函数
CHR 按数据库的字符集由数字返回字符
CONCAT(c1,c2) 把两个字符c1,c2组合成一个字符, 和 || 相同
REPLACE(c,s,r) 把字符c里出现s的字符替换成r, 返回新字符
SUBSTR(c,m,n) m大于0,字符c从前面m处开始取n位字符,m等于0和1一样,
m小与0,字符c从后面m处开始取n位字符
TRANSLATE(c,f1,t1) 字符c按f1到t1的规则转换成新的字符串
INITCAP 字符首字母大写,其它字符小写
LOWER 字符全部小写
UPPER 字符全部大写
LTRIM(c1,c2) 去掉字符c1左边出现的字符c2
RTRIM(c1,c2)
TRIM(c1,c2) 去掉字符c1左右两边的字符c2
LPAD(c1,n,c2) 字符c1按制定的位数n显示不足的位数用c2字符串替换左边的空位
RPAD(c1,n,c2)
3. 日期函数
ADD_MONTHS(d,n) 日期值加n月
LAST_DAY 返回当月的最后一天的日期
MONTHS_BETWEEN(d1,d2) 两个日期值间的月份,d1<d2 返回负数
NEXT_DAY 返回日期值下一天的日期
SYSDATE 当前的系统时间
DUAL是SYS用户下一个空表，它只有一个字段dummy
4. 转换函数(1)
TO_CHAR(date,'日期显示格式')
TO_CHAR(number) 用于显示或报表的格式对齐
TO_DATE(char,'日期显示格式')
TO_LOB 把long字段转换成lob字段
TO_NUMBER(char) 用于计算或者比较大小
转换函数(2)
to_date里日期显示格式
YYYY 年 YEAR YYY YY Y
Q 季度
MM 月 MONTH MON
W 星期 (week of month) WW, IW (week of year)
(说明：周计是按ISO标准,从1月1日的星期数到后面七天为一周,不一定是从周一到周日) DD 日 DAY DY
HH24 小时 HH12 HH
MI 分钟
SS 秒
如果想固定日期的显示格式可以在数据库的参数文件initorasid.ora里新写一行参数
NLS_DATE_FORMAT=yyyy-mm-dd
hh24:mi:ss可以在UNIX环境变量或者NT的注册表里的设置 NLS_DATE_FORMAT=yyyy-mm-dd 7
J2EE @ zxw
hh24:mi:ss
转换函数(3)
如果想固定日期的显示格式可以用alter session命令改变
SQL> alter session set nls_date_format='yyyy-mm-dd hh24:mi:ss';
它的作用顺序如下:
initialization parameter
Environment variable
ALTER SESSION command
转换函数 (4)
to_char(number)里数字显示格式
9 数字位
0 数字前面补0 to_char(-1200,'00000.00')
小数点的位置
标记位置的逗号用在数字显示格式的左边
L 根据数据库字符集加货币符号 to_char(-1200,'L9999.99')
B 把数字0显示为空格,用在数字显示格式的右边
MI 右边的负数标记 to_char(-1200,'9999.99MI')
PR 括起来的负数 to_char(-1200,'9999.99PR')
EEEE 用指数方式表示 to_char(-1200,'9999.99EEEE')
5. 输入字符,返回数字的函数
instr(c1,c2) 字符c2出现在c1的位置, 不出现, 返回0, 常用于模糊查询
length 按数据库的字符集,算出字符c的长度,跟数据库的字符集有关, 一个汉字长度为1
6. 有逻辑比较的函数NVL(EXPR1, EXPR2)函数
解释: IF EXPR1=NULL RETURN EXPR2
ELSE RETURN EXPR1
DECODE(AA0V10R10V20R2....)函数
解释: IF AA=V1 THEN RETURN R1
IF AA=V2 THEN RETURN R2
..…
ELSE
RETURN NULL
举例: decode(id,1,'dept sale',2,'dept tech')
文章来源：网络编辑：联动北方技术论坛
（如有侵权请及时联络以便删除）。