oracle--sql汇总

1、sql汇总

一、重复操作查询

--where条件得distinct systemdicid作为唯一标识

select *

  from dmis_zd_systemdic t

 WHERE typeid = '06012'

   and t.systemdicid in (select min(systemdicid)

                           from dmis_zd_systemdic

                          where typeid = '06012'

                          group by name)

 order by orderno;

二、检查表是否存在

select count(tname) from tab where tname = upper('表名');

三、日期函数

--返回当前日期年度的第一天

select trunc(sysdate,'year') from dual;

--返回当前日期月份的第一天

select trunc(sysdate,'month') from dual;

--上月最后一天

select last_day(add_months(sysdate, -1)) from dual;

--给定日期后最近星期几得日期

select next_day(to_date('2009-12-01', 'yyyy-mm-dd'), '星期一') next_day

from dual;

四、同一张表中，根据一个字段更新另一个字段

update (select t.fgenerationtime as ftime, t.fgeneratedateall as str

          from dmis_fs_approvebook t

         where t.fgenerationtime is not null)

   set str = TO_CHAR(ftime, 'yyyy-mm-dd')

 where str is null;

五、重复数据查询

select * FROM EMP E

WHERE E.ROWID > (SELECT MIN(X.ROWID)

FROM EMP X

WHERE X.EMP_NO = E.EMP_NO);

六、合并不同表的数据（merge  into）

merge into student s

using (select id, name, tel from test001) x

on (s.s_id = x.id)

when matched then

  update set s_name = x.name

when not matched then

  insert (s_id, s_name, s_age) values (x.id, x.name, x.tel);

commit;

七、查询执行sql（v$sql）

select t.module, t.first_load_time, t.sql_text

  from v$sql t

 order by first_load_time desc;

2、数据库精度修改处理

-- Create table

/*drop table temp_data;*/

create table temp_data

(

  FID      VARCHAR2(40) not null,

  USEHOURS NUMBER(10) default 0,

  FVOLTAGE NUMBER(10) default 0,

  INVOLTAGE NUMBER(10) default 0

)

;

alter table TEMP_DATA

  add constraint tempfid primary key (FID);

 

insert into temp_data

  select a.fid, a.usehours, a.fvoltage, a.involtage

    from dmis_fs_factorymonthdetail a;

 

update dmis_fs_factorymonthdetail t

   set t.usehours = '', t.fvoltage = '', t.involtage = '';

 

alter table DMIS_FS_FACTORYMONTHDETAIL modify USEHOURS NUMBER(10,1);

alter table DMIS_FS_FACTORYMONTHDETAIL modify FVOLTAGE NUMBER(10,1);

alter table DMIS_FS_FACTORYMONTHDETAIL modify INVOLTAGE NUMBER(10,1);

 

update (select a.usehours  as tusehours,

               b.usehours  as fusehours,

               a.fvoltage  as tfvoltage,

               b.fvoltage  as ffvoltage,

               a.involtage as tinvoltage,

               b.involtage as finvoltage,

               a.fid       as ffid,

               b.fid       as tfid

          from dmis_fs_factorymonthdetail a, temp_data b

         where a.fid = b.fid) tt

   set tt.tusehours  = tt.fusehours,

       tt.tfvoltage  = tt.ffvoltage,

       tt.tinvoltage = tt.finvoltage

 where ffid = tfid;

  

drop table temp_data;

commit;

 

3、恢复drop掉的存储过程

用sys用户登陆，执行如下的查询：

SQL> select text from dba_source as of timestamp to_timestamp('2009-03-06 09:45:00', 'YYYY-MM-DD HH24:MI:SS') where owner='IPRA' and name= 'P_IPACCHECK_NC' order by line;

4、删除某个用户下的对象

--删除某个用户下的对象  

set heading off;  

set feedback off;  

spool c:/dropobj.sql;  

  prompt --Drop constraint  

 select 'alter table '||table_name||' drop constraint '||constraint_name||' ;' from user_constraints where constraint_type='R';  

 prompt --Drop tables  

 select 'drop table '||table_name ||';' from user_tables;   

   

 prompt --Drop view  

 select 'drop view ' ||view_name||';' from user_views;  

   

 prompt --Drop sequence