Database – Page 2 – CoP PSU IT Blog

ว่าด้วยเรื่องการคำนวณตัวเลขตรวจสอบ (Check Digit)

May 17, 2022 16:47

ได้รับมอบหมายจากทีมในการสร้าง Oracle Function เพื่อคำนวณตัวเลขตรวจสอบ (check digit) ของการชำระเงินค่าสมัครผ่านช่องทางการชำระเงินช่องทางหนึ่ง โดยได้รับ requirement มาดังภาพข้างล่างนี้ จากภาพข้างต้นจะมีข้อมูลสำหรับการนำเข้า 4 ชุดซึ่งประกอบด้วย ชุดที่ 1 : Customer No.1/Ref.1 ชุดที่ 2 : Due Date (DDMMYY : พ.ศ.) ชุดที่ 3 : Customer No.2/Ref.2 ชุดที่ 4 : จำนวนเงินที่ต้องชำระ พร้อมด้วยขั้นตอนวิธีในการคำนวณตัวเลขตรวจสอบ (check digit) ดังนี้ มาทำความเข้าใจกับวิธีคำนวณกันก่อนที่จะเริ่มต้นสร้าง Oracle Function ถ้ามาดูรายละเอียดของวิธีการคำนวณในข้อที่ 1 ซึ่งเป็นการหาค่าประจำหลักของข้อมูลนำเข้า โดยวิธีการคือ นำข้อมูลแต่ละหลักคูณค่าคงที่ คือ 6, 4, 5, 8, 7 ไปเรื่อย ๆ ตามลำดับ การหาค่าประจำหลักของข้อมูลนำเข้า จะสังเกตุว่าถ้านำข้อมูลนำเข้าทั้ง 4 ชุดข้อมูลมาเรียงต่อกันจะมีความยาวเท่ากับ 35 โดยที่ ลำดับที่ 1, 6, 11, 16, 21, 26, 31 นำค่าข้อมูลคูณด้วย 6 ลำดับที่ 2, 7, 12, 17, 22, 27, 32 นำค่าข้อมูลคูณด้วย 4 ลำดับที่ 3, 8, 13, 18, 23, 28, 33 นำค่าข้อมูลคูณด้วย 5 ลำดับที่ 4, 9, 14, 19, 24, 29, 34 นำค่าข้อมูลคูณด้วย 8 ลำดับที่ 5,10, 15, 20, 25, 30, 35 นำค่าข้อมูลคูณด้วย 7 กรณีถ้าไม่ต้องคิดให้ซับซ้อนเราก็บอกว่า fix ค่าไปเลยตามเงื่อนไขข้างต้น ก็สามารถจะหาค่าประจำหลักของข้อมูลนำเข้าแต่ละตัวได้ แต่ถ้าจะยืดหยุ่นกว่านั้นก็สามารถมองได้ว่า ลำดับที่ 1, 6, 11, 16, 21, 26, 31 เมื่อ mod ด้วย 5 จะได้ค่ากับ 1 ลำดับที่ 2, 7, 12, 17, 22, 27, 32 เมื่อ mod ด้วย 5 จะได้ค่ากับ 2 ลำดับที่ 3, 8, 13, 18, 23, 28, 33 เมื่อ mod ด้วย 5 จะได้ค่ากับ 3 ลำดับที่ 4, 9, 14, 19, 24, 29, 34 เมื่อ mod ด้วย 5 จะได้ค่ากับ 4 ลำดับที่ 5,10, 15, 20, 25, 30, 35 เมื่อ mod ด้วย 5 จะได้ค่ากับ 0 ดังนั้นเราก็สามารถค่าประจำหลักของข้อมูลนำเข้าได้ดังต่อไปนี้ ค่าลำดับที่ mod ด้วย 5 ได้เท่ากับ 1 ให้นำค่าข้อมูลคูณด้วย 6 ค่าลำดับที่ mod ด้วย 5 ได้เท่ากับ

Oracle: แปลงข้อมูล JSON เป็น Table หรือ View

March 29, 2022 13:23

ในการส่งข้อมูลระหว่างแอพพลิเคชัน ในปัจจุบันปฏิเสธไม่ได้ว่ารูปแบบ JSON ได้รับความนิยมสูงมาก วันนี้มีโจทย์ในการพัฒนาระบบอย่างนึงที่ผมเจอคือ ได้ข้อมูลจาก log มาจัดเก็บในฐานข้อมูลซึ่งอยู่ในรูปแบบ JSON แต่ต้องการแยกเก็บเป็นฟิลด์เพื่อนำไปประมวลผล และวิเคราะห์ต่อ วิธีการแปลงข้อมูลที่ผมเลือกใช้ คือใช้ฟังก์ชัน JSON_TABLE โดยตัวอย่างข้อมูล JSON มีรูปแบบดังนี้ จะเห็นได้ว่ามีจำนวน Key และ Value จำนวนมากและอยู่ในรูปแบบ Parent Chid ด้วยแต่สามารถสร้าง View หรือ Table จากข้อมูลดังกล่าวด้วยคำสั่งเดียวดังนี้ สังเกตุว่า จุดสำคัญคือคำสั่ง json_table ที่เราจะต้องระบุฟิลด์ที่เก็บข้อมูล JSON ของเราดังในตัวอย่างคือ l.detail ที่เดิมเก็บอยู่ในตาราง log_traffic โดยใช้ path ย่อเป็น ‘$’ จากนั้นระบุ Keyword columns และตั้งชื่อ columns สำหรับ view ที่เราจะสร้างเช่น service_name ซึ่งใช้เก็บข้อมูลที่อยู่ใน path ดังนี้ ‘$.service.name’ เป็นต้น ซึ่งเมื่อรันคำสั่งดังกล่าวจะได้ view ดังรูป ในเรื่องของประสิทธิภาพพบว่าประมวลผลจำนวน 4000+ รายการใช้เวลาประมาณ 0.0008 วินาที ซึ่งถือว่ารวดเร็ว แต่ถ้าจำนวนข้อมูลมากขึ้น และประมวลผลช้าลงอาจปรับเป็น Materialize View หรือวิธีการอื่นๆ ที่ใช้ในการประมวลผลเฉพาะรายการที่เพิ่มใหม่ หรือประมวลผลตามรอบเวลา (ไม่เรียลไทม์) โดยใช้คำสั่งดังกล่าวช่วยก็ได้เช่นกัน หวังว่าจะเป็นประโยชน์สำหรับท่านที่มีโจทย์ประมาณนี้นะครับ

สร้าง ER Diagram ง่ายๆ ด้วย Toad for Oracle

February 24, 2022 14:39

สวัสดีผู้อ่านทุกท่าน Blog ที่ 2 ของ TOR รอบนี้จะขอว่าด้วยเรื่องของ ER Diagram กันค่ะ การจัดทำ ER Diagram โดยปกติเราสามารถทำได้หลากหลายวิธี ใช้งานได้มากมายหลากหลายเครื่องมือ ก็แล้วแต่แหละเนอะ ว่าใครถนัดแบบไหน ใช้เครื่องมือใด สำหรับทางผู้เขียนจะคลุกคลีตีโมงอยู่กับ Toad for Oracle เป็นหลัก ครั้งนี้เลยจะมาขอแชร์วิธีการเล็กๆ น้อยๆ ซึ่งมีความสะดวกสบายในการสร้าง ER Diagram จากข้อมูลตารางที่อยู่ในฐานข้อมูลของเราโดยอัตโนมัตินั่นเอ๊งงงง !!! ปะ …. เรามาเริ่มกันเลยละกัน เมื่อเราเปิด Toad for Oracle และ Connect เข้า Database ที่เราต้องการเรียบร้อยแล้ว เราก็จะได้หน้าจอตามรูป ปล…หน้าตาอาจจะต่างกันเล็กน้อยแล้วแต่ version ของแต่ละคนที่ใช้งาน 2. ให้คลิกเลือกเมนู “Database” —> จากนั้นเลือกเมนู “Report” —> เลือกเมนู “ER Diagram” เมื่อเรียบร้อยแล้ว เราก็จะได้พื้นที่ Workspace ของเราขึ้นมา ตัวอย่างดังรูปด้านล่างเลยจ๊ะ 3. จากนั้นคลิกเลือก Add Objects ตรงสัญลักษณ์เครื่องหมาย + สีเขียวๆฟ้าๆ จากนั้นโปรแกรมจะแสดงหน้าต่างให้เราเลือก Table หรือ View จาก Schema ที่เราต้องการ เพื่อ “Add to ER Diagram“ ปล … ทั้งนี้หากเราไม่เลือกที่ละรายการ ก็สามารถเลือกได้ว่า Select All , Deselect All หรือ Invert selection ผ่าน เครื่องมือที่มีให้ได้เช่นเดียวกัน 4. เราลองมาเลือก Table ข้อมูลที่เราต้องการนำมาสร้าง ER Diagram กันเลย และเมื่อเลือกเรียบร้อยแล้วก็ให้คลิกปุ่ม “OK” ได้เลยนะ จากนั้นก็จะได้หน้าตา ER Diagram ที่โยงความสัมพันธ์ของข้อมูลให้แล้ว ดังรูปเลยทุกคน !! 5. และหากเราต้องการให้ใน ER Diagram ของเราแสดงเพียงแค่ชื่อ Column Name เท่านั้น ไม่แสดงรายละเอียดอื่นๆ ก็แนะนำให้เลือกตรงข้อความ All Columns จากนั้นเลือกแสดงแบบ “Column Names only“ 6. เมื่อเลือกแสดงแบบ Column Names only ก็จะได้หน้าตาดังรูปด้านล่างนะ เป็นยังไงกันบ้างเอ่ย Blog นี้พอจะช่วยให้การสร้าง ER Diagram สำหรับผู้อ่านง่ายขึ้นบ้างมั้ย ? ทั้งนี้ …. ทางผู้เขียนก็ขอออกตัวก่อนเลย มันมีมากมายหลายวิธีจริงๆ ในการสร้าง ER Diagram ทั้งอาจจะง่ายกว่าวิธีนี้ หรือยุ่งยากกว่าวิธีนี้ก็เป็นได้ ผู้เขียนจึงอยากจะขอแชร์วิธีที่ผู้เขียนเลือกใช้เพื่อช่วยในการทำงานของทางผู้เขียนเองเท่านั้น และก็ยังคงหวังเป็นอย่างยิ่งว่า Blog นี้จะมีประโยชน์กับผู้อ่านทุกท่าน ไม่มากก็น้อย ตามคติที่ว่า “รู้ไว้ใช่ว่าใส่บ่าแบกหามมมมม” นั้นเอง 555+ Special Thanks : Supervisor Regist Team สำหรับคำแนะนำในการใช้งานแง๊บบบบ 🙂

เชื่อมต่อจากฐานข้อมูล Oracle ไปยังฐานข้อมูลอื่นด้วย Oracle Database Gateway

May 26, 2021 22:58

โดยปกติหากต้องการเชื่อมต่อฐานข้อมูลสองฐานข้อมูลสำหรับ Oracle สามารถทำได้โดยการสร้าง database link แต่กรณีที่ต้องการสร้างการเชื่อมต่อจากฐานข้อมูล Oracle ไปยังฐานข้อมูลที่สร้างโดยระบบจัดการฐานข้อมูลอื่นที่ไม่ใช่ Oracle เช่น MS SQL Server, MySQL สามารถทำได้โดยใช้เครื่องมือที่เรียกว่า Oracle Database Gateway นั่นเอง ตัวอย่างการใช้ Oracle Database Gateway เชื่อมต่อไปยัง MS SQL Server เพื่อให้เห็นภาพการใช้งานจะยกตัวอย่างการใช้งานกับ MS SQL Sever ดังนี้ หมายเหตุ รุ่นของ Software ที่ใช้ในตัวอย่างนี้คือ Oracle Database 12c R2 (12.2.0) Oracle Database Gateways 12c (12.2.0) เตรียมข้อมูลที่จำเป็นก่อนการติดตั้ง ข้อมูลของ SQL Server ที่เราต้องการเชื่อมต่อ ชื่อเครื่องหรือ IP Address ของเครื่องที่ SQL Server ติดตั้งอยู่ หมายเลข port ของ SQL Server ที่เราต้องการเชื่อมต่อ ชื่อฐานข้อมูล SQL Server ที่เราต้องการเชื่อมต่อ 2. ขั้นตอนการติดตั้ง Oracle Database Gateway เมื่อดาวน์โหลดตัวติดตั้งจากเว็บไซต์ของ Oracle แล้ว ดำเนินการติดตั้งจากตัวติดตั้งที่ดาวน์โหลด ดังตัวอย่างต่อไปนี้ ขั้นตอนนี้จะเป็นการใส่ข้อมูลของฐานข้อมูล MS SQL Server ที่ต้องการเชื่อมต่อ หลังจากนั้นทำการติดตั้งไปตามขั้นตอนตามลำดับ จนถึงขั้นตอนการสร้าง listener หลังจากติดตั้ง listener สำเร็จแล้ว ก็เป็นอันเสร็จสิ้นการติดตั้ง Oracle Database Gateway *** หลังจากการติดตั้ง หากเรียกดู Service จะเห็นว่ามี service ของ listener ปรากฎขึ้นมา 3. การตั้งค่าหลังจากติดตั้ง ไปยังโฟลเดอร์ที่ติดตั้ง Oracle Database Gateway เช่น C:\app\tg\Administrator\product\12.2.0\tghome_1\dg4msql\admin เปิดไฟล์ initdg4msql.ora ตรวจสอบการตั้งค่าตรง HS_FDS_CONNECT_INFO HS_FDS_CONNECT_INFO=[192.168.100.184]/1433/MSSQLSERVER กรณีมีแค่ instance เดียว อาจจะละชื่อ instance ไว้ และระบุชื่อฐานข้อมูลได้เลย ดังตัวอย่าง HS_FDS_CONNECT_INFO=[192.168.100.184]:1433//FILMSDSQL 4. สร้าง database link เมื่อติดตั้ง Oracle Database Gateway เสร็จแล้ว ขั้นตอนต่อมาคือการสร้าง database link บนฐานข้อมูล Oracle เพื่อทำการเรียกดูข้อมูลบน MS SQL Server ผ่านทาง database link ที่สร้างขึ้นมา รูปแบบคำสั่งในการสร้าง Database Link เป็นดังนี้ CREATE PUBLIC DATABASE LINK DBLinkName CONNECT TO sa IDENTIFIED BY password USING ‘dg4msql’; DbLinkName คือ ชื่อของ database link sa คือชื่อของ user บน MS SQL Server (อาจจะเป็น user อื่นก็ได้) password คือ รหัสผ่านสำหรับ user บนฐานข้อมูล MS SQL Server 5. การเรียกดูข้อมูล สามารถเรียกดูข้อมูลผ่าน database link ที่สร้างขึ้นโดยใช้คำสั่ง sql เช่น select * from table@DBLinkName; เมื่อ table

การใช้ grant และ grant with grant option ใน Oracle

May 26, 2021 22:54

การใช้คำสั่ง grant โดยทั่วไปเมื่อต้องการให้สิทธิ์สำหรับดำเนินการกับ object ใด ๆ ของ user หนึ่งให้กับอีก user หนึ่ง จะใช้คำสั่งคือ grant ตัวอย่าง เช่น user1 เป็นเจ้าของตารางชื่อ table01 ในฐานข้อมูลมี user ชื่อ user2 อยู่ในฐานข้อมูลเดียวกับ user1 ต้องการให้ user2 สามารถใช้คำสั่ง select ข้อมูลจากตาราง table01 ของ user1 บน user1 ดำเนินการโดยใช้คำสั่งในการให้สิทธิ์คือ grant select on user1.table01 to user2; หลังจากให้สิทธิ์แล้ว บน user2 สามารถใช้คำสั่งในการ select ข้อมูลจากตาราง table01 ของ user1 ได้ ตังตัวอย่างดังนี้ select * from user1.table01; //ต้องระบุ user คั่นด้วยจุด และตามด้วยชื่อตาราง การให้สิทธิ์ นอกจาก select แล้ว ยังมีสิทธิ์อื่น ๆ อีก เช่น insert, update, delete, execute ฯลฯ ตัวอย่างการให้สิทธิ์มากกว่าหนึ่งสิทธิ์ grant select, insert, update, delete on user1.table01 to user2; การใช้คำสั่ง grant with grant option รูปแบบการใช้คำสั่งคือ หลังคำสั่ง grant ปกติ ตามด้วย with grant option เช่น grant select on user1.table01 to user2 with grant option; ผลจากการใช้คำสั่ง with grant option จะมีผลให้ user ที่ได้รับสิทธิ์ สามารถให้สิทธิ์ที่ได้รับมา grant ต่อไปให้กับ user อื่นต่อได้นั่นเอง ต่อเนื่องจากตัวอย่างแรก มี user3 ในฐานข้อมูลเดียวกันกับ user1 และ user2 เมื่อ user1 ใช้คำสั่ง grant select on user1.table01 to user2 with grant option; มีผลทำให้ user2 สามารถ grant สิทธิ์ในการ select ให้กับ user อื่นต่อได้ เช่น grant select on user1.table01 to user3; user ที่ได้รับสิทธิ์ grant with option สามารถ ใช้ grant with option ต่อให้กับ user อื่นได้หรือไม่ ??? user ที่ได้รับการ grant ด้วยคำสั่ง with grant option สามารถ grant ให้ user อื่นด้วยคำสั่ง with grant option ด้วยเช่นกัน ตัวอย่าง เช่น มี user4 ในฐานข้อมูลเดียวกับ user1, user2, user3 ต่อเนื่องจากตัวอย่างก่อนหน้านี้ user3 สามารถใช้คำสั่ง with grant option ต่อให้กับ user4 ได้