orcal语法

0 快速复制表结构

 create table BRANCHCOMPANY_FINANCE  as 
select * 
from finance_amount 
where 1=2; 

1 获取列表某一列的数据

select 'fname1',feesname

from (select feesname, Rank() Over(ORDER BY rownum DESC) Rn FROM vehicle_fees_regist )

where Rn = 2

2  动态行转列

CREATE OR REPLACE PROCEDURE tms.prc_order_count is

    v_sql varchar2(5000);

    cursor cursor_1 is select distinct r.goodsname from

         ba_goods_info r order by r.goodsname;

    i number :=1;

      begin

        v_sql := 'select e.months ';

        for v_goodsname in cursor_1

        loop

          v_sql := v_sql || ',' || 'sum(decode(t.goodsname,''' || v_goodsname.goodsname ||

                   ''',t.onev,0,t.twov,0,t.threev,0,t.fourv,0,t.fivev,0,t.sixv,0,t.sevenv,0,t.fightv,0,

                   t.ninev,0,t.tenv,0,t.elevenv,0,t.twelvev,0

                   )) as' ||'name'||i;

                   i:=i+1;

        end loop;

        v_sql := v_sql || ' from view_order_shouamount t right join view_month e 

          on t.singletime=e.months

         group by e.months  order by e.months ';

        dbms_output.put_line(v_sql);

        v_sql := 'create or replace view view_order_count  as '|| v_sql ;

        dbms_output.put_line(v_sql);

        execute immediate v_sql;

      end;

call prc_order_count();

select * from view_order_count

3 静态列转行

select 'costliving','生活费' from dual

union

select 'ticketfines','有票罚款' from dual

union

select 'noticketfines','无票罚款' from dual

4 带参数的函数

create or replace function raise_sal(years in varchar2)

return varchar2

as

x number;

v_sql varchar2(32763);

v_s varchar2(32763);

begin

x:=4;

v_sql := 'select t.goodsname ';

for x in reverse 1..4 loop

v_s := v_s || ',(' || 'select sum(s.shouamount)

                          from bu_order_info r, bu_shipping_list s

                         where r.billno = s.billno

                           and r.goodsname = t.goodsname

                           and to_char(r.singletime, '''||'mm'||''') ='|| x || '

                           and to_char(r.singletime, '''||years||''') = 2013) as '|| 'months'||x ;

       -- dbms_output.put_line(v_s);

end loop ;

v_sql := v_sql || v_s || ' from bu_order_info t

                 group by t.goodsname';

return v_sql;

exception

  when no_data_found then

    raise_application_error(-20000,'Current Employee does not exists');

end;

5 带输入参数的存储过程

create or replace procedure query_sal(

years in number

--v_sq out varchar2(32763)

)

is

x number;

v_sql varchar2(32763);

v_s varchar2(32763);

begin

x:=4;

v_sql := 'select t.goodsname ';

for x in reverse 1..4 loop

v_s := v_s || ',(' || 'select sum(s.shouamount)

                          from bu_order_info r, bu_shipping_list s

                         where r.billno = s.billno

                           and r.goodsname = t.goodsname

                           and to_char(r.singletime, '''||'mm'||''') ='|| x || '

                           and to_char(r.singletime, '''||years||''') = 2013) as '|| 'months'||x ;

end loop ;

v_sql := v_sql || v_s || ' from bu_order_info t

                 group by t.goodsname';

end;

call query_sal(2013);

6.if else 判断

CASE 

WHEN c.accessway = 1 THEN '新装'  

WHEN c.accessway = 2 THEN '接入'  

WHEN c.accessway = 3 THEN '租赁'  

WHEN c.accessway = 4 THEN '以新换旧' 

WHEN c.accessway = 5 THEN '以旧换新'  

WHEN c.accessway = 6 THEN '移机' 

ELSE '0' END

7.  合并多条结果

wmsys.wm_concat()

8 。两级结构时  只显示一二级目录，去掉有二级目录的一级目录单独显示的

select t.id,

       t.areaname preant,

       a.areaname

  from (select * from area_info ar where ar.arealevel =1 ) t, area_info a

 where t.id = a.parentid(+)   

9. 获取某一月的全部日期

select to_date('2014-04', 'yyyy-mm') + (rownum - 1) datetime

  from dual

connect by rownum <= last_day(to_date('2014-04', 'yyyy-mm')) -

           to_date('2014-04', 'yyyy-mm') + 1