编程宝库 - 技术改变世界

编程宝库

Search

Oracle知识

Oracle递归查询connect by用法

Oracle执行Update语句的几种方式

Oracle中的函数详解

一、数值函数

1、mod(n1,n2):n1除以n2的余数。

如果n2为0，则返回n1。

select mod(23,8),mod(24,8) from dual;--返回：7,0

2、power(n1,n2):返回数字n1的n2次幂;
exp(y)：返回e的y次幂。（e为数学常量）;
log(x,y)：返回以x为底的y的对数；
ln(y)：返回e为底的自然对数。

select power(2.5,2),power(1.5,0),power(20,-1) from dual;

3、sqrt(n):平方根。

select sqrt(64),sqrt(10) from dual;--返回：8 , 3.16227766

4、ceil(n):返回大于等于n的最小整数。;

floor(n):返回小于等于n的最大整数。

select ceil(3.1),ceil(2.8+1.3),ceil(0) from dual;--返回4，5，0

5、sign(x)：返回x的正负值

若为正值返回1，负值返回-1，0返回0。

select sign(100),sign(-100),sign(0) from dual;

6、trunc(n[,len]):n截取到小数点len位。

len默认为0。len>0，截取到小数点右len位。len<0，截取到小数点左len位。

select trunc(5555.66666,2.1),trunc(5555.66666,-2.6),trunc(5555.033333)  from dual;--返回：5555.66  5500   5555

7、round(n[,len]):n四舍五入到小数点len位，规则同trunc。

select round(5555.6666,2.1),round(5555.6666,-2.6),round(5555.6666) from dual;--返回：   5555.67  ,5600 ,5556

8、sys.dbms.random.value()：产生0-1之间的随机数。

DBMS_RANDOM.VALUE()是随机产生( 0,1 )之间的数。
DBMS_RANDOM.VALUE(n1,n2)：产生n1-n2之间的随机数。

trunc(dbms_random.value(10,100))    //80：生成10-100之间的随机数。

二、字符函数

1：lower(c1):返回字符串,并将所有的字符小写

SELECT LOWER('AbcDedf Gbad') FROM DUAL;

2：upper(c1):返回字符串,并将所有的字符大写

SELECT UPPER('abcdEf') FROM DUAL;

3: initcap(c1):返回字符串并将字符串的第一个字母变为大写

全部单词的首字母大写

SELECT INITCAP('your didn''t try your best') FROM DUAL;

4: initcap(c1,n[,c2]):在列的左边填充字符

C1 字符串
n 追加后字符总长度
c2 追加字符串,默认为空格

SELECT LPAD('WELCOME', 20, 'HELLO') FROM DUAL;

5: rpad(c1,n[,c2]):在列的右边填充字符

注意长度值并不是粘贴字符的长度，而是整个字符串的长度，如果长度小于原始字符串

--SELECT RPAD('HELLO', 4, '*') FROM DUAL; 的值为HELL
SELECT RPAD('HELLO', 10, '*') FROM DUAL;
SELECT RPAD('HELLO', 10, 'E') FROM DUAL;

6: ltrim(X,[TRIM_STRING]):删除左边出现的字符串。

默认为空字符串

SELECT LTRIM('  hello world!') FROM DUAL;
SELECT LTRIM('hello, world', 'hello') FROM DUAL;

7: rtrim(X, [TRIM_STRING]):删除右边出现的字符串

TRIM_STRING,默认为空字符串。

SELECT RTRIM('hello world!    ') FROM DUAL;

8: trim('s' from 'string'):删除两边出现的字符串

LEADING 剪掉前面的字符
TRAILING 剪掉后面的字符
如果不指定,默认为空格符

SELECT TRIM('Y' FROM 'YOU') FROM DUAL;

9: instr(C1,C2,I,J):在一个字符串中搜索指定的字符,返回发现指定的字符的位置;

C1 被搜索的字符串
C2 希望搜索的字符串
I 搜索的开始位置,默认为1
J 出现的位置,默认为1

SELECT INSTR('HELLO WORLD! WELCOME', 'WORLD', 1)  FROM DUAL;

10：substr(string,start,count):取子字符串,从start开始,取count个

SELECT SUBSTR('you are right!, come on', 3, 30) FROM DUAL;

11：replace('string','s1','s2'):替换

string 希望被替换的字符或变量
s1 被替换的字符串
s2 要替换的字符串

SELECT REPLACE('HE LOVE YOU', 'HE' ,'I') FROM DUAL;

12：translate(c1,c2,c3)：将指定字符替换为新字符

c1 希望被替换的字符或变量
c2 查询原始的字符集
c3：替换新的字符集，将c2对应顺序字符，替换为c3对应顺序字符

select TRANSLATE('he love you','he','i'),
TRANSLATE('重庆的人','重庆的','上海男'),
TRANSLATE('重庆的人','重庆的重庆','北京男士们'),
TRANSLATE('重庆的人','重庆的重庆','1北京男士们'),
TRANSLATE('重庆的人','1重庆的重庆','北京男士们') from dual;
--i love you,上海男人,北京男人,1北京人,京男士人

13: length(c1)：返回字符串的长度;

返回表某条数据某个列实际长度，如果该表没有数据，返回0

SELECT LENGTH(TYPE_NAME) FROM USER_TYPES

14：ascii（x1)：返回字符串的ASCII值

SELECT ASCII('A') FROM DUAL;
SELECT ASCII('a') FROM DUAL;

15: chr(n1)：返回整数所对应的ASCII字符

SELECT CHR('65') FROM DUAL;
SELECT CHR(400) FROM DUAL;  --如果超出ACII值，则返回空

16: concat(c1,c2)：连接字符串A和字符串B

SELECT CONCAT('您好', '欢迎来到ORACLE世界') AS TEXT FROM DUAL;
--如果要连接表里面的两个字段可以用||
SELECT TYPECODE || '____' || TYPE_NAME AS "TYPE" FROM USER_TYPES;

三、日期时间函数

1：sysdate、current_date：系统的当前日期

（1）日期加上范围日期，得到新日期

data+n,加减n天。（n为负数，表示减去）
date+n/24:加减n小时
date+n/24/60:加减n分钟
date+n/24/60/3600:加减n秒

（2）date1-date2:两日期相差的天数：

(date1-date2)*24*3600:两日期相差的秒数

SELECT SYSDATE FROM DUAL;

2：add_months(date,n1)：增加或减去月份

SELECT TO_CHAR(ADD_MONTHS(TO_DATE('20080818','YYYYMMDD'),2), 'YYYY-MM-DD') FROM DUAL;
SELECT TO_CHAR(ADD_MONTHS(SYSDATE, -1), 'YYYY-MM-DD') FROM DUAL

3: months_between(date2,date1)：给出date2-date1的月份

SELECT MONTHS_BETWEEN(TO_DATE('2011-05-03', 'YYYY-MM-DD'), TO_DATE('2011-01-23', 'YYYY-MM-DD')) FROM DUAL;
SELECT MONTHS_BETWEEN('19-12月-1999','19-3月-1999') mon_between FROM DUAL;
--SELECT MONTHS_BETWEEN('2011-1月-23', '2011-9月-1') FROM DUAL;  文字与格式字符串不匹配

4: last_day(date)：返回日期的最后一天

SELECT LAST_DAY(SYSDATE) FROM DUAL;
SELECT LAST_DAY(ADD_MONTHS(SYSDATE, -2)) FROM DUAL;

5：next_day(date[,fmt]）:返回日期d1在下周，星期几(参数c1)的日期

星期日 = 1 星期一 = 2 星期二 = 3 星期三 = 4 星期四 = 5 星期五 = 6 星期六 = 7

SELECT NEXT_DAY(SYSDATE, 2) FROM DUAL;--下周星期一,

6、round(date[,fmt]）:日期时间四舍五入结果。

fmt默认是day.

7: trunc(date[,fmt]）:TRUNC函数为指定元素而截去的日期值。

TRUNC(TO_DATE('24-Nov-1999 08:00 pm'),'dd-mon-yyyy hh:mi am')　='24-Nov-1999 12:00:00 am'
TRUNC(TO_DATE('24-Nov-1999 08:37 pm','dd-mon-yyyy hh:mi am'),'hh') ='24-Nov-1999 08:00:00 am'

8：extract(c1 from date) :找出日期或间隔值的字段值

SELECT EXTRACT(MONTH FROM SYSDATE) "MONTH" FROM DUAL;
SELECT EXTRACT(DAY FROM SYSDATE)  AS "DAY" FROM DUAL;
SELECT EXTRACT(YEAR FROM SYSDATE) AS "YEAR" FROM DUAL;

9：new_time(date,'this','that'):给出在this时区=other时区的日期和时间

SELECT TO_CHAR(SYSDATE, 'YYYY.MM.DD HH24:MI:SS') BeiJing_Time,TO_CHAR(NEW_TIME(SYSDATE, 'PDT', 'GMT'), 'YYYY.MM.DD HH24:MI:SS') LOS_ANGELS FROM DUAL;

简写时区

AST OR ADT 大西洋标准时间
HST OR HDT 阿拉斯加—夏威夷时间
BST OR BDT 英国夏令时
MST OR MDT 美国山区时间
CST OR CDT 美国中央时区
NST 新大陆标准时间
EST OR EDT 美国东部时间
PST OR PDT 太平洋标准时间
GMT 格伦威治标准时间
YST OR YDT Yukon标准时间

10: dbtimezone() :返回时区

SELECT DBTIMEZONE FROM DUAL;

11: sessiontimezone:返回会话时区

其中DBTIMEZONE是数据库的，session是针对当前会话的，因为时区在会话级可以改变

SELECT SESSIONTIMEZONE FROM DUAL;  
ALTER SESSION SET TIME_ZONE = '8:00';
SELECT SESSIONTIMEZONE FROM DUAL;

12、常用时间查询：

----  上月最后一天
SELECT TO_CHAR(LAST_DAY(ADD_MONTHS(SYSDATE, -1)), 'YYYY/MM/DD') FROM DUAL;
----: 上各月的今天
SELECT TO_CHAR(ADD_MONTHS(SYSDATE, -1), 'YYYY-MM-DD') FROM DUAL;
---- 上个月第一天
SELECT TO_CHAR(ADD_MONTHS(LAST_DAY(SYSDATE)+1,-2),'YYYY-MM-DD') FirstDay FROM DUAL;
---  要找到某月中所有周五的具体日期 
SELECT TO_CHAR(T.D, 'YY-MM-DD')
FROM (SELECT TRUNC(SYSDATE, 'MM') + ROWNUM -1 AS D FROM DBA_OBJECTS WHERE ROWNUM < 32) T
WHERE TO_CHAR(T.D, 'MM') = TO_CHAR(SYSDATE, 'MM') AND TRIM(TO_CHAR(T.D, 'DAY')) = '星期五'

四、转换函数

1： to_char(date,'format') ：把对应的数据转换为字符串类型

TO_CHAR的fmt:格式字符串，不分大小写。

select to_char(sysdate,'yyyy-mm-dd hh24:mi;ss') from dual

Y或YY或YYY 年的最后一位，两位或三位
SYEAR或YEAR: SYEAR使公元前的年份前加一负号 --TWENTY ELEVEN
Q：季度，1～3月为第一季度 -- 2表示第二季度
MM：月份数 --04表示4月
RM：月份的罗马表示 --IV表示4月
MON：月份 --4月
Month：用9个字符长度表示的月份名 -- 4月
WW：当年第几周 -- 24表示2002年6月13日为第24周
W：本月第几周 -- 2011年04月26日为第4周
DDD：当年第几天. 1月1日为001，2月1日为032
DD：当月第几天
D：周内第几天
DY：周内第几天缩写
HH或HH12： 12进制小时数
HH24： 24小时制
MI：分钟数(0～59) ：提示注意不要将MM格式用于分钟(分钟应该使用MI)。MM是用于月份的格式，将它用于分钟也能工作，但结果是错误的。
SS：秒数(0～59)

SELECT TO_CHAR(SYSDATE, 'YYYY-MM-DD HH24:MI:SS') FROM DUAL;

2： to_char(n,'format')：把对应的数字为字符串类型

FORMAT格式符：

9：带有指定位数的值
0：前导零的值
. （句点）：小数点
, （逗号）：分组（千）分隔符
PR：尖括号内负值
S：带负号的负值（使用本地化）
L：货币符号（使用本地化）
D：小数点（使用本地化）
G：分组分隔符（使用本地化）
MI：在指明的位置的负号（如果数字 < 0）
PL：在指明的位置的正号（如果数字 > 0）
SG：在指明的位置的正/负号
RN：罗马数字（输入在 1 和 3999 之间）
TH or th：转换成序数

SELECT TO_CHAR(122323.45, '$99999999.99') FROM DUAL;

3： to_date(string,'format')：将字符串转化为日期

主要用于比较和修改日期。

SELECT TO_DATE('2011/03/24', 'YYYY-MM-DD') FROM DUAL;

4： to_number：将给出的字符转换为数字

SELECT TO_NUMBER('￥2008.00'，'L9999D99') AS Year FROM DUAL;

五、辅助函数

1、decode(条件,值1,翻译值1,值2,翻译值2,...值n,翻译值n,缺省值):根据条件返回相应值

值1……n 不能为条件表达式,这种情况只能用case when then end解决。

select decode(xqn,1,'星期一',2,'星期二',3,'星期三','星期三以后') 星期FROM xqb

2、greatest(exp1,exp2,exp3,……,expn):返回表达式列表中值最大的一个。 ; least(exp1,exp2,exp3,……,expn):返回表达式列表中值最小的一个。

如果表达式类型不同，会隐含转换为第一个表达式类型。

SELECT greatest(10,32,'123','2006') FROM dual;

3、nullif (expr1, expr2)：expr1和expr2相等返回NULL，不相等返回expr1。

SELECT NULLIF( 'a', 'b' ) ;--返回值 a
SELECT NULLIF( 'a', 'a');--返回 NULL

实际应用：

--添加函数查询结果，要求（将日期类型默认'0001/1/1',改成null,不相等，返回本身日期）--
select NULLIF(RECEIVEDATE,TO_DATE('0001/1/1','yyyy-mm-dd hh24:mi:ss')) 收货日期,NAME 单据名称 from tab

4、nvl (expr1, expr2):若expr1为NULL，返回expr2；expr1不为NULL，返回expr1。

select NVL(null,'未知') SexType from dual;--expr1为空，返回expr2，结果：'未知'
select NVL('1','2') SexType from dual;--expr1不为空，返回expr1， 结果：1

实际应用：

--加函数时情况，要求（field 为null，返回0 ；field 不为null,返回本身
select NVL(UseFlag,'0') 使用标志,NAME 单据名称 from tab

5、nvl2(expr1, expr2, expr3) :expr1不为NULL，返回expr2；expr2为NULL，返回expr3。

expr2和expr3类型不同的话，expr3会转换为expr2的类型

select NVL2(0,1,2) from dual;--不为null时，返回expr2 ，结果：1
select NVL2(null,1,2) from dual;--为null时，返回expr3 ，结果：2

实际应用：

--应用到实际查询中，要求（field 为null,返回0; field 不为null,返回本身）
select NVL2(UseFlag,UseFlag,'0') 使用标志,A.NAME 单据名称 from tab

6、coalesce(c1, c2, ...,cn)：返回列表中第一个非空的表达式，如果所有表达式都为空值则返回1个空值。

select COALESCE(null,3*5,44) hz from dual; --返回15

7、sys_context('USERENV',c2)：返回系统'USERENV'变量中c2对应的的值。

SYS_CONTEXT('USERENV','LANGUAGE') language,

8、sys_connect_by_path(column_name,'分隔符')：把一个父节点下的所有子节点通过某个字符进行区分，然后连接在一个列中显示

详见Oracle递归查询connect by

第一个参数是形成树形式的字段，第二个参数是父级和其子级分隔显示用的分隔符
伪列CONNECT_BY_ROOT,CONNECT_BY_LEAF,CONNECT_BY_ISCYCLE

结构化查询：START WITH ...CONNNECT BY PRIOR基本语法是：

SELECT ...FROM
WHERE （过滤返回记录，仅过滤被限定节点，其根节点和子节点均不受影响）
START WITH (根节点，可以指定多个节点)
CONNECT BY PRIOR= （连接条件，PRIOR置于等号前，则从根节点到叶节点开始检索；置于等号后，则从叶节点到根节点开始检索）

该查询访问路径如下：从根节点开始，向下扫描子节点，该子节点已被访问则转向其最左侧未被访问的子节点，否则判断该节点是否为根节点，是则访问完毕，否则返回父节点重新执行判断。

SELECT ename   FROM scott.emp    START WITH ename = 'KING'    CONNECT BY PRIOR empno = mgr;

--得到结果为：

KING
JONES
SCOTT
ADAMS
FORD
SMITH
BLAKE
ALLEN
WARD
MARTIN
TURNER
JAMES

SELECT SYS_CONNECT_BY_PATH(ename, </'>') "Path"    FROM scott.emp    START WITH ename = 'KING'    CONNECT BY PRIOR empno = mgr;

--得到结果为：

KING
KING>JONES
KING>JONES>SCOTT
KING>JONES>SCOTT>ADAMS
KING>JONES>FORD
KING>JONES>FORD>SMITH
KING>BLAKE
KING>BLAKE>ALLEN
KING>BLAKE>WARD
KING>BLAKE>MARTIN
KING>BLAKE>TURNER
KING>BLAKE>JAMES
KING>CLARK
KING>CLARK>MILLER

六、聚合函数

AVG(DISTINCT|ALL)：平均值，DISTINCT表示对不同的值求平均值,重复值的列的只取一次。

SELECT AVG(DISTINCT SAL) FROM SCOTT.EMP;

MAX(DISTINCT|ALL)：最大值
MIN(DISTINCT|ALL)：最小值
SUM(DISTINCT|ALL)：求和
COUNT(DISTINCT|ALL)：求记录数
wmsys.wm_concat(DISTINCT|ALL)：合并列。
将一列的多行记录合并到一列，用逗号隔开。例如表的有两个字段，要按airport_id合并成两行可用sql语句

select airport_id,   wmsys.wm_concat(distinct  account) from AIRPORT_MODIFY group by airport_id

七、分析函数

见专题：Oracle分析函数

关于Oracle函数的文章就介绍到这了。希望对大家的学习有所帮助，也希望大家多多支持编程宝库。

下一节：Oracle执行Update语句的几种方式

Oracle数据库

Oracle没有update from语法，可以通过四种写法实现同样的功能：一、标准update语法（常用、速度可能最慢）当更新的表示单个或者被更新的字段不需要关联表带过来，此法是最好 ...

Oracle递归查询connect by用法

Oracle执行Update语句的几种方式