PL/SQL之函数总结

-----函数总结:

--一,数字函数:

--abs(n):用于返回n的绝对值
declare
  v_abs number(6, 2);
begin
  v_abs := abs(&no);
  dbms_output.put_line('绝对值:' || v_abs);
end;
select abs(-34) from dual;

---exp(n):返回e的n次幂(e=2.71828183...).
declare
  v_exp number(6, 2);
begin
  v_exp := exp(&no);
  dbms_output.put_line('e的'||&no||'次幂:' || v_exp);
end;

---floor(n):返回小于等于数字n的最大整数.
select floor(15.5),floor(14.1) from dual;

---ceil(n):返回大于等于数字n的最小整数.
select ceil(14.1),ceil(15.1) from dual;

--ln(n):返回数字n的自然对数,n必须大于0.
declare
  v_ln number(6, 2);
begin
  v_ln := ln(&no);
  dbms_output.put_line(&no||'的自然对数:' || v_ln);
end;

--log(m,n):返回数字m为底的数字n的对数,数字m可以是除0,1外的所有正整数,数字n可以产任何正整数.
select log(2,8),log(10,100) from dual;

--mod(m,n):返回两个数相除后的余数,如果n为0,返回结果为m.
declare
  v_mod number(6, 2);
begin
  v_mod := mod(8, 3);
  dbms_output.put_line('8除3的余数:' || v_mod);
end;

---power(m,n):返回数字m的n次幂,底数m,指数n可以是任何数字,如果m为负数,n必须是正数.
select power(-2,3),power(2,-1),power(3,4) from dual;

---round(n,[m]):执行四舍五入运算,如果省略m,则四舍五入到整数,
---如果m是负数,四舍五入到小数点前m位,如果m是正数,四舍五入到小数点后m位.
declare
  v_round number(6, 2);
begin
  v_round := round(&no, 2);
  dbms_output.put_line('四舍五入到小数点后两位:' || v_round);
end;

---trunc(n,[m]):截取数字,如果省略m,则将数字n的小数部分截去,
--如果m是正数,将数字n截取至小数点后第m位,如果数字m是负数,将数字n截取至小数点前第m位.
select trunc(45.926),trunc(45.926,1),trunc(45.926,-1) from dual;

---sign(n):检测数字的正负,如果n小于0,则返回-1,如果n等于0,则返回0;如果n大于0,则返回1;
select sign(-12),sign(0),sign(23)  from dual;

---sqrt(n):返回数字n的平方根.n必须大于等于0.
declare
  v_sqrt number(6, 3);
begin
  v_sqrt := sqrt(&no);
  dbms_output.put_line(&no || '的平方根:' || v_sqrt);
end;

--acos(n):返回数字n的反余弦值,输入值的范围是(-1,1),输出值的单位为弧度.   
select acos(0.3),acos(-0.3) from dual;

--asin(n):返回数字n的反正弦值,输入值的范围是(-1,1),输出值的单位为弧度.
declare
  v_asin number(6, 2);
begin
  v_asin := asin(&no);
  dbms_output.put_line(&no||'的反正弦:' || v_asin);
end;

--atan(n):返回数字n的反正切值,输入值可以是任何数字,输出值的单位为弧度.
select atan(12.3),atan(8) from dual;

--atan2(n,m):返回数字n除数字m的反正切值,输入值m不能为0外可以是任何数字,输出值的单位为弧度.
declare
  v_atan2 number(6, 2);
begin
  v_atan2 := atan2(19, 3);
  dbms_output.put_line('19/3的反正切值:' || v_atan2);
end;

--sin(n):返回数字n(以弧度表示的角度值)的正弦值.
declare
  v_sin number(6, 2);
begin
  v_sin := sin(0.5);
  dbms_output.put_line('0.5的正弦值:' || v_sin);
end;

---sinh(n):返回数字n的双曲正弦值.
select sinh(0.5) from dual;

--cos(n):返回数字n(以弧度表示的角度值)的余弦值.
declare
  v_cos number(6, 2);
begin
  v_cos := cos(0.5);
  dbms_output.put_line('0.5的余弦值:' || v_cos);
end;

---cosh(n):返回数字n的双曲余弦值.
select cosh(0) from dual;

--tan(n):返回数字n(以弧度表示的角度值)的正切值.
declare
  v_tan number(6, 2);
begin
  v_tan := tan(0.5);
  dbms_output.put_line('0.5的正切值:' || v_tan);
end;

---tanh(n):返回数字n的双曲正切值.
select tanh(0.5) from dual;

---------------------------------------------------------------------------------------
--二,字符函数:

--ascii(char):返回字符串的ascii码.
select ascii('a') "a",ascii('A') "A" from dual;

--chr(n):将ascii值转换为字符.
declare
  v_chr varchar2(10);
begin
  v_chr := chr(56);
  dbms_output.put_line('ascii码为56的字符:' || v_chr);
end;

---concat:连接字符串:作用与连接操作符'||'相同.
select concat('good','  morning') from dual;

---initcap(char):将字符串每个单词的首字母大写,其它字符小写,单词之间用空格和非字母分隔符.
declare
  v_initcap varchar2(10);
begin
  v_initcap := initcap('my word');
  dbms_output.put_line('首字母大写:' || v_initcap);
end;

---instr(char1,char2[,n[,m]]):用于取得子串在字符串中的位置,其中数字n为起始搜索位置,
--m为子串出现次数,如果n为负数,则从尾部开始搜索,数字m必须为正整数,并且n,m的默认值为1.
select instr('morning','n') from dual;

---length(char):返回字符串的长度,如果字符串的类型为char,则其长度包括所有的后缀空格,
--如果char为null,则返回null.
declare
  v_len int;
begin
  v_len := length('my god');
  dbms_output.put_line('字符串长度为:' || v_len);
end;

---lower(char):将字符串转换为小写格式.
select lower('LFKDSAKFJ') from dual;

---upper(char):将字符串转换为大写格式.
select upper('abcedfg') from dual;

---lpad(char1,n,char2):在字符串char1的左端填充字符串char2,直到字符串总长度为n,
--char2的默认值为空格,如果char1长度大于n,则返回char1左边的n个字符.
declare
  v_lpad varchar2(10);
begin
  v_lpad := lpad('aaaa',10,'*');
  dbms_output.put_line('在字符串左端添加字符*:' || v_lpad);
end;

---rpad(char1,n,char2):在字符串char1的右端填充字符串char2,直到字符串总长度为n,
--char2的默认值为空格,如果char1长度大于n,则返回char1右边的n个字符.
declare
  v_rpad varchar2(10);
begin
  v_rpad := rpad('aaaa',10,'*');
  dbms_output.put_line('在字符串右端添加字符*:' || v_rpad);
end;

---ltrim(char1[,set]):取掉字符串char1左端所包含的set中的任何字符,oracle从左边第一个字
--母开始扫描,逐一取掉在set中出现的字符,当遇到不是set中的字符时终止,然后返回剩余结果.
select ltrim('morning','m'),ltrim('morning','or') from dual;

---rtrim(char1[,set]):取掉字符串char1右端所包含的set中的任何字符,oracle从右边第一个字
--母开始扫描,逐一取掉在set中出现的字符,当遇到不是set中的字符时终止,然后返回剩余结果.
select rtrim('morning','ng'),rtrim('morning','ing') from dual;

---trim(char FROM string):从字符串的头部,尾部或两端截断特定字符,参数char为要截取的字符,
--string是源字符串
declare
  v_source varchar2(20) := 'babcdebfbab';
  v_trim   varchar2(20);
begin
  v_trim := trim('b' from v_source);
  dbms_output.put_line(v_trim);
end;

---replace(char,search_str[,rep_str]):用于将字符串的子串替换成其它子串,如果rep_str为null,
--则会取掉指定子串,如果search_str为null,则返回原字符串.
select replace('缺省值为10','缺省','默认') from dual;

---soundex(char):返回字符串的语音表示,使用它可比较发音相同的字符串.
select soundex('ship'),soundex('sheep') from dual;

---substr(char,m[,n]):用于取得字符串的子串,其中数字m是字符开始位置,n是子串的长度,如果m为0,
--则从首字符开始,如果m是负数,则从尾部开始.
declare
  v_subs varchar2(20);
begin
  v_subs := substr('morning', 1, 3);
  dbms_output.put_line('字符串的子串:' || v_subs);
end;
select substr(字段,m,n) from table;

---translate(char,from_str,to_str):用于将字符串char的字符按照from_str和to_str的对应关系进行转换.
select translate('2abc123',
                 '0123456789abcdefghijklmnopqrstuvwxyz',
                 '9999999999xxxxxxxxxxxxxxxxxxxxxxxxxx') "trans"
  from dual;

--------------------------------------------------------------------------------------------------------

---三,日期时间函数:

--add_months(d,n):返回特定日期时间d之后(之前)的n个月所对应的日期时间(n为正整数表示之后,负整数表示之前).
declare
  v_date date;
begin
  v_date := add_months(sysdate, -14);
  dbms_output.put_line('当前日期前14个月对应的日期:' || v_date);
end;

---current_date:返回当前会话时区所对应的日期时间.
alter session set time_zone='+8:0';
alter session set nls_date_format='yyyy-mm-dd hh24:mi';
select current_date from dual;
select sysdate from dual;

---current_timestamp:返回当前会话时区日期时间:
select current_timestamp from dual;

---dbtimezone:返回数据库所在时区:
select dbtimezone from dual;

---sessiontimezone:返回会话所对应的时区:
select sessiontimezone from dual;

---extract:从日期日间中取得所需要的特定数据(如年份,月份等):
select extract(year from sysdate) "年份" from dual;

---last_day(d):返回特定日期所在月份的最后一天.
select last_day(sysdate) from dual;

---localtimestamp:返回当前会话时区的日期时间.
select localtimestamp from dual;

---months_between(d1,d2):返回日期d1,d2之间相差的月数,如果d1<d2,返回负数,如果d1,d2天数相同
--或都是月底,则返回整数,否则oracle以每月31天为准来计算结果的小数部分.
select months_between(sysdate,'2011-01-31') from dual;

---new_time(date,zone1,zone2):返回时区1对应的时区2的日期时间.
select new_time(sysdate,'BST','EST') from dual;

---next_day(d,char):返回指定日期后的第一个工作日,(由char指定)所对应的日期.
select next_day(sysdate,'monday') from dual;

---round(d[,fmt]):返回日期时间的四舍五入结果,如果fmt指定年度,则7月1日为分界线,如果fmt指定月,
--则16日为分界线,如果指定天,则中午12:00为分界线.
select round(sysdate,'month') from dual;

---systimestamp:返回当前系统日期时间及时区:
select systimestamp from dual;

---trunc(d,[fmt]):截断日期时间数据,如果fmt指定年度,则结果为本年度的1月1日,如果指定月,则为本月1日.
select trunc(sysdate,'month') from dual;

---------------------------------------------------------------------------------------------------------
---四.转换函数:page116.

---ASCIISTR(string):将任意字符集的字符串转变为数据库字符集的ASCII字符串.
select ASCIISTR('中国') from dual;

----bin_to_num(expr[,expr][,expr][,expr]...)将位向量值转变为实际的数字值.
select bin_to_num(1,1,0,1) from dual;

---cast(expr as type_name):将一个内置数据类型或集合类型转变为另一个内置数据类型或集合类型.
declare
  v_cast varchar2(20);
begin
  v_cast := cast(sysdate as varchar2);
  dbms_output.put_line('转换结果:' || v_cast);
end;

---compose(string):将输入字符串转换为UNICODE字符串值
select compose('A'||unistr('\0508')) from dual;

---convert(char,dest_char_set,source_char_set):将一个字符串人一个字符集转换为另一个字符集.
declare
  v_convert varchar2(20);
begin
  v_convert := convert('中国', 'US7ASCII', 'WE8ISO8859P1');
  dbms_output.put_line('转换结果:' || v_convert);
end;

---to_char(date[,fmt[,nls_param]]):将日期值转换为字符串,其中fmt用于批定日期格式,nls_param
--用于指定nls参数.
select to_char(sysdate,'yyyy-mm-dd') from dual;

---to_char(n[,fmt[,nls_param]]):将数字值转变为varchar2数据类型.
select to_char(-10000,'L99G999D99MI') from dual;

----to_date(char[,fmt[,nls_param]]):将符合特定日期格式的字符转变为DATE类型的值.
select to_date('20110101','yyyymmdd') from dual;

---unistr(string):输入字符串返回相应的unicode字符.
select unistr('\00D6') from dual;

----五:分组函数:

---avg([all|distinct]expr):用于计算平均值:
select avg(sal) from emp;

---corr(expr1,expr2):返回函数的相关系数:
---其数值使用表达式"covar_pop(expr1,expr2)/(stddev_pop(expr1)*stddev_pop(expr2))"获得
select weight_class, corr(list_prict, min_prict)
  from product_information
group by weight_class;

----count([all|distinct]expr):返回总计行数
select count(distinct sal) from emp;

---first:不能单独使用,与分组函数结合使用.取得排序等级的第一级,然后使用分组函数汇总该等级的数据
select min(sal) keep(dense_rank first order by comm desc) "补助最高级别雇员的最低工资",
       max(sal) keep(dense_rank first order by comm desc) "补助最高级别雇员的最高工资"
  from emp;
 
----last:(同上)取得排序等级的最后一级,然后使用分组函数汇总该等级的数据
select min(sal) keep(dense_rank last order by comm desc) "补助最高级别雇员的最低工资",
       max(sal) keep(dense_rank last order by comm desc) "补助最高级别雇员的最高工资"
  from emp;
 
---max([all|distinct]expr):取得列或表达式的最大值.
select deptno,max(sal) from emp group by deptno;


---min([all|distinct]expr):取得列或表达式的最小值.
select deptno,min(sal) from emp group by deptno;

---covar_pop(expr1,expr2):
---返回成对数字的协方差,其数值使用表达式"(sum(x1*x2)-sum(x1)*sum(x2)/n)/n"获得
---covar_samp(expr1,expr2):
---返回成对数字的协方差,其数值使用表达式"(sum(x1*x2)-sum(x1)*sum(x2)/n)/(n-1)"获得
select t.calender_month_number,
       covar_pop(s.amount_sold, s.quantity_sold) as covar_pop,
       covar_samp(s.amount_sold, s.quantity_sold) as covar_samp,
  from sales s, times t
where s.time_id = t.time_id
   and t.calender_year = 1998
group by t.calender_month_number;

---cume_dist(expr1,expr2..)within group (order by expr1,expr2,...):
---返回特定数值在一组行数据中累积分布比例.
select cume_dist(2000)within group (order by sal) "cume-dist" from emp;

---dense_rank(expr1,expr2...) within group (order by expr1,expr2...):
----返回特定数据在一组数据中的等级.
select dense_rank(5000) group (order by sal) rank from emp;

----group_id():区分分组结果中的重复行:
select deptno,job,avg(sal),group_id() from emp group by deptno,rollup(deptno,job);

---grouping(expr):确定分组结果用到了特定的表达式,返回值为0,表示用到了,为1表示未用到.
select deptno,job,sum(sal),grouping(job) from emp group by rollup(deptno,job);

---percent_rank(expr1,expr2...) within group(order by expr1,expr2,...):
---返回特定数值在统计级别中所占的比例
select percent_rank(3000) within group (order by sal) percent from emp;

---percentile_cont(percent_expr) within group(order by expr):
---返回在统计级别中处于某个百分点的特定数值(按照连续分布模型确定)
select percentile_cont(.6) within group (order by sal) value from emp;

---percentile_disc(percent_expr) within group(order by expr):
---返回在统计级别中处于某个百分点的特定数值(按照离散分布模型确定)
select percentile_cont(.6) within group (order by sal) value from emp;

---rank(expr1,expr2....) within group (order by expr1,expr2....):
----返回特定数值在统计数值中所占的等级.
select rank(3000) within group (order by sal) rank from emp;

---stddev([all|distinct]expr):取得标准偏差,按方差的平方根取得.
select stddev(sal) from emp;

---stddev_pop(expr):返回统计标准偏差,返回统计方差的平方根.
select stddev_pop(sal) from emp;

  ---stddev_samp(expr):返回采样标准偏差,返回采样方差的平方根.
select stddev_samp(sal) from emp;

---sum([all|distinct]expr):计算列或表达式的总和.
select deptno,sum(sal) from emp group by deptno;

---var_pop(expr):返回统计方差,使用公式:(sum(x*x)-sum(x)*sum(x)/count(x))/count(x)
select var_pop(sal) from emp;

  ---var_samp(expr):返回采样方差,使用公式:(sum(x*x)-sum(x)*sum(x)/count(x))/count(x-1)
select var_samp(sal) from emp;

  ---variance([all|distinct]expr):返回列式表达式的方差,数值与var_samp(expr)相同
select variance(sal) from emp;

-------------------------------------------------------------------------------------