Oracle小技巧

Oracle纵向变横向,多行变一行

取处方下的药品大类,以

处方药品大类

a001 中药.西药，中成药的的格式显示

/*处方与药品大类的对应 周璇球 20130829*/
SELECT SCFXX01,
     replace(trim(max(sys_connect_by_path(SJCXX02, ' '))),' ',',') s_drug_types
FROM(
  SELECT  YYGLE61.SCFXX01,
     YYGL101.SJCXX02,
     lag(SJCXX02) over(partition by YYGLE61.SCFXX01 order by SJCXX02) l_SJCXX02
  FROM YYGLE61,yygle62,YYGL110,YYGL101
  where yygle61.scfxx01 = yygle62.scfxx01
  and YYGLE62.SCFMX03 = YYGL110.SYPXX01(+)
  and YYGL110.SYPXX14 = YYGL101.SJCXX01(+)
) a
start with l_SJCXX02 is null
connect by nocycle SCFXX01 = prior SCFXX01 and prior SJCXX02 = l_SJCXX02
GROUP BY SCFXX01;

根据原型

select name, max(sys_connect_by_path(report, '  ')) s_report
from 
    (select name, report, lag(report) over(partition by name order by report) l_report
         from tb)
    start with l_report is null
    connect by name = prior name and prior report = l_report
group by name
order by name;

发现将视图放在内连接里边比在查询列中用子查询的速度要快

(快)

select 
max(a.s_drug_types) as s_drug_types
FROM YYGLE61,yygle62,V_LDMZ_PRES_DRUG_TYPES a
where yygle61.scfxx01 = yygle62.scfxx01 
    and yygle61.scfxx01  = a.scfxx01

(慢)

select 
(select  a.s_drug_types
from  V_LDMZ_PRES_DRUG_TYPES a
where  yygle61.scfxx01  = a.scfxx01) as s_drug_types
FROM YYGLE61,yygle62
where yygle61.scfxx01 = yygle62.scfxx01

网上查到的

使用LAG和LEAD函数统计

Lag和Lead函数可以在一次查询中取出同一字段的前N行的数据和后N行的值。这种操作可以使用对相同表的表连接来实现，不过使用LAG和LEAD有更高的效率。以下是LAG和LEAD的例子：

SQL> select year,region,profit ,lag (profit,1) over (order by year)

2 as last_year_exp from test;

YEAR REGION PROFIT LAST_YEAR_EXP

---- ------- ---------- -------------

2003 West 88

2003 West 88 88

2003 Central 101 88

2003 Central 100 101

2003 East 102 100

2004 West 77 102

2004 East 103 77

2004 West 89 103

SQL> select year,region,profit ,lead (profit,1) over (order by year)

2 as next_year_exp from test;

YEAR REGION PROFIT NEXT_YEAR_EXP

---- ------- ---------- -------------

2003 West 88 88

2003 West 88 101

2003 Central 101 100

2003 Central 100 102

2003 East 102 77

2004 West 77 103

2004 East 103 89

2004 West 89

Lag函数为Lag(exp,N,defval)，defval是当该函数无值可用的情况下返回的值。Lead函数的用法类似。

Lead和Lag函数也可以使用分组，以下是使用region分组的例子：

SQL> select year,region,profit ,

2 lag (profit,1,0) over (PARTITION BY region order by year)

3 as last_year_exp from test;

YEAR REGION PROFIT LAST_YEAR_EXP

---- ------- ---------- -------------

2003 Central 101 0

2003 Central 100 101

2003 East 102 0

2004 East 103 102

2003 West 88 0

2003 West 88 88

2004 West 77 88

2004 West 89 77

3.创建Oracle Job

在comand窗口里边

variable job number;

begin
  sys.dbms_job.submit(job => :job,
                      what => 'print;',
                      next_date => to_date('01-01-4000', 'dd-mm-yyyy'),
                      interval => 'trunc(sysdate+1)+1/288');
  commit;
end;
/

print是存储过程

带参数的存储过程Job

variable job number;

begin
  sys.dbms_job.submit(job => :job,
                            what => 'yygl_yktjz(trunc(sysdate));',
                      next_date => to_date('01-01-4000', 'dd-mm-yyyy'),
                      interval => 'trunc(sysdate+1)+1/288');
  commit;
end;
/

begin
  sys.dbms_job.submit(job => :job1,
                      what => 'declare s1 varchar2(200); s2 varchar2(200); begin p_test.usp_test_jobincs(s1,s2); end;',
                      next_date => sysdate,
                      interval => 'sysdate+1/1440');
  commit;
end;
/

Oracle去换行，去空格等

/*去除换行*/
update zhzl_address t set t.add_administration_num=replace(t.add_administration_num,chr(10),'');
/*去掉回车*/
update zhzl_address t set t.add_administration_num=replace(t.add_administration_num,chr(13),'');
/*去掉空格*/
update zhzl_address t set t.add_administration_num=trim(t.add_administration_num);

select rowid,yyglk60.* 
from yyglk60    
where INSTR(syycs04,CHR(32)) > 0
or INSTR(syycs01,CHR(32)) > 0
OR INSTR(syycs02,CHR(32)) > 0
OR INSTR(syycs03,CHR(32)) > 0
OR INSTR(syycs05,CHR(32)) > 0
OR INSTR(syycs04,CHR(10)) > 0
or INSTR(syycs01,CHR(10)) > 0
OR INSTR(syycs02,CHR(10)) > 0
OR INSTR(syycs03,CHR(10)) > 0
OR INSTR(syycs05,CHR(10)) > 0
;

select * 
from yyglk60    
where INSTR(syycs04,CHR(9)) > 0
or INSTR(syycs01,CHR(9)) > 0
OR INSTR(syycs02,CHR(9)) > 0
OR INSTR(syycs03,CHR(9)) > 0
OR INSTR(syycs05,CHR(9)) > 0

update yyglk60
set syycs04 = replace(syycs04,' ','') ,
    syycs01 = replace(syycs01,' ',''),
    syycs02 = replace(syycs02,' ',''),
    syycs03 = replace(syycs03,' ',''),
    syycs05 = replace(syycs05,' ',''),
    syycs06 = replace(syycs06,' ','')
where syycs04 like '% %' or  syycs01 like '% %'  or  syycs02 like '% %' or syycs03 like '% %';

替换空格：

update yygl120 set szzxm24 = trim(szzxm24) where  INSTR(szzxm24,' ') > 0   ;