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

/*
 * 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必须大于0
declare
   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或SECOND
DECLARE
  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或MONTH
select 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类型时，字符串用单引号括起来，前面加上n
DECLARE
  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类型时，字符串需用单引号括起来，且在前面加上n
select 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_view
where UNDER_PATH(res,'/sys/schemas/OE',1)=1
AND 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的URL
select sys_dburigen(ename) from emp
where 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)) Employee
from emp where deptno=1;

select * from emp;

--XMLFOREST(value_expr1[,value_expr2],...):用于返回XML块
select xmlelement("Employee",xmlforest(ename,sal)) from emp
where 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_information
group 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_samp
from sales s,times t
where s.time_id = t.time_id and t.calendar_year=1998
group 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_addresses
select 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;