十一,函数

文章目录[x]
  1. Oracle常用函数
  2. 数学函数
  3. USERENV



Oracle常用函数

字符函数
字符函数是oracle中最常用的函数
lower(char):将字符串转化为小写的格式。
upper(char):将字符串转化为大写的格式。

select lower(name) from student;

length(char):返回字符串的长度。

select name from student where length(name)=4;

substr(char, m, n):截取字符串的子串,n代表取n个字符的意思,不是代表取到第n个

select substr(name,1,3) from student;
SELECT UPPER(substr(NAME,1,1)) || LOWER(substr(NAME,2,LENGTH(NAME)-1)) FROM student; 
第一个字母大写显示

replace(char1, search_string, replace_string):替换字符显示

select replace(name,'a','@') from student;

instr(C1,C2,I,J) –>判断某字符或字符串是否存在,存在返回出现的位置的索引,否则返回小于1;在一个字符串中搜索指定的字符,返回发现指定的字符的位置;
C1 被搜索的字符串
C2 希望搜索的字符串
I 搜索的开始位置,默认为1
J 出现的位置,默认为1

数学函数

数学函数的输入参数和返回值的数据类型都是数字类型的。数学函数包括cos,cosh,exp,ln, log,sin,sinh,sqrt,tan,tanh,acos,asin,atan,round等

round(n,[m]) 该函数用于执行四舍五入

SELECT round(23.75123) FROM dual; –返回24
SELECT round(23.75123, -1) FROM dual; –返回20
SELECT round(27.75123, -1) FROM dual; –返回30
SELECT round(23.75123, -3) FROM dual; –返回0
SELECT round(23.75123, 1) FROM dual; –返回23.8
SELECT round(23.75123, 2) FROM dual; –返回23.75
SELECT round(23.75123, 3) FROM dual; –返回23.751

trunc(n,[m]) 该函数用于截取数字

SELECT trunc(23.75123) FROM dual; –返回23
SELECT trunc(23.75123, -1) FROM dual; –返回20
SELECT trunc(27.75123, -1) FROM dual; –返回20
SELECT trunc(23.75123, -3) FROM dual; –返回0
SELECT trunc(23.75123, 1) FROM dual; –返回23.7
SELECT trunc(23.75123, 2) FROM dual; –返回23.75
SELECT trunc(23.75123, 3) FROM dual; –返回23.751

mod(m,n)取余函数

select mod(10,2) from dual; –返回0
SELECT MOD(10,3) FROM dual; –返回1
floor(n) 返回小于或是等于n的最大整数
ceil(n) 返回大于或是等于n的最小整数
SELECT ceil(24.56) from dual; –返回25
SELECT floor(24.56) from dual; –返回24

abs(n) 返回数字n的绝对值
对数字的处理,在财务系统或银行系统中用的最多,不同的处理方法,对财务报表有不同的结果

日期函数
日期函数用于处理date类型的数据。默认情况下日期格式是dd-mon-yy 即“12-7 月-12”

sysdate 返回系统时间

SQL> select sysdate from dual;

oracle add_months函数
oracle add_months(time,months)函数可以得到某一时间之前或之后n个月的时间

select add_months(sysdate,-6) from dual; –该查询的结果是当前时间半年前的时间
select add_months(sysdate,6) from dual; –该查询的结果是当前时间半年后的时间

转换函数
转换函数用于将数据类型从一种转为另外一种。在某些情况下,oracle server允许值的数据类型和实际的不一样,这时oracle server会隐含的转化数据类型
create table t1 (id varchar2(10))
insert into t1 values(1);
这样oracle就会自动的将1 –>’1’;
select to_char(birthday,’yy-mm-dd’) from student;
SELECT birthday FROM student WHERE to_char(birthday,’yy’)=18;

yy 两位数字的年份 2004–>04
yyyy 四位数字的年份 2004年
mm 两位数字的月份 8 月–>08
dd 两位数字的天 30 号–>30
hh24 8点–>20
hh12 8点–>08
mi、ss –>显示分钟秒
9 显示数字,并忽略前面0
0 显示数字,如位数不足,则用0补齐
. 在指定位置显示小数点
, 在指定位置显示逗号
$ 在数字前加美元
L 在数字前面加本地货币符号
C 在数字前面加国际货币符号
G 在指定位置显示组分隔符、
D 在指定位置显示小数点符号(.)

sys_context()系统函数

terminal 当前会话客户所对应的终端的标示符,如计算机名
language 语言
db_name 当前数据库名称
nls_date_format 当前会话客户所对应的日期格式
session_user 当前会话客户所对应的数据库用户名
current_schema 当前会话客户所对应的默认方案名
host 返回数据库所在主机的名称

通过该函数,可以查询一些重要信息,如正在使用哪个数据库

select sys_context('USERENV','db_name') from dual;

注意:USERENV是固定的,不能改的,db_name可以换成其它,

select sys_context('USERENV','language') from dual;
select sys_context('USERENV','current_schema') from dual;

CONCAT
语法: CONCAT(string1,string2)

功能:连接两个字符串

INITCAP
语法:INITCAP(string)

功能:返回字符单词首字母大写,其余小写,单词用空格和非字母字符分隔。

NLS_INITCAP
语法:NLS_INITCAP(string[,nlsparams])

功能:返回字符串每个单词第一个字母大写而单词中的其他字母小写的string,nlsparams

NLS_LOWER

语法:NLS_LOWER(string[,nlsparams])

功能:返回字符串中的所有字母都是小写形式的string。不是字母的字符不变。

NLS_UPPER

语法:NLS_UPPER(string[,nlsparams])

功能:返回字符串中的所有字母都是大写的形式的string。不是字母的字符不变。nlsparams参数的形式与用途和NLS_INITCAP中的相同。如果没有设定参数,则NLS_UPPER功能和UPPER相同。

REGEXP_REPLACE

语法:REGEXP_REPLACE(str1,pattem[,str2[,pos[,occ[,par]]]])

功能:10g新增函数,扩展了REPLACE函数的功能,并且用于按照特定正则表达式的规则替换字符串。其中参数

REGEXP_SUBSTR

语法:REGEXP_SUBSTR(str1,pattem [,pos[,occ[,par]]])

功能:10g新增函数,扩展了SUBSTR函数的功能,并且用于按照特定表达式的规则返回字符串的子串。其中参数str1指定源字符表达式,pattem指定规则表达式, pos指定起始搜索位置,occ指定替换出现的第几个字符串,par指定默认匹配操作的文本串。

REPLACE

语法:REPLACE(string,search_str[,replace_str])

功能:把string中的所有的子字符串search_str用可选的replace_str替换,如果没有指定replace_str,所有的string中的子字符串search_str都将被删除。REPLACE是TRANSLATE所提供的功能的一个子集。

TRIM/LTRIM/RTRIM

语法1:LTRIM/RTRIM(string1,[string2])

语法2:trim([string2] from string1)

语法1功能:中删除从左/右边算起出现在string1中的字符string2,string2如果是多个字符则逐个单字符比对删除,tring2被缺省设置为单个的空格。当遇到不在string2中的第一个字符,结果就被返回了;
语法2功能:删除左右两边出现在string1中的字符string2,tring2必须为单字符,否则报错。

SOUNDEX

语法: SOUNDEX(string)

功能: 返回string的声音表示形式.这对于比较两个拼写不同但是发音类似的单词而言很有帮助,如果字符发音相同,则返回的结果会一致.

SYS_CONTEXT

语法:sys_coniext(‘context’,’attribute’)

功能:该函数用于返回应用上下文的特定属性值,获得系统信息,其中context为上下文名,而attribute为应用上下文名,此函数可以得到oracle主机及客户端的信息。

     SYS_CONTEXT ('USERENV', 'LANGUAGE') 客户端语言,

     SYS_CONTEXT ('USERENV', 'SESSIONID') sessionid,

     SYS_CONTEXT ('USERENV', 'INSTANCE') instance,

     SYS_CONTEXT ('USERENV', 'ENTRYID') entryid,

     SYS_CONTEXT ('USERENV', 'ISDBA') isdba,

     SYS_CONTEXT ('USERENV', 'NLS_TERRITORY') 地区,

     SYS_CONTEXT ('USERENV', 'NLS_CURRENCY') 货币,

     SYS_CONTEXT ('USERENV', 'NLS_CALENDAR') nls_calendar,

     SYS_CONTEXT ('USERENV', 'NLS_DATE_FORMAT') 时间格式,

     SYS_CONTEXT ('USERENV', 'NLS_DATE_LANGUAGE') 时间语言,

     SYS_CONTEXT ('USERENV', 'NLS_SORT') nls_sort,

     SYS_CONTEXT ('USERENV', 'CURRENT_USER') current_user,

     SYS_CONTEXT ('USERENV', 'CURRENT_USERID') current_userid,

     SYS_CONTEXT ('USERENV', 'SESSION_USER') session_user,

     SYS_CONTEXT ('USERENV', 'SESSION_USERID') session_userid,

     SYS_CONTEXT ('USERENV', 'PROXY_USER') proxy_user,

     SYS_CONTEXT ('USERENV', 'PROXY_USERID') proxy_userid,

     SYS_CONTEXT ('USERENV', 'DB_DOMAIN') db_domain,

     SYS_CONTEXT ('USERENV', 'DB_NAME') 数据库名称,

     SYS_CONTEXT ('USERENV', 'HOST') 客户端完成名称,

     SYS_CONTEXT ('USERENV', 'OS_USER') 客户端用户,

     SYS_CONTEXT ('USERENV', 'EXTERNAL_NAME') external_name,

     SYS_CONTEXT ('USERENV', 'IP_ADDRESS') 客户端IP地址,

     SYS_CONTEXT ('USERENV', 'NETWORK_PROTOCOL') 网络协议,

     SYS_CONTEXT ('USERENV', 'BG_JOB_ID') bg_job_id,

     SYS_CONTEXT ('USERENV', 'FG_JOB_ID') fg_job_id,

     SYS_CONTEXT ('USERENV', 'AUTHENTICATION_TYPE') authentication_type,

     SYS_CONTEXT ('USERENV', 'AUTHENTICATION_DATA') authentication_data

USERENV

语法:USERENV(option)

功能:根据参数option,取得一个有关当前会话信息的VARCHAR2数值.

使用位置:过程性语言和SQL语句.

返回当前用户环境的信息,opt可以是:

ENTRYID,SESSIONID,TERMINAL,ISDBA,LABLE,LANGUAGE,CLIENT_INFO,LANG,VSIZE

OPTION=’ISDBA’若当前是DBA角色,则为TRUE,否则FALSE.

OPTION=’LANGUAGE’返回数据库的字符集.

OPTION=’SESSIONID’为当前会话标识符.

OPTION=’ENTRYID’返回可审计的会话标识符.

OPTION=’LANG’返回会话语言名称的ISO简记.

OPTION=’INSTANCE’返回当前的实例.

OPTION=’terminal’返回当前计算机名

ISDBA 查看当前用户是否是DBA如果是则返回true

点赞

发表评论

电子邮件地址不会被公开。必填项已用 * 标注