Cơ sở dữ liệu - Mã kịch bản, hàm và thủ tục

9/5/2016 1 MÃ KỊCH BẢN, HÀM VÀ THỦ TỤC CƠ SỞ DỮ LIỆU Ths. Trịnh Hoàng Nam, namth@buh.edu.vn C ơ s ở d ữ l i ệ u – T ổ n g q u a n h ệ t h ố n g c ơ s ở d ữ l i ệ u Ths. Trịnh Hoàng Nam, namth@buh.edu.vn C ơ s ở d ữ l i ệ u – T ổ n g q u a n h ệ t h ố n g c ơ s ở d ữ l i ệ u MÃ KỊCH BẢN Các câu lệnh SQL riêng lẻ Gom thành các nhóm lệnh (batch) Nhóm câu lệnh 1 GO Nhóm câu lệnh n GO Mã kịch bản Mỗi nhóm câu lệnh được biên dịch như một lệnh đơn Mỗi nhóm câu lệnh kết thúc bởi lệnh GO 9/5/2016 2 Ths. Trịnh Hoàng Nam, namth@buh.edu.vn C ơ s ở d ữ l i ệ u – T ổ n g q u a n h ệ t h ố n g c ơ s ở d ữ l i ệ u MÃ KỊCH BẢN  Các câu lệnh xử lý mã kịch bản Từ khóa Mô tả USE Thay đổi cơ sở dữ liệu hiện thời thành cơ sở dữ liệu được chỉ định trong câu lệnh PRINT Trả về thông báo tới client DECLARE Định nghĩa biến cục bộ SET Gán giá trị cho biến cục bộ hoặc biến theo phiên làm việc EXEC Thực thi câu lệnh SQL hoặc stored procedure động Ths. Trịnh Hoàng Nam, namth@buh.edu.vn C ơ s ở d ữ l i ệ u – T ổ n g q u a n h ệ t h ố n g c ơ s ở d ữ l i ệ u MÃ KỊCH BẢN  Các câu lệnh điều khiển luồng thực thi Từ khóa Mô tả IFELSE Điều khiển luồng thực thi dựa vào điều kiện BEGINEND Định nghĩa khối câu lệnh WHILE Lặp lại các câu lệnh chừng nào điều kiện được chỉ định là đúng BREAK Thoát khỏi vòng lặp WHILE trong cùng CONTINUE Quay về điểm bắt đầu vòng lặp WHILE TRYCATCH Điều khiển luồng thực thi khi có lỗi xảy ra GOTO Thay đổi luồng thực thi vô điều kiện RETURN Thoát vô điều kiện Ths. Trịnh Hoàng Nam, namth@buh.edu.vn C ơ s ở d ữ l i ệ u – T ổ n g q u a n h ệ t h ố n g c ơ s ở d ữ l i ệ u VÍ DỤ (1) 9/5/2016 3 Ths. Trịnh Hoàng Nam, namth@buh.edu.vn C ơ s ở d ữ l i ệ u – T ổ n g q u a n h ệ t h ố n g c ơ s ở d ữ l i ệ u MÃ KỊCH BẢN Lưu trữ giá trị trong mã kịch bản Biến vô hướng Biến bảng Bảng tạm Giá trị đơn (giá trị dữ liệu chuẩn) Tập kết quả (dữ liệu bảng) Ths. Trịnh Hoàng Nam, namth@buh.edu.vn C ơ s ở d ữ l i ệ u – T ổ n g q u a n h ệ t h ố n g c ơ s ở d ữ l i ệ u BẢNG TẠM  Dùng để lưu trữ một tập kết quả trả về (dữ liệu dưới dạng bảng) từ một câu lệnh SELECT  Hai loại bảng tạm  Bảng tạm cục bộ  Tên bắt đầu bằng dấu #  Tồn tại trong phiên làm việc hiện tại. Trong phạm vi cửa sổ soạn thảo truy vấn tạo ra bảng đó  Bảng tạm toàn cục  Tên bắt đầu bằng dấu ##  Tồn tại trong tất cả các phiên làm việc  Bảng tạm được lưu trong CSDL tempdb  Để xóa một bảng tạm, sử dụng lệnh DROP TABLE Ths. Trịnh Hoàng Nam, namth@buh.edu.vn C ơ s ở d ữ l i ệ u – T ổ n g q u a n h ệ t h ố n g c ơ s ở d ữ l i ệ u VÍ DỤ (2) 9/5/2016 4 Ths. Trịnh Hoàng Nam, namth@buh.edu.vn C ơ s ở d ữ l i ệ u – T ổ n g q u a n h ệ t h ố n g c ơ s ở d ữ l i ệ u Ths. Trịnh Hoàng Nam, namth@buh.edu.vn C ơ s ở d ữ l i ệ u – T ổ n g q u a n h ệ t h ố n g c ơ s ở d ữ l i ệ u THỦ TỤC (PROCEDURE)  Procedure là một tập các câu lệnh  Thực hiện một nhiệm vụ cụ thể  Được đặt tên và lưu trữ dưới dạng đã biên dịch  Các thành phần  Biến  Cấu trúc điều khiển  Tên tối đa 128 ký tự Ths. Trịnh Hoàng Nam, namth@buh.edu.vn C ơ s ở d ữ l i ệ u – T ổ n g q u a n h ệ t h ố n g c ơ s ở d ữ l i ệ u VÍ DỤ 9/5/2016 5 Ths. Trịnh Hoàng Nam, namth@buh.edu.vn C ơ s ở d ữ l i ệ u – T ổ n g q u a n h ệ t h ố n g c ơ s ở d ữ l i ệ u THỦ TỤC (PROCEDURE) ALTER PROCEDURE [] [WITH [RECOMPILE] [, ENCRYPTION] [, ]] AS DROP PROCEDURE CREATE PROCEDURE [] [WITH [RECOMPILE] [, ENCRYPTION] [, ]] AS Ths. Trịnh Hoàng Nam, namth@buh.edu.vn C ơ s ở d ữ l i ệ u – T ổ n g q u a n h ệ t h ố n g c ơ s ở d ữ l i ệ u THỦ TỤC (PROCEDURE)  Hai loại tham số  Tham số đầu vào  Tham số bắt buộc  Tham số tùy chọn  Tham số đầu ra  Truyền trị cho tham số  Truyền theo tên  Truyền theo vị trí Ths. Trịnh Hoàng Nam, namth@buh.edu.vn C ơ s ở d ữ l i ệ u – T ổ n g q u a n h ệ t h ố n g c ơ s ở d ữ l i ệ u VÍ DỤ 9/5/2016 6 Ths. Trịnh Hoàng Nam, namth@buh.edu.vn C ơ s ở d ữ l i ệ u – T ổ n g q u a n h ệ t h ố n g c ơ s ở d ữ l i ệ u THỦ TỤC (PROCEDURE)  Làm việc với giá trị trả về  Sử dụng tham số OUTPUT để trả về giá trị có kiểu dữ liệu bất kì  Sử dụng hàm RETURN để trả về giá trị số nguyên Ths. Trịnh Hoàng Nam, namth@buh.edu.vn C ơ s ở d ữ l i ệ u – T ổ n g q u a n h ệ t h ố n g c ơ s ở d ữ l i ệ u VÍ DỤ Ths. Trịnh Hoàng Nam, namth@buh.edu.vn C ơ s ở d ữ l i ệ u – T ổ n g q u a n h ệ t h ố n g c ơ s ở d ữ l i ệ u VÍ DỤ 9/5/2016 7 Ths. Trịnh Hoàng Nam, namth@buh.edu.vn C ơ s ở d ữ l i ệ u – T ổ n g q u a n h ệ t h ố n g c ơ s ở d ữ l i ệ u Ths. Trịnh Hoàng Nam, namth@buh.edu.vn C ơ s ở d ữ l i ệ u – T ổ n g q u a n h ệ t h ố n g c ơ s ở d ữ l i ệ u HÀM (FUNCTION)  Function là một tập các câu lệnh  Thực hiện một nhiệm vụ cụ thể  Được đặt tên và lưu trữ dưới dạng đã biên dịch  Trả về kết quả là một giá trị (lệnh return)  Các thành phần  Biến  Cấu trúc điều khiển  Tên tối đa 128 ký tự Ths. Trịnh Hoàng Nam, namth@buh.edu.vn C ơ s ở d ữ l i ệ u – T ổ n g q u a n h ệ t h ố n g c ơ s ở d ữ l i ệ u VÍ DỤ 9/5/2016 8 Ths. Trịnh Hoàng Nam, namth@buh.edu.vn C ơ s ở d ữ l i ệ u – T ổ n g q u a n h ệ t h ố n g c ơ s ở d ữ l i ệ u HÀM (FUNCTION)  Chú ý  Không có tham số đầu ra (output)  Không chứa các lệnh Insert, Update, Delete liên quan đến bảng (khung nhìn) có sẵn  Có thể tạo bảng, bảng tạm, biến bảng Ths. Trịnh Hoàng Nam, namth@buh.edu.vn C ơ s ở d ữ l i ệ u – T ổ n g q u a n h ệ t h ố n g c ơ s ở d ữ l i ệ u HÀM (FUNCTION)  Hàm giá trị vô hướng  Trả về giá trị đơn  Hàm giá trị bảng đơn giản  Trả về bảng, kết quả của một câu lệnh SELECT đơn  Hàm giá trị bảng phức tạp  Trả về bảng, kết quả của nhiều câu lệnh Ths. Trịnh Hoàng Nam, namth@buh.edu.vn C ơ s ở d ữ l i ệ u – T ổ n g q u a n h ệ t h ố n g c ơ s ở d ữ l i ệ u HÀM GIÁ TRỊ VÔ HƯỚNG CREATE FUNCTION [.] ([@ [= ]] [, ]) RETURNS [WITH [ENCRYPTION] [, SCHEMABINDING] [, ]] [AS] BEGIN [] RETURN END 9/5/2016 9 Ths. Trịnh Hoàng Nam, namth@buh.edu.vn C ơ s ở d ữ l i ệ u – T ổ n g q u a n h ệ t h ố n g c ơ s ở d ữ l i ệ u VÍ DỤ Ths. Trịnh Hoàng Nam, namth@buh.edu.vn C ơ s ở d ữ l i ệ u – T ổ n g q u a n h ệ t h ố n g c ơ s ở d ữ l i ệ u HÀM GIÁ TRỊ BẢNG ĐƠN GIẢN CREATE FUNCTION [.] ([@ [= ]] [, ]) RETURNS TABLE [WITH {ENCRYPTION|SCHEMABINDING|ENCRYPTION,SCHEMABINDING}] [AS] RETURN [(] [)] Ths. Trịnh Hoàng Nam, namth@buh.edu.vn C ơ s ở d ữ l i ệ u – T ổ n g q u a n h ệ t h ố n g c ơ s ở d ữ l i ệ u VÍ DỤ 9/5/2016 10 Ths. Trịnh Hoàng Nam, namth@buh.edu.vn C ơ s ở d ữ l i ệ u – T ổ n g q u a n h ệ t h ố n g c ơ s ở d ữ l i ệ u HÀM GIÁ TRỊ BẢNG PHỨC TẠP CREATE FUNCTION [] ([@ [= ]] [,]) RETURNS @ TABLE ( [] [, []]) [WITH [ENCRYPTION] [, SCHEMABINDING] [, ]] [AS] BEGIN RETURN END Ths. Trịnh Hoàng Nam, namth@buh.edu.vn C ơ s ở d ữ l i ệ u – T ổ n g q u a n h ệ t h ố n g c ơ s ở d ữ l i ệ u VÍ DỤ