เครดิต : http://it.zomzaa.com/oracle/pl/sql/pl/sql.html
PL/SQL
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 ได้แก่ …
Exception … Error Raised if
CURSOR_ALREADY_OPEN ORA-06511 you try to OPEN an already open cursor; you must CLOSE a cursor before you can reOPEN it
DUP_VAL_ON_INDEX ORA-00001 you try to INSERT or UPDATE Duplicate values in a UNIQUE database column
INVALID_CURSOR ORA-01001 you try an illegal cursor operation Such as closing an unopened cursor
INVALID_NUMBER ORA-01722 the conversion of a character String to a number fails in a SQL statement
LOGIN_DENIED ORA-01017 you log on to oracle with an Invalid username/password
NO_DATA_FOUND ORA-01403 a SELECT INTO returns no rows, Or you refer to an unintialized Row in a PL/SQL table
NOT_LOGGED_ON ORA-01012 your PL/SQL program issues a Database call without being Logged on to oracle
PROGRAM_ERROR ORA-06501 PL/SQL has an internal problem Such as exiting a function that Has no RETURN statement
STORAGE_ERROR ORA-06500 PL/SQL runs out of memory or Memory is corrupted
TIMEOUT_ON_RESOURCE ORA-00051 a timeout occurs while oracle is Waiting for resource
TOO_MANY_ROWS ORA-01422 a SELECT INTO returns more Than one row
TRANSACTION_BACKED_OUT ORA-00061 the remote part of a transaction is Rolled back because oracle data Might be inconsistent at some Nodes
VALUE_ERROR ORA-06502 the conversion of a character String to a number fails in a procedural statement, or an arithmetic, conversion, truncation, or constraint error occurs
ตัวอย่าง :
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;
*****************
ขอขอบคุณเจ้าของความคิด
เครดิต : http://it.zomzaa.com/oracle/pl/sql/pl/sql.html
1 ความคิดเห็น:
ขอบคุณมากเลยอ่ะ สั้นเข้าใจง่าย
แสดงความคิดเห็น