Lồng nhau nếu khác trong thủ tục lưu trữ mysql

Câu lệnh IF được sử dụng trong các chương trình được lưu trữ triển khai cấu trúc điều kiện cơ bản trong MySQL. Dựa trên một điều kiện nào đó, nó cho phép chúng ta thực hiện một tập hợp các câu lệnh SQL. Nó trả về một trong ba giá trị Đúng, Sai hoặc NULL

Chúng ta có thể sử dụng câu lệnh này theo ba cách mệnh đề IF-THEN, IF-THEN-ELSE, IF-THEN-ELSEIF-ELSE và có thể kết thúc bằng END-IF. Chúng ta hãy xem chi tiết từng tuyên bố này

Câu lệnh IF-THEN

Câu lệnh này thực thi một tập hợp các truy vấn SQL dựa trên các điều kiện hoặc biểu thức nhất định. Cú pháp của câu lệnh IF-THEN như sau

Trong cú pháp trên, chúng ta phải chỉ định một điều kiện để thực thi mã. Nếu câu lệnh được đánh giá là đúng, nó sẽ thực thi câu lệnh giữa IF-THEN và END-IF. Nếu không, nó sẽ thực hiện câu lệnh sau END-IF

Thí dụ

NẾU. Khối ENDIF thực thi với các chương trình được lưu trữ và kết thúc bằng dấu chấm phẩy, như trong ví dụ bên dưới

Tiếp theo, lấy hai biến và đặt giá trị cho cả hai như bên dưới

Bây giờ, gọi hàm thủ tục lưu trữ để kiểm tra đầu ra

Chúng tôi sẽ nhận được đầu ra sau

Lồng nhau nếu khác trong thủ tục lưu trữ mysql

Câu lệnh IF-THEN-ELSE

Nếu chúng ta muốn thực hiện các câu lệnh khác khi điều kiện chỉ định trong khối IF không được đánh giá là đúng, thì có thể sử dụng câu lệnh này. Cú pháp của câu lệnh IF-THEN-ELSE được đưa ra dưới đây

Trong cú pháp trên, chúng ta phải chỉ định một điều kiện để thực thi mã. Nếu câu lệnh được đánh giá là đúng, nó sẽ thực thi câu lệnh giữa IF-THEN và ELSE. Ngược lại, nó sẽ thực thi câu lệnh sau ELSE và END-IF

Hãy để chúng tôi sửa đổi thủ tục được lưu trữ myResult() ở trên. Vì vậy, trước tiên, hãy xóa thủ tục lưu trữ myResult() bằng cách sử dụng lệnh bên dưới

Hướng dẫn THỦ TỤC LƯU TRỮ MySQL này giải thích cách tạo, cập nhật, liệt kê, xóa và gọi THỦ TỤC LƯU TRỮ trong MySQL

MySQL Cung cấp THỦ TỤC LƯU TRỮ để có một tập hợp các câu lệnh MySQL được nhóm lại với nhau trong một hàm có thể được gọi theo yêu cầu với các tham số đầu vào cụ thể

Với những thứ này, bạn có thể sử dụng lại mã và tập lệnh MySQL, giúp tiết kiệm đáng kể thời gian và công sức, đồng thời cung cấp chất lượng và hiệu suất cao với cấu trúc được mô đun hóa cho Tập lệnh MySQL

=> Đọc qua TẤT CẢ Hướng dẫn đào tạo MySQL tại đây

Chúng được lưu trữ trong cơ sở dữ liệu/lược đồ tương tự như các phần tử khác như bảng, chỉ mục, trình kích hoạt, v.v. Trong hướng dẫn này, chúng ta sẽ hiểu các phần tử, tham số, nội dung khác nhau, v.v. để tạo các thủ tục được lưu trữ này

Bạn sẽ học được gì

THỦ TỤC LƯU TRỮ MySQL

Lồng nhau nếu khác trong thủ tục lưu trữ mysql

Thuận lợi

Như đã thảo luận trước đó, những điều này cho phép sử dụng lại mã ở mức độ lớn và giúp tránh trùng lặp hoặc lặp đi lặp lại cùng một tập lệnh SQL ở những nơi khác nhau

Một số ưu điểm chính của việc sử dụng chúng như sau

  • khả năng tái sử dụng mã
  • Chuyển mạng ít hơn – E. g. cho các ứng dụng web - thay vì gọi các truy vấn riêng lẻ, có thể thực thi một thủ tục có thể trực tiếp trả về kết quả mong muốn.
  • An toàn hơn – Quản trị viên cơ sở dữ liệu có thể CẤP hoặc THU HỒI các đặc quyền ở cấp độ thủ tục
  • Cấu trúc mã mô-đun hóa

Lệnh THỦ TỤC TẠO MySQL

Trong phần này, chúng ta sẽ xem cách chúng ta có thể tạo những thứ này trong MySQL. Chúng ta sẽ xem xét cú pháp và các yếu tố khác nhau là một phần của định nghĩa và khai báo thủ tục

Ghi chú. Trong hướng dẫn này, chúng ta sẽ sử dụng MySQL Workbench Client để tạo các thủ tục.

Ngoài ra, chúng có thể được tạo từ dòng lệnh cũng như thông qua thực thi trực tiếp. tập tin kịch bản sql

cú pháp

DELIMITER {custom delimiter}
CREATE PROCEDURE {procedureName}([optional parameters])
BEGIN
	// procedure body...
	// procedure body...
END
{custom delimiter}

Ở dạng đơn giản nhất, bạn có thể tạo chúng bằng cú pháp trên

Nơi đây

#1) tên thủ tục. Đây là tên của thủ tục. Điều này tương tự như tên hàm mà chúng ta có trong hầu hết các ngôn ngữ lập trình

  • Bạn nên đặt tên proc được lưu trữ bắt đầu bằng sp_ hoặc sp để xác định rằng đó là proc
  • Ngoài ra, tên của thủ tục phải chỉ ra các bước xảy ra trong nội dung thủ tục. Ví dụ: một quy trình sẽ tìm nạp số dư cho một tài khoản nhất định, bạn có thể đặt tên cho quy trình đó là – sp_GetAccountBalance hoặc spGetAccountBalance

#2) {dấu phân cách tùy chỉnh}. Điều này được sử dụng khi bạn đang tạo procs bằng dòng lệnh hoặc thông qua ứng dụng khách GUI. Thật hữu ích khi có một dấu phân cách tùy chỉnh để máy chủ MySQL biết khi nào proc được lưu trữ kết thúc và toàn bộ nội dung thủ tục có thể được gửi dưới dạng một phương thức duy nhất

Xin lưu ý rằng dấu phân cách là tùy chọn và không cần thiết nếu không có câu lệnh nào trong quy trình kết thúc bằng dấu chấm phẩy ';'

#3) Cơ quan thủ tục. Đây là một hoặc nhiều câu lệnh MySQL sẽ được thực thi bất cứ khi nào proc được tạo sẽ được gọi

Sử dụng DELIMITERS trong khi tạo THỦ TỤC LƯU TRỮ

Trong khi tạo những thứ này trong MySQL vì chúng có thể chứa một hoặc nhiều câu lệnh SQL có dấu phân cách mặc định là dấu chấm phẩy ';', điều quan trọng là phải có Dấu phân cách riêng đánh dấu điểm bắt đầu và kết thúc để chuyển toàn bộ phần thân thủ tục tới máy chủ dưới dạng một

Để thay đổi dấu phân cách thành giá trị tùy chỉnh, cú pháp sẽ là

DELIMITER {custom value}

Thí dụ

DELIMITER //

Với câu lệnh trên, máy chủ MySQL sẽ thay đổi dấu phân cách thành ‘//’ thay vì ‘;’ Sau khi proc được tạo và lưu trong máy chủ MySQL, bạn có thể chuyển lại DẤU PHÉP thành dấu chấm phẩy bằng lệnh bên dưới

DELIMITER ;

Điều này một lần nữa sẽ đặt lại DELIMITER thành dấu chấm phẩy

Dữ liệu thử nghiệm

Chúng tôi sẽ tạo một lược đồ có tên là stored_proc_tutorial và giữ tất cả các bảng liên quan cũng như các proc thực tế trong lược đồ này

Chúng tôi cũng sẽ có một bảng kiểm tra có tên là dữ liệu sinh viên đang có dữ liệu về điểm của sinh viên

-- create schema
CREATE SCHEMA stored_proc_tutorial;

-- table creation
CREATE TABLE studentMarks (stud_id SMALLINT(5) NOT NULL AUTO_INCREMENT PRIMARY KEY, total_marks INT, grade VARCHAR(5));


-- insert sample data
INSERT INTO studentMarks(total_marks, grade) VALUES(450, 'A'), (480, 'A+'), (490, 'A++'), (440, 'B+'),(400, 'C+'),(380,'C')
,(250, 'D'),(200,'E'),(100,'F'),(150,'F'),(220, 'E');

Tạo THỦ TỤC LƯU TRỮ MySQL

Trong phần này, chúng ta hãy xem các cách khác nhau để chúng ta có thể TẠO THỦ TỤC LƯU TRỮ MySQL. Chúng ta sẽ tìm hiểu cách tạo các thủ tục không có bất kỳ tham số nào và với các loại tham số được hỗ trợ khác nhau

Với một SELECT QUERY đơn giản

Bây giờ hãy xem cách chúng ta có thể TẠO nó để chọn dữ liệu từ bảng studentMarks

Nếu chúng tôi chỉ lấy dữ liệu đó thông qua một truy vấn, thì chúng tôi có thể chạy truy vấn bên dưới

SELECT * FROM stored_proc_tutorial.studentMarks;

Bây giờ hãy xem cách chúng ta có thể chạy truy vấn này như một phần của phần thân thủ tục

DELIMITER $$
CREATE PROCEDURE GetStudentData()
BEGIN
	SELECT * FROM studentMarks;
END$$
DELIMITER ;

Hãy xác thực bằng cách gọi thủ tục đã tạo ở trên

CALL GetStudentData();

Lồng nhau nếu khác trong thủ tục lưu trữ mysql

Ghi chú. Để gọi một Proc đã tạo, bạn có thể sử dụng cú pháp dưới đây.

________số 8_______

Với thông số đầu vào và đầu ra

Tham số thủ tục MySQL

Trong MySQL, các tham số thủ tục có thể là bất kỳ một trong ba loại này

#1 TRONG. Tham số IN giống như một đối số hàm cục bộ mà chúng ta có trong hầu hết các ngôn ngữ lập trình như Java, C#, v.v.

Các tham số IN cần được chuyển cùng với lệnh gọi thủ tục và được bảo vệ. Điều này có nghĩa là việc được bảo vệ là giá trị của tham số IN có thể thay đổi trong quá trình thực thi chức năng nhưng được giữ lại sau khi luồng thực thi hoàn thành. tôi. e giá trị đã thay đổi là cục bộ để thực thi chức năng

#2) NGOÀI. Tham số OUT là tham số OUTPUT trong đó thủ tục được cho là sẽ trả về sau khi hoàn tất thực thi chức năng. Giá trị mặc định có thể được thay đổi trong quá trình thực thi thủ tục và được trả lại cho hàm gọi hoặc câu lệnh

#3) ĐẦU VÀO. INOUT là sự kết hợp của các tham số IN và OUT. Nó có thể được chỉ định tùy chọn khi thủ tục được gọi và có thể được sửa đổi và trả lại cho người gọi

Dưới đây là cú pháp chỉ định các tham số trong thủ tục MySQL

[IN/OUT/INOUT] {parameter_name} {datatype}

#4) Loại thông số. Có thể là một trong IN, OUT hoặc INOUT

#5) tên_tham số. Mã định danh cho tham số đã khai báo

#6) Kiểu dữ liệu. Loại dữ liệu mà tham số được khai báo sẽ chứa. Nó có thể là bất kỳ kiểu dữ liệu nào được MySQL hỗ trợ

=> Kiểm tra tại đây để biết danh sách đầy đủ các kiểu dữ liệu được hỗ trợ bởi MySQL

Hãy xem các ví dụ về tất cả các loại tham số khác nhau và cách chúng có thể được sử dụng trong các thủ tục MySQL

Tạo một thủ tục với các tham số đầu vào

Bây giờ hãy xem cách chúng ta có thể chuyển các tham số đầu vào để tìm nạp các kết quả cần thiết

Ví dụ: để mở rộng ví dụ trên để tìm nạp dữ liệu sinh viên, hãy tạo một thủ tục để lấy thông tin chi tiết của sinh viên với ID sinh viên được chuyển làm tham số đầu vào.

DELIMITER {custom value}
0

Trong định nghĩa trên, bạn có thể thấy rằng

  • Tên Proc được lưu trữ cũng bao gồm tên DB dưới dạng tiền tố – Điều này cho biết quy trình sẽ được lưu trữ trong DB nào và một phần của sau khi được tạo
  • Chúng tôi đã tạo một tham số IN có tên là studentID thuộc loại INT – Điều quan trọng cần lưu ý là loại tham số IN, về cơ bản là tham số INPUT có giá trị có thể được sử dụng bởi proc nhưng không thể thay đổi
  • Chúng tôi đã sử dụng tham số IN trong truy vấn CHỌN của mình để tìm nạp thông tin chi tiết về sinh viên cho id được cung cấp dưới dạng tham số IN

Để đảm bảo rằng thủ tục đã được tạo thành công, bạn có thể tham khảo danh sách MySQL Workbench Schemas

  • Chọn lược đồ/cơ sở dữ liệu nơi proc được tạo
  • Chọn menu Thủ tục lưu trữ
  • Bạn sẽ thấy một danh sách các thủ tục được tạo

Lồng nhau nếu khác trong thủ tục lưu trữ mysql

Bây giờ chúng ta hãy gọi thủ tục này và xem cách chúng ta có thể lấy thông tin chi tiết của một sinh viên cụ thể với ID đã cho

DELIMITER {custom value}
1

//Đầu ra

Lồng nhau nếu khác trong thủ tục lưu trữ mysql

Tạo thủ tục với các tham số đầu ra

Trong phần trước, chúng ta đã tìm hiểu về cách sử dụng các tham số Đầu vào (IN) được đề cập khi nó được gọi

Bây giờ hãy xem cách chúng ta có thể sử dụng các tham số Đầu ra hoặc OUT

Ví dụ. Giả sử chúng ta muốn tính điểm trung bình của tất cả học sinh từ bảng studentMarks và trả về điểm trung bình dưới dạng trường OUT.

DELIMITER {custom value}
2

Tại đây, bạn có thể thấy rằng chúng ta đã tạo một thủ tục có tên là spGetAverageMarks và chỉ định một tham số có tên là average với kiểu dữ liệu DECIMAL

Bây giờ chúng ta hãy xem làm thế nào chúng ta có thể gọi thủ tục này. Điều này có thể được thực hiện theo cách tương tự, như chúng ta đã làm đối với các thủ tục không có tham số hoặc có tham số IN

Tuy nhiên, ở đây vì chúng ta có một tham số OUT sẽ trả về một giá trị, chúng ta có thể chỉ định một trình giữ chỗ có tiền tố là '@'

Ví dụ.   Trong trường hợp này, chúng tôi đã chỉ định “@average_marks” sẽ giữ giá trị của tham số OUT trung bình khi thực thi trả về.

DELIMITER {custom value}
3

Khi nó được thực thi, bạn có thể chạy SELECT cho biến OUT để lấy kết quả

DELIMITER {custom value}
4

Lồng nhau nếu khác trong thủ tục lưu trữ mysql

Thủ tục Với INOUT THAM SỐ

Tham số INOUT là một loại tham số đặc biệt là sự kết hợp của IN i. e. INPUT và OUT tôi. e. tham số ĐẦU RA

Điều này về cơ bản có nghĩa là GỌI cho thủ tục có thể chuyển một giá trị cho tham số này và nó có thể thay đổi giá trị và chuyển lại giá trị đó cho người dùng hoặc người gọi

Hãy cố gắng hiểu điều này với một ví dụ

Giả sử chúng ta cần có một hàm lấy giá trị ban đầu của bộ đếm và tăng nó với một số nhất định

DELIMITER {custom value}
5

Bạn có thể thấy ở trên chúng ta đã khai báo 2 tham số là bộ đếm kiểu INOUT và số gia kiểu IN

Vì vậy, ở đây đối với tham số INOUT - chúng tôi đang sử dụng giá trị đã truyền và cập nhật giá trị đó để người gọi sử dụng

Để thực hiện điều này, chúng tôi đang đặt giá trị của tham số INOUT @counter thành 10 và chuyển 3 cho tham số IN gia tăng

Kết quả dự kiến ​​là => 10 + 3 => 13 (được trả về trong biến @counter khi thực hiện thủ tục)

DELIMITER {custom value}
6

Lồng nhau nếu khác trong thủ tục lưu trữ mysql

THỦ TỤC LƯU TRỮ MySQL Biến cục bộ

Giống như cách chúng ta thảo luận về các loại tham số được hỗ trợ khác nhau, chúng ta cũng có thể sử dụng Biến cục bộ bên trong các thủ tục lưu trữ tạm thời có phạm vi giới hạn chỉ cho chính thủ tục đó

cú pháp

DELIMITER {custom value}
7

Hãy hiểu việc sử dụng các biến cục bộ với sự trợ giúp của một ví dụ

Giả sử chúng ta muốn tìm số học sinh có điểm dưới điểm trung bình của tất cả các học sinh

Hãy tạo một proc

DELIMITER {custom value}
8

Ở đây bạn có thể thấy rằng chúng tôi đã khai báo một biến cục bộ có tên là avgMarks

DELIMITER {custom value}
9

Biến này sẽ giữ giá trị của giá trị trung bình được tính toán từ truy vấn CHỌN đầu tiên

DELIMITER //
0

Cũng cần lưu ý rằng giá trị cũng có thể được gán cho biến cục bộ bằng cách sử dụng SET nếu đó là giá trị tĩnh được tính toán trước

Ví dụ

DELIMITER //
1

Sau khi giá trị được đặt, chúng tôi sẽ sử dụng giá trị từ biến cục bộ để tìm số học sinh có tổng_điểm thấp hơn giá trị được tính toán (thành avgMarks)

DELIMITER //
2

Bây giờ hãy xem kết quả của việc gọi thủ tục này

DELIMITER //
3

//Đầu ra

Lồng nhau nếu khác trong thủ tục lưu trữ mysql

Liệt kê tất cả các THỦ TỤC ĐÃ LƯU TRỮ

Để xem xét tất cả các quy trình trong cơ sở dữ liệu, thông qua ứng dụng khách GUI như MySQL Workbench, bạn luôn có thể điều hướng đến lược đồ mong muốn và mở rộng nút storedProcedures để có danh sách tất cả chúng được tạo trong DB đó

Lồng nhau nếu khác trong thủ tục lưu trữ mysql

Tuy nhiên, đôi khi mong muốn có một danh sách các thủ tục với một điều kiện tìm kiếm nhất định. Ví dụ.   Tìm tất cả các proc có tên bắt đầu bằng sp.

DELIMITER //
4

//Đầu ra

Lồng nhau nếu khác trong thủ tục lưu trữ mysql

Bạn có thể thấy rằng đầu ra liệt kê tất cả các procs có tên bắt đầu bằng 'sp'

Chúng tôi cũng có thể lọc chúng theo DB

DELIMITER //
5

Cập nhật THỦ TỤC LƯU TRỮ

Tương tự như việc cập nhật một bảng hiện có hoặc cập nhật kiểu dữ liệu của trường cột, đôi khi, có thể phải CẬP NHẬT một quy trình hiện có

Nhưng việc cập nhật một cái hiện có không đơn giản như một lệnh ALTER TABLE đơn giản

Có 2 cấp độ cập nhật có thể được thực hiện đối với quy trình hiện có

#1) Cập nhật nhận xét, loại bảo mật, v.v. Để làm được điều này chúng ta có thể sử dụng lệnh ALTER PROCEDURE nhưng nó chỉ hỗ trợ rất hạn chế những thứ có thể cập nhật

cú pháp

DELIMITER //
6

Nơi đây

  • {procedure_name}. Đây là tên đủ điều kiện của quy trình mà chúng tôi muốn cập nhật
  • đặc trưng. Đây là danh sách các giá trị mà chúng tôi muốn thay đổi. Một số cái được hỗ trợ là - BÌNH LUẬN, NGÔN NGỮ, BẢO MẬT SQL, v.v.

Hãy thử cập nhật các bình luận thông qua lệnh ALTER PROCEDURE

Chúng tôi đang tạo một thủ tục mới không có bình luận

DELIMITER //
7

Bây giờ hãy sử dụng ALTER để thêm nhận xét

DELIMITER //
8

Bây giờ chúng ta có thể chạy SHOW CREATE PROCEDURE, để xem định nghĩa với các nhận xét được cập nhật

DELIMITER //
9

Trong dạng xem Trình chỉnh sửa biểu mẫu, bạn có thể thấy định nghĩa cập nhật của THỦ TỤC TẠO với các nhận xét được cập nhật

Lồng nhau nếu khác trong thủ tục lưu trữ mysql
x

#2) Cập nhật nội dung thủ tục, số lượng và loại tham số. Ví dụ bạn muốn thêm một tham số IN mới, hoặc thay đổi kiểu dữ liệu của một tham số hiện có, v.v., thì không có cách nào để cập nhật trực tiếp .

Lựa chọn duy nhất trong những trường hợp như vậy là

  • DROP thủ tục hiện có
  • TẠO quy trình mới với danh sách nội dung/tham số được cập nhật, v.v.

Xóa một THỦ TỤC ĐÃ LƯU TRỮ

Lệnh DROP PROCEDURE có thể được sử dụng để xóa thủ tục khỏi cơ sở dữ liệu đã cho

cú pháp

DELIMITER ;
0

Ví dụ. Giả sử chúng ta muốn xóa proc được lưu trữ có tên là spGetAverageMarks.

DELIMITER ;
1

Chúng ta cũng có thể DELETE hoặc DROP một thủ tục từ các máy khách GUI như MySQL Workbench

#1) Chỉ cần mở rộng tùy chọn StoredProcedures trong lược đồ đã cho

#2) Chọn proc cần xóa và nhấp chuột phải

#3) Chọn tùy chọn Drop Stored Procedure

Lồng nhau nếu khác trong thủ tục lưu trữ mysql

#4) Chọn DROP NOW để DROP ngay hoặc REVIEW để xem SQL script rồi thực thi

Lồng nhau nếu khác trong thủ tục lưu trữ mysql

Ghi chú. Để thực hiện lệnh DROP PROCEDURE, người dùng phải có đặc quyền của lệnh ALTER ROUTINE.

Gọi một thủ tục từ một thủ tục lưu trữ khác

Chúng ta có thể sử dụng khái niệm thủ tục lồng nhau khi có thể gọi một thủ tục từ bên trong một thủ tục khác

Hãy hiểu điều này với sự giúp đỡ của một ví dụ

Ta sẽ gọi một thủ tục từ một thủ tục khác để trả về kết quả chung của một sinh viên. Nếu điểm của học sinh trên trung bình – thì kết quả sẽ là ĐẠT ngược lại – KHÔNG ĐẠT

Chúng ta sẽ tạo 2 procs

#1) Đầu tiên, được đặt tên là spGetIsAboveAverage sẽ trả về giá trị Boolean nếu điểm của học sinh trên trung bình hay không

  • Tính TRUNG BÌNH sử dụng hàm AVG và lưu trữ kết quả trong một biến cục bộ
  • Lấy điểm cho ID sinh viên được truyền trong lệnh gọi hàm
  • So sánh studentMarks với điểm trung bình và trả về kết quả là 0 hoặc 1

#2) Cái thứ hai có tên là spGetStudentResult – Nó sẽ truyền studentId làm đầu vào (IN) và mong đợi kết quả làm tham số đầu ra (OUT)

  • Gọi thủ tục đầu tiên spGetIsAboveAverage
  • Sử dụng kết quả được trả về từ bước 1) và đặt kết quả thành ĐẠT hoặc KHÔNG tùy thuộc vào giá trị từ bước 1) tương ứng là 1 hoặc 0

Hãy xem các câu lệnh mã cho cả hai thủ tục

Mã cho Thủ tục 1

DELIMITER ;
2

Mã cho thủ tục 2

DELIMITER ;
3

Như bạn thấy ở trên, chúng ta đang gọi thủ tục spGetIsAboveAverage từ bên trong spGetStudentResult

Bây giờ hãy gọi thủ tục để lấy kết quả. (Điểm trung bình cho tất cả các mục trong studentTable là 323. 6)

Đối với ĐẠT – Chúng tôi sẽ sử dụng ID sinh viên 2 – có tổng số điểm – 450
Vì 450 > 323. 6 – chúng tôi sẽ mong đợi kết quả là “PASS”

DELIMITER ;
4

Lồng nhau nếu khác trong thủ tục lưu trữ mysql

Đối với kết quả FAIL, chúng tôi sẽ sử dụng studentId – 10 có tổng số điểm – 150
Vì 150 < 323. 6 – chúng tôi sẽ mong đợi kết quả là “PASS”

DELIMITER ;
5

Lồng nhau nếu khác trong thủ tục lưu trữ mysql

Sử dụng câu lệnh có điều kiện

Bây giờ hãy xem cách chúng ta có thể sử dụng các câu điều kiện như IF-ELSE hoặc CASE, v.v. trong một thủ tục

Ví dụ chúng ta muốn viết một thủ tục để lấy studentId và tùy thuộc vào studentMarks mà chúng ta cần trả về lớp theo các tiêu chí bên dưới.

Điểm >= 400. Hạng – Hạng Nhất
Điểm >= 300 và Điểm < 400 – Hạng Nhì
Điểm < 300 – Không thành công

Hãy thử tạo một thủ tục như vậy bằng cách sử dụng các câu lệnh IF-ELSE

DELIMITER ;
6

Như đã thấy ở trên, chúng tôi đã sử dụng

  • Khối IF – ELSEIF trong phần thân để truy vấn điểm của sinh viên và SET tham số OUT có tên lớp được trả về cho trình gọi thủ tục
  • studentId được truyền dưới dạng tham số IN
  • Chúng tôi đã khai báo một biến cục bộ có tên là 'marks' sẽ lấy tổng_marks từ bảng studentMarks cho ID đã cho và lưu trữ tạm thời
  • Biến cục bộ được sử dụng để so sánh với khối IF ELSE-IF

Hãy thử chạy quy trình trên cho các đầu vào khác nhau và xem kết quả

Đối với ID sinh viên – 1 – tổng_điểm là 450 – do đó kết quả dự kiến ​​là LỚP ĐẦU TIÊN

DELIMITER ;
7

Lồng nhau nếu khác trong thủ tục lưu trữ mysql

Đối với ID sinh viên – 6 – total_marks là 380 – Do đó, kết quả mong đợi là LỚP HAI

DELIMITER ;
8

Lồng nhau nếu khác trong thủ tục lưu trữ mysql

Đối với ID sinh viên – 11 – total_marks là 220 – Do đó, kết quả mong đợi KHÔNG ĐẠT

DELIMITER ;
9

Lồng nhau nếu khác trong thủ tục lưu trữ mysql

Xử lý lỗi trong THỦ TỤC LƯU TRỮ

Giống như bất kỳ ngôn ngữ lập trình nào khác, MySQL cũng có thể đưa ra các lỗi và ngoại lệ trong khi thực hiện các truy vấn

Vì vậy, trong khi thực hiện các truy vấn riêng lẻ trên thiết bị đầu cuối hoặc máy khách, bạn có thể nhận được phản hồi thành công hoặc lỗi xác định điều gì đã xảy ra

Tương tự, trong các thủ tục MySQL, các lỗi này có thể xảy ra

Chúng tôi có thể thêm trình xử lý vào nó để xử lý bất kỳ mã lỗi chung hoặc cụ thể nào được đưa ra trong quá trình thực thi quy trình. Trình xử lý lỗi này có thể chỉ đạo công cụ thực thi tiếp tục thực hiện thủ tục hoặc thoát với một số thông báo

Khai báo Trình xử lý lỗi

Để xử lý các ngoại lệ hoặc lỗi, bạn cần khai báo một XỬ LÝ trong phần thân thủ tục

Cú pháp khai báo handler là

-- create schema
CREATE SCHEMA stored_proc_tutorial;

-- table creation
CREATE TABLE studentMarks (stud_id SMALLINT(5) NOT NULL AUTO_INCREMENT PRIMARY KEY, total_marks INT, grade VARCHAR(5));


-- insert sample data
INSERT INTO studentMarks(total_marks, grade) VALUES(450, 'A'), (480, 'A+'), (490, 'A++'), (440, 'B+'),(400, 'C+'),(380,'C')
,(250, 'D'),(200,'E'),(100,'F'),(150,'F'),(220, 'E');
0

{action} có thể có giá trị

  • TIẾP TỤC. Điều này vẫn sẽ tiếp tục thực hiện thủ tục hiện tại
  • LỐI RA. Việc thực thi thủ tục này sẽ dừng lại và luồng sẽ bị chấm dứt

{điều kiện}. Đó là sự kiện sẽ khiến hàm XỬ LÝ được gọi

  • Ex – và mã lỗi MySQL – ex. MySQL sẽ đưa ra mã lỗi 1062 do vi phạm PRIMARY KEY trùng lặp
  • SQLWARNING / NOTFOUND – v.v. được sử dụng cho các trường hợp chung chung hơn. Ví dụ , điều kiện SQLWARNING được gọi bất cứ khi nào công cụ MySQL đưa ra bất kỳ cảnh báo nào cho câu lệnh được thực thi. Ví dụ, CẬP NHẬT được thực hiện mà không cần mệnh đề WHERE.

{statement} – Có thể là một hoặc nhiều câu lệnh mà chúng ta muốn thực thi khi trình xử lý đang thực thi. Nó sẽ được đặt trong các từ khóa BEGIN và END tương tự như phần thân THỦ TỤC thực tế

Ghi chú. Điều quan trọng cần lưu ý là bạn có thể khai báo nhiều trình xử lý trong một định nghĩa thân thủ tục MySQL nhất định. Điều này tương tự với việc có nhiều khối bắt cho các loại ngoại lệ khác nhau trong nhiều ngôn ngữ lập trình như Java, C#, v.v.

Hãy xem một ví dụ về khai báo XỬ LÝ cho một CHÈN KHÓA DUPLICATE (là mã lỗi 1062)

-- create schema
CREATE SCHEMA stored_proc_tutorial;

-- table creation
CREATE TABLE studentMarks (stud_id SMALLINT(5) NOT NULL AUTO_INCREMENT PRIMARY KEY, total_marks INT, grade VARCHAR(5));


-- insert sample data
INSERT INTO studentMarks(total_marks, grade) VALUES(450, 'A'), (480, 'A+'), (490, 'A++'), (440, 'B+'),(400, 'C+'),(380,'C')
,(250, 'D'),(200,'E'),(100,'F'),(150,'F'),(220, 'E');
1

Như đã thấy ở trên, chúng tôi đã tạo một trình xử lý lỗi 1062. Ngay khi điều kiện này được gọi, các câu lệnh giữa khối BEGIN và END sẽ được thực thi

Trong trường hợp này, câu lệnh SELECT sẽ trả về errorMessage là ‘LỖI KHÓA DUPLICATE’

Chúng tôi có thể thêm nhiều câu lệnh nếu cần trong BEGIN này. khối KẾT THÚC

Sử dụng Trình xử lý trong Nội dung THỦ TỤC LƯU TRỮ

Bây giờ chúng ta hãy hiểu cách các trình xử lý này có thể được sử dụng trong phần thân của thủ tục MySQL

Chúng tôi sẽ hiểu điều này với sự giúp đỡ của một ví dụ

Hãy tạo một thủ tục sẽ chèn một bản ghi vào bảng studentMarks và có các tham số IN là studentId, total_marks và grade. Chúng tôi cũng đang thêm một tham số OUT có tên rowCount sẽ trả về tổng số bản ghi trong bảng studentMarks

Hãy cũng thêm Trình xử lý lỗi cho bản ghi Khóa trùng lặp i. e. nếu ai đó gọi nó để chèn một bản ghi với ID sinh viên hiện có, thì trình xử lý Lỗi sẽ được gọi và sẽ trả về một lỗi thích hợp

-- create schema
CREATE SCHEMA stored_proc_tutorial;

-- table creation
CREATE TABLE studentMarks (stud_id SMALLINT(5) NOT NULL AUTO_INCREMENT PRIMARY KEY, total_marks INT, grade VARCHAR(5));


-- insert sample data
INSERT INTO studentMarks(total_marks, grade) VALUES(450, 'A'), (480, 'A+'), (490, 'A++'), (440, 'B+'),(400, 'C+'),(380,'C')
,(250, 'D'),(200,'E'),(100,'F'),(150,'F'),(220, 'E');
2

Bây giờ hãy gọi Quy trình này với id sinh viên hiện có

-- create schema
CREATE SCHEMA stored_proc_tutorial;

-- table creation
CREATE TABLE studentMarks (stud_id SMALLINT(5) NOT NULL AUTO_INCREMENT PRIMARY KEY, total_marks INT, grade VARCHAR(5));


-- insert sample data
INSERT INTO studentMarks(total_marks, grade) VALUES(450, 'A'), (480, 'A+'), (490, 'A++'), (440, 'B+'),(400, 'C+'),(380,'C')
,(250, 'D'),(200,'E'),(100,'F'),(150,'F'),(220, 'E');
3

Đầu ra sẽ hiển thị thông báo lỗi được xác định trong trình xử lý lỗi

Ngoài ra, vì nó là trình xử lý thoát, luồng thủ tục chính sẽ không tiếp tục. Do đó, trong trường hợp này, tham số rowCount OUT sẽ không được cập nhật vì câu lệnh này nằm sau câu lệnh INSERT đã tạo ra tình trạng lỗi

Lồng nhau nếu khác trong thủ tục lưu trữ mysql

Vì vậy, nếu bạn cố truy xuất giá trị của rowCount, bạn sẽ nhận được NULL

-- create schema
CREATE SCHEMA stored_proc_tutorial;

-- table creation
CREATE TABLE studentMarks (stud_id SMALLINT(5) NOT NULL AUTO_INCREMENT PRIMARY KEY, total_marks INT, grade VARCHAR(5));


-- insert sample data
INSERT INTO studentMarks(total_marks, grade) VALUES(450, 'A'), (480, 'A+'), (490, 'A++'), (440, 'B+'),(400, 'C+'),(380,'C')
,(250, 'D'),(200,'E'),(100,'F'),(150,'F'),(220, 'E');
4

Lồng nhau nếu khác trong thủ tục lưu trữ mysql

Hãy DROP quy trình này và tạo lại bằng hành động TIẾP TỤC thay vì EXIT cho trình xử lý lỗi

-- create schema
CREATE SCHEMA stored_proc_tutorial;

-- table creation
CREATE TABLE studentMarks (stud_id SMALLINT(5) NOT NULL AUTO_INCREMENT PRIMARY KEY, total_marks INT, grade VARCHAR(5));


-- insert sample data
INSERT INTO studentMarks(total_marks, grade) VALUES(450, 'A'), (480, 'A+'), (490, 'A++'), (440, 'B+'),(400, 'C+'),(380,'C')
,(250, 'D'),(200,'E'),(100,'F'),(150,'F'),(220, 'E');
5
-- create schema
CREATE SCHEMA stored_proc_tutorial;

-- table creation
CREATE TABLE studentMarks (stud_id SMALLINT(5) NOT NULL AUTO_INCREMENT PRIMARY KEY, total_marks INT, grade VARCHAR(5));


-- insert sample data
INSERT INTO studentMarks(total_marks, grade) VALUES(450, 'A'), (480, 'A+'), (490, 'A++'), (440, 'B+'),(400, 'C+'),(380,'C')
,(250, 'D'),(200,'E'),(100,'F'),(150,'F'),(220, 'E');
6

Như bạn có thể thấy ở trên, thay vì `DECLARE EXIT', chúng tôi hiện đang sử dụng 'DECLARE CONTINUE', điều này sẽ dẫn đến việc tiếp tục thực thi sau khi xử lý mã lỗi

Hãy thử gọi thủ tục này với ID sinh viên hiện có

-- create schema
CREATE SCHEMA stored_proc_tutorial;

-- table creation
CREATE TABLE studentMarks (stud_id SMALLINT(5) NOT NULL AUTO_INCREMENT PRIMARY KEY, total_marks INT, grade VARCHAR(5));


-- insert sample data
INSERT INTO studentMarks(total_marks, grade) VALUES(450, 'A'), (480, 'A+'), (490, 'A++'), (440, 'B+'),(400, 'C+'),(380,'C')
,(250, 'D'),(200,'E'),(100,'F'),(150,'F'),(220, 'E');
3

Bạn vẫn sẽ thấy lỗi tương tự, nhưng lần này RowCount sẽ được cập nhật, vì chúng tôi đã sử dụng hành động TIẾP TỤC thay vì EXIT

Hãy thử lấy giá trị của tham số rowCount OUT

-- create schema
CREATE SCHEMA stored_proc_tutorial;

-- table creation
CREATE TABLE studentMarks (stud_id SMALLINT(5) NOT NULL AUTO_INCREMENT PRIMARY KEY, total_marks INT, grade VARCHAR(5));


-- insert sample data
INSERT INTO studentMarks(total_marks, grade) VALUES(450, 'A'), (480, 'A+'), (490, 'A++'), (440, 'B+'),(400, 'C+'),(380,'C')
,(250, 'D'),(200,'E'),(100,'F'),(150,'F'),(220, 'E');
4

Lồng nhau nếu khác trong thủ tục lưu trữ mysql

THỦ TỤC LƯU TRỮ MySQL so với CHỨC NĂNG

MySQL cung cấp 2 cách để tạo các phương thức hoặc mã được sử dụng lại dưới dạng CHỨC NĂNG và THỦ TỤC

Tuy nhiên, có sự khác biệt nhất định giữa cả hai

PROCEDUREFUNCTIONHỗ trợ các loại tham số khác nhau như IN, OUT và INOUT. Chỉ hỗ trợ các tham số đầu vào. Họ có thể gọi các chức năng. Hàm không thể gọi thủ tục. Các ngoại lệ có thể được xử lý trong các thủ tục. Không thể xử lý ngoại lệ trong FUNCTIONS. Có thể hoặc không thể trả về một giá trị. MỘT CHỨC NĂNG dự kiến ​​​​sẽ luôn trả về kết quả. Chúng không thể được gọi từ bên trong câu lệnh SELECT. Các hàm có thể được gọi từ bên trong câu lệnh SELECT. Chúng chủ yếu được sử dụng để xử lý các tác vụ lặp lại. CHỨC NĂNG được sử dụng để tính toán các giá trị và trả lại kết quả cho người gọi. Chúng được biên dịch sẵn - tôi. e. chúng được biên dịch một lần và mã đã biên dịch được sử dụng lại cho các lần gọi thủ tục tiếp theo. CHỨC NĂNG được biên dịch mỗi khi chúng được gọi

Các câu hỏi thường gặp

Q #1) MySQL có thủ tục lưu sẵn không?

Câu trả lời. MySQL có THỦ TỤC LƯU TRỮ được lưu trữ như một phần của cơ sở dữ liệu/lược đồ giống như các thực thể khác như bảng, chỉ mục, v.v.

Chúng có thể được tạo bằng cách sử dụng lệnh CREATE PROCEDURE và có thể có các tham số tùy chọn được chỉ định để truyền dữ liệu cũng như thu được kết quả do thực hiện thủ tục

Q #2) Làm cách nào để tôi xem một thủ tục được lưu trữ trong MySQL?

Câu trả lời. Chúng thường được liên kết với cơ sở dữ liệu trong MySQL (ngoại lệ có thể là các thủ tục do hệ thống tạo)

Để liệt kê tất cả các thủ tục, bạn có thể

  • Xem qua các máy khách GUI như bàn làm việc của MySQL, bằng cách mở rộng tùy chọn StoredProcedures trong cơ sở dữ liệu hoặc lược đồ
  • Lấy danh sách bằng cách thực hiện lệnh SHOW PROCEDURE STATUS
-- create schema
CREATE SCHEMA stored_proc_tutorial;

-- table creation
CREATE TABLE studentMarks (stud_id SMALLINT(5) NOT NULL AUTO_INCREMENT PRIMARY KEY, total_marks INT, grade VARCHAR(5));


-- insert sample data
INSERT INTO studentMarks(total_marks, grade) VALUES(450, 'A'), (480, 'A+'), (490, 'A++'), (440, 'B+'),(400, 'C+'),(380,'C')
,(250, 'D'),(200,'E'),(100,'F'),(150,'F'),(220, 'E');
9

Lệnh trên sẽ liệt kê tất cả các thủ tục có từ Student trong tên

Q #3) Tại sao lại sử dụng thủ tục lưu sẵn trong MySQL?

Câu trả lời. Chúng là một phương tiện để có cấu trúc mô-đun mạnh mẽ cho các tập lệnh SQL có thể lặp lại, giúp ích trong việc

  • Sử dụng lại các tập lệnh hiện có
  • Tối ưu hóa hiệu suất bằng cách giảm chuyển mạng
  • Dễ dàng giữ các tập lệnh được phiên bản như một phần của kho mã, v.v.

Tuy nhiên, để làm việc với chúng, có thể cần một lượng kiến ​​thức/kỹ năng tốt và do đó sẽ liên quan đến một số thời gian học tập

Q #4) Tham số đầu ra trong thủ tục được lưu trữ là gì?

Câu trả lời. MySQL hỗ trợ các tham số đầu ra được sử dụng bởi từ khóa OUT. Đây là các tham số mà người gọi sẽ sử dụng và mong muốn thủ tục được gọi cập nhật

Ví dụ. Chúng ta có thủ tục lấy điểm cao nhất từ ​​bảng dữ liệu sinh viên. Chúng ta có thể có một cái như thế này với điểm cao nhất được lưu trong tham số OUT.

SELECT * FROM stored_proc_tutorial.studentMarks;
0

Q #5) Làm cách nào để chuyển các loại tham số khác nhau cho thủ tục lưu trữ SQL?

Câu trả lời. MySQL hỗ trợ 3 loại tham số có thể được chuyển khi một thủ tục được gọi

Cú pháp giống nhau cho tất cả

SELECT * FROM stored_proc_tutorial.studentMarks;
1

Các loại tham số khác nhau có các tiện ích khác nhau

TRONG. Loại này được sử dụng khi người gọi thủ tục cần chuyển một số dữ liệu sẽ được sử dụng bởi thủ tục

OUT. Loại này được sử dụng khi người gọi mong đợi một số giá trị được tính bằng thủ tục được gọi. Ví dụ: tìm Điểm cao nhất, Điểm trung bình từ bảng dữ liệu Sinh viên, v.v.

ĐẦU VÀO. Đây là một loại tham số đặc biệt có thể được chuyển bởi người gọi cũng như được cập nhật bởi việc thực hiện thủ tục và gửi lại cho người gọi. Một ví dụ có thể là – Người gọi chuyển giá trị ban đầu của tham số và thủ tục được gọi thực hiện một số thao tác và cập nhật giá trị ban đầu

Q #6) MySQL lưu trữ thủ tục lưu trữ ở đâu?

Câu trả lời. Trong MySQL, chúng được lưu trữ trong bảng Hệ thống 'thông tin lược đồ. thói quen`

Để xem tất cả các mục trong bảng này, chúng ta có thể chạy truy vấn CHỌN trên bảng này ( Lưu ý. Những quyền này thường có sẵn cho người dùng root hoặc quản trị viên hệ thống).

SELECT * FROM stored_proc_tutorial.studentMarks;
2

Tham khảo => Lược đồ thông tin. bảng thói quen

Q #7) Làm cách nào tôi có thể gọi thủ tục được lưu trữ trong một thủ tục được lưu trữ khác trong MySQL?

Câu trả lời. Hoàn toàn hợp lệ khi gọi một thủ tục từ bên trong một thủ tục khác. Một điểm quan trọng, bạn cần đảm bảo rằng thủ tục được gọi phải tồn tại và phải hợp lệ trước khi nó được gọi trong thủ tục khác. Nó giống như gọi một hàm bên trong một hàm khác trong bất kỳ ngôn ngữ lập trình nào khác như Java, C#

Phần kết luận

Trong hướng dẫn này, chúng tôi đã giới thiệu các THỦ TỤC LƯU TRỮ trong MySQL

Chúng ta đã học về cách tạo các thủ tục đơn giản, khái niệm sử dụng DELIMITER trong định nghĩa thủ tục và các loại Tham số khác nhau được chúng hỗ trợ

Chúng tôi cũng đã tìm hiểu về cách chúng có thể được gọi và cách sử dụng các giá trị được truyền lại từ quá trình thực thi thủ tục

Ngoài những điều này, chúng tôi đã tìm hiểu về cách xử lý các mã lỗi trong phần thân thủ tục thông qua các trình xử lý lỗi và cách chúng tôi có thể gọi một thủ tục hiện có từ bên trong định nghĩa hoặc phần thân của một thủ tục khác

Chúng được sử dụng rộng rãi trong rất nhiều dự án trưởng thành đang thực hiện các tương tác và tính toán cơ sở dữ liệu rất nặng. Những điều này có thể ảnh hưởng lớn đến hiệu suất của các ứng dụng và có thể nâng cao đáng kể khả năng sử dụng lại các tập lệnh và giảm truyền dữ liệu Mạng để có hiệu suất tối ưu

Làm cách nào để triển khai nếu khác trong MySQL?

Câu lệnh IF-THEN-ELSE của MySQL . Câu điều kiện IF THEN; . true – câu lệnh giữa IF-THEN và ELSE sẽ được thực thi. If a specified condition is: true – statements between IF-THEN and ELSE will be executed.

Làm cách nào để sử dụng các câu điều kiện trong MySQL?

Câu lệnh IF-THEN đơn giản của MySQL .
Đầu tiên, chỉ định một điều kiện để thực thi mã giữa IF-THEN và END IF. Nếu điều kiện đánh giá là TRUE, các câu lệnh giữa IF-THEN và END IF sẽ thực thi. .
Thứ hai, chỉ định mã sẽ thực thi nếu điều kiện đánh giá là TRUE

Làm cách nào để kiểm tra xem một thủ tục được lưu trữ có phải là null trong MySQL không?

Hàm ISNULL() của MySQL dùng để kiểm tra xem một biểu thức có phải là NULL hay không. Hàm này trả về 1 nếu biểu thức được truyền là NULL, ngược lại trả về 0. Hàm ISNULL() chấp nhận biểu thức dưới dạng tham số và trả về một số nguyên có giá trị 0 hoặc 1 tùy thuộc vào tham số được truyền.

Làm cách nào để tạo thủ tục được lưu trữ trong MySQL với các tham số?

Đầu tiên, chỉ định chế độ tham số, có thể là IN , OUT hoặc INOUT tùy thuộc vào mục đích của tham số trong thủ tục lưu trữ. Thứ hai, chỉ định tên của tham số. Tên tham số phải tuân theo quy tắc đặt tên của tên cột trong MySQL. Thứ ba, chỉ định loại dữ liệu và độ dài tối đa của tham số