Chương 9: Views & Lập trình trong SQL

Chương 9 Views & Lập trình trong SQL 1 Views (virtual tables) trong SQL • View: là một bảng đơn được tạo ra từ các bảng cơ sở hoặc từ các view được tạo trước – View không tồn tại vật lý vì vậy view còn gọi là bảng ảo. – Đơn giản hoá việc truy cập dữ liệu – Chọn dữ liệu cần thiết ứng với mổi user, đảm bảo an toàn dữ liệu – Dùng để Import và export – View là một đối tượng của CSDL Views (virtual tables) trong SQL • Cú pháp tạo view: Ví dụ: CREATE VIEW view_name [(column[ ,...n ])] AS select_statement [ WITH CHECK OPTION ] CREATE VIEW WORKS_ON1 AS SELECT FNAME, LNAME, PNAME, HOURS FROM EMPLOYEE, PROJECT, WORKS_ON WHERE SSN=ESSN AND PNO=PNUMBER; Views (virtual tables) trong SQL Ví dụ: CREATE VIEW DEPTJNFO (DEPT_NAME,NO_OF_EMPS,TOTAL_SAL) AS SELECT DNAME, COUNT (*), SUM (SALARY) FROM DEPARTMENT, EMPLOYEE WHERE DNUMBER=DNO GROUP BY DNAME; Views (virtual tables) trong SQL Ví dụ: CREATE TABLE WORKS_ON_NEW AS SELECT FNAME, LNAME, PNAME, HOURS FROM EMPLOYEE, PROJECT, WORKS_ON WHERE SSN=ESSN AND PNO=PNUMBER GROUP BY PNAME; Views (virtual tables) trong SQL • Câu lệnh select trong view không được chứa: – Mệnh đề ORDER BY – Mệnh đề COMPUTE VÀ COMPUTE BY – Các bảng tạm không được tham chiếu trong view không dùng select Into trong view – Các trigger và chỉ mục không được tạo ra trên view 6 Views (virtual tables) trong SQL • DELETE VIEW: DROP VIEW view_name • RENAME Views: sp_rename old_viewname, new_viewname • CHECK VIEW: sp_helptext viewname • MODIFY VIEW : ALTER VIEW view_name (column_list) AS select_statement Lập trình trong SQL • Lập trình CSDL: Giao tiếp với chương trình ứng dụng – Chương trình bao gồm: Biến (variable), câu lệnh SQL và cấu trúc điều khiển. • Các khái niệm cơ bản: – Định danh (Identifiers) – Batch (tập các câu lệnh T-SQL liên tiếp kết thúc bằng lệnh GO) – Script Lập trình trong SQL • Kiểu dữ liệu: Có 2 loại – Kiểu dữ liệu của hệ thống (System - supplied data type) – Kiểu dữ liệu do người dùng định nghĩa (User- defined data type) • Reference to object: Server.database.owner.object Biến • Biến cục bộ (Local variable) – Cú pháp khai báo: – Ví dụ: DECLARE @EmpIDVar int DECLARE@ VariableName var_type Biến – Gán giá trị cho biến: Khi một biến được khai báo thì giá trị của nó là Null – Ví dụ 1: DECLARE @temp_name varchar(20) SELECT @temp_name = companyname FROM customers WHERE customerid = ‘adsff’ SET @VariableName = expression or SELECT{@VariableName=expression} [,…n] Biến – Ví dụ 2: DECLARE @temp_city varchar(10) SET @temp_city = ‘london’ SELECT * FROM Customers WHERE city = @temp_city Biến – Ví dụ 3: DECLARE @temp_CustID Char(5), @temp_name varchar(50) SET @temp_CustID = ‘ALFKI’ SELECT @temp_name = CompanyName FROM Customers Where CustomerID = @temp_CustID PRINT ‘CustomerID is ‘ + @temp_CustID + ‘ and Name is ‘+ @temp_name Biến • Biến toàn cục (Global Variables): là một hàm hệ thống. – Giá trị trả về của hàm được hiển thị bởi câu lệnh SELECT @@Variablename. – Không gán giá trị cho biến toàn cục. – Biến toàn cục không có kiểu – Tên biến được bắt đầu với @@. Biến • Một số biến toàn cục thông dụng – @@SERVERNAME: trả về tên của server – @@ROWCOUNT: số dòng chịu tác dụng của câu lệnh cuối cùng. – @@ERROR: trả về chỉ số index của lỗi – @@IDENTITY: trả về định danh . Biến Ví dụ: Update Employees set LastName = ‘Brooke’ Where LastName =‘Brook’ If(@@rowcount =0) begin print ‘No rows were updated’ return end Cách thực thi câu lệnh SQL • Cú pháp: Example: DECLARE @vname varchar(20), @table varchar(20), @vdbase varchar(20) SET @vname="'White'" SET @table='authors' SET @vdbase='pub' EXECUTE ('USE'+@vdbase + 'SELECT * FROM '+ @ vtable + 'WHERE au_lastname=‘+@vname) EXEC [USE] ({@string_variable| [ N ] 'tsql_string'} [+ ...n ] ) Cách thực thi câu lệnh SQL • Batches: là một tập các câu lệnh SQL gửi đến server và được thực thi tại cùng một thời điểm. – Nếu một câu lệnh trong batch bị lỗi thì SQL server sẽ không thực thi tất cả những câu lệnh trong batches. – Mỗi batch không thể chứa tất cả những câu lệnh sau: CREATE PROCEDURE, CREATE TRIGGER, CREATE VIEW, CREATE RULE, CREATE DEFAULT. Cách thực thi câu lệnh SQL Ví dụ: go use master if exists(select * from sysdatabases where name like 'sales') drop database sales go create database sales on ( name = sales_data, filename ='e:\sales_data.mdf', size = 1, maxsize = 5, filegrowth =1) log on ( name = sales_log, filename ='e:\sales_log.ldf', size = 1, maxsize = 2, filegrowth =1) Cách thực thi câu lệnh SQL • Transact-SQL Scripts: – Script là một tập các câu lệnh T-SQL được lưu trữ trong một file với một hoặc nhiều batchs. • Transactions: là một đơn vị công việc với 4 đặc điểm sau: – Atomic (nguyên tố) – Consistent (nhất quán) – Isolated (cô lập) – Durable (bền) Cách thực thi câu lệnh SQL • Cấu trúc của Transaction: BEGIN TRANSACTION [] [WITH MARK ]… … [SAVE TRANSACTION ]… … ROLLBACK TRANSACTION [ | ] COMMIT TRANSACTION Cách thực thi câu lệnh SQL Ví dụ: BEGIN TRAN UPDATE authors SET city=‘San Jose’ Where au_lname=‘smith’ INSERT titles VALUES(‘BU1122’,’Teach Yourself SQL’,’business’, ‘9988’, $35.00, $1000,10,4501,’a great book’) SELECT *from titleauthor COMMIT TRAN Cách thực thi câu lệnh SQL • Ví dụ: BEGIN TRAN DELETE Sales where titles_id =‘BU1032’ if @@ERROR >0 ROLLBACK TRAN (huỷ hoàn toàn giao tác) else COMMIT TRAN Cấu trúc điều khiển • IF … ELSE IF boolean_expression {sql_statement | statement_block} [ELSE boolean_expression {sql_statement | statement_block}] Cấu trúc điều khiển • BEGIN …END BEGIN {sql_statement | statement_ block} END Cấu trúc điều khiển • Ví dụ: IF ( SELECT COUNT(*) FROM authors WHERE contract =0) >0 BEGIN PRINT 'These authors do not have contracts on file: ' SELECT au_lname, au_fname, au_id FROM authors WHERE contract=0 END ELSE BEGIN PRINT 'All authors have contracts on file.' END Cấu trúc điều khiển • WHILE WHILE boolean_expression {sql_statement | statement_block} [BREAK] {sql_statement | statement_block} [CONTINUE] Cấu trúc điều khiển Ví dụ 1: DECLARE @counter INT SET @counter=0 WHILE (@counter<20) BEGIN INSERT INTO Pubs..paractice VALUES ('last'+CAST(@counter as char(2)), 'First') SET @counter=@counter+1 END Cấu trúc điều khiển Ví dụ 2: WHILE (SELECT AVG(price) FROM titles) < $30 BEGIN UPDATE titles SET price = price * 2 SELECT MAX(price) FROM titles IF (SELECT MAX(price) FROM titles) > $50 BREAK ELSE CONTINUE END PRINT 'Too much for the market to bear' Cấu trúc điều khiển • CASE – Cú pháp CASE đơn giản CASE input_expression WHEN when_expression THEN result_expression [ ...n ] [ELSE else_result_expression ] END Cấu trúc điều khiển – Cú pháp CASE đầy đủ: CASE WHEN Boolean_expression THEN result_expression [ ...n ] [ ELSE else_result_expression ] END Cấu trúc điều khiển Ví dụ: SELECT Category = CASE type WHEN 'popular_comp' THEN 'Popular Computing' WHEN 'mod_cook' THEN 'Modern Cooking' WHEN 'business' THEN 'Business' WHEN 'psychology' THEN 'Psychology' WHEN 'trad_cook' THEN 'Traditional Cooking ELSE 'Not yet categorized‘ END CAST(title AS varchar(25)) AS 'Shortened Title', price AS Price FROM titles WHERE price IS NOT NULL ORDER BY type, price COMPUTE AVG(price) BY type Cấu trúc điều khiển Ví dụ: SELECT ProductID, Quantity, UnitPrice, [discount%]= CASE WHEN Quantity <=5 THEN 0.05 WHEN Quantity BETWEEN 6 and 10 THEN 0.07 WHEN Quantity BETWEEN 11 and 20 THEN 0.09 ELSE 0.1 END FROM [Order Details] ORDER BY Quantity, ProductId Cấu trúc điều khiển • PRINT: Hiển thị kết quả câu lệnh SQL • RETURN • WAITFOR PRINT ‘any ACII Text’|@local_variable| @@FUNTION| String_expr RETURN [integer_expression] integer_expression : return value WAITFOR { DELAY 'time' | TIME 'time' } Cấu trúc điều khiển Ví dụ: BEGIN WAITFOR TIME '22:20' EXECUTE update_all_stats END Cấu trúc điều khiển • RAISERROR RAISERROR({msg_id | msg_str} { , severity , state } [ , argument [ ,...n ] ] ) [ WITH option [ ,...n ] ] Thủ tục (Stored Procedures) và Hàm (Function) Giới thiệu • Stored procedure (SP): là một đoạn mã chứa các khái báo hoặc các câu lệnh SQL. – Stored procedure được lưu trữ trong danh mục CSDL server và nó có thể được gọi từ trigger, một thủ tục khác hoặc một ứng dụng phía client. – Stored procedures có thể được tạo trong CSDL và tái sử dụng. – Thủ tục nhận tham số đầu vào và trả về một kết quả. Loại Stored procedures • System SP (sp): được lưu trữ trong CSDL Master, nhưng có thể thực thi ở bất kỳ CSDL nào. – sp_helptext: In nội dung của rule, a default, an unencrypted stored procedure, user-defined function, trigger, computed column, or view. Example : master.dbo.sp_helptext – sp_help: Xuất thông tin về một đối tượng của CSDL. – sp_depends: Hiển thị thông tin về đối tượng của CSDL phụ thuộc vào view(s), trigger(s), và procedure(s) trong CSDL. Loại Stored procedures Extended SP (xp): được tạo từ ngôn ngữ khác (C++,...) và được sử dụng như một thủ tục của SQL Server  User_defined : ‒Local sp: là đối tượng trong CSDL dùng để thực thi các tác vụ, có thể tạo trong CSDL master. ‒Temporary sp: local (tên bắt đầu với #) và global (tên bắt đầu với ##). Tạo stored procedures • Cú pháp: CREATE PROC [EDURE ] procedure_name [ ; number ] [ { @parameter data_type } [ VARYING ] [ = default ] [ OUTPUT ] ] [,...n ] [ WITH { RECOMPILE | ENCRYPTION | RECOMPILE, ENCRYPTION } ] [FOR REPLICATION ] AS sql_statement [ ...n ] Tạo stored procedures Ví dụ: CREATE PROCEDURE OrderSummary AS SELECT Ord.EmployeeID, SummSales=SUM(OrDet.UnitPrice*OrDet.Quantity ) FROM Orders AS Ord JOIN [Order Details] AS OrDet ON (Ord.OrderID = OrDet.OrderID) GROUP BY Ord.EmployeeID Thực thi stored Procedures • Execute: Dùng để thực thi – User-defined function. – System procedure. – User-defined stored procedure, – Extended stored procedure. Thực thi stored Procedures • Cú pháp: 44 [ [ EXEC [ UTE ] ] {[ @return_status = ] { procedure_name [ ;number ] | @procedure_name_var } [ [ @parameter = ] { value | @variable [ OUTPUT ] | [ DEFAULT ] ] [,...n ] [ WITH RECOMPILE ] Thực thi stored Procedures – Hoặc: Ví dụ: EXECUTE dbo.overdueOrders EXECUTE ProductName [ ; number ] [[, …n][ OUTPUT ]] Hiệu chỉnh stored Procedures • Cú pháp: ALTER PROC[EDURE] procedure_name [WITH option] AS sql_statement [...n] Hiệu chỉnh stored Procedures Ví dụ: ALTER PROC dbo.overdueOrders AS SELECT CONVERT(CHAR(8), RequiredDate,1) RequiredDate, CONVERT(CHAR(8), orderDate,1) orderDate, orderId, Customerid, EmployeeID FROM dbo.orders WHERE RequiredDate<GETDATE()and shippeddate is null ORDER BY RequiredDate Xóa stored Procedures • Cú pháp: DROP PROC owner.stored_procedure_name Tham số trong stored Procedures • Input parameter: CREATE PROCEDURE procedure_name [@parameter_name data_type] [=default_value] [WITH option] AS sql_statement [...n] Tham số trong stored Procedures Ví dụ1: CREATE PROC dbo.MovieByRating @rating varchar(5) = NULL AS SELECT rating , title FROM movie WHERE rating = @rating ORDER BY title Tham số trong stored Procedures Ví dụ 2: CREATE PROC sp_name @parameter data_type =value AS IF @parameter IS NULL BEGIN PRINT ‘Message Line 1’ PRINT ‘Message Line 2’ RETURN END SELECT statement GO 51 Viết thủ tục có tham số a, b dạng input để giải phương trình bậc nhất ax+b=0. create proc ptbn @a int,@b int as if(@a=0) print 'Phuong trinh vo nghiem.' else print 'phuong trinh co nghiem la:‘ +cast(cast(-@b as float)/@a as nvarchar(12)) go declare @a int declare @b int set @a=0 set @b=6 exec ptbn @a,@b Tham số trong stored Procedures • Output parameter: CREATE PROCEDURE procedure_name [@parameter_name data_type] [=default_value] OUTPUT [WITH option] AS sql_statement [...n] Tham số trong stored Procedures Ví dụ: CREATE PROC count_row @movie_count int OUTPUT AS SELECT @movie_count = COUNT(*) FROM Movie GO • Sp execution with output parameter: a variable must be declared to stored the return value of the output parameter DECLARE @num int EXEC count_row @num OUTPUT SELECT @num Quản lý lỗi • sp_addmessage: Lưu thông báo lỗi mới do người dùng định nghĩa trong một thể hiện của Database Engine – Thông báo lỗi có thể được xem bằng sys.messages. • Cú pháp: sp_addmessage [ @msgnum= ] msg_id , [ @severity= ] severity , [ @msgtext= ] 'msg' [ , [ @lang= ] 'language' ] [ , [ @with_log= ] { 'TRUE' | 'FALSE' } ] [ , [ @replace= ] 'replace' ] Quản lý lỗi Ví dụ: Create an error message by sp_addmessage EXEC sp_addmessage @msgnum = 50001, @severity = 10, @msgtext=‘Cannot delete customer. Customer has orders .’, @withlog = ‘true’ GO Quản lý lỗi Example: Display an error message when delete a customer CREATE PROC DeleteCust @cust_num nvarchar(5) = null AS IF EXISTS (SELECT customerID FROM Orders WHERE customerID like @cust_num) BEGIN RAISERROR (50001, 10, 1) RETURN END DELETE FROM Customers WHERE customerID like @cust_num GO Quản lý lỗi • @@ERROR: trả về số của lỗi nếu lệnh trước đó gặp lỗi Ví dụ: USE AdventureWorks2008R2; GO UPDATE HumanResources.EmployeePayHistory SET PayFrequency = 4 WHERE BusinessEntityID = 1; IF @@ERROR = 547 PRINT N'A check constraint violation occurred.'; GO Quản lý lỗi • @@ERROR và @@ROWCOUNT: sử dụng để xác nhận tính hợp lệ của câu lệnh UPDATE. – Giá trị của @@ERROR được kiểm tra cho bất kỳ dấu hiệu của một lỗi. – Giá trị của @@ROWCOUNT được sử dụng để chắc chắn lệnh cập nhật thành công vào các dòng trong bảng. Hàm (FUNCTION) • Hàm hệ thống (System function):  aggregate funtion: avg(), count(), count(*), sum(), max(), min(),...  Other function: getdate(), month(), upper(), user_name(),@@rowcount,... • Hàm do người dùng định nghĩa (User-defined function): Cho phép định nghĩa một hàm T-SQL mà nó có thể chấp nhận một hoặc nhiều tham số và trả về một giá trị đơn hoặc một bảng giá trị. Hàm (FUNCTION) • Có 3 loại hàm do người dùng định nghĩa: – Scalar: trả về một giá trị đơn dựa trên giá trị đầu vào. – Multi-statement Table-valued: trả về một tập các dòng. – Inline Table-valued: trả về một tập các dòng. Hàm (FUNCTION) • Scalar function: CREATE FUNCTION [ owner_name. ] function_name ( [ { @parameter_name [AS] scalar_parameter_data_type [= default ] } [ ,...n ] ] ) RETURNS scalar_return_data_type. [WITH [ [,] ...n] ] [AS ] BEGIN function_body RETURN scalar_expression END Hàm (FUNCTION) Ví dụ: CREATE FUNCTION dbo.OrderNum (@monthOrd tinyint ) RETURNS tinyint AS BEGIN DECLARE @Ordnum tinyint SELECT @Ordnum = count(orderid) FROM Orders WHERE month(orderdate)= @monthOrd RETURN @Ordnum END GO Hàm (FUNCTION) • Thực thi hàm: SELECT dbo.OrderNum(7) – Hoặc dùng trong mệnh đề Where Select orderid from orders Where dbo.OrderNum(7)>50 and month(orderdate)=7 Hàm (FUNCTION) 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 [)] Hàm (FUNCTION) Ví dụ: CREATE FUNCTION SalesByCategory(@Categoryid Int) RETURNS TABLE AS RETURN (SELECT c.CategoryName, P. ProductName, SUM(Quantity) AS TotalQty FROM Categories c INNER JOIN Products p ON c.CategoryID= p. CategoryID INNER JOIN [Order Details] od ON p.ProductID = od.ProductID WHERE c.CategoryID= @Categoryid GROUP BY c. CategoryName,p.ProductName) Hàm (FUNCTION) • Multistatement Table-valuesd CREATE FUNCTION [owner_name.]function_name ([{@parameter_name [AS] data_type [=default]} [ ,…n ]]) RETURNS @return_variable TABLE ({column_definition | table_constraint} [ ,…n ]) [WITH { ENCRYPTION | SCHEMABINDING } [ [,] ...n] ] [AS] BEGIN function_body RETURN END Ví dụ: CREATE FUNCTION Contacts(@suppliers bit=0) RETURNS @Contacts TABLE (ContactName nvarchar(30), Phone nvarchar(24), ContactType nvarchar(15)) AS BEGIN INSERT @Contacts SELECT ContactName, Phone, 'Customer' FROM Customers INSERT @Contacts SELECT FirstName + ' ' + LastName, HomePhone, 'Employee' FROM Employees IF @Suppliers=1 INSERT @Contacts SELECT ContactName, Phone, 'Supplier‘ FROM Suppliers RETURN END Hàm (FUNCTION) • Thực thi: SELECT * FROM CONTACTS(1) ORDER BY ContactName TRIGGERS-CURSOR Trigger • Trigger là một loại thủ tục đặc biệt, tự động thực thi khi một sự kiện xãy ra trong database server. • DML triggers: thực thi khi người dùng cố hiệu chỉnh dữ liệu thông qua một sự kiện ngôn ngữ thao tac dữ liệu (DML). • DML events gồm: các câu lệnh INSERT, UPDATE, or DELETE trên bảng hoặc view Tạo trigger • Cú pháp: CREATE TRIGGER [ schema_name . ]trigger_name ON { table | view } [ WITH ENCRYPTION ] { { { FOR | AFTER | INSTEAD OF } { [DELETE] [,] [ INSERT ] [, ] [ UPDATE ] } [ WITH APPEND ] [ NOT FOR REPLICATION ] AS sql_statement [ ...n ] } } Tạo trigger – Schema_name: tên của lược đồ chứa trigger. – Table | view: tên bảng hoặc View mà trên đó trigger thực thi. – WITH ENCRYPTION: mã hóa trigger. – FOR | AFTER | INSTEAD OF: loại trigger Tạo trigger • AFTER triggers hoặc FOR trigger : – Thực thi sau khi thực hiện insert/ delete các dòng trong table, gọi là reactive, chỉ tạo trên table. – Khi tạo trigger nếu không chỉ định rõ thì mặc định là AFTER Trigger 74 Tạo trigger • INSTEAD OF triggers: kiểm tra trứơc khi thực hiện Insert/Delete, gọi là proactive, tạo trên table và view. – Table1 có trigger1, table2 có trigger2, nếu thao tác trên table1 có liên quan đến table2 thì trigger2 tự động thực thi, gọi là trigger lồng (Nested Trigger) Tạo trigger 76 For/After Instead of - Chỉ áp dụng cho table - áp dụng cho table, view - Có thể định nghĩa nhiều trigger trên một hành động I/U/ D - Chỉ định nghĩa một Trigger trên một hành động I/U/D Thực thi sau khi : + xử lý ràng buộc + thực hiện xong hành động I/U/D phát sinh trigger - Thi hành trước khi: + Xử lý ràng buộc + Thay thế hành động phát sinh trigger - Không xây dựng được trên table có áp dụng cascade delete/ update Tạo trigger – {[DELETE] [ , ] [INSERT] [ , ] [UPDATE] }: chỉ định thao tác mà khi thực hiện thì trigger tự động thực thi • Khi Insert mẫu tin mới vào Table thì mẫu tin mới đó cũng lưu trong table INSERTED • Khi Delete mẫu tin trong table: Thì các mẫu tin bị xoá đó được di chuyển sang table DELETED. • Khi Update mẫu tin trong table: thì table đó và table INSERTED đều chứa mẫu tin có nội dung mới, còn DELETED chứa mẫu tin có nội dung cũ. Tạo trigger – WITH APPEND • Chỉ định thêm một trigger • WITH APPEND không được dùng với INSTEAD OF triggers. – NOT FOR REPLICATION • Trigger sẽ không thực hiện khi bảng có liên quan đến kỹ thuật sao chép nhân bản (relication) • sql_statement: câu lệnh SQL chứa điều kiện và hành động của triiger. Tạo trigger Ví dụ: tạo một trigger hiển thị số dòng được chèn vào bảng Nhomsach CREATE TRIGGER Thongbaomautin ON NHOMSACH FOR INSERT AS RAISERROR('Có %D mau tin duoc chen'