Author: benjawan.n

  • วิธีใช้ Power Query ดึงข้อมูลจาก PSU Web API

    benjawan.n

    PSU Web API เป็นเว็บที่ให้บริการข้อมูลในรูปแบบ JSON ทุกท่านที่มี PSU Passport สามารถสมัครเพื่อขอใช้บริการได้ที่เว็บไซต์ api.psu.ac.th โดยปัจจุบันข้อมูลที่เปิดให้บริการแล้วคือ ข้อมูลส่วนกลาง ข้อมูลระบบสารสนเทศนักศึกษา และข้อมูลระบบสารสนเทศบุคลากร สำหรับทุกท่านที่สนใจใช้บริการข้อมูลสามารถศึกษาวิธีการได้จากคู่มือของระบบค่ะ

    วันนี้จะขอนำเสนอวิธีการดึงข้อมูลจาก PSU Web API ที่ให้บริการออกมาเป็นรูปแบบตารางด้วย Power Query ใน Microsoft Excel 365 กันค่ะ ซึ่งการดึงข้อมูลด้วย Power Query นี้น่าจะเหมาะสำหรับคนที่ไม่ถนัดในการเขียนโปรแกรมเพื่อดึงข้อมูลมาใช้งานกันค่ะ

    STEP 1 : เชื่อมต่อกับแหล่งข้อมูล PSU Web API

    • คลิกเมนู Data –> Get Data –> From Other Sources –> From Web
    • ตัวอย่าง แสดงการดึงข้อมูลชื่อปริญญาที่ให้บริการจากระบบ PSU Web API
    • ป้อน URL ตามรูปแบบที่กำหนด ศึกษาได้จากคู่มือการใช้งานของระบบ (ตามตัวอย่างให้แสดงข้อมูล 100 รายการ)
    • คลิกปุ่ม OK

    STEP 2 : ตั้งค่า Power Query Editor

    • ระบบแสดงผลลัพธ์ข้อมูล 2 ส่วน คือส่วน paging ลักษณะข้อมูลเป็น Record และส่วนของ data ซึ่งจะมีลักษณะข้อมูลเป็น List ซึ่งภายใน List จะมี Record ซ้อนอยู่ข้างในอีกที
    • ตั้งชื่อ Query Name : Degree
    • คลิก Convert Into Table เพื่อแปลงผลลัพธ์ 2 ส่วนออกมาเป็นตาราง

    STEP 3 : ทำการ Transpose และแตก List ข้อมูล

    • คลิกเมนู Transpose –> Reverse Rows เพื่อสลับให้ paging เป็น First Row
    • คลิก Use First Row as Header เพื่อเตรียมแตก List ในคอลัมน์ data ออกมา
    • คลิกปุ่ม Expand ที่หัวตารางของคอลัมน์ Column2 แล้วคลิก Expand to New Rows เราก็จะได้ Record ออกมาในแต่ละบรรทัด
    • คลิกปุ่ม Expand ที่หัวตารางของคอลัมน์ Column2 อีกครั้ง
    • สามารถคลิกเลือกหรือไม่เลือกคอลัมน์ที่ต้องการหรือไม่ต้องการได้
    • คลิกไม่เลือก Use original column name as prefix
    • คลิกปุ่ม OK ระบบแสดงข้อมูลจำนวน 100 รายการตามที่กำหนดจาก PSU Web API
    • คลิกเมนู Home –> Close & Load เพื่อเสร็จสิ้น

    STEP 4 : Power Query ดึงข้อมูลจาก PSU Web API มาแสดงเป็นตารางเรียบร้อยแล้วค่ะ

    ^_^ หวังว่าโพสนี้จะเป็นประโยชน์ต่อผู้อ่านไม่มากก็น้อยนะคะ แล้วพบกันใหม่ค่ะ

  • วิธีรวมข้อมูล Excel จากหลายชีทเข้าด้วยกันเป็นตารางเดียวด้วย Power Query

    benjawan.n

    Power Query เครื่องมือสำคัญของ Power BI และ Microsoft Excel 2016 | 2019 | 365 ที่จะช่วยจัดการข้อมูลให้ได้ตามต้องการ ไม่ว่าเป็นการรวบรวมข้อมูลจากหลายแหล่งเข้าด้วยกัน การเปลี่ยนประเภทข้อมูล การจัดตารางหรือคิวรี การจัดคอลัมน์ การจัดการข้อมูลที่บกพร่อง การรวมข้อมูล การแยกข้อมูล การปรับแต่งข้อมูล และการสร้างรูปแบบข้อมูล

    จุดเด่นของ Power Query

    • รวบรวมข้อมูลได้จากหลายแหล่ง หลากหลายรูปแบบ
    • ใช้งานง่าย มีปุ่มเมนูให้ใช้งาน
    • ทุกการกระทำจะถูกบันทึกไว้เป็น Steps สามารถแก้ไข สลับลำดับ หรือลบ Steps ที่ทำไว้ได้อย่างง่ายดาย
    • รองรับการทำงานที่ Advance มากขึ้นด้วย M Code ซึ่งเป็นภาษาพิเศษของ Power Query

    ในการใช้งาน Microsoft Excel เราอาจจะมีความจำเป็นที่จะรวมรวมข้อมูลจากหลาย ๆ ซีทเป็นตารางเดียว เพื่อให้สามารถนำไปวิเคราะห์ต่อ วันนี้จึงจะขอนำเสนอความสามารถของ Power Query เพื่อรวมข้อมูล Excel แต่ละชีทรวมเป็นตารางเดียวโดยเริ่มจาก

    STEP 1 : ทำการแปลงข้อมูลในแต่ละชีทให้เป็นตาราง

    • เนื่องจาก Power Query จะทำงานกับข้อมูลในรูปแบบที่เป็นตาราง ดังนั้นต้องดำเนินการแปลงข้อมูลแต่ละชีทให้เป็นตาราง
    • ภายในชีท HATYAI คลิกเซลล์ A1
    • คลิกเมนู Insert –> Table
    • ระบบเลือกช่วงข้อมูลที่ต้องการให้อัตโนมัติ
    • เลือก My table has headers
    • คลิกปุ่ม OK
    • กำหนด Table Name : T_HATYAI
    • ดำเนินการเช่นเดียวกันในชีทที่เหลือโดยตั้งชื่อตาราง T_PATTANI, T_PHUKET, T_SURAT, T_TRANG ตามลำดับ

    STEP 2 : รวมข้อมูลเป็นตารางเดียวด้วย Power Query

    • คลิกเมนู Data –> Get Data –> From Other Sources –>Blank Query
    • ระบบแสดงหน้าต่าง Power Query Editor

    STEP 3 : กำหนดรายละเอียดของ Query

    • ตั้งชื่อ Query Name : ALL_CAMPUS
    • พิมพ์ฟังก์ชัน “=Excel.CurrentWorkbook()” ซึ่งเป็นภาษา M ที่อยู่เบื้องหลังของ Power Query เพื่อแสดงตารางข้อมูลทั้งหมดในไฟล์ Excel ของเรา
    • เราสามารถ Filter เพื่อเลือกรวมเฉพาะตารางข้อมูลที่ต้องการเท่านั้นได้
    • สมมุติเราต้องการรวมเฉพาะตารางเฉพาะที่ขึ้นต้นด้วยตัวอักษร ‘T’ เราสามารถทำได้โดยคลิกที่ Text Filters –> Begins With….
    • กรอกตัวอักษร T ลงไป คลิกปุ่ม OK

    STEP 4 : กำหนดรายละเอียดของคอลัมน์

    • สามารถเลือกหรือไม่เลือกคอลัมน์ที่ต้องการหรือไม่ต้องการได้
    • กำหนดเงื่อนไขไม่เลือก Use original column name as prefix กรณีเลือกจะมีชื่อ Content นำหน้าชื่อคอลัมน์เช่น Content.Subject ID Content.Subject Code เป็นต้น
    • คลิกปุ่ม OK ระบบแสดงข้อมูลที่เกิดจากการรวมของตารางข้อมูลตามเงื่อนไข
    • คลิกปุ่ม Close & Load ระบบจะทำการรวมข้อมูลทั้ง 5 ชีทไว้ที่ชีท ALL_CAMPUS

    STEP 4 : Power Query รวมข้อมูล Excel จากหลายชีทเข้าด้วยกันเป็นตารางเดียวเรียบร้อยแล้วค่ะ

    ^_^ หวังว่าโพสนี้จะเป็นประโยชน์ต่อผู้อ่านไม่มากก็น้อยนะคะ แล้วพบกันใหม่ค่ะ

  • วิธีการแยกคำนำหน้าชื่อ ชื่อและนามสกุล ที่รวมอยู่ในช่องเดียวกันใน Microsoft Excel

    benjawan.n

    ในการใช้งาน Microsoft Excel เราคงจะเคยเจอปัญหาที่ข้อมูลมีทั้งคำนำหน้าชื่อ ชื่อและนามสกุล รวมอยู่ในช่องเดียวกัน แต่การนำไปใช้งานของเราต้องการที่จะแยกคำนำหน้าชื่อ ชื่อและนามสกุลออกจากกันเป็นคนละช่อง

    วันนี้จะขอนำเสนอสูตรเพื่อแก้ปัญหาดังกล่าวข้างต้น มาดูกันเลยคะว่าทำยังไง ในการแก้ปัญหานี้จะต้องใช้ฟังก์ชันหลายฟังก์ชัน รวมทั้งเทคนิคการคำนวณแบบ Array ของ Excel ผสมผสานกันโดยเริ่มจาก

    STEP 1 : สร้างคอลัมน์สำหรับการแสดงผล และสร้าง List รายการคำนำหน้าชื่อที่คอลัมน์ I

    STEP 2 : ทำการแปลง TITLE_LIST เป็นตาราง เพื่อความสะดวกในการจัดการข้อมูลคำนำหน้าชื่อและการอ้างอิงข้อมูล

    • คลิกเซลล์ I1
    • คลิกเมนู Insert –> Table
    • ระบบเลือกช่วงข้อมูลที่ต้องการให้อัตโนมัติ
    • เลือก My table has headers
    • คลิกปุ่ม OK
    • กำหนด Table Name : TITLE

    STEP 3 : ทำการแยกเฉพาะคำนำหน้าชื่อมาแสดงที่คอลัมน์ D ด้วยฟังก์ชัน SEARCH , MATCH และ INDEX

    =INDEX(TITLE[TITLE_LIST],MATCH(1,SEARCH(TITLE[TITLE_LIST],B2),0))

    ฟังก์ชัน SEARCH : ค้นหาสตริงข้อความหนึ่งภายในสตริงข้อความที่สอง และส่งกลับตัวเลขที่เป็นตำแหน่งเริ่มต้นของสตริงข้อความแรกจากอักขระแรกของสตริงข้อความที่สอง

    ไวยากรณ์ : SEARCH(find_text,within_text,[start_num])

    กรณี

    • ถ้าไม่พบค่าของ find_text จะส่งกลับ #VALUE! เป็นค่าความผิดพลาด
    • กรณีพบค่าของ find_text ส่งกับตัวเลขที่เป็นตำแหน่งเริ่มต้นของสตริงข้อความแรกจากอักขระแรกของสตริงข้อความที่สอง
      • กรณี find_text เป็นช่วงข้อมูล จะส่งค่ากลับเป็นเลข 1

    ฟังก์ชัน MATCH : ค้นหารายการที่ระบุช่วงในเซลล์ แล้วส่งกลับตําแหน่งสัมพันธ์ของรายการนั้นในช่วง ตัวอย่างเช่น ถ้าช่วง A1:A3 มีค่า 5, 25 และ 38 สูตร =MATCH(25,A1:A3,0) จะส่งกลับตัวเลข 2 เนื่องจาก 25 เป็นรายการที่สองในช่วง

    ไวยากรณ์ : MATCH(lookup_value, lookup_array, [match_type])

    ฟังก์ชัน INDEX : ส่งกลับค่าจากภายในตารางหรือช่วงข้อมูล

    ไวยากรณ์ : INDEX(array, row_num, [column_num])

    STEP 4 : ทำการแยกชื่อและนามสกุลออกจากคำนำหน้าชื่อมาแสดงที่คอลัมน์ C ด้วยฟังก์ชัน SUBSTITUTE

    =SUBSTITUTE(B2,D2,””)

    ฟังก์ชัน SUBSTITUTE : ใช้แทนที่ข้อความเดิม old text ด้วยข้อความใหม่ new text ส่วน instance_num ใช้เพื่อระบุตำแหน่งการแทนที่ ซึ่งถ้าไม่ระบุ old text จะถูกเปลี่ยนด้วย new text ทั้งหมด

    ไวยากรณ์ : SUBSTITUTE(text, old_text, new_text, [instance_num])

    STEP 5 : ทำการแยกชื่อมาแสดงที่คอลัมน์ E ด้วยฟังก์ชัน LEFT และ SEARCH

    =LEFT(C2,SEARCH(” “,C2,1)-1)

    ฟังก์ชัน LEFT : ส่งกลับอักขระจำนวนหนึ่งที่อยู่ในลำดับแรกๆ ในสตริงข้อความ ตามจำนวนอักขระที่ระบุ

    ไวยากรณ์ : LEFT(text, [num_chars])

    STEP 6 : ทำการแยกนามสกุลมาแสดงที่คอลัมน์ F ด้วยฟังก์ชัน RIGHT , LEN และ SEARCH

    =RIGHT(C2,LEN(C2)-SEARCH(” “,C2,1))

    ฟังก์ชัน RIGHT : ส่งกลับอักขระสุดท้ายจำนวนหนึ่งในสตริงข้อความ ตามจำนวนอักขระที่ระบุ

    ไวยากรณ์ : RIGHT(text,[num_chars])

    ฟังก์ชัน LEN :  ส่งกลับเป็นจำนวนอักขระในสตริง

    ไวยากรณ์ : LEN(text)

    STEP 7 : แยกคำนำหน้าชื่อ ชื่อและนามสกุล ที่รวมอยู่ในช่องเดียวกันใน Microsoft Excel เรียบร้อยแล้วค่ะ

    ^_^ หวังว่าโพสนี้จะเป็นประโยชน์ต่อผู้อ่านไม่มากก็น้อยนะคะ แล้วพบกันใหม่ค่ะ

  • หาข้อมูลที่ซ้ำซ้อนในตารางด้วย LAG Function

    benjawan.n

    LAG Function
    เป็นฟังก์ชันที่ให้เราสามารถเข้าถึงแถวข้อมูลก่อนหน้าของตารางได้โดยที่ไม่ต้องทำการ self-join

    รูปแบบการใช้งาน
    LAG ( expression [, offset [, default] ] )
    OVER ( [ query_partition_clause ] order_by_clause )

    โดยที่

    • expression : ฟิลด์ข้อมูลที่ต้องการแสดง
    • offset : ระบุว่าต้องการแถวข้อมูลลำดับที่เท่าไหร่ก่อนแถวปัจจุบัน เช่น 2 คือต้องการข้อมูลลำดับที่สองก่อนหน้าแถวปัจจุบัน กรณีไม่ระบุ จะหมายถึงข้อมูลแถวที่1
    • query_partition_clause : ใช้สำหรับการจัดกลุ่มข้อมูลเพื่อให้ได้ผลลัพธ์ตามกลุ่มที่ต้องการ
    • order_by_clause : ใช้เพื่อจัดลำดับข้อมูลภายในแต่ละ partition

    ตัวอย่าง : การใช้งานฟังก์ชัน LAG

    Query ข้างต้นเป็นการหาผลการเรียนของภาคการศึกษาก่อนหน้าของรายวิชาที่ลงทะเบียนเรียน โดยที่

    • expression : ข้อมูลผลการเรียนคือฟิลด์ GRADE
    • offset : กรณีไม่ระบุหมายถึงลำดับที่หนึ่งก่อนแถวปัจจุบัน และ ตัวเลข 2 หมายถึง ลำดับที่สองก่อนแถวปัจจุบัน
    • query_partition_clause : จัดกลุ่มข้อมูลตามฟิลด์ SUBJECT_ID
    • order_by_clause : จัดเรียงข้อมูลภายใน SUBJECT_ID ด้วยฟิลด์ EDU_YEAR แล้วตามด้วยฟิลด์ EDU_TERM

    ผลลัพธ์จาก Query

    ✔ ฟิลด์ GRADE_BEFORE_1 = ผลการเรียนภาคการศึกษาก่อนหน้า
    ✔ ฟิลด์ GRADE_BEFORE_2 = ผลการเรียนของ 2 ภาคการศึกษาก่อนหน้า
    กรณีรายวิชาไหนมีค่าเป็น NULL หมายความว่านักศึกษาลงทะเบียนเรียนเพียงครั้งเดียว ไม่เคยลงทะเบียนเรียนซ้ำ
    รายวิชาในกรอบสีแดงคือรายวิชาที่นักศึกษามีการลงทะเบียนเรียนซ้ำ ตัวอย่างเช่น
    🎯 รายวิชา 0006343 มีการลงทะเบียนเรียน 2 ครั้ง
    🎯 รายวิชา 0007615 มีการลงทะเบียนเรียน 5 ครั้ง
    สามารถดูข้อมูลผลการเรียนของภาคการศึกษาก่อนหน้าได้ที่ฟิลด์ GRADE_BEFORE_1 และ GRADE_BEFORE_2

    คราวนี้เรามาประยุกต์ใช้งานฟังก์ชัน LAG กันค่ะ

    ตัวอย่าง : การประยุกต์ใช้ฟังก์ชัน LAG เพื่อหาข้อมูลที่ซ้ำซ้อนในตาราง
    เรามีตารางข้อมูลชื่อ V3_PLAN_SEC2_1_PLO ซึ่งประกอบด้วยฟิลด์
    PLAN_SEC2_1_PLO_ID (🔑) , PLAN_REPORT_ID และ PLO_DESC
    โดยเงื่อนไขของระบบกำหนดไว้ว่าจะต้องมี PLAN_REPORT_ID และ PLO_DESC เพียงรายการเดียวเท่านั้น

    📌จากข้อมูลข้างต้นจะเห็นว่ามีข้อมูลที่มีรายการซ้ำซ้อนของข้อมูล PLAN_REPORT_ID และ PLO_DESC
    เช่นที่ PLAN_REPORT_ID = C2933F24A525 และที่ PLAN_REPORT_ID = C29482CB6553 เป็นต้น📌

    วิธีการหาข้อมูลที่ซ้ำซ้อนเราสามารถหาได้หลายวิธีแต่วันนี้จะขอนำเสนอการประยุกต์ใช้ฟังก์ชัน LAG เพื่อหาข้อมูลที่ซ้ำซ้อนกันค่ะ
    ตามคำอธิบายข้างต้น LAG Function เป็นฟังก์ชันที่ให้เราสามารถเข้าถึงแถวข้อมูลก่อนแถวปัจจุบันได้โดยที่ไม่ต้องทำการ self-join
    ดังนั้นเราก็จะประยุกต์ใช้ความสามารถนี้ในการหาข้อมูลที่ซ้ำซ้อนก้นโดยมีขั้นตอนด้งนี้

    • ใช้ฟังก์ชัน LAG ในการดึงค่าข้อมูล PLO_DESC ก่อนหน้า ของ PLAN_REPORT_ID ปัจจุบัน
    • ทำการเปรียบเทียบค่า PLO_DESC กับ PLO_DESC ก่อนหน้า
    • กรณี PLO_DESC มีค่าเท่ากับ PLO_DESC ก่อนหน้า แสดงว่าข้อมูลรายการปัจจุบันมีความซ้ำซ้อนกับข้อมูลก่อนหน้า

    สามารถเขียนเป็น Query ได้ดังนี้

    ผลลัพธ์จาก Query

    😊😊เราได้ข้อมูลรายการที่ซ้ำซ้อนกันเรียบร้อยแล้วค่ะ ก็ไม่ยากแล้วใช่มั้ยคะที่จะลบรายการที่ซ้ำซ้อนออกจากตารางข้อมูลต่อไป
    ^_^ หวังว่าโพสนี้จะเป็นประโยชน์ต่อผู้อ่านไม่มากก็น้อยนะคะ แล้วพบกันใหม่ค่ะ

  • กู้คืนข้อมูลที่ถูกลบด้วย Oracle Flashback Query

    benjawan.n

    เคยเจอเหตุการณ์ที่ใช้คำสั่งผิดพลาดหรือเผลอลบข้อมูลไปโดยที่ไม่ได้ตั้งใจ และได้ทำการ commit ไปเรียบร้อยแล้วไม่สามารถ rollback กลับได้มั้ย 😊

    สำหรับคำถามข้างต้นคิดว่าคงจะมีบ้างแหละที่จะพลาดกันบ้างใช่มั้ยคะ แต่ไม่เป็นไรค่ะ เรามีวิธีการแก้ปัญหาที่เกิดจากความผิดพลาดที่ไม่ได้ตั้งใจของเรา วันนี้จึงขอนำเสนอวิธีการที่จะกู้คืนข้อมูลได้ด้วยตัวเอง โดยที่ไม่ต้องไปร้องขอให้ DBA กู้คืนข้อมูลให้ด้วย Flashback Query กันค่ะ

    Flashback Query เป็นฟีเจอร์ที่ช่วยให้เราสามารถเรียกดูข้อมูลย้อนหลัง ณ เวลา TIMESTAMP ที่ต้องการ ได้โดยใช้ AS OF clause การใช้งาน Flashback Query ก็ไม่ยาก ตามไปดูตัวอย่างการใช้งานกันค่ะ

    ตัวอย่าง

    Step1 : วันที่ 22 สิงหาคม 2565 เวลา 10:55:25 เรามีตารางข้อมูล TEST_NEW_STUDENT จำนวน 5 รายการ

    Step2 : เวลา 11:01:18 คงเบรอ ๆ นิดหน่อย ทำการลบข้อมูลตาราง TEST_NEW_STUDENT และทำการ COMMIT ไปเรียบร้อย นั่งไปสักพักเอะเมื่อกี้เราทำอะไรไป พลาดไปแล้ว ข้อมูลหายหมดแล้ว ทำไงดี

    Step3 : ใช่ Oracle มีฟีเจอร์ Flashback Query งั้นขอเรียกดูข้อมูลย้อนหลัง ณ เวลา 10:55:25 เพราะจำได้ว่าตอนนั้นข้อมูลยังมีอยู่ครบถ้วนสมบูรณ์ ก่อนที่จะพลาดลบไป 😂

    🎈TO_TIMESTAMP (‘2022-08-02 10:55:25 ‘, ‘YYYY-MM-DD HH24:MI:SS’) คือการทำการแปลงให้เป็น TIMESTAMP

    Step4 : นี่ไงข้อมูลที่เราลบไป รออะไรหละ ดำเนินการกู้คืนข้อมูลกันเลยค่ะ

    ไชโย😍 ข้อมูลกลับมาเรียบร้อยแล้ว Flashback Query ช่วยชีวิตเราได้จริง ๆ👍👍👍 หวังว่าโพสนี้จะเป็นประโยชน์ต่อผู้อ่านไม่มากก็น้อยนะคะ แล้วพบกันใหม่ค่ะ

    หมายเหตุ : ถ้าเราใช้วิธีการ Truncate จะไม่สามารถกู้คืนข้อมูลกลับได้ด้วย Flashback Query น้า

  • การประยุกต์ใข้  Sequence เพื่อสร้างตัวเลขอัตโนมัติให้กับ Table ใน Oracle Database

    benjawan.n

    ก่อนหน้านี้เคยได้รับความต้องการจากระบบหนึ่งซึ่งเป็นระบบที่รับสมัครนักเรียนเพื่อเข้าศึกษาต่อในมหาวิทยาลัยโดยมีความต้องการข้อหนึ่งว่า “ในส่วนการจัดเก็บข้อมูลเลขที่สมัคร ให้รันข้อมูลเลขที่ผู้สมัครตามโครงการที่เปิดรับในแต่ละปีการศึกษา”

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

    วิธีการดำเนินการก็ไม่ยุ่งยากแค่สร้าง sequence ตามโครงการและปีที่เปิดรับทั้งหมดไว้ให้ก่อนให้เรียบร้อยและเมื่อนักศึกษามาสมัครก็สามารถเรียกใช้ได้เลย

    ตามวิธีการข้างต้นก็ไม่น่าจะมีปัญหาอะไร แต่ก็เริ่มมีคำถามในใจ ถ้าโครงการนั้นไม่มีผู้สมัครเลย ถ้าดำเนินการสร้างไว้ก่อนก็เปลืองเปล่า ๆ หรือถ้าลืมสร้างของบางโครงการ ระบบต้องเกิดข้อผิดพลาดแน่นอน จึงเกิดแนวคิดใหม่แทนที่จะสร้างไว้ก่อน เปลี่ยนเป็นสร้าง sequence ตอนที่นักเรียนสมัครในโครงการนั้น ๆ ดีกว่า โครงการไหนไม่มีการสมัครก็ไม่ต้องสร้าง และไม่เกิดปัญหาสร้าง sequence ไม่ครบในทุกโครงการแน่นอน

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

    ขั้นตอนที่ 1

    ตรวจสอบว่ามีสร้าง sequence หรือยัง โดยเราสามารถตรวจสอบได้จาก object : user_sequences ตามตัวอย่าง Oracle Function ดังต่อไปนี้

        FUNCTION CountSequence (var_seq_name IN VARCHAR2)

            RETURN NUMBER

        IS

            var_seq_count   NUMBER := 0;

            var_seq_query   VARCHAR2 (1000);

        BEGIN

            var_seq_query :=

                   ‘SELECT COUNT (*) FROM user_sequences WHERE sequence_name = ”’

                || var_seq_name

                || ””;

            EXECUTE IMMEDIATE var_seq_query

                INTO var_seq_count;

            RETURN var_seq_count;

        END;

    ขั้นตอนที่ 2

    ทำการสร้าง sequence ตามหลักการการตั้งชื่อ ตามตัวอย่าง Oracle Procedure ดังต่อไปนี้

    PROCEDURE CreateSequence (var_seq_name IN VARCHAR2)

        IS

            var_seq_count   NUMBER := 0;

        BEGIN

            var_seq_count := CountSequence (var_seq_name);

            IF var_seq_count = 0

            THEN

                EXECUTE IMMEDIATE   ‘CREATE SEQUENCE ‘

                                 || var_seq_name

                                 || ‘ START WITH 1 INCREMENT BY 1 MINVALUE 1 MAXVALUE 9999999            

                                 NOCACHE NOCYCLE’;

            END IF;

        END;

    ขั้นตอนที่ 3

    เมื่อทำการสร้าง sequence เสร็จเรียบร้อย เราสามารถดึงค่าถัดไปของลำดับด้วยคำสั่ง nextval

    การออกเลขที่ผู้สมัครให้กับนักเรียน ซึ่งจะถูกเก็บไว้ที่ตัวแปร var_app_no โดยมีความยาวขนาด 5 หลัก เริ่มต้นจาก 00001,00002,……  ตามตัวอย่าง

    หมายเหตุ : หลักการในการตั้งชื่อ sequence คือ  ‘SEQ_APP_NO_’  + รหัสโครงการ + ปีการศึกษา

    เราสามารถออกเลขที่ผู้สมัครได้ตามตัวอย่าง Oracle Procedure ดังต่อไปนี้

        PROCEDURE ToGetApplicationNO (var_seq_name   IN     VARCHAR2,

                                      var_app_no        OUT VARCHAR2)

        IS

        BEGIN

            CreateSequence (var_seq_name);

            EXECUTE IMMEDIATE   ‘SELECT LPAD (‘

                             || var_seq_name

                             || ‘.NEXTVAL, 5, ‘

                             || ”’0”)’

                             || ‘ FROM DUAL’

                INTO var_app_no;

        END;

    จากขั้นตอนที่ 1-3 เราก็สามารถที่จะออกเลขที่ผู้สมัคร โดยรันข้อมูลเลขที่ผู้สมัครตามโครงการที่เปิดรับในแต่ละปีการศึกษา ได้ตามความต้องการแล้วค่ะ หวังว่าโพสนี้จะเป็นประโยชน์ต่อผู้อ่านไม่มากก็น้อยนะคะ แล้วพบกันใหม่ค่ะ

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

    benjawan.n

    ได้รับมอบหมายจากทีมในการสร้าง 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 ได้เท่ากับ 2 ให้นำค่าข้อมูลคูณด้วย 4
    • ค่าลำดับที่ mod ด้วย 5 ได้เท่ากับ 3 ให้นำค่าข้อมูลคูณด้วย 5
    • ค่าลำดับที่ mod ด้วย 5 ได้เท่ากับ 4 ให้นำค่าข้อมูลคูณด้วย 8
    • ค่าลำดับที่ mod ด้วย 5 ได้เท่ากับ 0 ให้นำค่าข้อมูลคูณด้วย 7

    จากแนวคิดข้างต้นและขั้นตอนวิธีการคำนวณตาม requirement ที่กำหนดให้สามารถเขียนเป็น Oracle Function เพื่อจะคำนวณหาค่าตัวเลขตรวจสอบ (check digit) ได้ดังนี้

        FUNCTION GetPaymentCheckDigit (var_reference_1   IN VARCHAR2,

                                       var_reference_2   IN VARCHAR2,

                                       var_amount        IN VARCHAR2,

                                       var_due_date      IN VARCHAR2)

            RETURN VARCHAR2

        IS

            var_text_concat   VARCHAR2 (50);

            var_total_sum     NUMBER (5);

            var_x             VARCHAR2 (1);

            var_out_result    VARCHAR2 (2);

            var_text_length   NUMBER (2);

        BEGIN

            var_total_sum := 0;

            var_text_concat :=

                var_reference_1 || var_due_date || var_reference_2 || var_amount;

            var_text_length := LENGTH (var_text_concat);

            FOR i IN 1 .. var_text_length

            LOOP

                var_x := TO_NUMBER (SUBSTR (var_text_concat, i, 1));

                CASE

                    WHEN MOD (i, 5) = 1

                    THEN

                        var_total_sum := var_total_sum + var_x * 6;

                    WHEN MOD (i, 5) = 2

                    THEN

                        var_total_sum := var_total_sum + var_x * 4;

                    WHEN MOD (i, 5) = 3

                    THEN

                        var_total_sum := var_total_sum + var_x * 5;

                    WHEN MOD (i, 5) = 4

                    THEN

                        var_total_sum := var_total_sum + var_x * 8;

                    WHEN MOD (i, 5) = 0

                    THEN

                        var_total_sum := var_total_sum + var_x * 7;

                END CASE;

            END LOOP;

            var_out_result := TRIM (TO_CHAR (MOD (var_total_sum * 3, 100), ’00’));

            RETURN var_out_result;    

    END;

    หลังจากที่ได้เขียน Oracle Function : GetPaymentCheckDigit ตามเงื่อนไขวิธีการคำนวณเรียบร้อยแล้ว ได้ทำการทดลอง Exec Oracle Function : GetPaymentCheckDigit โดยใช้ข้อมูลทดสอบตามไฟล์ตัวอย่างข้างต้นโดยที่

    • Customer No.1/Ref.1  = 6400000327
    • Due Date (DDMMYY : พ.ศ.)  = 311264
    • Customer No.2/Ref.2  = 649002300013
    • จำนวนเงิน = 0050000
    • ตัวเลขตรวจสอบ (check digit) = 87

    ตัวเลขลขตรวจสอบ (check digit) จากไฟล์ตัวอย่างมีค่าเท่ากับ 87 และผลจากการรัน Oracle Function : GetPaymentCheckDigit ผลลัพธ์ที่ได้คือ 87 เท่ากัน

    อันนี้เป็นตัวอย่างวิธีหนึ่งในการคำนวณ กรณีเพื่อน ๆ มีวิธีการอย่างอื่นก็สามารถแนะนำหรือนำเสนอกันได้นะคะ หวังว่าโพสนี้จะเป็นประโยชน์ต่อผู้อ่านไม่มากก็น้อยนะคะ แล้วพบกันใหม่ค่ะ

  • การเชื่อมต่อฐานข้อมูล Oracle ผ่าน Access office 365

    benjawan.n

    สำหรับหลาย ๆ ท่านนอกเหนือจากที่ต้องมีหน้าที่ในการพัฒนาโปรแกรมเป็นหลักแล้ว อาจจะต้องมีหน้าที่ในการนำเข้าข้อมูลด้วย ตัวเองก็เช่นกันต้องทำหน้าที่ในการนำเข้าข้อมูลเข้าฐานข้อมูล 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

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

    benjawan.n

    หลาย ๆ คนคงเคยเจอปัญหาว่าฐานข้อมูลของเรากำหนด 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 เราก็สามารถแสดงผลเป็น ภาษาอะไรก็ได้ตามที่เราต้องการ