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

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

เนื่องในการพัฒนาระบบนี้ มีการใช้ 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 เราก็สามารถที่จะออกเลขที่ผู้สมัคร โดยรันข้อมูลเลขที่ผู้สมัครตามโครงการที่เปิดรับในแต่ละปีการศึกษา ได้ตามความต้องการแล้วค่ะ หวังว่าโพสนี้จะเป็นประโยชน์ต่อผู้อ่านไม่มากก็น้อยนะคะ แล้วพบกันใหม่ค่ะ

Leave a Reply

This site uses Akismet to reduce spam. Learn how your comment data is processed.