Category: Oracle

คำสั่ง Update โดยใช้ข้อมูลจากอีกตาราง [Oracle]
ในการสั่ง update ข้อมูล ในบางครั้งเราอาจจะเจอโจทย์ว่า ต้องการ update ข้อมูลในตารางหนึ่งโดยใช้ข้อมูลจากอีกตารางหนึ่ง ซึ่งลักษณะของข้อมูลทั้งสองตารางนี้ จะต้องมีฟีลด์ข้อมูลที่สามารถเชื่อมโยงความสัมพันธ์ของข้อมูลแต่ละเร็คคอร์ดได้

ตัวอย่างข้อมูล
1. มีข้อมูลสองตาราง
ตารางที่ 1 เก็บข้อมูลวันในหนึ่งสัปดาห์ภาษาไทย [ชื่อตาราง : DAY_OF_WEEK_THAI]

โครงสร้างตารางประกอบด้วย
- ฟีลด์ ID เก็บ ID ของแต่ละเร็คคอร์ด
- ฟีลด์ DAY_FULL เก็บข้อมูลวันแบบเต็มภาษาไทย
- ฟีดล์ DAY_SHORT เก็บข้อมูลวันแบบย่อภาษาไทย
ข้อมูลในตารางเป็นดังนี้

ตารางที่ 2 เก็บข้อมูลวันในหนึ่งสัปดาห์ภาษาอังกฤษ [ชื่อตาราง : DAY_OF_WEEK]

โครงสร้างตารางประกอบด้วย
- ฟีลด์ ID เก็บ ID ของแต่ละเร็คคอร์ด
- ฟีลด์ DAY_ENG เก็บข้อมูลวันแบบเต็มภาษาอังกฤษ
- ฟีดล์ DAY_ENG_SHORT เก็บข้อมูลวันแบบย่อภาษาอังกฤษ
ข้อมูลในตารางเป็นดังนี้

2. ฟีลด์ที่เชื่อมโยงความสัมพันธ์ของทั้งสองตารางคือฟีลด์ ID

3. ต่อมามีการปรับแก้โครงสร้างตาราง DAY_OF_WEEK โดยเพิ่มฟีลด์ 2 ฟีลด์ ดังนี้

ฟีลด์ DAY_THA สำหรับเก็บข้อมูลวันแบบเต็มภาษาไทย

ฟีลด์ DAY_THA_SHORT สำหรับเก็บข้อมูลวันแบบย่อภาษาไทย

4. เนื่องจากมีข้อมูลวันภาษาไทยในตาราง DAY_OF_WEEK_THAI อยู่แล้ว และสามารถเชื่อมโยงข้อมูลกันได้ด้วยฟีลด์ ID ดังนั้นความต้องการคือ การใช้ข้อมูลจากตาราง DAY_OF_WEEK_THAI มา update ให้กับตาราง DAY_OF_WEEK

5. รายละเอียดการ update

นำข้อมูลวันแบบเต็มภาษาไทยในฟีลด์ DAY_FULL ของตาราง DAY_OF_WEEK_THAI มาใส่ในฟีลด์ DAY_THA ของตาราง DAY_OF_WEEK

นำข้อมูลวันแบบย่อภาษาไทยในฟีลด์ DAY_SHORT ของตาราง DAY_OF_WEEK_THAI มาใส่ในฟีลด์ DAY_THA_SHORT ของตาราง DAY_OF_WEEK

รูปแบบคำสั่ง SQL

แบบที่ 1

update table1 t1 set

table1.data1= (select table2.data1 from table2 where table2.key = t1.key),

table1.data2 = (select table2.data2 from table2 where table2.key = t1.key);

แบบที่ 2

update table1 t1 set

(table1.data1, table1.data2) =

(select table2.data1, table2.data2 from table2 where table2.key = t1.key);

อธิบายคำสั่ง

table1 คือ ตารางที่ต้องการ update

t1 คือ ชื่ออ้างอิงแทนตาราง table1 ซึ่ง t1 จะถูกใช้ในคำสั่ง select

table1.data1 และ table1.data2 คือ ฟีลด์ข้อมูลที่ต้องการ update

table2 คือ ตารางที่จะนำข้อมูลมาใช้เพื่อ update ให้กับ table1

table2.data1 และ table2.data2 คือ ฟีลด์ข้อมูลที่จะนำมาใช้เพื่อ update ให้กับ table1.data1 และ table1.data2 ตามลำดับ

ตัวอย่างการนำไปใช้งาน

แบบที่ 1

update day_of_week t1 set
day_tha = (select day_full from day_of_week_thai where day_of_week_thai.id = t1.id),
day_tha_short = (select day_short from day_of_week_thai where day_of_week_thai.id = t1.id);

แบบที่ 2

update day_of_week t1 set
(day_tha, day_tha_short) =
(select day_full, day_short from day_of_week_thai where day_of_week_thai.id = t1.id);

ผลลัพธ์การรันคำสั่งทั้งสองรูปแบบ จะได้ผลลัพธ์เช่นเดียวกัน ดังรูป

ข้อมูลก่อน update

ข้อมูลหลัง update

สรุป
1. สามารถใช้คำสั่ง update ได้ทั้งสองรูปแบบซึ่งจะได้ผลลัพธ์เหมือนกัน
2. แบบที่ 2 จะเป็นการใช้คำสั่งที่สั้นและกระชับกว่าแบบที่ 1 มาก กรณีที่ข้อมูลที่ต้องการ update มีมากกว่า 1 ฟีลด์
3. ข้อควรระวังคือ ความสัมพันธ์ระหว่างสองตารางต้องเป็น 1 ต่อ 1 จึงจะสามารถใช้คำสั่งรูปแบบนี้ได้
November 18, 2020
Change BYTE to CHAR for all columns, all tables
เมื่อมีการสร้างฟิลด์ให้มีชนิดเป็น char หรือ varchar2 จริงๆ แล้ว มันมีหน่วยย่อยไปอีกว่าจะให้เป็น Byte หรือ Char ซึ่งโดยปกติจะเป็น Byte สามารถเปลี่ยนค่า default นี้ได้ด้วยการเปลี่ยนตัวแปรของระบบตัวแปรชื่อ nls_length_semantics

https://docs.oracle.com/cd/B19306_01/server.102/b14237/initparams127.htm#REFRN10124
ดูรายละเอียดเพิ่มเติม

ทีนี้ Byte กับ Char มันสำคัญอย่างไร…สำคัญเรื่องการแปลง Character Set เช่น จาก TH8ASCII เป็น AL32UTF8 จากข้อมูลเดิม 1 ตัวอักษรเท่ากับ 1Byte สำหรับ TH8ASCII และ 3Byte สำหรับ AL32UTF8 ทำให้เมื่อเราสร้างฟิลด์ ตอนมี Character set บน TH8ASCII ชนิดของข้อมูลเป็น varchar2 มีขนาด 3Byte ถ้าจะนำข้อมูลเข้าอีกฐานข้อมูลที่มี Character Set เป็น AL32UTF8 จะนำเข้าไม่ได้เพราะขนาดเกินจำนวน Byte ที่สร้างคือกลายเป็น 9Byte ต้องกลับไปเปลี่ยนต้นทางจากหน่วย Byte เป็น Char คือเก็บเป็นตัวอักษรแทน

ทีนี้เราจากสร้างสคริปต์เพื่อ สร้างสคริปต์ อีกที… ด้วยคำสั่ง (สคริปต์ในตัวอย่างนี้จะเป็นการสร้างจาก Username SCOTT)
```
SELECT 'alter table '||owner||'.'|| table_name ||
       ' modify '||column_name||' varchar2('|| 
       data_length ||' CHAR); '
  FROM all_tab_columns
 WHERE data_type = 'VARCHAR2'
   AND char_used = 'B'
   and owner in ('SCOTT')
;
```
เมื่อสั่งสคริปต์ทำงานจะได้ผลลัพธ์ประมาณว่า

alter table

ทีนี้จากสคริปต์ข้างต้น จะดึงข้อมูลของ Table และ View มาทั้งหมดทำให้เมื่อได้สคริปต์ไปแล้วสั่งทำงานจะมี error ต้องกรองเอา view ออก

error

เปลี่ยนสคริปต์ใหม่เป็น
```
select 'alter table '||all_objects.OWNER||'.'||'"'||all_objects.OBJECT_NAME ||'"'||' modify '||'("'||column_name||'"'||' varchar2('|| ALL_TAB_COLUMNS.DATA_LENGTH ||' CHAR)); '
from   all_objects,all_tab_columns
where  all_objects.object_type in ('TABLE')
and all_tab_columns.data_type = 'VARCHAR2'
AND all_tab_columns.char_used = 'B'
and object_name not in (select all_objects.object_name from all_objects where all_objects.object_type = 'VIEW')
and all_objects.object_name=all_tab_columns.table_name
and all_objects.owner in ('SCOTT');
```
เมื่อสั่งรันสคริปต์จะได้ผลลัพธ์

alter script

ก็จะสามารถนำสคริปต์ที่ได้ไปสั่งรันได้ปกติ

Complete

หลังจากเปลี่ยนข้อมูล TH8ASCII จาก Byte เป็น Char แล้วสามารถนำเข้าใน AL32UTF8 ได้เลย

จบขอให้สนุก

ต้นฉบับ

http://webgeest.blogspot.com/2014/12/change-byte-to-char-for-all-columns-all.html
April 14, 2020
How to safely grant ALTER SYSTEM KILL SESSION “privilege” to non-DBA users
อยากฆ่าคน เอ้ย session บน oracle แต่ไม่มีสิทธิ์ (Privilege) ไปขอสิทธิ์ DBA แล้ว DBA บอกว่าไม่มีสิทธิ์ kill session จะแจกให้!!! ทำไงดี

เข้าระบบฐานข้อมูล Oracle ด้วยผู้ใช้ system หรือ sys แล้วสร้าง Procedure ชื่อว่า sp_kill_dev_session ด้วยคำสั่ง
```
CREATE OR REPLACE PROCEDURE sys.sp_kill_dev_session(p_sid NUMBER, p_serial NUMBER)
AS
    v_user VARCHAR2(30);
BEGIN
    SELECT MAX(username)
    INTO v_user
    FROM v$session
    WHERE sid = p_sid
      AND serial# = p_serial;

    IF v_user IN ('SCOTT') THEN --the list can be extended
         EXECUTE IMMEDIATE 'ALTER SYSTEM KILL SESSION ''' || p_sid || ',' || p_serial || '''';
    ELSIF v_user IS NULL THEN
         RAISE_APPLICATION_ERROR(-20001,'Session has Expired or Invalid sid/serial Arguments Passed');
    ELSE
         RAISE_APPLICATION_ERROR(-20002,'Unauthorized Attempt to Kill a Non-Dev Session has been Blocked.');
    END IF;
END sp_kill_dev_session;
/
```
จาก code ข้างต้น ให้เปลี่ยน SCOTT เป็น username ที่เป็นเจ้าของ session เช่น
```
IF v_user IN ('HIRXPXT','PHU_XDMIHHION') THEN --the list can be extended
```
เป็นต้น จากตัวอย่าง จะมี 2 user คือ HIRXPXT และ PHU_XDMIHHION

แล้ว grant สิทธิ์ ให้สามารถ exec กับ user ที่ต้องการฆ่า session เหล่านั้นด้วยคำสั่ง ตัวอย่างจะให้ user ชื่อว่า HIRXPXT มีสิทธิ์ exec
```
GRANT EXECUTE ON sp_kill_dev_session TO HIRXPXT;
```
แล้วจะทำให้ user ที่ชื่อ HIRXPXT สามารถฆ่า (kill) session ของ user ได้ 2 คนคือ HIRXPXT และ PHU_XDMIHHION

วิธีใช้งาน เข้าระบบด้วย user ที่ชื่อ HIRXPXT แล้วเรียกใช้คำสั่ง
```
exec sys.sp_kill_dev_session(sid,serial num);
```
เช่น

Session Properties
```
exec sys.sp_kill_dev_session(154,42802);
```
โดย 14 คือ sid และ 26043 คือ serial ซึ่งสองค่านี้สามารถเรียกดูได้จากคำสั่ง
```
SELECT SID, SERIAL#, STATUS, SERVER
FROM V$SESSION
WHERE USERNAME = upper('<username>');
```
เปลี่ยน <username> เป็น username ที่ต้องการ แต่การที่จะดูค่าเหล่านี้ได้ต้องมีสิทธิ์ จากตัวอย่าง user ที่ชื่อ HIRXPXT สามารถ select view และ table เหล่านี้ได้
```
SYS.V_$PROCESS
SYS.V_$SESSION
SYS.V_$TRANSACTION
SYS.V_$SESSION_LONGOPS
SYS.V_$LOCK
SYS.V_$SESSTAT
SYS.V_$STATNAME
SYS.V_$ACCESS
SYS.V_$ROLLNAME
SYS.V_$SQLTEXT
SYS.V_$SQLTEXT_WITH_NEWLINES
SYS.V_$SQL
SYS.V_$OPEN_CURSOR
SYS.V_$SESSION_WAIT
SYS.V_$SESSION_EVENT
SYS.V_$SESSION_CONNECT_INFO
SYS.V_$EVENT_NAME
SYS.V_$SESS_IO
SYS.V_$EVENTMETRIC
SYS.DBA_ROLLBACK_SEGS
```
เท่านี้ก็เพียงพอที่จะให้ทีมพัฒนาสามารถฆ่า (kill) session ของตัวเองที่ค้างอยู่บน server ได้โดยไม่ต้องมาจิกหัว DBA กรั่กๆ

ต้นฉบับ

https://sqlpatterns.wordpress.com/2015/03/02/how-to-safely-grant-alter-system-kill-session-privilege-to-non-dba-users/

จบ…. ขอให้สนุก
December 13, 2019
การเชื่อมต่อฐานข้อมูล Oracle ผ่าน Access office 365
สำหรับหลาย ๆ ท่านนอกเหนือจากที่ต้องมีหน้าที่ในการพัฒนาโปรแกรมเป็นหลักแล้ว อาจจะต้องมีหน้าที่ในการนำเข้าข้อมูลด้วย ตัวเองก็เช่นกันต้องทำหน้าที่ในการนำเข้าข้อมูลเข้าฐานข้อมูล Oracle อยู่เป็นประจำ จริงๆ มีหลายเครื่องมือในการนำเข้าข้อมูล เช่น SQL Developer , Toad for Oracle เป็นต้น แต่บางครั้งก็ไม่สะดวกเพราะบางข้อมูลต้องผ่านการจัดการก่อนถึงจะสามารถถ่ายโอนได้ ตัวเองก็มีอีกวิธีคือ ถ่ายโอนผ่าน Access office 365

สำหรับบทความในครั้งนี้ ขอนำเสนอวิธีการเชื่อมต่อฐานข้อมูล Oracle ผ่าน Access office 365 เผื่อใครจะใช้เป็นทางเลือกในการจัดการข้อมูลของ Oracle กันค่ะ

โดยทำตามขั้นตอนดังต่อไปนี้

STEP 1
- ติดตั้ง Oracle Database 12c Release 2 Client for Microsoft Windows (32–bit)
- ตั้งค่า TNS Service name ในการเข้าถึงฐานข้อมูล Oracle ผ่าน Oracle Net Manager โดยมีขั้นตอนดังนี้
  - เปิดโปรแกรม Net Manager
  - คลิกที่ Local -> Service Naming คลิกเครื่องหมาย +
  - ระบบแสดงหน้าต่าง Net Service Name Wizard
  - ขั้นตอนที่ 1 ตรงช่อง Net Service Name ป้อนชื่อที่ต้องการ เช่น TEST_DB คลิกปุ่ม ถัดไป
  - ขั้นนตอนที่ 2 คลิกเลือก TCP/IP (Internet Protocol) คลิกปุ่ม ถัดไป
  - ขั้นตอนที่ 3 ตรงช่อง Host Name: ป้อนชื่อฐานข้อมูล Oracle ที่ต้องการ เช่น TEST.PSU.AC.TH ระบุ Port Number: 1521
  - ขั้นตอนที่ 4 ตรงช่อง Service Name ให้ใส่ชื่อ Service Name ของ Database ที่ระบุในขั้นตอนที่ 3 คลิกปุ่ม ถัดไป
  - ขั้นตอนที่ 5 ซึ่งเป็นขั้นตอนสุดท้าย เราสามารถคลิกปุ่ม Test เพื่อทดสอบการ connect กับฐานข้อมูล และคลิกปุ่ม เสร็จสิ้น เพื่อสิ้นสุดการการสร้าง Service Naming
ภาพแสดงการตั้งค่า TNS Service name ขั้นตอนที่ 1

ภาพแสดงการตั้งค่า TNS Service name ขั้นตอนที่ 2

ภาพแสดงการตั้งค่า TNS Service name ขั้นตอนที่ 3

ภาพแสดงการตั้งค่า TNS Service name ขั้นตอนที่ 4

ภาพแสดงการตั้งค่า TNS Service name ขั้นตอนที่ 5

STEP 2
- เปิดโปรแกรม ODBC Data Sources (32-bit)
  - คลิกเลือกแท็บ System DSN คลิกปุ่ม Add…
  - คลิกเลือก driver : Oracle in OraClient 12Home1_32bit ในหน้าต่าง Create New Data Source คลิกปุ่ม Finish
  - ระบบแสดงหน้าต่าง Oracle ODBC Driver Configuration
    กำหนด Data Source Name และ Description
    เลือก TNS Service Name จากที่สร้างไว้ในขั้นตอนการตั้งค่า TNS Service name ขั้นตอนที่ 2 (กรณีไม่มีให้เลือกสามารถพิมพ์เองได้)
    ตรงช่อง User ID ระบุ user / schema เพื่อ connect เข้าฐานข้อมูล Oracle
    คลิกปุ่ม OK
ภาพแสดงหน้าต่างการเลือก driver ของ data source

ภาพแสดงหน้าต่าง Oracle ODBC Driver Configuration

STEP 3
- เปิดโปรแกรม Access office 365
  - คลิกเลือกเมนู External Data -> New Data Source -> From Other Sources -> ODBC Database
  - ระบบแสดงหน้าต่าง Get External Data – ODBC Database
  - คลิกเลือก Link to data source by creating a linked table.
  - คลิกปุ่ม OK
  - ระบบแสดงหน้าต่าง Select Data Source คลิกแท็บ Machine Data Source
  - คลิกเลือก Data Source Name ที่เราได้สร้างไว้แล้ว คลิกปุ่ม OK
  - ระบบแสดงหน้าต่าง Oracle ODBC Driver Connect
  - กรอก Password เพื่อ connect เข้าฐานข้อมูล Oracle คลิกปุ่ม OK
  - ระบบแสดง Link Tables คลิกเลือกตารางที่ต้องการ คลิกปุ่ม OK
ภาพแสดงหน้าจอหลักของ Access

ภาพแสดงหน้้าต่าง Get External Data – ODBC Database

ภาพแสดงหน้าต่าง Select Data Source

ภาพแสดงหน้าต่าง Oracle ODBC Driver Connect

ภาพแสดง Link Tables

จากนั้นตารางที่เราเลือกก็จะมาแสดงให้เราเห็น เราก็สามารถจัดการข้อมูลดังกล่าวผ่าน Access office 365 ได้แล้ว คราวนี้ถ้าต้องการนำเข้าข้อมูลเราก็สามารถใช้ความสามารถของ Access ได้ผ่านตัว Append Query ได้หรือแม้แต่จะปรับปรุงข้อมูลก็สามารถทำได้ผ่านตัว Update Query เป็นต้น

ภาพแสดงตัวอย่าง Linked Table ของฐานข้อมูล PSU_ADMISSION
July 15, 2019

วิธีการแสดงผลวันที่เป็นภาษาที่ต้องการด้วยฟังก์ชัน TO_CHAR

หลาย ๆ คนคงเคยเจอปัญหาว่าฐานข้อมูลของเรากำหนด default การจัดเก็บข้อมูลวันที่ไว้เป็นปี ค.ศ. แต่ระบบที่เราพัฒนาต้องแสดงผลเป็นปี พ.ศ. เราต้องทำอย่างไร วันนี้จึงขอนำเสนอวิธีการแสดงผลวันที่เป็นภาษาที่เราต้องการด้วยฟังก์ชัน TO_CHAR กันค่ะ

โดยที่ฟังก์ชัน TO_CHAR ทำหน้าที่ในการแปลงข้อมูลตัวเลขและข้อมูลวันที่เป็นตัวอักษร ซึ่งมีรูปแบบ Syntax ดังนี้

TO_CHAR( input_value, [format_mask], [nls_parameter] )

พารามิเตอร์ของฟังก์ชัน TO_CHAR คือ

input_value (จำเป็น): ค่าของวันที่ที่ต้องการจะแปลงค่า
format_mask (ทางเลือก): เป็นรูปแบบที่ต้องการให้แสดง หากไม่ได้ระบุไว้จะแสดงตามค่า default ที่กำหนดไว้
nls_parameter (ทางเลือก): เป็น nls language ที่จะใช้ในการแปลงค่า

ต่อไปนี้เป็นตัวอย่าง format_mask ที่ใช้บ่อย ๆ โดยแต่ละ format_mask สามารถนำผสมรวมกันได้ เพื่อให้ได้ค่าการแสดงผลที่เราต้องการ

format_mask	คำอธิบาย
YEAR	อ่านค่าปีเป็นตัวอักษร
YYYY, YYY, YY, Y	แสดงจำนวนหลักสุดท้ายของปีตามที่ระบุ
MM	แสดงเดือน (01-12, JAN = 01)
MON	แสดงชื่อเดือนแบบย่อ
MONTH	แสดงชื่อเดือนแบบเต็ม
D	แสดงวันในสัปดาห์
DAY	แสดงชื่อของวัน
DD	แสดงวันของเดือน (1-31)
DDD	แสดงวันของปี (1-366)
DY	แสดงชื่อย่อของวัน
HH	แสดงชั่วโมงของวัน (1-12)
HH12	แสดงชั่วโมงของวัน (1-12)
HH24	แสดงชั่วโมงของวัน (0-23)
MI	แสดงนาที (0-59)
SS	แสดงวินาที (0-59)

คราวนี้ลองมาเขียน query เพื่อดูตัวอย่างค่าของ format_mask แต่ละตัวกันว่าจะแสดงผลออกมาเป็นอย่างไร

รูปแสดง query และผลลัธพ์ที่ได้จากการแสดงผลตาม format_mask

จะเห็นว่าค่าวันที่ที่แสดง แสดงเป็นปี ค.ศ. ตาม default ของฐานข้อมูล สังเกตุว่าข้อมูลบาง format_mask จะมีค่าศูนย์นำหน้า และมีช่องว่างตามหลัง ดังนั้นถ้าต้องการตัดเลขศูนย์และช่องว่างเราสามารถใช้ “FM” นำหน้า format_mask ตามตัวอย่างต่อไปนี้

รูปแสดง query และผลลัพธ์ที่ได้จากการใช้ “FM” นำหน้า format_mask

คราวนี้ถ้าเราต้องการที่จะให้การแสดงผลวันที่เป็นวัน เดือน ปี ไทย เราสามารถใช้ nls_parameter เข้ามาช่วยได้โดยสามารถกำหนดได้ตามตัวอย่างข้างล่างนี้

รูปแสดงตัวอย่างวิธการกำหนด nls_parameter และผลลัพธ์ที่ได้

ตัวแปรที่สำคัญคือ
– NLS_CALENDAR ตัวนี้แหละที่ทำให้ พ.ศ. แสดงเป็น พุทธศักราช 2562 (THAI BUDDHA)
– NLS_DATE_LANGUAGE ตัวนี้กำหนดให้แสดงเดือนเป็นภาษาไทย (THAI)

ด้วยตัวพารามิเตอร์ NLS_CALENDAR และ NLS_DATE_LANGUAGE เราก็สามารถแสดงผลเป็น ภาษาอะไรก็ได้ตามที่เราต้องการ

July 14, 2019

วิธีการคำนวณหาอายุด้วย Oracle Datetime Functions

อายุเป็นเพียงตัวเลข มีคนพูดไว้แบบนี้ แล้วถ้าเราอยากจะรู้ล่ะว่าไอ้ตัวเลข จำนวนปี จำนวนเดือน และจำนวนวัน ของอายุเรามันเป็นตัวเลขอะไร ถ้าไม่อะไรมากใช้เครื่องคิดเลข หรือนับเอาก็ได้อยู่ แต่ถ้าต้องคำนวณของทุกคนในองค์กรล่ะจะนับเอง หรือเครื่องคิดเลขก็คงไม่ไหวแล้ว ดังนั้นวันนี้จึงจะขอแนะนำ Oracle Datetime Functions ที่สามารถนำมาประยุกต์ใช้ เพื่อคำนวณหาอายุของเรากันค่ะ

Oracle Datetime Functions ที่จะแนะนำในวันนี้คือ ฟังก์ชัน TRUNC (date), ADD_MONTHS และฟังก์ชัน MONTHS_BETWEEN โดยมีรายละเอียดของฟังก์ชันดังนี้

ฟังก์ชัน	คำอธิบาย
ADD_MONTHS	เพิ่มจำนวนเดือนลงในวันที่ที่ระบุ
MONTHS_BETWEEN	หาจำนวนเดือนระหว่าง 2 วันที่ที่ระบุ
TRUNC (date)	คืนค่าวันที่พร้อมส่วนเวลาของวันที่ตัดให้เป็นหน่วยที่ระบุ

ตัวอย่างการใช้งานฟังก์ชัน ADD_MONTHS
Syntax :
ADD_MONTHS(date, integer)

รูปแสดงตัวอย่างการเขียน query และผลลัพธ์ที่ได้ของฟังก์ชัน ADD_MONTHS

ตัวอย่างการใช้งานฟังก์ชัน MONTHS_BETWEEN
Syntax :
MONTHS_BETWEEN(date1, date2)

รูปแสดงตัวอย่างการเขียน query และผลลัพธ์ที่ได้ของฟังก์ชัน MONTHS_BETWEEN

ตัวอย่างการใช้งานฟังก์ชัน TRUNC (date)
Syntax :
TRUNC ( date [, format ] )
โดยที่ format ไม่ต้องระบุก็ได้ กรณีไม่ระบุวันที่จะถูกตัดเป็นวันที่ใกล้ที่สุด สามารถแสดงตัวอย่างของ format ได้ดังต่อไปนี้

รูปแบบ Format	หน่วยปัดเศษ
SYYYY, YYYY, YEAR. SYEAR, YYY, YY, Y	ปี
MONTH, MON, MM, RM	เดือน
DDD, DD, J	วัน
DAY, DY, D	วันเริ่มต้นของสัปดาห์
HH, HH12, HH24	ชั่วโมง
MI	นาที

รูปแสดงตัวอย่างการเขียน query และผลลัพธ์ที่ได้ของฟังก์ชัน TRUNC (date)

จากตัวอย่าง query การใช้งานทั้ง 3 ฟังก์ชันข้างต้น เราสามารถประยุกต์ใช้เพื่อคำนวณหาจำนวนปี จำนวนเดือน และจำนวนวันของอายุได้ดังนี้

รูปแสดงตัวอย่าง query การหาจำนวนปี จำนวนเดือน และจำนวนวันของอายุ

คิดว่านอกเหนือจากการคำนวณหาอายุแล้วก็ยังไปประยุกต์ใช้สำหรับการหาช่วงเวลาอื่นๆ ได้อีกเช่น ระยะเวลาในการปฏิบัติงานเป็นต้น

June 28, 2019

SQL Expression สำหรับหาช่วงเวลาที่ต้องการ

ใครที่เคยสร้าง Materialized View จะทราบว่าต้องมีการกำหนดช่วงเวลาเพื่อให้ Materialized View นั้นทำการ Refresh ข้อมูลเพื่อให้ได้ข้อมูลที่เป็นปัจจุบัน หรือแม้แต่ Job ก็เช่นกันต้องกำหนดช่วงเวลาเพื่อให้ Job ทำงานตามช่วงเวลาที่กำหนด บางคนอาจจะเจอปัญหาว่าไม่รู้ว่าต้องกำหนดอย่างไร เช่น อยากให้ทำงานทุก 8 โมงเช้า หรืออยากให้ทำงานทุกเที่ยงคืน เป็นต้น เพื่อให้ปัญหานั้นหมดไป เรามาทำความเข้าใจเกี่ยวกับช่วงเวลาบน Oracle กันดีกว่า

จากที่เราทราบกันว่า 1 วันมี 24 ชั่วโมง 1 ชั่วโมงมี 60 นาที และ 1 นาทีมี 60 วินาที บน Oracle เราสามารถเขียนเป็น Expression ได้ตามตัวอย่างข้างล่างนี้

ช่วงเวลา : Period	Expression 1	Expression 2
1 Day	1	1
1 Hour	1/24	1/24
1 Minute	1/(24*60)	1/1440
1 Second	1/(256060)	1/86400

ดังนั้นจาก Expression ข้างต้นเราก็สามารถเขียนช่วงเวลา ด้วย SQL Expression ดังตัวอย่างต่อไปนี้

ช่วงเวลาที่ต้องการ	SQL Expression
Now	sysdate
Tomorrow	sysdate+1
Yesterday	sysdate-1
One hour from now	sysdate + 1/24
Ten minutes from now	sysdate + 10/1440
Thirty seconds from now	sysdate + 30/86400
Tomorrow at 12 Midnight	trunc(sysdate+1)
Tomorrow at 8 AM	trunc(sysdate+1) + 8/24
Yesterday at 10 AM	trunc(sysdate-1) + 10/24
Next Monday at 12 Noon	next_day(trunc(sysdate),’MONDAY’) + 12/24

คราวนี้เราลองมาเขียนคำสั่งในการ select ข้อมูลตามช่วงเวลาที่ต้องการกันดีกว่าค่ะ

ตัวอย่างที่ 1 ต้องการ query ข้อมูลเพื่อหาข้อมูลช่วงเวลาดังนี้ วันเวลาปัจจุบัน, หนึ่งชั่วโมงถัดไปจากปัจจุบัน, พรุ่งนี้ตอนเที่ยงคืน และพรุ่งนี้ตอน 8 โมงเช้า สามารถเขียน query เพื่อให้ได้ช่วงเวลาที่ต้องการได้ดังนี้ค่ะ

ผลลัพธ์ที่ได้จาก query ตัวอย่างที่ 1

ตัวอย่างที่ 2 ต้องการ query ข้อมูลเพื่อหาข้อมูลช่วงเวลาดังนี้ วันเวลาปัจจุบัน, สิบนาทีถัดไปจากปัจจุบัน, 30 วินาทีถัดไปจากปัจจุบัน และเวลาเที่ยงของวันศุกร์ถัดไป สามารถเขียน query เพื่อให้ได้ช่วงเวลาที่ต้องการได้ดังนี้ค่ะ

ผลลัพธ์ที่ได้จาก query ตัวอย่างที่ 2

จาก 2 ตัวอย่างข้างต้นคิดว่าก็พอจะเป็นแนวทางให้กับทุกท่านได้สามารถกำหนดช่วงเวลาที่ต้องการได้ไม่มากก็น้อย

June 24, 2019

การเขียน SQL เพื่อเลื่อนลำดับขึ้นลงอัตโนมัติ

การเขียน SQL เพื่อเลื่อนลำดับขึ้นลงอัตโนมัติของ column ที่ระบุลำดับเป็นตัวเลข

รูปที่ 1 ตัวอย่างรูปแบบตาราง
(more…)

June 24, 2019
ELK #08 Oracle Audit Trail
ต่อจา ELK #07 – Logstash คราวนี้ มาใช้งานจริง โดยใช้ ELK เพื่อเก็บ Log ของ Oracle Audit Trail
1. Oracle Audit Trail บน Database Server เก็บ Log ในรูปแบบ XML โดยแต่ละ Event จะมี tag <AuditRecord> … </AuditRecord> คุมอยู่ ที่แตกต่างจาก Log ทั่วไปคือ ในแต่ละ Event จะมีเครื่องหมาย CRLF (การขึ้นบรรทัดใหม่) เป็นระยะ ๆ
2. ออกแบบให้ Logstash รับข้อมูล (Input Plugin) จาก TCP Port 5515 ซึ่งต้องใช้ Codec ในการรวบ Multiline ในแต่ละ Event เข้าด้วยกัน โดยหา pattern “<AuditRecord>” เป็นจุดเริ่มต้น ส่วนบรรทัดที่ไม่เจอ Pattern ดังกล่าวนั้นการตั้งค่า negate => “true” เป็นการบอกว่า “ให้ดำเนินการต่อไป” โดยจะเอาบรรทัดที่ตามมาจากนี้ ต่อท้าย ด้วยการตั้งค่า what=> “previous”
3. ในส่วนของ Filter Plugin จะอ่านค่าจาก “message” และ ส่งสิ่งที่ถอดจาก XML ได้ ไปยัง “doc”
4. ในส่วของ Output Plugin จะส่งออกไปยัง ElasticSearch ที่ TCP port 9200
ดัง Configuration ต่อไปนี้
```
input {
   syslog {
      port => 5515
      codec => multiline {
           pattern => "<AuditRecord>"
           negate  => "true"
           what    => "previous"
      }
   }
}
filter {
   xml {
      source => "message"
      target => "doc"
   }
}
output {
  elasticsearch {
     hosts => ["elk.server:9200"]
  }
}
```
จากนั้น ทาง Oracle Database Server ทำการเปิด Audit Trail แล้วเขียน Log ลงไฟล์ แล้วเขียน Cron เพื่อ Netcat ไฟล์ส่งมาให้ Lostash ที่เปิด Port TCP 5515 ไว้รอรับ

ผลที่ได้คือ

โดยวิธีนี้ จะเป็นการนำ Log ซึ่งจากเดิมเป็น Text Format นำมาเป็น NoSQL ได้ ซึ่งจะสามารถ Query ข้อมูลได้ง่ายยิ่งขึ้น

หวังว่าจะเป็นประโยชน์ครับ
April 16, 2019

Category: Oracle

STEP 1

STEP 2

STEP 3

การเขียน SQL เพื่อเลื่อนลำดับขึ้นลงอัตโนมัติของ column ที่ระบุลำดับเป็นตัวเลข