วันพฤหัสบดีที่ 25 ตุลาคม พ.ศ. 2555

การเขียน store procedure


การเขียน store procedure บน ms sql server ตอนที่ 1


Store Procedure คืออะไร?
     หลายคนถามว่า Store Procedure คืออะไร? ผมก็จะขอนิยามความหมายซักนิดนะครับ Store Procedure นั้นเป็นโปรแกรมที่เก็บอยู่ในฐานข้อมูลนั่นเอง ซึ่งการจะเขียน Store procedure นั้นเราจะต้องดูด้วยว่า โปรแกรมฐานข้อมูล ที่เราใช้อยู่นั้นมี ส่วนของ Store procedure อยู่ด้วยหรือเปล่า ส่วนใหญ่จะมีกับฐานข้อมูลใหญ่ เช่น Oracle , Ms sql server , Mysql เวอร์ชั่นใหม่ๆ และอื่นๆ

ทำไมเราถึงจำเป็นต้องใช้ Store Procedure 
     ถามว่าทำไมต้องใช้ อันนี้ที่จริงแล้ว ถ้าไม่ใช้ เราก็ยังสามารถเขียนโปรแกรม ติดต่อดาต้าเบสดึงข้อมูลได้เหมือนกัน ถ้าเรา ใช้คำสั่ง select * from table ดึงข้อมูลเพียง table เดียวคำสั่งสั้นๆ คงไม่เห็นถึงความแตกต่างเท่าไหร่ แต่เมื่อไหร่ ที่ต้องมีการทำงาน ในหลายๆ table พร้อมๆกัน ซัก 4-5 table เราต้อง join table อีกเพียบ หรืออาจจะมี sub query อยู่ด้วย ถ้าเมื่อไหร่ เราต้องทำงานแบบนี้ การส่งคำสั่ง Query ปริมาณมหาศาล ทำให้เกิด Traffic ระหว่าง application กับ database ขนาดใหญ่มาก และส่งผลให้ โปรแกรมเราทำงานช้าจนน่าเกลียดเลยทีเดียว 

แต่หากเราใช้ store procedure ในการดึงข้อมูล จะลดปัญหา Traffic ไปได้มาก เนื่องจาก Store Procedure นั้นเป็น Database object ที่จะเก็บในรูปแบบ Compile แล้วและมีการทำงานแบบ Sql Statement เรียบร้อย การทำงานจะตกอยู่กับ database server ดังนั้นเราจึงสามารถใช้คำสั่ง Query ได้หลายตัวพร้อมๆกัน เพื่อให้ได้ผลลัพธ์ตามที่เราต้องการ จึงทำให้ performance ของโปรแกรมเราดีขึ้นอย่างมากมาย

การเขียน Store procedure ใน Ms Sql Server นั้นเราจะใช้ภาษา T-sql ในการเขียน ซึ่งจิงๆแล้วก็เหมือนกันเขียน Query พวก select , insert , update , delete นั่นแหละครับ เพียงแต่ T-sql นั้นมี Control Structure พวก If , Case When , While และอื่นๆอยู่ด้วยนะครับ ทำให้เราสามารถเขียนโปรแกรมบนฐานข้อมูลได้สะดวกมากมายยิ่งขึ้น

ประเภทของ Store Procedure นั้นมี 3 ประเภทคือ
1. System Store Procedure คือ Store Procedure ของระบบ เช่น sp_helpdb ใช้เพื่อดูรายละเอียดของฐานข้อมูล เป็น Store procedure ที่มีอยู่แล้วในระบบ
2. Extended Store Procedure เช่น xp_cmdshell
3. User Store Procedure คือ Store procedure ที่เราสร้างขึ้นมาใช้งานเอง

ในที่นี้ผมจะขออ้างอิง ฐานข้อมูล Ms Sql Server นะครับเนื่องจากเป็นฐานข้อมูลที่ผมถนัด และที่เราจะอธิบายคือการ สร้าง User Store procedure 
ขั้นแรก เราต้องเตรียมฐานข้อมูลและสร้าง ตารางตัวอย่างพร้อมข้อมูลไว้สำหรับการทดสอบนะครับ ให้ใช้คำสั่ง Sql ด้านล่างนี้ในการสร้าง table และ ข้อมูลทดสอบ
สร้าง ตาราง และข้อมูลที่ใช้ในตัวอย่าง
  1. -- สร้าง table tbl_department ทดสอบ
  2. CREATE TABLE [dbo].[tbl_department](
  3. [dep_code] [varchar](2) COLLATE Thai_CI_AS NOT NULL,
  4. [dep_name] [varchar](50) COLLATE Thai_CI_AS NULL,
  5. CONSTRAINT [PK_tbl_department] PRIMARY KEY CLUSTERED 
  6. (
  7. [dep_code] ASC
  8. )WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY]
  9. ) ON [PRIMARY]
  10. -- เพิ่มข้อมูลทดสอบใน tbl_department 
  11. insert into tbl_department values ('01','Account')
  12. insert into tbl_department values ('02','Marketing')
  13. insert into tbl_department values ('03','Information Technology')

  15. -- สร้าง table tbl_employee
  16. CREATE TABLE [dbo].[tbl_employee](
  17. [emp_code] [varchar](5) COLLATE Thai_CI_AS NOT NULL,
  18. [emp_name] [varchar](50) COLLATE Thai_CI_AS NULL,
  19. [emp_surname] [varchar](50) COLLATE Thai_CI_AS NULL,
  20. [emp_tel] [varchar](20) COLLATE Thai_CI_AS NULL,
  21. [emp_email] [varchar](60) COLLATE Thai_CI_AS NULL,
  22. [dep_code] [varchar](2) COLLATE Thai_CI_AS NULL,
  23. CONSTRAINT [PK_tbl_employee] PRIMARY KEY CLUSTERED 
  24. (
  25. [emp_code] ASC
  26. )WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY]
  27. ) ON [PRIMARY]
  28. -- เพิ่มข้อมูลทดสอบใน tbl_employee
  29. insert into tbl_employee values ('E0001','สราวุธ','จงเจริญมั่นคง','081627xxxx','<?xml:namespace prefix = st1 ns = "urn:schemas-microsoft-com:office:smarttags" />nuijang20@hotmail.com','03')
  30. insert into tbl_employee values ('E0002','สุรเดช','ศรีเจริญ','081761xxxx','deth@hotmail.com','01')
  31. insert into tbl_employee values ('E0003','หิรัญ','ศรีสุพรรณ','081665xxxx','hirun@hotmail.com','02')
  32. insert into tbl_employee values ('E0004','อ่อนนุช','ชื่อซอยจ๊ะ','081555xxxx','onnuch@hotmail.com','02')
  33. insert into tbl_employee values ('E0005','บางนา','ชื่อถนนจ๊ะ','081222xxxx','bangna@hotmail.com','02')
คัดลอกไปที่คลิปบอร์ด
เมื่อเตรียมข้อมูลเรียบร้อยแล้วทีนี้ก็จะเข้าสู่การเขียน Store procedure ในตอนต่อไปนะครับ

การเขียน store procedure บน ms sql server ตอนที่ 2 รูปแบบต่างๆของ store procedure


บทความตอนที่ 2 นี้จะเป็นจะขอกล่าวถึงรูปแบบของ Store Procedure ในแบบต่างๆ ซึ่ง store procedure นั้นมีหลายๆรูปแบบ ผมจะขอจำแนกออกเป็นดังนี้ 
1. Stored procedures แบบทั่วๆไป2. Stored Procedures แบบรับ Parameter
     2.1 Stored Procedures แบบกำหนดค่าเริ่มต้นให้ Parameter
     2.2 Stored Procedures แบบ Output Parameter
3. Stored Procedures แบบ Return ค่ากลับ

1. มาดูตัวอย่างเริ่มต้นการเขียน Store Procedure แบบทั่วๆไป กันก่อนนะครับ
  1. -- การสร้าง Store procedure แบบปกติ
  2. Create Procedure ชื่อ Store Procedure 
  3. As
  4. BEGIN
  5. คำสั่ง Sql Statement
  6. END
คัดลอกไปที่คลิปบอร์ด
ตัวอย่างเช่น
  1. Create Procedure sp_fullemployee
  2. As
  3. BEGIN

  5. select 
  6. d.dep_code,d.dep_name,e.emp_code,e.emp_name,e.emp_surname,e.emp_tel,e.emp_email
  7. from tbl_department d left join tbl_employee e on e.dep_code=d.dep_code
  8. END

  10. -- การเรียกใช้ Store ที่สร้างขึ้นนั้นเราจะใช้คำสั่ง execute ดังนี้
  11. EXEC fullemployee

  13. ผลลัพธ์ที่ได้คือ
  14. 01 Account E0002 สุรเดช ศรีเจริญ 081761xxxx deth@hotmail.com
  15. 02 Marketing E0003 หิรัญ ศรีสุพรรณ 081665xxxx hirun@hotmail.com
  16. 02 Marketing E0004 อ่อนนุช ชื่อซอยจ๊ะ 081555xxxx onnuch@hotmail.com
  17. 02 Marketing E0005 บางนา ชื่อถนนจ๊ะ 081222xxxx bangna@hotmail.com
  18. 03 Information Technology E0001 สราวุธ จงเจริญมั่นคง 081627xxxx nuijang20@hotmail.com
คัดลอกไปที่คลิปบอร์ด

การเขียน store procedure บน ms sql server ตอนที่ 3 store procedure แบบที่มีการรับค่า parameter


จากบทความตอนที่แล้วที่ได้กล่าวถึง รูปแบบ store procedure และตัวอย่างการเขียน store procedure แบบทั่วๆไป
สำหรับบทความในตอนที่ 3 นี้จะเป็นการเขียน store procedure แบบที่มีการรับค่า parameter นะครับ
มาดูตัวอย่างเริ่มต้นการเขียน Store Procedure แบบที่มีการรับค่า parameter กันนะครับ

  2. -- การสร้าง Stored Procedure แบบรับค่าParameter
  3. Create Procedure sp_fullemployee_by_emp_code (
  4.    @parameter1 datatype ,
  5.    @parameter2 datatype ,
  6.    @parameter3 datatype ,
  7.    …
  8. )
  9. As
  10. BEGIN
  11. คำสั่ง Sql Statement
  12. END
คัดลอกไปที่คลิปบอร์ด
ตัวอย่างการสร้าง Store procedure แบบมี parameter นะครับ จะเป็นการสร้าง store procedure สำหรับ แสดงข้อมูล employee แบบที่มีการใส่เงื่อนไข ตาม emp_code ดังโคดด้านล่าง

  2. create procedure sp_fullemployee_by_emp_code (
  3.                 @emp_code varchar(5)
  4. ) 
  5. AS
  6. BEGIN
  7. select 
  8. d.dep_code,d.dep_name,e.emp_code,e.emp_name,e.emp_surname,e.emp_tel,e.emp_email
  9. from tbl_department d left join tbl_employee e on e.dep_code=d.dep_code
  10. where e.emp_code=@emp_code
  11. END
  12. -- การเรียกใช้งาน Stored procedures แบบรับค่า parameter 
  13. exec sp_fullemployee_by_emp_code 'E0002'
  14. -- หรือ ส่งค่าให้กับ parameter ที่ต้องการ โดยการกำหนดชื่อดังเช่นตัวอย่างด้านล่างก็ได้ ตัวอย่างนี้จะใช้บ่อยๆ ในกรณี สร้าง Store procedure แบบกำหนดค่าเริ่มต้นให้กับ parameter 
  15. exec sp_fullemployee_by_emp_code @emp_code='E0002'
คัดลอกไปที่คลิปบอร์ด
2.1 Stored Procedures แบบกำหนดค่าเริ่มต้นให้ Parameter ในการสร้าง Store procedure แบบนี้จะเอาไว้สำหรับ Store procedure ที่มีการรับหลายๆ parameter และมี บาง parameter ที่ค่าข้อมูลเหมือนๆกัน จึงกำหนดเป็นค่าเริ่มต้นได้ จะทำให้เวลาเรียกใช้งาน จะได้ง่ายขึ้น ไม่ต้องใส่ parameter ให้ครบทุกตัว

  2. -- การสร้าง Stored Procedure แบบกำหนดค่าเริ่มต้นให้ Parameter 
  3. Create Procedure sp_fullemployee_by_emp_code (
  4.    @parameter1 datatype ,
  5.    @parameter2 datatype=Default Value,
  6.    @parameter3 datatype ,
  7.    …
  8. )
  9. As
  10. BEGIN
  11. คำสั่ง Sql Statement
  12. END
คัดลอกไปที่คลิปบอร์ด
ตัวอย่างเช่น การสร้าง Store procedure สำหรับเพิ่มข้อมูลพนักงานใหม่ (จากตัวอย่าง ผมจะแสดงให้ดูถึงข้อมูลจึงใช้คำสั่ง select @parameter นะครับ หากจะใช้จริง ก็เขียน ตรง Sql statement เป็น insert ไปแทน

  2. create procedure sp_insert_employee (
  3.                 @emp_code varchar(5) ,
  4.                 @emp_name varchar(50) ,
  5.                 @emp_surname varchar(50) ,
  6.                 @emp_tel varchar(20)='-' , -- กำหนดค่าเริ่มต้นเป็น -
  7.                 @emp_email varchar(60)='-' , -- กำหนดค่าเริ่มต้นเป็น -
  8.                 @dep_code varchar(2)='01' -- กำหนดค่าเริ่มต้นเป็นฝ่าย Account
  9. )
  10. AS
  11. BEGIN
  12.                 select @emp_code , @emp_name,@emp_surname,@emp_tel,@emp_email,@dep_code
  13. END
  14. -- การเรียกใช้งาน เมื่อเรา สร้าง Store procedure แบบกำหนดค่าเริ่มต้นให้ parameter ทำให้เรา จะใส่ข้อมูลใน parameter @emp_tel , @emp_email , @dep_code หรือไม่ก็ได้ เช่น
  15. exec sp_insert_employee 'E0006','ทดสอบ','นามสกุล'  
  16. จากตัวอย่าง จะเห็นว่า ผมกรอกเข้าไปเพิ่มเพียง 3 parameter เท่านั้น ส่วนที่เหลือจะเป็นค่าเริ่มต้นที่กำหนดไว้

  18. หรือ หากต้องการเปลี่ยนค่า ให้กับค่าเริ่มต้น บาง parameter เราก็สามารถทำได้เช่น 
  19. exec sp_insert_employee 'E0006','ทดสอบ','นามสกุล' ,@dep_code='02'
  20. จากตัวอย่าง ผมต้องการเปลี่ยน @dep_code เท่านั้นก็สามารถทำได้ โดยการระบุชื่อ Parameter ที่ต้องการ แล้วใส่ค่า value เข้าไปเท่านั้นเองครับ
คัดลอกไปที่คลิปบอร์ด
2.2 Stored Procedures แบบ Output Parameter ใช้ในกรณีที่เราต้องการ ให้ Store procedure มีการส่งค่าบางอย่างกลับมาให้เรานะครับ
ซึ่งการใช้วิธีนี้ เราสามารถ สร้างตัวแปร output ส่งค่ากลับมาได้หลายตัวแปร จะต่างจาก การสร้าง Store procedure แบบ return ซึ่ง ส่งค่ากลับมาได้ ตัวเดียวนะครับ

  2. -- การสร้าง Stored Procedure แบบ Output Parameter 
  3. Create Procedure sp_fullemployee_by_emp_code (
  4.    @parameter1 datatype ,
  5.    @parameter2 datatype=Default Value,
  6.    @parameter3 datatype output,
  7.    …
  8. )
  9. As
  10. BEGIN
  11. คำสั่ง Sql Statement
  12. END
คัดลอกไปที่คลิปบอร์ด
ตัวอย่างเช่น การสร้าง Store procedure สำหรับค้นหาข้อมูลพนักงาน ที่ต้องการค่า output คือจำนวนที่ค้นหาพบ ดังนั้นเราจึงต้องให้มี Output parameter เป็นจำนวนที่ค้นหาพบ จึงเขียน Store procedure ได้ดังนี้

  2. create procedure sp_search_employee(
  3.                 @txtsearch varchar(50),
  4.                 @ItemCount int output 
  5. )
  6. AS
  7. BEGIN
  8. -- query ข้อมูล
  9. select * from tbl_employee
  10. where (emp_code + ' ' + emp_name + ' ' + emp_surname) like '%'+@txtsearch+'%'
  11. -- นับจำนวน
  12. set @ItemCount=(select count(*) from tbl_employee
  13.                              where (emp_code + ' ' + emp_name + ' ' + emp_surname) like '%'+@txtsearch+'%'
  14.                                                                 )
  15. END
คัดลอกไปที่คลิปบอร์ด
การเรียกใช้งาน เนื่องจาก Store procedure แบบ output parameter นี้การเรียกใช้งาน เราจึงต้องมีการสร้างตัวแปร ขึ้นมาเพื่อรับ ค่า output ด้วย รูปแบบการเรียกใช้งานจึงเป็นเช่นนี้
Declare @count int
exec sp_search_employee 'ส', @count output

จะเห็นว่า เรามีการ Declare ตัวแปร @count มีชนิดเป็น int ชนิดเดียวกับ @ItemCount ที่อยู่ใน store procedure แล้ว เราจะใช้รูปแบบการกับคือ การ ใส่ตัวแปร แล้วเติม output เข้าไปในตำแหน่งของ output parameter ของ store procedure 

เมื่อเรากำหนดข้อมูลได้เรียบร้อยแล้ว เราสามารถที่จะ select @count เพื่อเห็น ผลลัพย์ของ output parameter ได้นะครับ 

การเขียน store procedure บน ms sql server ตอนที่ 4 การเขียน Store procedure แบบ Return ค่ากลับ


3. Stored Procedures แบบ Return ค่ากลับ 
     การสร้าง store procedure แบบ return ค่ากลับมานั้น ก็สามารถทำได้ ซึ่งปกติแล้วการ return store procedure นั้นเราจะใช้เพื่อตรวจสอบสถานะของ Store procedure เท่านั้น เนื่องจากมันคืนค่ากลับมาเป็น interger และ จะ return กลับมาได้เพียง 1 ค่า (ยกเว้น เราจะใช้วิธี การ ใช้ร่วมกับ output parameter)
  1. -- การสร้าง Stored Procedure return ค่ากลับ

  3. Create Procedure sp_fullemployee_by_emp_code (
  4. @parameter1 datatype ,
  5. @parameter2 datatype=Default Value,
  6. @parameter3 datatype output,
  7. …
  8. )
  9. As
  10. BEGIN
  11. Declare @num int
  12. @num=คำสั่ง Sql Statement
  13. RETURN @num
  14. END
คัดลอกไปที่คลิปบอร์ด
ตัวอย่างเช่น 
     การสร้าง Store procedure เพื่อ Update ข้อมูล Employee จะมีการ Return ค่า @@error กลับมาเพื่อให้ทราบว่าสถานะของ Store procedure นั้นทำงานเป็นอย่างไร 
(ค่า 0 ไม่พบ error แต่หากพบ error เราสามารถใช้ View sys.messages เพื่อหาดูได้ว่า เกิดจาก error อะไรเช่น 
select * from sys.messages where message_id=8134 and language_id=1033
ขออธิบาย ซักนิด message_id คือ หมายเลข error id ที่ได้จาก @@error และ language_id คือ ภาษา 1033 เป็น us_english)
  1. create procedure sp_update_employee (
  2. @emp_code varchar(5) ,
  3. @emp_name varchar(50) ,
  4. @emp_surname varchar(50) ,
  5. @emp_tel varchar(20)='-' , -- กำหนดค่าเริ่มต้นเป็น -
  6. @emp_email varchar(60)='-' , -- กำหนดค่าเริ่มต้นเป็น -
  7. @dep_code varchar(2)='01' -- กำหนดค่าเริ่มต้นเป็นฝ่าย Account
  8. )
  9. AS
  10. BEGIN
  11. Update tbl_employee set 
  12.            emp_name = @emp_name ,
  13.            emp_surname=@emp_surname ,
  14.            emp_tel = @emp_tel ,
  15.            emp_email=@emp_email ,
  16.            dep_code=@dep_code
  17. Where emp_code=@emp_code

  19. Return @@error

  21. END
คัดลอกไปที่คลิปบอร์ด
-- การเรียกใช้งาน การ update ข้อมูล employee
  1. Declare @err int
  2. exec @err=sp_update_employee 'E0005','นาย บางนา','นามสกุล ถนน' ,@dep_code='03'

  4. select @err
คัดลอกไปที่คลิปบอร์ด
ผลลัพท์ที่ได้จะไม่พบ Error เนื่องจาก การประมวลผลถูกต้อง

การเขียน store procedure บน ms sql server ตอนที่ 5 การสร้าง Store procedure แบบ รับ เงื่อนไข อิสระ


ก็มาถึงบทความเรื่อง store procedure ในตอนที่ 5 แล้วนะครับ 
     ในเนื้อหาที่เป็นพื้นฐานจริงๆ นั้นจบไปตั้งแต่ บทความตอนที่ 4 แล้วนะครับ เพราะ วิธีการเขียน t-sql control structure พวก if , while ที่จำเป็นต้องใช้ในการสร้าง store procedure แบบ Advance ผมจะค่อยๆ เขียนมาเพิ่มทีหลังครับ 

ในตอนนี้ที่ 5 นี้ก็จะเป็นการประยุกต์สร้าง store procedure ให้ยืดหยุ่น เพิ่มขึ้นเท่านั้นนะครับเป็นเทคนิคเล็กๆ เฉยๆ เกี่ยวกับการใส่เงื่อนไขให้ store procedure นะครับ ลองดูครับ


ตอนพิเศษ1 เรื่อง Store procedure การสร้าง Store procedure แบบ รับ เงื่อนไข อิสระ

จาก Store Procedure ที่ผ่านมา หากเราต้องการ ใช้เงื่อนไขที่อยาก ใส่เองแบบอิสระ จะทำไม่ได้ เนื่องจาก เรา ไม่สามารถใส่ชุดคำสั่ง Where ตามที่เราต้องการได้ เพื่อให้ Store procedure ที่เราสร้างยืดหยุ่น ขึ้นไปอีก จึงเป็นที่มาของ บทความตอนนี้นะครับ
ตัวอย่างการสร้าง Store Procedure สำหรับ กำหนดเงื่อนไขอิสระ

  2. create procedure sp_select_filter_employee(
  3.                 @condition varchar(500)='',
  4.                 @order varchar(100)='emp_code'
  5. )
  6. AS
  7. BEGIN
  8.                 EXEC(
  9.                                 'select * from tbl_employee ' +
  10.                                 @condition + ' ' + 
  11.                                 'order by '+ @order
  12.                 )
  13. END
คัดลอกไปที่คลิปบอร์ด
จะเห็นว่า มีการสร้าง parameter @condition ขนาด 500 ตัวอักษร เพื่อให้เรา สามารถใส่เงื่อนไข Where ได้อย่างอิสระ จริงๆ และ มี parameter @order สำหรับ sort ข้อมูลเช่นกัน

มาดูตัวอย่างการใช้งานบ้างนะครับ
  1. Declare @condition varchar(500),@order varchar(100)
  2. set @condition='where (emp_code + '' '' + emp_name + '' '' + emp_surname) like ''%เจริญ%'''
  3. set @order='emp_name'
  4. exec sp_select_filter_employee @condition,@order
คัดลอกไปที่คลิปบอร์ด
หมายเหตุ จากตัวแปร @condition ที่เรากำหนด สำหรับ field ทีเป็น string เราจะต้องใส่ ' ครอบ ตัวอักษร เพื่อเปรียบเทียบข้อมูล แต่ในที่นี้ ให้เราเพิ่มเป็น '' 2 ตัว เช่น 
set @condition='where emp_name=''สราวุธ'''

เขียนโดย NuengKap ที่
ป้ายกำกับ: ,

2 ความคิดเห็น:

สมัครสมาชิก: ส่งความคิดเห็น (Atom)