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à
– Đơ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
bảng ảo.
– Dùng để Import và export – View là một đối tượng của CSDL
an toàn dữ liệu
Views (virtual tables) trong SQL
• Cú pháp tạo view:
CREATE VIEW view_name [(column[ ,...n ])] AS select_statement [ WITH CHECK OPTION ] Ví dụ: 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
6
view
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
• 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)
SQL và cấu trúc điều khiển.
– 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)
defined data type) • Reference to object:
– Kiểu dữ liệu do người dùng định nghĩa (User-
Server.database.owner.object
Biến
• Biến cục bộ (Local variable)
– Cú pháp khai báo:
DECLARE@ VariableName var_type
– Ví dụ: DECLARE @EmpIDVar int
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 SET @VariableName = expression or SELECT{@VariableName=expression} [,…n]
– Ví dụ 1:
DECLARE @temp_name varchar(20) SELECT @temp_name = companyname FROM customers WHERE customerid = ‘adsff’
Biến
DECLARE @temp_city varchar(10) SET @temp_city = ‘london’ SELECT * FROM Customers WHERE city = @temp_city
– Ví dụ 2:
Biến
SELECT @temp_name = CompanyName FROM Customers
Where CustomerID = @temp_CustID
PRINT ‘CustomerID is ‘ + @temp_CustID + ‘ and Name is ‘+
@temp_name
– Ví dụ 3: DECLARE @temp_CustID Char(5), @temp_name varchar(50) SET @temp_CustID = ‘ALFKI’
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
– 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 @@.
SELECT @@Variablename.
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
– @@ERROR: trả về chỉ số index của lỗi – @@IDENTITY: trả về định danh .
lệnh cuối cùng.
Biến
Ví dụ:
return
Update Employees set LastName = ‘Brooke’ Where LastName =‘Brook’ If(@@rowcount =0)
begin print ‘No rows were updated’ 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ữ
• 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)
trong một file với một hoặc nhiều batchs.
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
{sql_statement | statement_ block}
BEGIN 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
BEGIN PRINT 'All authors have contracts on file.' END
ELSE
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
INSERT INTO Pubs..paractice VALUES ('last'+CAST(@counter as char(2)), 'First') SET @counter=@counter+1
SET @counter=0 WHILE (@counter<20) BEGIN 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 đủ:
result_expression [ ...n ]
WHEN Boolean_expression THEN [ ELSE else_result_expression ]
CASE END
Cấu trúc điều khiển
Ví dụ: SELECT Category =
ELSE 'Not yet categorized‘
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 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
WHEN Quantity <=5 THEN 0.05 WHEN Quantity BETWEEN 6 and 10 THEN 0.07 WHEN Quantity BETWEEN 11 and 20 THEN 0.09
0.1
Ví dụ: SELECT ProductID, Quantity, UnitPrice, [discount%]= CASE ELSE 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
PRINT ‘any ACII Text’|@local_variable| @@FUNTION| String_expr
• RETURN
RETURN [integer_expression] integer_expression : return value
• WAITFOR
WAITFOR { DELAY 'time' | TIME 'time' }
Cấu trúc điều khiển
Ví dụ:
BEGIN WAITFOR TIME '22:20' END
EXECUTE update_all_stats
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
‒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 ##).
User_defined :
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:
EXECUTE ProductName [ ; number ]
Ví dụ:
[
EXECUTE dbo.overdueOrders
Hiệu chỉnh stored Procedures
• Cú pháp:
sql_statement [...n]
ALTER PROC[EDURE] procedure_name [WITH option] AS
Hiệu chỉnh stored Procedures
orderDate,1)
orderDate,
Ví dụ:
ALTER PROC dbo.overdueOrders
AS
SELECT CONVERT(CHAR(8), RequiredDate,1) RequiredDate,
CONVERT(CHAR(8),
orderId,
Customerid, EmployeeID
FROM dbo.orders
WHERE 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:
PRINT ‘Message Line 1’
PRINT ‘Message Line 2’
BEGIN
RETURN
CREATE PROC sp_name
@parameter data_type =value
AS
IF @parameter IS NULL
END
SELECT statement
GO
51
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
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.
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
sys.messages.
• Cú pháp:
thể được xem bằng lỗi có
sp_addmessage [ @msgnum= ] msg_id , [
@severity= ] severity , [ @msgtext= ] 'msg'
[ , [ @lang= ] 'language' ]
[ , [ @with_log= ] { 'TRUE' | 'FALSE' } ]
[ , [ @replace= ] 'replace' ]
Quản lý lỗi
EXEC sp_addmessage
@msgnum = 50001,
@severity = 10,
@msgtext=‘Cannot delete customer. Customer has
orders .’,
@withlog = ‘true’
GO
Ví dụ: Create an error message by sp_addmessage
Quản lý lỗi
Example: Display an error message when delete a customer
CREATE PROC DeleteCust
@cust_num nvarchar(5) = null
RAISERROR (50001, 10, 1)
RETURN
BEGIN
END
DELETE FROM Customers
AS
IF EXISTS (SELECT customerID FROM Orders
WHERE customerID like @cust_num)
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ỳ
– 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.
dấu hiệu của một lỗi.
Hàm (FUNCTION)
• Hàm hệ thống (System function):
aggregate funtion: avg(), count(), count(*),
sum(), max(), min(),...
Other function: getdate(), month(), upper(),
• Hàm do người dùng định nghĩa (User-defined
user_name(),@@rowcount,...
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.
dòng.
– Multi-statement Table-valued: trả về một tập các
– 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 < function_option> [ [,] ...n] ]
[AS ]
BEGIN
function_body
RETURN scalar_expression
END
Hàm (FUNCTION)
Ví dụ:
DECLARE @Ordnum tinyint
SELECT @Ordnum = count(orderid)
FROM Orders
RETURN @Ordnum
CREATE FUNCTION dbo.OrderNum (@monthOrd tinyint )
RETURNS tinyint
AS
BEGIN
WHERE month(orderdate)= @monthOrd
END
GO
Hàm (FUNCTION)
• Thực thi hàm:
SELECT dbo.OrderNum(7)
Select orderid
from orders
Where dbo.OrderNum(7)>50 and month(orderdate)=7
– Hoặc dùng trong mệnh đề Where
Hàm (FUNCTION)
Table-valued Functions
CREATE FUNCTION [ owner_name. ] function_name
([{ @parameter_name
[AS] scalar_parameter_data_type [= default ] } [,...n ] ])
RETURNS TABLE
[WITH < function_option > [ [,] ...n ] ]
[AS ]
RETURN [(] select-stmt [)]
Hàm (FUNCTION)
Ví dụ:
(SELECT c.CategoryName, P. ProductName,
SUM(Quantity) AS TotalQty
FROM Categories c
CategoryID
INNER JOIN [Order Details] od ON p.ProductID =
od.ProductID
WHERE c.CategoryID= @Categoryid
CREATE FUNCTION SalesByCategory(@Categoryid Int)
RETURNS TABLE
AS
RETURN
INNER JOIN Products p ON c.CategoryID= p.
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
(ContactName
nvarchar(30),
Phone
TABLE
Ví dụ:
CREATE FUNCTION Contacts(@suppliers bit=0)
RETURNS @Contacts
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)
FROM
CONTACTS(1) ORDER
BY
• Thực thi:
*
SELECT
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.
là AFTER Trigger
74
– Khi tạo trigger nếu không chỉ định rõ thì mặc định
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
For/After
Instead of
- Chỉ áp dụng cho table
- áp dụng cho table, view
- Chỉ định nghĩa một Trigger
trên một hành động I/U/D
- Có thể định nghĩa nhiều
trigger trên một hành động
I/U/ D
+ Xử lý ràng buộc
+ Thay thế hành động
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:
phát sinh trigger
- Không xây dựng được trên
table có áp dụng cascade
delete/ update
76
Tạo trigger
• 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ũ.
– {[DELETE] [ , ] [INSERT] [ , ] [UPDATE] }: chỉ định
thao tác mà khi thực hiện thì trigger tự động thực
thi
Tạo trigger
• Chỉ định thêm một trigger
• WITH APPEND không được dùng với INSTEAD OF
triggers.
– WITH APPEND
• 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.
– NOT FOR REPLICATION
Tạo trigger
Ví dụ: tạo một trigger hiển thị số dòng được chèn vào
bảng Nhomsach
tin duoc chen', 0, 1,
RAISERROR('Có %D mau
@@ROWCOUNT)
PRINT 'Có ' + CAST(@@ROWCOUNT AS CHAR)+' mẫu tin
được chèn'
CREATE TRIGGER Thongbaomautin
ON NHOMSACH FOR INSERT
AS
Tạo trigger
Ví dụ:
Print ‘You cannot delete the Productid=12’
RollBack transaction
CREATE TRIGGER NoDelete
ON Product
FOR DELETE AS
IF(SELECT ProductID FROM Deleted )=12
BEGIN
END
Tạo trigger
Ví dụ:
PRINT ‘You cannot update Productid’
RollBack Transaction
CRETE TRIGGER NoUppdate
ON Product
FOR Update
IF Update(ProductID)
BEGIN
END
Tạo trigger
CREATE TRIGGER NoupdareOrders
Print ‘Ngay lap hoa don <=ngay hien hanh’
RollBack Transaction
Ví dụ:
ON Orders
FOR Update AS
IF (Select OrderDate from Deleted) > Getdate()
BEGIN
END
RAISERROR(500103,10,1)
Ví dụ:
CREATE TRIGGER Trg_NgayLap_NgayGiaoHD
ON Hoadon AFTER INSERT
AS
DECLARE @NgayLapHD DateTime, @NgayGiao DateTime
SELECT @NgayLapHD=hd.NGayLapHD,NgayGiao=hd.NgayGiaoNhan
FROM HoaDon hd INNER JOIN Inserted i ON hd.MaHD=i.Mahd
If @NgayGiao<@NgayLapHD
BEGIN
ROLLBACK TRANSACTION
END
Ví dụ: AFTER TRIGGERS
IN (SELECT hd.MaHD FROM HoaDon hd
INNER JOIN Deleted d ON hd.MaHD=d.Mahd)
CREATE TRIGGER Trg_Xoa_HD
ON Hoadon AFTER DELETE
AS
SET NOCOUNT ON
IF EXISTS (SELECT * FROM Deleted)
BEGIN
DELETE CT_HOADON WHERE CT_HOADON.MaHD
RAISERROR('Cac chi tiet HD da bi xoa',10,1)
END
SET NOCOUNT ON
Hiệu chỉnh Triggers
• Syntax:
ALTER 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 ]
}
}
Hiệu chỉnh Triggers
Ví dụ:
ALTER TABLE [Order Details]
• Xóa trigger:
– DROP TRIGGER Trigger_Name
DISABLE TRIGGER ALL
Con trỏ
hệ thống khi câu lệnh SQL được thực thi.
• Con trỏ là vùng làm việc tạm được tạo trong bộ nhớ
• Con trỏ chứa thông tin của câu lệnh select, sau khi
cursor được định vị trên một dòng, các hoạt động có
thể thực hiện trên dòng đó hoặc khối các dòng bắt
đầu từ vị trí con trỏ.
• Con trỏ có thể chứa nhiều hơn một dòng nhưng chỉ
thao tác trên một dòng tại một thời điểm.
Loại con trỏ
• Implicit cursors:
– Được tạo mặc định khi câu lệnh INSERT, UPDATE,
DELETE được thực thi và khi câu lệnh SELECT trả
về đúng một dòng được thực thi.
• Explicit cursors:
– Được tạo khi thực thi câu lệnh SELECT và trả về
nhiều hơn một dòng.
– Mặc dù cursor lưu trữ nhiều record nhưng chỉ có
một record được thực thi tại một thời điểm gọi là
record hiện hành.
Khai báo con trỏ
• Cú pháp:
DECLARE cursor_name CURSOR
[ LOCAL | GLOBAL ]
[ FORWARD_ONLY | SCROLL ]
[ STATIC | KEYSET | DYNAMIC | FAST_FORWARD ]
[ READ_ONLY | SCROLL_LOCKS | OPTIMISTIC ]
[ TYPE_WARNING ]
FOR select_statement
[ FOR UPDATE [ OF column_name [,...n ] ] ]
Sử dụng con trỏ
trong SQL Server:
– Khai báo cursor
– Mở cursor.
– Lấy mẫu tin hoặc điều hướng cursor (FETCH)
– Dữ liệu trong dòng hiện hành có thể được hiệu
• Các thao tác cần thực hiện trong khi sử dụng cursor
chỉnhnếu cần thiết.
90
– Đóng cursor nhưng vẫn tồn tại.
– Giải phóng Cursor(deallocate) khi không dùng nữa.
Sử dụng con trỏ
• Open cursor:
• Fetch cursor: Access each line of data
OPEN {cursor_name }
| ABSOLUTE n | RELATIVE n]]
FETCH [[NEXT | PRIOR | FIRST | LAST
FROM cursor_name
[INTO @variable_name [,...n ] ]
Sử dụng con trỏ
• Delete data by cursor:
• Close cursor:
DELETE
WHERE CURRENT OF
• DEALLOCATE: Removes a cursor reference
CLOSE cursor_name
DEALLOCATE cursor_name
• Ví dụ:
c.CustomerID,c.Companyname,c.contactname,
o.OrderID,o.OrderDate
@od DATETIME, @cn VARCHAR( 80)
DECLARE MyCursor CURSOR FOR
SELECT
FROM Customers c, Orders o WHERE c.CustomerID = o.CustomerID
FOR UPDATE
--Open cursor
OPEN MyCursor
DECLARE @cid VARCHAR( 8), @c VARCHAR( 80), @o INT,
FETCH NEXT FROM MyCursor INTO @cid, @c, @cn, @o, @od
SELECT @cid
BEGIN TRANSACTION
UPDATE Customers SET CompanyName = 'q'
WHERE CURRENT OF Mycursor
DEALLOCATE MyCursor
SELECT * FROM Customers
ROLLBACK TRANSACTION
Review questions
1.Howdoes SQL allow implementation of general
integrity constraints?
2.What is a view in SQL, and how is it defined?
Discuss the problems that may arise when one
attempts to update a view. How are views
typically implemented?
3.List the three main approaches to database
programming. What are the advantagesand
disadvantages of each approach?
Review questions
4. What is the impedance mismatch problem?
Which of the three programming approaches
minimizes this problem?
5. Describe the concept of a cursor and how it is
used in embedded SQL.
6. What is SQLJ used for? Describe the two
types of iterators available in SQL
Review questions
• State which of the following queries and
updates would be allowed on the view. If a
query or update would be allowed, show what
the corresponding query or update on the
base relations would look like, and give its
result when appliedto the database of Figure
5.6.
Review questions
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:
PRINT ‘Message Line 1’ PRINT ‘Message Line 2’
BEGIN RETURN
CREATE PROC sp_name
@parameter data_type =value AS IF @parameter IS NULL END SELECT statement GO
51
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
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.
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 sys.messages.
• Cú pháp:
thể được xem bằng lỗi có
sp_addmessage [ @msgnum= ] msg_id , [ @severity= ] severity , [ @msgtext= ] 'msg' [ , [ @lang= ] 'language' ] [ , [ @with_log= ] { 'TRUE' | 'FALSE' } ] [ , [ @replace= ] 'replace' ]
Quản lý lỗi
EXEC sp_addmessage @msgnum = 50001, @severity = 10, @msgtext=‘Cannot delete customer. Customer has
orders .’, @withlog = ‘true’ GO
Ví dụ: Create an error message by sp_addmessage
Quản lý lỗi
Example: Display an error message when delete a customer CREATE PROC DeleteCust @cust_num nvarchar(5) = null
RAISERROR (50001, 10, 1) RETURN
BEGIN END
DELETE FROM Customers
AS IF EXISTS (SELECT customerID FROM Orders WHERE customerID like @cust_num) 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ỳ
– 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.
dấu hiệu của một lỗi.
Hàm (FUNCTION)
• Hàm hệ thống (System function):
aggregate funtion: avg(), count(), count(*),
sum(), max(), min(),...
Other function: getdate(), month(), upper(),
• Hàm do người dùng định nghĩa (User-defined
user_name(),@@rowcount,...
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.
dòng.
– Multi-statement Table-valued: trả về một tập các
– 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 < function_option> [ [,] ...n] ] [AS ]
BEGIN function_body RETURN scalar_expression END
Hàm (FUNCTION)
Ví dụ:
DECLARE @Ordnum tinyint SELECT @Ordnum = count(orderid) FROM Orders
RETURN @Ordnum
CREATE FUNCTION dbo.OrderNum (@monthOrd tinyint ) RETURNS tinyint AS BEGIN WHERE month(orderdate)= @monthOrd END GO
Hàm (FUNCTION)
• Thực thi hàm:
SELECT dbo.OrderNum(7)
Select orderid from orders Where dbo.OrderNum(7)>50 and month(orderdate)=7
– Hoặc dùng trong mệnh đề Where
Hàm (FUNCTION)
Table-valued Functions CREATE FUNCTION [ owner_name. ] function_name ([{ @parameter_name [AS] scalar_parameter_data_type [= default ] } [,...n ] ]) RETURNS TABLE [WITH < function_option > [ [,] ...n ] ] [AS ] RETURN [(] select-stmt [)]
Hàm (FUNCTION)
Ví dụ:
(SELECT c.CategoryName, P. ProductName, SUM(Quantity) AS TotalQty FROM Categories c
CategoryID
INNER JOIN [Order Details] od ON p.ProductID = od.ProductID WHERE c.CategoryID= @Categoryid
CREATE FUNCTION SalesByCategory(@Categoryid Int) RETURNS TABLE AS RETURN INNER JOIN Products p ON c.CategoryID= p. 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
(ContactName
nvarchar(30),
Phone
TABLE
Ví dụ: CREATE FUNCTION Contacts(@suppliers bit=0) RETURNS @Contacts 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)
FROM
CONTACTS(1) ORDER
BY
• Thực thi: * SELECT 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.
là AFTER Trigger
74
– Khi tạo trigger nếu không chỉ định rõ thì mặc định
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
For/After
Instead of
- Chỉ áp dụng cho table
- áp dụng cho table, view
- Chỉ định nghĩa một Trigger trên một hành động I/U/D
- Có thể định nghĩa nhiều trigger trên một hành động I/U/ D
+ Xử lý ràng buộc + Thay thế hành động
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: phát sinh trigger
- Không xây dựng được trên table có áp dụng cascade delete/ update
76
Tạo trigger
• 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ũ.
– {[DELETE] [ , ] [INSERT] [ , ] [UPDATE] }: chỉ định thao tác mà khi thực hiện thì trigger tự động thực thi
Tạo trigger
• Chỉ định thêm một trigger • WITH APPEND không được dùng với INSTEAD OF
triggers.
– WITH APPEND
• 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.
– NOT FOR REPLICATION
Tạo trigger
Ví dụ: tạo một trigger hiển thị số dòng được chèn vào bảng Nhomsach
tin duoc chen', 0, 1,
RAISERROR('Có %D mau @@ROWCOUNT) PRINT 'Có ' + CAST(@@ROWCOUNT AS CHAR)+' mẫu tin được chèn'
CREATE TRIGGER Thongbaomautin ON NHOMSACH FOR INSERT AS
Tạo trigger
Ví dụ:
Print ‘You cannot delete the Productid=12’ RollBack transaction
CREATE TRIGGER NoDelete ON Product FOR DELETE AS IF(SELECT ProductID FROM Deleted )=12 BEGIN END
Tạo trigger
Ví dụ:
PRINT ‘You cannot update Productid’ RollBack Transaction
CRETE TRIGGER NoUppdate ON Product FOR Update IF Update(ProductID)
BEGIN END
Tạo trigger
CREATE TRIGGER NoupdareOrders
Print ‘Ngay lap hoa don <=ngay hien hanh’ RollBack Transaction
Ví dụ: ON Orders
FOR Update AS IF (Select OrderDate from Deleted) > Getdate() BEGIN END
RAISERROR(500103,10,1)
Ví dụ: CREATE TRIGGER Trg_NgayLap_NgayGiaoHD ON Hoadon AFTER INSERT AS DECLARE @NgayLapHD DateTime, @NgayGiao DateTime SELECT @NgayLapHD=hd.NGayLapHD,NgayGiao=hd.NgayGiaoNhan FROM HoaDon hd INNER JOIN Inserted i ON hd.MaHD=i.Mahd If @NgayGiao<@NgayLapHD
BEGIN ROLLBACK TRANSACTION END
Ví dụ: AFTER TRIGGERS
IN (SELECT hd.MaHD FROM HoaDon hd INNER JOIN Deleted d ON hd.MaHD=d.Mahd)
CREATE TRIGGER Trg_Xoa_HD ON Hoadon AFTER DELETE AS SET NOCOUNT ON IF EXISTS (SELECT * FROM Deleted) BEGIN DELETE CT_HOADON WHERE CT_HOADON.MaHD RAISERROR('Cac chi tiet HD da bi xoa',10,1) END SET NOCOUNT ON
Hiệu chỉnh Triggers
• Syntax:
ALTER 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 ] } }
Hiệu chỉnh Triggers
Ví dụ: ALTER TABLE [Order Details]
• Xóa trigger:
– DROP TRIGGER Trigger_Name
DISABLE TRIGGER ALL
Con trỏ
hệ thống khi câu lệnh SQL được thực thi.
• Con trỏ là vùng làm việc tạm được tạo trong bộ nhớ
• Con trỏ chứa thông tin của câu lệnh select, sau khi cursor được định vị trên một dòng, các hoạt động có thể thực hiện trên dòng đó hoặc khối các dòng bắt đầu từ vị trí con trỏ.
• Con trỏ có thể chứa nhiều hơn một dòng nhưng chỉ
thao tác trên một dòng tại một thời điểm.
Loại con trỏ
• Implicit cursors:
– Được tạo mặc định khi câu lệnh INSERT, UPDATE, DELETE được thực thi và khi câu lệnh SELECT trả về đúng một dòng được thực thi.
• Explicit cursors:
– Được tạo khi thực thi câu lệnh SELECT và trả về
nhiều hơn một dòng.
– Mặc dù cursor lưu trữ nhiều record nhưng chỉ có một record được thực thi tại một thời điểm gọi là record hiện hành.
Khai báo con trỏ
• Cú pháp:
DECLARE cursor_name CURSOR [ LOCAL | GLOBAL ] [ FORWARD_ONLY | SCROLL ] [ STATIC | KEYSET | DYNAMIC | FAST_FORWARD ] [ READ_ONLY | SCROLL_LOCKS | OPTIMISTIC ] [ TYPE_WARNING ] FOR select_statement [ FOR UPDATE [ OF column_name [,...n ] ] ]
Sử dụng con trỏ
trong SQL Server: – Khai báo cursor – Mở cursor. – Lấy mẫu tin hoặc điều hướng cursor (FETCH) – Dữ liệu trong dòng hiện hành có thể được hiệu
• Các thao tác cần thực hiện trong khi sử dụng cursor
chỉnhnếu cần thiết.
90
– Đóng cursor nhưng vẫn tồn tại. – Giải phóng Cursor(deallocate) khi không dùng nữa.
Sử dụng con trỏ
• Open cursor:
• Fetch cursor: Access each line of data
OPEN {cursor_name }
| ABSOLUTE n | RELATIVE n]]
FETCH [[NEXT | PRIOR | FIRST | LAST FROM cursor_name [INTO @variable_name [,...n ] ]
Sử dụng con trỏ
• Delete data by cursor:
• Close cursor:
DELETE
• DEALLOCATE: Removes a cursor reference
CLOSE cursor_name
DEALLOCATE cursor_name
• Ví dụ:
c.CustomerID,c.Companyname,c.contactname,
o.OrderID,o.OrderDate
@od DATETIME, @cn VARCHAR( 80)
DECLARE MyCursor CURSOR FOR SELECT FROM Customers c, Orders o WHERE c.CustomerID = o.CustomerID FOR UPDATE --Open cursor OPEN MyCursor DECLARE @cid VARCHAR( 8), @c VARCHAR( 80), @o INT, FETCH NEXT FROM MyCursor INTO @cid, @c, @cn, @o, @od SELECT @cid BEGIN TRANSACTION
UPDATE Customers SET CompanyName = 'q' WHERE CURRENT OF Mycursor DEALLOCATE MyCursor SELECT * FROM Customers ROLLBACK TRANSACTION
Review questions
1.Howdoes SQL allow implementation of general
integrity constraints?
2.What is a view in SQL, and how is it defined? Discuss the problems that may arise when one attempts to update a view. How are views typically implemented?
3.List the three main approaches to database programming. What are the advantagesand disadvantages of each approach?
Review questions
4. What is the impedance mismatch problem? Which of the three programming approaches minimizes this problem?
5. Describe the concept of a cursor and how it is
used in embedded SQL.
6. What is SQLJ used for? Describe the two
types of iterators available in SQL
Review questions
• State which of the following queries and updates would be allowed on the view. If a query or update would be allowed, show what the corresponding query or update on the base relations would look like, and give its result when appliedto the database of Figure 5.6.
