ORACLE 语句技巧

----- 批量修改oracle 表的名称
DECLARE
v_ename VARCHAR2(32);
CURSOR c_emp IS select table_name from dba_all_tables t where t.owner='SUNXF';
BEGIN
OPEN c_emp;
LOOP
FETCH c_emp INTO v_ename;
EXIT WHEN c_emp%NOTFOUND;
DBMS_OUTPUT.PUT_LINE('Salary of Employee'|| v_ename);
if v_ename!=UPPER(v_ename) then
execute immediate 'rename "'||v_ename||'" to '||UPPER(v_ename);
end if;
END LOOP;
end;




--------去重复
DELETE FROM BM E WHERE E.ROWID > (SELECT MIN(X.ROWID)
FROM BM X
WHERE X.mobile = E.mobile)



-------删除指定行
DELETE FROM BM
WHERE mobile IN (select mobile from (SELECT *
FROM (SELECT mobile, ROWNUM rn
FROM BM)
WHERE rn not BETWEEN 0 AND 4000))


-------------------------日期函数

select (sysdate-to_date( to_char(insertdate,'yyyy-mm-dd hh24:mi:ss'), 'yyyy-mm-dd hh24:mi:ss '))*24*60*60
from TB_ABC_Log;

------------存在就更新 不存在就插入

merge into gmf f --fzq1表是需要更新的表
using gm g -- 关联表
on (g.mobile = f.mobile and g.content=f.content) --关联条件
when matched then --匹配关联条件，作更新处理
update set
f.Send_Times=f.Send_Times+1
when not matched then --不匹配关联条件，作插入处理。如果只是作更新，下面的语句可以省略。
insert values( g.ID,g.CODE,g.NO,g.NUM,g.MOBILE,g.CONTENT,g.SENDTIME,g.RECVTIME,
g.SEQNO,g.WAYTIME,g.MSGID,g.NUMBER,
g.SENDSTATUS,g.FILENO,g.IP,g.ISSEND,0);

-----行转列
select wm_concat(column_name) from user_tab_cols where table_name='NEW_USER_BILL'

-----时间间隔等待函数，等待2秒

DBMS_LOCK.sleep(2);

--------通过dblink 查询两个库 不同的字段

select 'alter table ' || table_name || ' modify ' || column_name || ' ' ||
data_type || '(' || data_length || ');'
from user_tab_cols
where table_name || column_name || data_type || data_length || nullable not in
(select table_name || column_name || data_type || data_length ||
nullable
from user_tab_cols@TEST
where table_name like 'ABC_%')
and table_name like 'ABC_%';

---------产生种子id，类似于mysql的自增长id

create or replace trigger TR_DTPROPERTIES
before insert on DTPROPERTIES for each row
begin
select to_char(DTPROPERTIES_SEQUENCE.nextval) into :new.id from dual;
end TR_DTPROPERTIES;