SQL วันเดียว

สำหรับตอนนี้ มาจากการที่มักจะมีคำถามอยู่บ่อย ๆ ว่า SQL บน Oracle ต่างจาก SQL บน SQLServer อย่างไร หรือต่างจาก SQL บน Relational Database Management System (RDBMS) ตัวอื่น ๆ อย่างไร ครั้นพอจะจัด Full Course Training ก็ดูเหมือนว่าอาจจะไม่จำเป็น เพราะคนที่ทำงานโดยใช้ SQL มา ก็น่าจะมีทักษะอยู่แล้ว แต่อยากรู้เฉพาะจุดที่แตกต่างกัน มากกว่า อย่างไรก็ตามผมคิดของผมเอาเองว่าการรู้ว่า Oracle SQL มี Features สำคัญ ๆ อย่างไร น่าจะเป็นประโยชน์กับผู้ใช้งานบ้างไม่มากก็น้อย อาจจะจำรายละเอียดไม่ได้ แต่อย่างน้อยต่อไปเมื่อต้องการใช้ก็สามารถที่จะไปค้นดูจากใน Reference ได้ต่อไป โจทย์จึงอยู่ที่ว่าทำอย่างไรจึงจะสามารถสรุปเอาเฉพาะส่วนหลัก ๆ (แต่ครอบคลุม) เนื้อหาของ SQL บน Oracle มาให้ผู้ที่ต้องการศึกษาได้ โดยใช้เวลาในการศึกษาไม่มาก จึงได้บทสรุปย่อ ๆ ขนาดเล็กมาบทหนึ่ง

ตารางที่ใช้เป็นตารางที่อยู่ใน Schema(User) HR ซึ่งเป็นดีฟอลต์ของ Oracle ที่มีมาตั้งแต่ตอนสร้างฐานข้อมูล ซึ่ง Schema(User) HR ใน Oracle10g จะล๊อคอยู่โดยดีฟอลต์ คุณจะต้องปลดล๊อคก่อนโดย ล็อกอินเข้าฐานข้อมูลด้วย User ที่มีสิทธิ์เป็น DBA แล้วใช้คำสั่ง

SQL> alter user hr account unlock;
User altered.

********************************************************************************
Chapter1: Basic SQL Statements
********************************************************************************
เราสามารถแบ่งประเภทของ SQL Statements ได้เป็น 5 ประเภทคือ
1. Data Retrieval
- SELECT
2. Data Manupulation Language (DML)
- INSERT
- UPDATE
- DELETE
- MERGE
3. Data Definition Language (DDL)
- CREATE
- ALTER
- DROP
- RENAME
- TRUNCATE
4. Transaction Control
- COMMIT
- ROLLBACK
- SAVEPOINT
5. Data Control Language (DCL)
- GRANT
- REVOKE

1. การใช้คำสั่ง SELECT โดยทั่วไป
select * from departments;
select department_id, department_name, manager_id, location_id from departments;
Note:
- คำสั่ง SQL จะใช้ตัวใหญ่หรือตัวเล็กก็ได้ (Not Case Sensitive)
2. การใช้ตัวคำนวณทางคณิตศาสตร์ เช่น + - * /
select last_name, salary, salary + 300 from employees;
Note:
- ถ้าค่าที่ใช้ในการคำนวณเป็น null ผลการคำนวณที่ได้จะเป็น null เสมอ
select last_name, job_id, salary, commission_pct, 12*salary*commission_pct from employees;
3. ชื่อย่อของ Column (Alias)
select last_name as name, commission_pct comm from employees;
select last_name "Name", salary*12 "Annual Salary" from employees;
4. การนำข้อมูลในต่างคอลัมน์มาแสดงผลต่อกัน (Concatenate)
select last_name || job_id as "Employees" from employees;
5. การพิมพ์ค่าตายตัว (Constant) ลงร่วมกับค่าที่อยู่ในคอลัมน์ (Literal Character String)
select last_name || ' : 1 Month salary = ' || salary Monthly from employees;
6. การกำจัดแถวซ้ำ
select department_id from employees;
select DISTINCT department_id from employees;
7. การดูโครงสร้างตาราง
DESC employees;
select * from tab;
select * from user_tables;
select * from user_tab_columns;


********************************************************************************
Chapter2: Restricting and sorting data
********************************************************************************
1. คิวรีเลือกเฉพาะแถวที่ต้องการตามเงื่อนไข โดยใช้เครื่องหมายเท่ากับ
select employee_id, last_name, job_id, department_id from employees where department_id = 90;
select last_name, job_id, department_id from employees where last_name = 'Whalen';
select last_name, department_id, hire_date from employees where hire_date = '03-jan-90';
2. คิวรีเลือกเฉพาะแถวที่ต้องการตามเงื่อนไข โดยใช้เครื่องหมายเปรียบเทียบอื่น ๆ (Comparison condition)
= > >= < <= <> != ^=
-- Between
select last_name, salary from employees where salary BETWEEN 2500 and 3500;
-- IN
select employee_id, last_name, salary, manager_id from employees where manager_id IN (100,101,201);
-- LIKE
select first_name from employees where first_name like 'S%';
select last_name, hire_date from employees where hire_date like '%95';
select last_name from employees where last_name like '_o%';
select employee_id, last_name, job_id from employees where job_id like '%SA\_%' escape '\'
-- NULL
select last_name, manager_id from employees where manager_id is null;
Logical condition
-- AND
select employee_id, last_name, job_id, salary from employees where salary >= 10000 and job_id like '%MAN%';
-- OR
select employee_id, last_name, job_id, salary from employees where salary >= 10000 or job_id like '%MAN%';
-- NOT
select last_name, job_id from employees where job_id not in ('IT_PROG','ST_CLERK','SA_REP');
3. การเรียงลำดับแถว (Order by)
select last_name, job_id, department_id,hire_date from employees order by hire_date desc;
select employee_id, last_name, salary*12 annsal from employees order by annsal;
select last_name, department_id, salary from employees order by department_id, salary desc;

********************************************************************************
Chapter3: ฟังก์ชั่นที่ใช้กับข้อมูลแต่ละแถว (Single Row Functions)
********************************************************************************
1. ฟังก์ชันที่ใช้กับตัวอักษร Character functions
- Case manipulation functions: LOWER, UPPER, INITCAP
select 'The job id for ' || Initcap(first_name) || ' ' || UPPER (last_name) || ' is ' || LOWER(job_id) as "EMPLOYEE DETAILS" from employees;
select employee_id, last_name, department_id from employees where last_name = 'higgins';
select employee_id, last_name, department_id from employees where LOWER(last_name) = 'higgins';
select employee_id, last_name, department_id from employees where INITCAP(last_name) = 'Higgins';
- Character-manipulation functions: CONCAT, SUBSTR, LENGTH, INSTR, LPAD, RPAD, TRIM
select employee_id, concat(first_name, last_name) name, job_id, LENGTH(last_name), INSTR(last_name,'n',1,2) "Contains 'a'?" from employees where SUBSTR(job_id,4) = 'REP';
select employee_id, concat(first_name, last_name) name, job_id, LENGTH(last_name), INSTR(last_name,'a') "Contains 'a'?" from employees where SUBSTR(last_name,-1,1) = 'n';

2. ฟังก์ชันที่ใช้กับตัวเลข (Number functions)
--ROUND
select ROUND(45.923,2), ROUND(45.923,0), ROUND(45.923,-2) from dual;
--TRUNC
select TRUNC(45.923,2), TRUNC(45.923), TRUNC(45.923,-1) from dual;
--MOD
select last_name, salary, MOD(salary,5000) from employees where job_id = 'SA_REP';

3. ฟังก์ชั่นที่ใช้กับวันที่ (Date functions)
select last_name, hire_date from employees where last_name like 'G%';
select SYSDATE from dual;
-- Arithmetic with Dates
select sysdate +1, sysdate -1, sysdate - date '2009-05-20', sysdate + 1/24 from dual;
select last_name, (sysdate - hire_date) / 7 as weeks from employees where department_id = 90;
-- Date Functions

select MONTHS_BETWEEN(date '1995-09-01',date '1994-01-11') from dual;
select ADD_MONTHS (date '1994-01-11',6) from dual; -- Add 6 months
select NEXT_DAY (sysdate,'FRIDAY') from dual; -- Next Friday
select LAST_DAY (sysdate) from dual;-- Last day of month

select employee_id, hire_date, MONTHS_BETWEEN(sysdate, hire_date) hire_month, ADD_MONTHS(hire_date,6) review, NEXT_DAY(sysdate,'FRIDAY'), LAST_DAY(hire_date) from employees
where months_between (SYSDATE, hire_date) > 175;

select
ROUND(sysdate,'MONTH') ,
ROUND(sysdate,'YEAR') ,
TRUNC(sysdate,'MONTH') ,
TRUNC(sysdate,'YEAR')
from dual;
select employee_id, hire_date, ROUND(hire_date,'MONTH'), TRUNC(hire_date, 'MONTH') from employees where hire_date like '%97';

4. ฟังก์ชั่นที่ใช้เพื่อแปลงชนิดของข้อมูล (Data type conversion functions)
-- การแปลงแบบปริยาย (Implicit conversion)
VARCHAR2 or CHAR => NUMBER
VARCHAR2 or CHAR => DATE
NUMBER => VARCHAR2
DATE => VARCHAR2
-- การแปลงแบบจงใจ (Explicit conversion)
Convert to Character: TO_CHAR
can convert number,date to character
Convert to Number: TO_NUMBER
can convert character to number
Convert to Date: TO_DATE
can convert character to date

select employee_id, TO_CHAR(hire_date,'MM/YY') "Month Hire" from employees where last_name = 'Higgins';
-- ส่วนประกอบของรูปแบบการแสดงผลวันที่ (Elements of the Date format)
YYYY = Full year in numbers
YEAR = Year spelled out
MM = 2-digit month
MONTH = Full name month
MON = 3-letter month
DAY = Full name day
DY = 3-letter day of week
DD = Numeric day of month
Q = Quarter of year
WW = Week of year
W = Week of month
DDD,DD,D = Day of year, month, week
J = Julian day
select TO_CHAR(sysdate,'DAY') from dual;

-- ส่วนประกอบของรูปแบบการแสดงผลเวลา (Time elements)
HH24:MI:SS AM เช่น 15:45:32 PM
DD "of" MONTH เช่น 12 of OCTOBER
AM or PM = Meridian indicator
A.M. or P.M.
HH or HH12 or HH24 = Hour of day
MI = Minute
SS = Second
SSSSS = Seconds past midnight (0-86399)
TH = แสดงลำดับ
SP = แสดงคำอ่าน
SPTH or THSP = แสดงคำอ่านของลำดับ
เช่น ddSPTH แปลงเป็นคำอ่านของวันที่แบบมีลำดับเช่น Fourteenth
select to_char(sysdate,'DDspTH') from dual;
select last_name, TO_CHAR(hire_date, 'fmDD Month YYYY') as hiredate from employees;
select last_name, TO_CHAR(hire_date, 'fmDdspth "of" Month YYYY fmHH:MI:SS AM') hiredate from employees;
เปลี่ยนภาษาในการแสดงผลวันที่
alter session set NLS_DATE_LANGUAGE=THAI;
แก้เป็นปีพุทธศักราช
alter session set NLS_CALENDAR='THAI BUDDHA';
- ใช้ TO_CHAR กับ number
TO_CHAR
9 = Numeric position
0 = Leading Zero
$ = Floating dollar sign
L = Floating local currency
MI = Minus signs ทางขวา
PR = วงเล็บครอบเลขติดลบ

select salary, TO_CHAR(salary, '$0099,9999.00') x_salary from employees where last_name = 'Ernst';
- Nesting functions
select last_name, manager_id, NVL(TO_CHAR(manager_id), 'No Manager') from employees where manager_id is null;
select TO_CHAR(NEXT_DAY(ADD_MONTHS(hire_date,6), 'FRIDAY'),'fmDay, Month DDth, YYYY') "Next 6 Month Review" from employees order by hire_date;

5. ฟังก์ชั่นอื่น ๆ
NVL (expr1,expr2) ถ้า expr1 เป็น null จึง expr2 ไม่งั้นเป็น expr1
select last_name, salary, commission_pct, (salary*12) + (salary*12*commission_pct) an_sal from employees;
select last_name, salary, NVL(commission_pct,0), (salary*12) + (salary*12*NVL(commission_pct,0)) an_sal from employees;
-- NVL2 (expr1,expr2,expr3) ถ้า expr1 เป็น null จึง expr3 ไม่งั้นเป็น expr2
select last_name, salary, commission_pct, NVL2(commission_pct,'SAL+COMM','SAL') income from employees where department_id in (50,80);
-- NULLIF (expr1,expr2) ถ้า expr1 = expr2 จึงให้มีค่าเป็น null ไม่งั้นเป็น expr1
select first_name, length(first_name) "expr1", last_name, length(last_name) "expr2", NULLIF(length(first_name), length(last_name)) result from employees;
-- COALESCE แสดงค่าที่เป็น Not Null ตัวแรกของสมาชิกในกลุ่มของ Expression
select last_name, commission_pct,salary,10, COALESCE(commission_pct, salary, 10) comm from employees order by commission_pct;

6. การใช้เงื่อนไขในคิวรี (Condition expression)
-- การใช้ CASE
CASE expr
WHEN comparison_expr1 THEN return_expr1
[WHEN comparison_expr2 THEN return_expr2
WHEN comparison_exprn THEN return_exprn
ELSE else_expr]
END
เช่น
select last_name, job_id, salary,
CASE job_id WHEN 'IT_PROG' THEN 1.10*salary
WHEN 'ST_CLERK' THEN 1.15*salary
WHEN 'SA_REP' THEN 1.20*salary
ELSE salary END "REVISED_SALARY"
from employees;

-- การใช้ DECODE
DECODE (col | expression, search1, result1[, search2, result2,...,][,default])
select last_name, job_id, salary,
DECODE(job_id, 'IT_PROG', 1.10*salary,
'ST_CLERK', 1.15*salary,
'SA_REP', 1.20*salary,
salary) revise_salary
from employees;

select last_name, salary,
DECODE(trunc(salary/2000,0),
0,0.00,
1,0.09,
2,0.20,
3,0.30,
4,0.40,
5,0.42,
6,0.44,
0.45) tax_rate
from employees
where department_id = 80;

********************************************************************************
Chapter4: การ Join
********************************************************************************
-- ใช้คำสั่งแบบ Oracle
select table1.column, table2.column from table1, table2 where table1.column1=table2.column2;
select table1.column, table2.column from table1 join table2 on (table1.column1 = table2.column2);
1. Cartesian products
select last_name from employees;
107 rows
select department_name from departments;
207 rows
select last_name, department_name from employees, departments
2889 rows

2. Equijoins
select employees.employee_id, employees.last_name, employee.department_id, departments.department_id
, departments.location_id
from employees , departments
where employees.department_id = departments.department_id;
-- Table alias
select e.employee_id, e.last_name, e.department_id,
d.department_id, d.location_id
from employees e, departments d
where e.department_id = d.department_id;
- Join more than 2 tables
select e.last_name, d.department_name, l.city
from employees e, departments d, locations l
where e.department_id = d.department_id
and d.location_id = l.location_id;

3. Non-Equijoins
select * from employees;
select * from jobs;
select e.last_name, e.salary, j.job_title,min_salary || ' - ' || max_salary
from employees e, jobs j
where e.salary between j.min_salary and j.max_salary;

4. Outer Joins
- เครื่องหมายบวก '(+)' อยู่ข้างที่ข้อมูลขาดหายไป (เป็น NULL)
select e.last_name, e.department_id, d.department_name
from employees e, departments d
where e.department_id(+) = d.department_id;
ุุหรือใช้ SQL1999
select e.last_name, e.department_id, d.department_name
from employees e
left outer join departments d
on (d.department_id = e.department_id);
select * from employees;

5. Self Joins
- การที่ตาราง Join กับตัวมันเอง ใช้ในกรณีที่มีความสัมพันธ์เป็นลำดับชั้นในตาราง เช่น หัวหน้า-ลูกน้องเป็นต้น
select worker.last_name || ' works for ' || manager.last_name
from employees worker, employees manager
where worker.manager_id = manager.employee_id;


- ใช้คำสั่ง SQL มาตรฐาน (SQL1999)
1. Cross Joins (Cartesian Product)
select last_name, department_name
from employees CROSS JOIN departments;

2. Natural Joins
select department_id, department_name, location_id, city
from departments NATURAL JOIN locations;

3. Using
select e.employee_id, e.last_name, d.location_id
from employees e JOIN departments d USING (department_id)
where location_id = 1400;

4. Inner Join with 'ON'
select e.employee_id, e.last_name, e.department_id, d.department_id, d.location_id
from employees e JOIN departments d
ON (e.department_id = d.department_id);

5. Three-Way Joins with 'ON'
select employee_id, city, department_name
from employees e
JOIN departments d on d.department_id = e.department_id
JOIN locations l on d.location_id = l.location_id;

6. Left(Right) Outer Join
select e.last_name, e.department_id, d.department_name
from employees e
LEFT OUTER JOIN departments d
ON (e.department_id = d.department_id);

select e.last_name, e.department_id, d.department_name
from employees e
RIGHT OUTER JOIN departments d
ON (e.department_id = d.department_id)

7. Full Outer Join
-- การ Join ที่แสดงค่าว่างของทั้งข้างซ้ายและขวาขึ้นมา เหมือนการเอา Left กับ Right Join มารวมกัน
select e.last_name, e.department_id, d.department_name
from employees e
FULL OUTER JOIN departments d
ON (e.department_id = d.department_id);

8. Additional Condition
select e.employee_id, e.last_name, e.department_id,
d.department_id, d.location_id
from employees e JOIN departments d
ON (e.department_id = d.department_id) AND e.manager_id = 149;

********************************************************************************
Chapter5: ฟังก์ชั่นที่ใช้กับข้อมูลเป็นกลุ่มของแถว (Group Functions)
********************************************************************************
1. การใช้ฟังก์ชัน
AVG([DISTINCT | ALL] n) ไม่เอา Null (ถ้าข้อมูลเป็น NULL ไม่เอามาคำนวณ)
COUNT({* | [DISTINCT | ALL] expr}) ถ้าเป็น * เอา Null (ถ้าไม่ใช้ * ไม่เอา NULL)
MAX([DISTINCT | ALL] expr) ไม่เอา Null
MIN([DISTINCT | ALL] expr) ไม่เอา Null
STDDEV([DISTINCT | ALL] x) ไม่เอา Null
SUM([DISTINCT | ALL] n) ไม่เอา Null
VARIANCE([DISTINCT | ALL] x) ไม่เอา Null

-- ตัวอย่าง ===========================================
select AVG(salary), MAX(salary), MIN(salary), SUM(salary)
from employees
where job_id like '%REP%';

select MIN(hire_date), MAX(hire_date) from employees;

select COUNT(*) from employees where department_id = 50;
-- 45
select COUNT(commission_pct) from employees where department_id = 80;
-- 34
select COUNT(distinct department_id) from employees;
-- 11
select AVG (commission_pct) from employees;
-- คำนวณเฉพาะ Rows ที่มี commission_pct ไม่เป็น NULL เท่านั้น คือเอา Rows ที่ commisiton_pct ไม่เป็น Null แล้วหาค่าเฉลี่ยจาก Rows เหล่านี้
select AVG(nvl(commission_pct,0)) from employees;

2. การใช้ฟังก์ชั่น กับ GROUP BY
select department_id, AVG(salary) from employees GROUP BY department_id ORDER BY AVG(salary);
- Group มากกว่า 1 คอลัมน์
select department_id dept_id, job_id, sum(salary)
from employees
GROUP BY department_id, job_id;
- เงือนไขในการ Group (HAVING)
select department_id, AVG(salary)
from employees
GROUP BY department_id
HAVING AVG(salary) > 8000
- More
select job_id, sum(salary) payroll
from employees
where job_id not like '%REP%'
group by job_id
HAVING SUM(salary) > 13000
ORDER BY SUM(salary);
- Nesting Group
select MAX(AVG(salary)) from employees group by department_id;

********************************************************************************
Chapter6: การใช้ Subquery
********************************************************************************
-- Single-row (ส่วนที่เป็น Subquery ให้ผลเพียงเรคคอร์ดเดียว)

select last_name, job_id from employees
where job_id =
(select job_id from employees where employee_id = 141);

select last_name, job_id, salary from employees
where job_id =
(select job_id from employees where employee_id = 141)
and salary >
(select salary from employees where employee_id = 143);

select last_name, job_id, salary from employees
where salary =
(select min(salary) from employees);

select department_id, min(salary)
from employees
group by department_id
having min(salary) >
(select min(salary) from employees where department_id = 50);

-- Multi-row (ส่วนที่เป็น Subquery ให้ผลหลายเรคคอร์ด)
select last_name, salary, department_id
from employees
where salary IN
(select min(salary) from employees group by department_id);

-- การใช้ ANY
select employee_id, last_name, job_id, salary
from employees
where salary < ANY
(select salary from employees where job_id = 'IT_PROG')
and job_id <> 'IT_PROG';
-- ข้อมูลในตาราง EMPLOYEES เอาเรคคอร์ดใด ๆ ที่ไม่ได้เป็น Programmer (IT_PROG) และมีค่า SALARY น้อยกว่า SALARY ใด ๆ ในตาราง EMPLOYEES ที่เป็น Programmer (IT_PROG) หรืออาจจะกล่าวอีกอย่างได้ว่า "เอาพนักงานในแผนกอื่นที่มีเงินเดือนน้อยกว่าโปรแกรมเมอร์ที่มีเงินเดือนมากที่สุด"

select employee_id, last_name, job_id, salary
from employees
where salary < ALL
(select salary from employees where job_id = 'IT_PROG')
and job_id <> 'IT_PROG';
-- ข้อมูลในตาราง EMPLOYEES เอาเรคคอร์ดใด ๆ ที่ไม่ได้เป็น Programmer (IT_PROG) และมีค่า SALARY น้อยกว่า SALARY ทุก SALARY ในตาราง EMPLOYEES ที่เป็น Programmer (IT_PROG) หรืออาจจะกล่าวอีกอย่างได้ว่า "เอาพนักงานในแผนกอื่นที่มีเงินเดือนน้อยกว่าโปรแกรมเมอร์ที่มีเงินเดือนน้อยที่สุด"

********************************************************************************
Chapter7: การเปลี่ยนแปลงข้อมูล (Manipulation Data)
********************************************************************************
1. การแทรกแถวข้อมูล Insert
INSERT INTO departments (department_id, department_name, manager_id, location_id)
VALUES (700, 'Public Relations', 100, 1700);

INSERT INTO departments (department_id, department_name)
VALUES (700, 'Public Relations');

INSERT INTO departments
VALUES (700, 'Public Relations',null, null);

- การแทรกแถวด้วยข้อมูลพิเศษ (เช่นวันที่ปัจจุบัน (SYSDATE))
INSERT INTO employees (employee_id, first_name, last_name, email, phone_number, hire_date, job_id, salary, commission_pct, manager_id, department_id)
VALUES (999, 'Louis', 'Popp', 'LPOPP1', '515.124.4567', SYSDATE, 'AC_ACCOUNT', 6900, NULL, 205, 100);

- การแทรกแถวด้วยข้อมูลที่เป็นวันที่
INSERT INTO employees
VALUES (1999,'Den','Raphealy','xDraphel','515.127.4561', TO_DATE('1999 12', 'YYYY MM'), 'AC_ACCOUNT', 11000, null, 100,30);

2. การแทรกแถวข้อมูลโดยเอาแถวข้อมูลจากอีกตารางหนึ่ง
INSERT INTO sales_reps(id, name, salary, commission_pct)
SELECT employee_id, last_name, salary, commission_pct
from employees
where job_id like '%REP%';

3. การเปลี่ยนแปลงข้อมูล
UPDATE employees SET department_id = 70
WHERE employee_id = 113;

UPDATE employees
SET job_id = (select job_id from employees where employee_id = 205),
salary = (select salary from employees where employee_id = 205)
WHERE employee_id = 114;

UPDATE employees e
SET salary = (select max_salary from jobs j where j.job_id = e.job_id)
WHERE job_id like '%REP';

4. การลบแถวข้อมูล
DELETE from departments
WHERE department_name = 'Finance';

DELETE employees
WHERE department_id = (select department_id from departments where department_name like '%Public%');
-- เมื่อรันคำสั่งแล้ว เมื่อคิวรีด้วย Subquery ที่อยู่ใน Insert into จะต้องปรากฎเรคคอร์ดนั้นด้วย ถ้าไม่ จะขึ้น Error
insert into
(select employee_id, last_name, email, hire_date, job_id, salary,department_id from employees where department_id = 50 WITH CHECK OPTION)
values (999998, 'Smith','JSMITH',to_date('07-JUN-99','dd-mon-rr'),'ST_CLERK',5000,40);

5. การใช้ค่าดีฟอลต์ที่กำหนดไว้ในโครงสร้างตาราง
update jobs set max_salary = DEFAULT where job_id = 'IT_PROG';

6. การใช้ MERGE (เปรียบเทียบค่าในคอลัมน์ของตารางหนึ่งกับของอีกตารางหนึ่ง ถ้า Match (เหมือนกัน) ก็ทำอย่างหนึ่ง หรือถ้าไม่ Match ก็ทำอีกอย่าง)
MERGE INTO employees e
using copy_emp c
on (e.employee_id = c.employee_id)
WHEN MATCHED THEN
UPDATE SET
e.salary = e.salary + c.extra_bonus
WHEN NOT MATCHED THEN
INSERT VALUES (c.employee_id,c.first_name,c.last_name,c.email,c.phone_number,c.hire_date,c.job_id,c.salary,c.commission_pct,c.manager_id,c.department_id);

7. การควบคุมธุรกรรม (Transaction Control)
Controlling Transactions
- Session A ==========================================
select * from employees where employee_id = 100 for update;
- Session B ==========================================
update employees set salary = salary + 2 where employee_id =100;
-- Transaction hang
- Session A ==========================================
update employees set salary = salary + 1 where employee_id =100;
commit;
- Session B ==========================================
-- Transaction successful wait for DCL

- SAVEPOINT ช่วยให้สามารถ Rollback ถึงเพียงบางจุด ไม่ต้อง Rollback ทั้งหมด
select * from regions;
insert into regions values (5,'Africa');
select * from regions;
SAVEPOINT before_lunch;
insert into regions values (6,'South East Asia');
select * from regions;
ROLLBACK TO before_lunch;
select * from regions;
ROLLBACK;
select * from regions;

********************************************************************************
Chapter8: การสร้างและจัดการตาราง
********************************************************************************
CREATE TABLE EMPLOYEE
(empid NUMBER(10)
,lastname VARCHAR2(25)
,firstname VARCHAR2(25)
,salary NUMBER(10,4) DEFAULT 10)
TABLESPACE users;

select * from user_tables;
select * from user_catalog;
select * from cat;

-- ชนิดของข้อมูลที่มีใน Oracle
VARCHAR2(size) => Variable-length character; size is a maximum size (upto 4000 bytes)
CHAR(size) => Fixed-length character; size is a maximum size
NUMBER(p,s) => p is precision จำนวนหลักทั้งหมดรวมตัวเลขหลังจุดทศนิยมแต่ไม่รวมจุด and s is scale จำนวนหลักของตัวเลขหลังจุดทศนืืยม
DATE => Date and time values
CLOB => Character data upto 4GB
BLOB => Binary data upto 4 GB
BFILE => Binary data ที่เก็บในไฟล์ที่อยู่นอก Database ขนาดใหญ่สุด 4GB
ROWID => เลขฐานสิบหกที่ใช้ชี้ที่อยู่ของแถว ในฐานข้อมูล
TIMESTAMP => วันที่ รวมกับเศษส่วนของวินาที
INTERVAL YEAR TO MONTH => เก็บช่วงเวลาเป็นปีและเดือน
INTERVAL DAY TO SECOND => เก็บช่วงเวลาเป็นวันและชั่วโมง,นาที และวินาที
เช่น
create table test_time (time_begin TIMESTAMP(6));
select * from test_time
insert into test_time select hire_date from employees;
commit;
select time_begin from test_time;
-- สร้างตารางที่ใส่จำนวนปีได้ถึง 3 หลัก
drop table test_time2;
create table test_time2 (time_duration INTERVAL YEAR (3) TO MONTH);
-- Insert ข้อมูลเข้าไป 120 เดือน ตรง MONTH(2) ถ้าใส่ 3 หรือตัด (2) ออก ก็จะได้ค่าเท่ากัน ตราบใดที่ค่าที่ใส่เข้าไปยังอยู่ใน YEAR(3) หรือ 999 ปีก็ใช้ได้
insert into test_time2 (time_duration) values (INTERVAL '120' MONTH(3));
select * from test_time2;
select sysdate + time_duration from test_time2;
insert into test_time2 (time_duration) values (INTERVAL '120' YEAR(3));
select sysdate + time_duration from test_time2;
insert into test_time2 (time_duration) values (INTERVAL '10-2' YEAR(2) TO MONTH);
select sysdate + time_duration from test_time2;

drop table test_time3;
create table test_time3 (time_duration INTERVAL DAY (1) TO SECOND);
insert into test_time3 (time_duration) values (INTERVAL '3' DAY (3));
insert into test_time3 (time_duration) values (INTERVAL '3' HOUR (3));
select * from test_time3;
select sysdate + time_duration from test_time3;

--Create table by using a subquery
CREATE TABLE dept80
as
select * from employees where department_id = 80;

-- Alter table
ALTER TABLE dept80 ADD new_col varchar2(20);
ALTER TABLE dept80 MODIFY new_col char(5);
ALTER TABLE dept80 set unused column new_col;
select * from user_unused_col_tabs;
ALTER TABLE dept80 drop unused column;
-- Drop table
DROP TABLE dept80;
-- Flashback เอาตารางที่ Drop ไปแล้วกลับมา
select * from dept80;
FLASHBACK TABLE dept80 TO BEFORE DROP;
-- Rename table
RENAME test_time to test_time1;
-- Truncate table
TRUNCATE TABLE test_time1;

********************************************************************************
Chapter9: การใช้ Constraints
********************************************************************************
drop table employee2;
-- การสร้าง Constraints พร้อม ๆ กับตาราง
create table employee2
(
empid number(10) UNIQUE
,lastname number(25) NOT NULL
,firstname number(25) NOT NULL
,salary number(10,4) CHECK (salary > 100)
,department_id number(4)
)
tablespace users;

select * from user_constraints where table_name = 'EMPLOYEE2'
select * from user_cons_columns where table_name = 'EMPLOYEE2';

-- เพิ่ม Constraints ทีหลัง
ALTER TABLE employee2 ADD CONSTRAINT employee_pk PRIMARY KEY (employee_id);
ALTER TABLE employee2 ADD CONSTRAINT employee_uq UNIQUE (first_name,last_name);
ALTER TABLE employee2 ADD CONSTRAINT employee_ck CHECK (salary > 0);

ALTER TABLE employee2 ADD CONSTRAINT employee_fk FOREIGN KEY (department_id)
REFERENCES departments (department_id)
ON DELETE CASCADE NOVALIDATE;
-- NOVALIDATE คือถ้ามีข้อมูลเก่าที่ละเมิดกฎอยู่ก็ไม่เป็นไร แต่ข้อมูลใหม่จากนี้ไปจะถูกบังคับด้วยกฎของ FK
-- สามารถใช้ ON DELETE SET NULL ก็ได้

ALTER TABLE employee2 DISABLE CONSTRAINT employee_fk;
ALTER TABLE employee2 ENABLE CONSTRAINT employee_fk;

ALTER TABLE employee2 DROP CONSTRAINT CASCADE CONSTRAINTS;
ALTER TABLE departments DROP CONSTRAINT dept_id_pk CASCADE;

select * from USER_CONSTRAINTS;
select * from USRE_CONS_COLUMNS;

********************************************************************************
Chapter10: การสร้างวิว (Creating Views)
********************************************************************************
CREATE OR REPLACE VIEW empvu80
AS select employee_id, last_name, salary
from employees
where department_id = 80;

CREATE OR REPLACE VIEW salvu50
AS select employee_id ID_NUMBER, last_name NAME, salary*12 ANN_SALARY
from employees
where department_id = 50;

CREATE OR REPLACE VIEW dept_sum_vu
(name, minsal, maxsal, avgsal)
AS SELECT d.department_name, MIN(e.salary), MAX(e.salary), AVG(e.salary)
FROM employees e, departments d
WHERE e.department_id = d.department_id
GROUP BY d.department_name;

DROP VIEW dept_sum_vu;

-- Inline Views คือแทนตารางด้วยคิวรีในคำสั่ง
select a.last_name, a.salary, a.department_id, b.maxsal
from employees a, (SELECT department_id, max(salary) maxsal
FROM employees
GROUP BY department_id) b
where a.department_id = b.department_id
and a.salary < b.maxsal;

-- Top-N Analysis
-- การสร้างลำดับที่ และคิวรีเอาเฉพาะลำดับที่ ๆ ต้องการ
select ROWNUM as RANK, last_name, salary
from (select last_name, salary from employees order by salary desc)
where rownum <= 3;

select a.rank, a.last_name, a.salary from
(
select ROWNUM as RANK, last_name, salary
from (select last_name, salary from employees order by salary desc)
where rownum <=15
) a
where a.rank >=10;

********************************************************************************
Chapter11: การสร้างเลขอัตโนมัติ (Sequences)
********************************************************************************
CREATE SEQUENCE dept_deptid_seq
INCREMENT BY 10
START WITH 320
MAXVALUE 9999
NOCACHE
NOCYCLE;

-- การตรวจดู sequences
select sequence_name, min_value, max_value, increment_by, last_number
from user_sequences;

-- การใช้งาน sequences
insert into departments (department_id, department_name, location_id)
values (DEPT_DEPTID_SEQ.NEXTVAL,'Support', 2500);

select dept_deptid_seq.currval from dual;

-- ปัญหาการเกิดการข้ามหมายเลขใน sequences
เมื่อมีการใช้ Cache จะทำให้ระบบทำงานได้เร็วขึ้น แต่ก็อาจจะทำให้เกิดการข้ามหมายเลขได้ ถ้ามีการ Rollback หรือระบบ Down, เลขที่อยู่ใน Cache ก็จะหายไปหมด

-- การแก้ไข sequences จะแก้ได้ถ้าไม่ทำให้เกิดขัดแย้งกับเลขที่ได้รันไปแล้ว เช่นข้างล่างนี้ OK
ALTER SEQUENCE dept_deptid_seq
INCREMENT BY 20
MAXVALUE 999999
CACHE 20
NOCYCLE;

-- แต่ข้างล่างนี้จะ Error ถ้ามีการแก้ให้ไปเริ่มต้นที่ค่าใหม่ (START WITH) เมื่อแก้ไม่ได้ก็ต้อง Drop และสร้าง Sequence ตัวใหม่
alter sequence dept_deptid_seq
increment by 20
maxvalue 999999
cache 20
nocycle
START WITH 20;

-- การลบ sequence
DROP SEQUENCE dept_deptid_seq;

********************************************************************************
Chapter12: การใช้ Index เพื่อเพิ่มประสิทธิภาพของคิวรี
********************************************************************************
-- การสร้าง Index
create index emp_last_name_idx on employees (last_name);
select * from employees where last_name = 'ABF'

drop index emp_last_name_idx;
create unique index emp_last_name_idx on employees (first_name,last_name);

select * from employees where first_name = 'ABF'

-- ควรจะใช้อินเด็กซ์ก็ต่อเมื่อ
1. มีความหลากหลายของค่าในคอลัมน์
2. มี Null จำนวนมาก
3. ถูกใช้ใน Where clause บ่อย
4. ตารางมีขนาดใหญ่ และคิวรีส่วนใหญ่จะดึงข้อมูลมาประมาณ 2-4 % ของ Rows ทั้งหมด
-- ไม่ควรใช้อินเด็กซ์เมื่อ
1. ตารางเล็ก
2. ข้อมูลไม่ได้ถูกใช้ใน Where Clause
3. คิวรีส่วนใหญ่ดึงข้อมูลมาเกิน 2 ถึง 4 เปอร์เซ็นต์ของจำนวน Rows ทั้งหมด
4. ข้อมูลถูก Update บ่อยมาก
5. คอลัมน์ที่ถูก Index เวลาถูกใช้ใน Where Clause จะเป็นส่วนหนึ่งของ Expression

SELECT * FROM EMPLOYEES WHERE TRUNC(HIRE_DATE) = DATE '2009-01-01';
create index idx_employees on employees (trunc(hire_date))

-- อินเด็กซ์ที่ใช้ร่วมกับฟังก์ชั่น (Function-Based Indexes)
create index upper_dept_name_idx on departments (UPPER(department_name));
-- บน SQLPlus ใช้คำสั่งนี้เพื่อให้ผลที่ได้เป็น Explain Plan แทนที่จะเป็นแถวข้อมูล
set autotrace traceonly explain
select * from departments where upper(department_name) = 'SALES';

-- การลบ Index
drop index upper_dpt_name_idx;

-- การใช้ Synonym เพื่อทำปลอกครอบชื่อตาราง เราสามารถอ้างอิงถึงตารางโดยชื่อตารางโดยตรง หรือโดยใช้ Synonym ก็ได้
create synonym d_sum for dept_sum_vu;
drop synonym d_sum;

********************************************************************************
Chapter13: การใช้ SET OPERATIONS
********************************************************************************
-- UNION เอามาทั้งสองชุด ที่ซ้ำกันไม่เอา
select employee_id, job_id
from employees
UNION
select employee_id, job_id
from job_history;

-- UNION ALL เอามาทั้งสองชุด มีเท่าไรเอามาให้หมด
select employee_id, job_id, department_id
from employees
UNION ALL
select employee_id, job_id, department_id
from job_history
order by employee_id;

-- INTERSECT เอาตัวที่มีอยู่ในทั้งสองชุด
select employee_id, job_id
from employees
INTERSECT
select employee_id, job_id
from job_history;

-- MINUS เอามาเฉพาะตัวที่อยู่ในชุดแรก และไม่อยู่ในชุดที่สอง
select employee_id, job_id
from employees
MINUS
select employee_id, job_id
from job_history;

select current_timestamp from dual
select sysdate from dual;
select dbtimezone from dual;
select extract (year from sysdate) from dual;
select extract (month from sysdate) from dual;
select extract (day from sysdate) from dual;
select hire_date, hire_date + to_yminterval ('01-02') as hire_date_ymininterval
from employees where department_id = 20;

********************************************************************************
Chapter14: เพิ่มเติมกับการใช้ GROUP BY
********************************************************************************
select department_id, job_id, SUM(salary), COUNT(employee_id)
from employees GROUP BY department_id, job_id;

-- ใช้ HAVING เพื่อ Filter เอาผลที่ต้องการ
select department_id, AVG(salary)
from employees
GROUP BY department_id
HAVING AVG(salary) > 9500;

select department_id, job_id, SUM(salary)
from employees
group by department_id, job_id

-- ใช้ ROLLUP เพื่อให้ทำการ Sum ผลลัพธ์ในแต่ละ Group โดยอัตโนมัติ
select department_id, job_id, SUM(salary)
from employees
where department_id < 60
GROUP BY ROLLUP (department_id, job_id);

-- ใช้ CUBE เพื่อให้แสดงทุก ๆ ความเป็นไปได้ของผลลัพธ์ในมิติต่าง ๆ
select department_id, job_id, SUM(salary)
from employees
where department_id < 60
GROUP BY CUBE (department_id, job_id);

-- ฟังก์ชัน Grouping ใช้เพื่อให้ผลการ Group ดูง่ายขึ้น
-- 0 หมายถึงรายละเอียด
-- 1 หมายถึงสรุป
select department_id DEPTID, job_id JOB,
SUM(salary),
GROUPING (department_id) GRP_DEPT,
GROUPING (job_id) GRP_JOB
from employees
where department_id < 50
GROUP BY ROLLUP(department_id, job_id);

********************************************************************************
Chapter15: Advanced Subqueries
********************************************************************************
-- Subqueries
select last_name, job_id, salary
from employees
where salary < (select avg(salary)
from employees);
-- Pairwise Comparison subquery
-- ใน Where Clause มีคอลัมน์มากกว่าหนึ่ง
select employee_id, manager_id, department_id
from employees
where (manager_id, department_id) in
(select manager_id, department_id from employees where employee_id in (178,174))
and employee_id not in (178,174);

-- Nonpairwise Comparison subquery
-- คิวรีนี้ให้ผลเหมือนกับคิวรี Pairwise ข้างบน สังเกตว่าคอลัมน์ทั้งสอง (manager_id,department_id) ถูกแยกออกเป็นสองเงื่อนไข และใช้ Subquery คนละตัวกัน
select employee_id, manager_id, department_id
from employees
where manager_id in
(select manager_id from employees where employee_id in (178,174))
and department_id in
(select department_id from employees where employee_id in (178,174))
and employee_id not in (178,174);

-- Subquery in the FROM Clause
-- คิวรีข้างล่างแทนที่จะใช้สองตาราง Join กัน กลับใช้ 1 ตาราง กับอีก 1 คิวรี (ซึ่งใช้แทนตาราง) มา Join กันแทน
select a.last_name, a.salary, a.department_id, b.salavg
from employees a, (select department_id, AVG(salary) salavg from employees group by department_id) b
where a.department_id = b.department_id
and a.salary > b.salavg;

-- Scalar Subquery Expressions
-- เป็น Subquery ที่จะให้ค่าที่ Select ขึ้นมาเพียงหนึ่งค่า (หนึ่งแถวและหนึ่งคอลัมน์ ค่าเดียว จึงเรียกว่า Scalar)
-- เราใช้ Scalar Subquery แทนการ Join โดยเฉพาะ Outer Join
select last_name,d.department_name from employees e, departments d where e.department_id = d.department_id (+)
-- จากตัวอย่างเรา Select จากตาราง EMPLOYEES เท่านั้น ไม่ต้องทำ Outer Join กับ DEPARTMENTS เราจึงแน่ใจว่าคิวรีข้างล่างจะแสดงผลทุกแถวของ EMPLOYEES เสมอ
select last_name,(select department_name from departments d where d.department_id = e.department_id) dept_name from employees e
-- In CASE Expressions
-- เราสามารถใช้ Scalar Subquery ใน Case Expression ได้เช่นกัน
select employee_id, last_name
, (case
when department_id =
(select department_id from departments where location_id = 1800)
then 'Canada' else 'USA' end) location
from employees;

-- ORDER BY Expressions
-- เราสามารถใช้ Scalar Subquery ใน ORDER BY Clause ก็ได้
-- ตัวอย่างข้างล่างเป็นการ ORDER BY department_name แม้ว่าข้อมูลที่ Select ขึ้นมาจะไม่มีคอลัมน์นี้ (และในตาราง EMPLOYEES ก็ไม่มีคอลัมน์นี้)
select employee_id, last_name, department_id
from employees e
ORDER BY (select department_name from departments d where e.department_id = d.department_id);
-- ลอง Select ดูจากตาราง DEPARTMENTS โดย Order by DEPARTMENT_NAME
select department_name,department_id from departments order by department_name;

-- Correlated Subqueries
-- คือ Subquery ที่จะ Return ค่าที่ผันแปรตามค่าในเรคคอร์ดของคิวรีหลัก
select last_name, salary, department_id
from employees outer
where salary > (select avg(salary)
from employees
where department_id = outer.department_id);

-- Another example
select employee_id, last_name, e.job_id
from employees e
where 2 <= (select count(*)
from job_history
where employee_id = e.employee_id);

-- EXISTS Operators
-- เราใช้ EXISTS แทน IN และ NOT EXISTS แทน NOT IN
select * from departments for update
select distinct department_id from employees
select * from departments

select distinct department_id from departments where department_id not in (select nvl(department_id,0) from employees )
select department_id from departments d where not exists (select 'I love you' from employees e where e.department_id = d.department_id)

select * from employees e where e.department_id not in (select department_id from departments d)
select * from departments where department_id like '1%'
select * from departments d
where not exists
(select 1 from employees e where e.department_id = d.department_id)

select employee_id, last_name, job_id, department_id
from employees outer
where exists (select 0 from employees where manager_id = outer.employee_id);

select employee_id, last_name, job_id, department_id
from employees outer
where exists (select 0 from employees where manager_id = outer.employee_id);

select department_id, department_name
from departments d
where department_id not exists (select 0 from employees where department_id = d.department_id);

-- Correlated Update
-- การ Update ค่าในตารางหลัก โดยคิวรีเอาค่าจากตารางอื่นที่มีความสัมพันธ์กับค่าในบางคอลัมน์ของตารางหลัก
alter table employees add (department_name varchar2(14));

update employees e
set department_name = (select department_name
from departments d
where e.department_id = d.department_id);

-- Correlated Delete
-- การ Delete ค่าในตารางหลัก โดยคิวรีเอาค่าจากตารางอื่นที่มีความสัมพันธ์กับค่าในบางคอลัมน์ของตารางหลัก
delete from employee e
where employee_id = (select employee_id
from emp_history
where employee_id = e.employee_id);

delete from job_history jh
where employee_id = (select employee_id
from employees e
where jh.employee_id = e.employee_id
and start_date = (select min(start_date)
from job_history jh
where jh.employee_id = e.employee_id)
and 5 > (select count(*)
from job_history jh
where jh.employee_id = e.employee_id
group by employee_id
having count(*) > =4));

-- WITH
-- เปรียบ WITH คล้าย ๆ กับการใช้ View เพียงแต่เราไม่ต้องสร้าง View เอาไว้ก่อน การใช้ WITH ช่วยให้การเขียนคิวรีที่ต้องอ้างถึง Subquery ตัวเดิมซ้ำ ๆ กัน สามารถกำหนด Subquery ไว้ที่เดียว ทำให้คิวรีสั้นขึ้น ดูง่ายขึ้น และแก้ไขได้ง่ายขึ้น
with
dept_costs as (
select d.department_name, sum(e.salary) as dept_total
from employees e, departments d
where e.department_id = d.department_id
group by d.department_name),
avg_cost as (
select sum(dept_total) / count(*) as dept_avg
from dept_costs)
select *
from dept_costs
where dept_total > (select dept_avg from avg_cost)
order by department_name

********************************************************************************
Chapter16: Extension of DML and DDL
********************************************************************************
create table mgr_history as select employee_id,manager_id,salary from employees where 1=0;
create table sal_history as select employee_id,hire_date,salary from employees where 1=0;

-- Unconditional INSERT ALL
-- การ Insert ข้อมูลเข้าทีเดียวสองตาราง (หรือมากกว่า)
INSERT ALL
INTO sal_history (employee_id,hire_date,salary) values (empid, hiredate,sal)
INTO mgr_history (employee_id,manager_id,salary) values (empid, mgr, sal)
select employee_id empid, hire_date hiredate,
salary sal, manager_id mgr
from employees
where employee_id > 200;

rollback;

-- Conditional INSERT ALL
-- การ Insert ข้อมูลเข้าทีเดียวสองตาราง แบบมีเงื่อนไขว่า ถ้า.. ให้ Insert เข้าตาราง...
INSERT ALL
WHEN sal > 10000 THEN
INTO sal_history values (empid,hiredate,sal)
WHEN mgr > 200 THEN
INTO mgr_history values (empid,mgr,sal)
select employee_id empid, hire_date hiredate,
salary sal, manager_id mgr
from employees
where employee_id > 200;
select * from sal_history;
select * from mgr_history;

-- Conditional FIRST INSERT ถ้า Condition แรกเป็นจริงก็ข้ามไปแถวต่อไป
INSERT FIRST
WHEN sal > 25000 THEN
INTO special_sal VALUES (deptid, sal)
WHEN hiredate like ('%') then
INTO hiredate_history_00 VALUES (deptid, hiredate)
WHEN hiredate like '%99%') then
INTO hiredate_history_99 VALUES (deptid,hiredate)
ELSE
INTO hiredate_history values (dept_id, hiredate)
SELECT department_id deptid, sum(salary) sal,
max(hire_date) hiredate
FROM employees
GROUP BY department_id;

-- Pivot INSERT กลับคอลัมน์เป็นแถว
-- เราสามารถใช้ INSERT ALL ในการทำ Pivot ก็ได้
-- จากตัวอย่างข้างล่างเดิมปริมาณการขายเก็บของแต่ละวันในสัปดาห์ ถูกเก็บไว้แยกเป็นคอลัมน์วัน (จันทร์, อังคาร, ...) เราต้องการนำข้อมูลเหล่านี้มาเก็บในอีกตารางที่อยู่ในรูปของการเก็บแบบแต่ละวันของสัปดาห์ในรูปของแต่ละเรคคอร์ด
INSERT ALL
INTO sales_info values (employee_id,week_id,sales_MON)
INTO sales_info values (employee_id,week_id,sales_TUE)
INTO sales_info values (employee_id,week_id,sales_WED)
INTO sales_info values (employee_id,week_id,sales_THU)
INTO sales_info values (employee_id,week_id,sales_FRI)
select employee_id, week_id,sales_MON,sales_TUE,sales_WED,sales_THU,sales_FRI
from sales_source_data;

********************************************************************************
Chapter17: External Tables
********************************************************************************
เราสามารถ Select ข้อมูลจากเท็กซ์ไฟล์ข้างนอกระบบฐานข้อมูลได้

1. สมมติเรามีเท็กซ์ไฟล์ที่มีข้อมูลดังนี้
7369,SMITH,CLERK,7902,17-DEC-80,800,20
7499,ALLEN,SALESMAN,7698,20-FEB-81,1600,300,30
7521,WARD,SALESMAN,7698,22-FEB-81,1250,500,30
7566,JONES,MANAGER,7839,02-APR-81,2975,,20
7654,MARTIN,SALESMAN,7698,28-SEP-81,1250,1400,30
7698,BLAKE,MANAGER,7839,01-MAY-81,2850,,30
7782,CLARK,MANAGER,7839,09-JUN-81,2450,,10
7788,SCOTT,ANALYST,7566,19-APR-87,3000,,20
7839,KING,PRESIDENT,,17-NOV-81,5000,,10
7844,TURNER,SALESMAN,7698,08-SEP-81,1500,0,30
7876,ADAMS,CLERK,7788,23-MAY-87,1100,,20

2. การ Select จากไฟล์ จะต้องเก็บไฟล์ไว้ในไดเรคทอรีที่ Oracle รู้จัก โดยในตัวอย่างนี้เราจะเก็บไฟล์ไว้ในไดเรคทอรีบนดาต้าเบสที่ชื่อว่า 'DATA_PUMP_DIR' ซึ่งมีมากับดาต้าเบสโดยดีฟอลต์ การจะเช็คดูว่ามี directory แล้วหรือยังและไดเรคทอรีดังกล่าวแม็ปกับไดเรคทอรีบน OS ใด เราสามารถใช้คำสั่ง (จะต้อง Login user ที่มีสิทธิ์ DBA)
select * from dba_directories where directory_name = 'DATA_PUMP_DIR';
OWNER DIRECTORY_NAME DIRECTORY_PATH
------------ --------------------- ----------------------------------
SYS DATA_PUMP_DIR C:\oraclexe\app\oracle\admin\XE\dpdump\
เราอาจจะสร้าง directory ใหม่เพื่อให้แม็ปกับ directory ที่เราต้องการก็ได้โดยการสร้างจะต้องมีสิทธิ์ที่เป็น DBA เช่นกัน

3. Save ไฟล์ในข้อ 1 ด้วยชื่อ "emp.dat" ไว้ในไดเรคทอรีที่แม็ปไว้กับไดเรคทอรี DATA_PUMP_DIR ใน Oracle
4. ผู้จะสร้าง External Table จะต้องมีสิทธิ์ Read,write ในไดเรคทอรีที่เก็บไฟล์ (จะต้องให้ user ที่มีสิทธิ์ DBA grant ให้)
grant read,write on directory data_pump_dir to scott;
Grant succeeded

5. จากนั้นล็อกอินด้วย user ทีต้องการสร้างตารางแล้วสร้างตารางดังข้างล่าง
create table emp_ext
(empno number(4),
ename varchar2(10),
job varchar2(9),
mgr number(4),
hiredate date,
sal number(7,2),
comm number(7,2)
)
organization external
(type oracle_loader
default directory data_pump_dir
access parameters
(
records delimited by newline
fields terminated by ','
)
location ('emp.dat')
)
reject limit 1000;
โดยคอลัมน์ที่กำหนดจะต้องสอดคล้องกับข้อมูลในไฟล์
- ตรง 'location' เป็นตัวกำหนดว่าชื่อไฟล์ที่จะให้ไปอ่านชื่ออะไร
- โดย Oracle จะไปอ่านไฟล์ดังกล่าวตามที่กำหนดไว้ใน default directory
- 'fields terminated by' เป็นตัวกำหนดว่าข้อมูลแต่ละคอลัมน์ถูกแยกจากกันด้วยสัญญลักษณ์อะไร
- 'reject limit' เป็นตัวกำหนดว่าถ้ามีข้อผิดพลาดในการอ่านไม่เกินจำนวนเท่าไรจึงจะแสดง error และไม่ต้องอ่านต่อ

6. หลังจากสร้างตารางเสร็จแล้ว เราสามารถ select ข้อมูลได้เหมือนตารางปกติ
select * from emp_ext;

********************************************************************************
Chapter18: Hierarchical Retrieval
********************************************************************************
-- คิวรีข้างล่างแสดงข้อมูลของพนักงาน โดยเรคคอร์ดถัดไปจะเป็นเรคคอร์ดของหัวหน้าของเรคคอร์ดที่อยู่ก่อนหน้า
select employee_id, last_name, job_id, manager_id
from employees
START WITH employee_id = 101
CONNECT BY PRIOR manager_id = employee_id ;
-- เราสามารถใช้ PRIOR เพื่อแสดงผลในคอลัมน์ได้
select level,last_name || ' reports to ' ||
PRIOR last_name "Walk Top Down", level
from employees
START WITH last_name = 'King'
CONNECT BY PRIOR employee_id = manager_id ;

-- Using Level to format Hierarchical Reports
-- เราใช้ LEVEL เพื่อแสดงลำดับที่ในผลของคิวรีได้ ตัวอย่างข้างล่างในใช้ LEVEL เพื่อจัดรูปแบบของ Report ในลักษณะของแผนภูมิแบบลำดับชั้น (Hierarchical)
select LPAD(last_name, length(last_name) + (level*2) -2 , '_') as org_chart, level
from employees
START WITH last_name = 'King'
CONNECT BY PRIOR employee_id = manager_id
-- เราสามารถเพิ่มเงื่อนไขใน CONNECT BY ได้
select employee_id, last_name, job_id, manager_id
from employees
START WITH last_name = 'Kochhar'
CONNECT BY PRIOR employee_id = manager_id and last_name != 'Higgins'

-- Use WHERE to eliminate a node
-- เราใช้ WHERE เพื่อตัดเฉพาะบางโหนด (คน) ออกไป คิวรีข้างล่างเฉพาะเรคคอร์ดของ 'Higgins' ถูกตัดออกไป
select department_id, employee_id,last_name, job_id,salary, manager_id
from employees
where last_name != 'Higgins'
start with last_name = 'Kochhar'
connect by prior employee_id = manager_id;

-- Use CONNECT BY to eliminate a branch
-- เราเพิ่มเงื่อนไขใน CONNECT BY เพื่อตัดเฉพาะบางกิ่ง (โหนดและโหนดลูก,หลาน) ออกไป คิวรีข้างล่างเรคคอร์ดของ 'Higgins' และเรคคอร์ดที่เป็นลูกน้องของ 'Higgins' จะถูกตัดออกไป
select department_id, employee_id, last_name, job_id, salary, manager_id
from employees
start with last_name = 'Kochhar'
CONNECT BY PRIOR employee_id = manager_id
AND last_name != 'Higgins';