Explain SQL statements in Oracle SQL

 

1. Convert rows into columns
Drop table t_change_lc;
Create Table t_change_lc (card_code varchar2 (3), Q number, Bal number );

Insert into t_change_lc
Select '001' card_code, rownum Q, trunc (dbms_random.value * 100) BAL from dual connect by rownum <= 4
Union
Select '002 'card_code, rownum Q, trunc (dbms_random.value * 100) BAL from dual connect by rownum <= 4;

Select * From t_change_lc;

Select a. card_code,
Sum (decode (A. Q, 1, A. Bal, 0) Q1,
Sum (decode (A. Q, 2, A. Bal, 0) Q2,
Sum (decode (A. Q, 3, A. Bal, 0) Q3,
Sum (decode (A. Q, 4, A. Bal, 0) Q4
From t_change_lc
Group by A. card_code
Order by 1;

2. Convert columns into rows
Drop table t_change_cl;
Create Table t_change_cl
Select a. card_code,
Sum (decode (A. Q, 1, A. Bal, 0) Q1,
Sum (decode (A. Q, 2, A. Bal, 0) Q2,
Sum (decode (A. Q, 3, A. Bal, 0) Q3,
Sum (decode (A. Q, 4, A. Bal, 0) Q4
From t_change_lc
Group by A. card_code
Order by 1;

Select * From t_change_cl;

Select T. card_code,
T. Rn Q,
Decode (T. Rn, 1, T. Q1, 2, T. Q2, 3, T. Q3, 4, T. Q4) BAL
From (select a. *, B. Rn
From t_change_cl,
(Select rownum rn from dual connect by rownum <= 4) B) T
Order by 1, 2;

3. Convert rows into columns and merge Columns
Drop table t_change_lc_comma;
Create Table t_change_lc_comma as select card_code, 'quarter _ '| Q As Q from t_change_lc;

Select * From t_change_lc_comma;

Select t1.card _ code, substr (max (sys_connect_by_path (t1.q, ';'), 2) q
From (select a. card_code,
A. Q,
Row_number () over (partition by A. card_code order by A. Q) Rn
From t_change_lc_comma A) T1
Start with t1.rn = 1
Connect by t1.card _ code = prior t1.card _ code
And t1.rn-1 = prior t1.rn
Group by t1.card _ code;

4. columns to be converted and split into rows
Drop table t_change_cl_comma;
Create Table t_change_cl_comma
Select t1.card _ code, substr (max (sys_connect_by_path (t1.q, ';'), 2) q
From (select a. card_code,
A. Q,
Row_number () over (partition by A. card_code order by A. Q) Rn
From t_change_lc_comma A) T1
Start with t1.rn = 1
Connect by t1.card _ code = prior t1.card _ code
And t1.rn-1 = prior t1.rn
Group by t1.card _ code;

Select * From t_change_cl_comma;

Select T. card_code,
Substr (T. Q,
Instr (';' | T. Q, ';', 1, RN ),
Instr (T. q | ';', ';', 1, RN)-instr (';' | T. q, ';', 1, RN) q
From (select a. card_code, A. Q, B. Rn
From t_change_cl_comma,
(Select rownum rn from dual connect by rownum <= 100) B
Where instr (';' | A. Q, ';', 1, RN)> 0) T
Order by 1, 2;

5. Insert a record into multiple tables based on conditions
Drop table t_ia_src;
Create Table t_ia_src as select 'A' | rownum C1, 'B' | rownum C2 from dual connect by rownum <= 5;
Drop table t_ia_dest_1;
Create Table t_ia_dest_1 (flag varchar2 (10), c varchar2 (10 ));
Drop table t_ia_dest_2;
Create Table t_ia_dest_2 (flag varchar2 (10), c varchar2 (10 ));
Drop table t_ia_dest_3;
Create Table t_ia_dest_3 (flag varchar2 (10), c varchar2 (10 ));

Select * From t_ia_src;
Select * From t_ia_dest_1;
Select * From t_ia_dest_2;
Select * From t_ia_dest_3;

Insert all
When (C1 in ('a1', 'a3') then
Into t_ia_dest_1 (flag, c) values (flag1, C2)
When (C1 in ('a2 ', 'a4') then
Into t_ia_dest_2 (flag, c) values (flag2, C2)
Else
Into t_ia_dest_3 (flag, c) values (flag1 | flag2, C1 | C2)
Select C1, C2, 'f1' flag1, 'F2' flag2 from t_ia_src;

6. Update if it exists. Insert a statement if it does not exist.
Drop table t_mg;
Create Table t_mg (Code varchar2 (10), name varchar2 (10 ));

Select * From t_mg;

Merge into t_mg
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 );

7. Extraction/deletion of 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) name from dual connect by rownum <= 2;

Select * From t_dup;

Select * From t_dup A where 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 has an index
Select .*
From t_employees
Where a. employee_id in
(Select B. sales_rep_id from t_orders B where B. customer_id = 12 );

Select .*
From t_employees
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 an index
Select .*
From t_employees
Where a. department_id = 10
And exists
(Select 1 from t_orders B where a. employee_id = B. sales_rep_id );

Select .*
From t_employees
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
Select rownum RN, dbms_random.string ('Z', 10) Name, sysdate + dbms_random.value * 10 dt from dual
Connect by rownum <= 10;

Create index idx_nonfbi on t_fbi (DT );

Drop index idx_fbi_1;
Create index idx_fbi_1 on t_fbi (trunc (DT ));

Select * From t_fbi where trunc (dt) = to_date ('2017-09-21 ', 'yyyy-mm-dd ');

-- Not recommended
Select * From t_fbi where to_char (DT, 'yyyy-mm-dd') = '2017-09-21 ';

-- Commit/rollback in Loop
Drop table t_loop purge;
Create Table t_loop as select * From user_objects where 1 = 2;

Select * From t_loop;

-- Submit data row by row
Declare
Begin
For cur in (select * From user_objects) loop
Insert into t_loop values cur;
Commit;
End loop;
End;

-- Simulate batch submit http://blog.knowsky.com/
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;

-- Real batch submission
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;

-- Pessimistic locking/Optimistic Locking
Drop table t_lock purge;
Create Table t_lock as select 1 ID from dual;

Select * From t_lock;

-- Common Implementation logic, implicit bug
Declare
V_cnt number;
Begin
-- There is a concurrency bug.
Select max (ID) into v_cnt from t_lock;

-- Here for other operation
V_cnt: = v_cnt + 1;
Insert into t_lock (ID) values (v_cnt );
Commit;
End;

-- Security Implementation logic in a high concurrency Environment
Declare
V_cnt number;
Begin
-- Get lock for the specified row
Select ID into v_cnt from t_lock where id = 1 for update;
-- Continue the following operations when the lock is available
Select max (ID) into v_cnt from t_lock;

-- Here for other operation
V_cnt: = v_cnt + 1;
Insert into t_lock (ID) values (v_cnt );
Commit; -- submit and release lock
End;

-- Hard parsing/soft Parsing
Drop table t_hard purge;
Create Table t_hard (id int );

Select * From t_hard;

Declare
SQL _1 varchar2 (200 );
Begin
-- Hard parse
-- The equivalent statement in Java is statement.exe cute ()
For I in 1 .. 1000 Loop
SQL _1: = 'insert into t_hard (ID) values ('| I | ')';
Execute immediate SQL _1;
End loop;
Commit;

-- Soft parse
-- The equivalent statement in Java is preparedstatement.exe cute ()
SQL _1: = 'insert into t_hard (ID) values (: ID )';
For I in 1 .. 1000 Loop
Execute immediate SQL _1
Using I;
End loop;
Commit;
End;

 

-- Correct PagingAlgorithm
Select *
From (select a. *, rownum Rn
From (select * From t_employees order by first_name)
Where rownum< = 500)
Where rn> 480;

-- Paging algorithm (why not this one)
Select a. *, rownum Rn
From (select * From t_employees order by first_name)
Where rownum <= 500 and rownum> 480;

-- Paging algorithm (why not this one)
Select B .*
From (select a. *, rownum Rn
From t_employees
Where rownum <= 500
Order by first_name) B
Where B. Rn> 480;

-- OLAP
-- Total Subtotal
Select case
When a. deptno is null then
'Total'
When a. deptno is not null and A. empno is null then
'Subtotal'
Else
''| A. deptno
End deptno,
A. empno,
A. ename,
Sum (A. Sal) total_sal
From Scott. EMP
Group by grouping sets (A. deptno), (A. deptno, A. empno, A. ename ),());

-- Group sorting
Select a. deptno,
A. empno,
A. ename,
A. Sal,
-- Skip rank
Rank () over (partition by A. deptno order by A. Sal DESC) R1,
-- Intensive rank
Dense_rank () over (partition by A. deptno order by A. Sal DESC) R2,
-- Sort by group
Rank () over (order by Sal DESC) r3
From Scott. EMP
Order by A. deptno, A. Sal DESC;

-- Compare the data of the current row with the data of the first or last n rows
Select a. empno,
A. ename,
A. Sal,
-- The preceding line
Lag (A. Sal) over (order by A. Sal DESC) lag_1,
-- The following three rows
Lead (A. Sal, 3) over (order by A. Sal DESC) lead_3
From Scott. EMP
Order by A. Sal DESC;