十一，函数

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