Làm cách nào để sử dụng tham số trong truy vấn MySQL?

Trong bài viết này, chúng ta sẽ tìm hiểu về thủ tục lưu sẵn trong MySQL. Trong bài viết này, tôi đề cập đến những điều cơ bản của thủ tục được lưu trữ bao gồm những điều sau đây

1. Tóm tắt thủ tục lưu trữ MySQL
2. Tạo một thủ tục được lưu trữ bằng cách sử dụng bàn làm việc Truy vấn và MySQL
3. Tạo một thủ tục được lưu trữ được tham số hóa
4. Bỏ Thủ tục được lưu trữ bằng cách sử dụng truy vấn và bàn làm việc của MySQL

Thủ tục được lưu trữ là các câu lệnh SQL được bao bọc trong câu lệnh CREATE PROCEDURE. Thủ tục được lưu trữ có thể chứa một câu lệnh có điều kiện như IF hoặc CASE hoặc Vòng lặp. Thủ tục được lưu trữ cũng có thể thực thi một thủ tục được lưu trữ khác hoặc một chức năng mô đun hóa mã

Sau đây là những lợi ích của một thủ tục được lưu trữ

1. Giảm lưu lượng mạng. Nhiều câu lệnh SQL được gói gọn trong một thủ tục được lưu trữ. Khi bạn thực hiện nó, thay vì gửi nhiều truy vấn, chúng tôi chỉ gửi tên và các tham số của thủ tục được lưu trữ
2. Dễ bảo trì. Các thủ tục được lưu trữ có thể tái sử dụng. Chúng ta có thể triển khai logic nghiệp vụ trong một SP và nó có thể được các ứng dụng sử dụng nhiều lần hoặc các mô-đun khác nhau của một ứng dụng có thể sử dụng cùng một quy trình. Bằng cách này, một thủ tục được lưu trữ làm cho cơ sở dữ liệu nhất quán hơn. Nếu có bất kỳ thay đổi nào được yêu cầu, bạn chỉ cần thực hiện thay đổi trong thủ tục được lưu trữ
3. Chắc chắn. Các thủ tục được lưu trữ an toàn hơn các truy vấn AdHoc. Quyền có thể được cấp cho người dùng để thực thi thủ tục được lưu trữ mà không cấp quyền cho các bảng được sử dụng trong thủ tục được lưu trữ. Thủ tục lưu trữ giúp ngăn cơ sở dữ liệu khỏi SQL Injection

Cú pháp để tạo thủ tục lưu trữ MySQL như sau

Tạo thủ tục [Tên thủ tục] ([Tham số 1], [Tham số 2], [Tham số 3] )
Bắt đầu
Truy vấn SQL.
Kết thúc

trong cú pháp

1. Tên của thủ tục phải được chỉ định sau từ khóa Tạo thủ tục
2. Sau tên của thủ tục, danh sách các tham số phải được chỉ định trong ngoặc đơn. Danh sách tham số phải được phân tách bằng dấu phẩy
3. Các truy vấn và mã SQL phải được viết giữa các từ khóa BEGIN và END

Để thực thi thủ tục lưu trữ, bạn có thể sử dụng từ khóa CALL. Dưới đây là cú pháp

GỌI [Tên thủ tục] ([Tham số]. )

trong cú pháp

1. Tên thủ tục phải được chỉ định sau từ khóa CALL
2. Nếu thủ tục có các tham số, thì các giá trị tham số phải được chỉ định trong dấu ngoặc đơn

Hãy để chúng tôi tạo một thủ tục lưu trữ cơ bản. Để trình diễn, tôi đang sử dụng cơ sở dữ liệu sakila

Tạo thủ tục lưu trữ đơn giản

Giả sử bạn muốn điền vào danh sách phim. Đầu ra phải chứa film_id, tiêu đề, mô tả, năm phát hành và cột xếp hạng. Mã của thủ tục là như sau

1

2

3

4

5

6

7

DÒNG KHÁC //

TẠO THỦ TỤC sp_GetMovies()

BẮT ĐẦU

    chọn tiêu đề,mô tả,release_year,rating from film;

END //

DÒNG KHÁC ;

Để tạo Quy trình lưu trữ MySQL, hãy mở bàn làm việc MySQL Kết nối với Cơ sở dữ liệu MySQL sao chép-dán mã vào cửa sổ trình chỉnh sửa truy vấn, nhấp vào Thực thi

Bạn có thể xem thủ tục dưới thủ tục lưu sẵn. Xem ảnh chụp màn hình bên dưới

Để thực hiện thủ tục, hãy chạy lệnh dưới đây

1

GỌI sp_GetMovies

Dưới đây là ảnh chụp màn hình một phần của đầu ra

Tạo thủ tục bằng MySQL workbench wizard

Chúng ta có thể sử dụng MySQL workbench wizard để tạo thủ tục lưu sẵn. Giả sử bạn muốn lấy danh sách khách hàng từ cơ sở dữ liệu sakila. Để làm điều đó, hãy mở rộng lược đồ sakila Nhấp chuột phải vào Thủ tục được lưu trữ Chọn Tạo thủ tục được lưu trữ

Trong cửa sổ Thủ tục mới, một mẫu tạo đã được tạo. Trong mẫu, thay tên thủ tục bằng sp_getCustomers. Trong khối mã, nhập truy vấn sau

1

chọn store_id, first_name, last_name, email, create_date, last_update from customer

Nhấp vào Áp dụng. Một hộp thoại, Áp dụng tập lệnh cho cơ sở dữ liệu sẽ mở ra. Trên màn hình Xem lại tập lệnh, bạn có thể xem mã của thủ tục được lưu trữ. Nhấp vào Áp dụng

Tập lệnh được áp dụng thành công và quy trình được lưu trữ sp_GetCustomer đã được tạo thành công

Trong MySQL Workbench, Bạn có thể xem thủ tục được lưu trữ trong thư mục Thủ tục được lưu trữ của lược đồ sakila

Tạo thủ tục lưu trữ được tham số hóa

Tham số thủ tục lưu trữ MySQL có ba chế độ. VÀO, RA và VÀO. Khi chúng ta khai báo một tham số kiểu IN, ứng dụng phải truyền một đối số cho thủ tục lưu sẵn. Đó là một chế độ mặc định. Tham số loại OUT, thủ tục được lưu trữ trả về kết quả cuối cùng được tạo bởi Câu lệnh SQL. Khi chúng ta khai báo tham số kiểu INPUT, ứng dụng phải truyền một đối số và dựa trên đối số đầu vào;

Khi chúng ta tạo thủ tục lưu trữ, các tham số phải được chỉ định trong dấu ngoặc đơn. Cú pháp như sau

1

(IN . OUT . VÀO RA) (Tham số Tên [datatype(length)])

trong cú pháp

1. Chỉ định loại tham số. Nó có thể là IN, OUT hoặc INOUT
2. Chỉ định tên và kiểu dữ liệu của tham số

Ví dụ về tham số IN

Giả sử chúng ta muốn lấy danh sách phim dựa trên xếp hạng. param_rating là một tham số đầu vào và kiểu dữ liệu là varchar. Mã của thủ tục là như sau

1

2

3

4

5

6

DÒNG KHÁC //

TẠO THỦ TỤC sp_GetMoviesByRating(IN rating varchar(50))

BẮT ĐẦU

    chọn tiêu đề,mô tả,release_year,rating from film where rating=rating;

END //

DÒNG KHÁC ;

Để điền danh sách các phim có xếp hạng NC-17, chúng tôi chuyển giá trị NC-17 cho thủ tục sp_getMoviesByRating()

1

CALL sp_GetMoviesByRating('NC-17')

đầu ra

Ví dụ về tham số OUT

Giả sử chúng ta muốn lấy số lượng phim có xếp hạng PG-13. Total_Movies là tham số đầu ra và kiểu dữ liệu là số nguyên. Số lượng phim được gán cho biến OUT (Total_Movies) bằng cách sử dụng từ khóa INTO. Mã của thủ tục là như sau

1

2

3

4

5

6

DÒNG KHÁC //

TẠO THỦ TỤC sp_CountMoviesByRating(OUT Total_Movies int)

BẮT ĐẦU

    chọn số lượng(tiêu đề) INTO Total_Movies from film where rating='PG-13';

END //

DÒNG KHÁC ;

Để lưu trữ giá trị do thủ tục trả về, hãy chuyển một biến phiên có tên @PGRatingMovies

1

2

CALL sp_CountMoviesByRating(@PGRatingMovies)

Chọn @PGRatingPhim làm Phim

Ví dụ về tham số INOUT

Giả sử chúng tôi muốn lấy tổng số phim dựa trên xếp hạng. Tham số đầu vào là param_rating trong thủ tục và kiểu dữ liệu là varchar(10). Tham số đầu ra là Movies_count và kiểu dữ liệu là số nguyên

mã thủ tục

1

2

3

4

5

6

DÒNG KHÁC //

TẠO THỦ TỤC sp_CountMoviesByRating_Inout(inout Movies_count int, In param_rating varchar(10))

BẮT ĐẦU

    chọn số lượng(tiêu đề) INTO Movies_count from film where rating=param_rating ;

END //

DÒNG KHÁC ;

Thực hiện quy trình bằng từ khóa CALL và lưu kết quả đầu ra trong biến phiên có tên @MoviesCount

1

2

CALL sp_CountMoviesByRating_Inout(@T,'PG-13');

Chọn @T làm Phim

Xem danh sách thủ tục được lưu trữ trong cơ sở dữ liệu bằng truy vấn

Để xem danh sách thủ tục được lưu trữ, bạn có thể truy vấn information_schema. bảng thói quen. Nó chứa danh sách thủ tục lưu sẵn và hàm lưu trữ được tạo trên cơ sở dữ liệu. Để xem danh sách thủ tục được lưu trữ được tạo trong cơ sở dữ liệu sakila, hãy chạy truy vấn sau. Hơn nữa, nó cũng cung cấp chủ sở hữu, ngày tạo, loại bảo mật và quyền truy cập dữ liệu SQL vào các thủ tục được lưu trữ

1

chọn routine_name, routine_type,definer,created,security_type,SQL_Data_Access from information_schema.quy trình ở đâu routine_type=' and routine_schema='sakila';

Thả thủ tục lưu trữ

Để loại bỏ thủ tục đã lưu trữ, bạn có thể sử dụng lệnh thả thủ tục. Cú pháp như sau

Drop procedure [IF EXISTS]

Trong cú pháp, tên của thủ tục lưu trữ phải được theo sau bởi từ khóa Drop Procedure. Nếu bạn muốn xóa thủ tục sp_getCustomers khỏi cơ sở dữ liệu sakila, bạn có thể chạy truy vấn sau

1

Loại bỏ quy trình sp_getCustomers

Khi bạn cố gắng loại bỏ quy trình không tồn tại trên cơ sở dữ liệu, truy vấn hiển thị lỗi

LỖI 1305 (42000). THỦ TỤC. getCustomer không tồn tại

Để tránh điều này, bạn có thể bao gồm tùy chọn [IF EXISTS] trong lệnh thủ tục thả. Khi bạn bao gồm từ khóa IF EXISTS, thay vì lỗi, truy vấn sẽ trả về cảnh báo

Truy vấn OK, 0 hàng bị ảnh hưởng, 1 cảnh báo (0. 01 giây) 1305 THỦ TỤC sakila. getCustomer không tồn tại

Bỏ một thủ tục được lưu trữ bằng cách sử dụng thuật sĩ bàn làm việc của MySQL

Bạn có thể sử dụng trình hướng dẫn bàn làm việc của MySQL để bỏ thủ tục. Để loại bỏ bất kỳ thủ tục nào, hãy mở rộng lược đồ sakila Mở rộng Thủ tục lưu trữ Nhấp chuột phải vào sp_GetMovies Nhấp vào Thả thủ tục lưu trữ

Một hộp thoại mở ra. Bạn có thể chọn xem lại quy trình trước khi bỏ hoặc bạn có thể bỏ mà không cần xem lại. Bạn nên xem lại đối tượng cơ sở dữ liệu trước khi loại bỏ nó, vì vậy hãy chọn Xem lại SQL

Làm cách nào để chuyển một tham số trong truy vấn MySQL?

Tôi có thể sử dụng tham số trong truy vấn SQL không?

Làm cách nào để sử dụng các biến trong truy vấn MySQL?

%s và %D trong MySQL là gì?