Oracle SQL精妙SQL語句講解（二）

- 如果存在就更新，不存在就插入用一個語句實現?
DROP TABLE t_mg;?
CREATE TABLE t_mg(code VARCHAR2(10), NAME VARCHAR2(10));?

SELECT * FROM t_mg;?

MERGE INTO t_mg a?
USING (SELECT 'the code' code, 'the name' NAME FROM dual) b?
ON (a.code = b.code)?
WHEN MATCHED THEN?
UPDATE SET a.NAME = b.NAME?
WHEN NOT MATCHED THEN?
INSERT (code, NAME) VALUES (b.code, b.NAME);?

-- 抽取/刪除重復記錄?
DROP TABLE t_dup;?
CREATE TABLE t_dup AS SELECT 'code_'||ROWNUM code, dbms_random.string('z',5)NAME FROM dual CONNECT BY ROWNUM<=10;?
INSERT INTO t_dup SELECT 'code_'||ROWNUM code, dbms_random.string('z',5) NAMEFROM dual CONNECT BY ROWNUM<=2;?

SELECT * FROM t_dup;?

SELECT * FROM t_dup aWHERE a.ROWID <> (SELECT MIN(b.ROWID) FROM t_dup b WHERE a.code=b.code);?

SELECT b.code, b.NAME?
FROM (SELECT a.code,?
a.NAME,?
row_number() over(PARTITION BY a.code ORDER BY a.ROWID) rn?
FROM t_dup a) b?
WHERE b.rn > 1;?

-- IN/EXISTS的不同適用環境?
-- t_orders.customer_id有索引?
SELECT a.*?
FROM t_employees a?
WHERE a.employee_id IN?
(SELECT b.sales_rep_id FROM t_orders b WHERE b.customer_id = 12);?

SELECT a.*?
FROM t_employees a?
WHERE EXISTS (SELECT 1?
FROM t_orders b?
WHERE b.customer_id = 12?
AND a.employee_id = b.sales_rep_id);?

--t_employees.department_id有索引?
SELECT a.*?
FROM t_employees a?
WHERE a.department_id = 10?
AND EXISTS?
(SELECT 1 FROM t_orders b WHERE a.employee_id = b.sales_rep_id);?

SELECT a.*?
FROM t_employees a?
WHERE a.department_id = 10?
AND a.employee_id IN (SELECT b.sales_rep_id FROM t_orders b);?

-- FBI?
DROP TABLE t_fbi;?
CREATE TABLE t_fbi AS?
SELECT ROWNUM rn, dbms_random.STRING('z',10) NAME , SYSDATE + dbms_random.VALUE* 10 dt FROM dual?
CONNECT BY ROWNUM <=10;?

CREATE INDEX idx_nonfbiON t_fbi(dt);?

DROP INDEX idx_fbi_1;?
CREATE INDEX idx_fbi_1 ON t_fbi(trunc(dt));?

SELECT * FROM t_fbiWHERE trunc(dt) = to_date('2006-09-21','yyyy-mm-dd') ;?

-- 不建議使用?
SELECT * FROM t_fbi WHERE to_char(dt, 'yyyy-mm-dd') = '2006-09-21';?

-- LOOP中的COMMIT/ROLLBACK?
DROP TABLE t_loop PURGE;?
create TABLE t_loop AS SELECT * FROM user_objects WHERE 1=2;?

SELECT * FROM t_loop;?

-- 逐行提交?
DECLARE?
BEGIN?
FOR cur IN (SELECT * FROM user_objects) LOOP?
INSERT INTO t_loop VALUES cur;?
COMMIT;?
END LOOP;?
END;

-- 模擬批量提交
DECLARE?
v_count NUMBER;?
BEGIN?
FOR cur IN (SELECT * FROM user_objects) LOOP?
INSERT INTO t_loop VALUES cur;?
v_count := v_count + 1;?
IF v_count >= 100 THEN?
COMMIT;?
END IF;?
END LOOP;?
COMMIT;?
END;?

-- 真正的批量提交?
DECLARE?
CURSOR cur IS?
SELECT * FROM user_objects;?
TYPE rec IS TABLE OF user_objects%ROWTYPE;?
recs rec;?
BEGIN?
OPEN cur;?
WHILE (TRUE) LOOP?
FETCH cur BULK COLLECT?
INTO recs LIMIT 100;?
-- forall 實現批量?
FORALL i IN 1 .. recs.COUNT?
INSERT INTO t_loop VALUES recs (i);?
COMMIT;?
EXIT WHEN cur%NOTFOUND;?
END LOOP;?
CLOSE cur;?
END;?