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

?select