ตอนที่ 5 : รู้จัก Stored Procedure บน Oracle การรับส่ง Parameters (Oracle : Stored Procedure) |
ตอนที่ 5 : รู้จัก Stored Procedure บน Oracle การรับส่ง Parameters (Oracle : Stored Procedure) บทความในประเทศไทยที่เกี่ยวกับ Oracle Database นั้นค่อนข้างจะมีน้อยมาก ส่วนหนึ่งเพราะ Oracle Database จะได้รับความนิยมในโปรเจคขนาดใหญ่ ๆ การใช้งานก็จะน้อยตามไปด้วย ฉะนั้นเราจึงจะเห็นบทความมีอยู่แค่ไม่กี่เว็บเท่านั้น และในบทความของไทยครีเอทผมก็ยังไม่เคยได้พูดถึง Stored Procedure ซะเท่าไหร่ เพราะในการเขียนโปรแกรมทั่ว ๆ ไป ที่ไม่ต้องการความซับซ้อนมากนัก การเขียน SQL Query ปกติก็สามารถทำงานได้ และในการเขียนโปรแกรมรุ่นใหม่ ๆ ไม่ว่าจะเป็น PHP หรือ .Net Application หรืออื่น ๆ ก็มี Framework ใหม่ ๆ ที่ออกมาช่วยจัดการ ORM(Oreited Relational Mapping) ช่วยการเขียนติดต่อกับ Database นั้นง่ายมาก ยิ่งทำให้การเขียน Stored Procedure นั้นอาจจะไม่ค่อยมีความจำเป็นซะเท่าไหร่ และโดยเฉพาะใน .Net Application ที่ได้รับความนิยมมากในปัจุบัน ยังมี Framework ที่จัดการกับ Database อย่างเช่น LINQ to SQL , LINQ to Entities หรือ Entity Framework Model ที่สนับสนุนการเขียน Coding ทำการ Select, Insert, Update, Delete ในรูปแบบของ Syntax ภาษานั้น ๆ บนโปรแกรมได้เลย โดยไม่ต้องสนใจที่จะเขียน SQL และยังมีประโยชน์เมื่อต้องการเปลี่ยนประเภทของ Database เช่น MySQL, SQL Server ก็ไม่ต้องมานั้งทำการแก้ไขหรือเขียน Stored Procedure ใหม่
Oracle Database Server
การเขียน Stored Procedure จะจำเป็นมากในการพัฒนาระบบขนาดใหญ่ ที่มีการจัดเก็บข้อมูลและประมวลผลจำนวนมาก ๆ เพราะการเขียน Query ในปกติ ที่มีการติดต่อกับข้อมูลหลาย ๆ ครั้ง เราจะต้อง Select แล้วใช้โปรแกรมอ่านค่า เมื่ออ่านได้ค่าแล้วค่อยส่งไปประมวลผลที่ Database ซ้ำ ๆ ซึ่งจะเป็นการทำงานซ้ำซ้อน มีการรับส่งระหว่าง Application กับ Database เป็นสิบหรือหลายร้อยครั้ง ซึ่งผลที่ตามมาคือ Performance ของโปรแกรมจะทำงานช้ามาก ทางเลือกในการแก้ปํยหานี้ก็คือ เราใช้การทำงานซ้ำซ้อนทั้งหมดนี้ที่ Database แทน โดยเราเพียงส่งค่า Parameters ที่จำเป็นต้องใช้ จากนั้นบน Database ก็จะนำค่า Parameters ที่ส่งไปนั้น ทำงานตามคำสั่งต่าง ๆ บน Stored Procedure ที่เราเขียนขึ้น เมื่อได้ค่าที่ต้องการค่อยส่งค่า Result กลับมายังโปรแกรม วิธีนี้จะเป็นการเพิ่มประสิทธิภาพการทำงานของโปรแกรมให้ทำงานเร็วขึ้น และลด Traffic ระหว่าง Database กับ Application ได้สูงมาก
ข้อดีการใช้ Stored Procedure- Syntax เขียนง่าย เข้าใจง่าย และในปัจจุบันมี Tools ที่ช่วยให้การ Debug ง่ายมาก
- เพิ่มประสิทธิภาพการทำงานการ Query Database ได้อย่างดีเยี่ยม ลดภาระการทำงานของ Application
- ลด Traffic ของ Network หรือระหว่าง Database กับ Application
- การพัฒนาค่อนข้างจะเป็นระบบ แยกระหว่าง Application Logic กับ Database Logic ได้ชัดเจน เช่น ถ้าต้องการแก้ไข Logic ของ Database อาจจะเพียงแก้ไขที่ Stored Procedure เท่านั้น
ข้อเสียการใช้ Stored Procedure- การเขียน Stored จะภูกกับ Database นั้น ๆ เมื่อเปลี่ยน Database ไปใช้ตัวอื่น จะต้องเขียน Stored ใหม่ทั้งหมด
- Syntax ของการเขียน Stored Procedure จะไม่เหมือนกัน
- เมื่อนำไปใช้บน Production Server การ Debug ตรวจสอบหาข้อผิดพลาดทำได้ยากพอสมควร
สำหรับในบทความหัวข้อ Stored Procedure บน Oracle Database นี้ทางทีมงานของไทยครีเอทได้คิดหัวข้อมาประมาณ 20-21 หัวข้อ ซึ่งจะสรุปเฉพาะประเด็นและใจความสำคัญของการเขียน Stored ให้สามารถนำไปใช้งานได้จริง โดยได้ยกตัวอย่างการสร้าง Table ประกอบขึ้นมา 3 ตารางคือ CUSTOMER, COUNTRY และ AUDIT_LOG ตามโครงสร้างดังนี้
Table : CUSTOMER
CREATE TABLE "CUSTOMER"
( "CUSTOMER_ID" VARCHAR2(4 BYTE) NOT NULL ENABLE,
"NAME" VARCHAR2(50 BYTE),
"EMAIL" VARCHAR2(50 BYTE),
"COUNTRY_CODE" VARCHAR2(2 BYTE),
"BUDGET" NUMBER(18,2),
"USED" NUMBER(18,2),
CONSTRAINT "CUSTOMER_PK" PRIMARY KEY ("CUSTOMER_ID")
USING INDEX PCTFREE 10 INITRANS 2 MAXTRANS 255 NOCOMPRESS LOGGING
TABLESPACE "SYSTEM" ENABLE
) SEGMENT CREATION DEFERRED
PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 NOCOMPRESS LOGGING
TABLESPACE "SYSTEM" ;
INSERT INTO CUSTOMER (CUSTOMER_ID, NAME, EMAIL, COUNTRY_CODE, BUDGET, USED) VALUES ('C001', 'Win Weerachai', '[email protected]', 'TH', 1000000.00, 600000.00);
INSERT INTO CUSTOMER (CUSTOMER_ID, NAME, EMAIL, COUNTRY_CODE, BUDGET, USED) VALUES ('C002', 'John Smith', '[email protected]', 'UK', 2000000.00, 800000.00);
INSERT INTO CUSTOMER (CUSTOMER_ID, NAME, EMAIL, COUNTRY_CODE, BUDGET, USED) VALUES ('C003', 'Jame Born', '[email protected]', 'US', 3000000.00, 600000.00);
INSERT INTO CUSTOMER (CUSTOMER_ID, NAME, EMAIL, COUNTRY_CODE, BUDGET, USED) VALUES ('C004', 'Chalee Angel', '[email protected]', 'US', 4000000.00, 100000.00);


Table : COUNTRY
CREATE TABLE "COUNTRY"
( "COUNTRY_CODE" VARCHAR2(2 BYTE) NOT NULL ENABLE,
"COUNTRY_NAME" VARCHAR2(50 BYTE),
CONSTRAINT "COUNTRY_PK" PRIMARY KEY ("COUNTRY_CODE")
USING INDEX PCTFREE 10 INITRANS 2 MAXTRANS 255 NOCOMPRESS LOGGING
TABLESPACE "SYSTEM" ENABLE
) SEGMENT CREATION DEFERRED
PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 NOCOMPRESS LOGGING
TABLESPACE "SYSTEM" ;
INSERT INTO COUNTRY (COUNTRY_CODE, COUNTRY_NAME) VALUES ('TH', 'Thailand');
INSERT INTO COUNTRY (COUNTRY_CODE, COUNTRY_NAME) VALUES ('UK', 'United Kingdom');
INSERT INTO COUNTRY (COUNTRY_CODE, COUNTRY_NAME) VALUES ('US', 'United States');

Table : AUDIT_LOG
CREATE TABLE "AUDIT_LOG"
( "AUDIT_ID" NUMBER(*,0) NOT NULL ENABLE,
"CUSTOMER_ID" VARCHAR2(4 BYTE),
"LOG_DATE" DATE,
"USED" NUMBER(18,2),
CONSTRAINT "AUDIT_LOG_PK" PRIMARY KEY ("AUDIT_ID")
USING INDEX PCTFREE 10 INITRANS 2 MAXTRANS 255 NOCOMPRESS LOGGING
TABLESPACE "SYSTEM" ENABLE
) SEGMENT CREATION DEFERRED
PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 NOCOMPRESS LOGGING
TABLESPACE "SYSTEM" ;
INSERT INTO AUDIT_LOG (AUDIT_ID, CUSTOMER_ID,LOG_DATE,USED) VALUES ('1', 'C001', to_date('01-AUG-15','DD-MON-RR'), '100000.00');
INSERT INTO AUDIT_LOG (AUDIT_ID, CUSTOMER_ID,LOG_DATE,USED) VALUES ('2', 'C001', to_date('05-AUG-15','DD-MON-RR'), '200000.00');
INSERT INTO AUDIT_LOG (AUDIT_ID, CUSTOMER_ID,LOG_DATE,USED) VALUES ('3', 'C001', to_date('10-AUG-15','DD-MON-RR'), '300000.00');
INSERT INTO AUDIT_LOG (AUDIT_ID, CUSTOMER_ID,LOG_DATE,USED) VALUES ('4', 'C002', to_date('02-AUG-15','DD-MON-RR'), '400000.00');
INSERT INTO AUDIT_LOG (AUDIT_ID, CUSTOMER_ID,LOG_DATE,USED) VALUES ('5', 'C002', to_date('07-AUG-15','DD-MON-RR'), '100000.00');
INSERT INTO AUDIT_LOG (AUDIT_ID, CUSTOMER_ID,LOG_DATE,USED) VALUES ('6', 'C002', to_date('15-AUG-15','DD-MON-RR'), '300000.00');
INSERT INTO AUDIT_LOG (AUDIT_ID, CUSTOMER_ID,LOG_DATE,USED) VALUES ('7', 'C003', to_date('20-AUG-15','DD-MON-RR'), '400000.00');
INSERT INTO AUDIT_LOG (AUDIT_ID, CUSTOMER_ID,LOG_DATE,USED) VALUES ('8', 'C003', to_date('25-AUG-15','DD-MON-RR'), '200000.00');
INSERT INTO AUDIT_LOG (AUDIT_ID, CUSTOMER_ID,LOG_DATE,USED) VALUES ('9', 'C004', to_date('04-AUG-15','DD-MON-RR'), '100000.00');

สามารถนำ SQL Statement นี้ไปรันเพื่อสร้าง Table และ Database บน Query ได้เลย
ตัวอย่างการสร้าง Stored Procedure รองรับ Oracle บน Version 9,10,11,12 และสูงกว่า

Flow ของการสร้าง Stored Procedure บน Oracle Database ซึ่งขั้นตอนการสร้าง Stored Procedure สามารถสร้างได้จากทั้งผ่าน Query และการใช้ผ่าน Tool ของ Oracle SQL Developer
Example 1 : การสร้าง Stored Procedure แบบง่าย ๆ โดยแสดงข้อความกลับไปยัง Call
CREATE OR REPLACE PROCEDURE MY_STORED_PROCEDURE
AS
BEGIN
DBMS_OUTPUT.PUT_LINE('Hello World! ThaiCreate.Com');
END;
คำสั่งในการสร้าง Stored Procedure แบบง่าย ๆ โดยชื่อว่า MY_STORED_PROCEDURE จะแสดง Output คำว่า "Hello World! ThaiCreate.Com"
คำสั่ง DBMS_OUTPUT.PUT_LINE เป็นคำสั่งแสดง Output ในฝั่ง Database คล้ายกับ PRINT ของ SQL Server ซึ่งตัวที่ Call จะไม่สามารถเรียกใช้งานกลัผ่านตัวแปร OUT ได้

การรัน Query เพื่อสร้าง Stored Procedure

รายการ Stored Procedure ที่ถูกสร้าง
การเรียก Stored Procedure

ในขั้นแรกให้เปิดใช้งาน Dbms Output โดยไปที่เมนู Tool -> Dbms Output

เลือก Connect (เครื่องหมาย บวก)
EXEC MY_STORED_PROCEDURE();

การเรียกใช้งาน Stored Procedure และ Output ที่โปรแกรมที่แสดงผล
Note!! ในกาเขียน Stored Procedure บน Oracle Database จะไม่สามารถส่งค่า Result ผ่านการ SELECT ได้เหมือนกับ SQL Server หรือ MySQL ฉะนั้นค่าตัวแปรที่จะถูกส่งออกไปให้กับตัว Call จะต้องผ่าน Parameters แบบ OUT เท่านั้น
Example 2 : การสร้าง Stored Procedure ทำการ Select ข้อมูลจาก Table และแสดงผลทาง Output
CREATE OR REPLACE PROCEDURE GET_CUSTOMER
AS
BEGIN
FOR CusInfo IN (SELECT * FROM CUSTOMER)
LOOP
DBMS_OUTPUT.PUT_LINE(CusInfo.CUSTOMER_ID || ' ' || CusInfo.NAME || ' '
|| CusInfo.EMAIL || ' ' || CusInfo.COUNTRY_CODE || ' ' || CusInfo.BUDGET || ' ' || CusInfo.USED);
END LOOP;
END;
เขียนได้อีกวิธีคือ
CREATE OR REPLACE PROCEDURE GET_CUSTOMER
AS
CURSOR CusCuror IS SELECT * FROM CUSTOMER;
BEGIN
FOR CusInfo IN CusCuror
LOOP
DBMS_OUTPUT.PUT_LINE(CusInfo.CUSTOMER_ID || ' ' || CusInfo.NAME || ' '
|| CusInfo.EMAIL || ' ' || CusInfo.COUNTRY_CODE || ' ' || CusInfo.BUDGET || ' ' || CusInfo.USED);
END LOOP;
END;
จาก Stored Procedure นี้จะทำการ Select ข้อมูลมาจาก Table : CUSTOMER แลัวทำการ Loop เพื่อแสดงผลออกทางหน้าจอ
Call
EXEC GET_CUSTOMER();

Example 3 : การสร้าง Stored Procedure แบบมี Parameters และส่งค่า OUT กลับไปยังตัว Call
CREATE OR REPLACE PROCEDURE GET_CUSTOMER(pCustomer OUT SYS_REFCURSOR)
AS
BEGIN
OPEN pCustomer FOR
SELECT *
FROM CUSTOMER;
END;
จาก Code นี้จะมีการส่งค่าตัวแปร pCustomer ซึ่งเป็น OUT ชนิด SYS_REFCURSOR (ในรูปแบบของ Cursor , Recordset) ซึ่งตัวที่ Call สามารถนำค่านี้ไปใช้งานได้ เช่นบนโปรแกรม .NET , Java , PHP เป็นต้น
Call
VAR pCustomer REFCURSOR;
EXEC GET_CUSTOMER(:pCustomer)
PRINT pCustomer;

Example 4 : การสร้าง Stored Procedure แบบมี Parameters มากกว่า 1 ค่า และส่งค่า OUT กลับไปยังตัว Call
CREATE OR REPLACE PROCEDURE GET_CUSTOMER
(pCountryCode IN VARCHAR2, pCustomer OUT SYS_REFCURSOR)
AS
BEGIN
OPEN pCustomer FOR
SELECT *
FROM CUSTOMER WHERE COUNTRY_CODE = pCountryCode;
END;
จาก Code นี้จะมีการรับค่า pCountryCode เป็น IN และส่งค่าตัวแปร pCustomer ซึ่งเป็น OUT ชนิด SYS_REFCURSOR (ในรูปแบบของ Cursor , Recordset) ซึ่งตัวที่ Call สามารถนำค่านี้ไปใช้งานได้ เช่นบนโปรแกรม .NET , Java , PHP เป็นต้น
Call
VAR pCustomer REFCURSOR;
EXEC GET_CUSTOMER('US',:pCustomer)
PRINT pCustomer;
|