在Oracle中找出重複的紀錄的方法

在Oracle中找出重複的紀錄的方法
SELECT aid FROM atable GROUP BY aid HAVING COUNT(*) >1;

刪除重複的記錄
DELETE FROM atable a where a.ROWID!=(SELECT MAX(ROWID) FROM atable b WHERE a.aid=b.aid;

在ORACLE/PLSQL裡使用游標FOR迴圈(cursor FOR loop)

藉由從abc_stu資料表內抓取該學號的生日，來更新stu資料表裡每個學號的生日。

DECLARE
CURSOR cur IS
SELECT stuid FROM stu;
BEGIN
FOR rec IN cur
LOOP
UPDATE stu
SET (birth_yr,birth_mm,birth_dd)=
(SELECT birth_yr,birth_mm,birth_dd FROM abc_stu WHERE stuid=rec.stuid)
WHERE stuid=rec.stuid;
END LOOP;
END;

Oracle/PLSQL: If Exists Update Else Insert

If data exists then update it, otherwise insert it.
In Oracle/PLSQL

BEGIN
UPDATE TABLE_NAME SET COL='a' WHERE ID='foobar';
IF sql%rowcount = 0 THEN
INSERT INTO TABLE_NAME (ID,COL) VALUES('foobar','a');
END IF;
END;

Oracle中設定auto increment的一些相關筆記

清空資料表

truncate table TABLE_NAME;

設定Auto Increment
參考資料：
http://www.lifeaftercoffee.com/2006/02/17/how-to-create-auto-increment-columns-in-oracle/

先建立一個sequence

create sequence SEQUENCE_NAME
start with 1
increment by 1
nocache;

再建立一個trigger

create or replace trigger TRIGGER_NAME
before insert
on TABLE_NAME
referencing NEW as NEW
for each row
begin
select SEQUENCE_NAME.nextval into :NEW.AUTO_INCREMENT_COLUMN from dual;
end;
/

這樣就完成了

SEQUENCE有下列參數：MINVALUE、MAXVALUE、START WITH、INCREMENT BY、CACHE、NOCACHE、CYCLE

要加nocache，sequence才不會亂跳

SEQUENCE到達最大值之前不能重新計數，只能用DROP刪除後再重新CREATE建立。
可以用ALTER來修改SEQUENCE的參數

ALTER SEQUENCE seq01
INCREMENT BY 100;

用NEXTVAL取得下一個值

SELECT seq01.NEXTVAL FROM DUAL;

用CURRVAL取的目前值

SELECT seq01.CURRVAL FROM DUAL;