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('表名');

三、日期函數(shù)

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

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;

四、同一張表中，根據(jù)一個字段更新另一個字段

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;

五、重復數(shù)據(jù)查詢

select * FROM EMP E

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

FROM EMP X

WHERE X.EMP_NO = E.EMP_NO);

六、合并不同表的數(shù)據(jù)（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;

七、查詢執(zhí)行sql（v$sql）

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

? from v$sql t

?order by first_load_time desc;

2、數(shù)據(jù)庫精度修改處理

-- 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用戶登陸，執(zhí)行如下的查詢：

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

?

總結(jié)

以上是生活随笔為你收集整理的oracle-sql汇总的全部內(nèi)容，希望文章能夠幫你解決所遇到的問題。

如果覺得生活随笔網(wǎng)站內(nèi)容還不錯，歡迎將生活随笔推薦給好友。