oracle SQL葵花宝典(续写中)

厉4791

查询某列重复的数据
select * from Flyz_People where People_Name in (select   People_Name from   Flyz_People group by   People_Name having count (People_Name) > 1)
删除用户下面的所有表结构：
select   'Drop   table   '||table_name||';'     
from   all_tables   
where   owner='SF_UNITE';  
2011-07-12: 修改多表结构中相同字段的字段


CREATE OR REPLACE procedure ABLEMMS.update_table
is
v_length NUMBER;
sqls varchar2(4000);
table_name varchar2(4000);
b varchar2(4000);
c varchar2(4000);
d int;
cursor cur is select u.table_name, t.DATA_TYPE,t.COLUMN_NAME, t.DATA_LENGTH from user_tables u, user_tab_columns t where
u.table_name = t.TABLE_NAME  and t.TABLE_NAME = 'QF_KEYWORDS';
begin
open cur;
LOOP
FETCH cur INTO table_name,b,c,d;
EXIT WHEN cur%NOTFOUND;
if b = 'VARCHAR2' THEN
v_length := d+10;            
sqls :='alter table ' || table_name ||' modify ' || c || ' VARCHAR2('||v_length||')';
EXECUTE IMMEDIATE  sqls;
END IF;   
END LOOP;
close cur;
end update_table;
2011-04-25: 删除用户下所有的表结构
create or replace
PROCEDURE "del_table"
IS
cursor cur1 is select table_name from user_tables;
begin
for cur2 in cur1 loop
execute immediate 'drop table '||cur2.table_name||'cascade constraints';
end loop;
end;
2011-04-14：oracle查询被锁死的表以及解锁
select t2.username,t2.sid,t2.serial#,t2.logon_time
from v$locked_object t1,v$session t2
where t1.session_id=t2.sid order by t2.logon_time;
查询SID在执行的sql：
select sql_text from v$session a,v$sqltext_with_newlines b
where DECODE(a.sql_hash_value, 0, prev_hash_value, sql_hash_value)=b.hash_value
and a.sid in (&sid,&serial) order by piece;

alter system kill session '&sid,&SERIAL';


2011-03-25：oralce分页查询SQL语句
select * from ABLEPHOTO where rowid in(select rid from (select rownum rn,rid from(select rowid rid,ATIME from
ABLEPHOTO order by ATIME desc) where rownum<50) where rn>30) order by ATIME desc
ATIME 是表ABLEPHOTO 中的字段也是根据这个字段排序的
1、去掉重复记录 保留一条
DELETE FROM A_TEST
WHERE UNAME IN (  SELECT UNAME
FROM A_TEST
GROUP BY UNAME
HAVING COUNT(UNAME) > 1    )
AND ROWID NOT IN ( SELECT MIN(ROWID)
FROM A_TEST
GROUP BY UNAME
HAVING COUNT(UNAME) > 1   )
2、替换字段根据指定的字符
update QF_KEYWORDS set KEYWORD=REPLACE(KEYWORD, ',' , '')
3、oracle job时间


每天运行一次 'SYSDATE + 1'
每小时运行一次 'SYSDATE + 1/24'
每10分钟运行一次 'SYSDATE + 10/（60*24）'
每30秒运行一次 'SYSDATE + 30/(60*24*60)'
每隔一星期运行一次 'SYSDATE + 7'

每天午夜12点 'TRUNC(SYSDATE + 1)'
每天早上8点30分 'TRUNC(SYSDATE + 1) + （8*60+30）/(24*60)'
每星期二中午12点 'NEXT_DAY(TRUNC(SYSDATE ), ''TUESDAY'' ) + 12/24'
每个月第一天的午夜12点 'TRUNC(LAST_DAY(SYSDATE ) + 1)'
每个季度最后一天的晚上11点 'TRUNC(ADD_MONTHS(SYSDATE + 2/24, 3 ), 'Q' ) -1/24'
每星期六和日早上6点10分 'TRUNC(LEAST(NEXT_DAY(SYSDATE, ''SATURDAY"), NEXT_DAY(SYSDATE, "SUNDAY"))) + （6×60+10）/（24×60）'
每天凌晨0点执行
TRUNC(sysdate+1)
每天凌晨1点执行
TRUNC(sysdate+1)+1/24
每天早上8点30分执行
TRUNC(SYSDATE+1)+(8*60+30)/(24*60)
3、每周定时执行
例如：
每周一凌晨2点执行
TRUNC(next_day(sysdate,1))+2/24
TRUNC(next_day(sysdate,'星期一'))+2/24
每周二中午12点执行
TRUNC(next_day(sysdate,2))+12/24
TRUNC(next_day(sysdate,'星期二'))+12/24
4、每月定时执行
例如：
每月1日凌晨0点执行
TRUNC(LAST_DAY(SYSDATE)+1)
每月1日凌晨1点执行
TRUNC(LAST_DAY(SYSDATE)+1)+1/24
5、每季度定时执行
每季度的第一天凌晨0点执行
TRUNC(ADD_MONTHS(SYSDATE,3),'q')
每季度的第一天凌晨2点执行
TRUNC(ADD_MONTHS(SYSDATE,3),'q')+2/24
每季度的最后一天的晚上11点执行
TRUNC(ADD_MONTHS(SYSDATE+ 2/24,3),'q')-1/24
6、每半年定时执行
例如：
每年7月1日和1月1日凌晨1点执行
ADD_MONTHS(TRUNC(sysdate,'yyyy'),6)+1/24
7、每年定时执行
例如：
每年1月1日凌晨2点执行
ADD_MONTHS(TRUNC(sysdate,'yyyy'),12)+2/24
根据规律来算，其实就非常简单了。
8、每月的第一天跟最后一天
oracle 获取 每月第一天 最后一天
博客分类： oracle
Oracle
方法一：
select to_char(trunc(add_months(last_day(sysdate), -1) + 1), 'yyyy-mm-dd') "本月第一天",to_char(last_day(sysdate), 'yyyy-mm-dd') "本月最后一天" from dual;
方法二：
select trunc(sysdate, 'MONTH') 本月第一天,to_date(to_char(last_day(sysdate),'YYYY-MM-DD'),'YYYY-MM-DD') 本月最后一天 from dual;
方法三：
select trunc(sysdate, 'MONTH') 本月第一天,to_char(last_day(sysdate), 'YYYY-MM-DD') 本月最后一天 from dual;
4、oracle查询表名：
select table_name from all_tables t where table_name like '%SEND%'
当库中的表中太多太多的时候，可以查询所有库中的表名
select table_name from user_tables
查询用户下面的表

5、创建索引：
CREATE INDEX "IPIQXT"."TZHS10_SUBMIT" ON "IPIQXT"."TZHS10_SUBMIT"
(
"ID"
)
PCTFREE 10 INITRANS 2 MAXTRANS 255 NOLOGGING COMPUTE STATISTICS STORAGE
(
INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645 PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT
)
TABLESPACE "MBOP" ;
6、创建job：
--两种方法 三十分钟执行一次存储过程DAYBAKDATA
1、VARIABLE jobno number;
BEGIN
DBMS_JOB.SUBMIT(:jobno, 'DAYBAKDATA;', sysdate , 'SYSDATE+30/1440');
commit;
END;
2、DECLARE
X NUMBER;
BEGIN
SYS.DBMS_JOB.SUBMIT
( job       => X
,what      => 'DAYBAKDATA;'
,next_date => SYSDATE+30/1440
,interval  => 'SYSDATE+30/1440'
,no_parse  => FALSE
);
SYS.DBMS_OUTPUT.PUT_LINE('Job Number is: ' || to_char(x));
COMMIT;
END;
7、打印
dbms_output.put_line('归档了一条彩信发送状态');
8、创建索引：
CREATE INDEX "MY_SUBMIT4" ON "MY_SUBMIT4"
(
"ID"
)

10、创建触发器
create or replace
TRIGGER bjcbsend
AFTER INSERT ON a_SUBMIT
FOR EACH ROW
DECLARE
BEGIN

--sql操作 以上是插入数据时候的触发器 以下注释的是如果你想要修改某个字段的时候的触发器 只需把INSERT改成UPDATE  然后加上以下代码
--  if updating('字段') and :NEW.字段> '100'  then
--       执行sql操作
-- end if;
--END;
9、创建序列
CREATE SEQUENCE "IPIQXT"."SEQ_ABLEPHOTO"
MINVALUE 1 MAXVALUE 9999999999999999999999999999
INCREMENT BY 1 START WITH 20000 CACHE 20 NOORDER NOCYCLE ;
10、创建函数
--从表T_a 中取出phone 条件是AREANO =传入的参数areano1
create or replace
function vphone (areano1 in varchar2)
return varchar2
is
phone1 varchar2(20);
begin
select phone into phone1 from T_a where rownum=1 and AREANO = areano1 and flag
return(phone1);
end ;