Introduction

PL ย่อมาจาก Procedural Language เป็นภาษาที่ Oracle พัฒนาขึ้นเพื่อให้ผู้ใช้สามารถพัฒนาโปรแกรม
ในลักษณะ procedure ได้ โดยในขณะเดียวกันยังคงสามารถใช้คำสั่ง SQL ได้เช่นเดิม
สาเหตุที่ต้องพัฒนาภาษา PL/SQL ขึ้นมาใช้ เนื่องจากลักษณะคำสั่งภาษา SQL จะเป็นคำสั่งทีละคำสั่ง
เดียว แล้วให้ผลลัพธ์ทันที เช่น

SQL> SELECT * FROM emp ;
SQL> UPDATE emp
       2   SET salary = salary + (salary * 1.1)
 
ดังนั้นจึงไม่สะดวกต่อการพัฒนาโปรแกรม ซึ่งผู้พัฒนาส่วนใหญ่จะต้องมีการทำงานที่ต่อเนื่องกันในหลายคำสั่ง และอาจจะมีการใช้งานตัวแปรต่างๆ ซึ่งในภาษา SQL นั้นไม่มี

โครงสร้างโปรแกรมภาษา PL/SQL
การเขียนโปรแกรมภาษา PL/SQL เราจะเขียนเป็น block แต่ละ block มีโครงสร้างดังนี้
[ DECLARE]
                  variable_declaration (Declaration section)
BEGIN
                 executable_statements (Executable section)
[EXCEPTION]
                 exception_handling
END ;
    1. Declaration Section เริ่มต้นด้วย keyword DECLARE แล้วตามด้วยการประกาศตัวแปรต่างๆ ที่ต้องการใช้ใน block นั้น ส่วนนี้จะต้องเป็นส่วนแรกสุดของ block และจะใช้หรือไม่ก็ได้ โดยถ้าไม่ใช้ก็สามารถตัด keyword DECLARE ออกได้เลย
    2. Executable code เริ่มต้นด้วย keyword BEGIN แล้วตามด้วยคำสั่ง PL/SQL ต่างๆ ที่ต้องการทำใน block นั้น และจบด้วย keyword END ส่วนนี้เป็นส่วนที่จำเป็นต้องมีใน PL/SQL block ทุก block
    3. Exception Handler เป็นส่วนที่แทรกไว้ก่อน keyword END ใน PL/SQL block โดยเริ่มต้นด้วย keyword EXCEPTION แล้วตามด้วยคำสั่งในการตรวจสอบความผิดพลาดที่อาจเกิดขึ้นได้ในโปรแกรมส่วนนี้จะใช้ก็ต่อเมื่อต้องการตรวจสอบความผิดพลาดจากการทำงานในโปรแกรม ในกรณีที่ไม่ต้องการตรวจสอบความผิดพลาด จะไม่ใช้ส่วนนี้ก็ได้

หลักการเขียน PL/SQL blocks

• การประกาศตัวแปรและการ handle exception เป็น optional ถ้าไม่ใช้ไม่ต้องมีก็ได้
• 1 คำสั่ง จะใช้กี่บรรทัดก็ได้ แต่จะต้องปิดด้วย semicolon ( ; ) เสมอ
• สามารถเขียน PL/SQL block ซ้อนกันได้
• ตัวแปรที่ประกาศภายใน block จะใช้งานได้เฉพาะใน block นั้นถ้าออกนอก block แล้วก็จะไม่รู้จัก
• การ comment ทำได้ 2 วิธี
• ใช้ - - นำหน้าข้อความที่ต้องการ comments เป็นการ comment ตั้งแต่จุดนั้นจนจบบรรทัด
• ใช้ /* เปิด และ */ ปิดข้อความที่ต้องการ comment (ใช้ comment หลายบรรทัดได้
• การ assign ค่าให้ตัวแปรใช้เครื่องหมาย :=
• เครื่องหมายสำหรับเปรียบเทียบค่า ได้แก่ =

    1. logical operator ได้แก่ AND, OR, NOT

    2. เครื่องหมายที่ใช้ในการคำนวณ ได้แก่ + , - , * , / , ** (ยกกำลัง)

    3. Concatenation operator ได้แก่ ||
PL/SQL Variables
เราอาจใช้ตัวแปรในภาษา PL/SQL เพื่อเก็บค่าบางอย่างไว้ชั่วคราวเพื่อนำไปใช้งานต่อ โดยการจัดการกับตัวแปรนั้น จะมีหลักการดังนี้
  VARCHAR2 (maximum_length) variable length character (เก็บค่าได้สูงสุด 32767 bytes)
  NUMBER [ (p[,s] ) ] number (p = precision = จำนวนหลักทั้งหมดของตัวเลขที่จะเก็บ, s = scale = จำนวนหลักหลังจุดทศนิยม)
  DATE เก็บวันและเวลา ช่วงข้อมูลที่สามารถเก็บได้คือ ตั้งแต่ปี 4712 (ก่อนคริสตศักราช) ถึง ปี ค.ศ.9999
  CHAR [ (maximum_length) ] fixed length character ความยาวสูงสุดที่เก็บได้ คือ 32767 bytes ถ้าไม่ระบุความยาว default คือ 1 char
  LONG variable length character เก็บค่าได้สูงสุด 32760 bytes (ถ้าเป็น LONG datatype ใน database จะเก็บได้สูงสุด 2 GB)
  LONG RAW variable length binary data เก็บค่าได้สูงสุด 32760 bytes (ถ้าเป็น LONG RAW datatype ใน database จะเก็บได้สูงสุด 2 GB)
  BOOLEAN logical value มีค่าที่เป็นไปได้ 3 ค่า คือ TRUE , FALSE , NULL
  BINARY_INTEGER integer value มีค่าอยู่ระหว่าง –2417483647 ถึง 2147483647

Declaration Syntax :
      var_name [ CONSTANT ] datatype [ NOT NULL ] [ { DEFAULT | : = } initial_val ] ;
      โดย var_name คือชื่อตัวแปร
      CONSTANT เป็น keyword เพื่อระบุว่าตัวแปรนี้ เป็น constant variable (ตัวแปรที่ไม่สามารถเปลี่ยนค่าได้ในโปรแกรม)
      datatype คือประเภทของตัวแปร
ตัวอย่าง :

    v_job VARCHAR2(15) ;
    v_count BINARY_INTEGER : = 0 ;
    v_sum_sal NUMBER(10,2) : = 0 ;
    v_shipdate DATE : = SYSDATE + 5 ;
    c_vat_rate CONSTANT NUMBER (3,1) : = 7 ;
    v_valid BOOLEAN NOT NULL : = TRUE ;
    v_sal1 NUMBER(10,2) : = 5000 ;
    v_sal2 NUMBER(10,2) : = 6000;
    v_equal BOOLEAN : = TRUE ;
การใช้ % TYPE Attribute
   เราจะใช้ %TYPE Attribute เพื่อกำหนด datatype ของตัวแปรให้เหมือนกับ
   • Datatype ของ Database Column ใช้ table.column%TYPE
   • Datatype ของตัวแปรที่ declare ไว้แล้ว ใช้ variable_name%TYPE
   ตัวอย่างการประกาศตัวแปรโดยใช้ %TYPE Attribute
    ...
      v_ename emp.ename%TYPE ;
      v_sal NUMBER(10,2) : = 0 ;
      v_new_sal v_sal%TYPE : = 0 ;
      …
Non – PL/SQL variables
      เป็นการอ้างถึง Bind variables (ใน SQL * Plus) หรือ Host variables (ตัวแปรใน environment ที่ใช้อยู่)
เช่น ตัวแปรใน Developer Forms, Developer Reports, ตัวแปรใน Precompiler programs เป็นต้น การ reference
ถึงตัวแปรประเภทนี้ จะต้องนำหน้าด้วย : (colon) เสมอ เช่น :ename เป็นต้น
การติดต่อกับ database ในโปรแกรมภาษา PL/SQL
     ในโปรแกรมภาษา PL/SQL เราสามารถสั่ง SQL commands บางตัวได้ ได้แก่
     • SELECT Statement สามารถใช้ได้ แต่ต้องมี clause เพิ่ม คือ INTO clause
     • DMLs Statement สามารถใช้ได้ตามปกติ (และสามารถนำ PL/SQL variables มาใช้ใน DMLs Statement ได้ด้วย) เช่น INSERT,UPDATE,DELETE
     • Transaction Control ใช้ได้ตามปกติ เช่น COMMIT, ROLLBACK, SAVEPOINT
SELECT Statement
      ข้อจำกัดของการใช้ SELECT ... INTO เพื่อดึงข้อมูลจาก database คือ การ SELECT จะต้องได้ข้อมูล 1 record เท่านั้น (ไม่เจอก็ไม่ได้ จะเกิด NO_DATA_FOUND exception และถ้าเจอมากกว่า 1 record ก็จะเกิด TOO_MANY_ROWS exception) ซึ่งถ้าเราต้องการจะ SELECT ข้อมูลทีละหลาย records จะต้องใช้ Explicit Cursor ช่วย
Syntax :
   SELECT select_list
   INTO {variable_name [, variable_name] . . . | record_name}
   FROM table
   WHERE condition;
ตัวอย่าง :
   SELECT ename, job, sal
   INTO my_ename, my_job, my_sal
   FROM emp
   WHERE empno = my_empno;
Explicit cursors
   เป็น cursors ที่เราสร้างขึ้นเพื่อใช้ในการ SELECT ข้อมูล (ที่อาจ return ข้อมูล > 1 record)
Cursor’s Declaration
Syntax :
   CURSOR cursor_name IS
     Select_statement;
หมายเหตุ : - ไม่ต้องระบุ INTO clause ในการประกาศ cursor
ตัวอย่าง :
    CURSOR dept_data IS
    SELECT dept_id, dept_name
   FROM department
   OEDER BY dept_id ;
การ Fetch ข้อมูลจาก Cursor
Syntax :
    FOR record_name IN cursor_name LOOP
       Statement1;
       Statement2;
     . . .
   END LOOP;
หมายเหตุ : ไม่ต้องประกาศตัวแปร record_name ให้ตั้งชื่อได้เลย และโปรแกรมจะรู้จักตัวแปรนี้เฉพาะภายใน
loop เท่านั้น
ตัวอย่าง :
   FOR i IN dept_data LOOP
   BEGIN
     INSERT INTO backup_dept (dept_id , dept_name)
     VALUES (i.dept_id , i.dept_name)
    END;
   END LOOP;
การ Control flow การทำงานใน PL/SQL
     การ Control Flow การทำงานใน PL/SQL program มี 2 ลักษณะ ได้แก่
     • การเช็คเงื่อนไขด้วย IF statement
     • การวน LOOP ทำงาน
             - Basic Loop
             - FOR Loop
             - WHILE Loop

 IF Statement
Syntax :
   IF condition THEN
     statements;
  [ELSIF condition THEN
     statements; ]
     …
  END IF;
หมายเหตุ : Condition คือตัวแปร Boolean หรือ expression ที่ได้ผลลัพธ์เป็นค่า Boolean
ตัวอย่าง :
Declare
   v_code Varchar2(5);
   v_total Number(5,2);
Begin
  v_code := 0 ; /* การ Assign ค่าให้กับตัวแปร */
  v_total := 20 * (15/100) ; /* การคำนวณและเก็บค่า */
  If v_total < 100 Then /* การตรวจสอบเงื่อนไข */
     Message(‘จำนวนคงเหลือต่ำกว่า 100’); /* การแสดงข้อความ */
  Else
     Message(‘จำนวนคงเหลือสูงกว่า 100’);
  End if;
End;
Loop Statements
การทำงานแบบ Loop เป็นการทำงานคำสั่งชุดเดิมหลายๆ รอบ loop ใน PL/SQL มี 3 แบบ
1. Basic Loop
  เป็นการวน Loop ไปเรื่อย ๆ ไม่มีกำหนด (คือทำตั้งแต่ Loop จนถึง END Loop แล้ววนกลับขึ้นไปทำใหม่ตั้งแต่ Loop ไปเรื่อยๆ) จึงจำเป็นต้องมีการเช็คเงื่อนไขในการหยุดวน Loop
2. FOR Loop
  เป็นการวน Loop ที่ทราบจำนวนครั้งในการทำงานที่แน่นอน
3. WHILE Loop
  เป็นการวน Loop ตามเงื่อนไข โปรแกรมจะทำการเช็คเงื่อนไขก่อน ตราบใดที่เงื่อนไขได้ผลลัพธ์เป็น TRUE จะทำงานตาม Loop ดังนั้น Loop ชนิดนี้อาจไม่ถูกทำงานเลยก็ได้ถ้าการเช็คเงื่อนไขในครั้งแรกไม่ได้ผลเป็น TRUE

Basic Loop
Syntax : LOOP
                     statement1;
                     statement2;
                 END LOOP;
วิธีเช็คเงื่อนไขในการออกจาก Loop ทำได้ 2 แบบ
1) IF condition THEN
           EXIT;
     END IF;
2) EXIT WHEN condition (หรืออาจสั่ง EXIT; โดยไม่มีเงื่อนไขเลยก็ได้)
ตัวอย่าง 1 :
    DECLARE
      v_ord_id NUMBER := 100;
      v_counter NUMBER(2) := 1;
   BEGIN
    LOOP
       INSERT INTO ord_lines (ord_id , item_id) VALUE (v_ord_id, v_counter);
       v_counter := v_counter + 1;
       EXIT WHEN v_counter > 10;
    END LOOP;
   END;
ตัวอย่าง 2 :
    BEGIN
     LOOP /*ให้เริ่มทำการวน Loop*/
        Select NVL(Code,’0’) Into :Block1.code
        From Master; /*ทำการดึงข้อมูลมาลง Form*/
             If :System.Last_Record = ‘TRUE’ Then
                   Exit; /*ตรวจสอบการออกจาก Loop*/
            End if;
       Next_Record;
    END LOOP;

FOR loop
Syntax :
   FOR index IN [REVERSE] lowerbound . . upperbound LOOP
     statement1;
     statement2;
  END LOOP;
หมายเหตุ :
- [REVERSE] ใช้สำหรับวน loop แบบย้อนหลัง (จาก upper_bound ลดลงทีละ 1 ไปเรื่อยๆ จนถึง lower_bound)
- ไม่ต้องประกาศตัวแปร index ให้ตั้งชื่อได้เลย และโปรแกรมจะรู้จักตัวแปรที่เป็น index ภายใน loop เท่านั้น
ตัวอย่าง :
  BEGIN
     FOR i In 1..99 Loop /*คำสั่ง For Loop 1 ถึง 99*/
        Message(To_char(i)) ; /*แสดงค่า i */
     END LOOP;
  END;
WHILE loop
Syntax :
WHILE condition LOOP
  statement1;
  statement2;
END LOOP;
ตัวอย่าง :
DECLARE
  v_ord_id NUMBER := 100;
  v_counter NUMBER(2) := 1;
BEGIN
  WHILE v_counter <= 10 LOOP
     INSERT INTO ord_lines (ord_id , item_id)
     VALUE (v_ord_id , i);
     v_counter := v_counter + 1;
  END LOOP;
END;

คำสั่ง Exception
ใช้ในการตรวจสอบและป้องกันข้อผิดพลาดที่อาจเกิดขึ้นได้เนื่องจากการเขียนโปรแกรม
Syntax :
Statement…
Exception When [Error Message] Then Statement ;
Error Message ได้แก่ …

ตัวอย่าง :
  Declare
   v_name Varchar2(100) ;
  Begin
   Select emp_name Into v_name
   From employee
   Where emp_id = ‘00001’ ;
   Exception When No_Data_Found Then Null ; /* เมื่อไม่พบข้อมูล */
  End ;
Subprograms  มี 3 ประเภท คือ
  1) PROCEDURE เป็นโปรแกรมย่อยที่ทำงานแล้วไม่มีการคืนค่ากลับ
  2.) FUNCTION เป็นโปรแกรมย่อยที่นิยมใช้เพื่อหาค่าอะไรบางอย่างแล้วคืนค่านั้นกลับมาให้ในรูปของชื่อ function
  3.) PACKAGE เป็นการรวบรวม PROCEDURE หรือ FUNCTION หลายๆ ตัวไว้ด้วยกัน เพื่อง่ายต่อการควบคุมในแง่ privilege และเป็นหมวดหมู่ดีขึ้น
โครงสร้างการเขียน PROCEDURE และ FUNCTION
• PROCEDURE name [ ( parameter, . . . ) ]
   IS
         PL/SQL block;
• FUNCTION name [ ( parameter, . . . ) ]
   RETURN datatype
   IS
        PL/SQL block;
หมายเหตุ PL/SQL block ให้เริ่มด้วยการประกาศตัวแปร (ถ้ามี) โดยไม่ต้องมี keyword DECLARE หรือ ถ้าไม่ใช้ตัวแปร ให้เริ่มด้วย BEGIN ได้เลย
วิธีการประกาศ parameter ใน Subprograms
  param_name [ IN | OUT | IN OUT ] datatype [ { DEFAULT | := } expr ]
  โดย param_name คือชื่อ parameter [ IN | OUT | IN OUT] คือ mode ของ parameter
  IN หมายถึงเป็น parameter ที่รับค่าเข้ามาทำงาน โดยไม่มีการเปลี่ยนค่าระหว่างที่อยู่ใน subprogram
  OUT หมายถึงเป็น parameter ที่ไม่มีการรับค่าเริ่มต้นเข้ามา แต่จะใช้ในการส่งค่ากลับไป
  IN OUT หมายถึงเป็น parameter ที่รับค่าเข้ามาใช้ในการทำงานและสามารถเปลี่ยนแปลงค่าได้ใน subprogram ด้วย (คือลักษณะของ IN parameter และ OUT parameter รวมกันนั่นเอง)
ตัวอย่าง Procedure
PROCEDURE change_salary ( p_emp_id IN NUMBER, p_new_salary IN NUMBER) IS
/* variables declaration (don’t include DECLARE keyword) */
BEGIN
  UPDATE emp
  SET salary = p_new_salary
  WHERE id = p_emp_id;
  COMMIT;
END;
ตัวอย่าง Function
FUNCTION tax (p_value IN NUMBER) RETURN NUMBER IS
BEGIN
  RETURN (p_value * .1);
END;

---

TrackBack URI for this entry

Comments (0)

Subscribe to this comment's feed

Show/Hide comments

Show/Hide comment form

Name

Email

Comment

Write the displayed characters

Add Comment