ตอนที่ 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);
![Stored Procedure บน Oracle Database Stored Procedure บน Oracle Database](https://www.thaicreate.com/upload/tutorial/oracle-create-database-11.jpg?v=1001)
![](/images/adv.jpg)
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');
![Stored Procedure บน Oracle Database Stored Procedure บน Oracle Database](https://www.thaicreate.com/upload/tutorial/oracle-create-database-13.jpg?v=1001)
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');
![Stored Procedure บน Oracle Database Stored Procedure บน Oracle Database](https://www.thaicreate.com/upload/tutorial/oracle-create-database-15.jpg?v=1001)
สามารถนำ SQL Statement นี้ไปรันเพื่อสร้าง Table และ Database บน Query ได้เลย
ตัวอย่างการสร้าง Stored Procedure รองรับ Oracle บน Version 9,10,11,12 และสูงกว่า
![Stored Procedure บน Oracle Stored Procedure บน Oracle](https://www.thaicreate.com/upload/tutorial/oracle-stored-procedure-intro-01.jpg?v=1001)
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 ได้
![Stored Procedure บน Oracle Stored Procedure บน Oracle](https://www.thaicreate.com/upload/tutorial/oracle-stored-procedure-intro-02.jpg?v=1001)
การรัน Query เพื่อสร้าง Stored Procedure
![Stored Procedure บน Oracle Stored Procedure บน Oracle](https://www.thaicreate.com/upload/tutorial/oracle-stored-procedure-intro-03.jpg?v=1001)
รายการ Stored Procedure ที่ถูกสร้าง
การเรียก Stored Procedure
![Stored Procedure บน Oracle Stored Procedure บน Oracle](https://www.thaicreate.com/upload/tutorial/oracle-stored-procedure-intro-04.jpg?v=1001)
ในขั้นแรกให้เปิดใช้งาน Dbms Output โดยไปที่เมนู Tool -> Dbms Output
![Stored Procedure บน Oracle Stored Procedure บน Oracle](https://www.thaicreate.com/upload/tutorial/oracle-stored-procedure-intro-05.jpg?v=1001)
เลือก Connect (เครื่องหมาย บวก)
EXEC MY_STORED_PROCEDURE();
![Stored Procedure บน Oracle Stored Procedure บน Oracle](https://www.thaicreate.com/upload/tutorial/oracle-stored-procedure-intro-06.jpg?v=1001)
การเรียกใช้งาน 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();
![Stored Procedure บน Oracle Stored Procedure บน Oracle](https://www.thaicreate.com/upload/tutorial/oracle-stored-procedure-intro-07.jpg?v=1001)
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;
![Stored Procedure บน Oracle Stored Procedure บน Oracle](https://www.thaicreate.com/upload/tutorial/oracle-stored-procedure-intro-08.jpg?v=1001)
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;
|