SQL函数

版权声明:本文为博主原创文章,未经博主允许不得转载。 https://blog.csdn.net/Kalen_Chen/article/details/1666215

SQL函数

SQL函数包括单行函数和多行函数,其中单行函数是指输入一行输出也是一行的函数;多行函数也被称为分组函数,它会根据输入的多行数据输出一个结果。SQL函数不仅可以在SQL语句中引用,也可以在PL/SQL块内引用。大多数单行函数都可以直接在PL/SQL块内引用,但多行函数不能有PL/SQL块直接引用,而只能在PL/SQL块的内嵌SQL语句中引用。

 

一.数字函数

数字函数的输入参数和返回值都是数字型,并且多数函数精确到38位。函数COSCOSHEXPLNLOGSINSINHSQRTTANTANH精确到36位,而函数ACOSASINATANATAN2则精确到30位。这些函数不仅可以在SQL语句中引用,也可以直接在PL/SQL块中引用。

(1)    ABS(n):该函数用于返回数字n的绝对值。

示例:

DECLARE

  v_abs NUMBER(6,2);

BEGIN

  v_abs := ABS(&no);

  dbms_output.put_line('绝对值:'||v_abs);

END;

 

(2)    ACOS(n):该函数用于返回数字n的反余弦值,输入值的范围是-1~1,输出值的单位为弧度。

示例:

SELECT ACOS(.3),ACOS(-.3) FROM dual;

 

(3)    ASIN(n):该函数用于返回数字n的反正弦值,输入值的范围是-1~1,输出值的单位为弧度。

示例:

DECLARE

  v_asin NUMBER(6,2);

BEGIN

  v_asin := ASIN(0.8);

  dbms_output.put_line('0.8的反正弦值:'||v_asin);

END;

 

(4)    ATAN(n):该函数用于返回数字n的反正切值,输入值可以是任何数字,输出值的单位为弧度

示例:

SELECT ATAN(10.3),ATAN(-20.3) FROM dual;

 

(5)    ATAN2(n,m):该函数用于返回数字n处以数字m的反正切值。输入值除了m不能为0以外,可以是任意数字(m不能为0),输出值的单位为弧度。

示例:

DECLARE

  v_atan2 NUMBER(6,2);

BEGIN

  v_atan2 := atan2(19,3);

  dbms_output.put_line('19/3的反正切值:'||v_atan2);

END;

 

(6)    CEIL(n):该函数用于返回大于等于数字n的最小整数。

示例:

SELECT CEIL(15),CEIL(15.1) FROM dual;

 

(7)    COS(n):该函数用于返回数字n(以弧度表示的角度值)的余弦值。

示例:

DECLARE

  v_cos NUMBER(6,2);

BEGIN

  v_cos := COS(0.5);

  dbms_output.put_line('0.5的余弦值:'||v_cos);

END;

 

(8)    COSH(n):该函数用于返回数字n的双曲余弦值。

示例:

SELECT COSH(0) "0的双曲余弦值" FROM dual;

 

(9)    EXP(n):该函数用于返回en次幂(e=2.71828183…)

示例:

DECLARE

  v_exp NUMBER(6,2);

BEGIN

  v_exp := exp(4);

  dbms_output.put_line('e4此幂:'||v_exp);

END;

 

(10)FLOOR(n):该函数用于返回小于等于数字n的最大整数。

示例:

SELECT FLOOR(15),FLOOR(15.1) FROM dual;

 

(11)LN(n):该函数用于返回数字n的自然对数,其中数字n必须大于0

示例:

DECLARE

  v_ln NUMBER(6,2);

BEGIN

  v_ln := LN(4);

  dbms_output.put_line('4的自然对数:'||v_ln);

END;

 

(12)LOG(m,n):该函数用于返回以数字m为底的数字n的对数,数字m可以是除01以外的任何正整数,数字n可以是任何正整数。

示例:

SELECT LOG(2,8),LOG(10,100) FROM dual;

 

(13)MOD(m,n):该函数用于取得两个数字相除后的余数。如果数字n0,则返回结果为m

示例:

DECLARE

  v_mod NUMBER(6,2);

BEGIN

  v_mod := MOD(10,3);

  dbms_output.put_line('103的余数:'||v_mod);

END;

 

(14)POWER(m,n):该函数用于返回数字mn次幂,底数m和指数n可以是任意数字。但如果数字m为复数,则数字n必须是整数。

示例:

SELECT POWER(-2,3),POWER(2,-1) FROM dual;

 

(15)ROUND(n,[m]):该函数用于执行四舍五入运算;如果省略m,则四舍五入至整数位;如果m是负数,则四舍五入到小数点前m位;如果m是正数,则四舍五入到小数点后m位。

示例:

DECLARE

  v_round NUMBER(6,2);

BEGIN

  v_round := ROUND(&no,1);

  dbms_output.put_line('四舍五入到小数点后一位:'||v_round);

END;

 

(16)SIGH(n):该函数用于检测数字的正负。如果数字n小于0,则函数的返回值为-1;如果数字n等于0,则函数的返回值为0;如果数字n大于0,则函数的返回值为1

示例:

SELECT SIGN(-10),SIGN(0),SIGN(20) FROM dual;

 

(17)SIN(n):该函数用于返回数字n(以弧度表示的角)的正弦值。

示例:

DECLARE

  v_sin NUMBER(6,2);

BEGIN

  v_sin := SIN(0.3);

  dbms_output.put_line('0.3的正弦值:'||v_sin);

END;

 

(18)SINH(n):该函数用于返回数字n的双曲正弦值。

示例:

SELECT SINH(.5) FROM dual;

 

(19)SQRT(n):该函数用于返回数字n的平方根,并且数字n必须大于等于0

示例:

DECLARE

  v_sqrt NUMBER(6,2);

BEGIN

  v_sqrt := SQRT(10);

  dbms_output.put_line('10的平方根:'||v_sqrt);

END;

 

(20)TAN(n):该函数用于返回数字n(以弧度表示的角)的正切值。

示例:

SELECT TAN(45*3.14159265359/180) FROM dual;

 

(21)TANH(n):该函数用于返回数字n的双曲正切值。

示例:

DECLARE

  v_tanh NUMBER(6,2);

BEGIN

  v_tanh := TANH(10);

  dbms_output.put_line('10的双曲正切值:'||v_tanh);

END;

 

(22)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;

 

二.字符函数

字符函数的输入参数为字符类型,其返回值是字符类型或数字类型。字符函数既可以在SQL语句中使用,也可以直接在PL/SQL块中引用。

 

(1)    ASCII(char):该函数用于返回字符串首字符的ASCII码值。

示例:

SELECT ASCII('a') "a",ASCII('A') "A" FROM dual;

 

(2)    CHR(n):该函数用于将ASCII码值转变为字符。

示例:

DECLARE

  v_chr VARCHAR2(10);

BEGIN

  v_chr := chr(56);

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

END;

 

(3)    CONCAT:该函数用于连接字符串,其作用与连接操作符(||)完全相同。

示例:

SELECT CONCAT('Good','Morning') FROM dual;

 

(4)    INITCAP(char):该函数用于将字符串中每个单词的首字符大写,其他字符小写,单词之间用空格和非字母字符分隔。

示例:

DECLARE

  v_initcap VARCHAR2(10);

BEGIN

  v_initcap := INITCAP('my word');

  dbms_output.put_line('首字符大写:'||v_initcap);

END;

 

(5)    INSTR(char1,char2[,n[,m]]):该函数用于取得子串的字符串中的位置,其中数字n为起始搜索位置,数字m为子串出现次数。如果数字n为负数,则从尾部开始搜索;数字m必须为正整数,并且nm的默认值为1

示例:

SELECT INSTR('morning','n') FROM dual;

 

(6)    LENGTH(char):该函数用于返回字符串的长度。如果字符串的类型为CHAR,则其长度包括所有的后缀空格;如果charnull,则返回null

示例:

DECLARE

  v_len INT;

BEGIN

  v_len := LENGTH('my word');

  dbms_output.put_line('字符串长度:'||v_len);

END;

 

(7)    LOWER(char):该函数用于将字符串转换为小写格式。

示例:

SELECT LOWER('SQL introduction') FROM dual;

 

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

示例:

DECLARE

  v_lpad VARCHAR2(10);

BEGIN

  v_lpad := LPAD('aaaa',10,'*');

  dbms_output.put_line('在字符串左端添加字符*'||v_lpad);

END;

 

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

示例:

SELECT LTRIM('morning','m'),LTRIM('morning','or') FROM dual;

 

(10)NLS_INITCAP(char,’nls_param’):该函数用于将字符串char的首字符大写,其他字符小写,其中char用于指定NCHARNVARCHAR2类型字符串,其前面加上n,用单引号括起来,nls_param的格式为“nls_sort=sort”,用于指定特定语言特征。

示例:

DECLARE

  v_nls_initcap NCHAR(10);

BEGIN

  v_nls_initcap := nls_initcap(n'my word');

  dbms_output.put_line('首字符大写:'||v_nls_initcap);

END;

 

(11)NLS_LOWER(char,’nls_param’):该函数用于将字符串转变为小写,其中nls_param的格式为”nls_sort=sort”,用于指定特定语言特征。

示例:

SELECT NLS_LOWER(n'SQL') FROM dual;

 

(12)NLS_SORT(char,’nls_param’):该函数用于按照特定语言的要求进行排序,其中nls_param的格式为“nls_sort=sort”,用于指定特定语言特征。

示例:

SELECT * FROM test

ORDER BY NLSSORT(name,'NLS_SORT = XDanish');

 

(13)NLS_UPPER(char,’nls_param’):该函数用于将字符串转变为大写,其中nls_param的格式为“nls_sort=sort”,用于指定特定语言特征。

示例:

DECLARE

  v_upper VARCHAR2(10);

BEGIN

  v_upper := NLS_UPPER('my word','nls_sort=XGERMAN');

  dbms_output.put_line('字符串大写:'||v_upper);

END;

 

(14)REGEXP_REPLACE(source_string,pattern[,replace_string[,position[,occurrence[,match_parameter]]]]):该函数是Oracle 10g新增加的函数,它扩展了函数REPLACE的功能,并且该函数用于按照特定表达式的规则替换字符串。其中,参数source_string用于指定源字符表达式,pattern用于指定规则表达式,replace_string用于指定替换字符串,position用于指定起始搜索位置,occurrence用于指定替换出现的第n个字符串,match_parameter用于指定默认匹配操作的文本串。

示例:

SELECT REGEXP_REPLACE(country_name,'(.)','/1') "REGEXP_REPLACE"

FROM countries;

 

(15)REGEXP_SUBSTR(source_string,pattern[,position[,occurrence[,match_parameter]]]):该函数是Oracle 10g新增加的函数,它扩展了函数SUBSTR的功能,并且用于按照特定表达式的规则返回字符串的子串。其中,source_string用于指定源字符串表达式,pattern用于指定规则表达式,position用于指定起始搜索位置,occurrence用于指定第n次出现的字符串,match_parameter用于指定默认匹配操作的文本串。

示例:

SELECT REGEXP_SUBSTR('http://www.oracle.com/products',

'http://([[:alnum:]]+/.?)(3,4)/>') "REGEXP_SUBSTR" FROM dual;

 

(16)REPLACE(char,search_string[,replacement_string]):该函数用于将字符串的子串替换为其他子串。如果replacement_stringnull,则会去掉指定子串;如果search_stringnull,则返回原有字符串。

示例:

SELECT REPLACE('缺省值为10','缺省','默认') FROM dual;

 

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

示例:

DECLARE

  v_rpad VARCHAR2(10);

BEGIN

  v_rpad := RPAD('aaaa',10,'*');

  dbms_output.put_line('在右端添加字符:'||v_rpad);

END;

 

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

示例:

SELECT SOUNDEX('ship'),SOUNDEX('sheep') FROM dual;

 

(19)SUBSTR(char,m[,n]):该函数用于取得字符串的子串,其中数字m是字符开始位置,数字n是子串的长度。如果m0,则从首字符开始;如果m是负数,则从尾部开始。

示例:

DECLARE

  v_subs VARCHAR2(10);

BEGIN

  v_subs := SUBSTR('morning',1,3);

  dbms_output.put_line('字符串的子串:'||v_subs);

END;

 

(20)TRANSLATE(char,from_string,to_string):该函数用于将字符串char的字符按照from_stringto_string的对应关系进行转换。

示例:

SELECT TRANSLATE('2KRW229',

'0123456789ABCDEFGHIJKLMNOPQRSTUVWXYZ',

'9999999999XXXXXXXXXXXXXXXXXXXXXXXXXX') "TRANS"

FROM dual;

 

(21)TRIM(charFROM string):该函数用于从字符串的头部、尾部和两端截断特定字符,参数char为要截去的字符,string是源的字符串。

示例:

DECLARE

  v_source VARCHAR2(20) := 'ABCDGHJHJAB';

  v_trim VARCHAR2(20);

BEGIN

  v_trim := TRIM('A' FROM v_source);

  dbms_output.put_line(v_trim);

END;

 

(22)UPPER(char):该函数用于将字符串转换为大写格式。

示例:

SELECT UPPER('sql') FROM dual;

 

三.日期时间函数

日期时间函数用于处理DATETIMESTAMP类型的数据。除了函数MONTHS_BETWEEN返回数字值外,其他日期函数均返回DATE类型的数据。Oracle是以7位数字格式来存放日期数据的,包括世纪、年、月、日、小时、分钟、秒,并且默认日期显示格式为“DD-MON-YY”。

 

(1)    ADD_MONTHS(d,n):该函数用于返回特定日期时间d之后(或之前)n个月所对应的日期时间(n为正整数表示之后;n为负整数表示之前)

示例:

DECLARE

  v_date DATE;

BEGIN

  v_date := ADD_MONTHS(sysdate,-14);

  dbms_output.put_line('当前日期前14个月对应的日期:'||v_date);

END;

 

(2)    CURRENT_DATE:该函数是Oracle 9i新增加的函数,用于返回当前会话时区所对应的日期时间。

示例:

ALTER SESSION SET TIME_ZONE = '-5:0';

ALTER SESSION SET nls_date_format = 'YYYY-MM-DD HH24:MI';

SELECT CURRENT_DATE FROM dual;

 

(3)    CURRENT_TIMESTAMP:该函数是Oracle 9i新增加的函数,用于返回当前会话时区的日期时间。

示例:

SELECT CURRENT_TIMESTAMP FROM dual;

 

(4)    DBTIMESONE:该函数是Oracle 9i新增加的函数,用于返回数据库所在的时区。

示例:

DECLARE

  v_zone VARCHAR2(10);

BEGIN

  v_zone := DBTIMEZONE;

  dbms_output.put_line('当前数据库时区:'||v_zone);

END;

 

(5)    EXTRACT:该函数是Oracle 9i新增加的函数,用于从日期时间值中取得所需要的特定数据(例如取得年份、月份等)

示例:

SELECT EXTRACT(YEAR FROM sysdate) year FROM dual;

 

(6)    FROM_TZ:该函数是Oracle 9i新增加的函数,用于将特定时区的TIMESTAMP值转变为TIMESTAMP WITH TIMEZONE值。

示例:

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;

 

(7)    LAST_DAY(d):该函数用于返回特定日期所在月份的最后一天。

示例:

SELECT LAST_DAY(sysdate) FROM dual;

 

(8)    LOCALTIMESTAMP:该函数是Oracle 9i新增加的函数,用于返回当前会话时区的日期时间。

示例:

DECLARE

  v_ts VARCHAR2(100);

BEGIN

  v_ts := LOCALTIMESTAMP;

  dbms_output.put_line('当前日期时间:'||v_ts);

END;

 

(9)    MONTHS_BETWEEN(d1,d2):该函数用于返回日期d1d2之间相差的月数。如果d1小于d2,则返回负数。如果日期d1d2的天数相同或都是月底,则返回整数;否则Oracle以每月31填为准来计算结果的小数部分。

示例:

SELECT MONTHS_BETWEEN(sysdate,'31-8-1998') FROM dual;

 

(10)NEW_TIME(date,zone1,zone2):该函数用于返回时区一的日期所对应的时区二的日期时间。

示例:

DECLARE

  v_time DATE;

BEGIN

  dbms_session.set_nls('nls_date_format',

  '''YYYY-MM-DD HH24:MI:SS''');

  v_time := new_time(TO_DATE('2003-11-10 12:10:00',

  'YYYY-MM-DD HH24:MI:SS'),'BST','EST');

  dbms_output.put_line('当前日期时间:'||v_time);

END;

 

(11)NEXT_DAY(d,char):该函数用于返回指定日期后的第一个工作日(char指定)所对应的日期。

示例:

SELECT NEXT_DAY(sysdate,'星期一') FROM dual;

 

(12)NUMTODSINTERNAL(n,char_expr):该函数用于将数字n转换为INTERVAL DAY TO SECOND格式,其中char_expr可以是DAY,HOUR,MINUTESECOND

示例:

DECLARE

  v_date VARCHAR2(100);

BEGIN

  v_date := numtodsinterval(10000,'MINUTE');

  dbms_output.put_line('10000分钟对应的时间:'||v_date);

END;

 

(13)NUMTOYMINTERNAL(n,char_expr):该函数用于将数字n转换为INTERVAL YEAR TO MONTH格式,其中char_expr可以是YEARMONTH

示例:

SELECT numtoyminterval(100,'MONTH') AS year_month FROM dual;

 

(14)ROUND(d[,fmt]):该函数用于返回日期时间的四舍五入结果。如果fmt指定年度,则71为分界线;如果fmt指定月,则16日为分界线;如果指定天,则中午12:00时为分界线。

示例:

DECLARE

  v_date DATE;

BEGIN

  v_date := ROUND(SYSDATE,'MONTH');

  dbms_output.put_line(SYSDATE||'四舍五入结果:'||v_date);

END;

 

(15)SESSIONTIMEZONE:该函数是Oracle 9i新增加的函数,用于返回当前会话所在时区。

示例:

SELECT SESSIONTIMEZONE FROM dual;

 

(16)SYS_EXTRACT_UTC(datetime_with_timezone):该函数用于返回特定时区时间所对应的格林威治时间。

示例:

DECLARE

  v_timestamp TIMESTAMP;

BEGIN

  v_timestamp := SYS_EXTRACT_UTC(SYSTIMESTAMP);

  dbms_output.put_line('格林威治时间:'||v_timestamp);

END;

 

(17)SYSDATE:该函数用于返回当前系统的日期时间。

示例:

SELECT SYSDATE FROM dual;

 

(18)SYSTIMESTAMP:该函数是Oracle 9i新增加的函数,用于返回当前系统的日期时间及时区。

示例:

DECLARE

  v_timestamp VARCHAR2(100);

BEGIN

  v_timestamp := SYSTIMESTAMP;

  dbms_output.put_line('当前期铜时间及时区:'||v_timestamp);

END;

 

(19)TO_DISNITERNAL(char[,’nls_param’]):该函数是Oracle 9i新增加的函数,用于将符合特定日期和时间格式的字符串转变为INTERVAL DAY TO SECOND类型。

示例:

SELECT TO_DSINTERVAL('58:10:10') FROM dual;

 

(20)TO_TIMESTAMP(char[fmt[,’nls_param’]]):该函数是Oracle 9i新增加的函数,用于将符合特定日期和时间格式的字符串转变为TIMESTAMP类型。

示例:

DECLARE

  v_timestamp TIMESTAMP;

BEGIN

  v_timestamp := TO_TIMESTAMP('01-1-03');

  dbms_output.put_line('日期时间值:'||v_timestamp);

END;

 

(21)TO_TIMESTAMP_TZ(char[fmt[,’nls_param’]]):该函数是Oracle 9i新增加的函数,用于将符合特定日期和时间格式的字符串转变为TIMESTAMP WITH TIME ZONE类型。

示例:

SELECT TO_TIMESTAMP_TZ('2003-01-01','YYYY-MM-DD')

FROM dual;

(22)TO_YMINTERNAL(char):该函数是Oracle 9i新增加的函数,用于将字符串转变为INTERVAL YEAR TO MONTH类型。

示例:

DECLARE

  v_date DATE;

BEGIN

  v_date := SYSDATE+TO_YMINTERVAL('01-01');

  dbms_output.put_line('当前日期后的11个月:'||v_date);

END;

 

(23)TRUNC(d,[fmt]):该函数用于截断日期时间数据。如果fmt指定年度,则结果为本年度的11;如果fmt指定月,则结果为本月1日。

示例:

SELECT TRUNC(SYSDATE,'MONTH') FROM dual;

 

(24)TO_OFFSET(time_zone_name||SESSIONTIMEZONE||DBTIMEZONE):该函数是Oracle 9i新增加的函数,用于返回特定时区与UTC(格林威治)相比的时区偏移。

示例:

SELECT TZ_OFFSET('EST') FROM dual;

 

 
展开阅读全文

没有更多推荐了,返回首页