Làm cách nào để lấy dữ liệu từ thủ tục được lưu trữ trong PHP?

Một thủ tục được lưu trữ là một mã SQL đã chuẩn bị sẵn mà bạn có thể lưu, vì vậy mã này có thể được sử dụng lại nhiều lần

Vì vậy, nếu bạn có một truy vấn SQL mà bạn viết đi viết lại nhiều lần, hãy lưu nó dưới dạng một thủ tục được lưu trữ và sau đó chỉ cần gọi nó để thực thi nó

Bạn cũng có thể truyền tham số cho thủ tục được lưu trữ để thủ tục được lưu trữ có thể hoạt động dựa trên (các) giá trị tham số được truyền

Cú pháp thủ tục lưu trữ

TẠO THỦ TỤC tên thủ tục
AS
sql_statement
GO;

Thực hiện một thủ tục lưu trữ

---

Cơ sở dữ liệu demo

Dưới đây là một lựa chọn từ bảng "Khách hàng" trong cơ sở dữ liệu mẫu của Northwind

CustomerIDCustomerNameContactNameAddressCityPostalCodeCountry1Alfreds FutterkisteMaria AndersObere Str. 57Berlin12209Đức2Ana Trujillo Emparedados y heladosAna TrujilloAvda. de la Constitución 2222Mexico D. F. 05021Mexico3Antonio Moreno TaqueríaAntonio MorenoMataderos 2312México D. F. 05023Mexico4Xung quanh Sừng Thomas Hardy120 Hanover Sq. Luân ĐônWA1 1DPUK5Berglunds snbbköpChristina BerglundBerguvsvägen 8LuleåS-958 22Thụy Điển

---

Ví dụ thủ tục lưu trữ

Câu lệnh SQL sau đây tạo một thủ tục được lưu trữ để chọn Khách hàng từ một Thành phố cụ thể với Mã bưu chính cụ thể từ bảng "Khách hàng"

Nói một cách đơn giản, Thủ tục được lưu trữ ("SP") là một thủ tục (được viết bằng SQL và các câu lệnh điều khiển khác) được lưu trữ trong cơ sở dữ liệu có thể được gọi bởi công cụ cơ sở dữ liệu và các ngôn ngữ lập trình được kết nối

Trong hướng dẫn này, chúng ta sẽ xem cách tạo một SP trong MySQL và thực thi nó trong máy chủ MySQL và trong PHP

Ghi chú. Chúng tôi sẽ không đề cập đến toàn bộ khía cạnh của SPhere. Tài liệu MySQL chính thức phải luôn là nơi để tham khảo

SP cũng có sẵn trong các máy chủ cơ sở dữ liệu phổ biến khác (ví dụ Postgre) vì vậy những gì chúng ta sẽ thảo luận ở đây cũng có thể được áp dụng cho những máy chủ đó

Tại sao Thủ tục lưu trữ được khuyến nghị

Hầu hết chúng ta đều khá quen thuộc với việc thiết lập thông thường để xây dựng một ứng dụng cơ sở dữ liệu. tạo cơ sở dữ liệu, tạo bảng, thiết lập chỉ mục, CRUD dữ liệu, đưa ra truy vấn từ phía máy khách và xử lý thêm nếu cần

Quy trình công việc đó hoạt động tốt trong hầu hết các trường hợp nhưng có một khía cạnh quan trọng của lập trình cơ sở dữ liệu bị thiếu. thủ tục lưu trữ

Có ít nhất bốn lợi thế tôi có thể nghĩ đến khi sử dụng SP trong ứng dụng cơ sở dữ liệu

Thứ nhất, nó làm giảm lưu lượng mạng và chi phí hoạt động. Trong một ứng dụng web cơ sở dữ liệu PHP điển hình, có bốn lớp

• Lớp máy khách, thường là trình duyệt web. Nó nhận các tương tác của người dùng và trình bày dữ liệu trong giao diện người dùng
• Lớp máy chủ web, xử lý và gửi yêu cầu của người dùng và gửi lại phản hồi cho lớp máy khách
• Lớp PHP, xử lý tất cả các phiên dịch PHP, thực hiện logic ứng dụng và tạo phần phản hồi PHP
• Lớp cơ sở dữ liệu, xử lý tất cả các truy vấn cơ sở dữ liệu, bao gồm nhưng không giới hạn ở truy vấn

  CREATE USER 'tr'@'localhost' IDENTIFIED BY 'mypass';

  6, câu lệnh

  CREATE USER 'tr'@'localhost' IDENTIFIED BY 'mypass';

  7, v.v.

Trong một môi trường điển hình, các lớp này rất có thể sẽ không nằm trên một máy duy nhất, thậm chí có thể không nằm trong một mạng đối với các ứng dụng lớn hơn

Mặc dù tốc độ mạng đã tăng lên rất nhiều trong vài năm qua, nhưng nó vẫn chậm nhất và không đáng tin cậy nhất so với các cách truyền dữ liệu khác (bộ đệm CPU, bộ nhớ, đĩa cứng, v.v.). Vì vậy, để tiết kiệm băng thông và tăng cường độ mạnh mẽ, đôi khi nên thực hiện nhiều xử lý và logic hơn ở phía máy chủ (cụ thể là máy chủ MySQL) và có ít dữ liệu được truyền qua mạng hơn

Thứ hai, nó cải thiện hiệu suất. SP được lưu trữ và chạy trực tiếp trong máy chủ MySQL. Nó có thể được biên dịch trước và phân tích bởi máy chủ cơ sở dữ liệu. Điều này hoàn toàn khác so với việc đưa ra cùng một truy vấn từ phía máy khách, nơi truy vấn sẽ được trình điều khiển cơ sở dữ liệu phân tích cú pháp, được phân tích và tối ưu hóa (nếu có thể) mỗi khi câu lệnh truy vấn được gọi. Điều này bằng cách nào đó khá giống với việc thực thi ngôn ngữ được giải thích (ở cuối máy khách) và thực thi ngôn ngữ được biên dịch (ở cuối máy chủ cơ sở dữ liệu). Và chúng tôi biết một chương trình được biên dịch sẽ chạy nhanh hơn

Thứ ba, Viết một lần và Thực thi ở mọi nơi. SQL là tiêu chuẩn và hoàn toàn độc lập với nền tảng 100%. Nó chỉ dựa vào máy chủ cơ sở dữ liệu. Xem xét có bao nhiêu ngôn ngữ/lib khác nhau mà chúng ta có thể sử dụng để xử lý cơ sở dữ liệu. Sẽ tăng hiệu quả khi đặt việc truy xuất và xử lý dữ liệu ở cuối máy chủ thay vì viết cùng một logic xử lý theo một cú pháp khác do tất cả các ngôn ngữ/lib này cung cấp, nếu logic xử lý dữ liệu được sử dụng phổ biến như vậy

Cuối cùng nhưng không kém phần quan trọng, SP là một khía cạnh cơ bản của bảo mật cơ sở dữ liệu

Hãy xem xét một thiết lập cơ sở dữ liệu đơn giản. Trong một hệ thống thông tin nguồn nhân lực (HRIS), thật hợp lý khi giả định rằng tồn tại một bảng chứa thông tin lương của từng nhân viên. Một nhân viên nhân sự nên có quyền lấy một số số liệu ra khỏi bảng này. tổng lương, lương trung bình, v.v nhưng nhân viên này không nên xem lương chi tiết của từng nhân viên vì thông tin này sẽ quá nhạy cảm và chỉ nên xem cho một số ít

Chúng tôi biết MySQL có quyền kiểm soát đặc quyền toàn diện. Trong trường hợp này, rõ ràng là chúng tôi thậm chí không thể cấp đặc quyền

CREATE USER 'tr'@'localhost' IDENTIFIED BY 'mypass';

6 cho nhân viên nhân sự này (nếu chúng tôi làm như vậy, có nghĩa là anh ấy/cô ấy có thể xem mức lương chi tiết của mọi người). Nhưng nếu anh ấy/cô ấy không thể truy cập vào bảng

CREATE USER 'tr'@'localhost' IDENTIFIED BY 'mypass';

9, thì làm cách nào để nhân viên này có được thông tin tổng hợp liên quan đến

CREATE USER 'tr'@'localhost' IDENTIFIED BY 'mypass';

9?

Câu trả lời là sử dụng Thủ tục được lưu trữ để trả về thông tin được yêu cầu và cấp cho nhân viên đó đặc quyền

CREATE USER 'tr'@'localhost' IDENTIFIED BY 'mypass';

1. (Để có danh sách chi tiết và giải thích về các đặc quyền của MySQL, vui lòng tham khảo tài liệu chính thức. Liên kết ở đây là dành cho MySQL 5. 6. Vui lòng thay thế 5. 6 với phiên bản bạn đang sử dụng. )

SP hiện là cầu nối giữa người dùng (nhân viên nhân sự của chúng tôi) và bảng (

CREATE USER 'tr'@'localhost' IDENTIFIED BY 'mypass';

9), mà người dùng không có quyền truy cập trực tiếp

Đó là nó. Với SP, chúng tôi có thể yêu cầu người dùng hoàn thành nhiệm vụ mà không ảnh hưởng đến bảo mật cơ sở dữ liệu (và chính sách nhân sự)

Hạn chế của việc sử dụng thủ tục lưu trữ

Sau khi đặt tên cho tất cả các ưu điểm của việc sử dụng SP, chúng ta cần hiểu rõ về một số nhược điểm và xem liệu có cách nào để cải thiện hay không.

• Không kiểm soát phiên bản trên chính SP. Khi một SP được sửa đổi, nó được sửa đổi, không có dấu vết lịch sử nào có thể được lưu giữ ở phía máy chủ. Nó có thể tạo ra một số sự thất vọng khi người dùng muốn khôi phục thay đổi. Đề xuất của tôi là viết SP ở phía máy khách của bạn và đặt nó dưới sự kiểm soát phiên bản. Khi SP đã sẵn sàng, bạn có thể dễ dàng sao chép mã vào, chẳng hạn như MySQL Workbench và tạo mã ở phía máy chủ. Bằng cách đó, chúng tôi có thể kiểm soát phiên bản ở một mức độ nào đó
• Không có cách nào dễ dàng để "đồng bộ hóa" các thay đổi được áp dụng và buộc mọi người sử dụng phiên bản mới nhất, đặc biệt là khi mỗi thành viên trong nhóm có cơ sở dữ liệu cục bộ của riêng mình cho mục đích phát triển và thử nghiệm. Kiểm soát phiên bản có thể là giải pháp nhưng vẫn yêu cầu can thiệp thủ công bằng cách cập nhật bản sao cục bộ của SP trong máy chủ db cục bộ. Một cách khác là sử dụng "mocking". Các thành viên trong nhóm có thể được phân chia để ít nhất một người sẽ tập trung vào việc bảo trì SP và thực hiện lệnh gọi SP trong mã. Tất cả những người khác cần kết quả từ SP có thể phát triển và kiểm tra phần của họ bằng cách sử dụng các đối tượng mô phỏng, tôi. e, luôn giả sử cuộc gọi "giả mạo" tới SP sẽ trả về kết quả mong muốn. Trong giai đoạn sau, việc hợp nhất có thể được thực hiện để loại bỏ mã giả
• Khó sao lưu/xuất. SP ở phía máy chủ. Các nhà phát triển thông thường sẽ chỉ có các đặc quyền cơ bản (

  CREATE USER 'tr'@'localhost' IDENTIFIED BY 'mypass';

  6,

  CREATE USER 'tr'@'localhost' IDENTIFIED BY 'mypass';

  1, v.v.) và không có quyền quản trị để sao lưu và xuất. Theo một cách nào đó, tôi sẽ không gọi đó là nhược điểm mà là khía cạnh cơ bản của bảo mật db. Không có cách nào, và không nên vượt qua điều này. Đề xuất rằng, trong một nhóm, một quản trị viên DB chuyên trách sẽ được chỉ định để thực hiện các công việc như vậy. Một bản sao lưu db thông thường cũng có thể phục vụ mục đích sao lưu/xuất (và nhập)

Tạo thủ tục lưu trữ trong MySQL

Vì SP được lưu trữ trong máy chủ, nên tạo SP trực tiếp trong máy chủ, tôi. e. , không phải bằng cách sử dụng PHP hoặc các ngôn ngữ lập trình khác để đưa ra các lệnh SQL để làm như vậy

Hãy xem cách tạo SP trong máy chủ MySQL, tạo người dùng và áp dụng các đặc quyền và chạy (với tư cách là người dùng đó) SP để xem kết quả có đúng không. Trong môi trường làm việc của tôi, tôi đang sử dụng MySQL Workbench. Các công cụ khác có sẵn (ví dụ: PHPMyAdmin), vì vậy hãy thoải mái sử dụng các công cụ phù hợp nhất với bạn

Giả sử chúng ta có một bảng như thế này

CREATE TABLE `salary` (
  `empid` int(11) NOT NULL,
  `sal` int(11) DEFAULT NULL,
  PRIMARY KEY (`empid`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

Và đối với nhân viên nhân sự cần lấy thông tin tổng hợp về lương (trung bình, tối đa, tối thiểu, v.v.) từ bảng đó, trước tiên chúng tôi tạo một người dùng

CREATE USER 'tr'@'localhost' IDENTIFIED BY 'mypass';

5 như thế này

CREATE USER 'tr'@'localhost' IDENTIFIED BY 'mypass';

và đối với người dùng này, chúng tôi chỉ cấp đặc quyền

CREATE USER 'tr'@'localhost' IDENTIFIED BY 'mypass';

1 cho lược đồ nơi chứa bảng

CREATE USER 'tr'@'localhost' IDENTIFIED BY 'mypass';

9

grant execute on hris.*  to tr@`%`

Chúng tôi có thể xác minh đặc quyền cần thiết được cấp bằng cách truy cập "Người dùng và Đặc quyền" trong MySQL Bench

Bây giờ hãy tạo SP như thế này

DELIMITER $$

CREATE PROCEDURE `avg_sal`(out avg_sal decimal)
BEGIN
    select avg(sal) into avg_sal from salary;

END

GHI CHÚ. Tất cả các hoạt động trên sẽ yêu cầu vai trò quản trị viên trong máy chủ MySQL

Sau khi ban hành lệnh trong MySQL Workbench,

CREATE USER 'tr'@'localhost' IDENTIFIED BY 'mypass';

8 SP sẽ được tạo và sẵn sàng để được gọi. Nó sẽ trả về mức lương trung bình của bảng

CREATE USER 'tr'@'localhost' IDENTIFIED BY 'mypass';

9

Để kiểm tra xem người dùng

grant execute on hris.*  to tr@`%`

0 có thực sự chạy SP nhưng không thể truy cập bảng

CREATE USER 'tr'@'localhost' IDENTIFIED BY 'mypass';

9 hay không, chúng ta có thể chuyển đổi vai trò bằng cách đăng nhập vào máy chủ MySQL bằng người dùng

grant execute on hris.*  to tr@`%`

0. Nó có thể được thực hiện bằng cách tạo một kết nối khác trong MySQL Workbench bằng một cặp người dùng/mật khẩu khác

Sau khi đăng nhập với tên

grant execute on hris.*  to tr@`%`

0, điều đầu tiên chúng tôi nhận thấy là người dùng sẽ không thể nhìn thấy bất kỳ bảng nào và chỉ có thể nhìn thấy SP

Rõ ràng là người dùng

grant execute on hris.*  to tr@`%`

0 sẽ không thể chọn bất cứ thứ gì từ bất kỳ bảng nào (do đó không thể xem số tiền lương chi tiết của bảng

CREATE USER 'tr'@'localhost' IDENTIFIED BY 'mypass';

9) nhưng anh ấy/cô ấy có thể thực hiện SP chúng tôi vừa tạo và nhận được mức lương trung bình

CREATE USER 'tr'@'localhost' IDENTIFIED BY 'mypass';

3

Mức lương trung bình sẽ được hiển thị

Cho đến nay, chúng tôi đã hoàn thành tất cả các bước chuẩn bị để tạo người dùng, cấp đặc quyền, tạo SP và kiểm tra hoạt động của SP. Tiếp theo, chúng tôi sẽ trình bày cách gọi SP đó từ bên trong PHP

Gọi thủ tục lưu trữ từ PHP

Với PDO, việc gọi một SP rất đơn giản. Mã PHP như sau

CREATE USER 'tr'@'localhost' IDENTIFIED BY 'mypass';

4

grant execute on hris.*  to tr@`%`

6 sẽ chứa mức lương trung bình của bảng

CREATE USER 'tr'@'localhost' IDENTIFIED BY 'mypass';

9. Người dùng có thể xử lý thêm đầu ra bằng PHP ngay bây giờ

Phần kết luận

Trong bài viết này, chúng tôi đã xem xét thành phần bị lãng quên từ lâu trong cơ sở dữ liệu MySQL. thủ tục lưu trữ. Những lợi thế khi sử dụng SP là rõ ràng và hãy để tôi nhấn mạnh lại. Thủ tục được lưu trữ cho phép chúng tôi áp dụng kiểm soát truy cập cơ sở dữ liệu mạnh mẽ hơn đối với một số dữ liệu nhất định để phù hợp với yêu cầu kinh doanh

Chúng tôi cũng minh họa các bước cơ bản để tạo Thủ tục được lưu trữ, tạo người dùng và gán đặc quyền cũng như cách gọi nó trong PHP

Bài viết này không đề cập đến toàn bộ phạm vi của Thủ tục lưu trữ. Một số khía cạnh quan trọng như tham số đầu vào/đầu ra, câu lệnh điều khiển, con trỏ, cú pháp đầy đủ, v.v. không được thảo luận trong bài viết ngắn này

Nếu bạn cảm thấy hứng thú, vui lòng để lại nhận xét của bạn tại đây và chúng tôi sẽ rất vui khi mang đến những bài viết chuyên sâu hơn về khía cạnh hữu ích và mạnh mẽ này của MySQL

Chia sẻ bài viết này

Taylor Ren

Taylor là một nhà phát triển ứng dụng web và máy tính để bàn tự do sống ở Tô Châu, miền Đông Trung Quốc. Bắt đầu từ series công cụ phát triển Borland (C++Builder, Delphi), xuất bản sách trên InterBase, được chứng nhận Borland Expert năm 2003, anh chuyển sang phát triển web với cấu hình LAMP điển hình. Sau đó, anh ấy bắt đầu làm việc với jQuery, Symfony, Bootstrap, Dart, v.v.

Làm cách nào chúng ta có thể truy xuất dữ liệu từ thủ tục được lưu trữ?

Làm cách nào để gọi một thủ tục được lưu trữ trong PHP?

Làm cách nào tôi có thể tìm nạp dữ liệu từ cơ sở dữ liệu trong PHP?

Làm cách nào để sử dụng SP trong PHP?