1. Stored Procedure là gì?

Stored Procedures – là một tập các câu lệnh SQL nhằm thực thi tác vụ nào đó.

VD: Giả sử chúng ta có câu lệ như sau, muốn lưu trữ lại để sử dụng mà không phải viết lại query, chúng ta có thể sử dụng procedure

SELECT 
    customerName, 
    city, 
    state, 
    postalCode, 
    country
FROM
    customers
ORDERBY customerName;

có thể viết tạo stored procedure wrap câu lệnh trên.

DELIMITER $$

CREATEPROCEDURE GetCustomers()BEGINSELECT 
		customerName, 
		city, 
		state, 
		postalCode, 
		country
	FROM
		customers
	ORDERBY customerName;END$$
DELIMITER;

để chạy stored procedure có thể gọi

CALL GetCustomers();

Advantages

Giảm băng thông: Thay vì gửi 1 tập các câu lệnh giữa application vs Mysql server, application chỉ cần phải gửi tên và parameter tới Mysql Serve
Bảo mật: Có thể giới hạn quyền truy cập, chỉ cho phép application truy cập các stored procedure cụ thể, thay vì cung cập quyền truy cập đến bất cứ table nào.
Logic tập trung ở tầng DB: Có thể tạo các stored procedure có cùng logics và sử dụng chung giữa các application, việc này có thể loại bỏ các trùng lặp logic giữa các application.

Disadvantages

• Chưa hỗ trợ debug tool
• Cần có hiểu biết nhất định mới có thể maintain Stored procedure
• Làm tốn nhiều bộ nhớ của database nếu lạm dụng

2.Stored procedure basic

2.1 Tạo mới

DELIMITER//CREATEprocedure GetAllProducts()BEGINSelect*from products;END//DELIMITER;

Ở cau lệnh trên

• Change default delimiter thành //
• Sử dụng ‘;’ trong body procedure, sau đó // sau keyword END để kết thúc stored procedure
• chuyển delimiter về ‘;’

2.2 Drop

Cú pháp

DROPPROCEDURE[IFEXISTS] stored_procedure_name;

2.3 Variable

Cú pháp

DECLARE variable_name datatype(size)[DEFAULT default_value];

DECLARE define local varaible, scope của local variable là từ BEGIN đến END trong procedure

DELIMITER $$

CREATEPROCEDURE GetTotalOrder()BEGINDECLARE totalOrder INTDEFAULT0;SELECTCOUNT(*)INTO totalOrder
    FROM orders;SELECT totalOrder;END$$

DELIMITER;

call GetTotalOrder()-- 326

2.4 Parameter

[IN|OUT|INOUT] parameter_name datatype[(length)]

• IN: khai báo input của SP
• OUT: Output cúa SP
• INOUT: biến được khai báo được input vào SP, và trả về giá trị mới. Một lưu ý là variable được truyền vào INOUT sẽ bị thay đổi sau khi thực thi procedure. Ở IN thì không thay đổi variable.

VD:

DELIMITER $$

CREATEPROCEDURE SetCounter(INOUT counter INT,IN inc INT)BEGINSET counter = counter + inc;END$$

DELIMITER;

SET@counter=1;CALL SetCounter(@counter,1);-- 2CALL SetCounter(@counter,1);-- 3CALL SetCounter(@counter,5);-- 8SELECT@counter;-- 8

2.5 Drop

DROPPROCEDURE[IFEXISTS] procedure_name;

2.6 Show

SHOWPROCEDURESTATUS[LIKE'pattern'|WHERE search_condition];

VD:
Show theo database

SHOWPROCEDURESTATUSWHERE db ='classicmodels';

Show theo tên procedure

SHOWPROCEDURESTATUSLIKE'%Order%'

3. Condition statement

3.1 IF THEN ELSE ELSEIF END IF

Cú pháp

IF condition THENELSEIF condition2
ELSEENDIF;

Ví dụ

DELIMITER $$

CREATEPROCEDURE GetCustomerLevel(IN  pCustomerNumber INT,OUT pCustomerLevel  VARCHAR(20))BEGINDECLARE credit DECIMALDEFAULT0;SELECT creditLimit 
    INTO credit
    FROM customers
    WHERE customerNumber = pCustomerNumber;IF credit >50000THENSET pCustomerLevel ='PLATINUM';ELSEIF credit <=50000AND credit >10000THENSET pCustomerLevel ='GOLD';ELSESET pCustomerLevel ='SILVER';ENDIF;END $$

DELIMITER;

CALL GetCustomerLevel(447,@level);SELECT@level;-- GOLD

3.2 Loop, Repeate loop(do while), while loop

Mình sẽ trình bày về loop, còn repeate loop và while loop các bạn tự tìm hiểu nhé
Cú pháp

[begin_label:]LOOP
    statement_list
ENDLOOP[end_label]

DROPPROCEDURE LoopDemo;DELIMITER $$
CREATEPROCEDURE LoopDemo()BEGINDECLARE x  INT;DECLARE str  VARCHAR(255);SET x =1;SET str ='';
        
	loop_label:  LOOPIF  x >10THENLEAVE  loop_label;ENDIF;SET  x = x +1;IF(x mod 2)THENITERATE  loop_label;ELSESET  str = CONCAT(str,x,',');ENDIF;ENDLOOP;SELECT str;END$$

DELIMITER;

• Leave tương tự với break
• Iterate tương tự với continue

CALL LoopDemo();+-------------+| str         |+-------------+|2,4,6,8,10,|+-------------+1rowinset(0.01 sec)

Query OK,0rows affected (0.02 sec)

4. Error handling

Cú pháp

DECLAREactionHANDLERFOR condition_value statement;

action có các gia trị

• continue: tiếp tục chạy SP
• exit: dừng SP, chạy code block trong BEGIN END handler

condition_value có thể nhận giá trị

• A MySQL error code. https://dev.mysql.com/doc/mysql-errors/8.0/en/server-error-reference.html
• giá trị SQLSTATE, hoặc SQLWARNING , NOTFOUND, SQLEXCEPTION …
  VD:

CREATETABLE SupplierProducts (
    supplierId INT,
    productId INT,PRIMARYKEY(supplierId , productId));

DELIMITER $$
CREATEPROCEDURE InsertSupplierProduct(IN inSupplierId INT,IN inProductId INT)BEGIN-- exit if the duplicate key occursDECLAREEXITHANDLERFOR1062BEGINSELECT CONCAT('Duplicate key (',inSupplierId,',',inProductId,') occurred')AS message;END;-- insert a new row into the SupplierProductsINSERTINTO SupplierProducts(supplierId,productId)VALUES(inSupplierId,inProductId);-- return the products supplied by the supplier idSELECTCOUNT(*)FROM SupplierProducts
    WHERE supplierId = inSupplierId;END$$

DELIMITER;

Cách hoạt động
Khi có lỗi xảy ra, duplicate key 1062, Sẽ chạy terminate procedure, sau đó thực thi block code begin-end của handler trả về message

CALL InsertSupplierProduct(1,1);CALL InsertSupplierProduct(1,2);CALL InsertSupplierProduct(1,3);CALL InsertSupplierProduct(1,3);+------------------------------+| message                      |+------------------------------+|Duplicatekey(1,3) occurred |+------------------------------+1rowinset(0.01 sec)

Nếu thay exit thành continue ở trên handler thi câu lệnh select vẫn được chạy, và trả về giá trị

DECLAREEXITHANDLERFOR1062BEGINSELECT CONCAT('Duplicate key (',inSupplierId,',',inProductId,') occurred')AS message;END;CALL InsertSupplierProduct(1,3);+----------+|COUNT(*)|+----------+|3|+----------+1rowinset(0.01 sec)

Query OK,0rows affected (0.02 sec)