SQL函数

SQL函数

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

 

一． 数字函数

数字函数的输入参数和返回值都是数字型，并且多数函数精确到38位。函数COS、COSH、EXP、LN、LOG、SIN、SINH、SQRT、TAN和TANH精确到36位，而函数ACOS、ASIN、ATAN和ATAN2则精确到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)：该函数用于返回e的n次幂(e=2.71828183…)。

示例：

DECLARE

  v_exp NUMBER(6,2);

BEGIN

  v_exp := exp(4);

  dbms_output.put_line('e的4此幂：'||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可以是除0和1以外的任何正整数，数字n可以是任何正整数。

示例：

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

 

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

示例：

DECLARE

  v_mod NUMBER(6,2);

BEGIN

  v_mod := MOD(10,3);

  dbms_output.put_line('10除3的余数：'||v_mod);

END;

 

(14) POWER(m,n)：该函数用于返回数字m的n次幂，底数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必须为正整数，并且n和m的默认值为1。

示例：

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

 

(6)    LENGTH(char)：该函数用于返回字符串的长度。如果字符串的类型为CHAR，则其长度包括所有的后缀空格；如果char是null，则返回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，直至字符串总长度为n，char2的默认值为空格。如果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用于指定NCHAR或NVARCHAR2类型字符串，其前面加上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_string为null，则会去掉指定子串；如果search_string为null，则返回原有字符串。

示例：

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

 

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

 

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

示例：

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

 

(19) SUBSTR(char,m[,n])：该函数用于取得字符串的子串，其中数字m是字符开始位置，数字n是子串的长度。如果m为0，则从首字符开始；如果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_string和to_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;

 

三． 日期时间函数

日期时间函数用于处理DATE和TIMESTAMP类型的数据。除了函数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)：该函数用于返回日期d1和d2之间相差的月数。如果d1小于d2，则返回负数。如果日期d1和d2的天数相同或都是月底，则返回整数；否则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,MINUTE或SECOND。

示例：

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可以是YEAR或MONTH。

示例：

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

 

(14) ROUND(d[,fmt])：该函数用于返回日期时间的四舍五入结果。如果fmt指定年度，则 7 月 1 日 为分界线；如果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('当前日期后的1年1个月：'||v_date);

END;

 

(23) TRUNC(d,[fmt])：该函数用于截断日期时间数据。如果fmt指定年度，则结果为本年度的 1 月 1 日 ；如果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;