Oracle 系统函数的使用集合
目录Oracle 系统函数的使用集合decode
nal
user
to_char
to_date
to_number
sysdate
Oracle中的next_day(date,char)
Ltrim
Rtrim
trim
oracle中实现截取字符串:substr
查找字符串位置:instr
替换字符串:replace
字符函数
日期函数
转换函数
统计函数
其他函数
字符函数
Ltrim函数形式:ltrim(string)
函数说明:去掉指定字符串string左侧空格
举例:select ltrim(' aaa bbb ccc ') from dual;
运行结果:
aaa bbb ccc(左侧空格已经被删除)
进阶用法:
函数形式:ltrim(string1,string2)
函数说明:从字符串string1左侧开始去除与string2字符集合中单个字符匹配的字符,直到在string1中遇上某个字符,该字符不在string2字符集合中。
举例:
SQL> select ltrim('abadababa','ab') from dual;
dababa
Rtrim函数形式:rtrim(string)
函数说明:去掉指定字符串string右侧空格
举例:select rtrim(' aaa bbb ccc ') from dual;
运行结果:
aaa bbb ccc(右侧空格已经被删除)
进阶用法:
函数形式:rtrim(string1,string2)
函数说明:从字符串string1右侧开始去除与string2字符集合中单个字符匹配的字符,直到在string1中遇上某个字符,该字符不在string2字符集合中。
这段话读起来有点拗口,但是对于真正理解这两个函数的用法至关重要。oracle中的这两个函数中的第二个参数,可以是多个字符组成的“字符集合”,为什么说是“字符集合”而不是“字符串”(虽然严格意义上讲并没有字符集合这个概念),因为在以string2为基础对string1做字符去除的时候,string2并不是作为一个字符串那样的整体来与string1匹配的,而是单个单个字符的匹配。比如string1='abccba',string2='abc',如果string2作为一个整体来匹配,显然不管是用ltrim还是rtrim函数,对string1操作之后返回的结果应该是'cba',但实际上不是,返回的是一个空串!
举例:
SQL> select rtrim('abadababa','ab') from dual;
abad
trim函数形式:trim(string)
函数说明:去掉指定字符串string两侧空格
举例:select trim(' aaa bbb ccc ') from dual;
运行结果:
aaa bbb ccc(两侧空格已经被删除)
进阶用法:
函数形式:trim( leading | trailing | both string1 FROM string2)
函数说明:从string2中去除左侧 | 右侧 | 左侧两侧(默认是both,即左右侧都去掉)的string1字符,注意,string1只能是单个字符。
举例:
SQL> select trim(leading 'abc' from ' a aaaa bb cc aa caa cca a ') from dual;
select trim(leading 'abc' from ' a aaaa bb cc aa caa cca a ') from dual
*
ERROR at line 1:
ORA-30001: trim set should have only one character
SQL> select trim(leading ' ' from ' a aaaa bb cc aa caa cca a ') from dual;
a aaaa bb cc aa caa cca a
SQL> select trim(trailing ' ' from ' a aaaa bb cc aa caa cca a ') from dual;
TRIM(TRAILING''FROM'AAAAABBCCAA
a aaaa bb cc aa caa cca a
SQL> select trim(bith ' ' from ' a aaaa bb cc aa caa cca a ') from dual;
select trim(bith ' ' from ' a aaaa bb cc aa caa cca a ') from dual
*
ERROR at line 1:
ORA-00907: missing right parenthesis
SQL> select trim(both ' ' from ' a aaaa bb cc aa caa cca a ') from dual;
TRIM(BOTH''FROM'AAAAABBCCA
a aaaa bb cc aa caa cca a
oracle中实现截取字符串:substr函数形式:substr(string, start_position, [length])
函数说明:string是元字符串,start_position为开始位置,length是可选项,表示子字符串的位数。
举例:
substr('ABCDEFG', 0); -- 返回结果是:ABCDEFG,从0位开始截取后面所有
substr('ABCDEFG', 2); -- 返回结果是:BCDEFG,从2位开始截取后面所有
substr('ABCDEFG', 0, 3); -- 返回结果是:ABC,从0位开始往后截取3个字符长度
substr('ABCDEFG', 0, 100); -- 返回结果是:ABCDEFG,虽然100超出了元字符串长度,但是系统会按元字符串最大数量返回,不会影响返回结果
substr('ABCDEFG', -3); -- 返回结果是:EFG,如果是负数,则从尾部往前数,截取-3位置往后的所有字符串
查找字符串位置:instr函数形式:instr(string, subString, [start_position, [nth_appearance]])
函数说明:其中,string是元字符串;subString是要查找的子字符串;start_position是要查找的开始位置,为可选项(默认为1),注意在这里字符串索引从1开始,如果此参数为正,则从左到右检索,如果此参数为负,则从右到左检索;nth_appearance是元字符串中第几次出现的子字符串,此参数可选,缺省默认为1,如果是负数则系统报错。
举例:
instr('ABCDABCDAEF', 'AB'); -- 返回结果是:1,因为instr字符串索引从1开始,所以是1不是0
instr('ABCDABCDAEF', 'DA', 1, 2); -- 返回结果是:8,返回第二次出现'DA'的位置
instr('A BCDABCDAEF', 'DA', 1, 2); -- 返回结果是:9,由于我在元字符串中加了一个空格,空格仍然算一个字符
替换字符串:replace函数形式:replace(str1, str2, str3)
函数说明:在str1中查找str2,凡是出现str2的地方,都替换成str3。
举例:
replace('ABCDEFG', 'CDE', 'cde'); -- 返回结果是:ABcdeFG
replace('ABCDEFG', 'CDE', 'cde'); -- 返回结果是:ABcdeFG
replace('ABCDEFG', 'CDE', ''); -- 返回结果是:ABFG,CDE被替换成空字符
replace('ABCDEFG', 'CDE'); -- 返回结果是:ABFG,当不存在第三个参数时,CDE直接被删掉
日期函数
sysdate函数形式:select sysdate from dual;
函数说明:sysdate函数可以得到目前系统的时间
用法举例:
select sysdate from dual; 返回系统时间
select sysdate,add_months(sysdate,12) from dual; --加1年
select sysdate,add_months(sysdate,1) from dual; --加1月
select to_char(sysdate,'yyyy/mm/dd/day') from dual; to_char函数的另一种用法
select sysdate,to_char(sysdate+7,'yyyy-mm-dd HH24:MI:SS') from dual; --加1星期
select sysdate,to_char(sysdate+1,'yyyy-mm-dd HH24:MI:SS') from dual; --加1天
select sysdate,to_char(sysdate+1/24,'yyyy-mm-dd HH24:MI:SS') from dual; --加1小时
select sysdate,to_char(sysdate+1/24/60,'yyyy-mm-dd HH24:MI:SS') from dual; --加1分钟
select sysdate,to_char(sysdate+1/24/60/60,'yyyy-mm-dd HH24:MI:SS') from dual; --加1秒
Oracle中的next_day(date,char)函数样式:next_day(date,char)
函数说明:它是指指定日期date之后的下一个char天后的日期
当char是星期时:它是指下一周星期几(char)所指示的日期
当char是数字时:他是指从这一周末开始的往后char天后的日期。周末算第一天
转换函数
to_char函数形式:to_char (number, '格式')
函数说明:使用to_char函数处理数字
to_char(salary, $99,999.99);
函数形式:to_char (date, '格式')
函数说明:使用to_char函数处理日期
to_char(date, $99,999.99);
数字格式格式:
9 代表一个数字
0 强制显示0
$ 放置一个$符
L 放置一个浮动本地货币符
. 显示小数点
, 显示千位指示符
to_date函数形式:to_date(char,'格式')
函数说明:to_data将字符转换为格式的日期日期格式
格式控制描述:
YYYY、YYY、YY 分别代表4位、3位、2位的数字年
YEAR 年的拼写
MM 数字月
MONTH 月的全拼
MON 月的缩写
DD 数字日
DAY 星期的全拼
DY 星期的缩写
AM 表示上午或者下午
HH24、HH12 12小时制或24小时制
MI 分钟
SS 秒钟
SP 数字的拼写
TH 数字的序数词
to_number函数形式:to_number(char,'格式');
使用to_number函数将字符转换为数字
更多的格式控制
统计函数函数形式:select avg|count|max|min|sum(column) from tablename;
函数说明:column是列名称,tablename是表格名称avg : 求某一列的平均值
count:求表tablename的行数
max:求column列中最大的数值
min:求column列中最小的数值
sum:求column列数值的和
举例:
其他函数
decode函数形式:decode(条件,值1,翻译值1,值2,翻译值2,...,缺省值)
函数说明: 该函数与程序中的 If...else if...else 意义一样,
select decode(列名,值1,表达式1,值2,表达式2,......,值n,表达式n) from table
列名就是table中某一列的名称,在这里将充当条件,即:如果某一行的该列的字段值等于值1则返回表达式1的值,等于值2则返回表达式2的值。
nal函数格式:nal( string1, replace_with)
函数功能:如果string1为NULL,则NVL函数返回replace_with的值,否则返回string1的值,如果两个参数都为NULL ,则返回NULL。
user用户创建
必须是sysdba的身份才可以创建 create user 用户名 identified by 密码;
查看当前用户
show user
改用户密码
alter user 用户名 identified by 密码;
用新用户连接数据库
必须给新用户授权能够连接数据库会话的权限
grant create session to 用户名;
删除用户
先必须用sysdba身份登录数据库
drop user 用户名;