So sánh view và stored procedures

Set @Ma_lop = 8DH Set @Ma_lop = 8DH9 + Year(@Ngay_tuyen) Set @Si_so_lop = (SELECT count(*) FROM SV)

#######  Mỗi từ khóa SET chỉ dùng gán cho một biến

9

Bi¿n trong sql server (tt)

 Gán giá trß cho bi¿n:

Có thể gán giá trß cho biến bằng mệnh đề SELECT

Ví dụ 1:

####### Select @Ma_lop = 8DH029, @Si_so =

Ví dụ 2:

####### Select @Si_so =SiSo From Lop Where Malop =8DH02 9

####### Select @max= max(diemlan1), @min=min(diemlan1)

####### from DIEMTHI where mahocphan='sql'

10

Bi¿n trong sql server (tt)

 Gán giá trß cho bi¿n:

Lưu ý:

####### Khi gán giá trß cho biến thì giá trß phải cùng kiểu dữ

####### liệu với biến

####### Giá trß gán cho biến là kết quả câu truy vấn thì kết

####### quả cÿa câu truy vấn phải trả về đúng 1 giá trß.

Ví dụ:

####### Set @Ho_ten = (Select HoTen From sinhvien)  lỗi:

#######

####### permitted&=

11

Bi¿n trong sql server (tt)

 In k¿t ra màn hình:

DECLARE @KQ INT SET @KQ= 100 PRINT N8Kết quả là: 8 PRINT @KQ PRINT N9Kết quả là 9 + Convert(NVARCHAR(10),@KQ) (Có thể dùng SELECT thay cho PRINT) 12

Toán tử

Các loại toán tử

 Số hác: *, /, %, - , +

 So sánh: =, <>, >, >=, <, <=

 Nối chuỗi: +

 Logic: AND, OR, NOT

13

Toán tử (tt)

Ví dụ:

BEGIN 14

Mßt số hàm sử dÿng trong SQL

MAX, MIN, SUM, COUNT, AVG, ROUND

LEN, LEFT, RIGHT, SUBSTRING, UPPER,

LOWER

DAY, MONTH, YEAR

Hàm GETDATE: Lấy ngày hiện hành

#######  Ví dụ:

15

Mßt số hàm sử dÿng trong SQL (tt)

#######  Hàm DATEPART(Đßnh dạng,9Tháng/Ngày/Năm9):

####### Lấy 1 phần (ngày, tháng, năm, ngày trong tuần,

####### ngày trong năm) cÿa ngày

#######  Ví dụ:

16 SELECT DATEPART(yyyy,'02/10/1982') AS N'Năm', DATEPART(mm,'02/10/1982') AS N'Th·ng', DATEPART(dd,'02/10/1982') AS N'Ng‡y', DATEPART(DAYOFYEAR,'02/10/1982') AS N'Ngày trong năm', DATEPART(WEEKDAY,'02/10/1982') AS N'Ngày thứ'

Mßt số hàm sử dÿng trong SQL (tt)

#######  Hàm DATEDIFF(Đßnh dạng, 8ngày bắt đầu9, 8ngày

####### kết thúc9): Lấy hiệu giữa hai mốc thời gian (ngày

####### kết thúc) và (ngày bắt đầu)

#######  Ví dụ:

17 Select DATEDIFF (dd,'2018-10-20', '2018-10-25') as N'số ngày'

C¿u trúc lßnh

Cấu trúc If & Else

Cấu trúc While

Phát biểu Continue

Phát biểu Break

Phát biểu Return

Cấu trúc Case

Cấu trúc Try..

18

C¿u trúc điÁu khiển

 IF&ELSE

L°u ý:

Trong Cấu trúc If.., nếu có từ hai lệnh trở

lên thì phải đặt giữa hai từ khóa Begin và End.

19

IF Biểu thāc

{câu lệnh hoặc nhóm lệnh được thực thi}

ELSE

{câu lệnh hoặc nhóm lệnh được thực thi}

C¿u trúc điÁu khiển

20

 Ví dÿ:

C¿u trúc điÁu khiển

21

 CASE&WHEN

 D¿ng 1:

CASE

WHEN THEN WHEN THEN & ELSE

END

CASE&WHEN

22

 Ví dÿ 1:

CASE&WHEN (tt)

Ví dụ 2:

#######  Nhan_vien(MaNV,HoTen,NgaySinh, Phai). Cho biết

####### những nhân viên đến tuổi về hưu biết rằng tuổi về

####### hưu cÿa nam là 60, nữ là 55)

####### Select * From Nhan_vien

####### Where datediff(yy,Ngaysinh,Getdate()) >=

####### CASE Phai

####### WHEN 8Nam9 THEN 60

####### WHEN 8Nu9 THEN 55

####### END

23

CASE&WHEN (tt)

CASE

####### WHEN

####### WHEN

####### &

####### WHEN

####### ELSE

END

24

 D¿ng 2:

CASE&WHEN (tt)

 Ví dÿ: Liệt kê thông tin nhập hàng bao gồm: Số

phiếu nhập, Ngày nhập, tên vật tư, số lượng, nhận

xét. Trong đó: nếu số lượng nhập >=1000 thì ghi là

nhập nhiều, từ 500 trở lên thì ghi là nhập đÿ, các

trường hợp còn lại ghi nhập ít.

25

C¿u trúc while

26

 WHILE

WHILE Biểu thức

Câu lßnh hoặc nhóm lßnh đ°ợc thực thi

 Chú ý : Nếu trong các cấu trúc điều khiển là

một nhóm lệnh thì phải đặt nhóm lệnh đó trong

cặp từ khóa BEGIN & END

C¿u trúc while

27

 Ví dÿ 1:

C¿u trúc while (tt)

 Ví dÿ 2:

Cho SV(Masv:int, HoTen:nvarchar(30))

Yêu cầu chèn vào bảng sinh viên một sinh viên

mới sao cho mã sinh viên cÿa sinh viên chèn

vào đảm bảo tính liên tục từ thấp đến cao

Chẳng hạn ta có MaSV: 1,2,3,4,8,9,&  Sinh

viên mới sẽ có mã là 5

28

C¿u trúc while (tt)

 Ví dÿ 2:

Cho SV(Masv: int, HoTen: nvarchar(30))

&..

Declare @count int

Set @count = 1

While exists (Select * From SV Where MaSV =

@count)

set @count = @count +

Insert into SV(MaSV, HoTen)

Values (@count, N8Lê Như Ngác9)

29

C¿u trúc while (tt)

BREAK: Thoát khỏi vòng lặp WHILE

RETURN: Chúng ta có thể dùng RETURN

bất kỳ thời điểm nào để thoát khỏi thÿ tục.

Các phát biểu sau RETURN sẽ không

được thực thi.

CONTINOUS: Các câu lệnh thực thi sau từ

khóa CONTINOUS trong WHILE điều được

bỏ qua để chuyển qua vòng lặp mới.

30

C¿u trúc Try..

Cấu trúc Try.. trong SQL Server được dùng

để bắt lỗi tương tự như trong C# và C++.

Một nhóm các lệnh được đặt trong khối Try, nếu có

một lỗi xuất hiện bên trong khối Try thì điều khiển

được gởi đến một nhóm lệnh khác được đặt trong

một khối Catch.

Chỉ có từ phiên bản SQL Server 2005.

31

C¿u trúc Try.. (tt)

Cấu trúc Try& Catch có cấu trúc như sau:

####### BEGIN TRY

####### { }

####### END TRY

####### BEGIN CATCH

####### { }

####### END CATCH

####### [ ; ]

32

C¿u trúc Try..

Một số thông tin về lỗi:

#######  ERROR_NUMBER(): Trả về mã số lỗi.

#######  ERROR_SEVERITY(): Trả về māc độ cÿa lỗi.

#######  ERROR_STATE(): Mã trạng thái cÿa lỗi.

#######  ERROR_PROCEDURE(): Trả về tên cÿa thÿ tục

####### hay trigger xuất hiện lỗi.

#######  ERROR_LINE(): Trả về số dòng bên trong thÿ tục

####### xuất hiện lỗi.

#######  ERROR_MESSAGE(): Trả về dòng văn bản thông

####### báo lỗi một cách đầy đÿ.

Các hàm này trả về Null nếu nó được gái bên

ngoài cÿa khối Catch. 33

C¿u trúc Try.. (tt)

Ví dụ: Điều khiển lỗi trong chia hai số

declare @thuong float, @sobichia float, @sochia float Select @sobichia = 3, @sochia = 0 begin try set @thuong=@sobichia/@sochia print @thuong end try begin catch SELECT ERROR_NUMBER() AS ErrorNumber, ERROR_SEVERITY() AS ErrorSeverity, ERROR_STATE() AS ErrorState, ERROR_PROCEDURE() AS ErrorProcedure, ERROR_LINE() AS ErrorLine, ERROR_MESSAGE() AS ErrorMessage; Print N'Số chia bằng không' end catch 34

C¿u trúc Try.. (tt)

Ví dụ: Khi thực thi thÿ tục trên: phepchia 3 , 0ết

quả trình bày trong ngăn Results và ngăn

Messages như sau:

35

Thā tÿc l°u trữ - (Stored Procedure)

1. Giới thiệu

2. Phân loại thÿ tục

3. Tạo thÿ tục

4. Lời gái thÿ tục

5. Giá trß trả về cÿa tham số trong thÿ tục

6. Phát biểu điều khiển

7. Tham số với giá trß mặc đßnh

8. Thÿ tục với tham số Table

9. Sửa đổi thÿ tục

10. Xoá thÿ tục

36

Stored Procedure là gì?

Là một nhóm các câu lệnh T-SQL đã được biên

dßch từ trước (pre-compiled).

Thực hiện một nhiệm vụ cụ thể

Một Stored Procedure

#######  Có thể không chāa hoặc chāa nhiều tham số truyền

####### vào;

#######  Có thể trả giá trß về thông qua tham số truyền cho

####### thÿ tục.

37

Stored Procedure là gì?

Có 2 dạng Stored Procedure:

#######  System Stored Procedure

#######  User-defined Stored Procedure

Các System Stored Procedure có sẵn khi chúng

ta cài đặt SQL Server. Tất cả các System

Stored Procedure đều bắt đầu bằng tiền tố sp_

38

T¿i sao dùng Stored procedure?

Tăng tốc độ thực thi

Mô đun hóa, dễ dàng gái lại

Dễ dàng nâng cấp, bảo mật.

39

Phân lo¿i

Trong SQL Server có 3 nhóm thÿ tục nội tại sau:

 Nhóm thứ nh¿t là do người dùng tạo ra.

Gồm hai loại:

#######  Loại thÿ tục nội tại được người dùng tạo ra và lưu

####### vào CSDL. Chúng chāa các phát biểu T-SQL.

#######  Loại thā hai được khai báo và tạo ra bằng ngôn

####### ngữ lập trình .NET.

40

Phân lo¿i (tt)

 Nhóm thứ hai là thÿ tục nội tại hệ thống thực

hiện các chāc năng quản trß CSDL thường

dùng. Các thÿ tục này chāa trong CSDL

Resource.

#######  Danh sách các thÿ tục nội tại hệ thống hiển thß

####### trong ngăn System Stored Procedure

#######  Thÿ tục nội tại trong CSDL Resource luôn có tên

####### với tiÁn tố là sp_. Do đó bạn không nên đặt tên

####### thÿ tục nội tại do mình tạo ra bằng tiền tố này.

 Nhóm thứ ba là thÿ tục nội tại hệ thống mở

rộng. Loại này cũng được lưu trong CSDL

Resouce nhưng có tên bắt đầu với xp_.

41

T¿o mßt Sroted procedure

CREATE PROC [EDURE] [()]ASBEGIN[DECLARE ]END

42

Cú pháp :

T¿o mßt sroted procedure (tt)

####### CREATE PROC[EDURE] [()]

####### AS

####### [BEGIN]

[DECLARE ]

####### [END]

43

####### Ví dÿ 1: CREATE PROCEDURE TinhTong (@a int, @b int)

####### AS

####### Begin

####### Declare @kq int

####### Set @kq = @a + @b

####### Print @kq

####### End

T¿o mßt sroted procedure (tt)

####### CREATE PROC[EDURE] [()]

####### AS

####### [BEGIN]

[DECLARE ]

####### [END]

44

####### Ví dÿ 2 : CREATE PROCEDURE sp_NVP (@mp int)

####### AS

####### SELECT * FROM NhanVien WHERE Maphong=@mp

T¿o mßt sroted procedure (tt)

 Ví dÿ 3 : Viết thÿ tục in ra tổng cÿa các số từ 1..

####### CREATE PROCEDURE Tinhtong (@n int)

####### AS

####### Declare @tong int, @i int

####### Select @tong=0, @i=0

####### While @i<=@n

####### Begin

####### Set @tong=@tong+@i

####### Set @i=@i+1

####### End

####### Print @tong

####### GO

45

T¿o mßt sroted procedure (tt)

 Ví dÿ 4 : Cho CSDL quản lý điểm thi như sau:

46

T¿o mßt sroted procedure (tt)

47

T¿o mßt sroted procedure (tt)

48

T¿o mßt sroted procedure (tt)

49

T¿o mßt sroted procedure (tt)

50 )

Thực thi stored procedure

Thực thi thÿ tục bằng lời gái thÿ tục có dạng:

Tên_thā_tÿc [danh_sách_các_đối_số]

Lời gái thÿ tục được thực hiện bên trong một thÿ

tục khác, bên trong một trigger hay kết hợp với các

câu lệnh SQL khác, sử dụng cú pháp :

EXECUTE tên_thā_tÿc [danh_sách_các_đối_số]

Hoặc

EXEC tên_thā_tÿc [danh_sách_các_đối_số]

51

Thực thi stored procedure (tt)

Cú pháp:EXEC[UTE] []

52

Số lượng các đối và thứ tự của chúng phải phù hợp với số

lượng và thứ tự của các tham số hình thức

Ví dụ 1: EXEC sp_NVP 2

Ví dụ 2: EXECUTE sp_LenDanhSachDiem

'CST005','Cơ sở dữ liệu',5, ‘CT002'

Nếu truyền các đối không theo thứ tự thì tất cả các đối

đều phải viết dưới dạng:

@tên_tham_số = giá_trị

Tham số trong stored procedure

Tham số nhập

#######  Là tham số phải truyền giá trß vào khi gái thÿ tục

Tham số xuất (giữ lại giá trß cÿa đối số sau

khi kết thúc thÿ tục)

#######  Là tham số nhận giá trß mà thÿ tục cần trả về,

#######  Cách xây dựng tham số xuất:

####### @ OUT[PUT]

Và trong lời gái thÿ tục, sau đối số được

truyền cho thÿ tục, bạn cũng phải chỉ đßnh

thêm từ khoá OUTPUT (hoặc OUT)

53

Tham số trong stored procedure (tt)

#######  CREATE PROCEDURE

####### spCong_hai_so (@a

####### INT,@b INT, @c INT OUT)

AS SELECT @c=@a+@b GO

#######  Và lời gái thực thi:

DECLARE @tong INT SELECT @tong=0 EXECUTE spCong_hai_so 100,200,@tong OUT Print @tong 54

####### Ví dÿ:

#######  CREATE PROCEDURE

####### spCong_hai_so (@a INT,@b

####### INT, @c INT)

AS SELECT @c=@a+@b GO

#######  Và lời gọi thực thi

####### DECLARE @tong INT

SELECT @tong=0 EXECUTE spCong_hai_so 100,200,@tong Print @tong

Tham số vßi giá trß mặc đßnh

Giá trß mặc đßnh sẽ được gán cho tham số trong

trường hợp không truyền đối số cho tham số khi

có lời gái đến thÿ tục.

Cú pháp:

#######  @tên_tham_số = giá_trß_mặc_đßnh

Ví dụ: Viết thÿ tục spTestDefault như sau:

55

Tham số vßi giá trß mặc đßnh (tt)

CREATE PROC spTestDefault(@tenlop NVARCHAR(30) =NULL , @noisinh NVARCHAR(100) ='Hu¿‘ ) AS IF @tenlop IS NULL SELECT hodem,ten FROM sinhvien INNER JOIN lop ON sinhvien=lop WHERE noisinh like ('%'+@noisinh) ELSE SELECT hodem,ten FROM sinhvien INNER JOIN lop ON sinhvien=lop WHERE noisinh like ('%'+@noisinh) AND tenlop=@tenlop GO 56

Tham số vßi giá trß mặc đßnh (tt)

Thực hiện các lời gái thÿ tục với các mục đích

khác nhau như sau:

#######  Cho biết há tên cÿa các sinh viên sinh tại Huế:

spTestDefault

#######  Cho biết há tên cÿa các sinh viên lớp dữ liệu 2 sinh tại

####### Huế:

spTestDefault @tenlop=N'dữ liệu 2' 57

Tham số vßi giá trß mặc đßnh (tt)

Thực hiện các lời gái thÿ tục với các mục đích

khác nhau như sau:

#######  Cho biết há tên cÿa các sinh viên sinh tại Quảng nam

spTestDefault @noisinh=N'Quảng nam'

#######  Cho biết há tên cÿa các sinh viên lớp đồ háa 3 sinh tại

####### Đà Nẵng:

spTestTefault @tenlop=N'Đồ háa 3', @noisinh=N'Đà Nẵng' 58

Tham số Table

59 Ví dÿ 1:

Tham số Table (tt)

Vấn đề:

#######  Āng dụng cho người dùng nhập đơn hàng trên Form

####### (bao gồm thông tin chung và thông tin riêng cÿa từng chi

####### tiết đơn hàng).

#######  Vì tính an ninh dữ liệu, āng dụng không thể trực tiếp

####### insert dữ liệu vào các table mà phải thông qua các

####### stored procedure (SP)

#######  Làm sao truyền dữ liệu trên form vào SP?

Giải pháp:

#######  Dùng SP có tham số là table (chỉ có từ phiên bản SQL

####### Server 2008)

60

Tham số Table (tt)

 Ví dÿ 2:

#######  Cho lược đồ dữ liệu như sau:

 Hoadon(MaHD,MaKH,NgayLap,TongTien)  CTHOADON(MaHD,MaSP,SoLuong,DonGiaBan,ThanhTie n)

#######  B°ßc 1: T¿o kiểu dữ lißu bÁng t¿m để chứa các đ¡n

####### hàng từ Form truyÁn xuống:

CREATE TYPE tbTamCTHD AS TABLE ( HD varchar(10), SP varchar(10), SL float, DG float, TT float ) 61

Tham số Table (tt)

#######  B°ßc 2: Vi¿t thā tÿc nh¿n tham số là kiểu dữ lißu

####### bÁng t¿m tbTamCTHD để đổ dữ lißu từ bÁng t¿m vào

####### bÁng thực sự tên CSDL :

CREATE PROC uspNhapHD (@MaHD varchar(10), @Makh varchar(10), @Ngay Date, @CT tbTamCTHD ReadOnly) AS Declare @tt float Set @tt = (Select Sum(SL*DG) From @CT) Insert Into HoaDon Values @MaHD, @MaKH, @Ngay, @tt Update @CT Set HD = @MaHD, TT = SL * DG Insert Into CTHOADON Select * From @CT GO 62

Tham số Table (tt)

 B°ßc 3: Sử dÿng thā tÿc vừa vi¿t

####### Declare @CT tbTamCTHD

####### Insert Into @CT Values (&)

####### Insert &

####### EXEC uspNhapHD 8HD0019, 8KH0019, 82015-8-319, @CT

####### GO

63

Thay đổi, xóa stored procedure

Thay đổi: Alter Proc

Xóa: Drop Proc

64

HÀM

Hàm là đối tượng cơ sở dữ liệu tương tự như

thÿ tục.

Điểm khác biệt giữa hàm và thÿ tục:

#######  Hàm trả về một giá trß thông qua tên hàm còn thÿ tục

####### thì không.

65

HÀM

Hàm gồm 3 lo¿i:

Hàm trả về một giá trß có kiểu cơ sở như

int, varchar, float, datetime, &

Hàm trả về một table (tương tự view),

thường trong làm là một câu truy vấn lấy

dữ liệu.

Hàm trả về một table được đßnh nghĩa cấu

trúc ngay trong hàm và dữ liệu có được

nhờ thực hiện một dãy các thao tác insert

66

HÀM TRÀ VÀ MÞT GIÁ TRÞ

CREATE FUNCTION tên_hàm ([danh_sách_tham_số])RETURNS (kiểu_cÿa_giá_trß_trả_về)ASBEGINThân hàmReturn END

67

Cú pháp

Ví dÿ

Hàm trÁ vÁ mßt giá trß

68

Ví dÿ

Hàm trÁ vÁ mßt giá trß

select dbo(‘27/09/2017') 69

CREATE FUNCTION thu (@ngay DATETIME) RETURNS NVARCHAR( 10 ) AS BEGIN DECLARE @tt NVARCHAR( 10 ) SELECT @tt= CASE DATEPART(DW,@ngay) WHEN 1 THEN N'Chủ nhật' WHEN 2 THEN N'Thứ hai' WHEN 3 THEN N'Thứ ba' WHEN 4 THEN N'Thứ tư' WHEN 5 THEN N'Thứ năm' WHEN 6 THEN N'Thứ sáu' ELSE N'Thứ bảy' END RETURN (@tt)/* Giá Trị trả về của hàm*/ END

Ví dÿ

Hàm trÁ vÁ mßt giá trß

Viết hàm tính số ngày cÿa một tháng.

70

Hàm trÁ vÁ mßt bÁng (t°¡ng tự view)

CREATE FUNCTION ([danh sách tham

số]) RETURNS TABLE

AS

RETURN (câu lệnh select)

71

####### Chú ý:

• Kiểu trả về cÿa hàm được chỉ đßnh bởi mệnh đề

####### RETURNS TABLE.

• Trong phần thân cÿa hàm chỉ có duy nhất một câu lệnh

####### RETURN xác đßnh giá trß trả về cÿa hàm thông qua duy

####### nhất một câu lệnh SELECT (không sử dụng bất kỳ câu

####### lệnh nào khác trong phần thân thân hàm)

Cú pháp

Ví dÿ

hàm trÁ vÁ mßt bÁng

72 CREATE FUNCTION FXemThongTinNCC(@dc nvarchar( 30 )) RETURNS TABLE AS RETURN SELECT cc, cc, cc, cc, ch, Soluong*Dongia as thanhtien FROM Chuyenhang ch join NhaCC cc On ch =cc WHERE cc=@dc gọi h‡m Select * From FXemThongTinncc('TP HCM') Khi gọi h‡m cÛ kißu trả vß l‡ 1 bảng ta d ̆ng c ̇ ph·p SELECT * FROM TÍn_H‡m

Ví dÿ

Hàm trÁ vÁ mßt bÁng

VD 2: Vi¿t hàm xem điểm trung bình cāa mßt sinh viên b¿t kỳ CREATE FUNCTION XEMDTB( @MaSV int) RETURNS TABLE AS RETURN SELECT student, StudentName, AVG(mark) AS DTB FROM Student join Mark on Student = Mark WHERE Mark=@MaSV GROUP BY student, StudentName  --GàI HÀM  SELECT * FROM dbo(4) 73