1.计算一年有多少天1.1.Oraclesql语句实例
select 'Days in 2005: '|| to_char(add_months(trunc(sysdate,'y'),12)-1,'DDD') as report from dual union all select 'Days in 2004: '|| to_char(add_months(trunc( to_date('01-SEP-2004'),'y'),12)-1,'DDD') from dualREPORT-----------------Days in 2005: 365Days in 2004: 3662.查找含有数字和字母的字符串2.1.Oracle sql语句实例
with v as (select 'ClassSummary' strings from dual unionselect '3453430278' from dual unionselect 'findRow 55' from dual unionselect '1010 switch' from dual unionselect '333' from dual unionselect 'threes' from dual)select strings from (select strings, translate( strings, 'abcdefghijklmnopqrstuvwxyz0123456789', rpad('#',26,'#')||rpad('*',10,'*')) translated from v ) x where instr(translated,'#') ＞ 0 and instr(translated,'*') ＞ 03.把整数转换成二进制3.1.sql Oracle语句实例
ENAME SAL SAL_BINARY---------- ----- --------------------SMITH 800 1100100000ALLEN 1600 11001000000WARD 1250 10011100010JONES 2975 101110011111MARTIN 1250 10011100010BLAKE 2850 101100100010CLARK 2450 100110010010SCOTT 3000 101110111000KING 5000 1001110001000TURNER 1500 10111011100ADAMS 1100 10001001100JAMES 950 1110110110FORD 3000 101110111000MILLER 1300 101000101003.2.sql语句实例
select ename, sal, ( select bin from dual model dimension by ( 0 attr ) measures ( sal num, cast(null as varchar2(30)) bin, '0123456789ABCDEF' hex ) rules iterate (10000) until (num[0] ＜= 0) ( bin[0] = substr(hex[cv()],mod(num[cv()],2)+1,1)||bin[cv()], num[0] = trunc(num[cv()]/2) ) ) sal_binary from emp4.标量子查询转换为复合子查询4.1.sql语句实例
select e.deptno, e.ename, e.sal, (select d.dname,d.loc,sysdate today from dept d where e.deptno=d.deptno) from emp e4.2.SELECT列表里的子查询只允许返回一个值4.3.sql语句实例
create type generic_obj as object ( val1 varchar2(10), val2 varchar2(10), val3 date);4.3.1.对象类型4.4.sql语句实例
select x.deptno, x.ename, x.multival.val1 dname, x.multival.val2 loc, x.multival.val3 today from ( select e.deptno, e.ename, e.sal, (select generic_obj(d.dname,d.loc,sysdate+1) from dept d where e.deptno=d.deptno) multival from emp e ) xDEPTNO ENAME DNAME LOC TODAY------ ---------- ---------- ---------- ----------- 20 SMITH RESEARCH DALLAS 12-SEP-2005 30 ALLEN SALES CHICAGO 12-SEP-2005 30 WARD SALES CHICAGO 12-SEP-2005 20 JONES RESEARCH DALLAS 12-SEP-2005 30 MARTIN SALES CHICAGO 12-SEP-2005 30 BLAKE SALES CHICAGO 12-SEP-2005 10 CLARK ACCOUNTING NEW YORK 12-SEP-2005 20 SCOTT RESEARCH DALLAS 12-SEP-2005 10 KING ACCOUNTING NEW YORK 12-SEP-2005 30 TURNER SALES CHICAGO 12-SEP-2005 20 ADAMS RESEARCH DALLAS 12-SEP-2005 30 JAMES SALES CHICAGO 12-SEP-2005 20 FORD RESEARCH DALLAS 12-SEP-2005 10 MILLER ACCOUNTING NEW YORK 12-SEP-20054.5.对象本身是一个标量值，它并不会违反标量子查询的规则5.解析串行化的数据5.1.sql语句实例
STRINGS-----------------------------------entry:stewiegriffin:lois:brian:entry:moe::sizlack:entry:petergriffin:meg:chris:entry:willie:entry:quagmire:mayorwest:cleveland:entry:::flanders:Entry:robo:tchi:ken:5.1.1.sql语句实例
create view V asselect 'entry:stewiegriffin:lois:brian:' strings from dual union allselect 'entry:moe::sizlack:' from dual union allselect 'entry:petergriffin:meg:chris:' from dual union allselect 'entry:willie:' from dual union allselect 'entry:quagmire:mayorwest:cleveland:' from dual union allselect 'entry:::flanders:' from dual union allselect 'entry:robo:tchi:ken:' from dual5.2.sql语句实例
VAL1 VAL2 VAL3--------------- --------------- ---------------moe sizlackpetergriffin meg chrisquagmire mayorwest clevelandrobo tchi kenstewiegriffin lois brianwillie flanders5.2.1.sql语句实例
with cartesian as ( select level id from dual connect by level ＜= 100 ) select max(decode(id,1,substr(strings,p1+1,p2-1))) val1, max(decode(id,2,substr(strings,p1+1,p2-1))) val2, max(decode(id,3,substr(strings,p1+1,p2-1))) val3 from ( select v.strings, c.id, instr(v.strings,':',1,c.id) p1, instr(v.strings,':',1,c.id+1)-instr(v.strings,':',1,c.id) p2 from v, cartesian c where c.id ＜= (length(v.strings)-length(replace(v.strings,':')))-1 ) group by strings order by 16.计算比重6.1.Oracle支持内置函数RATIO_TO_REPORT6.2.sql语句实例
select job,num_emps,sum(round(pct)) pct_of_all_salaries from (select job, count(*)over(partition by job) num_emps, ratio_to_report(sal)over()*100 pct from emp ) group by job,num_emps7.正则表达式功能7.1.Oracle Database 10g7.2.sql语句实例
select emp_id, text from employee_comment where regexp_like(text, '[0-9]{3}[-. ][0-9]{3}[-. ][0-9]{4}') and regexp_like( regexp_replace(text, '[0-9]{3}([-. ])[0-9]{3}\1[0-9]{4}',''), '[0-9]{3}[-. ][0-9]{3}[-. ][0-9]{4}') EMP_ID TEXT---------- ---------------------------------------------------------- 7369 126 Varnum, Edmore MI 48829, 989 313-5351 7844 989-387.5359 9999 906-387-1698, 313-535.8886