›
›

Bài giảng Hệ quản trị cơ sở dữ liệu - Nguyễn Thị Thúy Loan

pdf 97 trang hapham 2740
Download
Bạn đang xem 20 trang mẫu của tài liệu "Bài giảng Hệ quản trị cơ sở dữ liệu - Nguyễn Thị Thúy Loan", để tải tài liệu gốc về máy bạn click vào nút DOWNLOAD ở trên

Tài liệu đính kèm:

  • pdfbai_giang_he_quan_tri_co_so_du_lieu_nguyen_thi_thuy_loan.pdf

Nội dung text: Bài giảng Hệ quản trị cơ sở dữ liệu - Nguyễn Thị Thúy Loan

  1. 1 Cách đánh giá HỆ QUẢN TRỊ CSDL . Thực hành: 30% . Bài tập: 20% ThS. Nguyễn Thị Thúy Loan . Lý thuyết: 50% Nguyễn Thị Thúy Loan Trang 2 2/12/2011 Tài liệu tham khảo Nội dung [1]. Bài giảng của Giảng viên khoa Công nghệ Thông tin. . Tổng quan về SQL Server. [2]. Giáo trình SQL Server 200, Nguyễn Thiên Bằng chủ biên, NXB Lao động – xã hội. . Các đối tượng trong cơ sở dữ liệu. [3]. Lập trình SQL căn bản, Đoàn Thiện Ngân chủ biên, NXB Lao . Lập trình với cơ sở dữ liệu. động – xã hội. [4]. Lập trình ứng dụng chuyên nghiệp SQL – Server 2000, Phạm . Store Procedure (thủ tục). Hữu Khang chủ biên, NXB Lao động – xã hội . Trigger & Function. [5]. and-Computer-Science/index.htm/ . Backup anh restore Nguyễn Thị Thúy Loan Trang 4 2/12/2011
  2. 2 Chương I NỘI DUNG . SQL Server là gì? TỔNG QUAN VỀ . Lịch sử phát triển. SQL SERVER . Mô hình Client/ Server . Các thành phần của SQL Server. ThS. Nguyễn Thị Thúy Loan Nguyễn Thị Thúy Loan Trang 6 2/12/2011 SQL Server là gì? Phần mềm quản trị dữ liệu đáng tin cậy, có tính mở cao. . Dễ dàng xây dựng một CSDL quan hệ lớn. SQL Server là gì? . Giải quyết tình trạng va chạm giữa các user khi cùng truy xuất một dữ liệu tại cùng một thời điểm. . Bảo đảm các RBTV trên CSDL. . Bảo vệ an toàn dữ liệu. . Truy vấn dữ liệu nhanh. Nguyễn Thị Thúy Loan Trang 8 2/12/2011
  3. 3 SQL Server là gì? Các dịch vụ của SQL Server . MSSQL Server: Xử lý các câu lệnh giao tác gởi . Hệ quản trị CSDL theo mô hình Client/ Server từ client và server. có thể dễ dàng phát triển trên mô hình phân . SQL Server Agent: tán. o Jobs: Các công việc có thể được lên kế hoạch để thực . Được truy cập qua hai con đường chính. thi tại một thời điểm chỉ định trước hay thực thi theo o Query Analyzer. các khoảng thời gian chu trình. Alerts: đưa ra các sự kiện xảy ra như: lỗi hay khi một o Enterprise Manager. o CSDL đạt tới một giới hạn vì bộ nhớ trống sẵn không còn đủ nữa. Nguyễn Thị Thúy Loan Trang 9 2/12/2011 Nguyễn Thị Thúy Loan Trang 10 2/12/2011 Các dịch vụ của SQL Server NỘI DUNG . SQL Server là gì? . Microsoft Distributed Transaction Coordinator: Điều phối các giao tác phân tán, . Lịch sử phát triển. quản lý các giao tác, có trách nhiệm điều . Mô hình Client/ Server phối các giao tác của CSDL trên nhiều . Các thành phần của SQL Server. server. Nguyễn Thị Thúy Loan Trang 11 2/12/2011 Nguyễn Thị Thúy Loan Trang 12 2/12/2011
  4. 4 Lịch sử phát triển Lịch sử phát triển . 1970 IBM đưa ra ngôn ngữ truy vấn SEQUEL . Nhưng sau đó IBM đưa ra phiên bản mới của (Structured English Query Language) OS/2 gọi là OS/2 mở rộng, HĐH này sẽ mạnh hơn OS/2 phiên bản 1.0 bởi vì nó sẽ tích hợp . 1975 IBM và Microsoft hợp tác xây dựng thêm một phần CSDL SQL của IBM (ngày nay HĐH OS/2 dựa theo hệ điều hành MS DOS chính là HQTCSDL DB2) của Microsoft. Ngày 16/12/1987 HĐH OS/2 . Microsoft hợp tác Sybase làm ra sản phẩm phiên bản 1.0 đã được chính thức phát hành. thuộc loại HQTCSDL Ashton-Tate vào năm 1988 phát triển trên môi trường OS/2. Nguyễn Thị Thúy Loan Trang 13 2/12/2011 Nguyễn Thị Thúy Loan Trang 14 2/12/2011 Lịch sử phát triển NỘI DUNG . Sau đó Sybase đã phát triển sản phẩm trên môi . SQL Server là gì? trường UNIX và đổi tên riêng là DataServer mà . Lịch sử phát triển. ngày nay có tên khác là Sybase Adaptive Server . Microsoft quyết định không phát triển HĐH OS/2 . Mô hình Client/ Server mà thay vào đó cho ra đời một HĐH mạng máy . Các thành phần của SQL Server. tính có tên là NT Server và thế là SQL Server chỉ hoạt động độc lập trên môi trường Windows NT Server mà thôi. Nguyễn Thị Thúy Loan Trang 15 2/12/2011 Nguyễn Thị Thúy Loan Trang 16 2/12/2011
  5. 5 Mô hình Client/Server Mô hình Client/Server . Vì sao phát triển trên mô hình Client/Server Sử dụng giao tiếp lập trình ứng dụng để truy xuất dữ o Giảm chi phí liệu (API- Application Programming Interfece) o Tốc độ nhanh Thư viện mạng client sử dụng 1 phương thức liên lạc o Tương thích cao bên trong mạng để giao tiếp với thư viện mạng của . Mô hình Client/Sever: là kiến trúc gồm 2 thành server phần máy Client và máy Server, 2 thành phần Thư viện mạng của server nhận gói dữ liệu và trao chúng cho các này liên lạc với nhau thông qua hệ thống mạng dịch vụ mở dữ liệu Nguyễn Thị Thúy Loan Trang 17 2/12/2011 Nguyễn Thị Thúy Loan Trang 18 2/12/2011 Mô hình Client/Server Mô hình Client/Server Nhánh máy trạm (client) Nhánh máy chủ (server) . Đọc và hiển thị dữ liệu hiện có bên trong . Các xử lý đảm bảo việc truy cập của các người CSDL, tính toán dữ liệu đang hiển thị trên màn dùng trên mạng là bảo mật. hình ứng dụng, in dữ liệu ra các kết xuất . Các xử lý liên quan đến việc thực hiện hoặc . Các ngôn ngữ sử dụng bên máy khách thường cập nhật dữ liệu đồng thời cùng lúc giữa những là: C++, C#, VB, Delphi người dùng hiện hành trên mạng. . Các ứng dụng khi xây dựng bên nhánh máy . Các xử lý sao lưu dữ liệu (backup data) tự trạm nên tránh việc đọc tòan bộ dữ liệu của động để đảm bảo các dữ liệu không bị mất bảng mà chỉ lấy đúng các thông tin cần thiết trong trường hợp có các sự cố xấu nhất tình cờ khi xử lý. xảy ra. Nguyễn Thị Thúy Loan Trang 19 2/12/2011 Nguyễn Thị Thúy Loan Trang 20 2/12/2011
  6. 6 NỘI DUNG Các thành phần SQL Server . SQL Server là gì? . Client network Utility: . Lịch sử phát triển. công cụ dùng để thiết lập, . Mô hình Client/ Server. tham khảo các cấu hình . Các thành phần của SQL Server. mạng: giao thức truyền cho client, các đường dẫn tham khảo đến thư viện. Nguyễn Thị Thúy Loan Trang 21 2/12/2011 Nguyễn Thị Thúy Loan Trang 22 2/12/2011 Các thành phần SQL Server Các thành phần SQL Server . Enterprise Manager (EM): là màn hình dùng . Import and Export Data: công cụ hỗ trợ cho để quản trị SQL Server. EM cung cấp cho người phép dễ dàng thực hiện việc chuyển dữ liệu từ quản trị chức năng để quản lý SQL bằng giao ngoài vào SQL Server và SQL Server ra diện đồ hoạ o Quản trị các database . Query Analyzer: là công cụ cung cấp bởi o Tạo database và các thành phần bên trong SQL Server, là trình soạn thảo và thực thi câu database lệnh SQL hay Stored Procedure. o Đăng ký làm việc với SQL Server khác. Nguyễn Thị Thúy Loan Trang 23 2/12/2011 Nguyễn Thị Thúy Loan Trang 24 2/12/2011
  7. 7 Các thành phần SQL Server Chương II . Server Network Utility: công cụ dùng để thiết lập các cấu hình cho server và hiển thị các CÁC ĐỐI TƯỢNG TRONG thông tin đường dẫn tham chiếu thư viện làm CƠ SỞ DỮ LIỆU việc . Server Manager: dùng để khởi động, tạm ThS. Nguyễn Thị Thúy Loan dừng, hoặc kết thúc các phục vụ của SQL Server. Nguyễn Thị Thúy Loan Trang 25 2/12/2011 NỘI DUNG . Cơ sở dữ liệu của SQL Server . Bảng (Table) . Bảng ảo (View) Cơ sở dữ liệu của SQL Server . Mô hình quan hệ dữ liệu . Kiểu dữ liệu do người dùng định nghĩa . Quy tắc kiểm tra miền giá trị dữ liệu (rule) . Giá trị mặc định. Nguyễn Thị Thúy Loan Trang 27 2/12/2011 Nguyễn Thị Thúy Loan Trang 28 2/12/2011
  8. 8 Cơ sở dữ liệu của SQL Server Cơ sở dữ liệu của SQL Server Khi cài SQL Server xong, hệ thống sẽ tự động tạo một vài CSDL mặc định: . Master . Model . Tempdb . Pubs . Northwind . Msdb Xem các CSDL trong Query Analyzer:SP_helpdb Nguyễn Thị Thúy Loan Trang 29 2/12/2011 Nguyễn Thị Thúy Loan Trang 30 2/12/2011 Các tập tin vật lý lưu trữ DL Các tập tin vật lý lưu trữ DL . Hai tập tin vật lý để lưu trữ dữ liệu: o Lưu trữ dữ liệu (data file) o Lưu trữ các giao tác mà người dùng đã thực hiện (transaction log file) . Các tập tin CSDL trong SQL Server được chia thành 3 loại tập tin. Nguyễn Thị Thúy Loan Trang 31 2/12/2011 Nguyễn Thị Thúy Loan Trang 32 2/12/2011
  9. 9 Các tập tin vật lý lưu trữ DL Tạo mới cơ sở dữ liệu Các thuộc tính CSDL trong SQL Server . Tập tin dữ liệu chính (Primary Data File). Phần . Tên CSDL (Database name): dài tối đa 128 mở rộng của tập tin này là *.mdf ký tự, duy nhất trong SQL Server . Tập tin thứ yếu (Secondary Data Files) (không . Vị trí tập tin (File location): Thông thường bắt buộc phải có khi tạo mới CSDL). Phần mở tập tin này sẽ được lưu tại C:\Program Files\Microsoft SQL Server\MSSQL\ Data rộng của tập tin này là *.ndf . Tên tập tin (File name): là tên luận lý của mỗi . Tập tin lưu trữ (Log Files). Phần mở rộng là loại tập tin dữ liệu tương ứng mà hệ thống *.ldf SQL Server dùng để quản lý bên trong. Nguyễn Thị Thúy Loan Trang 33 2/12/2011 Nguyễn Thị Thúy Loan Trang 34 2/12/2011 Tạo mới cơ sở dữ liệu Tạo mới cơ sở dữ liệu . Kích thước ban đầu (Initial size): là kích . Ví dụ: tạo CSDL có tên QLBH với kích thước thước khởi tạo của tập tin dữ liệu khi CSDL ban đầu là 50 MB, tự động tăng kích thước lên 10% khi dữ liệu đầy, kích thước tăng trưởng mới được tạo lập. tập tin tối đa không quá 200MB. Và tập tin lưu . Tăng kích thước tập tin dữ liệu (File vết với kích thước ban đầu lúc khởi tạo là growth) 10MB, tự động tăng kích thước tập tin lên 5 . Kích thước tối đa tập tin dữ liệu (Maximun MB khi dữ liệu bị đầy, kích thước tăng trưởng file size) tập tin không giới hạn. Nguyễn Thị Thúy Loan Trang 35 2/12/2011 Nguyễn Thị Thúy Loan Trang 36 2/12/2011
  10. 10 Tạo mới cơ sở dữ liệu Tạo mới cơ sở dữ liệu . Cách 1: tạo trong Query Analyzer . Cách 2: tạo trong Enterprise Manager CREATE DATABASE QLBH ON PRIMARY (NAME = QLBH, FILENAME= ‘C:\Program Files\Microsoft SQL Server\MSSQL.3\MSSQL\Data\ QLBH.mdf’, SIZE=50MB, MAXSIZE = 200MB, FILEGROWTH = 10%) LOG ON(NAME = QLBH_Log, FILENAME= ‘C:\Program Files\Microsoft SQL Server\MSSQL.3\MSSQL\Data\ QLBH_Log.ldf', SIZE=10MB, MAXSIZE = UNLIMITED, FILEGROWTH = 5MB) Nguyễn Thị Thúy Loan Trang 37 2/12/2011 Nguyễn Thị Thúy Loan Trang 38 2/12/2011 Tạo mới cơ sở dữ liệu NỘI DUNG Tên CSDL . Cơ sở dữ liệu của SQL Server . Bảng (Table) . Bảng ảo (View) . Mô hình quan hệ dữ liệu . Kiểu dữ liệu do người dùng định nghĩa . Quy tắc kiểm tra miền giá trị dữ liệu (rule) Click . Giá trị mặc định. Nguyễn Thị Thúy Loan Trang 39 2/12/2011 Nguyễn Thị Thúy Loan Trang 40 2/12/2011
  11. 11 Bảng (Tables) Bảng (Kiểu dữ liệu) Dùng để lưu trữ các thông tin dữ liệu của những Kiểu dữ liệuKích thướcMiền giá trị dữ liệu lưu trữ Số nguyên đối tượng, thực thể trong thế giới thực vào máy Int 4 bytes Từ -2,147,483,648 đến +2,147,483,648 Smallint 2 bytes Từ -32,768 đến + 32,767 tính. Tinyint 1 byte Từ 0 đến 255 Bit 1 byte 0,1 hoặc Null . Tên bảng (table name): dài tối đa 128 ký tự Các kiểu dữ liệu dạng số thập phân Decimal, 17 byte Từ -10^38 đến +10^38 . Tên cột (column name) Numeric . Kiểu dữ liệu (Data type): quy định kiểu dữ Các kiểu dữ liệu dạng số thực Float 8 bytes Từ -1.79E + 308 đến +1.79E + 308 liệu mà cột sẽ lưu trữ bên trong bảng. Real 4 bytes Từ -1.79E + 308 đến +1.79E + 308 Nguyễn Thị Thúy Loan Trang 41 2/12/2011 Nguyễn Thị Thúy Loan Trang 42 2/12/2011 Bảng (Kiểu dữ liệu) Tạo cấu trúc bảng dữ liệu Kiểu dữ liệuKích thướcMiền giá trị dữ liệu lưu trữ Các kiểu dữ liệu dạng chuỗi . Tạo cấu trúc bảng đơn giản Char N bytes Từ 1 đến 8,000 ký tự, độ dài cố định CREATE TABLE ( Varchar N bytes Từ 1 đến 8,000 ký tự, độ dài biến đổi Text N bytes Từ 1 đến 2,147,483,647 ký tự : [NOT NULL], Nchar 2* n bytes Unicode, từ 1 đến 4,000 ký tự, mỗi ký tự 2 bytes Nvarchar 2* n bytes Từ -10^38 đến +10^38 : [NOT NULL], Ntext 2* n bytes từ 1 đến 1,073,741,823 ký tự, mỗi ký tự 1 byte Các kiểu dữ liệu dạng ngày giờ datetime 8 bytes Từ 01/01/1753 đến 31/12/9999 smalldatetime 4 bytes Từ 01/01/1900 đến 06/06/2079 : [NOT NULL]) Các kiểu dữ liệu dạng chuỗi nhị phân Image N byte Từ 1 đến 2,147,483,647 bytes Binary N byte Từ 1 đến 8,000 Nguyễn Thị Thúy Loan Trang 44 2/12/2011
  12. 12 Tạo cấu trúc bảng dữ liệu Tạo cấu trúc bảng dữ liệu Ví dụ: . Tạo cấu trúc bảng có giá trị mặc định CREATE TABLE ( CREATE TABLE LOP( : Default Malop char(6) NOT NULL, gia_trị|hàm, TenLop varchar(20) NOT NULL, : [NOT NULL], SiSo smallInt NOT NULL) : [NOT NULL]) Nguyễn Thị Thúy Loan Trang 45 2/12/2011 Nguyễn Thị Thúy Loan Trang 46 2/12/2011 Tạo cấu trúc bảng dữ liệu Tạo cấu trúc bảng dữ liệu Ví dụ: . Tạo cấu trúc bảng có cột định danh CREATE TABLE LOP( CREATE TABLE ( Malop char(6) NOT NULL, : Identity TenLop varchar(20) NOT NULL, [(số_bắt_đầu,chỉ_số_tăng)], SiSo smallInt Default 0) : [NOT NULL], : [NOT NULL]) Nguyễn Thị Thúy Loan Trang 47 2/12/2011 Nguyễn Thị Thúy Loan Trang 48 2/12/2011
  13. 13 Tạo cấu trúc bảng dữ liệu Tạo cấu trúc bảng dữ liệu Ví dụ: Enterprise Manager . Right-click Tables/ chọn New Table CREATE TABLE LOP( Malop int Identity(1000,5), TenLop varchar(20) NOT NULL, SiSo smallInt NOT NULL) Nguyễn Thị Thúy Loan Trang 49 2/12/2011 Nguyễn Thị Thúy Loan Trang 50 2/12/2011 Tạo cấu trúc bảng dữ liệu Tạo cấu trúc bảng dữ liệu . Tạo các cột. . Đặt tên bảng Tên column Kiểu DL Nguyễn Thị Thúy Loan Trang 51 2/12/2011 Nguyễn Thị Thúy Loan Trang 52 2/12/2011
  14. 14 Thay đổi cấu trúc bảng Thay đổi cấu trúc bảng Query Analyzer Query Analyzer . Thêm một cột mới vào bảng . Hủy bỏ cột hiện có bên trong bảng ALTER TABLE Tên_bảng ALTER TABLE Tên_bảng ADD Tên_cột kiểu_dữ_liệu DROP COLUMN Tên_cột Ví dụ: Ví dụ: ALTER TABLE LOP ALTER TABLE LOP ADD Nien_khoa int DROP COLUMN Nien_khoa Nguyễn Thị Thúy Loan Trang 53 2/12/2011 Nguyễn Thị Thúy Loan Trang 54 2/12/2011 Thay đổi cấu trúc bảng Thay đổi cấu trúc bảng Query Analyzer . Đổi tên cột, tên bảng dữ liệu EXEC sp_rename ‘Tên_bảng[.tên_cột]’, . Sửa đổi kiểu dữ liệu của cột ‘Tên_mới’ [, ‘COLUMN’] ALTER TABLE Tên_bảng Ví dụ 1: đổi tên cột Nien_khoa thành khoa_hoc trong bảng LOP alter column Tên_cột EXEC sp_rename ‘LOP.nien_khoa’, Ví dụ: ‘khoa_hoc’, ‘COLUMN’ ALTER TABLE LOP Ví dụ 2: thay đổi tên LOP thành LOPHOC EXEC sp_rename ‘LOP’, ‘LOPHOC’ ALTER COLUMN Nien_khoa char(9) Nguyễn Thị Thúy Loan Trang 55 2/12/2011 Nguyễn Thị Thúy Loan Trang 56 2/12/2011
  15. 15 Tính toàn vẹn trong cơ sở DL Tính toàn vẹn trong cơ sở DL . Ràng buộc toàn vẹn (RBTV) dữ liệu thực sự là Các Phương Pháp Ðảm Bảo Data Integrity . Data Type việc đặc ra các quy tắc trong một cơ sở dữ liệu . Not Null Definitions nhằm kiểm tra các giá trị của dữ liệu trước khi . Default Definitions lưu trữ phải đảm bảo tính chính xác và hợp lý . Identity Properties . bên trong một cơ sở dữ liệu. Constraints . Rules . Triggers . Indexes Nguyễn Thị Thúy Loan Trang 57 2/12/2011 Nguyễn Thị Thúy Loan Trang 58 2/12/2011 Cách tạo các ràng buộc Cách tạo các ràng buộc . Primary Key Constraint . Primary Key Constraint CREATE TABLE LOP ALTER TABLE ADD (MaLop INT PRIMARY KEY, CONSTRAINT PRIMARY TenLop VARCHAR(30)) KEY(thuộc tính khóa chính) Hay Ví dụ: CREATE TABLE LOP ALTER TABLE HOCVIEN ADD (MaLop INT, CONSTRAINT MaHV_pk PRIMARY TenLop VARCHAR(30), KEY(MaHV) CONSTRAINT MaLop_pk PRIMARY KEY Chú ý: khi thêm khóa chính thì cột MaHV trong (MaLop)) bảng HOCVIEN buộc phải khác NULL Nguyễn Thị Thúy Loan Trang 59 2/12/2011 Nguyễn Thị Thúy Loan Trang 60 2/12/2011
  16. 16 Add Primary Key Cách tạo các ràng buộc Enterprise Manager Unique Constraint . Right-click column of interest . Set Primary Key . Cách tạo ra Unique Constraint cũng tương tự như Primary Key Constraint chỉ việc thay chữ Primary Key thành Unique. . Chỉ có một Primary Key Constraint trong một table trong khi ta có thể có nhiều Unique Constraint trên một table. Nguyễn Thị Thúy Loan Trang 61 2/12/2011 Nguyễn Thị Thúy Loan Trang 62 2/12/2011 Cách tạo các ràng buộc Cách tạo các ràng buộc . Foreign Key Constraint . Foreign Key Constraint CREATE TABLE LOP ALTER TABLE ADD (MaLop INT PRIMARY KEY, CONSTRAINT FOREIGN MaKh INT REFERENCES Khoa (MaKH)) KEY(các cột khóa ngoại) REFERENCES (các cột khóa chính) CREATE TABLE LOP Ví dụ: (MaLop INT PRIMARY KEY, MaKh INT, ALTER TABLE LOP ADD CONSTRAINT CONSTRAINT MaKh_fk FOREIGN KEY Makh_fk FOREIGN KEY(MaKh) (MaKh) REFERENCES Khoa(MaKh)) REFERENCES Khoa (MaKh) Nguyễn Thị Thúy Loan Trang 63 2/12/2011 Nguyễn Thị Thúy Loan Trang 64 2/12/2011
  17. 17 Cách tạo các ràng buộc Cách tạo các ràng buộc . Check Constraint (ràng buộc miền giá trị) . Check Constraint (ràng buộc miền giá trị) CREATE TABLE LOP CREATE TABLE LOP (MaLop INT PRIMARY KEY, (MaLop INT PRIMARY KEY, TenLop VARCHAR(30), TenLop VARCHAR(30), Siso INT CHECK (Siso BETWEEN 1 Siso INT, AND 100)) CONSTRAINT Siso_Ck CHECK (Siso BETWEEN 1 AND 100)) Nguyễn Thị Thúy Loan Trang 65 2/12/2011 Nguyễn Thị Thúy Loan Trang 66 2/12/2011 Cách tạo các ràng buộc Cách tạo các ràng buộc . Check Constraint (ràng buộc miền giá trị) . Check Constraint (ràng buộc miền giá trị) Alter table Alter table KhachHang add constraint check Add constraint DT_Ck check (DT like Ví dụ: ‘[0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9]’) Alter table Lop add constraint Siso_Ck check (Siso between 1 and Alter table SanPham 100) Add constraint GiaBan_Ck check (GiaBan >0) Nguyễn Thị Thúy Loan Trang 67 2/12/2011 Nguyễn Thị Thúy Loan Trang 68 2/12/2011
  18. 18 NỘI DUNG Khung nhìn (View) . Cơ sở dữ liệu của SQL Server . Đối tượng bên trong nó chỉ lưu trữ duy nhất . Bảng (Table) một câu lệnh SELECT . Bảng ảo (View) . Bảng ảo không hoàn toàn lưu trữ dữ liệu một . Mô hình quan hệ dữ liệu cách riêng rẽ. . Kiểu dữ liệu do người dùng định nghĩa . Quy tắc kiểm tra miền giá trị dữ liệu (rule) . Giá trị mặc định. Nguyễn Thị Thúy Loan Trang 69 2/12/2011 Nguyễn Thị Thúy Loan Trang 70 2/12/2011 Khung nhìn (View) Khung nhìn (View) . Cú pháp: . Một số từ khoá chuẩn không được dùng trong CREATE VIEW [(tên_các_cột)] VIEW: [WITH ENCRYPTION] //mã hóa câu lệnh ORDER BY AS COMPUTE (thống kê dữ liệu cuối cùng) Câu lệnh SELECT COMPUTE BY (thống kê dữ liệu theo từng [WITH CHECK OPTION]// ngăn cản các thao tác cập nhật dữ liệu (thêm, sửa) tác động trực nhóm) tiếp vào bảng ảo không thỏa điều kiện trong SELECT INTO mệnh đề WHERE. Nguyễn Thị Thúy Loan Trang 71 2/12/2011 Nguyễn Thị Thúy Loan Trang 72 2/12/2011
  19. 19 Khung nhìn (View) Khung nhìn (View) . Ví dụ: Tạo bằng Enterprise Manager . Bước 1: CREATE VIEW KQ_DAU AS select kq.masv,tensv,mamh,diem from sv,kq where diem>7 and kq.masv=sv.masv . Xem kết quả của View Select * from Nguyễn Thị Thúy Loan Trang 73 2/12/2011 Nguyễn Thị Thúy Loan Trang 74 2/12/2011 Khung nhìn (View) Khung nhìn (View) Tạo bằng Enterprise Manager . Bước 3: lần lượt chọn các cột . Bước 2: Chọn các bảng như trong màn hình thiết kế Nguyễn Thị Thúy Loan Trang 75 2/12/2011
  20. 20 NỘI DUNG Mô hình dữ liệu quan hệ . Cơ sở dữ liệu của SQL Server . Tạo các ràng buộc khóa ngoại một cách trực quan hơn. . Bảng (Table) . Tạo mô hình quan hệ . Bảng ảo (View) o Bước 1: . Mô hình quan hệ dữ liệu . Kiểu dữ liệu do người dùng định nghĩa . Quy tắc kiểm tra miền giá trị dữ liệu (rule) . Giá trị mặc định. Nguyễn Thị Thúy Loan Trang 77 2/12/2011 Nguyễn Thị Thúy Loan Trang 78 2/12/2011 Mô hình dữ liệu quan hệ Mô hình dữ liệu quan hệ o Bước 2: o Bước 3: Nguyễn Thị Thúy Loan Trang 79 2/12/2011 Nguyễn Thị Thúy Loan Trang 80 2/12/2011
  21. 21 NỘI DUNG KDL do người dùng định nghĩa . Cơ sở dữ liệu của SQL Server . Mục đích đảm bảo cho cấu trúc dữ liệu bên . Bảng (Table) trong CSDL được nhất quán và dễ sửa đổi. . Bảng ảo (View) . Cú pháp: . Mô hình quan hệ dữ liệu EXEC sp_addtype Tên_kiểu_dl_mới, . Kiểu dữ liệu do người dùng định nghĩa ‘kiểu_dữ_liệu_cơ_sở’ [,NULL| NOT NULL] . Quy tắc kiểm tra miền giá trị dữ liệu (rule) . Giá trị mặc định. Nguyễn Thị Thúy Loan Trang 81 2/12/2011 Nguyễn Thị Thúy Loan Trang 82 2/12/2011 KDL do người dùng định nghĩa NỘI DUNG . Ví dụ: . Cơ sở dữ liệu của SQL Server exec sp_addtype dn_sl, ‘float’, ‘not null’ . Bảng (Table) exec sp_addtype slg, ‘int’ . Bảng ảo (View) . Khi tạo lập cấu trúc bảng KQ chúng ta có thể . Mô hình quan hệ dữ liệu sử dụng: CREATE TABLE KQ . Kiểu dữ liệu do người dùng định nghĩa (MaSV nchar(10), . Quy tắc kiểm tra miền giá trị dữ liệu (rule) MaMH nchar(10), . Giá trị mặc định. Diem dn_sl) Nguyễn Thị Thúy Loan Trang 83 2/12/2011 Nguyễn Thị Thúy Loan Trang 84 2/12/2011
  22. 22 Rules Rules . Kiểm tra các cột bên trong bảng phải thoả mãn . Tên_quy_tắc: kiểm tra miền giá trị dữ liệu điều kiện nào đókhi dữ liệu bị sửa đổi hoặc được tạo mới. thêm vào. Tính năng hoạt động của nó gần . Biểu thức: biểu thức luận lý hoặc so sánh. Có giống như CHECK constraint 3 thành phần: . Cú pháp (Query Analyzer) o Tên biến hình thức (phải bắt đầu bằng @) CREATE RULE Tên_qui_tắc o Toán tử so sánh AS Biểu_thức o Giá trị so sánh Nguyễn Thị Thúy Loan Trang 85 2/12/2011 Nguyễn Thị Thúy Loan Trang 86 2/12/2011 Rules Rules . Ví dụ 1: Để tạo quy tắc kiểm tra miền giá trị . Ví dụ 2: Để tạo quy tắc kiểm tra miền giá trị dữ liệu cột điểm phải từ 0 đến 10. dữ liệu cột đơn vị tính của vật tư chỉ thuộc CREATE RULE R_Diem trong các từ: cái, bộ, kg, m2, m3. AS CREATE RULE rule_Dvtinh_Hople @Diem between 0 and 10 AS @Dvt IN (‘Cái’, ‘Bộ’, ‘Kg’, ‘m2’, ‘m3’) Nguyễn Thị Thúy Loan Trang 87 2/12/2011 Nguyễn Thị Thúy Loan Trang 88 2/12/2011
  23. 23 Rules (áp dụng quy tắc KT) Rules (áp dụng quy tắc KT) . Cú pháp: EXEC sp_bindrule . Ví dụ 2: Áp dụng quy tắc kiểm tra có tên là Tên_qui_tắc, Tên_đối_tượng R_Diem cho kiểu dữ liệu mới có tên là dn_sl . Ví dụ 1: Áp dụng quy tắc kiểm tra có tên EXEC sp_bindrule R_Diem, ‘dn_sl’ R_Diem cho cột điểm trong bảng KQ. EXEC sp_bindrule R_Diem, ‘KQ.Diem’ Nguyễn Thị Thúy Loan Trang 89 2/12/2011 Nguyễn Thị Thúy Loan Trang 90 2/12/2011 Rules (gỡ bỏ quy tắc KT) Rules . Cú pháp: . Enterprise Manager Tên Rule EXEC sp_unbindrule Tên_đối_tượng . Ví dụ EXEC sp_unbindrule ‘dn_sl’ ND Rule Nguyễn Thị Thúy Loan Trang 91 2/12/2011 Nguyễn Thị Thúy Loan Trang 92 2/12/2011
  24. 24 Rules Rules . Click vào mục Bind Columns trong . Chọn những bảng và cột tương ứng để ràng Properties. buộc. Nguyễn Thị Thúy Loan Trang 93 2/12/2011 Nguyễn Thị Thúy Loan Trang 94 2/12/2011 Rules NỘI DUNG . Xóa rule . Cơ sở dữ liệu của SQL Server DROP Rule Tên_rule . Bảng (Table) . Ví dụ; . Bảng ảo (View) Drop Rule R_Diem . Mô hình quan hệ dữ liệu Drop Rule R_Siso . Kiểu dữ liệu do người dùng định nghĩa . Quy tắc kiểm tra miền giá trị dữ liệu (rule) . Giá trị mặc định. Nguyễn Thị Thúy Loan Trang 95 2/12/2011 Nguyễn Thị Thúy Loan Trang 96 2/12/2011
  25. 25 Giá trị mặc định (Default) Giá trị mặc định (Default) . Cú pháp: . Ví dụ: tạo giá trị mặc định cho các cột điểm là 0. CREATE DEFAULT tên_giá_trị_mặc_định CREATE DEFAULT D_Diem AS Biểu_thức AS ‘0’ Trong đó: . Tên_giá_trị_mặc_định: tên giá trị mặc định phải duy nhất trong CSDL. . Biểu thức: là một giá trị cụ thể nào đó như số, ngày, chuỗi hoặc một hàm, biểu thức tính toán. Nguyễn Thị Thúy Loan Trang 97 2/12/2011 Nguyễn Thị Thúy Loan Trang 98 2/12/2011 Giá trị mặc định (Default) Giá trị mặc định (Default) . Liên kết các giá trị mặc định vào dữ liệu . Không liên kết các giá trị mặc định vào cột . Cú pháp: . Cú pháp: EXEC sp_bindefault Tên_mặc_định, EXEC sp_unbindefault Tên_đối_tượng Tên_đối_tượng . Ví dụ: Để hủy bỏ giá trị mặc định đã liên kết . Ví dụ: Để liên kết giá trị mặc định vừa tạo cho cột Diem trong bảng KQ trước đó. D_Diem vào cột Diem có trong bảng KQ EXEC sp_unbindefault ‘KQ.Diem’ EXEC sp_bindefault D_Diem,‘KQ.Diem’ Nguyễn Thị Thúy Loan Trang 99 2/12/2011 Nguyễn Thị Thúy Loan Trang 100 2/12/2011
  26. 26 Giá trị mặc định (Default) Giá trị mặc định (Default) . Enterprise Manager: Tương tự giống Rules . Xóa Default Tên Default DROP DEFAULT Tên_gt_mặc_định [, ] . Ví dụ: DROP DEFAULT D_Diem ND Default Nguyễn Thị Thúy Loan Trang 101 2/12/2011 Nguyễn Thị Thúy Loan Trang 102 2/12/2011 Chỉ mục (Index) Chỉ mục (Index) . Tạo chỉ mục để lưu thứ tự sắp xếp các bản ghi . Thực thi index theo giá trị tăng dần của các cột được cho trong Select from . danh sách. . Cú pháp” With(index(indexName)) CREATE INDEX . Ví dụ: ON ( , , ) Select * from mh . Ví dụ: CREATE INDEX I_Tenmh With(index(I_Tenmh)) ON MH(TenMH) Nguyễn Thị Thúy Loan Trang 103 2/12/2011 Nguyễn Thị Thúy Loan Trang 104 2/12/2011
  27. 27 Chỉ mục (Index) Chương III . Chỉ nên tạo chỉ mục cho các bảng có số lượng lớn các bản ghi và ít được cập nhật (vì nếu LLẬẬPP TRÌNHTRÌNH VVỚỚII bảng thường xuyên bổ sung dữ liệu thì phải cập nhật lại index sẽ làm mất nhiều thời gian), CCƠƠ SSỞỞ DDỮỮ LILIỆỆUU và chỉ nên tạo chỉ mục trên các cột mà thông tin của nó thường xuyên được tham khảo tới ThS. Nguyễn Thị Thúy Loan (vì sẽ làm gia tăng tốc độ tìm kiếm mà không cần phải tìm kiếm hết các dòng trên bảng). Nguyễn Thị Thúy Loan Trang 105 2/12/2011 NỘI DUNG . Biến cục bộ. . Biến hệ thống. . Các câu lệnh truy vấn dữ liệu. Biến cục bộ . Cấu trúc điều khiển. . Biến kiểu dữ liệu cursor. . Các hàm thường dùng. Nguyễn Thị Thúy Loan Trang 107 2/12/2011
  28. 28 Biến cục bộ Biến cục bộ . Cú pháp: . Lệnh SET chỉ để gán giá trị cụ thể hoặc các DECLARE @Tên_biến kiểu_dữ_liệu[, ] biểu thức tính toán hoặc giá trị tính toán từ . Ví dụ: các biến khác, ngược lại lệnh SELECT dùng DECLARE @ten_lop char(50), để gán các giá trị được lấy ra hoặc tính toán từ dữ liệu của các cột bên trong các bảng dữ @ngaysinh DATETIME liệu. Gán giá trị cho biến: SET hoặc SELECT cùng với phép gán (=) Nguyễn Thị Thúy Loan Trang 109 2/12/2011 Nguyễn Thị Thúy Loan Trang 110 2/12/2011 Biến cục bộ Biến cục bộ . Ví dụ 1: để gán giá trị ngày 25/1/1990 vào . Ví dụ 3: biến ngày sinh. Declare @MinDiem dn_sl, @MaxDiem dn_sl DECLARE @ngaysinh DATETIME Select @MinDiem = Min(Diem), SET @ngaysinh = ’01-25-1990’ . Ví dụ 2: @MaxDiem = Max(Diem) DECLARE @TongSSV int From KQ SELECT @TongSSV = count(MaSV) FROM SV Nguyễn Thị Thúy Loan Trang 111 2/12/2011 Nguyễn Thị Thúy Loan Trang 112 2/12/2011
  29. 29 Biến cục bộ Biến cục bộ . Ví dụ: . Xem giá trị hiện hành của biến Declare @MinDiem dn_sl, @MaxDiem dn_sl . Cú pháp: Select @MinDiem = Min(Diem), PRINT @Tên_biến | Biểu_thức_chuỗi @MaxDiem = Max(Diem) From KQ print 'Diem thap nhat la: ' print @MinDiem print 'Diem cao nhat la: ' print @MaxDiem Nguyễn Thị Thúy Loan Trang 113 2/12/2011 Nguyễn Thị Thúy Loan Trang 114 2/12/2011 Biến cục bộ Biến cục bộ Ví dụ: In ra tuổi nhỏ nhất của sinh viên Phạm vi hoạt động của biến: . Trong Transaction-SQL phạm vi hoạt động của biến chỉ nằm trong một thủ tục nội tại (stored procedure) hoặc một lô (batch) chứa các câu lệnh mà biến đã được khai báo bên trong đó. Nguyễn Thị Thúy Loan Trang 115 2/12/2011 Nguyễn Thị Thúy Loan Trang 116 2/12/2011
  30. 30 Biến cục bộ Biến cục bộ . Lô được xem như một nhóm tập hợp của một . Một từ khóa GO chỉ định kết thúc 1 lô. hoặc nhiều câu lệnh T-SQL sẽ được biên dịch . Do các câu lệnh trong một lô được biên dịch đồng thời cùng lúc tại SQL Server và sau đóhệ tại SQL Server vì thế khi có ít nhất 1 lệnh bên thống sẽ thực thi các câu lệnh này ngay sau khi trong lô có lỗi về cú pháp lúc biên dịch thì hệ đã biên dịch thành công. thống sẽ không có lệnh nào được thực thi bên trong lô đó. Nguyễn Thị Thúy Loan Trang 117 2/12/2011 Nguyễn Thị Thúy Loan Trang 118 2/12/2011 Biến cục bộ Biến cục bộ . Ví dụ: . Đối với các lỗi khi thực hiện (run-time) bên Select * From SV trong 1 lô nếu trường hợp các lỗi vi phạm ràng Order By TenSV buộc toàn vẹn dữ liệu thì hệ thống SQL Server Insert into SV ('004', 'Tran Thi Ha', '05-26-1990', chỉ ngưng lại câu lệnh gây lỗi và thực hiện tiếp '08CDTH') các lệnh bên trong lô đó. Select * From Khoa (thiếu từ khoá VALUES) thì các lệnh SELECT bên trong lô này Order By TenKH desc không được thực hiện. go Nguyễn Thị Thúy Loan Trang 119 2/12/2011 Nguyễn Thị Thúy Loan Trang 120 2/12/2011
  31. 31 Biến cục bộ Biến cục bộ . Ví dụ: . Ví dụ: Select * From SV Declare @NS datetime Order By TenSV Select @NS = Max(NgSinh) HT sẽ báo lỗi vì có thêm từ khoá GO ở giữa 2 Insert into SV values ('004', 'Tran Thi Ha', '05-26- From SV lệnh SELECT và PRINT. Bởi vì khi đócác lệnh này được chia làm 2 lô và lô thứ hai sẽ không hiểu 1990', '08CDTH') go biến @NS đã được khai báo trong lô thứ 1. Select * From Khoa (mặc dù vi phạm ràng buộc toàn vẹn Print 'SV nho tuoi nhat la: '+ convert (char(12), trong INSERT (giả sử trùng khoá chính Order By TenKH desc ở cột MaSV) nhưng các lệnh SELECT @NS) bên trong lô này vẫn được thực hiện go bình thường. go Nguyễn Thị Thúy Loan Trang 121 2/12/2011 Nguyễn Thị Thúy Loan Trang 122 2/12/2011 NỘI DUNG Biến hệ thống . Biến cục bộ. . Các biến hệ thống trong SQL Server luôn bắt . Biến hệ thống. đầu bằng 2 chữ @@ và giá trị mà chúng ta . Các câu lệnh truy vấn dữ liệu. đang lưu trữ do hệ thống SQL cung cấp, người . Cấu trúc điều khiển. lập trình không can thiệp trực tiếp để gán giá . Biến kiểu dữ liệu cursor. trị vào các biến hệ thống. . Các hàm thường dùng. Nguyễn Thị Thúy Loan Trang 123 2/12/2011 Nguyễn Thị Thúy Loan Trang 124 2/12/2011
  32. 32 Biến hệ thống Biến hệ thống Tên biếnkiểu trả về Dùng để trả về . Ví dụ: connections số nguyên Tổng số các kết nối vào SQL Server từ khi nó được khởi động Select * From SV Error số nguyên số mã lỗi của câu lệnh thực hiện gần nhất. Khi một lệnh thực hiện thành công thì biến này có giá trị là 0 Language chuỗi Tên ngôn ngữ mà hệ thống SQL đang sử dụng. Mặc định Select @@rowcount là US_English RowCount số nguyên Tổng số mẫu tin được tác động vào câu lệnh truy vấn gần (trả về tổng số mẫu tin đang hiện có trong bảng nhất ServerName chuỗi Tên của máy tính cục bộ được cài đặt trong SQL Server SV ) ServiceName chuỗi Tên dịch vụ kèm theo bên dưới SQL Server Fetch_Status số nguyên Trạng thái của việc đọc dữ liệu trong bảng theo cơ chế dòng mẫu tin (cursor). Khi dữ liệu đọc mẫu tin thành công thì biến này có giá trị là 0 Version chuỗi Phiên bản, ngày của phẩm SQL Server và loại CPU Nguyễn Thị Thúy Loan Trang 125 2/12/2011 Nguyễn Thị Thúy Loan Trang 126 2/12/2011 Biến hệ thống NỘI DUNG . Ví dụ: . Biến cục bộ. Update LOP . Biến hệ thống. Set Siso = Siso + 12 . Các câu lệnh truy vấn dữ liệu. Where TenLop like '08%' . Cấu trúc điều khiển. Select @@rowcount . Biến kiểu dữ liệu cursor. (Trả về tổng số mẫu tin có TenLop bắt đầu . Các hàm thường dùng. bằng chữ “08” trong bảng LOP ) Nguyễn Thị Thúy Loan Trang 127 2/12/2011 Nguyễn Thị Thúy Loan Trang 128 2/12/2011
  33. 33 Các câu lệnh truy vấn dữ liệu Các câu lệnh truy vấn dữ liệu oCần mở và đóng ngoặc đơn cho câu lệnh truy vấn . Truy vấn con: chỉ là một câu lệnh truy vấn lựa con. chọn (SELECT) được lồng vào các câu lệnh o Chúng ta chỉ được phép tham chiếu đến tên một truy vấn khác nhằm thực hiện các truy vấn tính cột hoặc một biểu thức sẽ trả về giá trị trong truy toán phức tạp. Khi sử dụng đến truy vấn con vấn con. chúng ta cần lưu tâm đến một vài yếu tố sau: oKết quả truy vấn con có thể trả về là một giá trị đơn lẻ hoặc một danh sách các giá trị. oCấp độ lồng nhau của các truy vấn con bên trong SQL Server là không giới hạn. Nguyễn Thị Thúy Loan Trang 129 2/12/2011 Nguyễn Thị Thúy Loan Trang 130 2/12/2011 Các câu lệnh truy vấn dữ liệu Các câu lệnh truy vấn dữ liệu . Truy vấn con trả về một giá trị đơn: là truy vấn Select * From SV mà kết qủa trả về của nó luôn đảm bảo chỉ là Where Ngsinh = '07-21-1990' một giá trị đơn. . Kết hợp 2 câu truy vấn trên . Ví dụ: để biết được sinh viên nào có ngày sinh SELECT * gần đây nhất. SELECT MAX(NGSINH) FROM SV FROM SV WHERE Ngsinh = (SELECT MAX(Ngsinh) Kết quả trả về: 1990-07-21 00:00:00.000 FROM SV) Nguyễn Thị Thúy Loan Trang 131 2/12/2011 Nguyễn Thị Thúy Loan Trang 132 2/12/2011
  34. 34 Các câu lệnh truy vấn dữ liệu Các câu lệnh truy vấn dữ liệu . Truy vấn con trả về danh sách các giá trị: kết . Ví dụ 1: để biết nhà cung cấp nào mà công ty đã đặt hàng trong tháng 01/2009. quả trả về của nó là danh sách các giá trị hay SELECT MaNCC còn gọi là một tập hợp các phần tử. Toán tử IN FROM DONDH sẽ được sử dụng để so sánh truy vấn con dạng WHERE Convert(char(7), NgayDH, 21) = này. “2009-01” . Kết quả trả về MaNCC C03, C01 Nguyễn Thị Thúy Loan Trang 133 2/12/2011 Nguyễn Thị Thúy Loan Trang 134 2/12/2011 Các câu lệnh truy vấn dữ liệu Các câu lệnh truy vấn dữ liệu . Hoặc: . Đâu đảm bảo rằng trong tháng 01/2009 công ty SELECT TenNCC, DienThoai chỉ đặt hàng cho 2 nhà cung cấp C01 và C03. FROM NHACC Do đó để luôn luôn có được danh sách họ tên WHERE MaNCC IN(‘C01’, ‘C03’) các nhà cung cấp mà công ty đã đặt hàng trong tháng 01-2009 chúng ta thực hiện truy vấn con sau: Nguyễn Thị Thúy Loan Trang 135 2/12/2011 Nguyễn Thị Thúy Loan Trang 136 2/12/2011
  35. 35 Các câu lệnh truy vấn dữ liệu Các câu lệnh truy vấn dữ liệu SELECT TenNCC, DienThoai . Hoặc dùng EXISTS SELECT TenNCC, DienThoai FROM NHACC FROM NHACC WHERE MaNCC IN (SELECT MaNCC WHERE EXISTS (SELECT * FROM DONDH FROM DONDH WHERE Convert(char(7), WHERE Convert(char(7), NgayDH, 21) = ‘2009-01’ ) NgayDH, 21) = ‘2009-01’ AND NHACC.MaNCC = DONDH.MaNCC) Nguyễn Thị Thúy Loan Trang 137 2/12/2011 Nguyễn Thị Thúy Loan Trang 138 2/12/2011 Các câu lệnh truy vấn dữ liệu Các câu lệnh truy vấn dữ liệu . Ví dụ 2: Để biết danh sách các nhà cung cấp . Hoặc nào mà công ty chưa bao giờ đặt hàng. Chúng SELECT TenNhaCC, DienThoai ta có thể thực hiện câu truy vấn như sau: FROM NHACC SELECT TenNhaCC, DienThoai WHERE MaNCC ALL Nguyễn Thị Thúy Loan Trang 139 2/12/2011 Nguyễn Thị Thúy Loan Trang 140 2/12/2011
  36. 36 Lệnh INSERT Lệnh INSERT . Cách 1: Thêm trực tiếp một bộ Insert into SV values('004', 'Tran Thi Ha', '05- INSERT INTO bảng[ , , , cột n] 26-1990', '08CDTH') VALUES( , , , ) .Hay Insert into SV(MaSV, TenSV, NgSinh, Malop) . Ví dụ: Thêm dữ liệu vào SV values('004', 'Tran Thi Ha', '05-26-1990', SV (MaSV, TenSV, NgSinh, Malop) '08CDTH') Nguyễn Thị Thúy Loan Trang 141 2/12/2011 Nguyễn Thị Thúy Loan Trang 142 2/12/2011 Lệnh INSERT Lệnh INSERT . Cách 2: Thêm nhiều bộ giá trị lấy từ các bộ . Ví dụ: giá trị của các bảng của CSDL LOP_SV( TenLop, SiSo) INSERT INTO bảng[ , , , INSERT INTO LOP_SV( TenLop, SiSo) ] SELECT TenLop, count(*) SELECT FROM . WHERE FROM SV, LOP WHERE SV.MaLop=LOP.MaLop GROUP BY TenLop Nguyễn Thị Thúy Loan Trang 143 2/12/2011 Nguyễn Thị Thúy Loan Trang 144 2/12/2011
  37. 37 Lệnh UPDATE Lệnh UPDATE . Cú pháp: . Ví dụ: Sửa sinh viên Dư Thanh Linh ở lớp UPDATE 11CDTH thành 11CDKT SET = , = , , = [WHERE ] Nguyễn Thị Thúy Loan Trang 145 2/12/2011 Nguyễn Thị Thúy Loan Trang 146 2/12/2011 Lệnh DELETE Biểu thức CASE . Cú pháp: . Cú pháp: DELETE [FROM] Bảng Case biểu_thức: When giá_tri_1|Bt_logic_1 Then [WHERE ] Biểu_thức_kết_quả_1 . Ví dụ: Xóa tất cả các nhân viên có LCB < 700 [When giá_tri_2|Bt_logic_2 Then Biểu_thức_kết_quả_2 ] [ELSE biểu_thức_kết_quả_N] End Nguyễn Thị Thúy Loan Trang 147 2/12/2011 Nguyễn Thị Thúy Loan Trang 148 2/12/2011
  38. 38 Biểu thức CASE Biểu thức CASE . Ví dụ 1: Hiển thị danh sách sinh viên (Masv, select Masv, Ho = case left(TenSV,1) Ho, Ngsinh) có trong bảng SV theo từng họ. when 'L' then 'Le' Họ là kí tự đầu tiên trong Tensv. (L(Le), when 'N' then 'Nguyen' N(Nguyen),T(Tran),còn lại là chưa phân biệt. when 'T' then 'Tran' else 'Chua phan biet' end, Ngsinh from SV Nguyễn Thị Thúy Loan Trang 149 2/12/2011 Nguyễn Thị Thúy Loan Trang 150 2/12/2011 Biểu thức CASE Biểu thức CASE . Ví dụ 2: Hiển thị danh sách các vật tư (Mavt, . Ví dụ 3: Hiển thị danh sách (Malop, Tenlopmoi Tenvt, DVT) có trong bảng VATTU theo từng và sĩ số) các lớp trong bảng LOP theo tên mới. loại hàng. Nếu 2 kí tự đầu của Mavt là DD Với tên mới là 1 kí tự bên phải của Tenlop (t (đầu DVD), VD (đầu VCD), TV (Tivi), TL (Tủ (cao dang ky thuat), c(cao dang tin hoc), còn lạnh), LO (Loa thùng), còn lại chưa phân biệt. lại là cao dang bao chi. Nguyễn Thị Thúy Loan Trang 151 2/12/2011 Nguyễn Thị Thúy Loan Trang 152 2/12/2011
  39. 39 Biểu thức CASE Biểu thức CASE . Ví dụ 4: Hiển thị danh sách các vật tư trong . Ví dụ: cho lược đồ CSDL như sau: bảng VATTU (MaVT, TenVT, DVTinh, MH(MaMH, TenMH, DVT, MaNCC) PhanTram), thông tin bổ sung thêm chuỗi ghi PXUAT(SoPX, NgayXuat, SoDH) chú, tùy thuộc vào giá trị của cột phần trăm giá CTPX(Ma_MH, SoPX, SLXuat, DGXuat) bán. ( 40 rất DDH(MaDH, NgayDat) lời) CTDH(MaDH, MaMH, SLDH, DonGia) Nguyễn Thị Thúy Loan Trang 153 2/12/2011 Nguyễn Thị Thúy Loan Trang 154 2/12/2011 Biểu thức CASE Biểu thức CASE . Ví dụ 5: Giảm giá bán hàng trong tháng 2-2007 . Ví dụ 6: Cập nhật Trợ cấp cho sinh viên sinh theo quy tắc sau: Nếu số lượng hàng 10 thì đến 70 thì 100000, lớp có sĩ số lớn hơn 70 thì giảm 20%. (Thay đổi trên ĐGXuất) 500000. Nguyễn Thị Thúy Loan Trang 155 2/12/2011 Nguyễn Thị Thúy Loan Trang 156 2/12/2011
  40. 40 NỘI DUNG Cấu trúc điều khiển . Biến cục bộ. . Cú pháp: . Biến hệ thống. Cấu trúc rẽ nhánh IF ELSE . Các câu lệnh truy vấn dữ liệu. IF Biểu_thức_luận_lý . Cấu trúc điều khiển. Câu_lệnh 1| khối_lệnh 1 . Biến kiểu dữ liệu cursor. ELSE . Các hàm thường dùng. Câu_lệnh 2| khối_lệnh 2 Nguyễn Thị Thúy Loan Trang 157 2/12/2011 Nguyễn Thị Thúy Loan Trang 158 2/12/2011 Cấu trúc điều khiển Cấu trúc điều khiển Ví dụ 1: Cho biết kết quả học tập của lớp Ví dụ: Cho biết kết quả học tập của Sinh viên 08CDTH như thế nào? Ví dụ điểm >=8 thì lớp 10CDTH001, khi biết đtb. giỏi, >= 7 thì lớp khá, >= 5 thì lớp TB, còn lại lớp yếu. Nguyễn Thị Thúy Loan Trang 159 2/12/2011 Nguyễn Thị Thúy Loan Trang 160 2/12/2011
  41. 41 Cấu trúc điều khiển Cấu trúc lặp WHILE . Ví dụ 1: Cho biết xếp loại học tập của sinh . Cú pháp: viên. WHILE Biểu_thức_luận_lý . Ví dụ 2: Cho biết những sinh viên học nhiều BEGIN hơn 3 môn? Nếu có thì hiển thị danh sách Các_lệnh_lặp TenSV đó ra, ngược lại thì thông báo chưa có END sinh viên nào học nhiều hơn 3 môn. Nguyễn Thị Thúy Loan Trang 161 2/12/2011 Nguyễn Thị Thúy Loan Trang 162 2/12/2011 Cấu trúc lặp WHILE Cấu trúc lặp WHILE Ví dụ 1: Để in ra 10 số nguyên dương bắt đầu từ Declare @sn int, @d int Set @sn = 10 10, mỗi số cách nhau 4 đơn vị. Set @d =0 While (@d<10) Begin Print 'so nguyen la: ' + convert(char(2),@sn) Set @sn = @sn +4 Set @d = @d +1; End Nguyễn Thị Thúy Loan Trang 163 2/12/2011 Nguyễn Thị Thúy Loan Trang 164 2/12/2011
  42. 42 Cấu trúc lặp WHILE Cấu trúc lặp WHILE . Từ khoá BREAK lồng vào cấu trúc WHILE để Declare @sn int, @d int có thể kết thúc việc lặp của các lệnh bên trong Set @sn = 10, set @d =0 While (0=0) vòng lặp. Begin Print 'so nguyen la: ' + convert(char(2),@sn) Set @sn = @sn +4, set @d = @d +1 if (@d = 10) break End Nguyễn Thị Thúy Loan Trang 165 2/12/2011 Nguyễn Thị Thúy Loan Trang 166 2/12/2011 Cấu trúc lặp WHILE Cấu trúc lặp WHILE WHILE Biểu_thức_luận_lý . Thực hiện giống ví dụ trước, nhưng không in BEGIN số nguyên 30. Chúng ta sử dụng cấu trúc lặp Các_lệnh_nhóm_lặp_1 WHILE như sau: [IF Biểu_thức_lặp_Tiếp CONTINUE] [IF Biểu_thức_thoát BREAK] Các_lệnh_nhóm_lặp_2 END Các lệnh khác Nguyễn Thị Thúy Loan Trang 168 2/12/2011
  43. 43 Cấu trúc lặp WHILE NỘI DUNG Declare @sn int, @d int . Biến cục bộ. Set @sn = 10 Set @d =0 . Biến hệ thống. While (@d<10) . Các câu lệnh truy vấn dữ liệu. Begin . Cấu trúc điều khiển. Set @sn = @sn +4; if (@sn = 30) continue . Biến kiểu dữ liệu cursor. Set @d = @d +1; Print 'so nguyen la: ' + convert(char(2),@sn) . Các hàm thường dùng. End Nguyễn Thị Thúy Loan Trang 169 2/12/2011 Nguyễn Thị Thúy Loan Trang 170 2/12/2011 Biến kiểu dữ liệu cursor Biến kiểu dữ liệu cursor . CSDL quan hệ thường làm việc trên dữ liệu có nhiều dòng mẫu tin – còn gọi là các bộ mẫu tin. Ví dụ lệnh SELECT kết quả luôn trả về nhiều dòng dữ liệu hơn là một dòng dữ liệu. Tuy nhiên có một số ngôn ngữ lập trình việc xử lý và tính toán dữ liệu trên từng dòng riêng lẻ. Để đáp ứng được yêu cầu này SQL Server tạo ra một kiểu dữ liệu đó chính là kiểu cursor. Nguyễn Thị Thúy Loan Trang 171 2/12/2011 Nguyễn Thị Thúy Loan Trang 172 2/12/2011
  44. 44 Biến kiểu dữ liệu cursor Biến kiểu dữ liệu cursor Các bước sử dụng kiểu dữ liệu cursor . Cú pháp định nghĩa biến có kiểu cursor . Định nghĩa biến kiểu cursor bằng lệnh DECLARE Tên_cursor CURSOR DECLARE. [LOCAL | GLOBAL] . Sử dụng lệnh OPEN để mở ra cursor đã định [FORWARD_ONLY | SCROLL] nghĩa trước đó. [STATIC | DYNAMIC | KEYSET] . Đọc và xử lý trên từng dòng dữ liệu bên trong [READ_ONLY | SCROLL_LOCK] cursor. FOR Câu_lệnh SELECT . Đóng cursor bằng lệnh CLOSE và [FOR UPDATE [OF danh_sách_cột_n]] DEALLOCATE. Nguyễn Thị Thúy Loan Trang 173 2/12/2011 Nguyễn Thị Thúy Loan Trang 174 2/12/2011 Biến kiểu dữ liệu cursor Biến kiểu dữ liệu cursor Trong đó: . SCROLL: Đọc dữ liệu trong cursor được phép . Tên cursor: tên của biến kiểu cursor di chuyển tới lui, qua lại các dòng mẫu tin bên . Từ khoá LOCAL | GLOBAL: dùng chỉ phạm trong cursor tùy thích. vi hoạt động của biến cursor hoặc là cục bộ (local) bên trong một thủ tục. . FORWARD_ONLY: đọc dữ liệu trong cursor theo chiều đi tới duyệt từ mẫu tin đầu tiên đến mẫu tin cuối cùng. Nguyễn Thị Thúy Loan Trang 175 2/12/2011 Nguyễn Thị Thúy Loan Trang 176 2/12/2011
  45. 45 Biến kiểu dữ liệu cursor Biến kiểu dữ liệu cursor . STATIC: Đọc dữ liệu bên trong cursor tĩnh. . DYNAMIC: dùng chỉ định dữ liệu trong cursor Khi đónếu những người dùng khác có thay đổi là động. Khi đóviệc cập nhật dữ liệu trong bên dưới dữ liệu gốc thì các thay đổi đósẽ bảng cơ sở bởi những người dùng khác sẽ được không được cập nhật tự động trong dữ liệu của cập nhật tự động trong dữ liệu cursor có kiểu là cursor. Bởi vì khi đódữ liệu trong cursor chính DYNAMIC. là dữ liệu của bảng tạm đã được hệ thống sao chép và lưu trữ trong CSDL tempdb của hệ thống khi định nghĩa cursor. Nguyễn Thị Thúy Loan Trang 177 2/12/2011 Nguyễn Thị Thúy Loan Trang 178 2/12/2011 Biến kiểu dữ liệu cursor Biến kiểu dữ liệu cursor . KEYSET: hoạt động giống với kiểu . READ_ONLY: chỉ định dữ liệu trong cursor DYNAMIC, các thay đổi dữ liệu trên các cột chỉ đọc nhằm hạn chế việc sửa đổi dữ liệu bên không là khóa chính trong bảng cơ sở bởi những người dùng khác sẽ được cập nhật trong trong cursor. Khi khai báo cursor với kiểu dữ dữ liệu cursor. Tuy nhiên đối với mẫu tin vừa liệu tĩnh (STATIC) thì dữ liệu trong cursor thêm mới hoặc các mẫu tin đã bị hủy bỏ bởi xem như chỉ đọc. những người dùng khác sẽ không được hiển thị trong dữ liệu cursor có kiểu là KEYSET. Nguyễn Thị Thúy Loan Trang 179 2/12/2011 Nguyễn Thị Thúy Loan Trang 180 2/12/2011
  46. 46 Biến kiểu dữ liệu cursor Biến kiểu dữ liệu cursor . SCROLL_LOCK: chỉ định hệ thống SQL . SELECT: dùng để chỉ đến các cột bên trong Server tự động khóa các dòng mẫu tin cần phải bảng mà chúng ta cần đọc dữ liệu. thay đổi giá trị hoặc hủy bỏ bên trong bảng . Danh sách các cột cập nhật: chỉ định danh sách nhằm bảo đảm các hành động cập nhật luôn tên các cột sẽ được phép thay đổi giá trị trong thành công. cursor. Nguyễn Thị Thúy Loan Trang 181 2/12/2011 Nguyễn Thị Thúy Loan Trang 182 2/12/2011 Biến kiểu dữ liệu cursor Biến kiểu dữ liệu cursor . Ví dụ 1: để định nghĩa một biến cursor chứa . Ví dụ 2: Định nghĩa một biến cursor chứa toàn toàn bộ các dòng dữ liệu bên trong bảng SV, bộ các dòng dữ liệu bên trong bảng MH, các dữ liệu trong cursor chỉ được phép đọc và việc các dòng dữ liệu trong cursor cho phép được đọc dữ liệu trong cursor chỉ theo một chiều đi cập nhật. tới. Declare C_SV cursor dynamic Declare C_MH cursor forward_only [static] For select * From SV Read_only For select * From MH Nguyễn Thị Thúy Loan Trang 183 2/12/2011 Nguyễn Thị Thúy Loan Trang 184 2/12/2011
  47. 47 Biến kiểu dữ liệu cursor Biến kiểu dữ liệu cursor . Mở Cursor . Ví dụ: Mở các cursor đã định nghĩa ở ví dụ 1 . Cú pháp: trên. Chúng ta sử dụng lệnh OPEN như sau: OPEN Tên_cursor OPEN C_SV . Trong đó: Tên cursor: tên của biến cursor đã được định nghĩa trước đóbằng lệnh DECLARE Nguyễn Thị Thúy Loan Trang 185 2/12/2011 Nguyễn Thị Thúy Loan Trang 186 2/12/2011 Biến kiểu dữ liệu cursor Biến kiểu dữ liệu cursor . Đọc và xử lý dữ liệu trong cursor Trong đó: . Next, Prior, First, Last: dùng để đọc dữ liệu FETCH [Next | Prior | First | Last | kế tiếp, trước, đầu, sau cùng. Absolute n | Relative n ] . Absolute: dữ liệu chính xác thứ n trong cursor. FROM Tên_cursor N>0 chỉ định việc đọc dữ liệu tại dòng thứ n [INTO danh_sách_biến] đếm từ dòng đầu tiên, n<0 dùng chỉ định việc đọc dữ liệu tại dòng thứ n được đếm ngược từ dòng cuối trở lên. Nguyễn Thị Thúy Loan Trang 187 2/12/2011 Nguyễn Thị Thúy Loan Trang 188 2/12/2011
  48. 48 Biến kiểu dữ liệu cursor Biến kiểu dữ liệu cursor Trong đó: . Tên_cursor: tên của biến cursor đã định nghĩa . Relative: dùng chỉ định việc đọc dữ liệu tại trước đóbằng lệnh DECLARE. một dòng tương đối so với dòng dữ liệu hiện . Danh sách biến: danh sách tên các biến cục bộ đã được định nghĩa trước đó. Các biến này hành. N là một số nguyên có thể dương có thể sẽ lưu trữ các giá trị dữ liệu được đọc từ lệnh âm để chỉ định theo chiều tới hoặc lui so với FETCH. dòng dữ liệu hiện hành. Nguyễn Thị Thúy Loan Trang 189 2/12/2011 Nguyễn Thị Thúy Loan Trang 190 2/12/2011 Biến kiểu dữ liệu cursor Biến kiểu dữ liệu cursor Đóng cursor . Cú pháp: CLOSE Tên_cursor DEALLOCATE Tên_cursor Nguyễn Thị Thúy Loan Trang 191 2/12/2011 Nguyễn Thị Thúy Loan Trang 192 2/12/2011
  49. 49 Biến kiểu dữ liệu cursor Biến kiểu dữ liệu cursor Trong đó . SQL Server cung cấp một biến hệ thống . CLOSE giải phóng các dòng dữ liệu tham @@FETCH_STATUS dùng để kiểm tra tình chiếu bên trong cursor. trạng đọc dữ liệu thành công hay thất bại. Giá . Lệnh DEALLOCATE giải phóng thật sự biến trị trả về 0 khi việc đọc dữ liệu là thành công. cursor ra khỏi bộ nhớ Nguyễn Thị Thúy Loan Trang 193 2/12/2011 Nguyễn Thị Thúy Loan Trang 194 2/12/2011 Biến kiểu dữ liệu cursor Biến kiểu dữ liệu cursor Cho lược đồ quan hệ như sau: . Ví dụ 1: Đọc dữ liệu cursor của bảng . MAT_HG (MaMH, TenMH, DVT, MaNCC) MAT_HANG chỉ đọc các vật tư là Tivi . Pnhap (MaPN, NgayNhap, ThanhTien) . CTPNhap (MaMH, MaPN, SLNhap, DonGia) Nguyễn Thị Thúy Loan Trang 195 2/12/2011 Nguyễn Thị Thúy Loan Trang 196 2/12/2011
  50. 50 Biến kiểu dữ liệu cursor Biến kiểu dữ liệu cursor Khai báo biến cursor Đọc dữ liệu declare cr_MatHang cursor keyset FETCH NEXT FROM cr_MatHang FOR SELECT * FROM MAT_HANG WHILE @@FETCH_STATUS = 0 WHERE MaMH like ‘TV%’ BEGIN ORDER BY MaMH Đọc tiếp dòng kế Mở cursor FETCH NEXT FROM cr_MatHang OPEN cr_MatHang END Nguyễn Thị Thúy Loan Trang 197 2/12/2011 Nguyễn Thị Thúy Loan Trang 198 2/12/2011 Biến kiểu dữ liệu cursor Biến kiểu dữ liệu cursor Đóng cursor . Ví dụ 2: Đọc dữ liệu cursor của bảng SV chỉ CLOSE cr_MatHang đọc các sinh viên có họ bắt đầu là L. DEALLOCATE cr_MatHang Nguyễn Thị Thúy Loan Trang 199 2/12/2011 Nguyễn Thị Thúy Loan Trang 200 2/12/2011
  51. 51 Biến kiểu dữ liệu cursor Biến kiểu dữ liệu cursor Khai báo biến cursor Đọc dữ liệu Declare cr_sv cursor keyset Fetch next from cr_sv For select * from SV While @@fetch_status =0 Where tensv like ‘L%' Begin Order by masv Đọc tiếp dòng kế Mở cursor FETCH NEXT FROM cr_SV OPEN cr_SV END Nguyễn Thị Thúy Loan Trang 201 2/12/2011 Nguyễn Thị Thúy Loan Trang 202 2/12/2011 Biến kiểu dữ liệu cursor Biến kiểu dữ liệu cursor Đóng cursor . Ví dụ 3: Cập nhật dữ liệu cho cột ThanhTien CLOSE cr_SV trong bảng PNHAP bằng cách duyệt qua từng DEALLOCATE cr_SV phiếu nhập, tính ra trị giá nhập của từng phiếu căn cứ vào số lượng nhập và đơn giá nhập của từng vật tư trong bảng CTPNHAP, sau cùng cập nhật vào cột ThanhTien. Nguyễn Thị Thúy Loan Trang 203 2/12/2011 Nguyễn Thị Thúy Loan Trang 204 2/12/2011
  52. 52 Biến kiểu dữ liệu cursor Biến kiểu dữ liệu cursor Khai báo biến cursor, các biến cục bộ Mở cursor declare @Sopn char(4), @TongTT Money OPEN cr_Pnhap DECLARE cr_Pnhap CURSOR FORWARD_ONLY FOR SELECT MAPN FROM PNHAP Nguyễn Thị Thúy Loan Trang 205 2/12/2011 Nguyễn Thị Thúy Loan Trang 206 2/12/2011 Biến kiểu dữ liệu cursor Biến kiểu dữ liệu cursor Dịch chuyển con trỏ Đọc dữ liệu và cập nhật giá trị cr_Pnhap vào @SoPN fetch next from cr_Pnhap into @Sopn Đóng cursor while @@fetch_status = 0 CLOSE cr_Pnhap begin select @Tongtt = sum(SLNhap*dongia) DEALLOCATE cr_Pnhap from ctpnhap where mapn = @sopn Print ‘dang cap nhat phieu nhap: ’ + @SoPN update pnhap set Thanhtien = @TongTT Where Current OF cr_Pnhap// mapn=@SOPN dịch con trỏ đến dòng kế tiếp fetch next from cr_Pnhap into @Sopn end Nguyễn Thị Thúy Loan Trang 208 2/12/2011
  53. 53 Biến kiểu dữ liệu cursor Biến kiểu dữ liệu cursor Đọc dữ liệu và cập nhật giá trị Khai báo biến cursor, các biến cục bộ while (0 = 0) begin declare @sopn char(4), @tongtt money fetch next from cur_pnhap into @sopn if @@fetch_status<>0 Break declare cr_pnhap cursor forward_only Select @Tongtg = Sum(SLNhap*dongia) for From Ctpnhap where mapn = @sopn select mapn from pnhap Print ‘dang cap nhat phieu nhap: ’ + @sopn Mở cursor Update Pnhap Set Thanhtien = @TongTT Open cr_Pnhap where current of cr_pnhap Nguyễn Thị Thúy Loan Trang 209 2/12/2011 end Biến kiểu dữ liệu cursor Biến kiểu dữ liệu cursor Đóng cursor DECLARE Tên_cursor CURSOR CLOSE cr_Pnhap {kiểu đọc | cập nhật dữ liệu} DEALLOCATE cr_Pnhap FOR Câu lệnh SELECT Mở cursor OPEN Tên_cursor Nguyễn Thị Thúy Loan Trang 211 2/12/2011 Nguyễn Thị Thúy Loan Trang 212 2/12/2011
  54. 54 Biến kiểu dữ liệu cursor Biến kiểu dữ liệu cursor Đọc dữ liệu và cập nhật giá trị Đóng cursor WHILE (0=0) CLOSE Tên_cursor Begin DEALLOCATE Tên_cursor FETCH NEXT FROM [INTO danh_sách_biến] IF @@FETCH_STATUS <> 0 Break End Nguyễn Thị Thúy Loan Trang 213 2/12/2011 Nguyễn Thị Thúy Loan Trang 214 2/12/2011 Biến kiểu dữ liệu cursor Biến kiểu dữ liệu cursor Khi nào chúng ta cần sử dụng kiểu dữ liệu cursor . Khi cần giải quyết vấn đề cập nhật dữ liệu thì trong Transaction-SQL để giải quyết các vấn luôn ưu tiên chọn các hướng giải quyết trên bộ đề: mẫu tin bởi vì khi đó làm cho các bộ xử lý . SQL Server là một hệ quản trị CSDL quan hệ được nhanh hơn. (Relational Database Management System) do . Sau cùng là hướng giải quyết theo kiểu cursor đó chúng ta nên chọn giải pháp làm việc trên là giải pháp sau cùng nhất để chọn lựa khi các bộ mẫu tin. không còn giải pháp nào tốt hơn Nguyễn Thị Thúy Loan Trang 215 2/12/2011 Nguyễn Thị Thúy Loan Trang 216 2/12/2011
  55. 55 NỘI DUNG Các hàm thường dùng . Biến cục bộ. . Các hàm chuyển đổi kiểu dữ liệu . Biến hệ thống. . Hàm CAST: chuyển đổi một biểu thức nào đó . Các câu lệnh truy vấn dữ liệu. sang một kiểu dữ liệu mong muốn. . Cấu trúc điều khiển. . Cú pháp: . Biến kiểu dữ liệu cursor. CAST(Biểu_thức AS kiểu_dữ_liệu) . Các hàm thường dùng. Nguyễn Thị Thúy Loan Trang 217 2/12/2011 Nguyễn Thị Thúy Loan Trang 218 2/12/2011 Các hàm thường dùng Các hàm thường dùng . Ví dụ: . Hàm CONVERT: chuyển đổi một biểu thức SELECT MaVTU, TenVT, nào đó sang một kiểu dữ liệu bất kỳ mong TyLe = cast(phantram as varchar(3)) + “%” muốn nhưng có thể theo một định dạng nào đó. FROM VATTU . Cú pháp: Convert (Kiểu_dữ_liệu, Biểu_thức[, định_dạng]) Nguyễn Thị Thúy Loan Trang 219 2/12/2011 Nguyễn Thị Thúy Loan Trang 220 2/12/2011
  56. 56 Các hàm thường dùng Các hàm thường dùng STT Định dạng năm (yyyy) Hiển thị dữ liệu . Ví dụ: 1 101 Mm/dd/yy 2 102 yy.mm.dd select sohd, 3 103 Dd/mm/yy 4 104 dd.mm.yy 5 105 dd-mm-yy convert(char(10),ngayhd,103) as ngayhd 6 106 Dd mon yy 7 107 Mon dd, yy from dondh 8 108 Hh:mm:ss 9 109 Mon dd yyyy hh:mm:ss 10 110 mm-dd-yy 11 111 Yy/mm/dd 12 112 Yymmdd 13 113 Dd mon yyyy hh:mm:ss 14 114 Hh:mm:ss:mmm 15 21 hoặc 121 Yyyy-mm-dd hh:mi:ss.mmm Nguyễn Thị Thúy Loan Trang 221 2/12/2011 16 20 hoặc 120 Yyyy-mm-dd hh:mi:ss Các hàm thường dùng Các hàm thường dùng . Hàm STR: chuyển đổi kiểu dữ liệu số sang . Ví dụ: kiểu dữ liệu chuỗi. Phải đảm bảo đủ khoảng Select Tensv, D = Str(diem,5,2)+' ' +'Diem' trắng để chứa các ký số khi chuyển sang kiểu From Sv, Kq dữ liệu chuỗi. Where Sv.Masv = kq.masv . Cú pháp: STR(Số_thực, Số_ký_tự[, Số_lẻ]) Nguyễn Thị Thúy Loan Trang 223 2/12/2011 Nguyễn Thị Thúy Loan Trang 224 2/12/2011
  57. 57 Các hàm thường dùng Các hàm thường dùng . Các hàm về ngày . DATENAME: trả về một chuỗi thời gian đại . DATEDIFF: trả về 1 số nguyên khoảng cách diện của 1 ngày chỉ định theo một đơn vị thời của hai ngày theo một đơn vị thời gian bất kỳ. gian bất kỳ DATEDIFF(don_vi, ngay1, ngay2) DATENAME(Don_vi, ngay) . Ví dụ: Select tensv, . Ví dụ: so_ng = datediff(dd, ngsinh, getdate()) select tensv, thu = datename(dw,ngsinh) From Sv from sv Nguyễn Thị Thúy Loan Trang 225 2/12/2011 Nguyễn Thị Thúy Loan Trang 226 2/12/2011 Các hàm thường dùng Các hàm thường dùng . GETDATE: trả về ngày giờ hiện hành của hệ . Ví dụ: thống Select tensv, ngsinh, GETDATE() ngay =datepart (dd,ngsinh) . DATEPART: trả về 1 số nguyên chỉ định thời From sv gian đại diện của 1 ngày theo một đơn vị thời gian bất kỳ DATEPART(Don_vi, ngay) Nguyễn Thị Thúy Loan Trang 227 2/12/2011 Nguyễn Thị Thúy Loan Trang 228 2/12/2011
  58. 58 Chương IV NỘI DUNG . Khái niệm STORE PROCEDURE . Thủ tục . Thủ tục với tham số đầu vào (THỦ TỤC) . Thủ tục với tham số đầu ra . Thủ tục có lệnh trả về Return . Sử dụng bảng tạm trong thủ tục ThS. Nguyễn Thị Thúy Loan . Tham số cursor bên trong thủ tục . Giao tác (Tracsaction) Nguyễn Thị Thúy Loan Trang 230 2/12/2011 Khái niệm . Stored procedure là một tập các lệnh Transact SQL được đặt tên và lưu trữ trong database server. Khái niệm . Có thể nhận tham số vào và tham số trả giá trị ra. . Trả về trạng thái thực thi của procedure là thành công hay không thành công. Nguyễn Thị Thúy Loan Trang 232 2/12/2011
  59. 59 Khái niệm Khái niệm Các nét đặc trưng . Thủ tục nội tại là một tập hợp chứa các dòng . Tên thủ tục. lệnh, các biến và các cấu trúc điều khiển trong . Tham số truyền giá trị vào. ngôn ngữ Transaction-SQL dùng để thực hiện . Tham số nhận giá trị ra . . Trong thủ tục nội tại được phép gọi thực thi một hành động nào đó. một thủ tục nội tại khác. . Có tính cục bộ bên trong một cơ sở dữ liệu lưu trữ thủ tục đó. . Có thể gọi thực hiện trong môi trường không phải Microsoft SQL Server. Nguyễn Thị Thúy Loan Trang 233 2/12/2011 Nguyễn Thị Thúy Loan Trang 234 2/12/2011 Khái niệm NỘI DUNG . Lợi ích của thủ tục: . Khái niệm o Tốc độ xử lý của các thủ tục nội tại rất nhanh. . Thủ tục o Việc tổ chức và phân chia các xử lý thành hai . Thủ tục với tham số đầu vào nơi khác nhau: tại máy chủ hoặc tại máy trạm . Thủ tục với tham số đầu ra sẽ giúp giảm thời gian xây dựng ứng dụng. . Thủ tục có lệnh trả về Return . Thủ tục hệ thống . Sử dụng bảng tạm trong thủ tục o Bắt đầu bằng chữ sp_ và hầu hết tất cả các thủ . Tham số cursor bên trong thủ tục tục hệ thống được lưu trữ bên trong CSDL Master. . Giao tác (Tracsaction) Nguyễn Thị Thúy Loan Trang 235 2/12/2011 Nguyễn Thị Thúy Loan Trang 236 2/12/2011
  60. 60 Thủ tục Thủ tục . Tạo mới thủ tục . Ví dụ: Tạo procedure để liệt kê danh sách tất . Cú pháp: cả các mặt hàng. CREATE PROC[EDURE] Tên_thủ_tục CREATE PROC P_MH AS AS [Declare biến_cục_bộ] SELECT * các_lệnh FROM MH . Gọi thực thi thủ tục EXEC[UTE] P_MH Nguyễn Thị Thúy Loan Trang 237 2/12/2011 Nguyễn Thị Thúy Loan Trang 238 2/12/2011 Thủ tục Thủ tục . Ví dụ: Tạo procedure để cập nhật giá cho mặt . Ví dụ: Cho biết số lượng sinh viên trong lớp? hàng kẹo là 17000. CREATE PROC P_DEMSV @MALOP create proc P_capnhat CHAR(9), @SLSV SMALLINT OUTPUT as AS update ctdh set Dgdat = 17000 SELECT @SLSV = COUNT(*) where mamh = 'keo' FROM SV . Gọi thực thi thủ tục WHERE @MALOP = MALOP exec P_capnhat Nguyễn Thị Thúy Loan Trang 239 2/12/2011 Nguyễn Thị Thúy Loan Trang 240 2/12/2011
  61. 61 Thủ tục Thủ tục DECLARE @SL SMALLINT . Thay đổi nội dung thủ tục EXEC P_DemSV '07CDTH', o Cú pháp: @SLSV = @SL OUTPUT ALTER PROC[EDURE] Tên_thủ_tục PRINT 'SO LUONG SINH VIEN O LOP: ' + AS [Declare biến_cục_bộ] CAST(@SL AS CHAR(3)) Các_lệnh. Nguyễn Thị Thúy Loan Trang 241 2/12/2011 Nguyễn Thị Thúy Loan Trang 242 2/12/2011 Thủ tục Thủ tục Alter Proc P_Demsv @Malop Char(9) . Ví dụ: cho lược đồ CSDL như sau: As MAT_HG(MaMH, TenMH, DVT, MaNCC) Select Malop, Tenlop PH_XUAT(SoPX, NgXuat, SoDH) From Lop CTPX(MaMH, SoPX, SLX, DGX) Where @Malop = Malop DDH(MaDH, NgDat) CTDH(MaDH, MaMH, SLD, DGDat) Nguyễn Thị Thúy Loan Trang 243 2/12/2011 Nguyễn Thị Thúy Loan Trang 244 2/12/2011
  62. 62 Thủ tục Thủ tục . Ví dụ: Cho biết mặt hàng nào có SO LUONG bán cao CREATE PROC p_MaxSLBan AS Declare @TenMH varchar(50), @MaxSL int nhất trong tháng 01/2007. Select @TenMH = RTRIM(TenMH), @MaxSL = SLXuat From CTPX, PH_XUAT, MAT_HG Where CTPX.SoPX = PH_XUAT.SoPX And MAT_HG.MaMH = CTPX.MaMH And convert(char(7), NgayXuat, 21) = ‘2007-01’ And SLXuat = (Select Max(SLXuat) From CTPX, PH_XUAT Where CTPX.SoPX = PH_XUAT.SoPX And convert(char(7), NgayXuat, 21) = ‘2007-01’) Print @TenMH + ‘Co doanh so cao nhat la’ + Nguyễn Thị Thúy Loan Trang 245 2/12/2011 Cast(@MaxSL as char(10)) Thủ tục Thủ tục . Gọi thực hiện thủ tục . Ví dụ: Cho biết mặt hàng nào có SO LUONG bán cao exec p_maxslban nhất trong tháng 01/2007. Nếu không có thì hãy thông báo. Nguyễn Thị Thúy Loan Trang 247 2/12/2011 Nguyễn Thị Thúy Loan Trang 248 2/12/2011
  63. 63 Thủ tục Thủ tục alter proc p_maxslban Select @tenmh = rtrim(tenmh), @maxsl = slxuat As declare @tenmh varchar(50), @maxsl int Fromctpx, px, mh if not exists(select mamh Where ctpx.sopx = px.sopx and from ctpx, px mh.mamh = ctpx.mamh and convert(char(7), where ctpx.sopx = px.sopx ngayxuat, 21)= ‘2007-01’ and slxuat = and convert(char(7), ngxuat, 21)= '2007-01') (select max(slxuat) Begin from ctpx, phieu_xuat where ctpx.sopx=phieu_xuat.sopx Print 'thang 01 nam 2007 chưa bán mặt hàng nào cả'; and convert(char(7), ngayxuat, 21)= ‘2007-01’) Return print @tenmh + ‘co doanh so cao nhat la’ + End cast(@maxsl as char(10)) Nguyễn Thị Thúy Loan Trang 249 2/12/2011 Nguyễn Thị Thúy Loan Trang 250 2/12/2011 Thủ tục NỘI DUNG . Gọi thực hiện thủ tục . Khái niệm exec p_maxslban . Thủ tục . Thủ tục với tham số đầu vào . Thủ tục với tham số đầu ra . Thủ tục có lệnh trả về Return . Sử dụng bảng tạm trong thủ tục . Tham số cursor bên trong thủ tục . Giao tác (Tracsaction) Nguyễn Thị Thúy Loan Trang 251 2/12/2011 Nguyễn Thị Thúy Loan Trang 252 2/12/2011
  64. 64 Thủ tục với tham số đầu vào Thủ tục với tham số đầu vào . Cú pháp: . Ví dụ 1: Nhập vào tên sinh viên, cho biết sinh CREATE PROC[EDURE] Tên_thủ_tục viên đóxuất hiện bao nhiêu lần? @Tên_tham_số kiểu_dữ_liệu [= giá_trị] AS [Declare biến_cục_bộ] các_lệnh Nguyễn Thị Thúy Loan Trang 253 2/12/2011 Nguyễn Thị Thúy Loan Trang 254 2/12/2011 Thủ tục với tham số đầu vào Thủ tục với tham số đầu vào create proc p_svien @tsv varchar(50) . Gọi thực hiện thủ tục As if not exists (select rtrim(Tensv) from sv exec p_svien ‘Nguyen Van A’ where @tsv = tensv) print ‘Khong co’ else begin declare @sl int select @sl = count(*) from sv where @tsv = tensv print 'So lan xh: ' + cast(@sl as char(2)) end Nguyễn Thị Thúy Loan Trang 255 2/12/2011 Nguyễn Thị Thúy Loan Trang 256 2/12/2011
  65. 65 Thủ tục với tham số đầu vào Thủ tục với tham số đầu vào . Ví dụ 2: Tạo thủ tục tính tổng giá trị của một CREATE PROC p_TGTX @SoPX char(5) AS Declare @TongTG money phiếu xuất hàng hoá có một tham số vào là số Select @TongTG=SUM(SLX*DGX) phiếu xuất với kiểu dữ liệu là chuỗi. From CTPX Where @SoPX=SoPX print ‘Tri gia phieu xuat’+ cast(@sopx as char(5)) Print ‘là: ’ + CAST(@TongTG as char(10)) Nguyễn Thị Thúy Loan Trang 257 2/12/2011 Nguyễn Thị Thúy Loan Trang 258 2/12/2011 Thủ tục với tham số đầu vào Thủ tục với tham số đầu vào . Gọi thực hiện thủ tục . Ví dụ 3: Tạo thủ tục tính số lượng đặt hàng của Exec p_TGTX ‘PX003’ một mặt hàng trong một đơn đặt hàng có 2 . Hoặc: Exec p_TGTX @SoPX=‘PX003’ tham số đầu vào là số đặt hàng và mã mặt hàng. Nguyễn Thị Thúy Loan Trang 259 2/12/2011 Nguyễn Thị Thúy Loan Trang 260 2/12/2011
  66. 66 Thủ tục với tham số đầu vào Thủ tục với tham số đầu vào create proc p_tinhsldat @sodh char(4), @mamh Select @SLDat = SLDat char(4) From CTDH AS Declare @Sldat int Where @sodh= madh and @mamh = mamh if not exists (select madh from ctdh Print ‘Don dat hang ’ + @SoDH where madh = @sodh and mamh = @mamh) Print ‘Voi ma mat hang ’ + @MaMH begin Print ‘Co so luong dat la: ’ + Cast(@SLDat print ‘khong hop le, xem lai don dat hang’ as varchar(10)) return end Nguyễn Thị Thúy Loan Trang 261 2/12/2011 Nguyễn Thị Thúy Loan Trang 262 2/12/2011 Thủ tục với tham số đầu vào Thủ tục với tham số đầu vào . Gọi thực hiện thủ tục: . Ví dụ 4: tạo thủ tục thêm mới dữ liệu vào bảng Exec p_TinhSLDat ‘001’, ‘Gao’ MAT_HANG với tên p_MATHANG_Them . Hoặc gồm có 4 tham số vào chính là các giá trị thêm mới cho các cột trong bảng MAT_HANG: mã exec p_tinhsldat @mamh=‘gao’, mặt hàng, tên mặt hàng, đơn vị tính, mã NCC. @sodh = ‘001’ Trong đócần kiểm tra các ràng buộc dữ liệu phải hợp lệ trước khi thực hiện lệnh INSERT INTO để thêm dữ liệu vào bảng MAT_HANG. Mã mặt hàng phải duy nhất. Nguyễn Thị Thúy Loan Trang 263 2/12/2011 Nguyễn Thị Thúy Loan Trang 264 2/12/2011
  67. 67 Thủ tục với tham số đầu vào Thủ tục với tham số đầu vào Create proc p_mathang_them . Gọi thực hiện thủ tục @mamh char(4), @tenmh varchar(50), exec p_mathang_them 'Dau', 'Dau nuoc cot @dvt char(10),@ncc char(10) dua', 'goi', ' Cty Z' As if exists(select mamh from mh where mamh = @mamh) Begin print N'Mã mặt hàng ['+@MaMH+'] đã có' return end insert into mh(mamh, tenmh, dvt, mancc) values(@mamh, @tenmh, @dvt, @ncc) Nguyễn Thị Thúy Loan Trang 265 2/12/2011 Nguyễn Thị Thúy Loan Trang 266 2/12/2011 NỘI DUNG Thủ tục với tham số đầu ra . Khái niệm . Cú pháp: . Thủ tục CREATE PROC Tên_thủ_tục . Thủ tục với tham số đầu vào @Tên_tham_số kiểu_dữ_liệu OUTPUT [, ] . Thủ tục với tham số đầu ra AS . Thủ tục có lệnh trả về Return [Declare Biến cục bộ] . Sử dụng bảng tạm trong thủ tục Các_lệnh . Tham số cursor bên trong thủ tục . Giao tác (Tracsaction) Nguyễn Thị Thúy Loan Trang 267 2/12/2011 Nguyễn Thị Thúy Loan Trang 268 2/12/2011
  68. 68 Thủ tục với tham số đầu ra Thủ tục với tham số đầu ra . Ví dụ 1: Đếm số lượng sinh viên trong lớp. . Gọi thực hiện. create proc p_demsv @malop char(9),@slsv declare @sl smallint smallint output exec p_demsv '08cdth', @slsv =@sl output as print 'so luong sinh vien o lop: ' + cast(@sl as select @slsv=count(*) char(3)) from sv where @malop=malop Nguyễn Thị Thúy Loan Trang 269 2/12/2011 Nguyễn Thị Thúy Loan Trang 270 2/12/2011 Thủ tục với tham số đầu ra Thủ tục với tham số đầu ra . Ví dụ 2: tạo thủ tục tính số lượng đặt hàng của create proc p_tinhsldat @sodh char(4), @mamh char(4), @sldat int output một mặt hàng trong một đơn đặt hàng có 2 as if not exists(select madh from ctdh tham số vào là số đặt hàng và mã mặt hàng, Where madh=@sodh And mamh=@mamh) trả ra số lượng đặt hàng của vật tư tương ứng Begin Print 'khong hop le, xem lai don dat hang‘ trong đơn đặt hàng thông qua tham số đầu ra. Return End Select @SLDat = SL From CTDH Nguyễn Thị Thúy Loan Trang 271 2/12/2011 Where Madh = @sodh and mamh = @mamh
  69. 69 Thủ tục với tham số đầu ra NỘI DUNG . Gọi thực hiện thủ tục . Khái niệm DECLARE @SLDatHang int . Thủ tục EXEC p_TinhSLDat @MaMH = ‘Gao’, . Thủ tục với tham số đầu vào @SoDH = ‘001’, @SLDat = @SLDatHang . Thủ tục với tham số đầu ra OUTPUT . Thủ tục có lệnh trả về Return Print ‘Don dat hang 001 với mặt hàng Gao’ . Sử dụng bảng tạm trong thủ tục Print ‘co so luong dat la: ’ + . Tham số cursor bên trong thủ tục CAST(@SLDatHang AS varchar(10)) . Giao tác (Tracsaction) Nguyễn Thị Thúy Loan Trang 273 2/12/2011 Nguyễn Thị Thúy Loan Trang 274 2/12/2011 Thủ tục có lệnh trả về Return Thủ tục có lệnh trả về Return . Return không có giá trị chỉ định thì thủ tục sẽ . Ví dụ: Tạo thủ tục tính tổng số lượng đặt hàng trả về giá trị là không (0). của một mặt hàng đối với một nhà cung cấp chỉ định, kiểm tra xem giá trị của mặt hàng và . Return [Số_nguyên] mã nhà cung cấp mà người dùng truyền vào thủ tục có đúng hay không? Qui định thủ tục trả về 1 khi mã mặt hàng không tồn tại, trả về 2 khi mã nhà cung cấp không tồn tại. Nguyễn Thị Thúy Loan Trang 275 2/12/2011 Nguyễn Thị Thúy Loan Trang 276 2/12/2011
  70. 70 Thủ tục có lệnh trả về Return Thủ tục có lệnh trả về Return create proc p_tgsldat @mancc char(10), Gọi thực hiện thủ tục: @mamh char(10), @tgsldat int output declare @tgsld int, @ketqua int As if not exists(select * from mh exec @ketqua = p_tgsldat 'Cty a', 'gao', where mamh = @mamh); return 1 @tgsldat = @tgsld output if not exists(select * from mh if @ketqua =1 where mancc = @mancc); return 2 select @tgsldat = sum(ctdh.sld) Print N'Mã mặt hàng không hợp lệ‘ from ctdh, ddh, mh else if @ketqua=2 where ddh.madh = ctdh.madh and Print N'Mã nhà cung cấp không hợp lệ‘ mancc = @mancc and mh.mamh = @mamh else Print N'Tổng số lượng đặt là: ' + if @tgsldat is null set @tgsldat=0 cast(@tgsld as char(10)) return Nguyễn Thị Thúy Loan Trang 278 2/12/2011 NỘI DUNG Sử dụng bảng tạm trong SP . Khái niệm . Cú pháp: . Thủ tục SELECT danh_sách_các_cột INTO . Thủ tục với tham số đầu vào #Tên_bảng_tạm . Thủ tục với tham số đầu ra FROM Tên_bảng_dữ_liệu . Thủ tục có lệnh trả về Return (#): tạo ra các bảng tạm cục bộ . Sử dụng bảng tạm trong thủ tục (##): tạo ra các bảng tạm toàn cục . Tham số cursor bên trong thủ tục . Giao tác (Tracsaction) Nguyễn Thị Thúy Loan Trang 279 2/12/2011 Nguyễn Thị Thúy Loan Trang 280 2/12/2011
  71. 71 Sử dụng bảng tạm trong SP Sử dụng bảng tạm trong SP create proc p_max @namthang char(7), @tenmh . Ví dụ: Tạo thủ tục cho biết tên mặt hàng nào varchar(50) output, @tongtien money output có doanh thu bán ra cao nhất trong một năm as select mh.mamh, tenmh, sum(slx*dgx) as tt into #doanhthu tháng bất kỳ. from px, ctpx, mh where px.sopx = ctpx.sopx and ctpx.mamh = mh.mamh and convert(char(7), ngxuat, 21) = @namthang group by mh.mamh, tenmh order by 3 desc select top 1 @tenmh=tenmh, @tongtien = tt Nguyễn Thị Thúy Loan Trang 281 2/12/2011 from #doanhthu Sử dụng bảng tạm trong SP NỘI DUNG Gọi thực hiện thủ tục . Khái niệm Declare @tenmh varchar(50), @tongtien money . Thủ tục Exec p_max '2009-10', @tenmh output, @tongtien . Thủ tục với tham số đầu vào output . Thủ tục với tham số đầu ra if @tenmh is null . Thủ tục có lệnh trả về Return Print N'không có dữ liệu tính toán' . Sử dụng bảng tạm trong thủ tục else . Tham số cursor bên trong thủ tục print @tenmh + N' có doanh thu cao nhất' + N' . Giao tác (Tracsaction) là'+cast(@tongtien as char(10))+ ' vnd' Nguyễn Thị Thúy Loan Trang 283 2/12/2011 Nguyễn Thị Thúy Loan Trang 284 2/12/2011
  72. 72 Tham số cursor bên trong SP Tham số cursor bên trong SP . Tham số cursor trả về danh sách các dòng dữ . Ví dụ: tạo thủ tục trả về danh sách các mã mặt liệu theo điều kiện chọn lọc nào đó. . Cursor được chia làm 2 phần: bên trong thủ tục hàng đã bán ra nhiều nhất trong năm tháng nào và bên ngoài thủ tục. đó. o Các hành động trong thủ tục: định nghĩa dữ liệu cho biến kiểu cursor và mở cursor. . Bước 1: tạo thủ tục có tham số kiểu dữ liệu o Các hành động bên ngoài thủ tục: đọc từng cursor chứa danh sách các mặt hàng đã bán ra dòng dữ liệu bên trong cursor và sau cùng là nhiều nhất. đóng cursor lại Nguyễn Thị Thúy Loan Trang 285 2/12/2011 Nguyễn Thị Thúy Loan Trang 286 2/12/2011 Tham số cursor bên trong SP Thủ tục create proc p_tinhdsoban . Ví dụ: cho lược đồ CSDL như sau: @namthang char(6), MAT_HG(MaMH, TenMH, DVT, MaNCC) @cur_dsmh cursor varying output PH_XUAT(SoPX, NgXuat, SoDH) as CTPX(MaMH, SoPX, SLX, DGX) DDH(MaDH, NgDat) CTDH(MaDH, MaMH, SLD, DGDat) Nguyễn Thị Thúy Loan Trang 287 2/12/2011 Nguyễn Thị Thúy Loan Trang 288 2/12/2011
  73. 73 Tham số cursor bên trong SP Tham số cursor bên trong SP Tạo bảng tạm tính ra tổng số lượng bán -Kiểm tra dữ liệu có phát sinh select ctpx.mamh, sum(slx) as tongslban if exists(select mamh from #tongslban) into #tongslban begin from ctpx, mh, px Khởi tạo giá trị biến CURSOR where convert(char(6), ngxuat, 112) = set @cur_dsmh = cursor forward_only @namthang for select mamh, tongslban and ctpx.mamh=mh.mamh from #tongslban and ctpx.sopx=px.sopx where tongslban=(select max(tongslban) group by ctpx.mamh from #tongslban) Nguyễn Thị Thúy Loan Trang 289 2/12/2011 Nguyễn Thị Thúy Loan Trang 290 2/12/2011 Tham số cursor bên trong SP Tham số cursor bên trong SP Mở cursor . Bước 2: đọc cursor, nhận danh sách các mã mặt OPEN @cur_Dsmh hàng đã bán ra nhiều nhất trong tháng 01 năm DROP TABLE #TongSLBan 2009 Return Declare @cur_dsmh cursor, @gtmh int, End @mamh char(4), @tongslban int Khi không có dữ liệu phát sinh exec @gtmh = p_tinhdsoban ‘200709’, @cur_dsmh DROP Table #TongSLBan output Return 1 Xử lý tiếp sau đó IF @Gtmh = 0 Nguyễn Thị Thúy Loan Trang 291 2/12/2011 Nguyễn Thị Thúy Loan Trang 292 2/12/2011
  74. 74 Tham số cursor bên trong SP NỘI DUNG Begin . Khái niệm Print ‘danh sách các mặt hàng’ While(0=0) . Thủ tục Begin . Thủ tục với tham số đầu vào Fetch Next From @cur_Dsmh INTO @MaMH, @TongslBan . Thủ tục với tham số đầu ra IF @@Fetch_status <>0; Break; Print ‘Mã vật tư: ’ + @MaMH . Thủ tục có lệnh trả về Return Print ‘Tổng số lượng:’ + CAST(@TongslBan AS varchar(10)) . Sử dụng bảng tạm trong thủ tục End End . Tham số cursor bên trong thủ tục ELSE . Giao tác (Tracsaction) Print ‘không có bán hàng trong năm tháng chỉ định’ Nguyễn Thị Thúy Loan Trang 293 2/12/2011 Nguyễn Thị Thúy Loan Trang 294 2/12/2011 Giao tác Giao tác . Một giao tác là một đơn vị xử lý nguyên tố Các tính chất gồm nhiều hành động. Khi thực hiện một giao . Tính nguyên tố (Atomicity) tác hoặc phải thực hiện tất cả các hành động . Tính nhất quán (Consistency) của nó hoặc không thực hiện hành động nào hết. . Tính độc lập (Isolation) . Giao tác trong các loại CSDL quan hệ lớn . Tính bền vững (Durability) được sử dụng trong những trường hợp mà các hành động cập nhật dữ liệu trên nhiều bảng khác nhau được thực hiện trong cùng một đơn vị (unit). Nguyễn Thị Thúy Loan Trang 295 2/12/2011 Nguyễn Thị Thúy Loan Trang 296 2/12/2011
  75. 75 Giao tác không tường minh Giao tác không tường minh . Mặc định các lệnh bên trong lô (batch) chứa . Ví dụ: chúng ta cho thực hiện cùng lúc 3 lệnh các câu lệnh không tường minh, điều này có để cập nhật dữ liệu vào 3 bảng khác nhau trong nghĩa là nếu có ít nhất 1 câu lệnh thực hiện cùng một lô. không thành công trong lô thì tất cả các lệnh Thêm mặt hàng mới còn lại sẽ không được ghi nhận lại. INSERT INTO mh(MaMH, TenMH, DVT) VALUES (‘Bot’, ‘bột ngọt’, ‘hộp’) Nguyễn Thị Thúy Loan Trang 297 2/12/2011 Nguyễn Thị Thúy Loan Trang 298 2/12/2011 Giao tác không tường minh Giao tác tường minh Sửa đổi tên mặt hàng có mamh ‘gao’ . Giao tác tường minh trong những trường hợp UPDATE MH cập nhật dữ liệu trên nhiều bảng khác nhau và SET Tenmh = ‘Gạo thơm thái’ Where Mamh = ‘gao’ phải đảm bảo các hành động này nằm trong Xoá đơn đặt hàng ‘001’ cùng một đơn vị xử lý. DELETE DDH Where MaDH = ‘001’ Vi phạm toàn vẹn DL GO về khóa ngoại. Nên các lệnh trước đó không thực hiện Nguyễn Thị Thúy Loan Trang 299 2/12/2011 Nguyễn Thị Thúy Loan Trang 300 2/12/2011
  76. 76 Giao tác tường minh Giao tác tường minh . Bắt đầu một giao tác ví dụ 1: BEGIN TRAN[SACTION] [Tên_giao_tác] begin tran cap1 . kết thúc một giao tác INSERT INTO mh(MaMH, TenMH, DVT) ROLLBACK TRAN[SACTION] VALUES('Nui', 'Nui gạo', 'gói' ) kết thúc giao tác nhưng [Tên_giao_tác] không ghi nhận lại các commit tran cap1 hành động cập nhật dữ . Hoặc liệu bên ngoài giao tác. COMMIT TRAN[SACTION] [Tên_giao_tác] kết thúc giao tác, đồng ý ghi nhận lại các hành động cập nhật dữ liệu bên trong giao tác. Nguyễn Thị Thúy Loan Trang 301 2/12/2011 Nguyễn Thị Thúy Loan Trang 302 2/12/2011 Giao tác tường minh Phân vùng trong giao tác ví dụ 2: begin tran cap1 . Ta chia nhỏ các hành động bên trong giao tác insert into test values(1,'aaa') thành nhiều phần, tương ứng từng phần nhỏ ta begin tran cap2 có thể dễ dàng chủ động đồng ý ghi nhận hoặc insert into test values(2,'bbb') không ghi nhận lại việc cập nhật dữ liệu. begin tran cap3 insert into test values(3,'ccc') . Cú pháp commit tran cap3 SAVE TRAN[SACTION] [Tên vùng] go commit tran cap2 Các lệnh Go rollback tran cap1 Nguyễn Thị Thúy Loan Trang 304 2/12/2011
  77. 77 Phân vùng trong giao tác Bài tập Ví dụ: KHG (Makh, Tenkh, Diachi, DT, Email) Begin tran Vùng 1 VatTu (Mavt, Tenvt, Dvt, GiaMua, Slton) SAVE TRAN Vung_1_2 insert into test values(1,'aaa') HD (Mahd, ngay, Makh, TgTG) insert into test values(2,'bbb') CTHD (Mahd, Mavt, SL, KhMai, GiaBan) Vùng 2 SAVE TRAN Vung_3 insert into test values(3,'ccc') RollBack Tran Vung_3 Commit Tran Vung_1_2 Nguyễn Thị Thúy Loan Trang 305 2/12/2011 Nguyễn Thị Thúy Loan Trang 306 2/12/2011 create proc C_dsoban @namthang char(6), Bài tập @cur_dmvt cursor varying output . Tạo cursor lấy ra danh sách tên của các khách as select c.mavt, sum(sl) as slban into #tongslban hàng đã mua hàng trong tháng, năm chỉ định. from cthd c, vattu v, hd h . Tạo cursor trả về danh sách các mã vật tư đã Where convert(char(6), ngay, 112) = @namthang bán ra nhiều nhất trong năm tháng nào đó. and c.mavt=v.mavt and c.mahd=h.mahd and sum(sl) >= all (select sum(sl) from cthd) group by c.mavt Nguyễn Thị Thúy Loan Trang 307 2/12/2011
  78. 78 Bài tập Bài tập . Lấy ra danh sách khách hàng đã mua hàng create proc P_ds5kh_TGTMax trong ngày, với ngày là tham số truyền vào. as . Lấy ra danh sách 5 khách hàng có tổng trị giá select top 5 k.makh,k.tenkh,k.diachi, các đơn hàng lớn nhất. SUM(SL*Giaban) as TONGTG . Lấy ra danh sách 3 mặt hàng có số lượng bán from cthd ct, kh k, hd h nhiều nhất. where k.makh=h.makh and h.mahd=ct.mahd . Lấy ra danh sách 3 mặt hàng bán ra có lãi ít group by k.makh, tenkh, diachi nhất. order by sum(sl*Giaban) Desc Nguyễn Thị Thúy Loan Trang 309 2/12/2011 Nguyễn Thị Thúy Loan Trang 310 2/12/2011 Bài tập Bài tập . Cho biết tổng giá trị của một hóa đơn bán . Tính giá trị cho cột khuyến mãi như sau: hàng, với MAHD là tham số truyền vào. Nếu chưa thanh toán thì thông báo (nếu đã thanh khuyến mãi 5% nếu SL > 100, 10% nếu toán thì có xuất hiện trong CTHD). SL>500. . Cho biết vật tư có số lượng bán ra cao nhất trong tháng 5/2008, nếu chưa có thì xuất thông . Kiểm tra xem có tồn tại hóa đơn nào thanh báo. toán cho vật tư đó không, nếu có tính ra số . Cho biết vật tư có số lượng bán ra cao nhất trong tháng (tham số truyền vào) của năm lượng của vật tư được mua trong một hóa đơn. 2008, nếu chưa có thì xuất thông báo. Với Mavt và Sohd là tham số đầu vào. Nguyễn Thị Thúy Loan Trang 311 2/12/2011 Nguyễn Thị Thúy Loan Trang 312 2/12/2011
  79. 79 Bài tập Chương V . Kiểm tra xem có tồn tại hóa đơn nào thanh toán cho vật tư đó không, nếu có tính ra số TRIGGER & FUNCTION lượng của vật tư được mua trong một hóa đơn. Với Mavt và Sohd là tham số đầu vào, và SL là tham số đầu ra. ThS. Nguyễn Thị Thúy Loan Nguyễn Thị Thúy Loan Trang 313 2/12/2011 Khái niệm Triger dùng cho các công việc . Trigger là một dạng đặc biệt của thủ tục nội tại. . Kiểm tra ràng buộc toàn vẹn dữ liệu phức tạp. Tuy nhiên khác với thủ tục nội tại: . Thực hiện các xử lý thiết kế thi hành tại server o Không có tham số. (trong mô hình client/server). Các xử lý sẽ tự o Không thể gọi trực tiếp bằng lệnh động thực hiện khi có thao tác INSERT, EXECUTE như thủ tục nội tại mà thực hiện UPDATE hoặc DELETE xảy ra. một cách tự động khi dữ liệu của bảng có . Trigger dùng thay thế các constraint trong liên quan đến trigger bị cập nhật. trường hợp ta muốn việc kiểm tra ràng buộc dữ liệu kèm theo các câu thông báo thích hợp theo ý muốn người dùng. Nguyễn Thị Thúy Loan Trang 315 2/12/2011 Nguyễn Thị Thúy Loan Trang 316 2/12/2011
  80. 80 RBTV dữ liệu với trigger RBTV dữ liệu với trigger . Để đảm bảo dữ liệu nhất quán và đúng đắn, ta . Đối tượng constraint kiểm tra cần kiểm tra thực hiện 3 thao tác: Insert, Update và Delete. o Kiểm tra miền giá trị . Có 2 cách kiểm tra o Kiểm tra các ràng buộc giữa các thuộc tính trên cùng một bảng dữ liệu o Kiểm tra mức giao diện: là công việc lập trình trên các màn hình giao diện . Đối tượng trigger o Kiểm tra mức CSDL: thực hiện bởi các đối o Kiểm tra tính toàn vẹn dữ liệu trên nhiều tượng constraint hoặc trigger cột hoặc nhiều dòng của các bảng khác nhau Nguyễn Thị Thúy Loan Trang 317 2/12/2011 Nguyễn Thị Thúy Loan Trang 318 2/12/2011 Các dạng ràng buộc toàn vẹn BÀI TOÁN . RBTV bằng phương pháp mô tả . KHG(MaKH, TenKhach) o Xác định khoá chính, khoá ngoại, miền giá trị, và mô tả chúng tại thời điểm tạo Table . PX(MAPX, Ngay_PX, MaKH) o Thực hiện trước khi cho phép thêm vào Table . CTPX(MAPX, MaHH, SoLuong, DonGia) . RBTV theo phương pháp thủ tục . HH(MAHH,Ten_HH, DGHienHanh) o Xác định bởi tập các câu lệnh T-SQL. Các lệnh chứa bên trong đối tượng trigger. KHG: một khách hàng có 1 mã duy nhất để o Được gọi thi hành khi có thao tác thêm, xoá phân biệt khách hàng này với khách hàng khác, hoặc sửa dữ liệu trên table tương ứng. có 1 tên khách hàng duy nhất. o Thực hiện sau khi dữ liệu được ghi vào Table Nguyễn Thị Thúy Loan Trang 319 2/12/2011 Nguyễn Thị Thúy Loan Trang 320 2/12/2011
  81. 81 BÀI TOÁN BÀI TOÁN . HH: một hàng hoá có 1 mã hàng duy nhất dùng . CTPX: một chi tiết phiếu xuất có mã phiếu để phân biệt với hàng hoá khác, có 1 tên hàng hoá và một đơn vị bán hiện tại duy nhất. xuất và mã hàng hoá dùng để xác định khoá . PX: một phiếu xuất có 1 mã duy nhất dùng để của quan hệ. Khoá này dùng để phân biệt với phân biệt với phiếu khác, có 1 ngày xuất xác định. chi tiết xuất khác, có 1 số lượng xác định, 1 o Một phiếu xuất liên quan đến 1 khách hàng đơn giá tương ứng với đơn giá hiện hành của duy nhất hàng hoá lấy từ thuộc tính DGHienHanh của o Một phiếu xuất có ít nhất 1 chi tiết xuất bảng HH. Nguyễn Thị Thúy Loan Trang 321 2/12/2011 Nguyễn Thị Thúy Loan Trang 322 2/12/2011 Cơ chế hoạt động của trigger Tạo mới trigger . 3 biến cố kích hoạt 1 trigger . Cú pháp: o INSERT CREATE TRIGGER Tên_trigger o UPDATE ON tên_table| tên_view o DELETE AFTER | INSTEAD OF| FOR . Trigger lưu trữ dữ liệu của mẩu tin vừa thêm biến_cố_kích_hoạt_trigger vào một table mới có tên là INSERTED. AS After là mặc định, . Trigger lưu trữ dữ liệu của mẩu tin vừa xóa từ chỉ định nghĩa duy Các câu lệnh T-SQL nhất được trên view một table có tên là DELETED . Trigger lưu trữ dữ liệu của mẩu tin vừa cập nhật là sự phối hợp của 2 table DELELTED và INSERTED Nguyễn Thị Thúy Loan Trang 324 2/12/2011
  82. 82 Ví dụ Ví dụ CREATE TRIGGER Them_HH CREATE TRIGGER SUA_HH ON HH ON HH AFTER UPDATE AFTER INSERT AS AS Select * From Inserted Select * From Inserted Select * From Deleted . Chèn dữ liệu . Cập nhật dữ liệu INSERT HH(MaHH, TenHH) UPDATE HH VALUES(‘TV01’, ‘Tivi Sony’) SET Ten_HH = ‘Man Hinh Sony’ WHERE MaHH = ‘TV01’ Nguyễn Thị Thúy Loan Trang 325 2/12/2011 Nguyễn Thị Thúy Loan Trang 326 2/12/2011 Ví dụ Ví dụ CREATE TRIGGER Xoa_HH . MH(MaMH, TenMH, DVT, MaNCC) ON HH AFTER DELETE . PX(SoPX, NgXuat, SoDH) AS . CTPX(Ma_MH, SoPX, SLX, DGX) Select * From Inserted . DDH(MaDH, NgDat) Select * From Deleted . Xóa dữ liệu . CTDH(MaDH, MaMH, SLD, DGDat) DELETE HH WHERE MaHH = ‘TV01’ Nguyễn Thị Thúy Loan Trang 327 2/12/2011 Nguyễn Thị Thúy Loan Trang 328 2/12/2011
  83. 83 Các thao tác trigger phổ biến Thêm mới mẩu tin . Thêm mới mẫu tin . Xây dựng trigger trong bảng PX để kiểm tra . Kiểm tra ràng buộc dữ liệu các ràng buộc toàn vẹn dữ liệu khi người dùng thêm mới thông tin của một phiếu xuất hàng o Khoá ngoại cho một bảng hoá đơn đặt hàng trước đó. Các o Miền giá trị ràng buộc toàn vẹn dữ liệu bao gồm Liên bộ trên một quan hệ o o Khoá ngoại: cần kiểm tra số đặt hàng phải o Liên thuộc tính trong cùng một bảng tồn tại trong bảng đơn đặt hàng. o Liên thuộc tính của nhiều bảng khác nhau o Miền giá trị: cần kiểm tra ngày giao hàng phải ở sau ngày đặt hàng. Nguyễn Thị Thúy Loan Trang 329 2/12/2011 Nguyễn Thị Thúy Loan Trang 330 2/12/2011 Thêm mới mẩu tin Thêm mới mẩu tin create trigger tg_px_insert Tính ra ngày đặt hàng on px Select @Ngdat = Ngdat for insert From ddh , Inserted I As declare @Ngdat datetime, @ErrMsg varchar(200) Where ddh.madh = I.sodh Kiểm tra số hoá đơn đã có trong bảng DDH không? Kiểm tra ngày giao hàng phải sau ngày đặt hàng IF NOT EXISTS(Select * IF @Ngdat > (Select ngxuat From Inserted) From Inserted I, ddh 16:Tình trạng lỗi Begin Where I.sodh= ddh.madh) 1: Mức độ lỗi Set @ErrMsg = N'ngày giao hàng phải ở sau ngày:' Begin + Convert(char(10), @ngdat, 103 ) Rollback Tran Raiserror(@ErrMsg,16,1) Raiserror('Phieu dat hang nay khg ton tai', 16,1) Rollback tran Return End End Nguyễn Thị Thúy Loan Trang 331 2/12/2011 Nguyễn Thị Thúy Loan Trang 332 2/12/2011
  84. 84 Hủy bỏ mẩu tin Hủy bỏ mẩu tin . Ví dụ: khi xoá một số hoá đơn đặt hàng trong create trigger TG_DDH_delete on ddh bảng CTDH cần phải kiểm tra các RBTV dữ for delete liệu sau: As declare @SoPX char(10), @ErrMsg char(200), @Delete_Err int o Kiểm tra xem đơn đặt hàng bị xoá đã được Kiểm tra xem đơn hàng đã được xuất chưa xuất hàng chưa? Nếu đã được xuất rồi thì IF EXISTS (Select soPX From px, deleted d thông báo không thể xoá đơn đặt hàng được. where px.sodh = d.madh) Begin o Ngược lại thì xoá dữ liệu liên quan bên bảng Set @ErrMsg = 'Đơn đặt hàng đã được nhập theo '+ chi tiết đơn đặt hàng (CTDH) 'số xuất hàng '+ @SoPX + '.Không thể huỷ được' RaisError(@ErrMsg,16,1) Rollback tran End Nguyễn Thị Thúy Loan Trang 333 2/12/2011 Nguyễn Thị Thúy Loan Trang 334 2/12/2011 Hủy bỏ mẩu tin Sửa đổi mẩu tin Else Begin . Kiểm tra ràng buộc dữ liệu Xoá tự động chi tiết các đơn đặt hàng liên quan o Khoá ngoại Delete CTDH o Miền giá trị Where Madh In(Select Madh From DELETED) Set @Delete_Err = @@ERROR o Liên bộ trên một quan hệ IF @Delete_Err <> 0 o Liên thuộc tính trong cùng một bảng Begin o Liên thuộc tính của nhiều bảng khác nhau Set @ErrMsg = 'Lỗi vi phạm xóa trên bảng chi tiết đặt hàng' RaisError(@ErrMsg, 16, 1) Rollback Tran End End Nguyễn Thị Thúy Loan Trang 336 2/12/2011
  85. 85 Update Update . Hàm Update . Cú pháp . Ý nghĩa . UPDATE (tên_cột) (biểu thức luận lý) o Kiểm tra dữ liệu của cột bên trong bảng có o Tên_cột: tên cột mà chúng ta muốn kiểm tra bị thay đổi trong các trigger sửa đổi dữ liệu xem dữ liệu tại đócóbị sửa đổi trong trigger không. o Biểu thức luận lý: trả về True khi giá trị dữ liệu của cột đã bị sửa đổi, ngược lại trả về False khi giá trị dữ liệu của cột không bị sửa đổi Nguyễn Thị Thúy Loan Trang 337 2/12/2011 Nguyễn Thị Thúy Loan Trang 338 2/12/2011 Update Update Ví dụ: sửa đổi thông tin của một số đặt hàng bên create trigger tg_ddh_update trong bảng DDH cần phải kiểm tra các ràng on ddh buộc toàn vẹn dữ liệu sau: for update as declare @NgayXH datetime, @ErrMsg varchar(200) . Không cho phép sửa đổi dữ liệu tại cột MaDH Khi sửa đổi các cột MaDH vì khi đódữ liệu sẽảnh hưởng đến nhiều bảng. IF Update(MaDH) . Sửa đổi giá trị cột ngày đặt hàng thì phải đảm Begin bảo luôn luôn trước ngày xuất hàng đầu tiên Rollback Tran Set @ErrMsg = 'Không thể thay đổi số đặt hàng’ của số đặt hàng đó(nếu đơn đặt hàng đã có RaisError(@ErrMsg, 16, 1) giao hàng). Return End Nguyễn Thị Thúy Loan Trang 339 2/12/2011 Nguyễn Thị Thúy Loan Trang 340 2/12/2011
  86. 86 Update Update Khi sửa đổi ngày đặt hàng kiểm tra giá trị ngày đặt hàng sau khi sửa đổi IF Update(Ngdat) phải luôn trước ngày giao hàng đầu tiên Begin IF @NgayXH < (Select Ngdat From Inserted) Kiểm tra đơn đặt hàng đã được xuất chưa Begin IF EXISTS (Select SoPX From PX, deleted d Rollback tran where px.sodh=d.madh) Set @ErrMsg = 'Ngày đặt hàng trước Begin ngày:'+ Convert(char(10), @NgayXH, 103) Tính ra ngày nhập hàng đầu tiên RaisError(@ErrMsg, 16, 1) Select @NgayXH = Min(NgXuat) End FromPX, DELETED D end Where PX.sodh = D.MaDH End Nguyễn Thị Thúy Loan Trang 341 2/12/2011 Nguyễn Thị Thúy Loan Trang 342 2/12/2011 Trigger cập nhật giá trị tự động Trigger cập nhật giá trị tự động Ví dụ: Dữ liệu của bảng TONKHO sẽ được tính o Kiểm tra mã vật tư phải có trong danh sách tự động từ dữ liệu của các bảng liên quan đến chi tiết, danh sách các mã vật tư phải có việc nhập hàng và việc xuất hàng, cụ thể sẽ là trong chi tiết đơn đặt hàng trước đó. các bảng: PNHAP, CTPNHAP, PXUAT, và o Kiểm tra tổng số lượng nhập hàng vẫn còn ít CTPXUAT hơn số lượng đặt hàng của vật của vật tư đó. . Khi thêm mới các thông tin của chi tiết một . Nếu tất cả các RBTV dữ liệu ở trên đều hợp lệ phiếu nhập hàng vào bảng CTNHAP, chúng ta thì tăng giá trị của cột tổng số lượng nhập cần kiểm tra các RBTV dữ liệu trong bảng TONKHO và cột tổng trị giá trong o Kiểm tra số phiếu nhập phải có trong bảng bảng PNHAP. PNHAP. Nguyễn Thị Thúy Loan Trang 343 2/12/2011 Nguyễn Thị Thúy Loan Trang 344 2/12/2011
  87. 87 Ví dụ Ví dụ create trigger T_Xoa_VT on vattu . Tạo trigger không cho phép xoá một vật tư mà for delete as đã có ít nhất một chi tiết hoá đơn của vật tư đó. declare @mavt nvarchar(5) select @mavt = mavt from deleted if(select count(*) from CTHD where mavt=@mavt)>=1 begin print 'Khong the xoa vat tu nay' rollback transaction return end Nguyễn Thị Thúy Loan Trang 345 2/12/2011 select * from vattu Ví dụ Ví dụ . Tạo trigger không cho phép xoá cùng lúc nhiều create trigger T_Xoa_KH on KH hơn một khách hàng. for delete as if((select count(*) from deleted)>1) begin print 'xoa mot nguoi cho mot lan xoa' rollback tran end select * from KH Nguyễn Thị Thúy Loan Trang 347 2/12/2011 Nguyễn Thị Thúy Loan Trang 348 2/12/2011
  88. 88 Ví dụ Ví dụ create trigger lopsv on lop . Tạo trigger kiểm tra một lớp phải có tối thiểu for delete, insert là 1 sinh viên. as if (select count(masv) from Inserted i, sv where i.malop = sv.malop) =0 begin rollback tran print 'toi thieu 1 sv' end Nguyễn Thị Thúy Loan Trang 349 2/12/2011 Nguyễn Thị Thúy Loan Trang 350 2/12/2011 Hàm do người dùng ĐN Hàm do người dùng ĐN (user defined functions) . Hàm xác định luôn trả về cùng giá trị nếu giá . Khái quát: UDFs là một chương trình con trị các tham số được truyền vào là như nhau. đảm trách một xử lý nào đóvới đặc tính là sẽ . Hàm không xác định có thể cho ra kết quả nhận các tham số đầu vào và trả về một giá trị khác biệt tại mỗi thời điểm chúng được gọi kết quả xử lý tính toán được dựa trên các tham . UDFs là sự kết hợp của 2 đối tượng View và số đầu vào đã nhận. Store Procedure . UDFs phân thành 2 nhóm: o Hàm xác định (deterministic) o Hàm không xác định (non-deterministic). Nguyễn Thị Thúy Loan Trang 351 2/12/2011 Nguyễn Thị Thúy Loan Trang 352 2/12/2011
  89. 89 Hàm do người dùng ĐN Hàm do người dùng ĐN . Khắc phục một số hạn chế của View và Store . Tạo UDF Procedure create function [Tên_Function](tham số) o SP không thể là một phần của câu lệnh RETURNS kiểu_dữ_liệu_trả_về SELECT nhưng UDFs thì có. AS o View không hỗ trợ đệ quy trong khi UDFs BEGIN thì có thể làm được điều này. Các lệnh Return END Nguyễn Thị Thúy Loan Trang 353 2/12/2011 Nguyễn Thị Thúy Loan Trang 354 2/12/2011 Hàm do người dùng ĐN Hàm do người dùng ĐN . Returns: thiết lập kiểu dữ liệu trả về của UDFs. . Inline table: khắc phục được nhược điểm Có 2 cách thiết lập chính không có tham số của VIEW. Có nghĩa rằng . Trả về giá trị kiểu vô hướng: một chuỗi, một UDFs loại inline table giống như một VIEW giá trị logic hoặc một kiểu số. có tham số. . Trả về một bảng: có thể trả về hai loại bảng . Multistatement table: UDFs loại này giống với Store Procedure. Loại này luôn trả về 1 biến table. Thực hiện các câu SELECT phức tạp, cho phép thực hiện các câu lệnh logic khác như UPDATE, INSERT INTO Nguyễn Thị Thúy Loan Trang 355 2/12/2011 Nguyễn Thị Thúy Loan Trang 356 2/12/2011
  90. 90 Ví dụ Ví dụ . Tính tổng hai số a, b. . Kiểm tra số chẵn hay lẻ create function F_Tong(@a int, @b int) create function Chan_Le(@n int) RETURNS int returns int as as BEGIN begin declare @kq int if (@n %2=0) set @kq=@a+@b return 1 return @kq return 0 END end Select dbo.F_tong(5,7) select dbo.Chan_le(6) Nguyễn Thị Thúy Loan Trang 357 2/12/2011 Nguyễn Thị Thúy Loan Trang 358 2/12/2011 Ví dụ Ví dụ Tính tổng hai số a, b. Nếu a lớn hơn 2 thì tổng create function Test_function(@a int, @b int) RETURNS int as được tăng lên 5, ngược lại tổng là a cộng b. BEGIN declare @kq int if @a>2 set @kq=@a+@b + 5 else set @kq=@a+@b return @kq END Select dbo.Test_function(5,7) Nguyễn Thị Thúy Loan Trang 359 2/12/2011 Nguyễn Thị Thúy Loan Trang 360 2/12/2011
  91. 91 Ví dụ Ví dụ create function Ngto(@n int) returns int as Viết hàm kiểm tra n có phải là nguyên tố hay begin if @n 0 returns bigint as print ' la ng to ' begin if (@x>20 or @x <0 ) else print ' khong la ngto' return 0 if @x < 2 return 1 return @x * dbo.F_gt(@x-1) End . select dbo.F_Gt(8) Nguyễn Thị Thúy Loan Trang 363 2/12/2011 Nguyễn Thị Thúy Loan Trang 364 2/12/2011
  92. 92 Ví dụ Ví dụ . Viết hàm tính điểm trung bình cho sinh viên. create function F_avg(@masv char(10)) . Tính doanh thu theo năm với là tham số truyền returns float as vào là năm. begin declare @tb float select @tb = avg(diem) from kq, sv where kq.masv=sv.masv and kq.masv=@masv return @tb end . select dbo.F_avg(001) Nguyễn Thị Thúy Loan Trang 365 2/12/2011 Nguyễn Thị Thúy Loan Trang 366 2/12/2011 Ví dụ Ví dụ Create function F_dt (@nam int) returns int As Nhập vào mã sinh viên cho biết những môn học Begin mà sinh viên thi rớt. declare @dt int select @dt=sum(slx*dgx) from ctpx, px where (ctpx.sopx=px.sopx) and year(ngxuat)=@nam return @dt End . Select dbo.F_dt(2009) Nguyễn Thị Thúy Loan Trang 367 2/12/2011 Nguyễn Thị Thúy Loan Trang 368 2/12/2011
  93. 93 Ví dụ Ví dụ create function F_thirot(@masv nchar(10)) . Nhập vào mã môn học, cho biết số lượng sinh returns table viên tham gia môn học đó. as Return (select mh.mamh, tenmh from kq, mh where kq.mamh = mh.mamh and @masv = kq.masv and diem < 5) select * from dbo.F_thirot ('003') Nguyễn Thị Thúy Loan Trang 369 2/12/2011 Nguyễn Thị Thúy Loan Trang 370 2/12/2011 Ví dụ Chương VI create function mhoc(@mamh nchar(10)) returns int as begin BACKUP AND declare @slsv int RESTORE select @slsv = count(Masv) from mh, kq where mh.mamh = kq.mamh and ThS. Nguyễn Thị Thúy Loan @mamh = mh.mamh return @slsv end select dbo.mhoc ('csdl') Nguyễn Thị Thúy Loan Trang 371 2/12/2011
  94. 94 Tại sao phải backup dữ liệu Tại sao phải backup dữ liệu . Ðĩa chứa data file hay Transaction Log File . Các thiết bị dùng để backup - restore bị đánh hay system file bị mất. cắp hay hư hỏng . Server bị hư hỏng . Những lỗi do vô ý của user như lỡ tay delete . Những thảm họa tự nhiên như bão lụt, động toàn bộ table chẳng hạn đất, hỏa hoạn . Những hành vi mang tính phá hoại của nhân . Toàn bộ server bị đánh cắp hoặc phá hủy viên như cố ý đưa vào những thông tin sai lạc. . Bị hack (nếu server có kết nối với internet). Nguyễn Thị Thúy Loan Trang 373 2/12/2011 Nguyễn Thị Thúy Loan Trang 374 2/12/2011 Các đối tượng backup DL Tạo backup trong EM . Tạo bảng sao của các đối tượng CSDL như . Right click trên đối tượng backup/ chọn New table, view, stored procedure, function, trigger, Backup Device. user, role, rule, default, các ràng buộc, nhật ký giao tác . Thiết bị backup: o File trên đĩa cục bộ o File trên mạng o Tape Nguyễn Thị Thúy Loan Trang 375 2/12/2011 Nguyễn Thị Thúy Loan Trang 376 2/12/2011
  95. 95 Tạo backup trong EM Tạo backup bằng T-SQL . Cú pháp: Sp_addumpdevice [@devtype] ‘device_type’ [@logicalname=] ‘logical_name’ [@physicalname=] ‘physical_name’ device_type: có thể là (disk, pipe, tape) Nhập tên CSDL backup . Ví dụ: tạo 1 backup device có tên mydisdump. Với tên vật lý là L:\QLSV.bak sp_addumpdevice 'disk','mydisdump', ‘L:\QLSV.bak‘ backup database QL_SV to mydisdump Nguyễn Thị Thúy Loan Trang 378 2/12/2011 Tạo backup bằng T-SQL Phục hồi CSDL (Restore) • @devtype = 'device_type' là tên của device . Khi CSDL bị nguy hiểm, qúa trình phục hồi như disk, tape nên theo các bước sau: • Logical_name: là tên được dùng trong các lệnh . Backup nhật ký các giao tác backup hoặc restore Backup log qlsv to qlsvLog with no_truncate • PhisicalName: Tên vật lý lưu trong đĩa . Phục hồi CSDL từ bảng backup gần nhất . Phục hồi nhật ký các giao tác . Phục hồi CSDL trong tình trạng không hoạt động. Nguyễn Thị Thúy Loan Trang 379 2/12/2011 Nguyễn Thị Thúy Loan Trang 380 2/12/2011
  96. 96 Restore trong EM Restore trong EM Chọn Database cần restore Nguyễn Thị Thúy Loan Trang 381 2/12/2011 Restore trong EM Restore trong EM Chọn vào đây Chọn từ thiết bị nào Nguyễn Thị Thúy Loan Trang 383 2/12/2011 Nguyễn Thị Thúy Loan Trang 384 2/12/2011
  97. 97 Restore trong EM Restore trong EM Chọn tập tin cần restore Check vào đây Nguyễn Thị Thúy Loan Trang 385 2/12/2011 Nguyễn Thị Thúy Loan Trang 386 2/12/2011 Restore bằng T-SQL . Cú pháp: restore database from disk = ‘ ’ . Ví dụ: Restore file L:\QLSV.bak restore database Loan from disk = 'L:\QLSV.bak' Nguyễn Thị Thúy Loan Trang 387 2/12/2011