Hệ quản trị cơ sở dữ liệu

MÔN: HỆ QUẢN TRỊ CSDL GVHD: ThS Đỗ ThịMinh Phụng  Cú pháp điều khiển  IF … [ELSE]  IF EXISTS WHILE … [BREAK] [CONTINUE] CASE … WHEN … END  Stored – Procedure (Thủ tục lưu trữ)  Cú pháp điều khiển IF … [ELSE]  IF EXISTS WHILE … [BREAK] [CONTINUE]  CASE … WHEN … END  Stored – Procedure (Thủ tục lưu trữ) Thiết lập điều kiện cho việc thực thi các câu lệnh T-SQL. Câu lệnh T-SQL sau từ khóa IF và biểu thức chứa điều kiện cần thỏa. Nếu biểu thức điều kiện được thỏa, câu lệnh T-SQL này sẽ được thực thi. Ngoài ra, nếu có từ khóa ELSE (tùy ý) thì câu lệnh sau ELSE sẽ được thực thi, khi biểu thức điều kiện không thỏa.  Ý nghĩa IF (T-SQL)  Cú pháp IF biểu_thức_điều_kiện { câu_lệnh_sql | khối_lệnh_sql } [ ELSE { câu_lệnh_sql | khối_lệnh_sql } ] - Nếu biểu thức điều kiện có chứa câu lệnh SELECT thì câu SELECT phải được đóng ngoặc. - Khối lệnh SQL được định nghĩa bởi BEGIN và END. Nếu không có khối lệnh thì chỉ 1 câu lệnh sau IF hoặc ELSE được thực hiện. - Câu lệnh IF có thể được sử dụng lồng nhau.  Cú pháp điều khiển  IF … [ELSE] IF EXISTS WHILE … [BREAK] [CONTINUE]  CASE … WHEN … END  Stored – Procedure (Thủ tục lưu trữ) IF EXISTS ... là một trong những trường hợp của câu lệnh IF, với biểu thức chứa điều kiện là câu lệnh sql EXISTS IF EXISTS biểu_thức_điều_kiện …  Cú pháp  Ví Dụ  Cú pháp điều khiển  IF … [ELSE]  IF EXISTS WHILE … [BREAK] [CONTINUE]  CASE … WHEN … END  Stored – Procedure (Thủ tục lưu trữ) Thiết lập 1 điều kiện cho sự lặp lại của việc thi hành 1 câu lệnh SQL hay một khối lệnh. Các câu lệnh được chạy trong vòng lặp nhiều lần, miễn là điều kiện đã cho luôn thỏa. Việc thực hiện các câu lệnh trong vòng lặpWHILE, có thể được điều khiển từ bên trong vòng lặp với từ khóa BREAK và CONTINUE.  Ý nghĩa While (T-SQL)  Cú pháp WHILE Biểu_thức_Boolean { câu_lệnh_SQL | khối_lệnh | BREAK | CONTINUE } - Biểu_thức_Boolean: là biểu thức trả về giá trị TRUE hay FALSE. Nếu biểu thức có chứa một lệnh SELECT thì câu lệnh SELECT phải được đóng ngoặc. - câu_lệnh_SQL | khối_lệnh: Là bất kỳ câu lệnh T-SQL đơn nào, hay các câu lệnh được nhóm lại thành một khối lệnh. Để định nghĩa 1 khối lệnh, ta sử dụng các từ khóa điều khiển BEGIN và END. - Từ khóa BREAK : thoát khỏi vòng lặpWHILE trong cùng nhất. Các câu lệnh nằm sau từ khóa END kết thúc của vòng lặp sẽ thực thi bình thường. - Từ khóa CONTINUE : Làm cho vòng lặp WHILE bắt đầu một vòng lặp mới, vòng lặp cũ bị kết thúc nên các câu lệnh sau từ khóa CONTINUE sẽ bị bỏ qua. Sử dụng vòng lặpWHILE với BREAK và CONTINUE Nếu tổng giá của các sản phẩm nhỏ hơn 1000, ta tăng giá mỗi sản phẩm thêm 5%. Nếu sau khi tăng giá, tổng giá ít hơn hay bằng 1250 thì ta lặp lại việc tăng giá. Việc tăng giá kết thúc khi ta có tổng giá cao hơn 1250.  Ví Dụ  Cú pháp điều khiển  IF … [ELSE]  IF EXISTS WHILE … [BREAK] [CONTINUE] CASE … WHEN … END  Stored – Procedure (Thủ tục lưu trữ) CASE (T-SQL) - Đánh giá theo một danh sách các điều kiện và trả về một trong nhiều biểu thức kết quả đã cho. - CASE có thể được sử dụng ở bất kì câu lệnh hay mệnh đề chứa biểu thức nào. Cụ thể, ta có thể sử dụng CASE trong các câu lệnh SELECT, UPDATE, DELETE và SET. Còn các mệnh đề hỗ trợ CASE như IN, WHERE, ORDER BY và HAVING. Biểu thức CASE có 2 dạng : dạng cơ bản và dạng tìm kiếm. + Dạng cơ bản : so sánh 1 biểu thức với một tập các biểu thức đơn giản để chọn ra kết quả. + Dạng tìm kiếm : đánh giá một tập các biểu thức Boolean để chọn ra kết quả.  Ý nghĩa  Phân loại  Cú pháp  Dạng cơ bản: CASE biểu_thức_so_sánh WHEN biểu_thức_điều_kiện_1 THEN biểu_thức_kết_quả_1 WHEN biểu_thức_điều_kiện_2 THEN biểu_thức_kết_quả_2 WHEN biểu_thức_điều_kiện_3 THEN biểu_thức_kết_quả_3 [ ...n ] [ ELSE biểu_thức_kết_quả_else ] END  Dạng tìm kiếm: CASE WHEN biểu_thức_Boolean_đk1 THEN biểu_thức_kết_quả_1 WHEN biểu_thức_Boolean _đk2 THEN biểu_thức_kết_quả_2 WHEN biểu_thức_Boolean _đk3 THEN biểu_thức_kết_quả_3 [ ...n ] [ ELSE biểu_thức_kết_quả_else ] END - Biểu_thức_so_sánh: biểu thức cần đánh giá với các điều kiện ở CASE dạng cơ bản. - Biểu_thức_điều_kiện_x ( x=1,2,3… ): là các biểu thức đơn giản để so sánh với biểu_thức_so_sánh ở CASE dạng cơ bản. Lưu ý, kiểu dữ liệu của biểu_thức_so_sánh và các biểu_thức_điều_kiện_x phải giống nhau hoặc phải có ép kiểu ngầm định. - Biểu_thức_Boolean_điều_kiện_x ( x=1,2,3… ): là các biểu thức đơn giản để đánh giá TRUE/FALSE ở CASE dạng tìm kiếm. - Biểu_thức_kết_quả_x ( x=1,2,3… ): là các biểu thức được trả về khi biểu_thức_so_sánh bằng với 1 trong các biểu_thức_điều_kiện, hoặc 1 trong biểu_thức_Boolean_điều_kiện có giá trị là TRUE. -Biểu_thức_kết_quả_else: là biểu thức trả về khi các so sánh đánh giá biểu thức không trả về TRUE. Nếu tham số này không có và không có so sánh nào trả về TRUE thì CASE trả về NULL. Lưu ý, kiểu dữ liệu của biểu_thức_điều_kiện và các biểu_thức_Boolean_điều_kiện phải giống nhau hoặc phải có ép kiểu ngầm định.  Sử dụng câu lệnh SELECT với CASE dạng cơ bản  Sử dụng câu lệnh SELECT với CASE dạng tìm kiếm  Ví Dụ  Cú pháp điều khiển  IF … [ELSE]  IF EXISTS WHILE … [BREAK] [CONTINUE]  CASE … WHEN … END  Stored – Procedure (Thủ tục lưu trữ) Stored Procedure (SP) 1. Giới thiệu - SP chứa tập hợp các lệnh T-SQL để thực thi 1 nhiệm vụ (task). SP được thiết kế, mã hóa, kiểm tra và biên dịch thành mã thực thi (single execution plan) đặt ngay trên server. Các ứng dụng (application) khi cần thực thi nhiệm vụ, chỉ cần gọi SP. Server sẽ chạy execution plan store procedure đó, nhận các tham số truyền vào, thực thi các câu lệnh SQL bên trong và trả kết quả về lại client. - Các cấu trúc điều khiển (IF, WHILE, FOR) có thể được sử dụng trong thủ tục. - Bên trong store procedure có thể sử dụng các biến như trong ngôn ngữ lập trình nhằm lưu giữ các giá trị tính được tính toán hoặc được truy xuất được từ cơ sở dữ liệu. 2. Ưu điểm a. Performance - tăng hiệu năng làm việc của hệ thống • Đơn giản hoá các thao tác trên cơ sở dữ liệu: nhờ vào khả năng module hoá các thao tác này. •Thực thi nhanh hơn:  Store procedure được phân tích, tối ưu khi tạo ra nên việc thực thi chúng nhanh hơn nhiều so với việc thực hiện một tập rời rạc các câu lệnh SQL tương đương theo cách thông thường.  Cú pháp của các câu lệnh SQL trong store procedure đã được SQL Sever kiểm tra trước khi save nên nó không cần kiểm lại khi thực thi.  User chỉ gởi một câu lệnh đơn và SQL Server chỉ kiểm tra một lần sau đó tạo ra một execute plan và thực thi. Nếu stored procedure được gọi lại nhiều lần thì execute plan sẽ được sử dụng lại nên sẽ làm việc nhanh hơn. • Giảm thiểu sự lưu thông trên mạng:  Khi thực thi một câu lệnh SQL thì SQL Server phải kiểm tra permission xem user gởi câu lệnh đó có được phép thực hiện câu lệnh hay không đồng thời kiểm tra cú pháp rồi mới tạo ra một execute plan và thực thi. Nếu có nhiều câu lệnh như vậy gởi qua network có thể làm giảm đi tốc độ làm việc của server.  SP được xử lý trong Cơ Sở Dữ Liệu và trả về kết quả. Sau đó ứng dụng chỉ cần đọc kết quả này và trình bày cho User. b. Security – Tăng khả năng bảo mật của hệ thống • Thay vì cấp phát quyền trực tiếp cho user trên các câu lệnh SQL và trên các đối tượng CSDL, ta có thể viết một SP để truy xuất dữ liệu và cấp phát quyền cho user sử dụng các SP đã viết sẵn. Nhờ đó, user không thao tác trực tiếp trên các dữ liệu. • Stored procedure có thể được encrypt (mã hóa) để tăng cường tính bảo mật. c. Programming framework Một khi stored procedure được tạo ra nó có thể được sử dụng nhiều lần cho nhiều ứng dụng khác nhau cũng như các client khác nhau.  Ðiều này sẽ làm cho việc bảo trì (maintainability) dễ dàng hơn do việc tách rời giữa business rules (tức là những logic thể hiện bên trong stored procedure) và database.  Nếu có một sự thay đổi nào đó về mặt logic thì ta chỉ việc thay đổi code bên trong stored procedure mà thôi. Những ứng dụng dùng stored procedure này có thể sẽ không cần phải thay đổi mà vẫn tương thích với business rule mới. 3. Phân loại * System SP: Là những stored procedure chứa trong Master database và thường bắt đầu bằng tiếp đầu ngữ sp_ . Các SP này thuộc loại built-in và chủ yếu dùng trong administration và security. * Local SP: Là loại thường dùng nhất. Chúng được chứa trong user database và thường được viết để thực hiện một công việc nào đó.. * Temporary SP: Các stored procedure này được tạo ra trên TempDB của SQL Server nên chúng sẽ bị delete khi connection tạo ra chúng bị cắt đứt hay khi SQL Server down. * Extended SP: Ðây là một loại stored procedure sử dụng một chương trình ngoại vi (external program) vốn được compiled thành một DLL để mở rộng chức năng hoạt động của SQL Server. Loại này thường bắt đầu bằng tiếp đầu ngữ xp_ . * Remote SP : Những stored procedure gọi stored procedure ở server khác. Theo chức năng: 5 loại Theo nguồn gốc: 2 loại -Thủ tục Hệ Thống (System Stored Procedure) - Thủ tục do Người Dùng tạo ra (User – Defined Stored Procedure). Trong nội dung của bài seminar, nhóm chỉ tập trung vào Stored Procedure dạng thông thường – Local Stored Procedure hay còn gọi là User – Defined Stored Procedure 4. Khởi tạo Stored - Procedure CREATE PROCEDURE tên_thủ_tục [; number] [ (danh_sách_tham_số) ] [ WITH { RECOMPILE | ENCRYPTION | RECOMPILE , ENCRYPTION } ] [ FOR REPLICATION ] AS Các_câu_lệnh_của_thủ_tục CREATE PROCEDURE Hoặc CREATE PROC // Rút gọn Có 2 cách để bật khung soạn thảo Stored Procedure trên SQL Server Ví dụ 1 SP đơn giản tên_thủ_tục Tên SP phải tuân theo qui tắc định danh và không được vượt quá 128 ký tự. danh_sách_tham_số Các tham số của thủ tục được khai báo ngay sau tên thủ tục và nếu thủ tục có nhiều tham số thì mỗi tham số phân cách nhau bởi dấu phẩy. Khai báo của mỗi một tham số tối thiểu phải bao gồm hai phần: - tên tham số được bắt đầu bởi dấu@. - kiểu dữ liệu của tham số các_câu_lệnh_của_ thủ_tục Tập hợp các câu lệnh sử dụng trong SP. Các câu lệnh này có thể đặt trong cặp từ khoá BEGIN...END hoặc có thể không.  Các thành phần cơ bản của 1 Stored Procedure  Các thông số không bắt buộc (optional) ;number: kiểu Số Nguyên (integer), để nhóm các SP có cùng tên lại với nhau (ví dụ : orderproc;1, orderproc;2), và sẽ cùng bị xóa bằng lệnh DROP PROC (ví dụ Drop Proc orderproc). { RECOMPILE | ENCRYPTION | RECOMPILE , ENCRYPTION } : Recompile: SP sẽ được biên dịch lại trong lúc Thực Thi (run time). Dùng tùy chọn “recompile” khi ta dùng các giá trị tạm hoặc các giá trị không điển hình (atypical values) mà không muốn ghi đè (override) lên execution plan (bảng thực thi) được trữ sẵn trong bộ nhớ ENCRYPTION: SQL Server sẽ mã hóa (Encrypt) Danh Mục (trong table syscomments) có chứa nội dung của câu lệnh Create Proc. Dùng để ngăn không cho Thủ tục bị xuất (published) thành 1 phần bản sao của SQL Server. FOR REPLICATION: 1 SP được tạo kèm theo tùy chọn “FOR REPLICATION”, sẽ được dùng như 1 bộ lọc SP và chỉ thực thi trong quá trình Replication. Không sử dụng cùng với tùy chọn “WITH RECOMPILE”. - Lưu ý - a/ Tham số với giá trị mặc định: Các tham số được khai báo trong SP có thể nhận các giá trị mặc định. Giá trị mặc định sẽ được gán cho tham số trong trường hợp không truyền đối số cho tham số khi gọi SP Tham số với giá trị mặc định khai báo theo cú pháp sau: @tên_tham_số kiểu_dữ_liệu = giá_trị_mặc_định * Giá trị mặc định có thể là NULL  Ví dụ b/ Sử dụng biến trong thủ tục: Ngoài những tham số được truyền cho SP, bên trong nó còn có thể sử dụng các biến nhằm lưu giữ các giá trị tính toán được hoặc truy xuất được từ cơ sở dữ liệu. Các biến trong SP được khai báo bằng từ khoá DECLARE và gán giá trị thông qua từ khoá SET theo cú pháp sau: DECLARE @tên_biến kiểu_dữ_liệu SET @tên_biến = giá_trị_biến //giá_trị_biến có thể là giá trị cụ thể của 1 loại dữ liệu nào đó (int, nvarchar …) hoặc 1 biểu thức tính toán  Ví dụ 5. Thông tin trả về Stored Procedure Một store procedure có thể sử dụng một hoặc tất cả những khả năng sau để trả về:  Giá trị trả về do người sử dụng định nghĩa: Người sử dụng có thể định nghĩa các giá trị trả về của mình trong các store procedure bằng cách bổ sung một tham số vào câu lệnh RETURN. Tất cả các số nguyên ngoại trừ các giá trị dành riêng cho hệ thống đều có thể được sử dụng.  Các tham số trả về: Khi cả hai câu lệnh CREATE PROCEDURE và EXECUTE chứa mục chọn OUTPUT (hoặc OUT) cho tên một tham số, thủ tục có thể sử dụng một biến để trả về trị của tham số đó đến người gọi. Bằng việc sử dụng từ khoá OUTPUT, bất cứ sự thay đổi nào đến cũng vẫn còn giữ lại sau khi thủ tục được thực hiện, và các biến có thể được sử dụng trong các câu lệnh SQL bổ sung sau đó trong tập lệnh hay thủ tục được gọi. Nếu từ khoá OUTPUT không được sử dụng, việc thay đổi đến tham số sẽ không được giữ lại sau khi kết thúc thực hiện thủ tục. Lưu ý: Nếu chúng ta chỉ định OUTPUT khi thực hiện một thủ tục nhưng tham số tương ứng không được định nghĩa với OUTPUT khi tạo thủ tục thì sẽ bị lỗi. Tuy nhiên nếu ta định nghĩa OUTPUT cho một tham số trong thủ tục nhưng không chỉ định OUTPUT khi thực hiện thì vẫn không bị lỗi (giá trị tham số khi đó sẽ không được trả về).  So sánh khi tham số có và không có OUTPUT đi kèm  Ví dụ 6. Thực thi Stored Procedure EXECUTE tên_Stored_Proc [danh sách các đối số] EXEC tên_Stored_Proc [danh sách các đối số] Rút gọn - Lưu ý - • Số lượng các đối số cũng như thứ tự của chúng phải phù hợp với số lượng và thứ tự của các tham số khi định nghĩa thủ tục. • Thứ tự của các đối số được truyền cho thủ tục có thể không cần phải tuân theo thứ tự của các tham số như khi định nghĩa thủ tục nếu tất cả các đối số được viết dưới dạng: @tên_tham_số = giá_trị Ví dụ thực thi một Stored - Procedure 7. Một số thao tác khác a. Sửa đổi Stored Procedure - ALTER ALTER PROCEDURE tên_thủ_tục [ (danh_sách_tham_số) ] [ WITH { RECOMPILE | ENCRYPTION | RECOMPILE , ENCRYPTION } ] …. AS Các_câu_lệnh_của_thủ_tục  Câu lệnh này sử dụng tương tự như câu lệnh CREATE PROCEDURE. Việc sửa đổi lại một thủ tục đã có không làm thay đổi đến các quyền đã cấp phát trên thủ tục cũng như không tác động đến các thủ tục khác hay trigger phụ thuộc vào thủ tục này. b. Xoá Stored Procedure - DROP c. Đổi tên Stored Procedure – sp_rename DROP PROCEDURE tên_thủ_tục DROP PROC tên_thủ_tục Rút gọn sp_rename ‘tên_thủ_tục’hiện_hành’ , ‘tên_thủ_tục_mới’ 8. Một số quy tắc cần lưu ý khi tạo SP • Số tham số tối đa của một thủ tục là 255. • Số biến cục bộ và toàn cục trong một thủ tục chỉ bị giới hạn bởi khả năng bộ nhớ. • Các thủ tục tạm thời cục bộ (private) và toàn cục (public), tương tự như các bảng tạm thời, có thể được tạo với dấu # và ## đứng trước tên thủ tục. # biểu diễn thủ tục tạm thời cục bộ còn ## biểu diễn thủ tục tạm thời toàn cục. • Câu lệnh CREATE PROCEDURE không thể kết hợp với các câu lệnh SQL khác trong một khối lệnh đơn (single batch). • Bản thân định nghĩa CREATE PROCEDURE có thể bao gồm bất kỳ số lượng cũng như câu lệnh SQL nào ngoại trừ những câu lệnh sau: CREATE PROCEDURE , CREATE TRIGGER CREATE RULE , CREATE VIEW, CREATE DEFAULT • Các đối tượng CSDL khác có thể được tạo bên trong một stored procedure. Ta có thể tham chiếu một đối tượng được tạo trong cùng thủ tục miễn là nó đã được tạo trước khi tham chiếu. • Bên trong một thủ tục, ta không thể tạo một đối tượng, xoá nó và sau đó tạo một đối tượng mới với cùng tên. • Ta có thể tham chiếu các bảng tạm thời bên trong một thủ tục. • Nếu ta thực thi một thủ tục mà gọi đến thủ tục khác, thủ tục được gọi có thể truy cập đến mọi đối tượng ngoại trừ các bảng tạm thời được tạo bởi thủ tục đầu tiên. • Nếu ta tạo một bảng tạm thời riêng (private temporary table) bên trong một thủ tục, bảng tạm thời chỉ tồn tại cho những mục đích của thủ tục đó; nó sẽ mất đi khi thoát ra khỏi thủ tục. 9. Một số dạng Stored Procedure a. Stored Procedure dạng đơn giản b. Stored Procedure có vòng lặp hàm ép kiểu c. Stored Procedure có đệ quy ~ Sinh viên thực hiện ~ Hoàng Thái Hà - 07520098 Nguyễn Thiên Ân - 07520018 Nguyễn Ngọc Phúc - 07520282 Trần Thị Thanh Hương - 07520168