การเขียน Stored Procedure

รู้จักและการเขียน Stored Procedure บน SQL Server (SQL Server : Stored Procedure) ในบทความของไทยครีเอทผมอาจจะไม่ค่อยได้พูดถึง Stored Procedure ซะเท่าไหร่ เพราะในการเขียนโปรแกรมทั่ว ๆ ไป ที่ไม่ต้องการความซับซ้อนมากนัก การเขียน SQL Query ปกติก็สามารถทำงานได้ และในการเขียนโปรแกรมรุ่นใหม่ ๆ ไม่ว่าจะเป็น PHP หรือ .Net Application หรืออื่น ๆ ก็มี Framework ใหม่ ๆ ที่ออกมาช่วยจัดการ ORM(Oreited Relational Mapping) ช่วยการเขียนติดต่อกับ Database นั้นง่ายมาก ยิ่งทำให้การเขียน Stored Procedure นั้นอาจจะไม่ค่อยมีความจำเป็นซะเท่าไหร่ และโดยเฉพาะใน .Net Application ที่ได้รับความนิยมมากในปัจุบบัน ยังมี Framework ที่มีชื่อว่า Entity Framework ที่สนับสนุนการเขียน Coding ทำการ Select, Insert, Update, Delete ในรูปแบบของ Syntax ภาษานั้น ๆ บนโปรแกรมได้เลย โดยไม่ต้องสนใจที่จะเขียน SQL และยังมีประโยชน์เมื่อต้องการเปลี่ยนประเภทของ Database เช่น MySQL, Oracle ก็ไม่ต้องมานั้งทำการแก้ไขหรือเขียน Stored Procedure ใหม่

SQL Server 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 บน SQL Server Database นี้ทางทีมงานของไทยครีเอทได้คิดหัวข้อมาประมาณ 10-11 หัวข้อ ซึ่งจะสรุปเฉพาะประเด็นและใจความสำคัญของการเขียน Stored ให้สามารถนำไปใช้งานได้จริง โดยได้ยกตัวอย่างการสร้าง Table ประกอบขึ้นมา 2 ตารางคือ CUSTOMER และ COUNTRY ตามโครงสร้างดังนี้

Table : CUSTOMER

CREATE TABLE [CUSTOMER](
[CUSTOMER_ID] [varchar](4) NOT NULL,
[NAME] [varchar](50) NULL,
[EMAIL] [varchar](50) NULL,
[COUNTRY_CODE] [varchar](2) NULL,
[BUDGET] [decimal](18, 2) NULL,
[USED] [decimal](18, 2) NULL,
CONSTRAINT [PK2_CUSTOMER] PRIMARY KEY CLUSTERED
([CUSTOMER_ID] ASC) ) ON [PRIMARY]
INSERT INTO CUSTOMER VALUES ('C001', 'Win Weerachai', 'win.weerachai@thaicreate.com', 'TH', 1000000, 600000);
INSERT INTO CUSTOMER VALUES ('C002', 'John  Smith', 'john.smith@thaicreate.com', 'UK', 2000000, 800000);
INSERT INTO CUSTOMER VALUES ('C003', 'Jame Born', 'jame.born@thaicreate.com', 'US', 3000000, 600000);
INSERT INTO CUSTOMER VALUES ('C004', 'Chalee Angel', 'chalee.angel@thaicreate.com', 'US', 4000000, 100000);

Stored Procedure บน SQL Server

Table : COUNTRY

CREATE TABLE [COUNTRY](
[COUNTRY_CODE] [varchar](2) NOT NULL,
[COUNTRY_NAME] [varchar](100) NULL,
CONSTRAINT [PK_COUNTRY] PRIMARY KEY CLUSTERED
([COUNTRY_CODE] ASC)) ON [PRIMARY]
INSERT INTO COUNTRY VALUES ('TH', 'Thailand  ');
INSERT INTO COUNTRY VALUES ('UK', 'England');
INSERT INTO COUNTRY VALUES ('US', 'United States');

Stored Procedure บน SQL Server

สามารถนำ SQL Statement นี้ไปรันเพื่อสร้าง Table และ Database บน Query ได้เลย

ตัวอย่างการสร้าง Stored Procedure รองรับ SQL Server บน Version 2005,2008,2012 และสูงกว่า

Stored Procedure บน SQL Server

หลังจากที่สร้าง Table และ Insert ตัวข้อมูลเรียบร้อยแล้ว เราจะเห็นชื่อ Table อยู่ภายใต้ Tables

Stored Procedure บน SQL Server

ในการสร้าง Stored Procedure จะอยู่ภายใต้ Programmability -> Stored Procedure ให้คลิกขวาเลือก New Stored Procedure

-- ================================================
-- Template generated from Template Explorer using:
-- Create Procedure (New Menu).SQL
--
-- Use the Specify Values for Template Parameters
-- command (Ctrl-Shift-M) to fill in the parameter
-- values below.
--
-- This block of comments will not be included in
-- the definition of the procedure.
-- ================================================
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:      <Author,,Name>
-- Create date: <Create Date,,>
-- Description: <Description,,>
-- =============================================
CREATE PROCEDURE <Procedure_Name, sysname, ProcedureName>
-- Add the parameters for the stored procedure here
<@Param1, sysname, @p1> <Datatype_For_Param1, , int> = <Default_Value_For_Param1, , 0>,
<@Param2, sysname, @p2> <Datatype_For_Param2, , int> = <Default_Value_For_Param2, , 0>
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;
 
-- Insert statements for procedure here
SELECT <@Param1, sysname, @p1>, <@Param2, sysname, @p2>
END
GO

หลังจากที่เลือก Create จะเห้นว่าค่า Default ที่มาให้นั้นค่อนข้างจะครบและสมบูรณ์ แต่จุดสำคัญ ๆ มีอยู่ไม่กี่บรรทัดเท่านั้น เราสามารถเขียนต่อได้จากคำสั่งนี้ หรือจะเขียนขึ้นมาใหม่ก็ได้

1.CREATE PROCEDURE myStoredProcedure
2.AS
3.SELECT * FROM CUSTOMER
4.GO

นี่เป็นตัวอย่างการสร้าง Stored Procedure ชื่อว่า myStoredProcedure ทำหน้าที่ SELECT ข้อมูลจาก CUSTOMER

Stored Procedure บน SQL Server

ขั้นตอนการสร้างให้เลือก Execute

Note!! ในการ Execute ครั้งที่ 2 จะต้องเปลี่ยนจาก CREATE เป็น ALTER หรือในกรณีที่เลือก Mode แก้ไข จะขึ้นคำสั่ง ALTER ให้อัตโนมัติ

Stored Procedure บน SQL Server

หลังจากที่สร้าง Stored Procedure เรียบร้อยแล้ว รายการจะแสดงไปยัง Programmability -> Stored Procedure

Stored Procedure บน SQL Server

กรณีที่ต้องการแก้ไข Stored Procedure ให้เลือก Modify ซึ่งจะแสดงรายการแก้ไข Stored Procedure

USE [mydatabase]
GO
/****** Object:  StoredProcedure [dbo].[myStoredProcedure]    Script Date: 11-Sep-15 3:29:52 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
 
ALTER PROCEDURE [dbo].[myStoredProcedure]
AS
SELECT * FROM CUSTOMER

จะเห็นว่าในการแก้ไขจะขึ้นคำสั่ง ALTER ให้อัตโนมัติ

การเรียกใช้งาน Stored Procedure

Stored Procedure บน SQL Server

สามารถเลือก New Query ตามด้วยคำสั่ง

1.EXEC myStoredProcedure

Stored Procedure บน SQL Server

เลือก Excute จะแสดงรายการ Result ที่ได้จาก Stored Procedures

บทความจาก http://www.thaicreate.com/
ข้อมูลเพิ่มเติม http://www.thaicreate.com/tutorial/sqlserver-stored-procedure.html

You May Also Like

About the Author: golderboy