Bài giảng Cơ sở dữ liệu - Chương 1: Tổng quan sql server

Giảng viên: Ths. Phạm Thị Lan AnhBộ môn: HTTTChương 1: TỔNG QUAN SQL SERVER*Nội dung bài họcSQL Server là gìSQL Server Integration SQL Server DatabaseSQL Server Security*SQL Server là gìGiới thiệu SQL ServerClient-Server ComponentClient-Server CommunicationSQL Server Services*Giới thiệu SQL Server (1)Online Transaction processing (OLTP) database và Online Analytical processing (OLAP) database*Giới thiệu SQL Server (2)Thực hiện các transaction processing, lưu trữ, phân tích và xây dựng các ứng dụng Là một hệ quản trị cơ sở dữ liệu quan hệQuản lý kho dữ liệu đối với các transaction và analysisTrả lời các request của client applicationDùng Transact-SQL, XML, Multidimendional expression (MDX) hoặc SQL distribute management Object (SQL – DMO) để gởi các request giữa client với SQL Server*Relational Database Management System RDBMS của SQL Server có nhiệm vụ:Duy trì các quan hệ giữa các dữ liệu trong database.Bảo đảm dữ liệu được lưu trữ đúng và hợp lệ theo các qui tắc đã đưa ra.Phục hồi tất cả các dữ liệu khi cầnGiới thiệu SQL Server (3)*OLTP database: Dữ liệu được lưu trong các table có quan hệ -> giảm dư thừa dữ liệu và tăng tốc độ cập nhật. Cho phép một số lượng lớn user thực hiện các transaction một cách đồng thờiOLAP Databases: Hổ trợ phân tích viên đưa ra các giải pháp, các mô hình dữ liệuGiới thiệu SQL Server (4)**Client Application: là những chương trình mà Users dùng để truy xuất dữ liệu trong DBMS. Chúng sử dụng: Transact-SQL là SQL và ngôn ngữ lập trình mà SQL Server sử dụngXMLMDXOLE DB(Object Linking and Embedding, Database) và ODBC APIsADOEnglish QueryGiới thiệu SQL Server (5)*Client-Server components (1)*Client-Server ArchitectureClient đóng vai trò business logic và presenting data cho user. Có thể chạy trên một hoặc nhiều máy hoặc chạy trên cùng máy với serverSQL Server quản lý database và cấp phát tài nguyên Client-Server components (2)*Client-Server Communication*SQL Server Services (1)*SQL Server Services (2)MSSQL Server Services Cấp phát tài nguyên máy tính cho nhiều user đồng thời.Ngăn chặn các logic problems ví dụ như timing requestsBảo đảm tính ổn định và ràng buộc dữ liệu.*SQL Server Services (3)SQLServerAgent Services Cảnh báo về trạng thái của một process ví dụ như một công việc này đó được hoàn thành hoặc xãy ra lỗi.Tạo ra một công việc mới và lập thời khoá để tự động hoá các nhiệm vụ.Gởi email hoặc khởi động một ứng dụng khác khi một thông báo xãy ra.*SQL Server Services (4)Microsoft Distributed Transaction Coordinator (MS DTC)Cho phép các clients gom nhiều nguồn dữ liệu khác nhau trong một transaction.Microsoft Searchlà một full-text engine hoạt động như một dịch vụ trong Windows 2000.*SQL Server IntegrationSQL Server tích hợp với các OSSQL Server tích hợp với các M_Server khác*SQL Server tích hợp với các OS*SQL Server tích hợp với*SQL Server DatabaseCác loại DatabasesCác đối tượng Database Tham chiếu đến đối tượng SQL Server Các table hệ thống (System Tables)! Metadata Retrieval*Các loại Database (1)*Các loại Databases (2)master: điều khiển các user databases và các hoạt động của SQL Server cũng như toàn bộ các thông tin như user accounts, biến môi trường và các system error messagesmodel: cung cấp một template/prototype database cho việc tạo mới một user databasestempdb: cung cấp vùng lưu trữ cho table tạm thời hoặc các vùng làm việc tạm thờimsdb: cung cấp vùng lưu trữ cho việc xắp thời khoá biểu các công việcdistribution: Lưu trữ history và transaction data được dùng để tạo bản saopubs: một database ví dụ để họcNorthwind một database ví dụ để họcUser1: một database do user tạo ra*Database Objects (1)*Database Objects (2)ObjectMô tảTableBảng quan hệ chứa dữ liệuData typeKiểu dữ liệuConstraintCác qui tắc để kiểm tra ràng buộc dữ liệuDefaultCác giá trị mặc định nếu giá trị của một field không được nhập vàoRuleCác thông tin mà định nghĩa các giá trị hợp lệ trong một field IndexLà một cấu trúc lưu trữ nhằm truy xuất nhanh dữ liệu*Database Objects (3)ObjectMô tảViewLà table ảo, đây là một cách để lấy dữ liệu từ các table hoặc các view khácUser-defined functionLà các function do programmer định nghĩaStored procedureLà các thủ tục do programmer định nghĩaTriggerLà một dạng đắc biệt của procedure nhưng được thực hiện tự động khi user thay đổi dữ liệu trong table/view*Tham chiếu đến một đối tượngTên đầy đủ: server.database.owner.objectTên ngắn gọn Được hiểu là server default, instance hiện hành, database hiện hành và owner là user name đang login*System Tables (1)System tables lưu trữ các thông tin về các System và Database ObjectsDatabase Catalog lưu trữ Metadata về một DatabaseSystem Catalog lưu trữ Metadata về toàn bộ hệ thống và tất cả các Databases*System tables (2)*Lấy dữ liệu MetaData(1)*Lấy dữ liệu MetaData(2)System Stored Procedure*SQL Server Security*Login Authentication*Database User Accounts and Roles*Việc kiểm tra quyền *Q&A*Môn học: Hệ quản trị cơ sở dữ liệuSố tiết: 30 tiết LT (10 buổi)GV: Phạm Thị Lan Anh Email: lananh020679@gmail.com Lịch trực: chiều thứ 2, tại VPK*Bài 3: Thao tác trên tableNội dungTạo table bằng công cụ Tạo table bằng lệnh T-SQLTạo mới dữ liệu bằng công cụTạo mới dữ liệu bằng lệnh T-SQLSửa cấu trúc tableXoá tableTóm tắt bài họcBài tậpTạo table bằng công cụSqlServer Management Studio ExpressCREATE TABLE     [ database_name.[ server_name ] . | owner. ] table_name     ( {         | column_name AS computed_column_expression         | ::= [ CONSTRAINT constraint_name ]           | [ { PRIMARY KEY | UNIQUE } [ ,...n ] }     ) Định nghĩa cộtCột tính toánTạo bảng bằng lệnh T_SQL (1)Định nghĩa các cột ::= { column_name data_type }         [ [ DEFAULT constant_expression ]         | [ IDENTITY [ ( seed , increment )     ]     [ ] [ ...n ] Tạo bảng bằng lệnh T_SQL (2)ArgumentsTable name: tên tableColumn name: tên fieldcomputed_column_expression : biểu thức tính toán từ các field trong cùng table cho field tính toánDefault: giá trị mặc định cho cộtIdentity: Giá trị của cột sẽ được tự động tăng theo seed và inreament, chỉ dùng cho field kiểu sốạo bảng bằng lệnh T_SQL (3)ConstraintCho phép định nghĩa các rule để bảo toàn các ràng buộc dữ liệu trong database.Có 2 loại constraints: Column constraint: Được định nghĩa như một thuộc tính của field và chỉ áp dụng và tham chiếu trên field đóTable constraint: được khai báo độc lập với các field trong table và có thể tham chiếu đến nhiều cột trong table đó.Tạo bảng bằng lệnh T_SQL (4) ::= [ CONSTRAINT constraint_name ]     { [ NULL | NOT NULL ]         | [ { PRIMARY KEY | UNIQUE }             [ CLUSTERED | NONCLUSTERED ]             [ WITH FILLFACTOR = fillfactor ]             [ON {filegroup | DEFAULT} ] ]         ]         | [ [ FOREIGN KEY ]             REFERENCES ref_table [ ( ref_column ) ]             [ ON DELETE { CASCADE | NO ACTION } ]             [ ON UPDATE { CASCADE | NO ACTION } ]             [ NOT FOR REPLICATION ]         ]         | CHECK [ NOT FOR REPLICATION ]         ( logical_expression )     } Tạo bảng bằng lệnh T_SQL (5) ::= [ CONSTRAINT constraint_name ]     { [ { PRIMARY KEY | UNIQUE }         [ CLUSTERED | NONCLUSTERED ]         { ( column [ ASC | DESC ] [ ,...n ] ) }         [ WITH FILLFACTOR = fillfactor ]         [ ON { filegroup | DEFAULT } ]     ]     | FOREIGN KEY         [ ( column [ ,...n ] ) ]         REFERENCES ref_table [ ( ref_column [ ,...n ] ) ]         [ ON DELETE { CASCADE | NO ACTION } ]         [ ON UPDATE { CASCADE | NO ACTION } ]         [ NOT FOR REPLICATION ]     | CHECK [ NOT FOR REPLICATION ]         ( search_conditions )     } Tạo bảng bằng lệnh T_SQL (5)Tạo mới dữ liệu bằng công cụSqlServer Management Studio ExpressTạo mới dữ liệu bằng lệnh T-SQLINSERT INTO table_nameVALUES (value1, value2, value3,...)HoặcINSERT INTO table_name (column1, column2, column3,...)VALUES (value1, value2, value3,...)Sửa cấu trúc tableXoá tableDROP TABLE table_name Không thể xoá một table mà được tham chiếu (referenced) bởi một ràng buộc FOREIGN KEY constraint. Ràng buộc FOREIGN KEY hoặc table tham chiếu phải được xoá trước.Tóm tắt bài họcChúng ta có thể quản lý table bằng công cụ Enterprise Manager hoặc bằng các lệnh Transact-SQLĐể tạo table bằng T-SQL, ta dùng lệnh create tableĐể sữa cấu trúc table bằng T-SQL, ta dùng lệnh alter tableĐể xoá table bằng T-SQL, ta dùng lệnh drop tableBài tậpTable: KHACHHANG Name Type Size MAKH Nvarchar 5 (khoaù chính) TENKH Nvarchar 30 DIACHI Nvarchar 50 DThoai Nvarchar 8 EMAIL Nvarchar 30Raøng buoäc: TENKH not null, DThoai laø 8 chöõ soá.Tạo table MonHoc có 2 fields:MaMH kiểu chuỗi 10 ký tự, khoá chínhTenMH kiểu chuỗi 50 ký tự, not nullTable HocKy có 2 fields:MaHK, số tự động tăng từ 1, khoá chínhTenHK kiểu chuỗi 20 ký tựTable MonHoc_HocKyMaMH kiểu chuỗi 10 ký tự tham chiếu từ table MonHocMaHK, số tham chiếu từ table HocKyKhoá chính là 2 field MaMH và MaHKThêm cột số tiết kiểu số vào table môn họcCÁC LỆNH SQLNội dung bài họcLệnh tạo các record: InsertLệnh truy vấn dữ liệu: selectLệnh cập nhật dữ liệu: UpdateLệnh xoá dữ liệu: DeleteBài tậpLệnh tạo các record: Insert (1)Tạo một record với đầy đủ giá trị cho các fields:INSERT INTO table_name VALUES (value1, value2,....)Chỉ định các field cụ thể:INSERT INTO table_name (column1, column2,...) VALUES (value1, value2,....)Lệnh tạo các record: Insert (2)Chèn dữ liệu vào table từ lệnh select của từ một hoặc nhiều table hoặc viewINSERT INTO TableName [(column1, column2...)] select ....Ví dụ:INSERT INTO MyCustomers SELECT CustomerID, CompanyName FROM Customers WHERE country= ‘USA' Lệnh truy vấn dữ liệu: selectCú pháp Các mệnh đề Cú pháp lệnh select (1)SELECT statement ::=  [ ORDER BY { order_by_expression | column_position [ ASC | DESC ] } [ ,...n ]    ]  [ COMPUTE    { { AVG | COUNT | MAX | MIN | SUM } ( expression ) } [ ,...n ]  ] ::=     { | ( ) }     [ UNION [ ALL ] ) [...n ] ] Cú pháp lệnh select (2) ::=     SELECT [ ALL | DISTINCT ]         [TOP integer | TOP integer PERCENT } [ WITH TIES ] ]          [ INTO new_table ]     [ FROM { } [ ,...n ] ]     [ WHERE ]     [ GROUP BY [ ALL ] group_by_expression [ ,...n ]         [ WITH { CUBE | ROLLUP } ]]    [ HAVING ] Các mệnh đề trong câu lệnh SELECTSELECT select_list [ INTO new_table ] FROM table_source [ WHERE search_condition ] [ GROUP BY group_by_expression ] [ HAVING search_condition ] [ ORDER BY order_expression [ ASC | DESC ] ] Mệnh đề select (1)SELECT [ ALL | DISTINCT ]    [ TOP n [ PERCENT ] [ WITH TIES ] ]      ::=     {    * | fieldName | expression as column_alias [,..n]}Mệnh đề select (2)Tham số trong mệnh đề selectALL: chỉ định rằng tất cả các dòng trùng nhau có thể xuất hiện trong tập record kết quả. Default là ALL.DISTINCT: Khi có nhiều hơn hai dòng trùng nhau, chi hiển thị một dòngTOP n [PERCENT]: n dòng đầu tiên được xuất ra. n nằm trong khoảng 0 -> 4294967295. Nếu có PERCENT, thì n phải nằm trong khoảng từ 0 đến 100.WITH TIES: Chỉ định rằng kết quả các record trả về có thể nhiều hơn n dòng bằng cách thêm vào các dòng có cùng giá trị trong cột order by. (bắt buộc phải có order by)Mệnh đề FROM(1)[ FROM { } [ ,...n ] ] ::=     table_name [ [ AS ] table_alias ]     | view_name [ [ AS ] table_alias ]         | derived_table [ AS ] table_alias [ ( column_alias [ ,...n ] ) ]     | ::=      ON     | CROSS JOIN     | ::=     [ INNER | { { LEFT | RIGHT | FULL } [ OUTER ] } ]     [ ]     JOIN Mệnh đề FROM(2)Tham sốderived_table: là một subquery trả về các record trong databaseINNER: lấy ra tất cả các cập dòng so trùng (join type default)FULL [OUTER]: lấy luôn tất cả các record của bảng hoặc trái hoặc phải mà không tìm thấy trong điều kiện join và giá trị không tìm thấy sẽ được điều vào là null.LEFT [OUTER]: lấy luôn tất cả các record của bảng trái mà không tìm thấy trong điều kiện join và giá trị không tìm thấy sẽ được điều vào là null.RIGHT [OUTER]: lấy luôn tất cả các record của bảng phải mà không tìm thấy trong điều kiện join và giá trị không tìm thấy sẽ được điều vào là null.Mệnh đề where (1)[ WHERE | ] ::=     column_name { * = | = * } column_name ::=     {    [ NOT ] | ( ) }         [ { AND | OR } [ NOT ] { | ( ) } ]  }    [ ,...n ] ::=     {    expression { = | | ! = | > | > = | ! > | ' )         | FREETEXT ( { column | * } , 'freetext_string' )         | expression [ NOT ] IN ( subquery | expression [ ,...n ] )         | expression { = | | ! = | > | > = | ! > | ]          Lệnh xoá dữ liệu: DeleteDELETE  [FROM] { table_name [ ...n ])| view_name}     [ WHERE { }] Bài tập dụng Lấy ra thông tin MAHD, NGAYHD, MAKH, TENKH của hoá đơn tháng 6 năm 2005Đếm xem mỗi khách hàng có bao nhiêu hoá đơnĐếm xem trong tháng 6 năm 2005, mỗi mặt hàng bán được số lượng bao nhiêuVí dụ 1Trong database Pubs, tăng 10% giá bán cho sách có pud_ID là 0766: UPDATE titles SET price = price * 1.1 WHERE (pub_id = '0766') Trong database NorthWind, khuyến mãi 10% số tiền cho các order details có số lượng >=100: UPDATE [Order Details] SET discount= quatity*price * 0.1 WHERE (quatity>= 100) Ví dụ dùng database pubsXóa tất cả các authorsUSE pubs DELETE authors Xóa các authors có điều kiệnUSE pubs DELETE FROM authors WHERE au_lname = 'McBadden' Xóa các titleauthor có điều kiệnUSE pubs DELETE FROM titleauthor WHERE title_id IN (SELECT title_id FROM titles WHERE title LIKE '%computers%') Áp dụngXoá các hoá đơn của các khách hàng có địa chỉ là TAN BINH*View ManagerNội dung bài họcGiới thiệu ViewLợi ích của ViewĐịnh nghĩa viewGiới thiệu view (1)Giới thiệu view (2)Giới thiệu view (3)View là một table ảoDùng để lưu trữ một truy vấn được định trước được xem là một đối tượng trong database.Các table tham gia truy vấn được gọi là base table:Dữ liệu trong view được lấy từ các bảngLà sự kết hợp từ một hoặc nhiều bảngLà sự quan hệ giữa một hoặc nhiều bảngCó thể chứa các tính toánCó thể kết hợp giữa các bảng và các view khácLợi ích của viewTập trung vào dữ liệu đang cần Che những phần dữ liệu chưa cần dùng trong databaseĐơn giản hoá việc quản lý quyền userCải thiện việc thực thiTổ chức lại dữ liệu để xuất ra cho các ứng dụng bên ngoàiĐịnh nghĩa viewTạo viewThay đổi view, Xoá viewVí dụ về tạo viewTạo view(1)Cú pháp CREATE VIEW view_name [(column [,n ])][WITH {ENCRYPTION | SCHEMABINDING | VIEW_METADATA} [,n ]]ASselect_statement[WITH CHECK OPTION]Tạo View(2)Tham sốselect statement: là câu lệnh select query lấy dữ liệu từ 1 hoặc nhiều table hoặc view khácWITH CHECK OPTION: Bắt buộc các lệnh thay đổi dữ liệu dựa trên view phải thỏa điều kiện định nghĩa viewWITH ENCRYPTION: Mã hoá lệnh tạo view.SCHEMABINDING: ràng buộc view vào schema. Khi đó, ta không thể xoá các table hoặc các view là cơ sở của view khi view này còn tồn tạiView_METADATA: chỉ định rằng SQL Server sẽ trả về cho DBLIB, ODBC và OLE DB APIs những “metadata information” của view bao gồm các thông tin như tên các base table, các column, Tạo View(3)Một số hạn chế trong select statementKhông thể chứa mệnh đề COMPUTE hoặc COMPUTE BYKhông thể chứa mệnh đề ORDER BY trừ khi có TOP nKhông thể chứa mệnh đề INTOKhông thể chứa các table tạmCó tối đa 1024 cộtThay đổi/xóa viewSửa cấu trúc view:ALTER VIEW [(column [,n])][WITH [,n]AS select_statement[WITH CHECK OPTION]Xoá view:DROP VIEW Lưu ýTên view lưu trữ trong sysobjects table, tên cột của view được lưu trữ trong syscolumns, text dùng để tạo ra view lưu trong syscomments tableĐể tạo view, ta phải có quyền create view và quyền select table trong các table cơ sở. Bài tập minh họa (1)Loai(MaLoai, TenLoai)SanPham(MaSP, TenSP, MaLoai)NhanVien(MaNV, HoTen, NgaySinh, Phai)PhieuXuat(MaPX, NgayLap, MaNV)ChiTietXuat(MaPX, MaSP, SoLuong)Tạo View cho biết: “mã sản phẩm, tên sản phẩm, tổng số lượng xuất của từng sản phẩm trong năm 2005”. Lấy dữ liệu từ View này sắp xếp tăng dần theo tên sản phẩm.Bài tập minh họa (2) Lấy ra danh sách khách hàng đã mua hàng trong tháng 6 năm 2000Lấy ra danh sách khách hàng không mua hàng trong tháng 6 năm 2000Tạo view V1 để lấy ra danh sách các khách hàng có địa chỉ là Tân BìnhTạo view V2 để lấy ra danh sách các khách hàng có địa chỉ không ở Tân BìnhTạo ra view v3 để đếm xem mỗi quận có bao nhiêu khách hàng.Bài tập minh họa (3) Tạo view V5 đếm xem có bao nhiêu mặt hàng trong một hoá đơn (MAHD, NGAY, MAKH, SLMH)Tạo view V6 lấy ra hoá đơn có số lượng mặt hàng lớn nhất (MAHD, NGAY, MAKH, SLMH)Tạo view V7: Lấy ra danh sách các khách hàng có hoá đơn trong năm 1996*Ngôn ngữ lập trình Transaction – SQL Nội dung bài họcEnterprise Application ArchitechterBiến và các kiểu dữ liệuToán tửHàmCác câu lệnh điều khiểnCác cách sử dụng các lệnh T-SQLBài tập ứng dụngEnterprise Application ArchitectureXác định các lớp logical (Logical Layers)Thiết kế các lớp vật lý (Physical Layers)Truy xuất dữ liệuXác định các mức Logic (1)Xác định các mức Logic (2)Data presentation LayerĐược xem là user service and cho phép user xem và thao tác lên data: web browser and các Microsoft Windows® applications Sử dụng các service mà application logic layer cung cấpXác định các mức Logic (3)Application Logic LayerChứa application logic, định nghĩa các rules và processes giúp cho user không cần truy xuất trực tiếp vào databaseClients kết nối vào business service để kết nối vào data server. Business service là các custom-built components hoặc integrated applications và services, ví dụ như Web services. Application logic layer chứa các components để tạo thành transaction services, messaging services, hoặc object và connection management services.Xác định các mức Logic (3)Data Services LayerData services bao gồm data access logic và data storage. Bao gồm các SQL Server stored procedures để quản lý data traffic và integrity trên the database server.Thiết kế các lớp vật lýTruy xuất dữ liệuBiến và các kiểu dữ liệuKhai báo biếnKiểu dữ liệuKhai báo biếnDùng từ khoá declare để khai báo biếnDECLARE {@local_variable data_type} [,...n]Gán giá trị cho biếnSET @local_variable_name = expressionVí dụDECLARE @vLastName char(20), @vFirstName varchar(11)SET @vLastName = 'Dodsworth‘SELECT @vFirstName = FirstNameFROM Northwind..EmployeesWHERE LastName = @vLastNamePRINT @vFirstName + ' ' + @vLastNameData Type (1)IntegersBigint: 8 bytesInt: 4 bytesSmallint: 2 bytesTinyint: 1 byte, từ 0 -> 255.bitBit: 1 hoặc 0 value.decimal and numericDecimal từ -10^38+1->10^38 –1. Numeric: giống decimal.money and smallmoneyMoney: 8 bytesSmallmoney: 4 bytesApproximate NumericsFloat: từ -1.79E + 308 -> 1.79E + 308.Real: từ -3.40E + 38 -> 3.40E + 38.Data Type (2)datetime and smalldatetimeDatetime: từ 1/1/1753-> 31/12/9999.Smalldatetime từ 1/1/1900, -> 6/6/2079.Character StringsChar: Fixed-length non-Unicode character, , >, >=, 0begin print ‘There are many Canada customers'endelse print ‘Welcome'Các câu lệnh điều khiển(3)WHILE Boolean_expression     { sql_statement | statement_block }     [ BREAK ]     { sql_statement | statement_block }     [ CONTINUE ] BREAK: thoát ra khỏi vòng whileCONTINUE: restart lại vòng lập, bỏ qua các lệnh sau CONTINUE. Các câu lệnh điều khiển(4)GOTO LabelNameIF (SELECT SYSTEM_USER()) = 'payroll' GOTO calculate_salary -- Other program code would appear here. -- When the IF statement evaluates to TRUE, -- the statements between the GOTO and -- the calculate_salary label are ignored. -- When the IF statement evaluates to FALSE the -- statements following the GOTO are executed.calculate_salary: -- Statements to calculate a salary would appear after the label. Các câu lệnh điều khiển(5)Simple CASE function:CASE input_expression     WHEN when_expression THEN result_expression         [ ...n ]     [ ELSE else_result_expression    ] END Searched CASE function:CASE    WHEN Boolean_expression THEN result_expression         [ ...n ]     [         ELSE else_result_expression     ] ENDCác câu lệnh điều khiển(6)Inline commentsSELECT ProductName,(UnitsInStock + UnitsOnOrder) AS Max, -- Calculates inventory SupplierIDFROM ProductsSELECTBlock comments/*** This code retrieves all rows of the products table** and displays the unit price, the unit price increased** by 10 percent, and the name of the product.*/SELECT UnitPrice, (UnitPrice * 1.1), ProductName FROM ProductsCác câu lệnh điều khiển(7)Trả về một thông báo lỗi do user định nghĩaCú pháp:RAISERROR ( { msg_id | msg_str } { , severity , state } )     [ WITH option [ ,...n ] ] Các câu lệnh điều khiển(8)Tham sốmsg_id là mã lỗi được lưu trong sysmessages table. msg_str: là chuỗi thông báo lỗi được định dạng giống như lệnh printf trong lập trình CSeverity: mức độ nghiêm trọng của lỗi. Có giá trị từ 0->18 được dùng bởi user, từ 19 -> 25 được dùng bởi sysadmin (dùng với WITH LOG).State: số nguyên từ 1 ->127 mô tả mức độ cần thiết của lỗi. Các cách thực hiện các lệnh T-SQLCác lệnh có cấu trúc độngDùng BatchDùng ScriptsDùng TransactionsDùng XMLDùng cấu trúc lệnh động Dùng lệnh EXECUTE với các hằng chuỗi và biến Sử dụng khi ta phải gán giá trị cho biến tại thời điểm executeCác biến và table tạm chỉ tồn tại trong thời gian thực thi lệnh.Syntax:EXECUTE ({@str_var | 'tsql_string'} + [{@str_var | 'tsql_string'}...])}Sử dụng khối Một hoặc nhiều lệnh T- SQL được submit cùng lúc với nhau.Sử dụng lệnh GO để kết thúc một khốiCác biến không thể tham chiếu sau lệnh GOKhông thể kết hợp các lệnh sau đây trong batch:CREATE PROCEDURECREATE VIEWCREATE TRIGGERCREATE RULECREATE DEFAULTVí dụ lệnh khối lệnh batch hợp lệCREATE DATABASE ...CREATE TABLE ...GOCREATE VIEW1 ...GOCREATE VIEW2 ...GOCREATE DATABASE ...CREATE TABLE ...CREATE TRIGGER ...CREATE TRIGGER ...GOVí dụ lệnh khối lệnh batch không hợp lệ Sử dụng script Script là một tập tin có phần mở rộng là .sql, có nội dung là các lệnh T-SQL, được tạo bởi bất kỳ một Text editor nào.Được thực hiện trong công cụ SQL Query Analyzer hoặc osql UtilityĐược dùng lại khi cần.Dùng TransactionsĐược xử lý giống một BatchBảo đảm tính toàn vẹn dữ liệuToàn bộ các lệnh trong transaction sẽ thành công hoặc không thành côngMột transaction có thể có nhiều batchTransaction được bắt đầu bằng lệnh BEGIN TRANSACTIONVà kết thúc bằng lệnh COMMIT TRANSACTION Hoặc ROLLBACK TRANSACTION Ví dụ transaction trong database QLVTBEGIN TRANSACTION insert into chitiethoadon values ('hd001','vt03',50,null,30000) IF @@ERROR 0 BEGIN RAISERROR ('Transaction not completed.', 16, -1) ROLLBACK TRANSACTION END update vattu set slton = slton-50 where mavt='vt03' IF @@ERROR 0 BEGIN RAISERROR ('Transaction not completed.', 16, -1) ROLLBACK TRANSACTION ENDCOMMIT TRANSACTIONDùng XMLCho phép Client Browser định dạng dữ liệu:Dùng mệnh để FOR XML trong lệnh SELECT để trả kết quả dạng XMLDùng FOR XML AUTO Hoặc FOR XML RAW Khi dùng mệnh đề FOR XML trong lệnh SELECT, ta không thể dùng:Lệnh SELECT lồng nhauMệnh đề INTO .Mệnh đề COMPUTE BY.Dùng Stored procedures mà được gọi trong lệnh INSERT Định nghĩa view hoặc user-defined function để trả về một rowset.Ví dụ dùng XML (1)Use QLVTSELECT makh, tenkh FROM khachhang FOR XML AUTODùng XML (2)Use QLVTSELECT makh, tenkh FROM khachhang FOR XML RAWBài tập ứng dụng (1)Viết một đoạn mã lệnh để cho biết tổng trị giá của tất cả các hoá đơn của khách hàng có mã là KH01 trong năm 2000 với kết quả trả vềKhách hàng KH01 có tổng trị giá các hoá đơn là Nếu Khách hàng đó không có hoá đơn nào thì in ra chuỗi:Khách hàng này không mua hàng trong năm 2000declare @tg intselect @tg=sum(sl*giaban)from chitiethoadon as cthd, hoadon as hdwhere cthd.mahd = hd.mahd and hd.makh='kh01‘ and year(ngay)=2000if @tg>0 print 'Khach hang kh01 co tong tri gia ' + str(@tg,10)else print 'Khach hang chua mua hang'Bài tập ứng dụng(2)Khai báo một biến n để chứa số lượng các chi tiet hoá đơn có trong table cthd của hoá đơn hd001. Nếu n=0 thì xoá hoá đơn có mã là hd001 trong table hoá đơn, ngược lại thì xuất ra thông báo lỗi “Hoa don nay khong xoa duoc”Hãy thực hiện các lệnh để xoá một hoá đơn có mã hd là hd001. nếu hoá đơn hd001 có chi tiết hoá đơn thì phải xoá các chi tiết hoá đơn của hd này trước. Các lệnh này phải hoàn thành hoàn toàn.Tính số lượng hoá đơn của 2 khách hàng KH01 và KH02 và in ra kết quả so sánh 2 số lượng hoá đơn nàyBài tập ứng dụng(3)Liệt kê các chi tiết hoá đơn của hoá đơn HD01 gồm các thông tin: Mã vật tư, SL, Giá bán, KM với KM = 0 nếu SL =10KM = (SL*giaban) * 0.2 nếu SL >=100*Stored ProcedureMục tiêu bài họcHiểu được stored procedure là gì, procedure hoạt động như thế nào.Quản lý procedure: Tạo, xoá, sửa và thực thi Tham số trong store procedureBài tập áp dụngNội dung bài họcGiới thiệu Stored proceduresTạo, thực thi, cập nhật và xoá stored proceduresBài tập thực hànhTruyền tham số trong stored proceduresĐiều khiển lỗi Một số lưu ýGiới thiệu Stored Procedure (1)Stored procedure là một tập các lệnh Transact SQL được đặt tên và lưu trữ trong database serverCó thể nhận tham số vào và tham số trả giá trị vềTrả về trạng thái thực thi của procedure là thành công hay không thành côngGiới thiệu Stored Procedure (2)Có 5 loại stored procedure:System (sp_): có trong master database, được truy xuất từ bất kỳ một database nào, nhằm cung cấp các thông tin system catalog hoặc thực hiện các nhiệm vụ của administration. Local : được tạo từ userTemporary: có tên bắt đầu bằng # (local) hoặc ## (global). Không còn tồn tại sau khi SQL Server shutdownRemote: giới hạn việc thực hiện một stored procedure trên remote SQL Server Extended (xp_) được implement bởi các nôn ngữ khác và được gọi là các DLL. Sau khi viết xong extended stored procedure, sysadmin đăng ký extended stored procedure với SQL Server và sau đó gán quyền cho users khác để thực hiện. Extended stored procedures chỉ được có trong master database. Xử lý Stored procedure (1)_InitialDelayed Name Resolution: cho phép stored procedure tham chiếu đến các đối tượng chưa tồn tại trong lúc procedure được tạo. Delayed Name resolution được thực hiện trong lúc procedure được thực hiệnCreation: Các lệnh trong procedure sẽ được phân tích cú pháp theo cú pháp của T-SQL. Nếu thành công, tên của stored procedure được lưu trong SysObjects table, còn text của procedure lưu trong SysComments.Execution: Khi lần thứ nhất mà procedure được thực hiện hoặc khi procedure phải recompile, query processor sẽ đọc procedure trong process được gọi là resolution. Sau giai đoạn resolution, SQL Server sẽ tạo ra một sơ đồ thực hiện và đặt sơ đồ này trong procedure cacheXử lý stored procedure (2)_ SubsequentLợi ích của stored procedureCho phép lập trình theo hướng modular (modular programming)Thực thi nhanh hơn, giảm được việc chiếm dụng đường truyền mạngBảo mật.Xử lý các chức năng và chia sẽ với các ứng dụng khácCú pháp tạo procedureCREATE PROCEDURE procedure_name     [ { @parameter data_type }     [ VARYING ] [ = default ] [ OUTPUT ]     ] [ ,...n ] [ WITH     { RECOMPILE | ENCRYPTION | RECOMPILE , ENCRYPTION } ] AS sql_statement [ ...n ] procedure_name: tên stored procedure@parameter: tham số, có tối đa 2.100 parameters trong một rpoceduredata_type : kiểu dữ liệu của tham số, bao gồm tất cả kiểu dữ liệu trong SQL Server.VARYING: chỉ định kết quả của tham số trả về là một result set. Chỉ được áp dụng cho cursor parameters.default: giá trị mặc nhiên, nếu tham số có giá trị mặc nhiên thì khi thực hiện procedure, có thể user không cần truyền tham số vào khi thực thiOUTPUT : chỉ định rằng đây là output parameterRECOMILE: procedure sẽ được dịch lại mỗi khi thực thiENCRYPTION: mã hoá mã lệnh của lệnh create procedure khi lưu vào table syscommentCú pháp tạo procedured (2)procedure_name: tên stored procedure@parameter: tham số, có tối đa 2.100 parameters trong một rpoceduredata_type : kiểu dữ liệu của tham số, bao gồm tất cả kiểu dữ liệu trong SQL Server. Tuy nhiên, kiểu cursor chỉ được dùng cho output parameterdefault: giá trị mặc nhiên, nếu tham số có giá trị mặc nhiên thì khi thực hiện procedure, có thể user không cần truyền tham số vào khi thực thi (nó sẽ có giá trị default)OUTPUT : chỉ định rằng đây là output parameterRECOMILE: procedure sẽ được dịch lại mỗi khi thực thiENCRYPTION: mã hoá mã lệnh của lệnh create procedure khi lưu vào table syscommentVARYING: chỉ định kết quả của tham số trả về là một result set. Chỉ được áp dụng cho cursor parameters.Ví dụ: CSDL NorthwindTạo procedure P1 để liệt kê danh sách tất cả các productsCreate procedure Tạo procedure P2 để cập nhật gía cho các sản phẩm tăng 10%Create procedure Lưu ýCó thể tham chiếu đến các tables, view, procedure khác cũng như các temporary tableĐể tạo một procedure, user phải có quyền CREATE PROCEDURE (sysadmin, hoặc database owner) Kích thước của procedure tối đa là 128 MBCó thể lồng 32 cấp procedureDùng procedure sp_helptext để hiển thị nội dung text của stored procedure mà user đã tạo Không thể kết hợp lệnh create procedure với các lệnh SQL khác để tạo thành một bó lệnh (batch)Chỉ có thể tạo procedure trong database hiện hành.Thực thi procedureLệnh để thực thi một stored procedure:EXECUTE [ @return_status = ] procedure_name [ [ @parameter = ] { value | @variable [ OUTPUT ] | [ DEFAULT ] ] [ ,...n ] [ WITH RECOMPILE ]Chỉ định rằng lấy giá trị trả về của tham sốChỉ định rằng lấy giá trị default của tham sốChỉ định rằng procedure phải recompile trước khi thực hiệnVí dụ Thực thi procedure P1 và P2:Execute P1goExecute P2goVí dụ 2: Create procedure Mexico_Customers as select * from customers where country=‘Mexico’goExecute Mexico_customersProcedure có tham sốcreate proc CustomerListOfCountry @country varchar(40)asselect customerid, CompanyName from customers where country=@country goexecute CustomerListOfCountry ‘Canada’Hoặc truyền tham số với giá trị khácexecute CustomerListOfCountry ‘USA’Nếu không truyền tham số:Execute CustomerListOfCountry ????????Procedure có tham số có giá trị defaultcreate proc CustomerList @country varchar(40)='canada' as select customerid, CompanyName from customers where country=@country goGọi thực thi có : execute CustomerList ‘Mexico’Gọi thực thi không truyền tham số: Execute CustomerList Procedure dùng output parameterTạo Procedure để trả về số lượng khách hàng có giá trị country là tham số truyền vào:create proc P2 @country varchar(40) = '%', @total integer OUTPUTAS SELECT @total = count(*) FROM customers WHERE country like @countryGoThực thi procedure P2declare @sluong integerExecute P2 'canada', @sluong outputSELECT 'The total customers of canada is ' +str(@sluong,4)goTham số có kiểu là cursor Chỉ dùng cho tham số OUTPUT. Nếu kiểu của tham số là cursor thì VARYING và OUTPUT là bắt buộc. Nếu VARYING được chỉ định cho một tham số thì kiểu dữ liệu của tham số phải là cursor và từ khoá OUTPUT phải được chỉ định. Kiểu dữ liệu Cursor (1)Được dùng trong procedure hoặc triggerChứa result set column, recordXử lý cursor:Khai báo biến kiểu cursor chứa dữ liệu trả về Kết hợp cursor với câu lệnh select bằng lệnh DECLARE CURSORDùng lệnh OPEN để mở cursorDùng lệnh FETCH INTO để đổ một record hiện hành vào các biến tương ứng với từng column.Dùng lệnh CLOSE để đóng cursorKiểu dữ liệu Cursor (2)sp_cursor_list để lấy ra danh sách các cursor hiện cósp_describe_cursor, sp_describe_cursor_column và sp_describe_cursor_tables để xem đặc tính của cursorSau khi cursor mở, hàm @@CURSOR_ROWS tra về số lượng record Sau lệnh FETCH, hàm @@FETCH_STATUS để phản ánh trạng thái fetch sau cùng (0,-1)System stored procedureDescriptionsp_cursor_listReturns a list of cursors currently visible on the connection and their attributes.sp_describe_cursorDescribes the attributes of a cursor, such as whether it is a forward-only or scrolling cursor.sp_describe_cursor_columnsDescribes the attributes of the columns in the cursor result set.sp_describe_cursor_tablesDescribes the base tables accessed by the cursor.SCROLL: Cursor có thể di chuyển hai chiềuLocal: Biến có phạm vi localCú pháp khai báo cursorDECLARE cursor_name CURSOR [ LOCAL | GLOBAL ] [ FORWARD_ONLY | SCROLL ] [ STATIC | KEYSET | DYNAMIC | FAST_FORWARD ] [ READ_ONLY | SCROLL_LOCKS | OPTIMISTIC ] FOR select_statement [ FOR UPDATE [ OF column_name [ ,...n ] ] ]Cursor_name: tên biến cursorGlobal: Biến có phạm vi Global, có thể truy xuất trong bất kỳ procedure hoặc bó lệnh nàoFORWARD_ONLY: Cursor chỉ di chuyển một chiều từ dòng đầu đến dòng cuốiStatic: định nghĩa một cursor là staticDYNAMIC: Định nghĩa một cursor phản ánh tất cả các thay đổi của dữ liệu trong result set.FAST_FORWARD: chỉ định FORWARD_ONLY, READ_ONLY cursor. FAST_FORWARD không thể được chỉ định với SCROLL hoặc FOR_UPDATE KEYSET: các thành phần và thứ tự các dòng trong cursor cố định khi cursor được mở. Tập các khoá trong cursor được lưu trong một table trong database tempdb gọi là keyset. READ_ONLY: không cho phép câp nhật thông qua cursorOPTIMISTIC: SQL Server không lock các dòng nếu như chúng được đọc vào cursor. FOR UPDATE [OF column_name [,...n]]: định nghĩa các cột có thể cập nhật trong cursor.SCROLL_LOCKS: khoá các dử liệu mà đã được đọc vào cursor Đọc và xử lý dữ liệu trong cursorFETCH [NEXT| PRIOR| FIRST| LAST| ABSOLUTE n| RELATIVE n] FROM Tên_cursor[INTO Danh_sách_biến]Đóng CursorClose Tên_cursorDeallocate Tên_cursorVí dụ 1DECLARE customer_cursor CURSOR FOR SELECT * FROM customersOPEN customer_cursor -- mở cursorFETCH NEXT FROM customer_cursorVí dụ 2 (1)DECLARE @customerId varchar(11), @CompanyName varchar(30), @message varchar(80) PRINT "-------- Customer report --------“DECLARE customer_cursor CURSOR FOR SELECT customerid, companyName FROM customers WHERE country = "USA" OPEN customer_cursor FETCH NEXT FROM customer_cursor INTO @customerid, @companyName Ví dụ 2 (2)DECLARE @customerId varchar(11), @CompanyName varchar(30), @message varchar(80) While @@FETCH_STATUS = 0 begin print ‘Customer ID: ‘ + @customerID print ‘Company Name: ‘ + @companyName Fecth next from customer_cursor into @customerid, @companyName endClose customer_cursorDeallocate customer_cursorgoSử dụng OUTPUT cursor parameter USE northwind CREATE PROCEDURE customer_cursor @customer_cursor CURSOR VARYING OUTPUT AS SET @customer_cursor = CURSOR FORWARD_ONLY STATIC FOR SELECT * FROM CUSTOMERSOPEN @customer_cursor GO Sử dụng tham số cursor trả về DECLARE @MyCursor CURSOR EXEC customer_cursor @customer_cursor = @MyCursor OUTPUT WHILE (@@FETCH_STATUS = 0) BEGIN FETCH NEXT FROM @MyCursor END CLOSE @MyCursor DEALLOCATE @MyCursor Bài tập ứng dụng (NorthWind)Tạo procedure và thực thi để in ra company name có số lượng orders nhiều nhấtTạo proc p1 để trả về doanh thu của năm truyền vào, nếu user không truyền ngày vào thì lấy năm hiện hànhKhai báo một procedure CustomersOfCountryCursor để lấy ra một cursor chứa các record của table customers có country bằng giá trị truyền vào. Thực thi CustomersOfCountryCursor và in ra các dữ liệu có trong cursor trả về.Đăng ký Extended stored proceduresp_addextendedproc [ @functname = ] 'procedureName' ,     [ @dllname = ] 'dll fileName '@functname : Là tên của function trong DLL. procedure: procedure là tên procedure.[ @dllname = ] 'dll‘: Là tên của DLL chứa function đăng ký.Giá trị trả về là 0 (success) or 1 (failure)USE master EXEC sp_addextendedproc xp_hello, 'xp_hello.dll' Nesting stored procedureCó thể lồng 32 cấp Mức nesting hiện hành được lưu trong hàm @@nestlevelNếu procedure thứ nhất gọi procedure thứ hai thì procedure thứ hai có thể truy xuất đến tất cả các đối tượng mà được tạo ra bởi procedure thứ nhất, ngay cả table tạmCó thể gọi đệ quiBài tập áp dụng (database QLVT)Tạo procedure P1 để lấy ra danh sách các hoá đơn gồm các thông tin: MAHD, NGAY, TENKH, TONGTGTạo procedure P2 để xoá các chi tiết hoá đơn của hoá đơn có mã là tham số truyền vàoTạo procedure P3 để tính tổng doanh thu của năm với năm là tham số truyền vào và trả về giá trị là tổng doanh thu đã tính được.Tóm tắt nội dung buổi họcStored procedure trong SQL Serever giống procedure trong các ngôn ngữ lập trìnhXử lý nhanh hơn batchProcedure có thể có các tham số input và outputthực thi một stored procedure dùng lệnh executeQ & ATạo proc lấy ra danh sách khách hang có dịa chị là tham số truyền vào:CREATE PROC P2 @DC VARCHAR(50)AS select * from khachhang where diachi=@dcCreate proc P1AsSelect hd.mahd, tenkh, ngay, sum(sl*giaban) as tongtgFrom hoadon hd, khachhang kh, chitiethoadon cthdWhere (hd.mahd=cthd.mahd) and (kh.makh = hd.makh)Group by hd.mahd, tenkh, ngayCreate proc p2 @mahd nvarchar(10)As delete * from chitiethoadon where mahd=@mahdGo-- thuc thiExec p2 ‘hd002’goCreate proc p3 @nam int, @dt int OUTPUTAs select @dt=sum(sl*giaban) From chitiethoadon cthd, hoadon hd Where (hd.mahd=cthd.mahd) AND year(ngay)= @namGoDeclare @dt intExec p3 2000,@dt OUTPUTPrint ‘Doanh thu nam 2000 la ‘ + str(@dt,8)Trong QLVTTạo procedure để lấy ra tên của các khách hàng đã mua hàng trong tháng . Và năm .. (tham số input). Danh sách này được trả về trong một kiểu cursor. Thực thi P4 để lấy ra danh sách các khách hàng của tháng 6 năm 2000 và in ra tên của các khách hàng đó.Trong NorthWindKhai báo một procedure CustomersOfCountryCursor để lấy ra một cursor chứa các record của table customers có country bằng giá trị truyền vào. Thực thi CustomersOfCountryCursor và in ra các dữ liệu có trong cursor trả về.*User Defined FunctionsSau khi học này, sinh viên có thể:Hiểu được 3 loại user-defined functions. Tạo và thay đổi các user-defined functions.Nội dungUser-defined Function là gì?Định ngĩa User-defined FunctionsMột số vị dụ tạo User-defined FunctionsUser Defined Functions là gì (1)User-defined function có thể không hoặc có nhận vào nhiều input parameters có thể trả giá trị về là một giá trị vô hướng hay một bảng. Input parameters có kiểu dử liệu trong bảng data type ngoại trừ timestamp, cursor, hoặc table. Không hỗ trợ output parameters.User Defined Functions là gì (2)User-defined functions có 3 loại:Scalar Functions: giống như các scalar function đã có sẳn Multi-Statement Table-valued Functions: là hàm trả về một table được xây dựng từ một hoặc nhiều Transact-SQL statementsGiống stored procedure nhưng có thể được tham chiếu trong mệnh đề FROM của lệnh SELECT, xem như là một view nhưng hỗ trợ đệ quy.In-Line Table-valued Functions Trả về một table là kết quả của một lệnh SELECT đơn. Giống view nhưng phức tạp hơn ví có truyển tham sốTạo Scalar Functions CREATE  FUNCTION function_name ([{@parameter_name [AS] scalar_parameter_data_type [=default]}[ ,...n ]] ) RETURNS scalar_return_data_type [ WITH [ [,] ...n] ] [ AS ] BEGIN     function_body     RETURN scalar_expressionENDTham sốscalar_parameter_data_type: là các kiểu dữ liệu vô hướng (trừ kiểu cursor và table). Kiểu timestamp và các user-defined data types không được hỗ trợ. scalar_return_data_type: giá trị vô hướng được trả về ngoại trừ kiểu text, ntext, image, và timestamp.scalar_expression: tính ra giá trị mà hàm sẽ trả về.function_body là một dãy các lệnh Transact-SQL dùng để tính toán ra một scalar value. Ví dụ (1)CREATE FUNCTION fn_NewValue(@myinput nvarchar(30))RETURNS nvarchar(30)BEGINIF @myinput IS NULLSET @myinput = 'Not Applicable'RETURN @myinputENDÁp dụngViết hàm f1 để kiểm tra một số là chẳn hay lẻViết hàm f2 để tính n giai thừaTrong csdl QLVATTU, tính doanh thu của năm, với năm là tham số truyền vàoLấy ra tên của sản phẩm được bán với số lượng nhiều nhất trong năm, với năm là tham số truyền vàoTạo Inline Table-valued Functions CREATE FUNCTION [owner_name.]function_name     ([{ @parameter_name [AS] scalar_parameter_data_type [=default]} [ ,...n ] ] ) RETURNS TABLE [WITH [ [,] ...n ] ] [ AS ] RETURN [ ( ] select-stmt [ ) ] Tham sốTABLE: chỉ ra rằng giá trị của hàm được trả về là một tableTABLE trả về được định nghĩa thông qua lệnh SELECT đơn. Không kết hợp với các biến trả về select-stmt: là một lệnh SELECT đơn để định nghĩa giá trị trả về cho hàm.Ví dụ (2)create function dbo.intable (@vt nvarchar(30))returns tableas return(select * from vattu where mavt like @vt)godeclare @vt1 nvarchar(30)set @vt1 ='vt01'select* from dbo.intable (@vt1 )Áp dụng (QLVT)Tạo hàm để trả về danh sách các khách hàng đã mua hàng trong tháng, năm truyền vàoTạo hàm để trả về số lượng bán được của từng mặt hàng trong năm truyền vàoTạo Multi-statement Table-valued Functions CREATE FUNCTION [ owner_name. ] function_name ([{@parameter_name [AS] scalar_parameter_data_type [=default]}[ ,...n ] ] ) RETURNS @return_variable TABLE [ WITH [ [,] ...n ] ] [ AS ] BEGIN     function_body     RETURNENDTham số @return_variable là một TABLE variable, được dùng để lưu trử và tích luỹ các dòng sẽ cho hàm. function_body là dãy các lệnh Transact-SQL để trả về một table cho hàm. :: =  ( { column_definition | table_constraint } [ ,...n ] ) Ví dụ (3)create function mult_table(@diachi nvarchar(50)=null)returns @khach table (Makh nvarchar(5) PRIMARY KEY, tenkh nvarchar(30) NOT NULL, diachi nvarchar(50), DT nvarchar(15), email nvarchar(30) )asbeginif @diachi is null insert into @khach select * from khachhangElse insert into @khach select * from khachhang where diachi like @diachireturnendÁp dụng CSDL QLVTTạo function f3 để trả về một table chứa các khách hàng có địa chỉ là tham số truyền vào, nếu địa chỉ không truyền vào thì trả về tất cả khách hàngTạo function f4 để trả về một table chứa các hoá đơn của khách hàng có mã truyền vào cùng với năm.Các hạn chế của FunctionsNondeterministic functions là các hàm có thể trả về các giá trị khác nhau với cùng một tập các giá trị input. Ví dụ hàm GETDATE(). Các Built-in nondeterministic functions không được phép gọi trong phần thân của user-defined functions.Built-in nondeterministic functions Permissions Muốn tạo được user defined function, user phải có quyền CREATE FUNCTION Quyền CREATE FUNCTION là default của sysadmin role, db_owner và db_ddladmin roles. Các sysadmin và db_owner có thể gán quyền CREATE FUNCTION cho các user khác bằng lệnh GRANT.Q&AViết hàm để tính tổng doanh thu của một tháng trong năm.Lấy ra mã hoá đơn có trị giá lớn nhất.Viết hàm để trả về một bảng các hoá đơn có cùng ngày mua với ngày mua của khách hàng có mã là tham số truyền vào.Viết hàm để trả về số lượng khách hàng ở địa chỉ là tham số truyền vàoViết hàm để lấy ra danh sách khách hàng ở địa chỉ là tham số truyền vào.Dem so luong khach hang mua hang trong nam voi nam la tham so truyen vao. Neu nam khong truyen vao, thi dem tat ca khach hangTạo f1 lấy tra doanh thu theo năm với là tham số truyền vàoCreate function f1 (@nam int) returns intAsBegin declare @dt int select @dt=sum(sl*giaban) from chitiethoadon cthd, hoadon hd where (cthd.madh=hd.mahd) and year(ngay)=@nam return @dtendSelect dbo.f1(2000) Cơ sở dữ liệu QLDIEMTạo hàm tính điểm trung bình ứng với mã sinh viên và học kỳ được truyền vào.Tạo hàm tính xếp loại ứng với mã sinh viên và học kỳ được truyền vào.Tạo view V1 chứa các thông tin MASV, HOTEN, DIEMTB, XL của học kỳ 1 với DIEMTB = trung bình điểm của các môn học (lấy điểm lần 2 nếu có điểm, ngựơc lại, lấy điểm lần 1), XL là DAT hoặc HONG.*TRIGGERSau bài học này, sinh viên có thể: Hiểu được trigger là gì, công dụng của nó Tạo trigger. Xoá trigger Thay đổi triggerNội dung bài họcGiới thiệu TriggersĐịnh nghĩa TriggersTriggers làm việc như thế nàoCác ví dụ TriggersCác lưu ýTrigger là gìTrigger là một stored procedure đặc biệt được gọi tự động khi user cập nhật dữ liệu trên một tableĐược kết hợp với table: Được định nghĩa trên một table cụ thể .Được gọi tự động: Khi có một thao tác cập chật dữ liệu trên table (insert, update, hoặc delete) thì trigger của thao tác tương ứng được tự động thực hiện. Khác với procedure, trigger không thể được gọi trực tiếp, không nhận tham sốLà một phần của transaction: những lệnh trong trigger được xem là một single transaction, có thể được roll back từ bất kỳ chổ nào trong triggerSử dụng trigger để làm gì?Để thực hiện cascade updates và cascade deletes qua các table quan hệ trong databaseÉp buộc tính toàn vẹn dữ liệu phức tạp : Thực hiện các ràng buộc có tham chiếu đến các column trong nhiều table.Định nghĩa Custom Error Messages: Dùng trigger để trả về các chuỗi thông báo trạng thái của một hàng động nào đó. Bảo trì các dữ liệu không được chuẩn hoá: ! Triggers hoạt động như thế nào?Triggers được thực hiện tự động sau khi lệnh INSERT, UPDATE, hoặc DELETE được thực hiện trên một table mà trigger đó được định nghĩa. Còn các constraints và INSTEAD OF trigger sẽ được kiểm tra trước khi lệnh INSERT, UPDATE, hoặc DELETE thực hiện.Constraints sẽ được kiểm tra trước trigger.Một table có thể có nhiều Triggers cho một action. Một trigger có thể được định nghĩa cho nhiều action.Khi có nhiều trigger trong một table, thì table owner có thể dùng procedure hệ thống sp_settriggerorder để chỉ định trigger đầu và trigger cuối để thực thi. Thứ tự của các trigger còn lại không thể sắp xếp được. User phải có quyền để thực hiện tất cả các lệnh mà được định nghĩa trong TriggersTable Owners không thể tạo ra các Triggers trên Views hoặc Temporary Tables nhưng có thể tham chiếu đến view và temporary.Triggers hoạt động như thế nàoTriggers không trả kết quả về.Triggers có thể điều khiển multi-row actions: một hành động INSERT, UPDATE, hoặc DELETE gọi một trigger có thể ảnh hưởng lên nhiều dòng dữ liệu, Ta có thể chọn: Xử lý tất cả các dòng cùng với nhau trong trường hợp các dòng ảnh hưởng phải thoả điều kiện của trigger. Xử lý từng dòng thỏa điều kiện.Logic tablesKhi có action Insert, table logic inserted sinh ra, có cấu trúc giống với cấu trúc table được insert, có dữ liệu là record đang được insertKhi co action delete, table deleted sinh ra, có cấu trúc giống với cấu trúc table bị deleted, có dữ liệu là record đang bị xoáKhi có action update, có 2 table inserted và deletedTạo triggerCREATE TRIGGER trigger_name ON { table | view } [ WITH ENCRYPTION ] {     {{ FOR | AFTER | INSTEAD OF } { [ INSERT ] [ , ] [ UPDATE ] [ , ] [ DELETE] }   [ WITH APPEND ]        AS         [ { IF UPDATE ( column )             [ { AND | OR } UPDATE ( column ) ]  [ ...n ]           } ]         sql_statement [ ...n ]     } } Tham số (1)Table | view : tên view/table mà tigger được thực hiện khi có action tương ứng. WITH ENCRYPTION: mã hoá nột dung text của lệnh create trigger trong table syscomments. AFTER: Trigger sẽ được gọi chỉ khi tất cả các hành động đã thực hiện xong. Các kiển tra constrain và cascade sẽ được kiểm tra hoàn thành trước khi trigger thực hiện. Default là AFTER nếu chỉ có từ khoá FOR được chỉ định. AFTER trigger không thể định nghĩa trên view.INSTEAD OF: chỉ định trigger đựoc thực hiện thay cho action của trigger. INSTEAD OF triggers không cho phép cập nhật dữ liệu trên view có WITH CHECK OPTION.Tham số (2){ [DELETE] [,] [INSERT] [,] [UPDATE] } : chỉ định action gắn với trigger. Đối với INSTEAD OF triggers, action DELETE không cho phép trên table mà có relationship mà chỉ định CASCADE ON DELETE. Tương tự, action UPDATE không cho phép trên table có relationships mà CASCADE ON UPDATE.Table deleted và inserted là logical tables. Chúng có cấu trúc giống với table mà trigger được định nghĩa, chứa các dòng giá trị cũ hoặc mới mà có thể thay đổi bởi action của user. Ta có truy xuất dữ liệu trong 2 table này trong định nghĩa trigger. Tham số (3)Các giá trị kiểu text, ntext, hoặc image trong table inserted và deleted không truy xuất được. Khi trigger ở mức 65, giá trị null sẽ được trả về cột có kiểu text, ntext, hoặc image trong table inserted hoặc deleted nếu cột cho phép null; chuỗi zero-length được trả về nếu cột có thể null. IF UPDATE (column): kiểm tra action update trên cột được chỉ định, không dùng cho action delete. With Append: Chèn thêm trigger này vào các trigger đã có trước đóVí dụUse NorthwindGOCREATE TRIGGER Empl_Delete ON EmployeesFOR DELETEASIF (SELECT COUNT(*) FROM Deleted) > 1BEGIN RAISERROR( 'You cannot delete more than one employee at a time.', 16, 1) ROLLBACK TRANSACTIONENDNhững lệnh sau đây không được dùng trong định nghĩa triggerVí dụ (1)USE pubs IF EXISTS (SELECT name FROM sysobjects WHERE name = 'reminder' AND type = 'TR') DROP TRIGGER reminder GO CREATE TRIGGER reminder ON titles FOR INSERT, UPDATE AS RAISERROR (50009, 16, 10) GO Alter triggerALTER TRIGGER trigger_name ON table [WITH ENCRYPTION] {{FOR {[,] [DELETE] [,] [UPDATE] [,][INSERT]} AS sql_statement [...n] } | {FOR {[,] [INSERT] [,] [UPDATE]} AS IF UPDATE (column) [{AND | OR} UPDATE (column) [,...n]] sql_statement [...n] } }Xoá triggerDROP TRIGGER trigger_nameBài tập áp dụng (QLVT)Tạo trigger để khi insert một record vào trong table CHITIETHOADON, thì cập nhật lại SLTON của vật tư đó trong table VATTUTạo trigger để không cho phép một hoá đơn có nhiều hơn 4 chi tiết hoá đơnTạo trigger không cho phép hai vật tư trùng tênTạo trigger để không cho phép xoá cùng lúc nhiều hơn một khách hàngBài tập áp dụng (QLVT)Tạo trigger để không cho phép xoá một vật tư mà đã có ít nhất một chi tiết hoá đơn của vật tư đó.Tạo trigger để kiểm tra số lượng bán ra của một vật tư phải nhỏ hơn số lượng tồn trong khoTạo trigger để khi insert một record ...create trigger t1on chitiethoadon1for insertasdeclare @sl int, @mavt varchar(10)select @sl = sl, @mavt = mavt from insertedupdate vattu1 set slt=slt- @sl where mavt =@mavt