数据库操作规范及SQL书写建议

select xz_lx ,        yp_mc ,        gg ,        max_sl ,        max_jl ,        max_ts ,        max_sl_mzk ,        max_jl_mzk ,        max_ts_mzk ,        ys_bm_id ,        a.kf_id ,        a.yp_bh    from drug_limit_info a,   dictmanage.v_dict_drug b   where   a.yp_bh = b.yp_bh   order by   xz_lx

select distinct ( a.jz_kh ),                 d.pat_name ,                  case                   when   d.xb_id = '1' then                    '?'                   when   d.xb_id = '2' then                    '?'                   else                    '??'                 end as xb ,                 case                   when floor ( months_between ( sysdate , d.CS_RQ ) / 12 ) > 3 then                    to_char ( floor ( months_between ( sysdate ,   d.CS_RQ ) / 12 )) || '??'                   else                    his.fun_getage ( d.CS_RQ )                 end as nl ,                 c.zflx_mc ,                  max ( a.yz_qrsj ) yzqrsj ,                 max ( a.yzjsrq ) yzjsrq   from his.outpat_order a ,   dictmanage.dict_pay_type c , his.pat_info d   where a.pat_id = d.pat_id    and a.pay_type = c.zflx_id (+)    and a.yfstatus = '1'    and a.tfbz in ( '0' , '6' )    and   a.cflx_id = '3'    and a.YZ_STATUS = '4'    and a.winid in   (select f.win_id                        from dictmanage.dict_machine_cfg f                       where machine = :in_machine)   group by a.jz_kh , d.pat_name , d.xb_id , d.CS_RQ , c.zflx_mc   order by yzjsrq asc

select distinct ( a.jz_kh ),                 d.pat_name ,                 case                    when   d.xb_id = '1' then                    '?'                   when   d.xb_id = '2' then                    '?'                   else                    '??'                 end as xb ,                 case                   when floor ( months_between ( sysdate , d.CS_RQ ) / 12 ) > 3 then                    to_char ( floor ( months_between ( sysdate ,   d.CS_RQ ) / 12 )) || '??'                   else                    his.fun_getage ( d.CS_RQ )                 end as nl ,                 c.zflx_mc ,                 max ( a.yz_qrsj ) yzqrsj ,                 max ( a.yzjsrq ) yzjsrq   from his.outpat_order a ,   dictmanage.dict_pay_type c , his.pat_info d dictmanage.dict_machine_cfg f   where a.pat_id = d.pat_id    and a.pay_type = c.zflx_id (+)    and a.yfstatus = '1'    and a.tfbz in ( '0' , '6' )    and a.cflx_id = '3'    and a.YZ_STATUS = '4'    and a.winid =   f.win_id    and f.machine   = :in_machine   group by a.jz_kh , d.pat_name , d.xb_id , d.CS_RQ , c.zflx_mc   order by yzjsrq asc 

select distinct a.jzkh ,                 b.pat_name ,                 c.yp_mc ,                 a.cf_lsh_id ,                 a.yzzx_lsh_id ,                 c.psjgsj ,                 ceil (( a.ps_jssj - a.ps_kssj ) * 24 * 60 )   psygsj ,                 d.yzjl_id ,                 a.fin_mxid ,                 case nvl ( f.brlx , '0' )                   when '1' then                    '??' ||   f.room || '(' || f.cwh || ')'                    when '3' then                    '??' ||   f.room || '(' || f.cwh || ')'                   else                    '???'                 end   brlx_mc   from his.outpat_order_act_detail a ,        his.pat_info                b ,        dictmanage.drug_info        c ,        his.outpat_order            d ,        his.pat_observe_info        f   where a.pat_id = b.pat_id    and a.sfxm_id = c.yp_id    and a.mzyzjl_id = d.yzjl_id    and a.jzlsh_id = f.jz_lsh_id (+)    and a.psjg_id <> '0'    and a.ps_jssj is not null    and a. fs_rq is null    and   ceil((sysdate - a.ps_kssj) * 24 * 60) > psjgsj    and a.zxks_id = : ksid

SQL> select count(*) from   his.outpat_order_act_detail a,dictmanage.drug_info c where a.ps_kssj <   sysdate - c.psjgsj/60/24;     COUNT(*) ----------   28081264   SQL> select count(*) from   his.outpat_order_act_detail a,dictmanage.drug_info c where ceil((sysdate -   a.ps_kssj) * 24 * 60) > c.psjgsj;     COUNT(*) ----------   28081264

update his.outpat_order     set psjg_id =         (select case                 when psjg_id = ps_fsjg then                  psjg_id                 else                  '4'               end psjg            from his.outpat_order_act_detail           where yzzx_lsh_id = 476253             and ly = '1'),         zhgxsj  = sysdate,         zhgxr   = '2899' where YZJL_ID = (select mzyzjl_id                    from   his.outpat_order_act_detail                   where yzzx_lsh_id =   476253)

update his.outpat_order     set psjg_id =         (select case                 when psjg_id = ps_fsjg then                  psjg_id                 else                  '4'               end psjg            from his.outpat_order_act_detail           where yzzx_lsh_id = 476253             and ly = '1'),         zhgxsj  = sysdate,         zhgxr   = '2899' where YZJL_ID = (select to_char(mzyzjl_id)                    from   his.outpat_order_act_detail                   where yzzx_lsh_id =   476253)

        31 HIS      INDEX PARTITION    TP_HIS42        26 HIS      TABLE              DICTSPACES        31 HIS      INDEX PARTITION    TP_HIS45        31 HIS      INDEX PARTITION    TP_HIS52        16 HIS      TABLE PARTITION    TP_HIS06        16 HIS      TABLE PARTITION    TP_HIS07        16 HIS      TABLE PARTITION    TP_HIS12        16 HIS      TABLE PARTITION    TP_HIS16        16 HIS      TABLE PARTITION    TP_HIS23        16 HIS      TABLE PARTITION    TP_HIS26        16 HIS      TABLE PARTITION    TP_HIS28        16 HIS      TABLE PARTITION    TP_HIS41        16 HIS      TABLE PARTITION    TP_HIS61        31 HIS      INDEX PARTITION    TP_HIS02        31 HIS      INDEX PARTITION    TP_HIS16        31 HIS      INDEX PARTITION    TP_HIS32        31 HIS      INDEX PARTITION    TP_HIS35         4 HIS      LOBINDEX           XMLDATA        31 HIS      INDEX PARTITION    TP_HIS29        31 HIS      INDEX PARTITION    TP_HIS44        16 HIS      TABLE PARTITION    TP_HIS02        16 HIS      TABLE PARTITION    TP_HIS05        16 HIS      TABLE PARTITION    TP_HIS14        16 HIS      TABLE PARTITION    TP_HIS36        16 HIS      TABLE PARTITION    TP_HIS37        16 HIS      TABLE PARTITION    TP_HIS39        16 HIS      TABLE PARTITION    TP_HIS40        16 HIS      TABLE PARTITION    TP_HIS42        16 HIS      TABLE PARTITION    TP_HIS49        16 HIS      TABLE PARTITION    TP_HIS53        16 HIS      TABLE PARTITION    TP_HIS59        31 HIS      INDEX PARTITION    TP_HIS10        31 HIS      INDEX PARTITION    TP_HIS12        31 HIS      INDEX PARTITION    TP_HIS14        31 HIS      INDEX PARTITION    TP_HIS58       225 HIS      TABLE              XMLDATA        26 HIS      INDEX              DICTSPACES        31 HIS      INDEX PARTITION    TP_HIS19        31 HIS      INDEX PARTITION    TP_HIS20        31 HIS      INDEX PARTITION    TP_HIS28        16 HIS      TABLE PARTITION    TP_HIS11        16 HIS      TABLE PARTITION    TP_HIS17        16 HIS      TABLE PARTITION    TP_HIS30        16 HIS      TABLE PARTITION    TP_HIS34        16 HIS      TABLE PARTITION    TP_HIS35        16 HIS      TABLE PARTITION    TP_HIS44        16 HIS      TABLE PARTITION    TP_HIS45        16 HIS      TABLE PARTITION    TP_HIS58        16 HIS      TABLE PARTITION    TP_HIS62        16 HIS      TABLE PARTITION    TP_HIS63        16 HIS      TABLE PARTITION    TP_HIS64        31 HIS      INDEX PARTITION    TP_HIS09        31 HIS      INDEX PARTITION    TP_HIS11        31 HIS      INDEX PARTITION    TP_HIS15        31 HIS      INDEX PARTITION    TP_HIS36        31 HIS      INDEX PARTITION    TP_HIS59        31 HIS      INDEX PARTITION    TP_HIS60        31 HIS      INDEX PARTITION    TP_HIS62        31 HIS      INDEX PARTITION    TP_HIS43        31 HIS      INDEX PARTITION    TP_HIS46        31 HIS      INDEX PARTITION    TP_HIS48        31 HIS      INDEX PARTITION    TP_HIS50        16 HIS      TABLE PARTITION    TP_HIS24        16 HIS      TABLE PARTITION    TP_HIS27        16 HIS      TABLE PARTITION    TP_HIS43        16 HIS      TABLE PARTITION    TP_HIS54        16 HIS      TABLE PARTITION    TP_HIS56        31 HIS      INDEX PARTITION    TP_HIS01        31 HIS      INDEX PARTITION    TP_HIS04        31 HIS      INDEX PARTITION    TP_HIS17        31 HIS      INDEX PARTITION    TP_HIS37        31 HIS      INDEX PARTITION    TP_HIS54        31 HIS      INDEX PARTITION    TP_HIS56        31 HIS      INDEX PARTITION    TP_HIS57         3 HIS      TABLE PARTITION    XMLDATA        31 HIS      INDEX PARTITION    TP_HIS22        31 HIS      INDEX PARTITION    TP_HIS23        31 HIS      INDEX PARTITION    TP_HIS26        16 HIS      TABLE PARTITION    TP_HIS03        16 HIS      TABLE PARTITION    TP_HIS09        16 HIS      TABLE PARTITION    TP_HIS15        16 HIS      TABLE PARTITION    TP_HIS19        16 HIS      TABLE PARTITION    TP_HIS29        16 HIS      TABLE PARTITION    TP_HIS38        16 HIS      TABLE PARTITION    TP_HIS48        31 HIS      INDEX PARTITION    TP_HIS07        31 HIS      INDEX PARTITION    TP_HIS31        31 HIS      INDEX PARTITION    TP_HIS38        31 HIS      INDEX PARTITION    TP_HIS55        31 HIS      INDEX PARTITION    TP_HIS40        31 HIS      INDEX PARTITION    TP_HIS21        31 HIS      INDEX PARTITION    TP_HIS47        31 HIS      INDEX PARTITION    TP_HIS53        16 HIS      TABLE PARTITION    TP_HIS01        16 HIS      TABLE PARTITION    TP_HIS08        16 HIS      TABLE PARTITION    TP_HIS13        16 HIS      TABLE PARTITION    TP_HIS18        16 HIS      TABLE PARTITION    TP_HIS33        16 HIS      TABLE PARTITION    TP_HIS50        16 HIS      TABLE PARTITION    TP_HIS52        16 HIS      TABLE PARTITION    TP_HIS57        16 HIS      TABLE PARTITION    TP_HIS60        31 HIS      INDEX PARTITION    TP_HIS05        31 HIS      INDEX PARTITION    TP_HIS08        31 HIS      INDEX PARTITION    TP_HIS13        31 HIS      INDEX PARTITION    TP_HIS18        31 HIS      INDEX PARTITION    TP_HIS34        31 HIS      INDEX PARTITION    TP_HIS63        31 HIS      INDEX PARTITION    TP_HIS64         4 HIS      LOBSEGMENT         XMLDATA        31 HIS      INDEX PARTITION    TP_HIS25        31 HIS      INDEX PARTITION    TP_HIS30        31 HIS      INDEX PARTITION    TP_HIS51        16 HIS      TABLE PARTITION    TP_HIS20        16 HIS      TABLE PARTITION    TP_HIS21        16 HIS      TABLE PARTITION    TP_HIS22        16 HIS      TABLE PARTITION    TP_HIS25        16 HIS      TABLE PARTITION    TP_HIS31        16 HIS      TABLE PARTITION    TP_HIS47        16 HIS      TABLE PARTITION    TP_HIS51          31 HIS      INDEX PARTITION    TP_HIS06       452 HIS      INDEX              XMLDATA        31 HIS      INDEX PARTITION    TP_HIS41        31 HIS      INDEX PARTITION    TP_HIS24        31 HIS      INDEX PARTITION    TP_HIS27        31 HIS      INDEX PARTITION    TP_HIS49        16 HIS      TABLE PARTITION    TP_HIS04        16 HIS      TABLE PARTITION    TP_HIS10        16 HIS      TABLE PARTITION    TP_HIS32        16 HIS      TABLE PARTITION    TP_HIS46        16 HIS      TABLE PARTITION    TP_HIS55        31 HIS      INDEX PARTITION    TP_HIS03        31 HIS      INDEX PARTITION    TP_HIS33        31 HIS      INDEX PARTITION    TP_HIS39        31 HIS      INDEX PARTITION    TP_HIS61 135   rows selected.