PL/SQL_002_Introduction to PL/SQL ตอนที่ 2

คำสั่งในภาษา PL/SQL

1. ใช้คำสั่งภาษา SQL ได้ดังนี้

Data Retrieval	SELECT
Data Manipulation Language	INSERT UPDATE DELETE
Transaction Control	COMMIT ROLLBACK SAVEPOINT

2. การ Assign ค่าตัวแปร

ชื่อตัวแปร := expression ;

3. การทำงานตามเงื่อนไขด้วย IF statement

IF condition THEN

statements ;

[ELSIF condition THEN

statements; ]

[ELSE

statements;]

END IF;

โดย : condition คือตัวแปร Boolean หรือ expression ที่ได้ผลลัพธ์เป็นค่า Boolean

หมายเหตุ

* ELSIF เขียนติดกัน และ 1 IF จะมีกี่ ELSIF ก็ได้
* END IF เขียนเป็น 2 คำ
* IF statement มีได้อย่างมาก 1 ELSE เท่านั้น

ตัวอย่าง

IF V_date_shipped - V_date_ordered < 5 THEN

V_ship_flag := ‘Acceptable’;
ELSE V_ship_flag := ‘Unacceptable’;

END IF;
IF V_last_name = ‘Dumas’ THEN

V_job := ‘Sales Representative’;
V_region_id := 35;

END IF;

4. การทำงานแบบ loop เป็นการทำงานคำสั่งชุดเดิม หลาย ๆ รอบ loop ใน PL/SQL มี 3 แบบ

4.1 Basic loop

LOOP

statement 1;
statement 2;
...
EXIT [WHEN condition];

END LOOP;

loop ประเภทนี้เป็นการวน loop ไปเรื่อย ๆ ไม่มีกำหนด (คือทำตั้งแต่ loop จนถึง END LOOP เสร็จแล้ววนกลับขึ้นไปทำใหม่ตั้งแต่ LOOP ไปเรื่อยๆ) จึงต้องมีการเช็คเงื่อนไขในการหยุดวน LOOP

วิธีเช็คเงื่อนไข ทำได้ 2 แบบ

แบบที่ 1

IF condition THEN

EXIT;

END IF;

แบบที่ 2

EXIT WHEN condition;
หรืออาจสั่ง EXIT; โดยไม่มีเงื่อนไขเลยก็ได้

ตัวอย่าง

...
V_ord_id NUMBER := 100;
V_counter NUMBER (2) := 1;
BEGIN
LOOP

INSERT INTO ord_lines (ord_id, item_id)
VALUES (V_ord_id, V_counter);
V_counter := V_counter +1;
EXIT WHEN V_counter > 10;

END;

4.2 FOR loop

เป็นการวน loop ที่ทราบจำนวนครั้งในการทำงานที่แน่นอน

FOR index IN [REVERSE]
lower_bound..upper_bound
LOOP

statement 1;
statement 2;

END LOOP;

[REVERSE] ใช้สำหรับวน loop แบบย้อนหลัง (จาก upper_bound ลดลงทีละ 1 จนถึง lower_bound)

หมายเหตุ ไม่ต้องประกาศตัวแปร index ให้ตั้งชื่อได้เลย และโปรแกรมจะรู้จักตัวแปรที่เป็น index ภายใน loop เท่านั้น

ตัวอย่าง

...
V_ord_id NUMBER := 100;
BEGIN

FOR i IN 1..10
LOOP

INSERT INTO ord_lines (ord_id, item_id)
VALUES (V_ord-id, i);

END LOOP;

...

สังเกตได้ว่า ตัวแปร i ซึ่งทำหน้าที่เป็น index ในการวน loop เราไม่ต้องประกาศ

4.3 WHILE loop

เป็นการวน loop ตามเงื่อนไข โปรแกรมจะทำการเช็คเงื่อนไขก่อน ตราบใดที่เงื่อนไขได้ผลลัพธ์เป็น TRUE จะทำงานตาม loop ดังนั้น loop ชนิดนี้อาจ ไม่ถูกทำงานเลยก็ได้ ถ้าการเช็คเงื่อนไขในครั้งแรกเป็น FALSE

WHILE condition
LOOP

statement 1;
statement 2;

END LOOP;

ตัวอย่าง

...
V_ord_id NUMBER := 100;
V_counter NUMBER(2) := 1;
BEGIN

...
WHILE V_counter <= 10
LOOP

INSERT INTO ord_lines (ord_id, item_id)
VALUES (V_ord_id, V_counter);
V_counter := V_counter + 1;

END LOOP;

END;

การนำ PL/SQL ไปใช้งาน

1. เขียนในรูปแบบของ Anonymous Block

[DECLARE variable_declaration ;]
BEGIN

executable - code ;

END;

2. ใช้พัฒนาเป็น SubProgram สำหรับเรียกใช้ได้มี 3 ลักษณะ

2.1 PROCEDURE เป็นโปรแกรมย่อยที่ทำงานอะไรบางอย่าง โดยสามารถรับ parameter มาทำงานได้

2.2 FUNCTION เป็นโปรแกรมย่อยที่นิยมใช้ เพื่อหาค่าอะไรบางอย่าง แล้วคืน กลับมาเป็นชื่อของตัว function เอง

2.3 PACKAGE เป็นการรวบรวม PROCEDURE หรือ FUNCTION หลาย ๆ ตัว ถ้าไว้ด้วยกัน เพื่อง่ายต่อการควบคุมในแง่ privilege และเป็นหมวดหมู่ดีขึ้น

ในที่นี้ จะพูดถึงเฉพาะ PROCEDURE และ FUNCTION เท่านั้น

โครงสร้างการเขียน PROCEDURE และ FUNCTION

PROCEDURE name [ ( parameter,….)] IS pl/sql block; FUNCTION name [( parameter…)] RETURN datatype IS pl/sql block;

หมายเหตุ PL/SQL block ให้เริ่มด้วยการประกาศตัวแปร (ถ้ามี) โดยไม่ต้องมี keyword DECLARE หรือถ้าไม่ใช้ตัวแปร สามารถเริ่มด้วย BEGIN ได้เลย

วิธีการประกาศ parameters ใน Subprograms

parameter.name [IN | OUT | IN OUT] datatype [{ := | DEFAULT} expr];

parameter.name	ชื่อ parameter
mode	IN = รับค่าเข้าOUT = ส่งค่ากลับIN OUT = รับค่าเข้าและส่งค่ากลับ
datatype	ชนิดของข้อมูล ไม่ต้องระบุความยาว
[{ := | DEFAULT} expr]	ใช้ initial ค่า กรณีเป็น parameter ใน mode IN และเวลาเรียกไม่ระบุค่า parameter เข้ามา

ตัวอย่าง

PROCEDURE change_salary
(p_emp_id IN NUMBER,
p_new_salary IN NUMBER)
IS

/*variables declaration
(don’t include DECLARE)*/
BEGIN

UPDATE emp
SET salary = p_new_salary
WHERE id = p_emp_id;
COMMIT;

END;

FUNCTION tax
(p_value IN NUMBER)
RETURN NUMBER
IS
BEGIN

RETURN (p_value * .1);

END;

เปรียบเทียบ Anonymous Block, Procedure และ Function

Anonymous Block	Procedure	Function
[DECLARE การประกาศตัวแปร ] BEGIN คำสั่งต่าง ๆ END;	PROCEDURE ชื่อ (parameter(s)) IS การประกาศตัวแปร BEGIN คำสั่งต่าง ๆ END;	FUNCTION ชื่อ (parameter(s)) IS การประกาศตัวแปร BEGIN คำสั่งต่าง ๆ END;

SQL *Plus กับ PL/SQL

SQL *Plus เป็น tool ตัวหนึ่งของ Oracle มีลักษณะเป็น command line การใช้งานจะต้อง connect กับ Oracle Database ก่อน

Commands in SQL *Plus

SQL*Plus รองรับคำสั่ง 3 ประเภท ได้แก่

1. คำสั่งภาษา SQL

2. คำสั่งที่ทำงานตาม PL/SQL Block

3. คำสั่งของ SQL *Plus เอง ซึ่งจะเกี่ยวกับเรื่องของ environment ต่างๆ เช่น กำหนดขนาดจำนวนตัวอักษรต่อ 1 บรรทัด

โดยปกติเราจะใช้ SQL *Plus ในช่วงการ develop เพื่อสร้าง object ต่างๆ หรือเพื่อตรวจเช็คข้อมูล

ตัวอย่างคำสั่งของ SQL *Plus

- Set pagesize 25 คือ กำหนดให้ 1 หน้า screen มี 25 บรรทัด
- Set linesize 100 คือ กำหนดให้ 1 บรรทัดมี 100 chars
- Spool a.txt คือ เก็บสิ่งที่ display บนหน้าจอ ลง file ชื่อ a.txt ตั้งแต่จุดนี้ไปจนกระทั่งปิด spool
- Spool off คือ ปิด spool

เราสามารถใช้ SQL*Plus เป็น tools ในการเขียน Stored Procedure โดยภาษา PL/SQL ได้เช่นกัน สำหรับตัวอย่างการสร้างและ การเรียกใช้งาน Strored Procedure ท่านสามารถดูรายละเอียดได้จาก เอกสาร Oracle Stored Procedure and PHP

และหากท่านมีคำถามหรีือข้อสงสัย ขอเชิญแวะมาที่ Discussion Forums

ที่มา : http://www.exzilla.net