oracle函数大全（word+chm双版本）

目录

oralce 函数大全..................................................................................................................................4

ABS...........................................................................................................................................15

ACOS........................................................................................................................................15

ADD_MONTHS.......................................................................................................................16

Ascii...........................................................................................................................................16

ASIN..........................................................................................................................................16

ATAN.........................................................................................................................................17

CONCAT...................................................................................................................................18

CONVERT(c,dset,sset).............................................................................................................18

COS...........................................................................................................................................19

COSH........................................................................................................................................19

COUNT.....................................................................................................................................19

CURRENT_DATE....................................................................................................................20

CURRENT_TIMESTAMP.......................................................................................................20

DBTIMEZONE.........................................................................................................................20

DECODE...................................................................................................................................20

DEREF......................................................................................................................................20

GREATEST...............................................................................................................................22

GROUPING..............................................................................................................................22

HEXTORAW............................................................................................................................22

INITCAP...................................................................................................................................22

INSTR(C1,C2,I,J).....................................................................................................................22

INSTRB.....................................................................................................................................23

LAST_DAY...............................................................................................................................23

LEAST.......................................................................................................................................23

LENGTH...................................................................................................................................24

LENGTHB................................................................................................................................24

MAKE_REF..............................................................................................................................26

MAX..........................................................................................................................................26

MIN...........................................................................................................................................26

MOD(n1,n2)..............................................................................................................................26

MONTHS_BETWEEN(date2,date1)........................................................................................26

NEW_TIME(date,'this','that')....................................................................................................27

NEXT_DAY(date,'day')............................................................................................................27

NEW_TIME..............................................................................................................................27

NLSSORT.................................................................................................................................28

NLS_CHARSET_DECL_LEN.................................................................................................28

POWER.....................................................................................................................................29

RAWTOHEX............................................................................................................................29

REF............................................................................................................................................29

REFTOHEX..............................................................................................................................29

REPLACE('string','s1','s2')........................................................................................................29

ROUND.....................................................................................................................................29

ROWID.....................................................................................................................................30

ROWIDTOCHAR.....................................................................................................................30

RPAD (粘贴字符).....................................................................................................................30

RTRIM.......................................................................................................................................31

SQRT.........................................................................................................................................32

STDDEV...................................................................................................................................32

SUBSTR(string,start,count)......................................................................................................33

SUBSTRB.................................................................................................................................33

SUM..........................................................................................................................................33

SYS_EXTRACT_UTC.............................................................................................................33

TAN...........................................................................................................................................35

TO_CHAR(date,'format')..........................................................................................................35

TO_DATE(string,'format')........................................................................................................38

TO_LOB....................................................................................................................................38

TO_MULTI_BYTE...................................................................................................................38

TO_NUMBER...........................................................................................................................38

TO_SINGLE_BYTE.................................................................................................................38

TO_TIMESTAMP.....................................................................................................................38

TO_TIMESTAMP_TZ..............................................................................................................39

TO_YMINTERVAL..................................................................................................................39

TRANSLATE(x,y,z)..................................................................................................................39

USEREVN................................................................................................................................41

UID............................................................................................................................................41

VSIZE........................................................................................................................................42

VARIANCE...............................................................................................................................42

常用技巧...........................................................................................................................................42

怎么把 select 出来的结果导到一个文本文件中？................................................................42

怎样估算 SQL 执行的 I/O 数 ?................................................................................................42

常用数据词典...................................................................................................................................43

DBA_.........................................................................................................................................44

Dba_extents.......................................................................................................................44

V$_视图....................................................................................................................................47

v$database.........................................................................................................................47

V$session..........................................................................................................................48

Oracle 优化........................................................................................................................................49

Like or...............................................................................................................................49

例：使用  函数

＞



使用  函数从字符串中提取子串

语法：

!"# 函数的语法如下：

!"#$$

变量定义如下：

$ 为字符列或字符串表达式

　　$ 为子串的起始位置

)*$

在字符串中查找模式

例：使用 +, 运算符

＞$-$&.//--

＞$*'

＞$0$-$1/0$-$$2

34430$-$$234$43

替换字符串的一部分

经常遇到的数据操纵任务是在特定的列中将数据由一种模式转换成另一种模

式。

变量定义如下：

$ 为字符表达式 

7$$$ 为已存在的字符串。

-$ 为用来替代的可选字符串。

例：使用 #561 函数

　　显示了在 1 表中如何使用 #561 来改变课程名称$：首先使

用查询显示当前课程名称，!506 语句中使用 #561 函数将 :+6#

改变成 0+1!+;，另一查询显示了 !506 语句的效果。

＞-1$<-$3:+6#3

=0+1!+;3

 左填充函数

使用 560 对字符串进行左填充。

语法：

560$-$

变量定义如下：

　　$ 为需左填充的字符串直接值或字符申列。

　　 为 560 返回的字符串长度。

　　-$ 为左填充到 $ 的字符串。

　　＞-).$

　　使用  函数人微言轻 560 函数的参数：