Oracle部分函数简单介绍

字符函数：输入的参数是字符类型，其返回值是字符类型或数字类型。


ASCII(char);用于返回字符串首字符的ASCII码值，如：


SELECT ascii（'a'）,ascii('A') from dual;


        97              65


CHR(n):用于将ASCCI码值转换为字符如：


declare
 v_chr varchar2(200);


begin


v_chr:=chr(56);
dbms_output.put_line('ASCII码值为56的字符'||v_chr);
end;


ASCII码值为56的字符为8；


CONCAT：用于连接字符串，其作用与操作符（||）完全相同如：


select concat('Good,''Morning') from dual;


结果为：GoodMorning


INITCAP(char):用于将字符串中每个单词首字符大写，其他字母小写，单词之间
用空格和非字母符分隔。如：


declare
  v_ini varchar2(20);
begin
  v_ini := initcap('my worD');
  dbms_output.put_line('结果为：' || v_ini);
end;


结果为：My Word


INSTR(char1，char2[,n[m]]):用于取得子串在字符的位置，其中数字n为起始搜索位置，数字m为
子串出现的次数。


select instr('morning','n') from dual;


结果为：4 n在第四位出现


LENGTH(char):用于返回字符串的长度。如果字符串类型为char，则长度包括所有的后缀空格
：如果char为null则返回null


declare
v_len  int;
begin
  v_len:=length('my good');
  dbms_output.put_line('字符串长度为：'||v_len);
end;


结果为：7包括空格


LOWER(char):用于将字符串转换为小写格式如：


select lower('SQL INtroduction') from dual;


结果为：sql introduction 全部都为小写；


LPAD(char1,char2):用于在字符串char1的左端填充字符串char2,直至字符串
总长度为n， char2的默认值为空格。如果char1长度大于n，则返回char1左端的n个字符


declare
  v_lp varchar2(200);
begin
  v_lp := lpad('aaaa', 5, '*');
  dbms_output.put_line('字符串左端添加字符*：' || v_lp);
end;


结果为：*aaaa


LTRIM(char[,set]):用于去掉字符串char左端所包含的set中的任何字符。
从左端第一个字符开始扫描，逐一去掉在set中出现的字符，当遇到不是set的字符终止，然后
返回剩余的结果：


select ltrim('morning','m') from dual;


结果为：orning


NLS_INITCAP(char,'nls_param'):用于将字符串char的首字母大写，其他字母小写，
其中char用于指定NCHAR或NVARCHAR2类型，其前面加上n，用单引号括起来，
nls_param的格式为"nls_sort=sort",用于指定特定语言的特征。


declare
  v_nls_ini nchar(10);
begin
  v_nls_ini := nls_initcap(n'my word');
  dbms_output.put_line('首字母大写：' || v_nls_ini);
end;


结果为：My Word   


UPPER(char,'nls_param'):用于将字符串转变为大写，
nls_param的格式为"nls_sort=sort",用于指定特定语言的特征。


declare
  v_upper varchar2(200);
begin
  v_upper := upper('my word');
  dbms_output.put_line('字符串大写：' || v_upper);
end;
结果为：MY WORD


TRRIM（char[,set]）:用于去掉char右边所包含的set中的任何字符


select rtrim('morning','ing') from dual;


结果为：morn




SOUNDEX(char):用于返回字符串的语音表示，使用该函数可以比较发音相同的字符串


select soundex('ship'),soundex('sheep') from dual;


结果为：S100 S100


TRIM（charFROM string）:用于从字符串的头部、尾部或者两端截断特定的字符，
参数char为要截取的字符，string是原字符。


declare
  v_source varchar2(20) := 'ABCDGHHJAB';
  v_trim   varchar2(20);
begin
  v_trim := trim('A' FROM v_source);
  dbms_output.put_line(v_trim);
end;


结果为：BCDGHHJAB


时间日期函数：用于处理DATE和TIMESTAMP类型的数据


ADD_MONTHS(d,n):用于返回特定日期时间d之后（或之前）的n个月所对应的日期时间


declare
  v_date date;
begin
  v_date := add_months(sysdate, -14);
  dbms_output.put_line('当前日期前十四的月对应的日期' || to_char(v_date, 'yyyy-mm-dd'));
end;


结果为：当前日期前十四的月对应的日期2017-02-13


CURRENT_TIMESTAMP：用于返回当前会话时区所对应的日期时间如：


select current_timestamp from dual;


结果为:13-4月 -18 11.35.18.443176 下午 +08:00


EXTRACT:用于从日期时间值中取得所需要的特定数据


select extract(year from sysdate)year from dual;


目前结果为2018


FROM_TZ：用于将特定的时区的TIMESTAMP值转为TIMESTAMP WITH TIME ZONE值
declare
  v_tzv varchar2(100);
begin
  v_tzv := from_tz(timestamp '2003-03-28 08:00:00', '3:00');
  dbms_output.put_line(v_tzv);
end;


结果为：28-3月 -03 08.00.00.000000000 上午 +03:00


LAST_DAT(d):用于返回特定的日期所在月份的最后一天


select last_day(sysdate) from dual;


目前结果为：2018/4/30 23:52:51


LOCALTIMESTAMP:用于返回当前会话时区的日期时间


declare
  v_ts varchar2(100);
begin
  v_ts := localtimestamp;
  dbms_output.put_line('当前日期：' || v_ts);
end;


目前结果为：当前日期：13-4月 -18 11.55.33.898510000 下午


MONTHS_BETWEEN(d1,d2):用于返回日期d1和d2之间相差的月数，如果
d1小于d2则返回负数。
select months_between(sysdate,'31-8月-1998') from dual;


当前结果为：235.451588261649


NEW_TIME(date,zonel,zone2):用于返回时区一的时间所对应的时区二的时间


NEXT_DAY(d,char):用于返回指定日期后的第一个工作日（由char指定）所对应的日期；


select next_day(sysdate,'星期一') from dual;


当前结果为：2018/4/16 0:06:38


SYSDATE:返回系统当前时间。


SYSTEMSTAMP：返回当前系统的日期时间及时区


转换函数：用于将数值从一种数据类型转换为另一种数据类型。




ASCIISTR(string):将任意的在字符集的字符串转为数据库字符集的ASCII字符串：


select asciistr('中华') from dual;


结果为：\4E2D\534E


BIN_TONUM(expr[,expr]...)将位向量值转变为实际数值


select bin_to_num(1,0,1,1,1) from dual;


结果为：23


CAST(expr AS type_name):将一个内置的数据类型或集合类型转变为
另一个内置数据类型或集合类型。


declare
  v_cast varchar2(20);
begin
  v_cast := cast(sysdate as varchar2);
  dbms_output.put_line('转换结果为' || v_cast);
end;


结果为:转换结果为14-4月 -18;


CHARTOROWID(char):用于将字符串值转变为ROWID数组类型，但字符串值必须符合ROWID格式


select chartorowid('AAAFDLAAFAAAABSAA/') FROM DUAL;           


结果为：AAAFDLAAFAAAABSAA/


DECOMPOSE(string):用于分解字符串并返回相应的UNICODE字符串


select decompose ('Chateaux') from dual;           


结果为：Chateaux




to_char(character);


集合函数


CARDINALITY（nested_table）:用于返回嵌套表的实际元素的个数


select product_id,cardinality(ad_textdocs_ntab) from print_media;


COLLECT(column):用于根据输入列和被选择行建立嵌套表结果


select cast(collect(phone_numbers)) as phone_book_t) from customers;


其他单行函数


BFILENAME('directory','filename'):用于初始化BFILE定位符，其中
directory是于OS路径相关的DIRECTORY对象，filename是OS文件的名称。


DEPTH(n):用于返回XML方案中UNDER_PATH路径所对应的相对层数。其中参数n用于指定相对层数


EMPTY_BLOB():用于初始化BLOB变量，


declare
  v_lob blob;
begin
  v_lob := empty_blob();
end;


EMPTY_CLOB():用户初始化CLOB变量


LEAST(expr[,expr]...):用于返回列表表达式expr1,expr2,...中最小的一个。
在比较之前，expr2等项会被隐含地转换为exprl的数据类型


select least('BLACK','BLACK','BLACK') FROM dual;


NULLIF（expr1,expr2）:用于比较表达式expr1和expr2，
如果二者相等，则返回NULL，否则返回expr1;


declare
  v_expr1 int := 100;
  v_expr2 int := 10;
begin
  if nullif(v_expr1, v_expr2) is null then
    dbms_output.put_line('二者相等');
  else
    dbms_output.put_line('二者不等');
  end if;
end;




NVL(expr1,expr2):用于将null转变为实际值，如果expr1是null则返回expr2
如果expr1不是null则返回expr1.


PATH(correction_integer)：用于返回特定的XML资源所对应的相对路径


SYS_CONTEXT('context','attribute'):用于返回应用上下文的特定的属性值，
其中context为应用上的下文名，而attribute则用于指定的属性名。


UID：用于返回当前会话所对应的用户ID号




declare
  v_uid int;
begin
  v_uid := uid;
  dbms_output.put_line('回话用户ID号' || v_uid);
end;


USER:用户返回当前会话所对应的数据库用户名


declare
  v_user varchar2(200);
begin
  v_user := user;
  dbms_output.put_line('当前用户为：' || v_user);
end;


分组函数：它会根据输入的多行数据返回一个结果。


AVG(expr1)：用于计算平均值。


CORR(expr1,expr2):用于返回成对数值的相关系数


COUNT(expr1):用于返回总计行数。


COVAR_POP(expr1,expr2):用于返回成对数数字的协方差


GROUPING(expr):用于确定分组结果是否用到了特定的表达式，返回值为0表示
用到了该表达式，返回值为1表示未用该表达式。


MAX(expr):用于取得列或表达式的最大值


MIN(expr):用于取得列或表达式的最小值


SUM(expr):用于计算列或表达式的总和


VALUE(expr):用于返回行对象所对应的对象实例数据，expr用于指定行对象的别名。