Khmer Knowledge

Stored Procedure គឺជាបណ្តុំនៃ SQL Statement ដែលធ្វើការងារពិសេសណាមួយ ហើយវាអាចធ្វើការ Request ទៅកាន់ Server ម្តងមួយ Block នៃ Statement តែម្តង ឬ ក៏អាចបង្កើតជាសំណុំការគណនាណាមួយដែលគោលការណ៍នៃក្រុមហ៊ុន ដែលប្រើនូវ T SQL ឬ Built-in Function របស់ SQL Server តែម្តង ។

1. បង្កើត Stored Procedure

ការបង្កើត Stored Procedure មានរូបមន្តដូចខាងក្រោម៖
Syntax:
 Create proc[edure] procedure_name @parameternamedatatype=[default]
 As 
  T-sql statement(s)
ឧទាហរណ៍ ៖ 
 Create Proc[edure] prostu
 As 
 Select * from tblstudent;
ការដំណើរការ Stored Procedure ដែលបានបង្កើតរួច
Execute:  Exec[ute] prostu;;

2. Parameter នៅក្នុង Stored Procedure

នៅក្នុងការបង្កើត Stored Procedure ជាទូទៅយើងការប្រើ Paremeter ដើម្បីធ្វើការទៅតាមការងារដែលយើងបង្កើត Procedure នោះ តែនៅក្នុង Pararmeter យើងក៏មានច្រើនប្រភេទសម្រាប់ធ្វើការទទួលតម្លៃដូចនេះយើងត្រូវកំណត់ Parameter ឱ្យបានច្បាស់នៅពេលបង្កើតនោះ។
ខាងក្រោមនេះជា Parameter ធម្មតានៅក្នុង Procedure តែប្រសិនបើយើងចង់កំណត់ Default ទៅឱ្យ Parameter  ឬ តម្លៃទទេរ
CREATE PROCEDURE GetTaskEvents @TaskName varchar(50), @ID int = NULL
AS
BEGIN
-- SP Logic
END;

នៅក្នុង Stored Procedure មាន Parameter ចំនួន ៣ ប្រភេទ គឺ ៖ Input, Output និង Optional 
CREATE PROCEDURE [Sales].[udpGetSalesByTerritory] 
                      @TerritoryID int, 
                      @SumTotalSubTotal money = 10000 OUTPUT, 
                      @SumTotalDue money OUTPUT 
AS 
SELECT
        @SumTotalSubTotal = SUM(SubTotal), 
        @SumTotalDue = SUM(TotalDue) 
FROM                   Sales.SalesOrderHeader 
WHERE          TerritoryID = @TerritoryID) AND 
                               (SubTotal > @SumTotalSubTotal)

បង្កើត Stored Procedure នៅក្នុង SQL Server
Create Proc [SP_Subject] @FacID int,@Years int,@Semester nvarchar(50),@AcademicYear nvarchar(50)
as
If @Semester='second session'
Select SubID,SubName from tblSubjects Where FacID=@FacID and Years=@Years and AcademicYear=@AcademicYear
Else
Select SubID,SubName from tblSubjects Where FacID=@FacID and Years=@Years and Semester in(@Semester) and AcademicYear=@AcademicYear

ហៅ Stored Procedure ប្រើជាមួយនឹង Class SQLCommand នៅក្នុង  VB.NET
 Dim cmd As New SqlCommand(“SP_Subject", con)
   con.Open()
   cmd.CommandType = CommandType.StoredProcedure
   cmd.Parameters.Add("@FacID", SqlDbType.Int).Value = cmbFaculty.SelectedValue
   cmd.Parameters.Add("@Years", SqlDbType.Int).Value =cmbYear.SelectedValue
   cmd.Parameters.Add("@Semester", SqlDbType.Int).Value = cmbSemester.SelectedValue
   cmd.Parameters.Add("@AcademicYear", SqlDbType.NVarChar, 50).Value = cmbAcademicYear.SelectedValue
   Dim dtSubject As New DataTable
   dtSubject.Load(cmd.ExecuteReader())
   con.Close()
   gvSubject.DataSource = dtSubject

ហៅ Stored Procedure ប្រើជាមួយនឹង Class SQLDataAdapter នៅក្នុង  VB.NET
 Dim da As New SqlDataAdapter(“SP_Subject", con)
 da.SelectCommand.CommandType=CommandType.StoredProcedure
 da.SelectCommand.Parameters.Add("@FacID", SqlDbType.Int).Value =      cmbFaculty.SelectedValue
  da.SelectCommand.Parameters.Add("@Years", SqlDbType.Int).Value =      cmbYear.SelectedValue
 da.SelectCommand.Parameters.Add("@Semester", SqlDbType.Int).Value =     cmbSemester.SelectedValue
  da.SelectCommand.Parameters.Add("@AcademicYear", SqlDbType.NVarChar, 50).Value =   cmbAcademicYear.SelectedValue
    Dim dtSubject As New DataTable
 Da.Fill(dtSubject)
    gvSubject.DataSource = dtSubject

ខាងក្រោមនេះជាការបង្កើត OUTPUT Parameter នៅក្នុង Procedure
ALTER Proc [dbo].[sp_output] @Name nvarchar(50),@ID nvarchar(50) OUTPUT
as
   SELECT @ID = names 
   FROM tblinfo Where Names=@Name
តើយើងត្រូវប្រើយ៉ាងមិចនៅក្នុងការសរសេរ Code នៅក្នុង VB.NET ឬ C#?
គឺយើងត្រូវកំណត់ Property Direction ទៅឱ្យ Parameter នោះស្មើនឹង ParameterDirection.Output

Dim cmd As New SqlCommand("sp_output", con)
            cmd.CommandType = CommandType.StoredProcedure
            cmd.Parameters.Add("@Name", SqlDbType.NVarChar, 50).Value = "sok"
            cmd.Parameters.Add("@ID", SqlDbType.NVarChar, 50)
            cmd.Parameters("@ID").Direction = ParameterDirection.Output
            con.Open()
            cmd.ExecuteReader()
            con.Close()
            MessageBox.Show(cmd.Parameters("@ID").Value)

ចូលបង្កើត Procedure ដែលធ្វើការ Insert Delete និង Update ទៅកាន់ Table student.
ឧទាហរណ៍ ៖
Insert ទិន្នន័យចូលទៅកាន់ Table info
Create Proc SP_InsertData 
  @Name nvarchar(50),@Phone nvarchar(50),@email nvarchar(255)
As
 Insert into tblinfo(Names,Phone,Email) Values(@Name,@Phone,@Email)
Delete ទិន្នន័យចេញពី Table info
    Create Proc SP_DeleteData @ID int
As
 Delete From tblinfo Where ID=@ID;

Update ទិន្នន័យចេញពី Table info
Create Proc SP_UpdateData @ID int,@name nvarchar(50),@phone nvarchar(50),@email nvarchar(255)
As
Update tblinfo SET Names=@name,Phone=@phone,Email=@Email
Where ID=@ID
 
លំហាត់
 ចូរធ្វើការបង្កើត Stored Procedure មួយដែលធ្វើការ Insert ទិន្នន័យចូលក្នុង Table Order និង Update ទិន្នន័យនៅក្នុង Table Stock_Out ។ 

Exercise

1. ចូរធ្វើការបង្កើត Sequence នៅក្នុង SQL Server ដែលមានទម្រង់ដូចខាងក្រោម៖ Pro001, Cus001, Stu001 ដែលបី Character ខាងមុខជាអក្សរហើយបីលេខខាងក្រោយ Auto Number 14070001, 14070002, 14070003, 14071001, 14071002, 14071003, 14072001 ធ្វើការបង្កើត Sequence ដែលកើតឡើងតាមថ្ងៃ និងឆ្នាំ Function ដែលត្រូវប្រើ - GetDate() សម្រាប់ទាញយកថ្ងៃ ខែ បច្ចុប្បន្ន - Year(GetDate()) សម្រាប់ទាញយកឆ្នាំបច្ចុប្បន្ន - Left(Expression,Count) សម្រាប់ចាប់យកផ្នែកណាមួយនៃ Expression ទៅតាមចំនួន Character នៅខាងឆ្វេង ។ 2. ចូរបង្កើត Procedure ដែលអាចធ្វើការ Insert, Update ទៅលើ Table ណាមួយ ដោយប្រើ SQLCommand និង SQLDataAdapter ព្រមទាំង OutPut Parameter ផងដែរ ។