Những điều nên biết về STORED PROCEDURE (SP)

Hôm nay chúng ta cùng tìm hiểu về STORED PROCEDURE với mình trong phần kiến thức hôm nay. Hay còn gọi tắt là SP.

1. STORED PROCEDURE (SP) trong SQL là gì?

Là đoạn chương trình để thực hiện công việc gì đó và khi nào gọi thì nó mới thực thi.

[Database] Stored Procedure (Part 1)

[Database] Stored Procedure (Part 2)

2. Mục đích sử dụng SP

• Nó là tập hợp các khối lệnh, dùng để tái sử dụng. Bạn cứ hình dung nó như hàm ở các ngôn ngữ lập trình vậy (function/method).
• Có thể truyền tham số khi gọi nó.

3. Hướng dẫn cách sử dụng SP trong SQL

3.1. Tạo SP

Cú pháp

CREATE PROCEDURE procedure_name ([parameter[,...]])
BEGIN
sql_statement

END;

Cách gọi để thực thi:

CALL procedure_name;

Ví dụ

Tạo ra một SP để đăng ký thông tin khám bệnh dựa trên 3 thông tin được đưa vào: mã bác sĩ (p_doctor_id), mã bệnh nhân (p_patient_id), phí khám bệnh (p_fee)

Với phần lệnh sẽ thực hiện đăng ký dữ liệu vào các table lần lượt là: medical_record (sổ khám bệnh), doctor_treatment (bác sĩ khám bệnh).

Sử dụng hàm LAST_INSERT_ID() để lấy mã sổ (record_id) vừa thêm vào table medical_record.

Cuối cùng, thực hiện hiển thị ra toàn bộ thông tin.

delimiter //

CREATE PROCEDURE sp_register_treatment (p_doctor_id INT

                                         , p_patient_id INT

                                         , p_fee DOUBLE)

BEGIN

INSERT INTO medical_record (date_of_examination, fee, patient_id)

VALUES (CURDATE(), p_fee, p_patient_id);

     INSERT INTO doctor_treatment (doctor_id, record_id)

  VALUES (p_doctor_id, LAST_INSERT_ID());

     SELECT d.doctor_id, d.last_name, mr.fee, p.patient_id, p.`name`

     FROM doctor d

INNER JOIN doctor_treatment dt ON d.doctor_id = dt.doctor_id

        INNER JOIN medical_record mr ON dt.record_id = mr.record_id

         INNER JOIN patient p ON mr.patient_id = p.patient_id

WHERE mr.record_id = LAST_INSERT_ID();

END //

delimiter ;

Thực hiện gọi SP thực thi bởi từ khoá CALL:

CALL sp_register_treatment(9, 3, 70000);

Hướng dẫn cách sử dụng SP trong SQL

3.2. Xoá SP

Cú pháp

DROP PROCEDURE procedure_name

Ví dụ

Xoá SP ở đã tạo ở bên trên:

DROP PROCEDURE  sp_register_treatment

4. Tham số và biến ở SP

Ở SP sẽ hỗ trợ 3 dạng tham số: IN, OUT, INOUT. Với ý nghĩa như sau:

• IN: kiểu tham số chỉ truyền giá trị vào SP.
• OUT: kiểu tham số chỉ tham gia SP và lấy giá trị khi kết thúc SP.
• INOUT: kiểu tham kết hợp IN + OUT.

(Xem thêm ví dụ ở video trên để nắm rõ hơn).

Còn muốn tạo ra biến lưu trữ giá trị khi thao tác với dữ liệu, thì hãy tạo như thế này:

DECLARE total INT DEFAULT 0;

SET total = 10;

Mình đang thực hiện tạo ra biến total có kiểu dữ liệu là số, với giá trị mặc định là 0. Sau đó tiếp tục thực hiện phép gán bởi từ khoá SET, giá trị của total cuối cùng là 10.

Kết luận

Như bạn đã thấy, STORED PROCEDURE khá giống như hàm ở các ngôn ngữ khác, nhưng ở đây nó hỗ trợ đa dạng về kiểu tham số và cú pháp sẽ phức tạp hơn so với các ngôn ngữ thông thường.

THỦ TỤC XẢ  sp_register_ Treatment