Oracle SQL Subtle SQL statement (ii)

-If there is an update, the insertion is implemented with a statement that does not exist
DROP TABLE t_mg;
CREATE TABLE t_mg (Code VARCHAR2 (Ten), 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 isn't matched then
INSERT (Code, NAME) VALUES (B.code, b.name);

--Extract/delete duplicate records
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 <> (Selectmin (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;

--The different environment for in/exists
--t_orders.customer_id with index
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 has index
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 ', ten) 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 ');

--Not recommended for use
SELECT * from T_FBI WHERE to_char (dt, ' yyyy-mm-dd ') = ' 2006-09-21 ';

--Commit/rollback in loops
DROP TABLE T_loop PURGE;
Create TABLE T_loop as SELECT * from User_objects WHERE 1=2;

SELECT * from T_loop;

--Progressive Submission
DECLARE
BEGIN
For cur in (SELECT * from user_objects) LOOP
INSERT into T_loop VALUES cur;
COMMIT;
END LOOP;
END;

--Analog Batch Submission
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 >=
COMMIT;
END IF;
END LOOP;
COMMIT;
END;

--Real Batch Submissions
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 realize batch
FORALL i in 1.. RECs. COUNT
INSERT into T_loop VALUES RECs (i);
COMMIT;
EXIT when Cur%notfound;
END LOOP;
CLOSE cur;
END;

Oracle SQL Subtle SQL statement (ii)