oracle常用基本語句（持續更新）

-基本串連操作

sqlplus scmgt/scmgt as sysdba串連oracle資料庫 //scmgt scmgt

sqlplus "scmgt/scmgt @testdb"  串連另外個資料庫
SELECT NAME FROM V$database;   查看當前資料庫名
select TABLE_NAME from all_tables;  查看所有表

-擷取最新的10條資料

select * from (select * from CH_T_SONG_INFO order by seq desc) where rownum<=10;

-比較A表跟B表的主鍵有多少不同
select count(*) from (select ALBUM_ID from CH_T_ALBUM_INFO_0326 where ALBUM_ID not in ( select ALBUMID from ARTIST_ALBUM) );

-備份表
create table CH_T_MUSIC_SOURCE_INFO_0319 as select * from CH_T_MUSIC_SOURCE_INFO

-重新命名表
rename CH_T_MUSIC_SOURCE_INFO to CH_T_MUSIC_SOURCE_INFO_new;

-if判斷
SINGGER_ID= DECODE(SINGGER_ID,'',''||VROW.ARTISTID,SINGGER_ID||','||VROW.ARTISTID)

-日期
跟目前時間比較
to_date(t.INVALID_DATE,'yyyy-mm-dd hh24:mi:ss')<SYSDATE;
2個時間比較
select count(*) from CH_T_SONG_INFO_ACTION_201303 t where t.insert_date>to_date('2013-03-20 17:00:45','yyyy-mm-dd hh24:mi:ss');

重複資料刪除資料保留一條
DELETE from CH_T_FTP_MUSIC_FILE_RES
 WHERE (seq) IN
       (select seq
          from CH_T_FTP_MUSIC_FILE_RES a
         inner join (SELECT CONTENT_ID, BIZ_TYPE, MUSIC_FORMAT_TYPE
                      FROM CH_T_FTP_MUSIC_FILE_RES
                     GROUP BY CONTENT_ID, BIZ_TYPE, MUSIC_FORMAT_TYPE
                    HAVING COUNT(seq) > 1) b
            on a.CONTENT_ID = b.CONTENT_ID
           and a.BIZ_TYPE = b.BIZ_TYPE
           and a.MUSIC_FORMAT_TYPE = b.MUSIC_FORMAT_TYPE)
   AND ROWID NOT IN (SELECT MIN(ROWID)
                       FROM CH_T_FTP_MUSIC_FILE_RES
                      GROUP BY CONTENT_ID, BIZ_TYPE, MUSIC_FORMAT_TYPE

                     HAVING COUNT(*) > 1);

分頁語句

select a.* from (select c.*,rownum num from CH_T_PRODUCT_CRBT c where c.status<>'2' and rownum<="+endIndex+") a where num>"+startIndex

擷取序號語句

select SEQ_FTP_MUSIC_FILE_RES.nextval from dual

獲得表的建立語句
set pagesize 0;
set long 90000;
set feedback off;
set echo off;
spool get_schema.sql;
select dbms_metadata.get_ddl('TABLE','CH_T_SONG_INFO') from dual;  

改變欄位

alter table CH_T_SINGGER_INFO modify(SINGGER_ID VARCHAR2(1024));

刪除,建立索引

DROP INDEX IDX_ALM_INFO_ALMID ;

create index IDX_ALM_INFO_ALMID on CH_T_ALBUM_INFO (
   ALBUM_ID ASC
)

建立主鍵

alter table CH_T_ALBUM_INFO add constraint pk_ALBUM_ID primary key (ALBUM_ID);

查詢表索引

select index_name from user_indexes where table_name='CH_T_ALBUM_INFO';
select index_name from user_indexes where table_name='CH_T_ALBUM_INFO_0408';