精通Oracle10编程SQL(5)SQL函数

数据库  /  管理员 发布于 5年前   341

/* * SQL函数*/--数字函数--ABS(n):返回数字n的绝对值declare  v_abs number(6,2);begin  v_abs:=abs(&no);  dbms_output.put_line('绝对值：'||v_abs);end;--ACOS(n):返回数字n的反余弦值，输入值的范围是-1~1，输出值的单位为弧度。select acos(.3),acos(-.3) from dual;--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;--ATAN(n):返回数字n的反正切值，输入值可以是任何数字，输出值的单位为弧度。select atan(10.3),atan(-20.3) from dual;--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;--CEIL(n):返回大于等于数字n的最小整数select ceil(15),ceil(15.1) 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) "0的双曲余弦值" from dual;--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;--FLOOR(n):返回小于等于数字n的最大整数select floor(15),floor(15.1) from dual;--LN(n):返回数字n的自然对数，其中数字n必须大于0declare   v_ln number(6,2);begin   v_ln:=ln(4);   dbms_output.put_line('4的自然对数：'||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(10,3);   dbms_output.put_line('10除3的余数：'||v_mod);end;--POWER(m,n):返回数字m的n次幂，底数m和指数n可以是任意数字。但如果数字m为负数，则数字n必须是正数。select power(-2,3),power(2,-1) from dual;--ROUND(n,[m]):执行四舍五入运算declare   v_round number(6,2);begin   v_round:=round(&no,1);   dbms_output.put_line('四舍五入到小数点后一位：'||v_round);end;--SIGN(n):用于检测数字的正负。如果数字n小于0，则函数的返回值为-1，如果数字n等于0，则函数的返回值为0，如果数字n大于0，则函数的返回值为1.select sign(-10),sign(0),sign(20) from dual;--SIN(n)：返回数字n（以弧度表示的角）的正弦值。declare  v_sin number(6,2);begin  v_sin:=sin(0.3);  dbms_output.put_line('0.3的正弦值：'||v_sin);end;--SINH(n):返回数字n的双曲正弦值select sinh(0.5) from dual;--SQRT(n):返回数字n的平方根，并且数字n必须大于等于0.declare  v_sqrt number(6,2);begin  v_sqrt:=sqrt(10);  dbms_output.put_line('10的平方根：'||v_sqrt);end;--TAN(n):返回数字n（以弧度表示的角）的正切值。select tan(45*3.14159265359/180) from dual;--TANH(n):返回数字n的双曲正切值。declare   v_tanh number(6,2);begin   v_tanh:=tanh(10);   dbms_output.put_line('10的双曲正切值：'||v_tanh);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;--字符函数--ASCII(char):返回字符串首字符的ASCII码值select ascii('a') "a",ascii('A') "A" from dual;--CHR(n):将ASCCI码值转变为字符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):用于返回字符串的长度。如果字符串的长度。如果字符串的类型为CARH，则其长度包括所有的后缀空格，如果char是null。declare  v_len int;begin  v_len:=length('my word');  dbms_output.put_line('字符串长度：'||v_len);end;--LOWER(char):将字符串转换为小写格式select lower('SQL introduction') from dual;--LPAD(char1,n,char2):用于在字符串char1的左端填充字符串char2,直至字符串总长度为n,char2的默认值为空格。--如果char1长度大于n，则该函数返回char1左端的n个字符declare  v_lpad varchar2(10);begin  v_lpad:=lpad('aaa',10,'*');  dbms_output.put_line('在字符串左端添加字符*:'||v_lpad);end;--LTRIM(char1[,set]):用于去掉字符串char1左端所包含的set中的任何字符。select ltrim('morning','m'),ltrim('morning','or') from dual;--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;--NLS_LOWER(char,'nls_param'):该函数用于将字符串转变为小写，其中nls_param的格式为"nls_sort=sort",用于指定特定语言特征。select nls_lower(n'SQL') from dual;--NLS_SORT(char,'nls_param'):该函数用于按照特定语言的要求进行排序，其中nls_param的格式为"nls_sort=sort",用于指定特定语言特征select * from emp order by NLSSORT(ename,'NLS_SORT=XDanish');select * from emp order by ename;--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;--REGEXP_REPLACE(source_string,pattern[,replace_string[,position[,occurrence[,match_parameter[[[[):扩展了REPLACE的功能，用于按照特定表达式的规则替换字符串。select REGEXP_REPLACE(country_name,'(.)','\l ') "REGEXP_REPLACE" FROM countries;--REGEXP_SUBSTR(source_string,pattern[,position[,occurrence[,match_parameter]]]):扩展了函数SUBSTR的功能，并且用于按照特定表达式的规则返回字符串的子串。select REGEXP_SUBSTR('http://www.oracle.com/products','http://([[:alnum:]]+\.?){3,4}/?') "REGEXP_SUBSTR" FROM DUAL;--REPLACE(char,search_string[,replacement_string]):将字符串的子串替换为其他子串。--如果replacement_string为null，则会去掉指定子串--如果search_string为null，则返回原有字符串select replace('缺省值为10','缺省','默认') from dual;--RPAD(char1,n,char2):用于在字符串char1的右端填充字符串char2,直至字符串的总长度为n,char2的默认值为空格。declare  v_rpad varchar2(10);begin  v_rpad:=rpad('aaaa',10,'*');  dbms_output.put_line('在右端添加字符：'||v_rpad);end;--RTRIM(char,[,set]):去掉字符串char右端所包含的、set中的任何字符。select rtrim('morning','ing') from dual;select rtrim('morning','ingr') from dual;--SOUNDEX(char):用于返回字符串的语音表示，使用该函数可以比较发音相同的字符串select soundex('ship'),soundex('sheep') from dual;--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;--TRANSLATE(char,from_string,to_string):用于将字符串char的字符按照from_string和to_string的对应关系进行转换select TRANSLATE('2KRW229','0123456789ABCDEFGHIJKLMNOPQRSTUVWXYZ','9999999999XXXXXXXXXXXXXXXXXXXXXXXXXX') "TRANS" from DUAL;--TRIM(char FROM 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;--UPPER(char):用于将字符串转换为大写格式select upper('sql') from dual;--日期时间函数--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;--CURRENT_DATE:返回当前会话时区所对应的日期时间alter session set time_zone = '-5:0';alter session set nls_date_format='YYYY-MM-DD HH24:MI';select current_date from dual;--CURRENT_TIMESTAMP:返回当前会话时区的日期时间select current_timestamp from dual;--DBTIMESONE:返回数据库所在时区。DECLARE  v_zone VARCHAR2(10);BEGIN  v_zone:=dbtimezone;  dbms_output.put_line('当前数据库时区：'||v_zone);end;--EXTRACT:用于从日期时间值中取得所需要的特定数据（例如取得年份、月份等）select extract(YEAR FROM sysdate) year FROM dual;select extract(month from sysdate) month from dual;select extract(day from sysdate) day from dual;--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;--LAST_DAY(d):用于返回特定日期所在月份的最后一天select last_day(sysdate) from dual;--LOCALTIMESTAMP:返回当前会话时区的日期时间DECLARE  v_ts VARCHAR2(100);BEGIN  v_ts:=localtimestamp;  dbms_output.put_line('当前日期时间:'||v_ts);END;--MONTHS_BETWEEN(d1,d2):返回日期d1和d2之间相差的月数。--如果d1和d2的天数相同或都是月底，则返回整数，否则ORACLE以每月31天为准来计算结果的小数部分。select months_between(sysdate,'27-8月-2009') from dual;--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('2009-11-10 12:10:00','YYYY-MM-DD HH24:MI:SS'),'BST','EST');  dbms_output.put_line('当前日期时间：'||v_time);end;--NEXT_DAY(d,char):用于返回指定日期后的第一个工作日（由char指定）所对应的日期select next_day(sysdate,'星期一') from dual;select next_day(sysdate,'星期三') from dual;--NUMTODSINTERNAL(n,char_expr):将数字n转换为INTERVAL DAY TO SECOND格式，其中char_expr可以是DAY，HOUR，MINUTE或SECONDDECLARE  v_date VARCHAR2(100);BEGIN  v_date:=numtodsinterval(100,'MINUTE');  dbms_output.put_line('100分钟对应的时间:'||v_date);end;--NUMTOYMINTERNAL(n,char_expr):将数字n转换为INTERVAL YEAR TO MONTH格式，其中char_expr可以是YEAR或MONTHselect numtoyminterval(100,'MONTH') as year_month from dual;--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;--SESSIONTIMEZONE:返回当前会话所在时区select sessiontimezone from dual;--SYS_EXTRACT_UTC(datetime_with_timezone):返回特定时区时间所对应的格林威治时间DECLARE   v_timestamp TIMESTAMP;BEGIN   v_timestamp:=SYS_EXTRACT_UTC(SYSTIMESTAMP);   dbms_output.put_line('格林威治时间：'||v_timestamp);END;--SYSDATE:返回当前系统的日期时间select sysdate from dual;--SYSTIMESTAMP:返回当前系统的日期时间及时区DECLARE  v_timestamp varchar2(100);begin  v_timestamp:=SYSTIMESTAMP;  dbms_output.put_line('当前系统时间及时区：'||v_timestamp);end;--TO_DSINTERNAL(char[,'nls_param']):将符合特定日期和时间格式的字符串转变为INTERVAL DAY TO SECOND类型--SELECT to_dsinterval('58:10:10') FROM dual;--TO_TIMESTAMP(char[fmt[,'nls_param']]):将符合特定日期和时间格式的字符串转变为TIMESTAMP类型DECLARE  v_timestamp TIMESTAMP;BEGIN  v_timestamp:=TO_TIMESTAMP('01-1月-03');  dbms_output.put_line('日期时间值：'||v_timestamp);END;--TO_TIMESTAMP_TZ(char[fmt[,'nls_param']]):将符合特定日期和时间格式的字符串转变为TIMESTAMP WITH TIME ZONE类型select to_timestamp_tz('2003-01-01','YYYY-MM-DD') FROM dual;--TO_YMINTERNAL(char):将字符串转变为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;--TRUNC(d,[fmt]):用于截断日期时间数据，如果fmt指定年度，则结果为本年度的1月1日，如果fmt指定月，则结果为本月1日select trunc(sysdate,'MONTH') from dual;select trunc(sysdate,'YEAR') from dual;--TZ_OFFSET(time_zone_name||SESSIONTIMEZONE||DBTIMEZONE):返回特定时区与UTC（格林威治）相比的时区偏移select tz_offset('EST') from dual;--转换函数--ASCIISTR(string):将任意字符集的字符串转变为数据库字符集的ASCII字符串select ASCIISTR('中国') from dual;--BIN_TO_NUM(expr[,expr][,expr]...):将位向量值转变为实际的数字值select bin_to_num(1,0,1,1,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;--CHARTOROWID(char):将字符串值转变为ROWID数据类型，但字符串值必须符合ROWID格式select CHARTOROWID('AAADd1AAFAAAABSAA/') FROM dual;--COMPOSE(string):将输入字符串转变为UNICODE字符串值select COMPOSE('O'||unistr('\0308')) 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;--DECOMPOSE(string):用于分解字符串并返回相应的UNICODE字符串select DECOMPOSE('Chateaux') FROM dual;--HEXTORAW(char):用于将十六进制字符串转变为RAW数据类型DECLARE  v_raw RAW(20);BEGIN  v_raw:=HEXTORAW('AB56FA2C');  DBMS_OUTPUT.put_line('转换结果：'||v_raw);END;--RAWTOHEX(raw):将RAW数值转变为十六进制字符串DECLARE  v_char VARCHAR2(20);BEGIN  v_char:=RAWTOHEX('AB56FA2C');  dbms_output.put_line('转换结果：'||v_char);END;--RAWTONHEX(raw):将RAW数值转变为NVARCHAR2的十六进制字符串select rawtonhex('7D') from dual;--ROWIDTOCHAR(rowid):将ROWID值转变为VARCHAR2数据类型DECLARE  v_char VARCHAR2(20);BEGIN  v_char:=ROWIDTOCHAR('AAAFfIAAFAAAABSAAb');  dbms_output.put_line('转换结果：'||v_char);END;--ROWIDTONCHAR(rowid):将ROWID值转变为NVARCHAR2数据类型select rowidtonchar('AAAFfIAAFAAAABSAAb') from dual;--SCN_TO_TIMESTAMP(number):根据输入的SCN值返回所对应的大概日期时间，其中number用于指定SCN值。select scn_to_timestamp(ORA_ROWSCN) FROM EMP WHERE empno=7788;--TIMESTAMP_TO_SCN(timestamp):根据输入的TIMESTAMP返回所对应的SCN值，其中timestamp用于指定日期时间select TIMESTAMP_TO_SCN(sysdate) from dual;--TO_CHAR(character):将NCHAR，NVARCHAR2，CLOB和NCLOB数据转变为数据库字符集数据当用于NCHAR，NVARCHAR2和NCLOB类型时，字符串用单引号括起来，前面加上nDECLARE  v_char VARCHAR2(20);BEGIN  v_char:=TO_CHAR(n'中华人民共和国');  dbms_output.put_line('转换结果：'||v_char);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数据类型DECLARE   v_char VARCHAR2(20);BEGIN   v_char:=TO_CHAR(-10000,'L99G999D99MI');   dbms_output.put_line('转换结果：'||v_char);END;--TO_CLOB(char):将字符串转变为CLOB类型。--char参数使用NCHAR，NVARCHAR2和NCLOB类型时，字符串需用单引号括起来，且在前面加上nselect to_clob(n'中华人民共和国') from dual;--TO_DATE(char[,fmt[,nls_param]]):将符合特定日期格式的字符串转变为DATE类型的值DECLARE  v_date DATE;BEGIN  v_date:=to_date('01-01-2001','DD-MM-YYYY');  dbms_output.put_line('转换结果：'||v_date);END;--TO_LOB(long_column):将LONG或LONG RAW列的数据转变为相应的LOB类型insert into a(clob_column)select to_lob(long_column) from b;--TO_MULTI_BYTE(char):将单字节字符串转变为多字节字符串DECLARE   v_multi VARCHAR2(10);BEGIN   v_multi:=TO_MULTI_BYTE('abcd');   dbms_output.put_line('转换结果：'||v_multi);END;--TO_NCHAR(character):将字符串由数据库字符集转变为民族字符集select to_nchar('伟大的中国') from dual;--TO_NCHAR(datetime,[fmt[,nls_param]]):将日期时间值转变为民族字符集的字符串DECLARE  v_nchar NVARCHAR2(30);BEGIN  v_nchar:=to_nchar(SYSDATE);  DBMS_OUTPUT.PUT_LINE('转换结果：'||v_nchar);END;--TO_NCHAR(number):将数字值转变为民族字符集的字符串select to_nchar(10) from dual;--TO_NCLOB(clob_column|char):将CLOB列或字符串转变为NCLOB类型DECLARE   v_nclob NCLOB;BEGIN   v_nclob:=to_nclob('伟大的祖国');   DBMS_OUTPUT.PUT_LINE('转换结果：'||v_nclob);END;--TO_NUMBER(char,[fmt[,nls_param]]):将符合特定数字格式的字符串值转变为数字值--select to_number('RMB1000.00','L99999D99') from dual;--TO_SINGLE_BYTE(char):将多字节字符集数据转变为单字节字符集DECLARE  v_single VARCHAR2(10);BEGIN  v_single:=to_single_byte('abcd');  dbms_output.put_line('转换结果：'||v_single);END;--TRANSLATE...USING:将字符串转变为数据库字符集（CHAR_CS)或者民族字符集（NCHAR_CS)select translate('中国' USING NCHAR_CS) FROM dual;--UNISTR(string):用于输入字符串并返回相应的UNICODE字符DECLARE  v_unicode VARCHAR2(10);BEGIN  v_unicode:=UNISTR('\00D6');  dbms_output.put_line('UNICODE字符:'||v_unicode);END;--集合函数--CARDINALITY(nested_table):返回嵌套表的实际元素个数select product_id,CARDINALITY(ad_textdocs_ntab) from print_media;--COLLECT(column):根据输入列和被选择行建立嵌套表结果create type phone_book_t AS TABLE OF phone_list_typ;select CAST(COLLECT(phone_numbers) AS phone_book_t)from customers;--POWERMULTISET(expr):用于生成嵌套表的超集（包含所有非空的嵌套表）CREATE TYPE cust_address_tab_tab_typ as TABLE OF cust_address_tab_typ;SELECT CAST(POWERMULTISET(cust_address_ntab) AS cust_address_tab_tab_typ) FROM customers_demo;--POWERMULTISET_BY_CARDINALITY(expr,cardinatility):用于根据嵌套表和元素个数，生成嵌套表的超集（包含所有非空的嵌套表）update customers_demo set cust_address_ntab = cust_address_ntab MULTISET UNION cust_address_ntab;select CAST(POWERMULTISET_BY_CARDINALITY(cust_address_ntab,2) as cust_address_tab_tab_typ) FROM customers_demo;--SET(nested_table):用于取消嵌套表中的重复结果，并生成新的嵌套表select set(cust_address_ntab) address from customers_demo;--其他单行函数--BFILENAME('directory','filename'):用于初始化BFILE定位符，其中directory是与OS路径相关的DIRECTORY对象，filename是OS文件的名称DECLARE  v_content VARCHAR2(100);  v_bfile BFILE;  amount INT;  offset INT:=1;BEGIN  v_bfile:=bfilename('USER_DIR','a.txt');  amount:=dbms_lob.getlength(v_bfile);  dbms_lob.fileopen(v_bfile);  dbms_lob.read(v_bfile,amount,offset,v_content);  dbms_lob.fileclose(v_bfile);  dbms_output.put_line(v_content);END;--COALESCE(expr1[,expr2][,expr3]...):返回表达式列表中第一个NOT NULL表达式的结果DECLARE  v_expr1 INT;  v_expr2 INT:=100;  v_expr3 INT:=1000;  v_nn INT;BEGIN  v_nn:=COALESCE(v_expr1,v_expr2,v_expr3);  dbms_output.put_line(v_nn);END;--DECODE(expr,search,result[,search2,result2,...][,default]):返回匹配于特定表达式的结果。--如果search1匹配expr,则返回result1;如果search2匹配expr,则返回result2,依次类推，如果没有任何匹配关系，则返回default.select deptno,ename,sal,decode(deptno,1,sal*1.2,2,sal*1.1,sal) "New Salary"from emp ORDER BY deptno;--DEPTH(n):返回XML方案中UNDER_PATH路径所对应的相对层数。其中参数n用于指定相对层数select PATH(1),DEPTH(2) FROM resource_viewwhere UNDER_PATH(res,'/sys/schemas/OE',1)=1AND UNDER_PATH(res,'/sys/schemas/OE',2)=1;--DUMP(expr,return_fmt):返回表达式所对应的数据类型代码、长度以及内部表示格式，其中return_fmt用于指定返回格式(8:八进制符号，10：十进制符号，16：十六进制符号，17，单字符）。--该函数只能在SQL语句中使用select dump('hello',1016) from dual;--EMPTY_BLOB():用于初始化BLOB变量DECLARE  v_lob BLOB;BEGIN  v_lob:=empty_blob();END;--EMPTY_BLOB():用于初始化BLOB变量select * from person;update person set resume=EMPTY_CLOB() WHERE id=1;--EXISTSNODE(XMLType_instance,Xpath_string):用于确定XML节点路径是否存在，返回0表示节点不存在，返回1表示节点存在select existsnode(VALUE(P),'/PurchaseOrder/User') from xmltable p;--EXTRACT(XMLType_instance,Xpath_string):用于返回XML节点路径下的相应内容select extract(value(p),'/PurchaseOrder/User')from xmltable p;--EXTRACTVALUE(XMLType_instance,Xpath_string):用于返回XML节点路径下的值select extractvalue(value(p),'/PurchaseOrder/User') from xmltable p;--GREATEST(expr1[,expr2]...):返回列表表达式expr1,expr2,...中值最大的一个，在比较之前，expr2等项会被隐含地转换为expr1的数据类型select GREATEST('BLACK','BLANK','BACK') FROM dual;--LEAST(expr[,expr]...):返回列表表达式expr1,expr2,...中值最小的一个，在比较之前，expr2等项会被隐含地转换为expr1的数据类型select least('BLACK','BLANK','BACK') from dual;--NLS_CHARSET_DECL_LEN(byte_count,charset_id):返回字节数在特定字符集中占用的字符个数select NLS_CHARSET_DECL_LEN(200,nls_charset_id('ZHS16GBKFIXED')) FROM dual;--NLS_CHARSET_ID(text):用于返回字符集的ID号select nls_charset_id('ZHS16GBKFIXED') from dual;--NLS_CHARSET_NAME(number):返回特定ID号所对应的字符集名select nls_charset_name(852) from dual;--NULLIF(expr1,expr2):用于比较表达式expr1和expr2.如果二者相等，则返回NULL，否则返回expr1.declare  v_expr1 INT:=100;  v_expr2 INT:=100;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--参数expr1和expr2可以是任意数据类型，但二者数据类型必须要匹配select ename,sal,comm,sal+nvl(comm,0) salary from emp where deptno=3;--NVL2(expr1,expr2,expr3):用于处理NULL--如果expr不是null，则返回expr2，如果expr1是null，则返回expr3，参数expr1可以是任意数据类型，而expr2和expr3可以是除LONG之外的任何数据类型select ename,sal,comm,nvl2(comm,sal+comm,sal) salary from emp where deptno=3;--PATH(correction_integer):用于返回特定XML资源所对应的相对路径select PATH(1),DEPTH(2) from resource_view where UNDER_PATH(res,'/sys/schemas/OE',1) = 1 and UNDER_PATH(res,'/sys/schemas/OE',2)=1;--SYS_CONNECT_BY_PATH(column,char):返回从根到节点的列值路径select LPAD(' ',2*level-1) || SYS_CONNECT_BY_PATH(ename,'/') "Path"from emp start with ename='SCOTT'connect by PRIOR empno=mgr;--SYS_CONTEXT('context','attribute'):返回应用上下文的特定属性值，其中context为应用上下文名，而attribute则用于指定属性名select sys_context('userenv','session_user') "数据库用户",sys_context('userenv','os_user') "OS用户"from dual;--SYS_DBURIGEN:根据列或属性生成类型为DBUriType的URLselect sys_dburigen(ename) from empwhere empno=7788;--SYS_GUID:用于生成类型为RAW的16字节的惟一标识符，每次调用该函数都会生成不同的RAW数据select sys_guid() from dual;--SYS_TYPEID(object_type_value):返回惟一的类型ID值--select name,SYS_TYPEID(VALUE(p)) "Type_id" FROM person p;--SYS_XMLAGG(expr[,fmt]):用于汇总所有XML文档，并生成一个XML文档select sys_xmlagg(SYS_XMLGEN(ename)) from emp where deptno=1;select ename from emp where deptno=1;--SYS_XMLGEN(expr[,fmt]):根据数据库表的行和列生成一个XMLType实例select sys_xmlgen(ename) from emp where deptno=1;--UID:用于返回当前会话所对应的用户ID号DECLARE  v_uid INT;BEGIN  v_uid:=UID;  DBMS_OUTPUT.put_line('会话用户ID号：'||v_uid);END;--UPDATEXML(XMLType_instance,Xpath_string,value_expr):用于更新特定XMLType实例相应节点路径的内容。UPDATE xmltable p set p=UPDATEXML(value(p),'/PurchaseOrder/User/text()','SCOTT');--USER：用于返回当前会话所对应的数据库用户名DECLARE  v_user VARCHAR2(10);BEGIN  v_user:=USER;  DBMS_OUTPUT.put_line('会话用户：'||v_user);END;--USERENV(parameter):返回当前会话上下文的属性信息，其中parameter有以下取值select USERENV('LANGUAGE'),userenv('isdba'),userenv('terminal'),userenv('client_info') FROM dual;--VSIZE(expr):用于返回ORACLE内部存储expr的实际字节数，如果expr是null，则该函数返回null.select ename,vsize(ename) from emp where deptno=2;--XMLAGG(XMLType_instance[ORDER BY sort_list]):用于汇总多个XML块，并生成XML文档select xmlagg(xmlelement("employee",ename||' '||sal)) from emp where deptno=1;--XMLCOLATTVAL(value_expr1[,value_expr2],..):用于生成XML块，并增加"column"作为属性名select xmlelement("emp",xmlcolattval(ename,sal)) from emp where empno=7788;--XMLCONCAT(XMLType_instance1[,XMLType_instance2],..):用于连接多个XMLType实例，并生成一个新的XMLType实例select xmlconcat(xmlelement("ename",ename),xmlelement("sal",sal))from emp where deptno=1;--XMLELEMENT(identifier[,xml_attribute_clause][,value_expr]):用于返回XMLType的实例，其中参数identifier（必须）用于指定元素名，--参数xml_attribute_clause（可选）用于指定元素属性子句，参数value_expr(可选）用于指定元素值select xmlelement("DATE",sysdate) from dual;select xmlelement("Emp",xmlattributes(empno AS "ID",ename)) Employeefrom emp where deptno=1;select * from emp;--XMLFOREST(value_expr1[,value_expr2],...):用于返回XML块select xmlelement("Employee",xmlforest(ename,sal)) from empwhere empno=7788;--XMLSEQUENCE(xmltype_instance):返回XMLType实例中顶级节点以下的VARRAY元素select xmlsequencetype(extract(value(x),'/PurchaseOrder/LineItems/*')) varry FROM xmltable x;--XMLTRANSFORM(xmltype_instance,xsl_ss):将XMLType实例按照XSL样式进行转换，并生成新的XMLType实例select XMLTRANSFORM(w.warehouse_spec,x.coll).GetClobVal() from warehouses w,xsl_tab x where w.warehouse_name='San Francisco';--分组函数--AVG（[ALL|DISTINCT|expr):用于计算平均值DECLARE   v_avg NUMBER(6,2);BEGIN   SELECT AVG(SAL) INTO v_avg FROM emp;   dbms_output.put_line('雇员平均工资：'||v_avg);END;--CORR(expr1,expr2):用于返回成对数值的相关系数，其数值使用表达式"COVAR_POP(expr1,expr2)/(STDDEV_POP(expr1)*STDDEV_POP(expr2))"获得select weight_class,corr(list_price,min_price)from product_informationgroup by weight_class;--COUNT([ALL|DISTINCT]expr):用于返回总计行数select count(distinct sal) from emp;--COVAR_POP(expr1,expr2):用于返回成对数字的协方差（Covariance）,其数值使用表达式"(sum(expr1*expr2)-sum(expr1)*sum(expr2)/n)/n"取得select t.calendar_month_number,COVAR_POP(s.amount_sold,s.quantity_sold) AS covar_pop,COVAR_SAMP(s.amount_sold,s.quantity_sold) as covar_sampfrom sales s,times twhere s.time_id = t.time_id and t.calendar_year=1998group by t.calendar_month_number;--COVAR_SAMP(expr1,expr2):返回成对数字的协方差，其数值使用表达式"(sum(expr1*expr2)-sum(expr1)*sum(expr2)/n)/(n-1)"取得--CUME_DIST(expr1,expr2,...) WITHIN GROUP(ORDER BY expr1,expr2,...):返回特定数值在一组行数据中的累积分布比例select cume_dist(100) within group(order by sal) "CUME-DIST" FROM emp;--DENSE_RANK(expr1,expr2,...) WITHIN GROUP(ORDER BY expr1,expr2,...):返回特定数据在一组行数据中的等级select dense_rank(50) within group (order by sal) rank 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;--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);--GROUPING_ID(expr1[,expr2],...):用于返回对应于特定行的GROUPING位向量的值select deptno,job,sum(sal),grouping_id(job,deptno)from emp group by rollup(deptno,job);--LAST:通过使用该函数，可以取得排序等级的最后一级，然后使用分组函数汇总该等级的数据select min(sal) KEEP (dense_rank last order by comm) "补助最高级别雇员的最低工资",max(sal) KEEP (dense_rank last order by comm) "补助最高级别雇员的最高工资"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;--PERCENT_RANK(expr1,expr2,...) WITHIN GROUP (ORDER BY expr1,expr2,...):用于返回特定数值在统计级别中所占的比例select percent_rank(300) within group(order by sal)percent from emp;--PERCENTILE_CONT(percent_expr) WITH 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_disc(.6) within group(order by sal) value from emp;--RANK(expr1,expr2,...) WITHIN GROUP(ORDER BY expr1,expr2,...):用于返回特定数值在统计数值中所占据的等级select rank(30) within group(order by sal) rank from emp;--STDDEV([ALL|DISTINCT]expr):用于取得标准偏差，其数值是按照方差VARIANCE的平方根取得的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(expr*expr)-sum(expr)*sum(expr)/COUNT(expr))/COUNT(expr)"取得select var_pop(sal) from emp;--VAR_SAMP(expr):返回采样方差，其数值使用公式"sum(expr*expr)-sum(expr)*sum(expr)/COUNT(expr))/(COUNT(expr)-1)"取得select var_samp(sal) from emp;--VARIANCE([ALL|DISTINCT]expr):返回列或表达式的方差，其数值与VAR_SAMP完全相同select variance(sal) from emp;--对象函数create type cust_address_typ_new as object(street_address VARCHAR2(40),postal_code VARCHAR2(10),city VARCHAR2(30),state_province VARCHAR2(10),country_id CHAR(2));create table address_table of cust_address_typ_new;create table customer_addresses(add_id NUMBER,address REF cust_address_typ_new SCOPE is address_table);insert into address_table VALUES('1 First','G45 EU8','Paris','CA','US');insert into address_table VALUES('招商路2号','422918','Beijing','CHAIN','CH');insert into customer_addressesselect 999,REF(a) from address_table a;commit;select * from address_table;--DEREF(expr):返回参照对象expr所引用的对象实例select deref(address).city from customer_addresses;--MAKE_REF(object_table|object_view,key):基于对象视图的一行数据或基于对象表（存在基于主键的对象标识符）的一行数据建立REF--select MAKE_REF(oc_inventories,3003) from dual;--REF(expr):用于返回对象行所对应的REF值select ref(e) from address_table e;--REFTOHEX(expr):用于将REF值转变为十六进制字符串select reftohex(ref(e)) from address_table e;--VALUE(expr):用于返回行对象所对应的对象实例数据，其中expr用于指定行对象的别名select value(e).city from address_table e;