Tham số trong MySQL
|
Bạn có thể tạo tham số bằng IN và OUT. IN được sử dụng để lấy tham số đầu vào và OUT có thể được sử dụng cho đầu ra Show Cú pháp như sau DELIMITER // CREATE PROCEDURE yourProcedureName(IN yourParameterName dataType,OUT yourParameterName dataType ) BEGIN yourStatement1; yourStatement2; . . N END; // DELIMITER ; Đầu tiên, chúng ta sẽ tạo một bảng. Truy vấn để tạo bảng như sau mysql> create table SumOfAll -> ( -> Amount int -> ); Query OK, 0 rows affected (0.78 sec) Chèn một số bản ghi vào bảng bằng lệnh chèn. Truy vấn như sau mysql> insert into SumOfAll values(100); Query OK, 1 row affected (0.18 sec) mysql> insert into SumOfAll values(330); Query OK, 1 row affected (0.24 sec) mysql> insert into SumOfAll values(450); Query OK, 1 row affected (0.10 sec) mysql> insert into SumOfAll values(400); Query OK, 1 row affected (0.20 sec) Hiển thị tất cả các bản ghi từ bảng bằng cách sử dụng câu lệnh chọn. Truy vấn như sau mysql> select *from SumOfAll; Sau đây là đầu ra +--------+ | Amount | +--------+ | 100 | | 330 | | 450 | | 400 | +--------+ 4 rows in set (0.00 sec) Bây giờ, chúng ta sẽ tạo thủ tục lưu sẵn để kiểm tra giá trị có trong bảng hay không. Nếu giá trị đã cho không có trong bảng thì bạn sẽ nhận được giá trị NULL Theo mặc định, phiên bản Cơ sở dữ liệu MySQL sử dụng nhóm tham số Cơ sở dữ liệu dành riêng cho cơ sở dữ liệu MySQL. Nhóm tham số này chứa các tham số cho công cụ cơ sở dữ liệu MySQL. Để biết thông tin về cách làm việc với các nhóm tham số và cài đặt tham số, hãy xem Làm việc với các nhóm tham số Các tham số RDS cho MySQL được đặt thành giá trị mặc định của công cụ lưu trữ mà bạn đã chọn. Để biết thêm thông tin về các tham số MySQL, hãy xem tài liệu về MySQL. Để biết thêm thông tin về công cụ lưu trữ MySQL, hãy xem Bạn có thể xem các tham số có sẵn cho một phiên bản RDS cho MySQL cụ thể bằng bảng điều khiển RDS hoặc AWS CLI. Để biết thông tin về cách xem các tham số trong nhóm tham số MySQL trong bảng điều khiển RDS, hãy xem Khi sử dụng AWS CLI, bạn có thể xem các tham số của phiên bản RDS cho MySQL bằng cách chạy lệnh dotConnect cho MySQL nâng cao khả năng xử lý SQL với việc sử dụng các tham số trong truy vấn SQL. Bạn có thể thực hiện một truy vấn hoặc thủ tục được lưu trữ rất linh hoạt bằng cách sử dụng một số kỹ thuật đơn giản. Bài viết này mô tả một số điều cơ bản mà bạn phải làm quen khi làm việc với các tham số trong dotConnect cho MySQL, cũng như đồng bộ hóa các tham số và một số sắc thái liên quan đến việc sử dụng các thủ tục được lưu trữ Bài viết gồm các phần sau thông số cơ bảnNói chung, một tham số là một trình giữ chỗ cho một biến chứa một số giá trị của một số loại khi thực hiện một truy vấn mục đích chung hoặc các đối số và giá trị trả về khi một thủ tục được lưu trữ được thực thi. Tham số được đại diện bởi MySql. Lớp MySqlParameter. Tất cả các tham số tham gia thực hiện truy vấn tạo thành một bộ sưu tập có thể được truy cập thông qua MySqlCommand. thuộc tính tham số MySQL hỗ trợ hai loại tham số. có tên và không có tên Tham số chưa được đặt tên có thể được chỉ định là '?' . truy vấn sau INSERT INTO dept (deptno, dname, loc) VALUES (?, ?, ?) tuyên bố rằng cần có ba tham số để chạy truy vấn đúng cách. Để đặt tham số cho truy vấn này, bạn có thể sử dụng mã tiếp theo MySqlCommand mySqlCommand1;
...
mySqlCommand1.CommandText = "INSERT INTO dept (deptno, dname, loc) VALUES (?, ?, ?)";
mySqlCommand1.Parameters.Add("param1", 30);
mySqlCommand1.Parameters.Add("param2", "SALES");
mySqlCommand1.Parameters.Add("param3", "CHICAGO");
Dim mySqlCommand1 as MySqlCommand
...
mySqlCommand1.CommandText = "INSERT INTO dept (deptno, dname, loc) VALUES (?, ?, ?)"
mySqlCommand1.Parameters.Add("param1", 30)
mySqlCommand1.Parameters.Add("param2", "SALES")
mySqlCommand1.Parameters.Add("param3", "CHICAGO")
Các tham số được đặt tên yêu cầu phù hợp với tên của các phiên bản MySqlParameter trong bộ sưu tập. Các tham số được đặt tên được khai báo bằng cách sử dụng '. ' hoặc '@' tiền tố theo sau tên của tham số. Lưu ý rằng tên của đối tượng MySqlParameter trong bộ sưu tập lệnh phải chứa tiền tố '@' nếu tham số trong CommandText được sử dụng với tiền tố '@'. Nếu tham số trong CommandText chứa '. ' tiền tố, nên sử dụng tên của đối tượng MySqlParameter trong bộ sưu tập của lệnh mà không có bất kỳ tiền tố nào. Bạn có thể sử dụng các tiền tố này ở bất kỳ kết hợp nào để chỉ định tham số. Có hai ưu điểm chính của các tham số được đặt tên. Thứ nhất, bạn không cần quan tâm đến thứ tự tạo tham số. Thứ hai, tham số đã đặt tên có thể xuất hiện nhiều lần trong văn bản truy vấn, nhưng bạn chỉ phải tạo một phiên bản của tham số đó trong bộ sưu tập Tham số Ví dụ: một câu lệnh Cập nhật đơn giản yêu cầu các tham số được đặt tên có thể giống như sau ________số 8_______Để đặt tham số cho truy vấn này, bạn có thể sử dụng mã tiếp theo MySqlCommand mySqlCommand1;
...
mySqlCommand1.CommandText = "UPDATE dept SET dname = :dname, +
loc = :loc WHERE deptno = @deptno";
mySqlCommand1.Parameters.Add("@deptno", 20);
mySqlCommand1.Parameters.Add("dname", "SALES");
mySqlCommand1.Parameters.Add("loc", "NEW YORK");
Dim mySqlCommand1 as MySqlCommand
...
mySqlCommand1.CommandText = "UPDATE dept SET dname = :dname, &_
loc = :loc WHERE deptno = @deptno"
mySqlCommand1.Parameters.Add("@deptno", 20)
mySqlCommand1.Parameters.Add("dname", "SALES")
mySqlCommand1.Parameters.Add("loc", "NEW YORK")
Các biến máy chủ MySQL cũng được biểu thị bằng ký hiệu '@'. Khi gặp trong văn bản truy vấn, các đoạn như vậy chỉ được coi là tham số nếu chúng được tìm thấy trong MySqlCommand. Bộ sưu tập tham số; Khi bạn gọi một thủ tục được lưu trữ, bạn phải tạo tập hợp các tham số tương ứng với bộ đối số cho thủ tục được lưu trữ về số lượng và loại. Tên của các tham số không thành vấn đề trừ khi bạn đặt MySqlCommand. Tham sốKiểm tra thuộc tính thành true. Bạn không được sử dụng tiền tố "@" trong tên tham số thủ tục được lưu trữ. Các tham số đại diện cho các đối số của thủ tục phải được đặt thuộc tính Hướng của chúng thành Đầu vào. Bạn truy xuất giá trị được trả về bởi một hàm được lưu trữ thông qua một tham số có thuộc tính Direction được đặt thành ReturnValue Sử dụng đồng bộ hóa tham số tự độngHành vi được mô tả ở trên giả định rằng MySqlCommand. Kiểm tra tham số là sai (theo mặc định). Bằng cách bật tính năng này, bạn có thể tự động đồng bộ hóa văn bản truy vấn và MySqlCommand. Bộ sưu tập thông số. Trong chế độ này, tất cả các tham số đầu vào đều được kiểm tra tính hợp lệ, các tham số mới sẽ được thêm vào nếu cần và các tham số dư thừa sẽ bị xóa. Do đó, bạn không phải quan tâm đến số lượng mục trong MySqlCommand. Bộ sưu tập tham số, bạn chỉ có thể chỉ định những tham số bạn thực sự cần. Việc đồng bộ hóa được thực hiện như sau
Đối với các truy vấn không đại diện cho một thủ tục được lưu trữ, việc đồng bộ hóa diễn ra khi
Đối với thủ tục được lưu trữ đồng bộ hóa xảy ra khi
Một truy vấn riêng biệt được thực hiện đối với máy chủ để thiết lập các loại tham số chính xác khi đồng bộ hóa được thực hiện cho các thủ tục được lưu trữ Nếu đồng bộ hóa đã được thực hiện, các lệnh gọi tiếp theo tới các phương thức này sẽ không dẫn đến đồng bộ hóa lại, trừ khi bạn đã sửa đổi bộ sưu tập Tham số hoặc một trong các thuộc tính được liệt kê ở trên Sử dụng các tham số với các thủ tục được lưu trữ trong chế độ đồng bộ hóaNếu các tham số được thêm vào bộ sưu tập lệnh theo thứ tự khác với thứ tự tham số chức năng trong cơ sở dữ liệu, thì cần phải mô tả lệnh bằng cách đặt MySqlCommand. Tham số Kiểm tra thành true để sắp xếp lại các tham số theo cách phù hợp Khi MySqlCommand. Kiểm tra tham số là đúng, bạn chỉ có thể chỉ định những tham số bạn thực sự cần. Các tham số bị bỏ qua sẽ được tạo và gán giá trị DBNull. Bạn có thể thiết lập các tham số cần thiết theo bất kỳ thứ tự nào bạn muốn. Bộ sưu tập sẽ được lấp đầy và sắp xếp do đồng bộ hóa Khi làm việc với các thủ tục được lưu trữ ở chế độ đồng bộ hóa, bạn chỉ có thể sử dụng các tham số được đặt tên. Tên tham số phải trùng với tên đối số của thủ tục. Tuy nhiên, tham số ReturnValue không cần phải có bất kỳ tên cụ thể nào Ở chế độ đồng bộ hóa, lần đầu tiên gọi tới MySqlCommand. Chuẩn bị hoặc MySqlCommand. Các phương thức thực thi dẫn đến việc giải trí tất cả các tham số đối số. Nếu tên của một tham số phù hợp với mô tả của thủ tục được lưu trữ, tham số sẽ được lưu trong bộ sưu tập, nếu không nó sẽ bị mất. Nếu MySqlCommand. Thuộc tính CommandText và bộ sưu tập Tham số không thay đổi, tất cả các lần gọi tiếp theo của phương thức Chuẩn bị hoặc Thực thi sẽ không dẫn đến việc tạo lại các tham số Ví dụ: giả sử bạn có một thủ tục được lưu trữ chấp nhận hai đối số, deptno và dname, sau đó thay đổi MySqlCommand. CommandText để tham chiếu một thủ tục được lưu trữ khác chấp nhận các đối số deptno và loc. Trong trường hợp này, bạn sẽ có tham số đầu tiên không thay đổi và tham số thứ hai được tạo lại với giá trị được gán cho DBNull Sử dụng các tham số OUT và INOUTThật không may, MySQL chưa hỗ trợ các tham số OUT trong giao thức. Tuy nhiên, dotConnect cho MySQL cho phép bạn xử lý các tham số OUT bằng cách sử dụng các biến phía máy chủ. Xem xét câu lệnh CREATE sau CREATE PROCEDURE testproc(IN param1 INTEGER(11), OUT param2 INTEGER(11)) BEGIN SET param2=param1*2; END Thủ tục lấy tham số đầu vào, nhân nó với hai và lưu kết quả trong tham số đầu ra. Để truy xuất giá trị của tham số out, hãy khai báo biến máy chủ tạm thời và sau đó truy vấn giá trị của nó như sau MySqlConnection myConn = new MySqlConnection(
"user id=root;database=demobase;host=localhost;password=root");
myConn.Open();
MySqlCommand command = new MySqlCommand(
"CALL testproc(10, @param2);SELECT @param2", myConn);
using (IDataReader reader = command.ExecuteReader()) {
if (reader.Read())
Console.WriteLine("@param2 = " + reader[0]);
}
myConn.Close();
Dim myConn As MySqlConnection = New MySqlConnection( _
"user id=root;database=demobase;host=localhost;password=root")
myConn.Open()
Dim command As MySqlCommand = New MySqlCommand( _
"CALL testproc(10, @param2);SELECT @param2", myConn)
Dim reader As IDataReader = command.ExecuteReader()
If (reader.Read()) Then
Console.WriteLine("@param2 = " & reader(0))
End If
reader.Close()
myConn.Close()
Kỹ thuật tương tự có thể được sử dụng để xử lý các tham số INOUT. Để gán giá trị ban đầu cho tham số, hãy sử dụng câu lệnh SET trước lệnh gọi thủ tục được lưu trữ. Ví dụ tiếp theo cho thấy cách cải tổ cuộc gọi trước đó thành thủ tục được lưu trữ bằng cách sử dụng tham số INOUT duy nhất CREATE PROCEDURE testproc(INOUT param1 INTEGER(11)) BEGIN SET param1=param1*2; END MySqlCommand mySqlCommand1;
...
mySqlCommand1.CommandText = "INSERT INTO dept (deptno, dname, loc) VALUES (?, ?, ?)";
mySqlCommand1.Parameters.Add("param1", 30);
mySqlCommand1.Parameters.Add("param2", "SALES");
mySqlCommand1.Parameters.Add("param3", "CHICAGO");
0MySqlCommand mySqlCommand1;
...
mySqlCommand1.CommandText = "INSERT INTO dept (deptno, dname, loc) VALUES (?, ?, ?)";
mySqlCommand1.Parameters.Add("param1", 30);
mySqlCommand1.Parameters.Add("param2", "SALES");
mySqlCommand1.Parameters.Add("param3", "CHICAGO");
1Vấn đề hiệu năngNói chung, thiết lập MySqlCommand. Thuộc tính ParameterCheck thành true dẫn đến một số mất hiệu suất Khi MySqlCommand. CommandType là "Văn bản" đồng bộ hóa được thực hiện trên máy khách, do đó hiệu suất giảm rất ít Khi MySqlCommand. CommandType là "StoredProcedure", dotConnect cho MySQL gửi các yêu cầu bổ sung đến máy chủ cần thiết để xác định số lượng, tên và các thông tin khác về tham số. Do đó, hiệu suất chủ yếu phụ thuộc vào tốc độ thực hiện các chuyến khứ hồi bổ sung này Để tối ưu hóa nhiều lần thực thi truy vấn, bạn có thể gọi thủ công, bạn có thể gọi MySqlCommand theo cách thủ công. Chuẩn bị phương thức tạo phiên bản đã biên dịch của truy vấn trên máy chủ và buộc đồng bộ hóa. Ngoài ra, đối với dữ liệu truy vấn đã chuẩn bị được truyền và xử lý ở chế độ (nhị phân) khác, nhanh hơn nhiều, đặc biệt khi nó liên quan đến các trường BLOB. Sau khi truy vấn được chuẩn bị, không nên thay đổi MySqlCommand. Text vì MySqlCommand lại không được chuẩn bị. Thực tiễn được đề xuất là thiết lập văn bản và tham số truy vấn, thực thi MySqlCommand. Chuẩn bị phương thức và sau đó chỉ thay đổi giá trị của tham số ví dụVí dụ đầu tiên cho thấy việc sử dụng các tham số có thể linh hoạt và thuận tiện như thế nào. Trong ví dụ này, hai bộ phận Bán hàng mới được thêm vào bảng Dept; Đầu tiên, văn bản truy vấn được gán cho đối tượng MySqlCommand. Khi MySqlCommand. Kiểm tra tham số được đặt thành true dotConnect để MySQL tự động tạo tập hợp các tham số mà bạn có thể truy cập để đặt giá trị. Thứ hai, MySqlCommand. Chuẩn bị cuộc gọi phương thức được ban hành để đạt được hiệu suất tốt nhất. Sau đó, hai hàng được thêm vào bảng, mỗi hàng tham chiếu Bộ phận bán hàng. Sau đó, văn bản truy vấn được thay đổi và một lần nữa MySqlCommand. Bộ sưu tập thông số được xây dựng lại. Lần này nó chỉ có một mục trong đó. Lưu ý rằng tham số này đã được gán một giá trị Int32 trước đó và bây giờ nó chứa một chuỗi. Điều này có thể xảy ra vì bạn không chỉ định MySqlParameter. MySqlType rõ ràng. Sau khi bạn đặt giá trị thành MySqlType hoặc thuộc tính DbType, tính năng tự động phát hiện loại dữ liệu sẽ ngừng hoạt động MySqlCommand mySqlCommand1;
...
mySqlCommand1.CommandText = "INSERT INTO dept (deptno, dname, loc) VALUES (?, ?, ?)";
mySqlCommand1.Parameters.Add("param1", 30);
mySqlCommand1.Parameters.Add("param2", "SALES");
mySqlCommand1.Parameters.Add("param3", "CHICAGO");
2MySqlCommand mySqlCommand1;
...
mySqlCommand1.CommandText = "INSERT INTO dept (deptno, dname, loc) VALUES (?, ?, ?)";
mySqlCommand1.Parameters.Add("param1", 30);
mySqlCommand1.Parameters.Add("param2", "SALES");
mySqlCommand1.Parameters.Add("param3", "CHICAGO");
3Ví dụ sau đây cho thấy cách làm cho chức năng được lưu trữ hoạt động. Chúng tôi sẽ không sử dụng tự động đồng bộ hóa tham số ở đây Giả sử bạn có một thủ tục được lưu trữ chấp nhận tên người dùng, thêm một số chuỗi vào đó, sau đó thêm phiên bản máy chủ và trả về kết quả dưới dạng kết quả hàm. Nó có thể được mô tả như sau MySqlCommand mySqlCommand1;
...
mySqlCommand1.CommandText = "INSERT INTO dept (deptno, dname, loc) VALUES (?, ?, ?)";
mySqlCommand1.Parameters.Add("param1", 30);
mySqlCommand1.Parameters.Add("param2", "SALES");
mySqlCommand1.Parameters.Add("param3", "CHICAGO");
4Để truyền tham số cho hàm và nhận giá trị trả về, bạn có thể sử dụng mã mẫu sau MySqlCommand mySqlCommand1;
...
mySqlCommand1.CommandText = "INSERT INTO dept (deptno, dname, loc) VALUES (?, ?, ?)";
mySqlCommand1.Parameters.Add("param1", 30);
mySqlCommand1.Parameters.Add("param2", "SALES");
mySqlCommand1.Parameters.Add("param3", "CHICAGO");
5MySqlCommand mySqlCommand1;
...
mySqlCommand1.CommandText = "INSERT INTO dept (deptno, dname, loc) VALUES (?, ?, ?)";
mySqlCommand1.Parameters.Add("param1", 30);
mySqlCommand1.Parameters.Add("param2", "SALES");
mySqlCommand1.Parameters.Add("param3", "CHICAGO");
6Ví dụ cuối cùng minh họa cách gọi một thủ tục được lưu trữ trong chế độ tự động đồng bộ hóa Giả sử bạn có một thủ tục được lưu trữ để thêm một nhân viên mới vào bảng Emp. Nó xác định EmpNo phù hợp tiếp theo, dán ngày hiện tại và kiểm tra các tham số đầu vào. Nếu chúng chứa một giá trị hợp lý, quy trình cũng sẽ dán giá trị này; . Đây là cách mã nguồn cho quy trình này có thể trông như thế nào MySqlCommand mySqlCommand1;
...
mySqlCommand1.CommandText = "INSERT INTO dept (deptno, dname, loc) VALUES (?, ?, ?)";
mySqlCommand1.Parameters.Add("param1", 30);
mySqlCommand1.Parameters.Add("param2", "SALES");
mySqlCommand1.Parameters.Add("param3", "CHICAGO");
7Chúng tôi sẽ gọi thủ tục này và truyền cho nó một tham số - EmpName. Vì Kiểm tra tham số là đúng, nên tham số thứ hai sẽ được tạo tại thời điểm gọi phương thức ExecuteNonQuery. Vì vậy, mã này sẽ dẫn đến việc thêm nhân viên mới có tên Roger và mức lương mặc định (1100) Lưu ý rằng trong chế độ tự động đồng bộ hóa, điều duy nhất quan trọng là tên của tham số. Bạn không cần phải quan tâm đến thứ tự tạo và không cần tạo các tham số có giá trị NULL. Nói cách khác, nếu chúng ta cần thêm một nhân viên có tên mặc định nhưng với mức lương cụ thể, chúng ta có thể tạo một đối số duy nhất với ParameterName được đặt thành "Salary" Tham số trong SQL là gì?Các tham số được được sử dụng để trao đổi dữ liệu giữa các thủ tục và hàm được lưu trữ và ứng dụng hoặc công cụ được gọi là thủ tục hoặc hàm được lưu trữ . Tham số đầu vào cho phép người gọi chuyển giá trị dữ liệu cho thủ tục hoặc hàm được lưu trữ.
Làm cách nào để tạo tham số trong MySQL?Đầ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ố
Có bao nhiêu tham số được yêu cầu trong MySQL?MySQL hỗ trợ hai loại tham số. có tên và không có tên. INSERT INTO dept (deptno, dname, loc) VALUES (?, ?, ?) tuyên bố rằng phải có ba tham số để chạy truy vấn đúng cách.
3 chế độ tham số là gì?Các tham số thủ tục PL/SQL có thể có một trong ba chế độ có thể. VÀO, RA hoặc VÀO RA . |
