`

精通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;

 

分享到:
评论

相关推荐

    精通Oracle10编程SQL(1-3)PLSQL基础

    进一步深入,"精通Oracle10编程SQL(5)SQL函数.sql"涵盖了SQL函数的使用,包括算术函数(如SUM、AVG)、字符串函数(如CONCAT、SUBSTR)、日期函数(如SYSDATE、ADD_MONTHS)以及转换函数(如TO_CHAR、TO_DATE),...

    精通Oracle 10g SQL和PL SQL.zip

    本资源“精通Oracle 10g SQL和PL SQL.zip”提供了全面的学习指南,帮助用户从基础到高级进阶,掌握这两个重要组件的精髓。 SQL(Structured Query Language)是用于管理关系数据库的标准语言,它允许用户创建、查询...

    《精通Oracle10编程》 PDF

    《精通Oracle10编程》是一本专为数据库管理员和开发者设计的专业书籍,旨在深入解析Oracle 10g数据库系统的各种核心技术和高级特性。Oracle 10g是Oracle公司推出的一个重要版本,它在性能、可扩展性和管理性方面都有...

    精通Oracle PLSQL编程

    PL/SQL(Procedural Language/Structured Query Language)是Oracle数据库提供的一个过程化语言,它结合了SQL的查询功能和传统的过程式编程语言的控制结构,使得数据库管理和应用程序开发更加高效和灵活。...

    Oracle 11g SQL和PL SQL从入门到精通 pdf格式电子书 下载(一)

     第19章 pl/sql函数  第20章 pl/sql包  第21章 触发器  第22章 使用对象类型 第四部分 pl/sql系统包  第23章 使用大对象  第24章 读写os文件  第25章 开发多媒体应用  第26章 开发web应用  第27章 dbms_sq...

    《精通Oracle PL/SQL》源码

    Oracle PL/SQL是一种强大的编程语言,它结合了SQL(结构化查询语言)的数据库操作能力和PL(过程化语言)的程序设计特性,是Oracle数据库系统中的核心组件之一。《精通Oracle PL/SQL》这本书深入探讨了这个语言的...

    精通Oracle10g SQL/PL编程.PDF

    《精通Oracle10g SQL/PL编程》是一本专为数据库开发者和管理员设计的专业书籍,旨在深入探讨Oracle10g数据库管理系统中的SQL和PL/SQL编程技术。这本书以通俗易懂的方式,为初学者提供了全面的学习路径,同时也为有...

    《精通Oracle SQL 第2版》PDF版本下载.txt

    根据提供的文件信息,我们可以推断出这是一本关于Oracle SQL的专业书籍——《精通Oracle SQL 第2版》。接下来,我们将围绕这一主题展开深入探讨,包括Oracle SQL的基本概念、本书的主要内容以及学习Oracle SQL的一些...

    精通Oracle_SQL(第2版)含源码

    《精通Oracle SQL(第2版)》是一本深入解析Oracle数据库管理系统中SQL语言的专业书籍,旨在帮助读者全面理解和掌握在Oracle环境下高效、精准地运用SQL进行数据查询、操作和管理的技能。书中不仅包含了基础的SQL语法...

    精通OracleSQL第2版.zip

    《精通Oracle SQL(第2版)》是一本深入解析Oracle数据库查询语言的专业书籍,由Oracle ACE和OakTable团队的专家共同撰写,集成了他们的丰富经验和专业知识。这本书旨在帮助读者掌握Oracle SQL的高级技巧,提升在...

    精通oracle10编程 教程 +pl/sql pdf

    本教程的"精通Oracle10编程"部分,将深入讲解如何使用PL/SQL进行数据库交互,包括变量声明、流程控制、异常处理和游标等基本概念。 在PL/SQL编程中,了解SQL DML语句(INSERT、UPDATE、DELETE)是基础,它们用于...

    Oracle SQL高级编程

    由于标题和描述是重复的且没有提供实质性的内容,我们无法从中得知具体的Oracle SQL高级编程知识点。但是,从标题我们可以推测该文档可能是关于如何使用Oracle数据库中的SQL语言进行高级编程。Oracle数据库是一个...

    精通ORACLE 10G SQL和PL_SQL

    Oracle 10g是一款强大的关系型数据库管理系统,SQL(Structured Query Language)和PL/SQL是其核心编程语言,用于数据查询、管理和维护。本资源"精通ORACLE 10G SQL和PL_SQL"旨在帮助用户深入理解并熟练掌握这两门...

    ORACLE PL/SQL从入门到精通

    ORACLE PL/SQL是从入门到精通的专业知识,涵盖了数据库开发与管理的多个方面,包括触发器、过程、函数、软件包、异常处理、游标、循环、分支、变量使用、数据库安装等关键知识点。 触发器是数据库中用来保证数据...

    精通Oracle10编程

    《精通Oracle10编程》这本书是为那些希望深入理解和掌握Oracle 10及PL/SQL编程的开发者量身定制的指南。 在Oracle 10中,PL/SQL(Procedural Language/Structured Query Language)是一种结合了SQL的声明式编程与...

    精通Oracle SQL编程全套教程

    这个“精通Oracle SQL编程全套教程”不仅适用于初学者,也适合有一定经验的DBA和开发者,它将全面提升你在Oracle数据库环境中的技能和效率。通过深入学习和实践,你将成为Oracle SQL编程的专家。

    精通Oracle10g PL_SQL编

    本教程《精通Oracle10g PL_SQL编程》旨在帮助学习者深入理解并熟练掌握Oracle 10g中的PL/SQL编程技术。 1. PL/SQL基础 - PL/SQL的结构:包括声明部分、执行部分和异常处理部分。 - 变量和常量:定义、声明及使用...

    精通oracle 10g plsql 编程-学习笔记

    ### 精通Oracle 10g PL/SQL编程学习笔记 #### 一、PL/SQL综述 **1.1 PL/SQL的功能与作用** PL/SQL (Procedural Language for SQL) 是一种专门为Oracle数据库设计的过程化语言,它结合了SQL的数据处理能力与过程化...

Global site tag (gtag.js) - Google Analytics