HỆ QUẢN TRỊ CƠ SỞ DỮ LIỆU
Download
1 / 42

HỆ QUẢN TRỊ CƠ SỞ DỮ LIỆU - PowerPoint PPT Presentation


  • 55 Views
  • Uploaded on

HỆ QUẢN TRỊ CƠ SỞ DỮ LIỆU. SQL SERVER. NỘI DUNG. KHUNG NHÌN (VIEW) THỦ TỤC LƯU TRÚ (STORED PROCEDURE - SP) TRIGGER HÀM CỦA NGƯỜI DÙNG (USER DEFINED FUNCTION - UDF). VIEW. KHUNG NHÌN. Bảng ảo. NỘI DUNG. Định nghĩa Câu lệnh Mục đích Cập nhật dữ liệu thông qua View Mã hóa View.

loader
I am the owner, or an agent authorized to act on behalf of the owner, of the copyrighted work described.
capcha
Download Presentation

PowerPoint Slideshow about ' HỆ QUẢN TRỊ CƠ SỞ DỮ LIỆU' - june


An Image/Link below is provided (as is) to download presentation

Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author.While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server.


- - - - - - - - - - - - - - - - - - - - - - - - - - E N D - - - - - - - - - - - - - - - - - - - - - - - - - -
Presentation Transcript

N i dung
NỘI DUNG

  • KHUNG NHÌN (VIEW)

  • THỦ TỤC LƯU TRÚ (STORED PROCEDURE - SP)

  • TRIGGER

  • HÀM CỦA NGƯỜI DÙNG (USER DEFINED FUNCTION - UDF)


VIEW

KHUNG NHÌN

Bảng ảo


N i dung1
NỘI DUNG

  • Định nghĩa

  • Câu lệnh

  • Mục đích

  • Cập nhật dữ liệu thông qua View

  • Mã hóa View


Nh ngh a view
Định nghĩa VIEW

  • View là bảng không chứa dữ liệu, nó chỉ là truy vấn kết hợp dữ liệu từ 1 hay nhiều bảng có quan hệ với nhau và được lưu thành một đối tượng của SQL SV

  • NSD có thể áp dụng ngôn ngữ thao tác dữ liệu trên các View giống như Table.


Nh ngh a view1

USER_VIEWS

nv_kinhdoanh

SELECT manv, hoten,luong

FROM NHANVIEN

WHERE ma_phong = ‘KDA’;

NHANVIEN

Định nghĩa VIEW

SQL Server

SELECT *

FROM nv_kinhdoanh;


C u l nh t o view
Câu lệnh tạo VIEW

CREATE VIEW <Tên_View>

[(<danhsáchtêncột>) ]

[WITH ENCRYPTION]

AS <CâulệnhSELECT>[with check option]


C u l nh t o view1
Câu lệnh tạo VIEW

Vídụ 1: tạo View nv_kinhdoanh

CREAT VIEWnv_kinhdoanh

AS

SELECTManv, Hoten, Luong

FROM NHANVIEN

* Sửdụng View:

SELECT * FROM NV_KINHDOANH


C u l nh t o view2
Câu lệnh tạo VIEW

Ví dụ 2: tạo View nv_tre (nhân viên dưới 35 tuổi)

CREAT VIEW nv_tre (Manv, Hoten, Tuoi)

AS

SELECT Manv, Hoten, Year(Getdate()) – Year(Ngaysinh)

FROM NHANVIEN

WHERE Year(Getdate()) – Year(Ngaysinh) <= 35

* Sử dụng View:

SELECT * FROM NV_TRE

 Nếu một thuộc tính trong View được xây dựng từ một biểu thức thì bắt buộc phải đặt tên cho thuộc tính đó.


M c ch d ng view
Mục đích dùng VIEW

  • Hạn chế tính phức tạp của dữ liệu đối với NSD đơn giản.

  • Tạo ra bảng ảo có dữ liệu theo yêu cầu cho NSD và sử dụng trong thiết kế báo cáo.

  • Hạn chế quyền truy cập dữ liệu của NSD.

  • View dùng để trình bày các thông tin dẫn xuất.


C p nh t d li u th ng qua view
Cập nhật dữ liệu thông qua View

  • View định nghĩa dữ liệu trên một bảng thì có thể dùng Insert

  • Nếu trong định nghĩa View có chứa mệnh đề Inner join thì không thể dùng các thao tác Insert hay Delete để thay đổi dữ liệu

  • Nếu trong định nghĩa View có chứa mệnh đề With check option thì chỉ những bản ghi thỏa mãn điều kiện của View mới được Insert, Update


X y d ng view d a tr n view kh c
Xây dựng View dựa trên View khác

  • Khixóa 1 view, mọi view đượcxâydựngdựatrên view đócũngbịxóa.

  • Cóthểdùngthủtụcsp_helptextđểxemđịnhnghĩaView

  • Tạo View bằng EM

  • Mãhoá View: dùngWITH ENCRYPTION

    • Khôngthểxemđượcnội dung View

    • Khôngthểthayđổilạiđược


STORED PROCEDURE

THỦ TỤC LƯU TRÚ


Th t c l u tr stored procedure
THỦ TỤC LƯU TRÚ (STORED PROCEDURE)

  • SP được xây dựng từ các câu lệnh T-SQL và được lưu trú trên SQL server.

  • Muốn thực hiện một SP, NSD chỉ cần thực hiện một lời gọi hàm.

  • Khi SP được chạy lần đầu tiên nó sẽ được biên dịch qua 5 bước và sinh ra một mô hình truy vấn. Mô hình này sẽ được đặt trong một CSDL của SQL server, lần sau chạy lại thủ tục sẽ không phải dịch lại nữa.


Th t c l u tr stored procedure1
THỦ TỤC LƯU TRÚ (STORED PROCEDURE)

  • Năm bước biên dịch thủ tục:

    • Thủ tục được phân tích ra thành nhiều phần

    • Kiểm tra sự tồn tại của các đối tượng (view, table, …) mà thủ tục tham chiếu tới.

    • Lưu trữ tên thủ tục vào bảng sysobject, lưu trữ các mã lệnh của thủ tục vào bảng syscomments.

    • Sinh ra mô hình truy vấn của thủ tục và lưu vào bảng sysprocedure

    • Khi SP được chạy lần đầu tiên, cây truy vấn sẽ được đọc và được tối ưu thành một kế hoạch thủ tục và chạy  tiết kiệm thời gian tái phân tích, biên dịch cây truy vấn mỗi khi chạy thủ tục.


Th t c l u tr stored procedure2
THỦ TỤC LƯU TRÚ (STORED PROCEDURE)

  • Trong một phiên làm việc, nếu SP được thực hiện, nó sẽ được lưu trữ vào vùng nhớ đệm. Những lần sau nếu SP được gọi thực hiện lại thì nó sẽ được đọc trực tiếp ra từ vùng nhớ đệm  nâng cao hiệu suất chạy truy vấn.


Th t c l u tr stored procedure3
THỦ TỤC LƯU TRÚ (STORED PROCEDURE)

  • Cú pháp lệnh tạo SP:

    CREATE PROC[EDURE] <tên SP>

    [@ <tên tham số 1> <kiểu DL>

    [= <giá trị>] [OUTPUT], . . .]

    [WITH RECCOMPILE|ENCRYPTION|RECOMPILE, ENCRYPTION]

    AS

    <các câu lệnh SQL, câu lệnh điều khiển, . . .>

    Giải thích:

  • =<giá trị>: gán giá trị ban đầu cho tham số

  • OUTPUT: giá trị thay đổi của tham số ở trong thủ tục được đưa ra bên ngoài thủ tục.

    Ví dụ


Th t c l u tr stored procedure4
THỦ TỤC LƯU TRÚ (STORED PROCEDURE)

  • Thực hiện thủ tục:

    EXEC <tên SP>

  • Thay đổi thủ tục

    ALTER PRO[CEDURE] . . .

  • Xóa thủ tục

    DROP PRO[CEDURE] <tên SP>

  • Bó thủ tục


Th t c l u tr stored procedure5
THỦ TỤC LƯU TRÚ (STORED PROCEDURE)

  • Sử dụng tùy chọn RECOMPILE trong câu lệnh CREATE PRO:

    • Thủ tục sẽ được biên dịch lại từ đầu mỗi khi thực hiện

    • Khi biên dịch lại các thông tin mới của SP được cập nhật và tối ưu lại.

  • Sử dụng tùy chọn RECOMPILE trong câu lệnh EXEC

    • Thủ tục sẽ được biên dịch lại trong lần thực thi đó và lưu lại kế hoạch thực thi mới của nó vào vùng nhớ đệm.

  • Biên dịch lại tất cả các thủ tục của bảng:

    EXEC sp_recompile <tên bảng>


Th t c l u tr stored procedure6
THỦ TỤC LƯU TRÚ (STORED PROCEDURE)

  • Vídụ:

    USE qlnv;

    GO

    CREATE PROC DS_Phong

    @BienMaPhong CHAR (3)

    AS

    SELECT Manv, HoTen, NgaySinh

    FROM NhanVien

    WHERE [email protected]

    GO

    Exec DS_Phong 'KDA'

SP này như một view có tham số!


Th t c l u tr stored procedure7
THỦ TỤC LƯU TRÚ (STORED PROCEDURE)

  • Vídụ:

    USE qlnv;

    GO

    CREATE PROC Soluong_Phong

    @BienMaPhong CHAR (3),

    @SL int OUTPUT

    AS

    SELECT @SL = COUNT (*)

    FROM NhanVien

    WHERE [email protected]

    GO

    -- Thuc hien thu tuc luu tru

    DECLARE @dem INT; DECLARE @phong CHAR(3);

    SET @phong='KDA';

    EXEC Soluong_Phong @phong, @dem OUTPUT

    --Hoac

    EXEC Soluong_Phong @[email protected], @[email protected] OUTPUT

    PRINT 'So luongnhanvienphong ' + @phong + ' la: ' + CAST (@dem AS CHAR(20))

    --Xoa thu tuc luu tru

    DROP PROC Soluong_Phong


Th t c l u tr stored procedure8
THỦ TỤC LƯU TRÚ (STORED PROCEDURE)

  • Vídụ: (Thu tucluutruvoitham so macdinh)

    CREATE PROC Soluong_Phong_Default

    @BienMaPhong CHAR (3)='KDA',

    @SL int OUTPUT

    AS

    SELECT @SL = COUNT (*)

    FROM NhanVien

    WHERE [email protected]

    GO

    DECLARE @dem INT;

    EXEC Soluong_Phong_Default @[email protected]

    EXEC Soluong_Phong_Default 'QTA', @dem


Th t c l u tr store procedure

Create proc XepLoaiSV @MaSV varchar(10),

@DTB float output, @XL nvarchar(20) output

As

Set @DTB = (Select avg(Diem) from KetQua Kq

Where MaSV = @MaSV and not exists (

select * from KetQua Kq1

where Kq1.MaSV = @MaSV and Kq1.MaMH=Kq.MaMH

and Kq1.LanThi > Kq.LanThi

)

)

If @DTB >= 9

Set @XL = N’Xuât sac’

Else if @DTB >= 8

Set @XL = N’Gi~i’

Else if @DTB >= 7

Set @XL = N’Khá’

Else if @DTB >= 5

Set @XL = N’Trung bình’

Else

Set @XL = N’Yêu’

Go

THỦ TỤC LƯU TRÚ (STORE PROCEDURE)


USER DEFINED FUNCTION

Hàm của người dùng


H m c a nsd user defined functions udfs
HÀM CỦA NSD(USER DEFINED FUNCTIONS-UDFs)

  • Cúphápcâulệnh:

    CREATE FUNCTION <tênhàm>

    ([@<tênthamsố> <kiểu DL vôhướng> [= <giátrị>] [, . . .]])

    RETURNS <kiểuvôhướng>|<table>

    [WITH ENCRYPTION]

    AS

    BEGIN

    [<cáccâulệnh>]

    RETURN <giátrịvôhướng>| (<câulệnh SELECT>)


H m c a nsd user defined functions udfs1
HÀM CỦA NSD(USER DEFINED FUNCTIONS-UDFs)

--Ham vohuong

CREATE FUNCTION Ham_Soluong_Phong ( @BienMaPhong CHAR (3) )

RETURNSint

AS

BEGIN

DECLARE @SL int;

SELECT @SL = COUNT (*)

FROM NhanVien

WHERE [email protected] ;

RETURN (@SL);

END

--Su dung ham (Ham xuat hien trong bieu thuc)

SELECT dbo.Ham_Soluong_Phong('KDA');

SELECT MaPhong, count (*)

FROM NhanVien

GROUP BY MaPhong

HAVING count(*) > dbo.Ham_Soluong_Phong('QTA')


H m c a nsd user defined functions udfs2
HÀM CỦA NSD(USER DEFINED FUNCTIONS-UDFs)

--Ham cho ket qua la mot bang

CREATE FUNCTION Ham_DS_Phong (@BienMaPhong CHAR (3))

RETURNS @kq TABLE (Manv CHAR(5), HoTen NCHAR(40), NgaySinh DATETIME)

BEGIN

INSERT INTO @kq

SELECT Manv, HoTen, NgaySinh

FROM NhanVien

WHERE [email protected]

RETURN

END

-- Su dunghamtra ket qua bang nhu la TABLE

SELECT *

FROM Ham_DS_Phong ('QTA')


H m c a nsd user defined functions udfs3
HÀM CỦA NSD(USER DEFINED FUNCTIONS-UDFs)

  • UDFs giống như SP nhưng khác ở các điểm sau:


TRIGGER

BẪY LỖI


N i dung2
Nội dung

  • Kháiniệmvề trigger

  • Câulệnhtạo trigger

  • Cácloại trigger

  • Ứngdụngcủa trigger

  • Sửa, xóa trigger


Kh i ni m v trigger
Khái niệm về trigger

  • Thủtụclưutrú (Stored Procedure) đặcbiệt

  • Trigertựđộngđượcchạymỗikhicómộthànhđộngliênquanđếnnóxảyra

  • Trigerkhôngcóthamsố, khôngtrảragiátrịnhư SP bìnhthường.

  • Cácloại trigger: Insert trigger, Delete trigger, Update trigger, trigger tổnghợp.


C ph p c u l nh t o trigger
Cú pháp câu lệnh tạo trigger

CREATE TRIGGER <tên triger>

ON <tên bảng>|<tên view>

[WITH ENCRYPTION]

{

{FOR | AFTER| INSTEAD OF}

<INSERT [, UPDATE] [, DELETE]>

}

AS

<câu lệnh SQL>


Trigger ch n insert trigger nh ngh a
Trigger chèn (Insert trigger): định nghĩa

  • Tự động được thực hiện mỗi khi bản ghi mới được chèn vào bảng gắn với nó

  • Một bảng tạm Inserted sẽ được sinh ra

  • Bản ghi cần chèn sẽ được ghi vào bảng cơ sở và bảng Inserted


Trigger ch n v d
Trigger chèn: ví dụ

CREATE TRIGGER trInsNV

ON NHANVIEN

FOR INSERT

AS

RAISERROR('%d hang daduoc them vao bang NHANVIEN', 0, 1,@@rowcount)

Trigger nàytựđộngđượcthựchiệnmỗikhicóbảnghimớiđượcchènvàobảngNHANVIEN


Trigger x a v d
Trigger xóa: ví dụ

CREATE TRIGGER trDelNV

ON NHANVIEN

FOR DELETE

AS

RAISERROR('%d hàngbịxóatrongbảng NHANVIEN', 0, 1,@@rowcount)

Trigger nàytựđộngđượcthựchiệnmỗikhicómộthoặcnhiềubảnghibịxóa ở bảng NHANVIEN


Trigger c p nh t update trigger v d
Trigger cập nhật (Update trigger): ví dụ

CREATE TRIGGER trUpNV

ON NHANVIEN

FOR UPDATE

AS

RAISERROR('%d %d hàngđãđượcsửa ở bảngbảng NHANVIEN', 0, 1,@@rowcount)

Trigger nàytựđộngđượcthựchiệnmỗikhicómộtcâulệnh Update đượcthựchiệntrênbảng NHANVIEN.


Trigger t ng h p v d
Trigger tổng hợp: ví dụ

CREATE TRIGGER trIns_UpNV

ON NHANVIEN

FOR INSERT, UPDATE

AS

RAISERROR('%d hàng đã được thêm hoặc sửa ở bảng NHANVIEN', 0, 1,@@rowcount)


Trigger instead of
Trigger Instead of

  • chophépcậpnhật DL cácbảngthông qua view cóliênkếtnhiềubảng


Ng d ng m b o r ng bu c to n v n d li u
Ứng dụng: Đảm bảo ràng buộc toàn vẹn dữ liệu

CREATE TRIGGER trDelNV

ON NHANVIEN

FOR DELETE

AS

RAISERROR('%d hang bi xoatrong bang NHANVIEN', 0, 1,@@rowcount)

------------

CREATE TRIGGER trDelPhong

ON PHONG

FOR DELETE

AS

DELETE NHANVIEN FROM DELETED WHERE DELETED.MAPHONG =NHANVIEN.MAPHONG


Ng d ng ng g i quy t c nghi p v
Ứng dụng: Đóng gói quy tắc nghiệp vụ liệu

CREATE TRIGGER trInsNV_PHONG

ON NHANVIEN

FOR INSERT

AS

UPDATE PHONG SET SONV = SONV+1 WHERE PHONG.MAPHONG = (SELECT MAPHONG FROM INSERTED)


M t s ch khi d ng trigger
Một số chú ý khi dùng trigger liệu

  • Một bảng có nhiều trigger

  • Mỗi một trigger có tên duy nhất

  • Trong trigger thường dùng mệnh đề IF EXISTS


X a s a trigger
Xóa, sửa liệutrigger

  • Xoá trigger

    DROP TRIGGER <tên trigger>

  • Sửa trigger

    ALTER TRIGGER <tên trigger> . . .

  • Xem lại định nghĩa của trigger: sp_helptext

  • Tạo trigger bằng EM


ad