our notes for our youngers

September 24, 2009

basic of SQL Statement (part 8)

Filed under: SQL Commands — Tags: , , , , — sevenlamp @ 9:32 AM

ဒီေန႔ ကြၽန္ေတာ္တို႔ SQL Server 2005 မွာ function ေတြကိုယ္တိုင္ ေဆာက္ျပီး သံုးတဲ႔ ပံုစံေလး ေလ့လာလိုက္ရေအာင္ဗ်ာ။ function ၂ မ်ိဳးေဆာက္လို႔ရတယ္ဗ်။ Table-valued function နဲ႔ Scalar-valued function တိုျဖစ္ပါတယ္။ Table-valued function ရဲ႕ return type က table တစ္ခုလိုမ်ိဳး column တစ္ခုထက္ပိုတဲ႔ data ေတြကို return ျပန္ပါတယ္။ Scalar-valued function ကေတာ့ return value အေနနဲ႔ column တစ္ခုပဲ return ျပန္ပါတယ္။ အရင္ဆံုး scalar-valued function ေလး တစ္ခုေလာက္ ေရးၾကည့္ရေအာင္။ မေန႔တုန္းက ေရးခဲ႔တဲ႔ category ေတြနဲ႔ total balance ထုတ္တဲ႔ query ေလးကို ျပန္ေရးပါ့မယ္။

select category.*,
(
	select sum(balance)
	from item
	where item.categoryid = category.categoryid
) as totalbalance
from category

result:

CategoryID CategoryName Status totalbalance
1 Computer Active 16
2 Stationary Active 15
3 Electronic Active 4
4 Clothes Inactive NULL
5 Kitchen wares Inactive NULL

အိုေက ဒီquery ထဲမွာ ေရးထားတဲ႔ subquery ေလးကို ကြၽန္ေတာ္က function ေလးခြဲျပီး ေရးခ်င္တာပါ။ အဲ့ဒီလိုေရးလိုက္ျခင္းအားျဖင့္ query ေရးတဲ႔ ေနရာမွာ ပိုျပီးရွင္းသြားမယ္။ ဒီေနရာမွာ ကြၽန္ေတာ္လိုခ်င္တာက totalbalance တစ္ခုထဲျဖစ္တဲ႔ အတြက္ scalar-valued function ေဆာက္ပါမယ္။ ဒီလိုပါ…

CREATE FUNCTION GetTotalBalance (@CategoryID int)
RETURNS int
AS
BEGIN
	DECLARE @TotalBalance int
	SELECT @TotalBalance = SUM(balance)
	FROM Item
	WHERE CategoryID = @CategoryID
	RETURN @TotalBalance
END

Function ေဆာက္ဖို႔ CREATE FUNCTION keyword ကိုသံုးပါတယ္။ ျပန္ျပင္ဖို႔အတြက္ေတာ့ ALTER FUNCTION နဲ႔

ALTER FUNCTION GetTotalBalance (@CategoryID int)
RETURNS int
AS
BEGIN
	DECLARE @TotalBalance int
	SELECT @TotalBalance = SUM(balance)
	FROM Item
	WHERE CategoryID = @CategoryID
	RETURN @TotalBalance
END

ဖ်က္ဖို႔အတြက္ေတာ့ DROP FUNCTION keyword တို႔ကို သံုးပါတယ္။

DROP FUNCTION GetTotalBalance

ဒီ function မွာ CategoryID ကို parameter လက္ခံျပီး item table ထဲမွာ အဲ့ဒီ categoryID ရဲ႕ totalbalance ကို return ျပန္ပါတယ္။ အိုေက အခုကြၽန္ေတာ္တို႔ ဒီ function ကို create လုပ္ျပီးသြားျပီဆိုရင္ ကြၽန္ေတာ္တို႔ ရဲ႕ query ေတြမွာ ျပန္ေခၚသံုးလို႔ ရပါျပီ။

select category.*, dbo.GetTotalBalance(categoryID)
from category

result:

CategoryID CategoryName Status totalbalance
1 Computer Active 16
2 Stationary Active 15
3 Electronic Active 4
4 Clothes Inactive NULL
5 Kitchen wares Inactive NULL

ကဲ ေနာက္ထပ္ FUNCTION တစ္မ်ိဳးက Table-valued function. အခုကြၽန္ေတာ္ categoryname, totalbalance နဲ႔ totalamount တို႔ကို ထုတ္ျပခ်င္ပါတယ္။ အဲ့ဒီ အတြက္ Table-value function ေလး ေဆာက္ရေအာင္။ သူလည္းပဲ Create function keyword ကိုပဲ သံုးပါတယ္။

CREATE FUNCTION GetItemByCategory()
RETURNS TABLE
AS
RETURN
(
    SELECT categoryname,
        sum(balance) as totalBalance,
        sum(price * balance) as totalamount
    FROM Item inner join category
	ON item.categoryID = category.categoryID
    GROUP BY categoryname
)

ျပီးရင္ အဲ့ဒီ function ေလးကို ဒီလို ေခၚပါမယ္

select * from GetItemByCategory()

result:

Categoryname totalBalance totalamount
Computer 16 105000
Electronic 4 960000
Stationary 15 7500

Leave a Comment »

No comments yet.

RSS feed for comments on this post. TrackBack URI

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

Create a free website or blog at WordPress.com.

%d bloggers like this: