Lập trình database với MySQL

-- function_name:  Tên hàm
-- argument:  Tên tham số
-- mode:  IN hoặc không cần viết.
-- datatype:  Kiểu dữ liệu của tham số

CREATE FUNCTION <function_name>
            (
               [
                [mode1] argument1 datatype1,
                [mode2] argument2  datatype2,
                ...
                ]
             )
            RETURNS  datatype
BEGIN
  -- Khai báo biến sử dụng
  -- Code nội dung của hàm
  -- Trả về giá trị của hàm.
END;

-- Một hàm có tham số:
CREATE FUNCTION Sum_Ab(a Integer, b Integer)
RETURNS Integer
Begin
   return a + b;
End;

-- Một hàm không tham số:
CREATE FUNCTION Get_Current_Datetime()
RETURNS Date
Begin
   return current_date();
End;

-- Hủy Function

DROP FUNCTION <function_name>;

-- Ví dụ:

DROP FUNCTION My_Function;

-- Ví dụ:

DROP FUNCTION IF EXISTS  `My_Function`;

-- Khi gọi hàm phải khai báo một biến trả về

-- Khai báo một biến c.
Declare c Integer;
....

-- Gọi hàm.
Set c = Sum_Ab(10, 100);

-- Huỷ function My_Sum nếu nó đã tồn tại.
-- (Để cho phép tạo lại)
DROP function  if Exists `My_Sum`;

-- Khi lập trình hàm/thủ tục bạn cần sử dụng dấu chấm phẩy để
-- ngăn cách các câu lệnh khác nhau.
-- Sử dụng DELIMITER $$ để cho phép sử dụng dấu chấm phẩy.
DELIMITER $$

Create Function My_Sum(P_a Float, p_B Float) returns Float
Begin  

 -- Khai báo một biến Float
  Declare v_C Float;
 
  -- Sét giá trị cho biến v_C
  Set V_C = p_A + p_B;
 
  -- Giá trị trả về của hàm.
  return v_C;

End;

SELECT
    acc.account_id,
    acc.cust_id,
    acc.avail_balance,
    acc.pending_balance,
    MY_SUM(acc.avail_balance, acc.pending_balance) balance
FROM
    account acc;

-- procedure_name:  Tên thủ tục
-- argument:  Tên tham số
-- mode:  Loại tham số: IN hoặc OUT hoặc INOUT, mặc định là IN
-- datatype:  Kiểu dữ liệu của tham số

CREATE PROCEDURE <procedure_name>
       (
        [
         [mode1]  argument1  datatype1,
         [mode2]  argument2   datatype2,
        ...
         ]
       )
BEGIN
-- Khai báo biến sử dụng
-- Nội dung của thủ tục.
END;

-- Ví dụ một thủ tục không tham số.
CREATE Procedure Do_Something()
Begin
   -- Khai báo biến tại đây.
  Declare v_a Integer;
  -- Làm gì đó tại đây.
  -- ....
End;

-- Ví dụ một thủ tục có tham số
--  Có một tham số đầu vào và một tham số đầu ra.

CREATE Procedure Do_Something(p_Param1 Varchar(20),
                                                 OUT v_Param2 Varchar(50) )
Begin
    -- Khai báo biến tại đây.  
   Declare v_a Integer;

   -- Làm gì đó tại đây.
   -- ...
End;

-- Hủy một thủ tục:

DROP PROCEDURE <Procedure_Name>

-- Hủy thủ tục Get_Employee_Infos nếu nó đã tồn tại.
-- (Để cho phép tạo lại)
DROP Procedure if Exists `Get_Employee_Infos`;

-- Khi lập trình hàm/thủ tục bạn cần sử dụng dấu chấm phẩy để
-- ngăn cách các câu lệnh khác nhau.
-- Sử dụng DELIMITER $$ để cho phép sử dụng dấu chấm phẩy.
DELIMITER $$

-- Thủ tục truyền vào p_Emp_Id
-- Và trả về v_First_Name, v_Last_Name, v_Dept_Id.
Create Procedure Get_Employee_Infos(p_Emp_Id     Integer
                                ,Out v_First_Name Varchar(50)
                                ,Out v_Last_Name    Varchar(50)
                                ,Out v_Dept_Id      Integer)  
Begin
-- Dành cho người lập trình biết chương trình chạy thế nào.
Select Concat('Parameter p_Emp_Id = ',p_Emp_ID)  as log_info1 ;
--
-- Sử dụng câu lệnh SELECT .. INTO để gán các giá trị truy vấn được vào biến
-- Câu lệnh này có thể gây lỗi nếu mệnh đề SELECT trả về nhiều hơn 1 dòng.
-- (Việc sử lý các lỗi như vậy sẽ được đề cập trong tài liệu này).
--
Select Emp.First_Name
     ,Emp.Last_Name
     ,Emp.Dept_Id
Into   v_First_Name
     ,v_Last_Name
     ,v_Dept_Id
From   Employee Emp
Where  Emp.Emp_Id = p_Emp_Id;
--
-- Log dành cho người lập trình
--
Select 'Found Record!' as log_info2 ;
Select concat(' v_First_Name= ', v_First_Name) as log_info3 ;
Select concat(' v_Last_Name= ', v_Last_Name) as log_info4 ;
Select concat(' v_Dept_Id= ', v_Dept_Id) as log_info5 ;

End;

Call Get_Employee_Infos(1,@v_First_Name,@v_Last_Name,@v_Dept_Id);

IF <điều kiện 1> THEN
   Công việc 1;
[ELSEIF <điều kiện 2> THEN
    Công việc 2;
]
[ELSE
    Công việc n + 1;
]
END IF;

If v_Option = 1 Then
 v_Action := 'Run';
Elseif v_Option = 2 Then
 v_Action := 'Backup';
Elseif v_Option = 3 Then
 v_Action := 'Stop';
Else
 v_Action := 'Invalid';
End If;

DROP Procedure if Exists `Else_If_Example`;

DELIMITER $$

CREATE PROCEDURE Else_If_Example()
BEGIN

   -- Khai báo một biến
   Declare v_Option Integer;
   Declare v_Action Varchar(30);
   
   Set v_Option = 2;
   
   
   If v_Option = 1 Then
     Set v_Action  = 'Run';
   Elseif v_Option = 2 Then
     Set v_Action  = 'Backup';
   Elseif v_Option = 3 Then
     Set v_Action  = 'Stop';
   Else
     Set v_Action  = 'Invalid';
   End If;
   
   -- Ghi ra log
   SELECT Concat('v_Action= ', v_Action) as Log_Info;
   
END

CASE case_value
   WHEN when_value THEN statement_list
   [WHEN when_value THEN statement_list] ...
   [ELSE statement_list]
END CASE

Hoặc:


CASE
   WHEN search_condition THEN statement_list
   [WHEN search_condition THEN statement_list] ...
   [ELSE statement_list]
END CASE

DROP Procedure if Exists `Case_When_Example1`;

DELIMITER $$

CREATE PROCEDURE Case_When_Example1()
BEGIN

 -- Khai báo 2 biến x và y.
 Declare x integer;
 Set x  = 3 ;

 -- Kiểm tra các trường hợp giá trị của x
 Case x
  -- Khi x = 1 thì
  When 1 then    
    Select 'one' as log_info1;
 
  -- Khi x = 2 thì
  When 2 then
     Select 'Two' as log_info2;
   
  -- Khi x = 3 thì
  When 3 then
     Select 'Three' as log_info3;
   
  -- Các giá trị khác
  Else
     Select 'Other' as log_info3;
   
  End case;


END;

DROP Procedure if Exists `Case_When_Example2`;

DELIMITER $$

CREATE PROCEDURE Case_When_Example2()
BEGIN

 -- Khai báo 2 biến x và y.
 Declare x integer;
 Set x  = 3 ;

 -- Kiểm tra các trường hợp:
 Case  
  -- Khi x = 1 thì
  When x = 1 then    
    Select 'one' as log_info1;
 
  -- Khi x = 2 thì
  When x = 2 then
     Select 'Two' as log_info2;
   
  -- Khi x = 3 thì
  When x = 3 then
     Select 'Three' as log_info3;
   
  -- Các giá trị khác
  Else
     Select 'Other' as log_info3;
   
  End case;


END;

<Loop_Name> : LOOP
     -- Làm gì đó tại đây.
END LOOP   <Loop_Name>;

DROP Procedure if Exists `Loop_Example1`;

DELIMITER $$

CREATE PROCEDURE Loop_Example1()
BEGIN

  -- Khai báo 2 biến x và y.
  Declare x,y integer;
  Set x  = 0 ;
  Set y = 10;
 
  -- Bắt đầu vòng lặp có tên My_Loop
  My_Loop: Loop
      -- Mỗi lần vòng lặp chạy giá trị của x tăng lên 1
      Set x = x + 1;
      -- Mỗi lần vòng lặp chạy giá trị của y giảm đi 2
      Set y = y - 2;
     
      if x > y then
           -- Thoát khỏi vòng lặp My_Loop.
          Leave My_Loop;
      End if;
     
  End Loop My_Loop;
     
  -- Ghi ra log
  SELECT Concat('x,y = ', x,', ', y) as Log_Info;
 
END;

DROP Procedure if Exists `Loop_Example2`;

DELIMITER $$

CREATE PROCEDURE Loop_Example2()
BEGIN

  -- Khai báo 2 biến x và y.
  Declare x,y integer;
  Set x  = 0 ;
  Set y = 10;
 
  -- Bắt đầu vòng lặp có tên My_Loop
  My_Loop: Loop
      -- Mỗi lần vòng lặp chạy giá trị của x tăng lên 1
      Set x = x + 1;
      -- Mỗi lần vòng lặp chạy giá trị của y giảm đi 2
      Set y = y - 2;
     
      if x <= y then
           -- Bỏ qua các dòng lệnh trong LOOP và bên dưới
           -- để tiếp tục.
          Iterate My_Loop;
      End if;
     
      -- Thoát khỏi vòng lặp.
      Leave My_Loop;
     
  End Loop My_Loop;
     
  -- Ghi ra log
  SELECT Concat('x,y = ', x,', ', y) as Log_Info;
 
END;

[Loop_label:] REPEAT

    -- Làm gì đó tại đây.

UNTIL  <điều kiện thoát khỏi vòng lặp>
END REPEAT [Loop_label];

DROP Procedure if Exists `Repeat_Until_Example`;

DELIMITER $$

CREATE PROCEDURE Repeat_Until_Example()
BEGIN

 -- Khai báo 2 biến x và y.
 Declare x integer;
 Set x  = 0 ;

 -- Bắt đầu vòng lặp có tên My_Loop
 My_Loop: Repeat

     -- Mỗi lần vòng lặp chạy giá trị của x tăng lên 1
     Set x = x + 1;
   
     -- Ghi ra log
     SELECT Concat('x = ', x) as Log_Info;

 -- Thoát khỏi vòng lặp khi x > 3
 Until x > 3    
 End Repeat My_Loop;      


END;

[Loop_Name:] WHILE search_condition DO
    -- Làm gì đó tại đây.
END WHILE [Loop_Name]

DROP Procedure if Exists `While_Do_Example`;

DELIMITER $$

CREATE PROCEDURE While_Do_Example()
BEGIN

  -- Khai báo 2 biến x và y.
  Declare x integer;
  Set x  = 0 ;
 
  -- Bắt đầu vòng lặp có tên My_Loop
  My_Loop: While x < 5 Do
 
      -- Mỗi lần vòng lặp chạy giá trị của x tăng lên 1
      Set x = x + 1;
     
      -- Ghi ra log
      SELECT Concat('x = ', x) as Log_Info;
     
  End While My_Loop;      
 
 
END;

DROP Procedure if Exists `Handling_Exception_Example1`;

DELIMITER $$

CREATE PROCEDURE Handling_Exception_Example1()
BEGIN

 -- Ghi ra log
 Select 'Three';  

 -- Trèn vào bảng Department
 -- Cột Dept_Id là cột tự tăng giá trị.
 -- Câu lệnh Insert này không có vấn đề gì.
 Insert into department(Dept_Id,Name)
 values (null, 'Dept 1');

 -- Ghi ra log
 Select 'Two';  

 -- Trèn vào bảng Department
 -- Đã có một bản ghi trong DB với Dept_ID = 1
 --  
  Insert into department(Dept_Id,Name)
  values (1 , 'Dept 2');
 
 
  -- Ghi ra log
  Select 'One!! Lets go';
   
END

DROP Procedure if Exists `Handling_Exception_Example1`;

DELIMITER $$

CREATE PROCEDURE Handling_Exception_Example1()
BEGIN

-- Khai báo một bộ điều khiển lỗi 1062, Ghi ra một thông điệp.
-- Và tiếp tục làm việc (CONTINUE).
 DECLARE CONTINUE HANDLER FOR 1062 SELECT 'Found Error: Ignore to Continue'  as My_Message;

 -- Ghi ra log
 Select 'Three';  

 -- Trèn vào bảng Department
 -- Cột Dept_Id là cột tự tăng giá trị.
 -- Câu lệnh Insert này không có vấn đề gì.
 Insert into department(Dept_Id,Name)
 values (null, 'Dept 1');

 -- Ghi ra log
 Select 'Two';  

 -- Trèn vào bảng Department
 -- Đã có một bản ghi trong DB với Dept_ID = 1
 --  
  Insert into department(Dept_Id,Name)
  values (1 , 'Dept 2');
 
 
   -- Ghi ra log
  Select 'One!! Lets go';
   
END

-- Khai báo bộ sử lý lỗi là EXIT
-- Khi gặp lỗi 1062, nó ghi ra dòng thông báo của bạn và thoát ra khỏi khối lệnh
-- (Khối lệnh chứa đoạn lệnh gây lỗi).
DECLARE EXIT HANDLER FOR 1062 SELECT 'Found Error: Exit now!!' as My_Message;

DROP Procedure if Exists `Handling_Exception_Example2`;

DELIMITER $$

CREATE PROCEDURE Handling_Exception_Example2()
BEGIN

-- Khai báo một biến
Declare error1602 Varchar(1) default 'N';

-- Khai báo một bộ điều khiển lỗi 1062.
-- Nếu lỗi này xẩy ra trong khối, gán giá trị 'Y' vào cho biến error1602.  
-- Và tiếp tục làm việc (CONTINUE).
DECLARE CONTINUE HANDLER FOR 1062 Set error1602 = 'Y';

-- Ghi ra log
Select 'Three';  

-- Trèn vào bảng Department
-- Cột Dept_Id là cột tự tăng giá trị.
-- Câu lệnh Insert này không có vấn đề gì.
Insert into department(Dept_Id,Name)
values (null, 'Dept 1');

-- Ghi ra log
Select 'Two';  

-- Trèn vào bảng Department
-- Đã có một bản ghi trong DB với Dept_ID = 1
--  
 Insert into department(Dept_Id,Name)
 values (1 , 'Dept 2');

 if error1602 = 'Y' then
    Select 'Found error Duplicate Entry (1062)' as My_Message;  
 End if;

  -- Ghi ra log
 Select 'One!! Lets go';
 
END

DECLARE <action> HANDLER FOR <condition_value> <statement>;

-- Lỗi trùng lặp khóa chính. (Khi insert)
DECLARE CONTINUE HANDLER FOR 1062
SELECT 'Error, duplicate key occurred';

-- Các SQLSTATE không bắt đầu bởi '00', '01', '02':
DECLARE EXIT HANDLER FOR SQLEXCEPTION
BEGIN
   ROLLBACK;
   SELECT 'An error has occurred, operation rollbacked and the stored procedure was terminated';
END;