oracle SQL葵花宝典(续写中)

 
查询某列重复的数据
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 ;