一、重復操作查詢

--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  

 select