在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;