oracle常用函数笔记

• /***********         &          ***********/
  select * from &JDLJFDL

  &JDLJFDL表示作为一个参数使用

  
  /***********         add_months          ***********/
  和当前比较日期超过三月

  add_months(to_date(t.createdate,'yyyy-MM-dd'),3)>sysdate

  
  往前推到7天

  where to_date(time_,'yyyy-mm-dd')<(sysdate-7)

  
  /***********         alter          ***********/
  1、alter table a_test2 modify b varchar2(50)

  
  /***********         connect by          ***********/
  create table MAIN_NODE
  (
    MLA_ID       INTEGER not null,
    MLA_ROOTID   INTEGER,
    MLA_PARENTID INTEGER,
    MLA_NAME     VARCHAR2(50)
  )

   

  insert into MAIN_NODE (MLA_ID, MLA_ROOTID, MLA_PARENTID, MLA_NAME)
  values (6, 0, 3, '孙孙子节点');
  insert into MAIN_NODE (MLA_ID, MLA_ROOTID, MLA_PARENTID, MLA_NAME)
  values (1, 0, 0, '父节点1  ');
  insert into MAIN_NODE (MLA_ID, MLA_ROOTID, MLA_PARENTID, MLA_NAME)
  values (2, 0, 1, '子节点1');
  insert into MAIN_NODE (MLA_ID, MLA_ROOTID, MLA_PARENTID, MLA_NAME)
  values (3, 0, 2, '孙子节点1');
  insert into MAIN_NODE (MLA_ID, MLA_ROOTID, MLA_PARENTID, MLA_NAME)
  values (5, 0, 1, '子节点2');
  insert into MAIN_NODE (MLA_ID, MLA_ROOTID, MLA_PARENTID, MLA_NAME)
  values (7, 0, 1, '子节点3');
  insert into MAIN_NODE (MLA_ID, MLA_ROOTID, MLA_PARENTID, MLA_NAME)
  values (8, 0, 7, '子节点31');

   

  根据父节点 得到所有的子节点

  select mla_parentid, mla_id, mla_name from main_node

  start with mla_id=1 connect by prior mla_id=mla_parentid

  
  根据子节点得到 父节点

   

  select mla_parentid, mla_id, mla_name from main_node

  start with mla_id=6 connect by prior mla_parentid = mla_id

   

  根据子节点 得到顶层父节点

  select * from (
  select mla_parentid, mla_id, mla_name,rownum nn from main_node

  start with mla_id=6 connect by prior mla_parentid = mla_id order by nn desc ) where rownum < 2

   

  父节点 得到子节点信息

  select mla_parentid, mla_id, mla_name,LEVEL,CONNECT_BY_ISLEAF,lpad(' ',level*5)||mla_id
  ,sys_connect_by_path(mla_id,',')
    from main_node
   start with mla_id=1 connect by   mla_parentid  = prior  mla_id

   

  /***********         create          ***********/
  1、create table a_test2 as select * from a_test

   

  /***********         dbms_output          ***********/
  1、
   CREATE OR REPLACE PROCEDURE mysp_nopara
   as
   name_ varchar2(20);
   begin
     select id into name_ from aaaaaa where name='1';
      dbms_output.put_line('name'||name_);
   end;

   

   调用存储过程

  
   begin
   mysp_nopara();
   end;

  
  2、

  /***********         decode          ***********/
  select decode('b','a',2,'b',3,4) from dual;
  结果为 3
  select decode('a','a',2,'b',3,4) from dual;

  结果为 2

  select decode('d','a',2,'b',3,4) from dual;
  结果为 4

   

  decode(input,case1,value1,case2,value2,.......,elsevalue)

   相当于
  case(input)
  {
   case case1:
    return value1;
   case case2:
    return value2;
   default
    return elsevalue;
  }

  /***********         delete          ***********/
  1、delete from a_test2

  
  /***********         distinct          ***********/
  1、select  distinct code from a_test;

   

  /***********         drop          ***********/
  1、drop table a_test2

  
  /***********         execute immediate          ***********/
  CREATE OR REPLACE PROCEDURE change_num_num2
  as
  result varchar2(4000);
  begin

      for temp_cursor in (select column_name as col,table_name as tab from user_tab_cols where table_name like 'VO_%' and data_type = 'NUMBER' and column_name='STATUS'   )
      loop
         -- IF   temp_cursor.num <2 then
       -- execute immediate('update   '||temp_cursor.tab|| ' set '||temp_cursor.col||' =:1') using '';
      --  commit;
            -- execute immediate ('alter table  '||temp_cursor.tab|| '  modify '||temp_cursor.col||' number(20)');
             execute immediate('alter table  '||temp_cursor.tab|| '  modify '||temp_cursor.col||' number(20)');
             commit;
         -- END IF;

       end loop;

  
  end;

  /***********         exp          ***********/
  导入
  imp gaoxinqu3/12345@orcl full=y file=e:/gaoxinqu-217-20101220.dmp

  导出
  exp gaoxinqu/12345@orcl full=n file=e:/gaoxinqu-217-20101220.dmp

  exp gaoxinqu/12345@orcl full=n file=e:/gaoxinqu-217-20101221_number.dmp

  最新的（导完数据  改完字段类型）

  exp gaoxinqu/12345@orcl full=n file=e:/gaoxinqu-217-20101221_number1.dmp

  
   

  /***********         group by          ***********/
  1、select sum(a),code from a_test group by code;

  
  /***********         group_concat_cfz          ***********/
  create or replace function group_concat_cfz(tabname in varchar2) return clob is
    sqlValue clob;
    collName clob;
  BEGIN
     for temp_cursor in ( select column_name col,data_type dtype from user_tab_cols where table_name =  tabname  )
     loop
      if temp_cursor.dtype ='NUMBER' then
            sqlValue := sqlValue||', trim('||temp_cursor.col||')';
         else
            sqlValue := sqlValue||', '||temp_cursor.col;
         end if;
         collName := collName||', '||temp_cursor.col;
      end loop;

  
     sqlValue := ltrim(sqlValue,',');
     collName := ltrim(collName,',');
     sqlValue := 'insert into '||tabname||'('||collName||')  select '||sqlValue||'  from  gaoxinqu3.'||tabname||';';

    return(sqlValue);
  end group_concat_cfz;

   

  wm_concat 这个函数与mysql 的类似

  
  /***********         imp          ***********/
  导入
  imp gaoxinqu3/12345@orcl full=y file=e:/gaoxinqu-217-20101220.dmp

  导出
  exp gaoxinqu/12345@orcl full=n file=e:/gaoxinqu-217-20101220.dmp

  exp gaoxinqu/12345@orcl full=n file=e:/gaoxinqu-217-20101221_number.dmp

  最新的（导完数据  改完字段类型）

  exp gaoxinqu/12345@orcl full=n file=e:/gaoxinqu-217-20101221_number1.dmp

  
   

  /***********         join          ***********/
  1、select t.c ,t.code,d.name from (select sum(a) as c,code from a_test group by code) t
   join a_test1 d on t.code = d.code 

  2、select t.c ,t.code,d.name from (select sum(a) as c,code from a_test group by code) t
  left  join a_test3 d on t.code = d.code

  
  /***********         level          ***********/
  得到一列，这列数据是 1——100数字

  
  (select LEVEL lv from dual CONNECT BY LEVEL <= 100)

  
  /***********         ltrim          ***********/

  例子1

  select ltrim('109224323','109') from dual;

  结果

  224323

  select ltrim('10900094323','109') from dual;

  结果

  4323

  
  分析
  看第一个例子
  '109'  作为 搜索 字符  分别有 1， 0 ，9
  109224323 
  从左往右  1  包含在 搜索 字符中
  从左往右  0  包含在 搜索 字符中
  从左往右  9  包含在 搜索 字符中
  从左往右  2  不包含在 搜索 字符中  停止搜索

  所以结果为 224323

  
  rtrim 作用和上面一样不过 就是从右搜索

   

  
  /***********         modify          ***********/
  1、alter table a_test2 modify b varchar2(50)

  
  /***********         null          ***********/
  select a from test where b is not null;
  select a from test where b is null;

  select a from test where trim(b) is not null;

  
  /***********         nvl          ***********/
  nvl(a,0)

  如果a为空的话 返回一个0

  
  /***********         over          ***********/
  积累函数

  create table  aaa
  (
  id number(10),
  value number(10)
  )

  insert into aaa values(10,3);
  insert into aaa values(20,5);
  insert into aaa values(30,6);

  alter table aa
  add code varchar2(10)

  修改数据如下

  10 3 a
  20 5 a
  30 6 b
  40 5 b
  67 44 c

   

   select code,id,value,sum(value) over(order by id) from aa
   结果如下
  a 10 3 3
  a 20 5 8
  b 30 6 14
  b 40 5 19
  c 67 44 63

  select code,id,value,sum(value) over(order by id desc) from aa
  结果如下

  c 67 44 44
  b 40 5 49
  b 30 6 55
  a 20 5 60
  a 10 3 63

  
    select code,id,value,sum(value) over(partition by code ) 
    from aa
  结果如下
  a 10 3 8
  a 20 5 8
  b 30 6 11
  b 40 5 11
  c 67 44 44

  
   select code,id,value,sum(value) over(partition by code order by id) 
    from aa
  结果为
  a 10 3 3
  a 20 5 8
  b 30 6 6
  b 40 5 11
  c 67 44 44

    select code,id,row_number() over(order by id) from aa;
  结果为
  a 10 1
  a 20 2
  b 30 3
  b 40 4
  c 67 5

  分组排名

   select code,id,row_number() over(partition by code order by id) as t from aa
    结果为
  a 10 1
  a 20 2
  b 30 1
  b 40 2
  c 67 1

   

  按某列统计个数

  select * from aa;
  10 3 a
  20 5 a
  30 6 b
  40 5 b
  67 44 c

  想得到如下结果

  a  b c
  10 30 67 
  20 40 0

  sql 语句如下

  select max(decode(code,'a',id,0)) as a,
  max(decode(code,'b',id,0)) as b,
  max(decode(code,'c',id,0)) as c
   from
  (
  select id,code,row_number() over(partition by code order by id) as pm from aa
  )
  group by pm;

   

  
  /***********         replace          ***********/
  替换字符方法

  select replace('5311313344','31','aaa') from dual

  把31 用aaa 替换

  将字母，数字中数字抽离的方法

  select replace(translate('asd5a3dsa113a133asdf44','0123456789','#'),'#','') from dual

  /***********         row_number          ***********/
  排名 
  见 over函数

  
  /***********         select into          ***********/
  create or replace function getbyleiji1(id in varchar2,yue in integer) return number is
    Result number(20,0) ;
  BEGIN
    select sum(to_number(trim(pfea_0))) into Result  from bb_sp_lsxs_2010 where yue>= to_number(trim(time_))
     and zzjg = id;
    return(Result);
  end getbyleiji1;

  /***********         select          ***********/
  1、select code from a_test

   

  /***********         sign          ***********/
  求数字类型 符号的方法

  select sign(3-1) from dual;
  结果为1

  select sign(-1) from dual;
  结果为-1

  
  /***********         start with          ***********/
  create table MAIN_NODE
  (
    MLA_ID       INTEGER not null,
    MLA_ROOTID   INTEGER,
    MLA_PARENTID INTEGER,
    MLA_NAME     VARCHAR2(50)
  )

   

  insert into MAIN_NODE (MLA_ID, MLA_ROOTID, MLA_PARENTID, MLA_NAME)
  values (6, 0, 3, '孙孙子节点');
  insert into MAIN_NODE (MLA_ID, MLA_ROOTID, MLA_PARENTID, MLA_NAME)
  values (1, 0, 0, '父节点1  ');
  insert into MAIN_NODE (MLA_ID, MLA_ROOTID, MLA_PARENTID, MLA_NAME)
  values (2, 0, 1, '子节点1');
  insert into MAIN_NODE (MLA_ID, MLA_ROOTID, MLA_PARENTID, MLA_NAME)
  values (3, 0, 2, '孙子节点1');
  insert into MAIN_NODE (MLA_ID, MLA_ROOTID, MLA_PARENTID, MLA_NAME)
  values (5, 0, 1, '子节点2');
  insert into MAIN_NODE (MLA_ID, MLA_ROOTID, MLA_PARENTID, MLA_NAME)
  values (7, 0, 1, '子节点3');
  insert into MAIN_NODE (MLA_ID, MLA_ROOTID, MLA_PARENTID, MLA_NAME)
  values (8, 0, 7, '子节点31');

   

  根据父节点 得到所有的子节点

  select mla_parentid, mla_id, mla_name from main_node

  start with mla_id=1 connect by prior mla_id=mla_parentid

  
  根据子节点得到 父节点

   

  select mla_parentid, mla_id, mla_name from main_node

  start with mla_id=6 connect by prior mla_parentid = mla_id

   

  根据子节点 得到顶层父节点

  select * from (
  select mla_parentid, mla_id, mla_name,rownum nn from main_node

  start with mla_id=6 connect by prior mla_parentid = mla_id order by nn desc ) where rownum < 2

   

  父节点 得到子节点信息

  select mla_parentid, mla_id, mla_name,LEVEL,CONNECT_BY_ISLEAF,lpad(' ',level*5)||mla_id
  ,sys_connect_by_path(mla_id,',')
    from main_node
   start with mla_id=1 connect by   mla_parentid  = prior  mla_id

   

  /***********         sum          ***********/
  1、select sum(a),code from a_test group by code;

  
  /***********         to_date          ***********/
  to_date('2009-01-03','yyyy-mm-dd')

   

  /***********         to_number          ***********/
  to_date('2009-01-03','yyyy-mm-dd')

   

  /***********         translate          ***********/
  例子

  select translate('5311313344','13','b') from dual

  替换规则

  1  替换成 b
  3  替换空格  （相等于去掉）

  从左往右搜索

  得到的结果

  5bbb44

  
  /***********         type          ***********/
  1、 create or replace type oraArr as varray(12) of varchar2(100);

  2、

  /***********         union all          ***********/
  1、select code ,a from a_test  where code ='a'
   union all
   select code ,b from a_test where code ='b'

  
  /***********         with          ***********/
  select a,b,c from
  (
          with test as (select 'aaa' a,'bbb' b,'1,2,3,5,6,7,78,8,3,99,33' c from dual)
          select * from test
  )