Truy vấn cơ sở dữ liệu

- I.Truy vấn cơ sở dữ liệu:II.
- Procedure: https://quantrimang.com/procedure-thu-tuc- trong-sql-server-159768 -Procedure (Thủ tục) là một chương trình trong cơ sở dữ liệu gồm nhiều câu lệnh mà bạn lưu lại cho những lần sử dụng sau.
- -Trong SQL Server, bạn có thể truyền các tham số vào procedure, tuy nó không trả về một giá trị cụ thể như function (hàm) nhưng cho biết việc thực thi thành công hay thất bại.
- Tạo procedure: https://docs.microsoft.com/en-us/sql/t- sql/statements/create-procedure-transact-sql?view=sql- server-ver15 Tạo một procedure trong SQL server, các thủ tục được lưu trữ tương tự như các thủ tục trong các ngôn ngữ lập trình khác ở chỗ chúng có thể: -Chấp nhận các tham số đầu vào và trả về nhiều giá trị ở dạng tham số đầu ra cho thủ tục (không trả về một giá trị cụ thể như function).
- -Chứa các câu lệnh lập trình thực hiện các hoạt động trong cơ sở dữ liệu, bao gồm việc gọi các thủ tục khác.
- Trả lại giá trị trạng thái cho một thủ tục và cho biết thành công hay thất bại (lý do thất bại.
- trả về tên của cơ sở dữ liệu hiện tại.
- Chúng ta có thể gói câu lệnh dó trong một thủ tục được lưu trữ, vd: CREATE PROC What_DB_is_this AS SELECT DB_NAME() AS ThisDB.
- -Gọi thủ tục lưu trữ với câu lệnh: EXEC What_DB_is_this.
- -Phức tạp hơn một chút là cung cấp một tham số đầu vào để làm cho quy trình linh hoạt hơn.
- Cung cấp số ID cơ sở dữ liệu khi bạn gọi thủ tục.
- EXECUTE AS Clause ] Tham số: schema_name: Tên schema (lược đồ) sở hữu procedure.- Các thủ tục bị ràng buộc bởi lược đồ.
- Nếu tên lược đồ không được chỉ định khi thủ tục được tạo, lược đồ mặc định của người dùng đang tạo thủ tục sẽ tự động được gán. procedure_name: Tên gán cho procedure.- Tên thủ tục phải tuân thủ các quy tắc cho số nhận dạng và phải là duy nhất trong lược đồ.- Tránh sử dụng tiền tố sp_ khi đặt tên cho các thủ tục.
- tiền tố này được SQL server sử dụng để chỉ định các thủ tục hệ thống.
- Việc sử dụng tiền tố có thể khiến mã ứng dụng bị hỏng nếu có quy trình hệ thống trùng tên.- Các thủ tục tạm thời cục bộ hoặc toàn cục có thể được tạo bằng cách sử dụng một dấu.
- trước tên_thủ tục(#procedure_name) cho các thủ tục tạm thời cục bộ và hai dấu hiệu số cho các thủ tục tạm thời toàn cục.
- Thủ tục tạm thời cục bộ chỉ hiển thị đối với kết nối đã tạo ra nó và bị ngắt khi kết nối đó bị đóng.
- Một quy trình tạm thời toàn cầu có sẵn cho tất cả các kết nối và được loại bỏ vào cuối phiên cuối cùng bằng cách sử dụng quy trình.
- Tên tạm thời không thể được chỉ định cho các thủ tục CLR.- Tên đầy đủ của một thủ tục hoặc một thủ tục tạm thời toàn cục, bao gồm cả.
- không được vượt quá 116 ký tự. @parameter: Một hay nhiều tham số được truyền vào hàm.- Một tham số được khai báo trong thủ tục.- Chỉ định tên tham số bằng cách sử dụng dấu tại.
- làm ký tự đầu tiên.- Tên tham số phải tuân thủ các quy tắc cho số nhận dạng.
- Các tham số là cục bộ của thủ tục.
- các tên tham số tương tự có thể được sử dụng trong các thủ tục khác.- Một hoặc nhiều tham số có thể được khai báo.
- Giá trị của mỗi tham số được khai báo phải được cung cấp bởi người dùng khi thủ tục được gọi trừ khi giá trị mặc định cho tham số được xác định hoặc giá trị được đặt bằng tham số khác.
- Nếu một thủ tục chứa các tham số có giá trị bảng và tham số bị thiếu trong lệnh gọi, một bảng trống sẽ được chuyển vào.
- Các tham số chỉ có thể thay thế cho các biểu thức hằng.
- chúng không thể được sử dụng thay thế cho tên bảng, tên cột hoặc tên của các đối tượng cơ sở dữ liệu khác.- Các tham số không thể được khai báo nếu (for reolication) được chỉ định. type_schema_name: Kiểu dữ liệu của schema (nếu có.
- type_schema_name.] data_type: Kiểu dữ liệu của tham số và lược đồ chứa kiểu dữ liệu.- Hướng dẫn thủ tục Transact-SQL: Tất cả các kiểu dữ liệu Transact-SQL có thể được sử dụng làm tham số.
- Bạn có thể sử dụng kiểu bảng do người dùng xác định để tạo các tham số có giá trị bảng.
- Các tham số có giá trị bảng chỉ có thể là tham số INPUT và phải đi kèm với từ khóa READONLY.
- Để biết thêm thông tin, hãy xem Sử dụng Tham số Giá trị Bảng (Công cụ Cơ sở dữ liệu) kiểu dữ liệu con trỏ chỉ có thể là tham số OUTPUT và phải đi kèm với từ khóa VARYING.- Hướng dẫn thủ tục CLR: Tất cả các kiểu dữ liệu SQL Server gốc có mã được quản lý tương đương có thể được sử dụng làm tham số.
- Để biết thêm thông tin về sự tương ứng giữa các kiểu CLR và kiểu dữ liệu hệ thống SQL Server, hãy xem Ánh xạ Dữ liệu Tham số CLR.
- Để biết thêm thông tin về kiểu dữ liệu hệ thống SQL Server và cú pháp của chúng, hãy xem Kiểu dữ liệu (Transact-SQL.
- Không thể sử dụng kiểu dữ liệu con trỏ hoặc giá trị bảng làm tham số.- Nếu kiểu dữ liệu của tham số là kiểu do người dùng xác định CLR, bạn phải có quyền THỰC HIỆN trên kiểu đó.- VARYING Chỉ định tập hợp kết quả được hỗ trợ làm tham số đầu ra.
- Tham số này được xây dựng động bởi thủ tục và nội dung của nó có thể khác nhau.
- Chỉ áp dụng cho các tham số con trỏ.
- Tùy chọn này không hợp lệ cho các thủ tục CLR. Datatype: Kiểu dữ liệu cho @parameter. Default: Giá trị mặc định gán cho @parameter.
- (giá trị mặc định cho một tham số)- Nếu một giá trị mặc định được xác định cho một tham số, thủ tục có thể được thực thi mà không cần chỉ định giá trị cho tham số đó.
- Giá trị mặc định phải là một hằng số hoặc nó có thể là NULL.
- Giá trị hằng số có thể ở dạng ký tự đại diện, nên có thể sử dụng từ khóa LIKE khi truyền tham số vào thủ tục.- Giá trị mặc định được ghi lại trong cột sys.parameters.default chỉ dành cho các thủ tục CLR.
- Cột đó là NULL cho các tham số thủ tục Transact-SQL. OUT/OUTPUT: @parameter là một tham số đầu ra.- Sử dụng tham số OUTPUT để trả về giá trị cho trình gọi của thủ tục.- Các tham số text, ntext và image không thể được sử dụng làm tham số OUTPUT, trừ khi thủ tục là thủ tục CLR\- Tham số đầu ra có thể là trình giữ chỗ con trỏ, trừ khi thủ tục là thủ tục CLR.- Không thể chỉ định kiểu dữ liệu giá trị bảng làm tham số OUTPUT của một thủ tục. READONLY: @parameter không thể bị procedure ghi đè lên.- Chỉ ra rằng tham số không thể được cập nhật hoặc sửa đổi trong phần thân của thủ tục.
- Nếu kiểu tham số là kiểu bảng-giá trị, thì phải chỉ định READONLY. ENCRYPTION: Mã nguồn (source) của procedure sẽ không được lưu trữ dưới dạng text trong hệ thống. RECOMPILE: Truy vấn sẽ không được lưu ở bộ nhớ đệm (cache) cho thủ tục này.- RECOMPILE Chỉ ra rằng Công cụ cơ sở dữ liệu không lưu vào bộ nhớ cache một kế hoạch truy vấn cho thủ tục này, buộc nó phải được biên dịch mỗi khi nó được thực thi. EXECUTE AS clause: Xác định ngữ cảnh bảo mật để thực thi thủ tục. FOR REPLICATION: Procedure đã lưu sẽ chỉ được thực thi trong quá trình replication (nhân bản.
- Một thủ tục được tạo với tùy chọn FOR REPLICATION được sử dụng như một bộ lọc thủ tục và chỉ được thực thi trong quá trình sao chép.
- Các tham số không thể được khai báo nếu FOR REPLICATION được chỉ định.
- FOR REPLICATION không thể được chỉ định cho các thủ tục CLR.
- Tùy chọn RECOMPILE bị bỏ qua đối với các thủ tục được tạo với FOR REPLICATION. {[BEGIN] sql_statement.
- n] [END]} Một hoặc nhiều câu lệnh Transact-SQL bao gồm phần thân của thủ tục.
- Bạn có thể sử dụng các từ khóa BEGIN và END tùy chọn để bao gồm các câu lệnh.VD:CREATE PROCEDURE spNhanvien@nhanvien_name VARCHAR(50) OUTASBEGINDECLARE @nhanvien_id INT;SET @nhanvien_id = 8;IF @nhanvien_id < 10SET @nhanvien_name = 'Smith';ELSESET @nhanvien_name = 'Lawrence';END;Thủ tục trên được gán tên là spNhanvien, có một tham sốlà @nhanvien_name, output của tham số sẽ được dựa trên @nhanvien_id.Sau đó, bạn có thể thực hiện tham chiếu spNhanvien như sau:USE [test]GODECLARE @site_name varchar(50);EXEC FindSite @site_name OUT;PRINT @site_name;GO2.
- Chỉ bỏ quy trình có điều kiện nếu nó đã tồn tại.- schema_name Tên của lược đồ chứa thủ tục.
- Không thể chỉ định tên máy chủ hoặc tên cơ sở dữ liệu.- thủ tục Tên của thủ tục được lưu trữ hoặc nhóm thủ tục được lưu trữ sẽ bị xóa.
- Các thủ tục riêng lẻ trong một nhóm thủ tục được đánh số không thể bị loại bỏ.
- toàn bộ nhóm thủ tục bị loại bỏ.- Thực hành tốt nhất Trước khi loại bỏ bất kỳ thủ tục đã lưu trữ nào, hãy kiểm tra các đối tượng phụ thuộc và sửa đổi các đối tượng này cho phù hợp.
- Việc loại bỏ một thủ tục được lưu trữ có thể khiến các đối tượng và tập lệnh phụ thuộc bị lỗi khi các đối tượng này không được cập nhật.
- Để biết thêm thông tin, hãy xem Xem sự phụ thuộc của một thủ tục được lưu trữ- metadata Để hiển thị danh sách các thủ tục hiện có, hãy truy vấn chế độ xem danh mục sys.objects.
- Ví dụ Ví dụ sau loại bỏ thủ tục được lưu trữ dbo.uspMyProc trong cơ sở dữ liệu hiện tại.
- GO Ví dụ sau đây loại bỏ một số thủ tục được lưu trữ trong cơ sở dữ liệu hiện tại.
- Ví dụ sau loại bỏ thủ tục được lưu trữ dbo.uspMyProc nếu nó tồn tại nhưng không gây ra lỗi nếu thủ tục không tồn tại.
- Tối ưu cơ sở dữ liệu với procedure:https://docs.microsoft.com/en-us/sql/relational-databases/stored-procedures/stored-procedures-database-engine?view=sql-server-ver15 - Việc tối ưu câu lệnh SQL sẽ làm tăng hiệu năng của ứng dụng, giảm thời gian chờ đợi của người dùng cuối.
- Cơ sở dữ liệu là nơi lưu trữ dữ liệu.
- Những thao tác tới cơ sở dữ liệu gồm truy vấn (select), thêm (insert), sửa (update), xoá (delete) dữ liệu.
- Khi dữ liệu nhỏ, tốc độ thực hiện gần như là tức thì, khi dữ liệu đủ lớn thì tốc độ thực hiện trở thành một trở ngại đối với dự án.
- Đôi khi bạn có thể mất hàng giờ để thực hiện một thao tác bất kỳ tới cơ sở dữ liệu.- Bài toán đặt ra lúc này là làm sao giảm thời gian thực hiện mà vẫn đáp ứng được dữ liệu mong muốn.
- Mặc dù hiện nay có rất nhiều phương án để giải quyết bài toán này như sử dụng cache, đưa dữ liệu lên RAM..
- Sử dụng store procedure (SP)Đối với những thao tác được thực hiện thường xuyên và phức tạp, bạn nên sửdụng SP để giảm lượng dữ liệu truyền đến máy chủ (thay vì bạn phải gửi câulệnh sql dài bạn chỉ cần gửi đi tên sp và danh sách tham số).Thủ tục được lưu trữ trong SQL Server là một nhóm gồm một hoặc nhiều câulệnh Transact-SQL hoặc tham chiếu đến phương pháp ngôn ngữ thời gian chạychung (CLR) của Microsoft .NET Framework.
- Các thủ tục giống với các cấutrúc trong các ngôn ngữ lập trình khác vì chúng có thể:Chấp nhận các tham số đầu vào và trả về nhiều giá trị ở dạng tham số đầu ra chochương trình đang gọi.Chứa các câu lệnh lập trình thực hiện các thao tác trong cơ sở dữ liệu.
- Chúngbao gồm gọi các thủ tục khác.Trả lại giá trị trạng thái cho một chương trình đang gọi để cho biết thành cônghay thất bại (và lý do thất bại).Lợi ích của việc sử dụng các thủ tục được lưu trữDanh sách sau đây mô tả một số lợi ích của việc sử dụng các thủ tục.- Giảm lưu lượng mạng máy chủ / máy kháchCác lệnh trong một thủ tục được thực thi dưới dạng một loạt mã.
- Điều này cóthể làm giảm đáng kể lưu lượng mạng giữa máy chủ và máy khách vì chỉ cólệnh gọi thực thi thủ tục được gửi qua mạng.
- Nếu không có quy trình đóng góimã được cung cấp, mọi dòng mã riêng lẻ sẽ phải vượt qua mạng.- Bảo mật mạnh mẽ hơnNhiều người dùng và chương trình khách có thể thực hiện các hoạt động trêncác đối tượng cơ sở dữ liệu cơ bản thông qua một thủ tục, ngay cả khi ngườidùng và chương trình không có quyền trực tiếp trên các đối tượng cơ bản đó.Thủ tục kiểm soát những quy trình và hoạt động nào được thực hiện và bảo vệcác đối tượng cơ sở dữ liệu bên dưới.
- Điều này giúp loại bỏ yêu cầu cấp quyềnở cấp đối tượng riêng lẻ và đơn giản hóa các lớp bảo mật.Mệnh đề EXECUTE AS có thể được chỉ định trong câu lệnh CREATEPROCEDURE để cho phép mạo danh người dùng khác hoặc cho phép ngườidùng hoặc ứng dụng thực hiện các hoạt động cơ sở dữ liệu nhất định mà khôngcần quyền trực tiếp trên các đối tượng và lệnh bên dưới.EXECUTE AS Clause (Transact-SQL)https://docs.microsoft.com/en-us/sql/t-sql/statements/execute-as-clause-transact-sql?view=sql-server-ver15Trong SQL Server, bạn có thể xác định ngữ cảnh thực thi của các mô-đun dongười dùng định nghĩa sau: các hàm (ngoại trừ các hàm có giá trị bảng nộituyến), thủ tục, hàng đợi và trình kích hoạt.Bằng cách chỉ định ngữ cảnh mà mô-đun được thực thi, bạn có thể kiểm soát tàikhoản người dùng nào mà Công cụ cơ sở dữ liệu sử dụng để xác nhận quyềntrên các đối tượng được tham chiếu bởi mô-đun.
- Điều này cung cấp sự linh hoạtvà kiểm soát bổ sung trong việc quản lý các quyền trên chuỗi đối tượng tồn tạigiữa các mô-đun do người dùng xác định và các đối tượng được tham chiếu bởicác mô-đun đó.
- Chỉ người dùng mà mô-đun đang chạy phải có quyền đối với các đốitượng được mô-đun truy cập.Ví dụ: một số hành động như BẢNG TRUNCATE, không có quyền có thể cấp.Để thực thi TRUNCATE TABLE, người dùng phải có quyền ALTER trên bảngđược chỉ định.
- Việc cấp cho người dùng ALTER quyền trên bảng có thể khônglý tưởng vì người dùng sẽ có các quyền hiệu quả vượt quá khả năng cắt bớtbảng.
- Bằng cách kết hợp câu lệnh TRUNCATE TABLE trong một mô-đun vàchỉ định mô-đun đó thực thi với tư cách người dùng có quyền sửa đổi bảng, bạncó thể mở rộng quyền cắt bớt bảng cho người dùng mà bạn cấp quyền THỰCHIỆN trên mô-đun.Khi gọi một thủ tục qua mạng, chỉ có lệnh gọi thực thi thủ tục mới hiển thị.
- Dođó, người dùng độc hại không thể xem tên đối tượng bảng và cơ sở dữ liệu,nhúng các câu lệnh Transact-SQL của riêng họ hoặc tìm kiếm dữ liệu quantrọng.Sử dụng các tham số thủ tục giúp bảo vệ chống lại các cuộc tấn công SQLinjection.
- Vì đầu vào tham số được coi là giá trị chữ và không phải là mã thựcthi, kẻ tấn công sẽ khó chèn lệnh vào (các) câu lệnh Transact-SQL bên trong thủtục và xâm phạm bảo mật.Các thủ tục có thể được mã hóa, giúp làm xáo trộn mã nguồn.
- Để biết thêmthông tin, hãy xem Mã hóa SQL Server.- Sử dụng lại mãMã cho bất kỳ hoạt động cơ sở dữ liệu lặp lại nào là ứng cử viên hoàn hảo đểđóng gói trong các thủ tục.
- Điều này giúp loại bỏ việc viết lại không cần thiếtcủa cùng một mã, giảm sự không nhất quán của mã và cho phép mã được truycập và thực thi bởi bất kỳ người dùng hoặc ứng dụng nào có các quyền cầnthiết.- Bảo trì dễ dàng hơnKhi các ứng dụng khách gọi các thủ tục và giữ các thao tác cơ sở dữ liệu trongtầng dữ liệu, chỉ các thủ tục phải được cập nhật cho bất kỳ thay đổi nào trong cơsở dữ liệu bên dưới.
- Tầng ứng dụng vẫn tách biệt và không phải biết về bất kỳthay đổi nào đối với bố cục, mối quan hệ hoặc quy trình cơ sở dữ liệu.- Cải thiện hiệu suấtTheo mặc định, một thủ tục biên dịch lần đầu tiên nó được thực thi và tạo ra mộtkế hoạch thực thi được sử dụng lại cho các lần thực thi tiếp theo.
- Vì bộ xử lýtruy vấn không phải tạo một kế hoạch mới, nên thường mất ít thời gian hơn đểxử lý thủ tục.Nếu có sự thay đổi đáng kể đối với các bảng hoặc dữ liệu được quy trình thamchiếu, kế hoạch được biên dịch trước thực sự có thể khiến quy trình thực hiệnchậm hơn.
- Trong trường hợp này, việc biên dịch lại quy trình và bắt buộc mộtkế hoạch thực hiện mới có thể cải thiện hiệu suất.Các loại thủ tục được lưu trữ- Đã xác định người dùngThủ tục do người dùng xác định có thể được tạo trong cơ sở dữ liệu do ngườidùng xác định hoặc trong tất cả cơ sở dữ liệu hệ thống ngoại trừ cơ sở dữ liệuTài nguyên.
- Quy trình có thể được phát triển trong Transact-SQL hoặc dướidạng tham chiếu đến phương pháp ngôn ngữ thời gian chạy thông dụng (CLR)của Microsoft .NET Framework.- Tạm thờiThủ tục tạm thời là một dạng thủ tục do người dùng xác định.
- Các thủ tục tạmthời giống như một thủ tục vĩnh viễn, ngoại trừ các thủ tục tạm thời được lưu trữtrong tempdb.
- Có hai loại thủ tục tạm thời: cục bộ và toàn cầu.
- Các thủ tục tạm thờiđịa phương có một dấu số duy nhất.
- chúngchỉ hiển thị với kết nối người dùng hiện tại và chúng sẽ bị xóa khi kết nối bịđóng.
- Các thủ tục tạm thời toàn cục có hai dấu hiệu số.
- chúng được hiển thị cho bất kỳ người dùng nào sau khi chúngđược tạo và chúng sẽ bị xóa vào cuối phiên cuối cùng bằng cách sử dụng quytrình.- Hệ thốngCác thủ tục hệ thống được bao gồm trong SQL Server.
- Chúng được lưu trữ vậtlý trong cơ sở dữ liệu Tài nguyên ẩn, nội bộ và xuất hiện một cách hợp lý tronglược đồ sys của mọi cơ sở dữ liệu do hệ thống và người dùng định nghĩa.
- Ngoàira, cơ sở dữ liệu msdb cũng chứa các thủ tục được lưu trữ hệ thống trong lượcđồ dbo được sử dụng để lập lịch cảnh báo và công việc.
- Bởi vì các thủ tục hệthống bắt đầu bằng tiền tố sp_, chúng tôi khuyên bạn không nên sử dụng tiền tốnày khi đặt tên cho các thủ tục do người dùng xác định.
- Để có danh sách đầy đủcác thủ tục hệ thống, hãy xem Thủ tục lưu trữ hệ thống (Transact-SQL)SQL Server hỗ trợ các thủ tục hệ thống cung cấp giao diện từ SQL Server đếncác chương trình bên ngoài cho các hoạt động bảo trì khác nhau.
- Các thủ tục mởrộng này sử dụng tiền tố xp_.
- Để có danh sách đầy đủ các thủ tục mở rộng, hãyxem Các thủ tục được lưu trữ mở rộng chung (Transact-SQL.
- Người dùng mở rộng xác định Các thủ tục mở rộng cho phép tạo các quy trình bên ngoài bằng ngôn ngữ lậptrình như C.
- Các thủ tục này là các DLL mà một phiên bản của SQL Server cóthể tự động tải và chạy IV.
- Nó có thể xem như một hàm trong các ngôn ngữ lập trình.Trigger là một stored procedure được chạy tự động dựa trên những sự kiện mànó được đăng ký.
- Ví dụ như một trigger có thể được gọi khi có một dòng dữ liệu đượcthêm vào một table, hay một số cột trong bảng bị thay đổi.TRIGGERS PROCEDURESMột Trigger sẽ thực thi tự động mỗi Một Procedure sẽ được thi khi khikhi có các sự kiện INSERT, được gọi thông qua lệnh exec,DELETE, UPDATE xảy ra trong một EXECUTE, hay đơn giảntable.
- procedure_nameKhông thể gọi một trigger trong một Có thể gọi một procedure trong mộttrigger khác.
- procedure khác.Cú pháp: CREATE TRIGGER Cú pháp: CREATE PROCEDURETRIGGER_NAME PROCEDURE_NAMECác mệnh đề trong transaction như Tất cả các mệnh đề trong transactionCOMMIT, ROLLBACK, như COMMIT, ROLLBACK,SAVEPOINT đều không được sử SAVEPOINT đều được sử dụng trongdụng trong trigger procedure.Trigger được sử dụng để duy trì tính Procedure được dùng để thực thi cáctoàn vẹn của dữ liệu bằng cách thực công việc cụ thể được người dùngthi các mã lệnh trên các dòng dữ liệu định nghĩa.mỗi khi xảy ra các hoạt động có thểảnh hưởng đến dữ liệu.Trigger không có giá trị trả về cũng Hoạt động như một hàm, procedurenhư các tham số đầu vào.
- có thể nhận các tham số và trả về các kết quả.https://www.geeksforgeeks.org/difference-between-trigger-and-procedure-in-dbms/

Xem thử không khả dụng, vui lòng xem tại trang nguồn
hoặc xem Tóm tắt