ถ้าพูดถึงภาษาที่ใช้ติดต่อกับ Database อย่าง SQL แล้วส่วนใหญ่น่าจะนึกถึงคำสั่งพวก select, insert, update, delete กันเป็นอย่างแรก หรือถ้านึกได้มากกว่านั้นก็จะเป็นคำสั่งพวก create table

แต่สำหรับภาษา SQL แล้วยังมีคำสั่งอีกชุดหนึ่งที่ไม่ค่อยมีคนรู้จักกันนั่นคือเจ้า 4 ตัวที่อยู่ในหัวเรื่องนั้นแหละ

คำสั่งทั้ง 4 คือ view, function, procedure, trigger ซึ่งทั้งหมดเป็นคำสั่งประเภท DDL (Data Definition Language) ภาษานิยามข้อมูล หรือ ภาษาที่เอาไว้ใช้สร้างโครงสร้างของ database ซึ่งจะต่างกับคำสั่งพวก select และ insert, update, delete ที่เป็น DML

คีย์เวิร์ดมีอยู่ 3 ตัวเหมือน DML แต่จะใช้คนละคำกัน ดูได้จากข้างล่างนี่

งั้นมาดูกันดีกว่าว่าคำสั่งพวกนี้ใช้ยังไง และเอาไว้ใช้กรณีไหน

View

รูปแบบคำสั่ง

create view [ชื่อวิว]
as
[select statement...]

สำหรับคำสั่งแรกคือวิว เจ้าวิวเนี่ยเหมือนกับตัวจำลองตารางย่อมๆ ที่ไม่มีอยู่จริง แต่สามารถเรียกใช้ได้เหมือน table เลย จะเทียบว่ามันคือ table จำลองที่ไม่มีอยู่จริงก็ได้ ... พูดไปก็งง มาดูตัวอย่างกันดีกว่า

สมมุติว่ามี Table อยู่ดังนี้

แล้วสิ่งที่เราอยากได้คือข้อมูลพนักงานทุกคนที่เป็น programmer ซึ่งเราสามารถเขียน SQL ได้แบบนี้

select *
from Employee
where position = 'programmer'

ทีนี้ถ้าคำสั่งนี้มักจะถูกเรียกใช้บ่อยๆ หรือใน SQL อื่นๆ มักจะมีการเรียกใช้ SQL ชุดนี้ เราสามารถสร้าง view ขึ้นมาแล้วบอกว่ามันแทน SQL ชุดนี้นะ (พูดง่ายๆ คือเขียนบ่อยมาก ขี้เกียจเขียนหลายๆ รอบ เลยสร้างวิวขึ้นมาแทน)

อ่ะ มาลองสร้างกัน

create view Programmer
as
select *
from Employee
where position = 'programmer'

เสร็จแล้วกดรันเลย ผลที่ได้จะยังไม่เห็นอะไรตามสไตล์ DDL แต่ให้รู้ไว้ว่าตอนนี้มีวิวชื่อว่า Programmer เกิดขึ้นมาในระบบเราแล้ว

เจ้าตารางข้อมูล Programmer ที่เกิดขึ้นมาใหม่เนี่ย ให้มองว่ามันทำงานได้เหมือน Table เลยนะ แต่มันไม่มีตัวตนจริงๆ เท่านั้นเอง แปลว่าเราสามารถ...

select *
from Programmer

หรือจะ...

select name, salary
from Programmer
where salary > 20000

ก็ยังได้เลย

โดยความลับของมันก็คือ เมื่อเราเรียกใช้วิว แต่มันไม่มีตัวตนจริงๆ มันก็จะไปเรียกใช้ Table ต่อให้อีกทีนั่นแหละ ถ้าข้อมูลใน Table ต้นฉบับเปลี่ยน View ก็จะเปลี่ยนตามด้วย

แต่ที่มันดีคือวิวไม่ได้กำหนดว่าเราจะสร้างมันขึ้นมาจาก Table ต้นได้แค่ตัวเดียวเท่านั้น ไม่! เราสามารถสร้างมันจากหลายๆ Table ผสมกันก็ยังได้เลย เช่น

create view `Sell History`
select e.eid, c.cid, o.date
from Employee e join Order o on e.eid = o.eid
        join Customer c on c.cid = o.cid

ผลที่ได้ก็จะเป็นแบบนี้

Function

รูปแบบคำสั่ง

create function [ชื่อฟังก์ชัน] ( [@ชื่อพารามิเตอร์] datatype ... )
returns [data type เช่น int/varchar/datetime/TABLE]
as
begin
[some statement...]
return [value...]
end


ขอ assume ว่าคนที่เข้ามาอ่านบทความนี้น่าจะพอมีพื้นการเขียนโปรแกรมอยู่บ้างสักภาษานึง แปลว่าคุณน่าจะรู้จักฟังก์ชันมาบ้างแล้ว อย่างน้อยเขียนไม่เป็นแต่เคยผ่านตามาก็ยังดี

งั้นมาดูอันนี้ก่อน

int plus(int x, int y){
return x + y;
}

โค้ดนี้เป็นฟังก์ชันในภาษาตระกูล C ใครเขียน C/C++, Java, PHP อะไรพวกนี้เป็นน่าจะอ่านออกเนอะ

วิธีการใช้ฟังก์ชันในภาษา SQL ก็เหมือนกับฟังก์ชันในภาษาโปรแกรมทั่วๆ ไปนั่นแหละ คือโค้ดที่รับค่า (หรือไม่รับก็ได้) มา มีคิดโปรเซสอะไรบางอย่าง ก่อนจะตอบผลกลับมา

เราลองเอาโค้ดข้างบนมาแปลงเป็นฟังก์ชันใน SQL ดูนะ

create function plus( @x int, @y int)
returns int
as
begin
return @x + @y
end

จะเห็นว่า มันสร้างเหมือนกันเลย parameter -> body -> return แค่ syntax ของภาษามันไม่เหมือนกันเท่านั้นเอง (โดนเฉพาะคนที่เคนเขียนแต่ภาษาตระกูล C มาจะไม่ชินเลย)

การรับพารามิเตอร์ ตัวแปรในภาษา SQL จะต้องนำหน้าด้วย @ แล้วตามด้วย datatype

ข้อสังเกตอย่างหนึ่งคือคำสั่ง returns กับ return นั้นไม่เหมือนกันนะ ... returns จะใช้เพื่อบอกว่าฟังก์ชันนี้จะคือค่าเป็นอะไร ส่วน return นั้นจะใช้เหมือนกับพวกภาษา C คือตอบค่าอะไรกลับ

Scalar vs. Table

ก่อนจะไปรู้เรื่องฟังก์ชันมากกว่านี้เรามารู้จักชื่อตัวแปร 2 แบบในภาษา SQL กันก่อนดีกว่า เพราะจะมีผลกับการเขียน returns ของฟังก์ชันมากๆ

Scalar

สเกล่าคือตัวแปรที่มีค่าแบบ primitive datatype หรือพวกค่าเดี่ยวๆ ในตัวเองเช่น int, string, varchar, datetime ... วิธีคิดง่ายๆ ว่าค่าไหนเป็นสเกล่าได้ ให้ลองคิดดูว่าตอนเราสร้าง Table น่ะมี datatype ชนิดไหนที่เรากำหนดให้ field (หรือ column หรือ attribute) ได้ เซ็ตตัวไหนได้ตัวนั้นเป็นสเกล่า โอเคนะ

เมื่อกี้เรายกตัวอย่าง scalar-function ไปแล้วตัวนึง ลองมาดู scalar-function แบบยากขึ้นมาอีกหน่อยดีกว่า

create function avgSalary ( @pos varchar(32) )
returns double
as
begin
    declare @avg

    select @avg := avg(salary)
    from Employee
    where position = @pos

    return @avg
end

สำหรับฟังก์ชันนี้ เอาไว้หาว่าค่าเฉลี่ยนเงินเดือนของพนักงานตำแหน่ง...มีค่าเท่าไหร่ ดังนั้นจึงรับพารามิเตอร์เป็น varchar ชื่อว่า @pos แล้วเอาค่านี้ไปหาค่าเฉลี่ยตามคิวรี่นี้ select avg(salary) from Employee where position = @pos

แต่ก็มีปัญหาคือแล้วจะเก็บค่านี้ไว้เพื่อ return ได้ยังไง

เราจึงสร้างตัวแปรเพิ่มมาอีก 1 ตัวด้วยคำสั่ง declare @avg แล้วเอาไปรับจากผลการ avg() ที่เพื่อสั่งไป ด้วย :=

สำหรับภาษา SQL จะใช้

• := เพื่อบอกว่ากำลัง assign value อยู่ (เทียบได้กับ = ในภาษา C)
• = เพื่อเปรียบเทียบค่า (compare) ผลที่ได้จะเป็น true/false (เทียบได้กับ == ในภาษา C)

จะเห็นว่าการเขียนฟังก์ชันจะแค่รับค่ามาแล้วคำนวนบางอย่างก็พอ แต่หากจะเอาค่าใน Table มาใช้ก็สามารถทำได้เช่นกันโดยผ่านคำสั่ง select

ส่วนเวลาเรียกใช้ scalar-function ให้ลองคิดว่าตอนเราเขียน SQL พวก select, insert, update, delete นั้นตรงไหนที่เราวางค่าจำพวก field ได้บาง มันก็จะวาง scalar-function ตรงนั้นได้เช่นกัน เช่น

ต้องการแสดงผลค่าเฉลี่ยเงินเดือนโปรแกรมเมอร์ (รู้กันใช่มั้ยว่าเราสั่ง select โดยไม่มี from ได้ หึหึ)

select avgSalary('programmer')

แสดงพนักงานทุกคนที่เงินเดือนสูงกว่าค่าเฉลี่ยเงินเดือนโปรแกรมเมอร์ (ปรากฎได้ออกมาทั้งบริษัท เพราะเงินเดือนโปรแกรมเมอร์ต่ำสุด ห๊ะ!)

select *
from Employee
where salary > avgSalary('programmer')

อัพเดทเงินเดือนของพนักงาน id 1234 ให้มีค่าเท่ากับเงินเดือนเฉลี่ยโปรแกรมเมอร์

update Employee
set salary = avgSalary('programmer')
where eid = 1234

 Table

ไม่ต้องพูดไรมากมั้ง ตารางก็คือตาราง ความหมายตรงตัวสุดๆ แต่ฟังก์ชันที่รีเทิร์นเป็นตารางมันหมายความว่ายังไงกันนะ ไม่ต้องคิดไรมาก มาดูตัวอย่างต่อๆ

ex. ต้องการเลือกพนักงานตามตำแหน่งออกมา (คล้ายๆ กับตัวอย่าง view ที่ยกไปข้างต้นนะ แค่ตอนทำวิว เราฟิกค่าว่าเอาแค่ตำแหน่ง โปรแกรมเมอร์ แต่ข้อนี้จะเอาตำแหน่งไหนก็ได้)

create function EmployeeByPosition ( @pos varchar(32) )
returns TABLE
as
    select *
    from Employee
    where position = @pos

 สำหรับข้อนี้ตัด begin-end ทิ้งไปได้ เพราะ begin-end ก็มีค่าคล้ายๆ { } ในภาษา C ตามกฎการย่อคำสั่ง ถ้าคำสั่งต่อไปมีแค่คำสั่งเดียว เราสามาระละตัวครบ หรือ block ได้เช่นเดียวกับ if(x){ printf("x"); } จะมีค่าเท่ากับ if(x) printf("x");

ตอนที่เราสร้างวิว จุดอ่อนที่สุดของวิวคือมันต้องกำหนดไปเลยว่ากฎของเราคืออะไร แบบที่สร้างวิวโดยมีเงื่อนไขว่า where position ='programmer' ไป ก็ไม่สามารถเปลี่ยนเป็นตำแหน่งอื่นได้ แต่ถ้าเราเปลี่ยนมาใช้ฟังก์ชัน มันมีคุณสมบัติการใส่ค่าผ่านพารามิเตอร์อยู่ ทำให้เราเรียกใช้มันได้หลากหลายมากขึ้น

ส่วนตำแหน่งที่เรียกใช้ table-function ได้คือตำแหน่งที่สามารถเรียกใช้ Table ได้ (ถ้าในชุดคำสั่งมันจะมีอยู่แค่ทีเดียวคือใน from) เช่น

อยากได้พนักงานที่เป็นโปรแกรมเมอร์ทั้งหมด

select *
from EmployeeByPosition('programmer')

หรือจะใช้ร่วมกับ scalar-function ก็ยังได้นะ เช่น อยากได้ชื่อของพนักงานที่เป็นโปรแกรมเมอร์ซึ่งมีเงินเดือนสูงกว่าค่าเฉลี่ย

select name
from EmployeeByPosition('programmer')
where salary > avgSalary('programmer')

Procedure

รูปแบบคำสั่ง

create function [ชื่อprocedure] ( [@ชื่อพารามิเตอร์] datatype ... )
as
begin
[some statement...]
end


คำนี้เชื่อว่าคนที่เขียนโปรแกรมฝั่ง C มาไม่น่าจะรู้จัก เพราะมันไม่มีคำสั่งนี้ยังไงล่ะ (ฮา)

แต่ไม่มีคำสั่งนี้ก็ไม่ใช่ว่าโพรซีเดอร์ (หรือจะอ่านว่าโพรซีเจอร์ก็ได้นะ) ในภาษา C อยู่ในรูปแบบของ function void ยังไงล่ะ

หากคนที่เรียนวิชา programming language หรือ compiler จะรู้ว่ารูปแบบการรันและการจองเมมโมรี่ให้โค้ดที่เป็น function ที่มีค่ารีเทิร์นกลับมา กับ function void ที่ไม่ตอบอะไรกลับมาเลยนั้นเขียนไม่เหมือนกัน แต่ในภาษา C ตัดความยุ่งยากนี้ออกไปโดยให้ใช้คำว่า void แทน

แต่สำหรับ SQL มันยังเรียกฟังก์ชันที่ไม่ตอบค่าอะไรกลับเลยว่า Procedure อยู่ ก็จำไปละกันว่า function ในภาษานี้จำเป็นต้องรีเทิร์นค่ากลับเสมอ ถ้าไม่อยากรีเทิร์นให้เปลี่ยนไปใช้ procedure แทน

ดังนั้นกลับไปดูรูปแบบการเขียน procedure ใหม่เลย มีอะไรคุ้นๆ มั้ย? ...ใช่แล้วล่ะ มันรูปแบบมันเหมือนกับการเขียน function ที่ตัด returns+return ออกไปแล้วเลย

มาดูตัวอย่างกัน

create procedure showEmployeeByPosition ( @pos varchar(32) )
as
begin
    select *
    from Employee
    where position = @pos
end

การสร้างเหมือน function เลยแต่ตัด return ออกไปอย่างที่บอกนะ

แต่ปัญหาอย่างหนึ่งของ procedure คือเมื่อมันไม่รีเทิร์น มันจะไปโผล่ในคิวรี่อื่นไม่ได้ การเรียกใช้มันจึงจะมีคำสั่งเฉพาะ คือ

สำหรับ MySQL ใช้คำสั่ง call

call showEmployeeByPosition('programmer')

สำหรับ SQL Server ใช้คำสั่ง exec

EXEC showEmployeeByPosition 'programmer'

Trigger

รูปแบบคำสั่ง

create trigger [ชื่อทริกเกอร์]
on [TABLEเป้าหมาย]
for [เหตุการณ์ที่จะให้ทำงานเช่น insert/update/delete]
begin
[some statement...]
end


เอามา มาถึงตัวสุดท้ายแล้ว

ที่ผ่านๆ มาพวก View, Function, Procedure ที่เราสร้างขึ้นมามันจะไม่ทำงานจนกว่าเราจะสั่งหรือเรียกใช้มัน แต่สำหรับ Trigger นั้นจะแปลกว่าชาวบ้านนิดหน่อยคือมันจะทำงานเองโดยเราไม่ต้องสั่ง!

แปลว่าถ้าลืมว่าสร้าง trigger ไว้ แล้วมันทำงานโดยเราไม่รู้บางครั้งก็งานเข้านะครับ (ฮา)

เอาล่ะ เวลาเราจะสร้างทริกเกอร์ เราต้องลิสต์ออกมาก่อนว่า อีเวนท์ไหน-และบนตารางไหน เมื่อเกิดแล้วให้ทริกเกอร์ของเราทำงาน เช่น

มีตารางคะแนนของนักเรียนอยู่ ซึ่งเก็บคะแนนสามวิชา เลข อังกฤษ และโปรแกรมมิ่ง ส่วนชื่อสุดท้ายเป็นค่าเฉลี่ยของวิชาทั้งสาม ... และในเมื่อมันมีชื่อว่าค่าเฉลี่ย ทุกครั้งที่มีการ update ข้อมูลในตารางนี้ ค่าเฉลี่ยที่ว่าเนี่ยก็ต้องเปลี่ยนตามด้วย แต่เราจะชัวร์ได้ยังไงว่าคนที่สั่ง update ค่าคะแนนในตารางจะไม่ลืมอัพเดทค่า avg ให้เราด้วย

เคสแบบนี้แหละที่เราจะใช้ trigger

งั้นกำหนอกมาก่อนเลย

• เกิดที่ตาราง: StudentScore
• เกิดเมื่อ: update

โอเค งั้นเขียนได้

create trigger UpdateAvgScore
on StudentScore
for update
as
begin
    update StudentScore
    set avg = (math + english + programming) / 3
    where sid = NEW.sid
end

สั่งให้ database ทำการอัพเดทค่า avg ใหม่ทุกครั้งที่มีการ update ค่าบนตาราง StudentScore ... ในการใช้ทริกเกอร์ หลังจากเราสร้างมันเสร็จ เราไม่ต้องสั่งให้มันทำงานนะ แค่สั่ง update มันก็จะทำงานเองเลย (โดยที่เราไม่เห็น ตรงนี้แหละที่น่ากลัว เพราะถ้าคุณลืมว่าเคยสร้าง trigger ไป จะเกิดอาการมึนงงได้ว่าทำไมอยู่ๆ ค่า avg มันเปลี่ยนได้โดยไม่ได้สั่งอะไรเลย)

อธิบายเสริมนิดนึง ในการสั่ง insert / update / delete นั่นเราอาจจะอยากจะใช้ค่าที่เพิ่มเกิดไปเมื่อกี้ เช่นเมื่อกี้จะบอกว่าเอาเฉพาะ row ที่มีการ update มาคิดค่าใหม่พอนะ แปลว่าเราต้องรู้ด้วยว่า row ที่มันเพิ่งโดยอัพเดทไปเมื่อกี้มัน id อะไร

ไม่ต้องห่วง database เตรียมไว้ให้คุณแล้ว โดย

• for insert: จะเกิดตัวแทน Table ชื่อ NEW มาให้คุณเรียกใช้ข้อมูลที่เพิ่ง insert ลงไปเมื่อกี้
• for delete: จะเกิดตัวแทน Table ชื่อ OLD มาให้คุณเรียกใช้ข้อมูลที่เพิ่ง delete ทิ้งไปเมื่อกี้
• for update: ตัวนี้จะพิเศษหน่อยเพราะจะมีให้ทั้ง NEW และ OLD เพราะการอัพเดทคือการ delete ข้อมูลเก่าทิ้ง แล้ว insert ข้อมูลใหม่เข้าไปแทน

DBMS บางตัวจะไม่ใช้ชื่อตารางว่า NEW / OLD แต่จะใช้ชื่อ INSERTED / DELETE แทน ... ใช้ตัวไหนก็เช็กกันด้วยล่ะ

อ่ะ อีกตัวอย่างละกันเผื่อไม่เก็ท

มีตารางกระทู้ (Post) อยู่ โปรแกรมของเราอนุญาตให้ user ลบกระทู้ออกจากระบบได้ แต่บังเอิญว่าเราอยากจะเก็บข้อมูล backup ไว้ด้วยว่ากระทู้ไหนเคยโดนลบไปแล้ว

create trigger backupPost
on Post
for delete
as
begin
    insert into PostBackup
    select *
    from OLD
end

เลยตั้งว่าทุกครั้งที่มีการ delete บนตาราง Post ให้เอาข้อมูลที่เพิ่งลบไป (คือตัว OLD อ่ะนะ) insert เข้าไปในตาราง PostBackup ด้วยล่ะ (รู้กันรึเปล่าว่าการ insert ไม่จำเป็นต้องตามด้วย values เสมอไป แต่ตามด้วย set เหมือน update ก็ได้ ... หรือเหมือนเคสนี้คือตามด้วย select ก็ยังได้เลยนะ)

โอเค จบล่ะ มีคำถามอะไรถามทิ้งไว้ข้างล่างละกันนะ