Bài 5 Lập trình với CSDL

Biến cục bộ

Khai báo biến cục bộ

DECLARE @Tên_biến Kiểu_dữ_liệu [, ...] 
Ví dụ:
DECLARE @Tongsldat INT, @Hotenncc CHAR(50)
DECLARE @Ngayxh DATETIME

Dùng để lưu trữ các giá trị tạm thời trong quá trình tính toán

Biến phải có kiểu dữ liệu
Biến muốn sử dụng trong một batch phải khai báo trước

Gán giá trị cho biến Sử dụng lệnh SET hoặc SELECT

SET @Biến = Giá_trị
SET @a = 5
Select @Biến = Tên_Cột From Tên_Bảng
Select @ConLai = TonCuoiKy From TonKho Where MaVTu = 'VT010' And NamThang = '200402'

Xem giá trӏ hiện hành của biến

Khi có kết hợp với chuỗi, phải đổi kiểu dữ liệu sang kiểu chuỗi bằng hàm CAST hay CONVERT

Print 'Giá trị của @A ' + cast(@A as char(4))

Phạm vi hoạt động của biến: Một biến chỉ có phạm vi hoạt động cục bộ

Trong một Batch
Trong một Stored Procedure hay Trigger

DECLARE @Ngaydhgn DATETIME
SELECT @Ngaydhgn=MAX(NGAYDH)
FROM DONDH
GO

PRINT 'Ngày đặt hàng gần nhất: ' + CONVERT(CHAR(12),@Ngaydhgn)
GO

Biến hệ thống

Ý nghĩa sử dụng

Cung cấp các thông tin hệ thống

Phiên bản SQL Server
Số dòng dữ liệu vừa được xử lý bởi câu lệnh
Mã lỗi
Số lượng kết nối
Tình trạng cursor

Không cần khai báo

Biến do SQL Server định sẵn
Tên bắt đầu bởi @@

Một vài biến hệ thống thường dùng

@@RowCount

Tổng số mẩu tin được tác động của câu lệnh truy vấn gần nhất.

@@Error

Số mã lỗi của câu lệnh thực hiện gần nhất
Khi một câu lệnh thực hiện thành công thì giá trị là 0.

@@Fetch_Status

Trạng thái của việc đọc dữ liệu trong bảng theo cơ chế từng mẩu tin (cursor).
Khi đọc dữ liệu của mẩu tin thành công thì giá trị là 0.

Các toán tử

Toán tử số học

Toán tử số hóa sql

SELECT 'Hello' + ' ' + 'The World!'
SELECT 'Ngày đặt hàng D007 là: ' + CAST(NGAYDH AS CHAR(11))
FROM DONDH
WHERE SODH='D007'

Toán tử so sánh

SELECT * FROM VATTU
WHERE
  (DVTINH='Bộ' AND PHANTRAM>10) 
  OR
  (DVTINH='Cái' AND PHANTRAM>20)

Cấu trúc điều khiển

Cấu trúc rẽ nhánh IF...ELSE

IF Biểu_thức_luận_lý
   Câu_lệnh1 | Khối_lệnh1
[ELSE
   Câu_lệnh2 | Khối_lệnh2 ]

IF (SELECT COUNT(*) FROM CTPXUAT WHERE SLXUAT>4) > 0
BEGIN
    PRINT 'Danh sách các hàng hóa bán với số lượng > 4'
    SELECT CTPX.MAVTU, TENVTU, SLXUAT
       FROM CTPXUAT CTPX INNER JOIN VATTU VT
            ON VT.MAVTU=CTPX.MAVTU
       WHERE SLXUAT>4
END
ELSE
 PRINT 'Chưa bán hàng hóa nào với số lượng >4'

Cú pháp If Exists

IF EXISTS (Câu_lệnh_SELECT)
   Câu_lệnh1 | Khối_lệnh1
[ELSE
   Câu_lệnh2 | Khối_lệnh2]

IF EXISTS (SELECT * FROM CTPXUAT WHERE SLXUAT>4)
BEGIN
    PRINT 'Danh sách các hàng hóa bán với số lượng > 4'
    SELECT CTPX.MAVTU, TENVTU, SLXUAT
       FROM CTPXUAT CTPX INNER JOIN VATTU VT ON VT.MAVTU=CTPX.MAVTU
       WHERE SLXUAT>4
END
ELSE
 PRINT 'Chưa bán hàng hóa nào với số lượng >4'

Cấu trúc lặp WHILE

WHILE Biểu_thức_luận_lý
BEGIN
   Các_lệnh_lặp
END

DECLARE @Songuyen INT
SET @Songuyen=100
WHILE (@Songuyen<110)
BEGIN
   PRINT 'Số nguyên : ' + CONVERT(CHAR(3), @Songuyen)
   SET @Songuyen = @Songuyen + 1
END

Khái niệm về cursor

Các lệnh của SQL Server làm việc trên một nhóm nhiều mẩu tin
Cursor là cấu trúc giúp làm việc từng mẩu tin tҥi một thời điểm

Khai báo cursor như một câu lệnh SELECT
Có thể di chuyển giữa các mẩu tin trong cursor để làm việc
Có thể dùng cursor để cập nhật dữ liệu (Update, Delete)

Các bước sử dụng kiểu dữ liệu cursor

Định nghĩa biến kiểu cursor bằng lệnh DECLARE

Có hai loại cursor: Local, Global
Cách di chuyển mẩu tin trong cursor: Forward only, scroll
Cách quản lý dữ liệu của cursor: static, dynamic, keyset

Sử dụng lệnh OPEN để mở ra cursor đã định nghĩa trước đó
Đọc và xử lý trên từng dòng dữ liệu bên trong cursor

Sử dụng biến @@Fetch_status  Các lệnh Fetch và cấu trúc while

Đóng cursor lại bằng lệnh CLOSE và DEALLOCATE

Sau khi close, có thể mở lại
Deallocate: hủy cursor khỏi bộ nhớ

Cú pháp Declare

DECLARE Tên_cursor CURSOR
[LOCAL | GLOBAL]
[FORWARD_ONLY | SCROLL]
[STATIC | DYNAMIC | KEYSET]
[READ_ONLY | SCROLL_LOCK]
FOR Câu_lệnh_SELECT
[FOR UPDATE [OF Danh_sách_cột_cập_nhật]]

DECLARE cur_Vattu CURSOR
DYNAMIC
FOR
   SELECT * FROM VATTU

Cú pháp Open

DECLARE cur_Vattu CURSOR
DYNAMIC
FOR
   SELECT * FROM VATTU
OPEN cur_Vattu

Cú pháp FETCH

Absolute n: Đọc dòng thứ n trong cursor
Relative n: Đọc dòng thứ n kể từ vị trí hiện hành

FETCH [NEXT | PRIOR | FIRST | LAST
   | ABSOLUTE n | RELATIVE n]
    FROM Tên_cursor
    [INTO Danh_sách_biến]

Ví dụ hoàn chỉnh

--1. Khai báo biến cursor
DECLARE cur_Vattu CURSOR KEYSET
FOR SELECT * FROM VATTU
    WHERE MAVTU LIKE 'TV%'
    ORDER BY MAVTU

--2. Mở cursor
OPEN cur_Vattu

--3. Đọc dữ liệu
FETCH NEXT FROM cur_Vattu
WHILE @@FETCH_STATUS = 0
BEGIN
 -- Xử lý dòng mới vừa đọc được
 -- Thực hiện đọc tiếp các dòng kế
 FETCH NEXT FROM cur_Vattu
END

--4. Đóng cursor
CLOSE cur_Vattu
DEALLOCATE cur_Vattu

Các hàm chuyển đổi kiểu dữ liệu

Một hàm của SQL Server có thể sử dụng ở bất cứ đâu thay cho một giá trị cụ thể

Đổi một số thành chuỗi

STR (Số_thực, Số_ký_tự [, Số_lẻ])

Đổi kiểu dữ liệu

CAST (Biểu_thức AS Kiểu_dữ_liệu)

Đổi kiểu dữ liệu và định dạng

CONVERT (Kiểu_dữ_liệu, Biểu_thức [, Định_dạng])

Một số định dạng chuỗi ngày thông dụng

Một số định dạng ngày thông dụng

Các hàm ngày giờ

Cộng ngày

DATEADD (Đơn_vị, Con_số, Ngày_chỉ_định)

So sánh hai biến ngày

DATEDIFF (Đơn_vị, Ngày1, Ngày2)

Lấy tên ngày, tháng, năm

DATENAME (Đơn_vị, Ngày)

Thời điểm hiện hành

GETDATE()

Lấy một thành phần ngày, giờ trong biến ngày

DATEPART (Đơn_vị, Ngày)

Lấy ngày, tháng, năm của biến ngày

DAY (Ngày)
MONTH (Ngày)
YEAR (Ngày)

Đơn vị ngày trong sql

Các hàm toán học

Lấy trị tuyệt đối

ABS (Biểu_thức_số)

Hằng số Pi

PI()

Luỹ thừa

POWER (Biểu_thức_số, Số_mũ)

Lấy số ngẫu nhiên

RAND ([Số_nguồn])

Làm tròn số

ROUND (Biểu_thức_số, Vtrí_làm_tròn)

Dấu của kết quả biểu thức : SIGN (Biểu_thức_số)
Lấy căn bậc 2 : SQRT (Biểu_thức_số)

Các hàm xử lý chuỗi

Hàm viết hoa, thường

UPPER (Chuỗi), LOWER (Chuỗi)

Hàm cắt chuỗi

LEFT (Chuỗi nguồn, Số_ktự), RIGHT (Chuỗi nguồn, Số_ktự)
SUBSTRING (Chuỗi nguồn,Vị_trí,Số_ktự)

Hàm cắt khoảng trắng, tҥo chuỗi khoảng trắng

LTRIM (Chuỗi), RTRIM (Chuỗi), SPACE (N)

Hàm tҥo chuỗi lặp

REPLICATE (Chuỗi_lặp, N)

Chiều dài chuỗi

LEN (Chuỗi)

Đảo chuỗi

REVERSE (Chuỗi)

Tìm và thay thế chuỗi

REPLACE (Chuỗi nguồn, Chuỗi_tìm, Chuỗi_thay_thế)

Đổi từ số thành ký tự và ngược lại

CHAR (Số) , ASCII(Ký_tự)

Xử lý lỗi TRY…CATCH

Ý nghĩa : Thực hiện các lệnh trong khối TRY, nếu gặp lỗi sẽ chuyển qua xử lý bằng các lệnh trong khối CATCH
Cú pháp :

BEGIN TRY
   { các câu lệnh }
END TRY
BEGIN CATCH
   { các câu lệnh}
END CATCH

Các điểm cần lưu ý:

TRY và CATCH phải cùng lô xử lý
Sau khối TRY phải là khối CATCH
Có thể lồng nhiều cấp

Ví dụ

BEGIN TRY
   SELECT * FROM BangKhongTonTai;
END TRY
BEGIN CATCH
   SELECT
     ERROR_NUMBER() as ErrorNumber,
     ERROR_MESSAGE() as ErrorMessage;
END CATCH

Một số hàm ERROR thường dùng

ERROR_NUMBER() : Trả về mã số của lỗi
ERROR_MESSAGE() Trả về chuỗi lỗi
ERROR_SEVERITY() returns the error severity.
ERROR_STATE() returns the error state number.
ERROR_LINE() : Trả về dòng gây ra lỗi
ERROR_PROCEDURE() Trả về tên thủ tục/ trigger gây ra lỗi

Thủ tục RAISERROR

Ý nghĩa : Trả thông báo lỗi về cho ứng dụng
Cú pháp :

Raiserror(tbao_loi, muc_do, trang_thai [, cac_tham_so] )

Trong đó:

tbao_loi :

mã thông báo lỗi do người dùng định nghĩa trước bằng sp_addmessage và được lưu trong sys.messages. Giá trị phải lớn hơn 50000.
chuỗi thông báo lỗi bất kỳ.

muc_do:

Số có giá trị từ 0
25 thể hiện mức độ nghiêm trọng của lỗi.

trang_thai: Số từ 1-127 để xác định vị trí lỗi khi sử dụng cùng một tbao_loi tại nhiều điểm khác nhau
cac_tham_so : Hỗ trợ cho các tbao_loi cần tham số

Ví dụ sử dụng RAISERROR

IF @nPhanTram NOT BETWEEN 0 AND 100
BEGIN
  SET @sErrMsg = N'Tỉ lệ phầm trăm phải nằm trong đoạn [0,100]'
  RAISERROR(@sErrMsg, 16, 1)
  RETURN
END

Sử dụng cấu trúc điều khiển

Cho biết đơn giá xuất trung bình của hàng hóa “Đầu DVD Hitachi 1 đĩa” trong bảng CTPXUAT hiện giờ là bao nhiêu? Nếu lớn hơn 3,800,000 thì in ra “không nên thay đổi giá bán”, ngược lại in ra “đã đến lúc tăng giá bán”

--Cách 1
IF    (
        SELECT avg(c.dgxuat)
        FROM ctpxuat c INNER JOIN  vattu v ON v.mavtu = c.mavtu
        WHERE v.tenvtu=N'Đầu DVD Hitachi 1 đĩa'
        GROUP BY c.mavtu,v.tenvtu
    ) > =3800000
BEGIN
    print 'không nên thay dổi giá bán'
END
ELSE
BEGIN
    print 'đã đến lúc tăng giá bán'
END

--Cách 2
SELECT c.mavtu,v.tenvtu,avg(c.dgxuat)AS 'DG trungbinh',(
            CASE
                WHEN AVG(c.dgxuat)> =3800000
                    THEN 'không nên thay dổi giá bán'
                ELSE
                    'đã đến lúc tăng giá bán'
            END
            ) AS 'Ghi Chu'
FROM ctpxuat c INNER JOIN  vattu v ON v.mavtu = c.mavtu
WHERE v.tenvtu=N'Đầu DVD Hitachi 1 đĩa'
GROUP BY c.mavtu,v.tenvtu
GO

Sử dụng hàm DATENAME để tính xem có đơn đặt hàng nào đã được lập vào ngày chủ nhật không? Nếu có thì in ra danh sách các đơn đặt hàng đó, ngược lại thì in ra chuỗi “Ngày lập các đơn đặt hàng đều là hợp lệ”

--Cach 1
DECLARE @SoNgay  INT

SELECT @SoNgay=COUNT(d.ngaydh)
FROM dondh d
WHERE DATENAME(dw,d.ngaydh)='sunday'

IF @SoNgay>0
BEGIN
    SELECT d.sodh, d.ngaydh, d.manhacc
    FROM dondh d
    WHERE DATENAME(dw,d.ngaydh)='sunday'
END
ELSE
    PRINT 'Ngày lập các đơn đặt hàng đều hợp lệ'
GO

--Cach 2
IF EXISTS (SELECT * FROM dondh d WHERE DATENAME(dw,d.ngaydh)='sunday')
    SELECT * FROM dondh d WHERE DATENAME(dw,d.ngaydh)='sunday'
ELSE
    PRINT N'Các hóa đơn đặt hàng có ngày hợp lệ'
GO

Hãy cho biết đã có bao nhiêu phiếu nhập hàng cho đơn đặt hàng D001, nếu có thì in ra “Có xx số phiếu nhập hàng cho đơn đặt hàng D001”, ngược lại thì in ra “Chưa có nhập hàng nào cho D001”

DECLARE @SoPhieuDH INT
DECLARE @TenDH VARCHAR(4)

SET @TenDH='D001'

SET @SoPhieuDH=(
    SELECT COUNT(p.sopn)
    FROM pnhap p
    WHERE p.sodh=@TenDH
)

IF @SoPhieuDH>0
    PRINT N'Có '+CAST(@SoPhieuDH AS VARCHAR(3))+ N' số phiếu nhập hàng cho dơn đặt hàng D001'
ELSE
    PRINT 'Chưa có đơn dặt hàng nào cho D001'
GO

Hãy cho biết đơn đặt hàng D001 đã có nhập đủ hàng chưa, nếu có thì in ra “Đã nhập đủ hàng cho đơn đặt hàng D001”, ngược lại thì in ra “

Chưa nhập đủ hàng cho đơn đặt hàng D001” Hãy cho biết vật tư TL90 đã có đặt hàng trong tháng 2/2009 chưa, nếu có thì in ra “Đã có đặt hàng với tổng số lượng đặt là xxx”, ngược lại thì in ra “Chưa có đặt hàng”

DECLARE @TongDonDH INT
DECLARE @MaVattu VARCHAR(4)
DECLARE @Thang INT
DECLARE @Nam INT
SET @MaVattu='TL90'
SET @Thang=2
SET @Nam=2009

IF EXISTS(
                    SELECT *
                    FROM ctdondh c INNER JOIN dondh d ON d.sodh = c.sodh
                    WHERE c.mavtu=@MaVattu AND MONTH (d.ngaydh)=@Thang AND YEAR(d.ngaydh)=@Nam
)
BEGIN
    SET @TongDonDH=(
                    SELECT SUM(c.sldat)
                    FROM ctdondh c INNER JOIN dondh d ON d.sodh = c.sodh
                    WHERE c.mavtu=@MaVattu AND MONTH (d.ngaydh)=@Thang AND YEAR(d.ngaydh)=@Nam
                    )
    PRINT N'Đã có đặt hàng với tổng số lựong dặt là '+CAST(@TongDonDH AS CHAR(3))
END
ELSE
    PRINT N'Chưa có đặt hàng'
GO

Liệt kê danh sách các đơn đặt hàng trong bảng DONDH có bổ sung thêm cột hiển thị thứ trong tuần (bằng tiếng Việt) của ngày đặt hàng

SELECT dh.*, 'Thứ' =
            (
                CASE DATENAME(dw,dh.ngaydh)
                    WHEN 'MONDAY'
                        THEN N'Thứ Hai'
                    WHEN 'TUESDAY'
                        THEN N'Thứ Ba'
                    WHEN 'WEDNESDAY'
                        THEN N'Thứ Tư'
                    WHEN 'THURSDAY'
                        THEN N'Thứ Năm'
                    WHEN 'FRIDAY'
                        THEN N'Thứ Sáu'
                    WHEN 'SATURDAY'
                        THEN N'Thứ Bảy'
                    ELSE
                        N'Chủ Nhật'
                END
            )
FROM dondh AS dh

Tính tổng các số nguyên từ 1 đến 100

DECLARE @Songuyen INT
DECLARE @Tong INT
SET @Tong = 0
SET @Songuyen = 0
while (@Songuyen <= 100)
BEGIN
    SET @Tong = @Tong + @Songuyen
    SET @Songuyen = @Songuyen +1
END
print @Tong

Tính tổng chẵn và tổng lẻ của các số nguyên từ 1 đến 100

DECLARE @Songuyen INT
DECLARE @TongChan INT
DECLARE @TongLe INT
SET @TongChan = 0
SET @TongLe = 0
SET @Songuyen = 0
while (@Songuyen <= 100)
BEGIN
    IF(@Songuyen % 2 = 0)
        BEGIN
            SET @TongChan = @TongChan + @Songuyen
        END
    ELSE
        BEGIN
            SET @TongLe = @TongLe + @Songuyen
        END
    SET @Songuyen = @Songuyen +1
END
print @TongChan
print @TongLe

Duyệt cursor và xử lý Hiển thị danh sách các thông tin trong bảng DONDH có thêm cột tổng số phiếu nhập

DECLARE DonDH_cur CURSOR
FOR
    SELECT dd.sodh, dd.ngaydh, dd.manhacc,COUNT(p.sodh)AS 'tongphieunhap'
    FROM DonDH dd LEFT JOIN PNhap p ON p.sodh=dd.sodh
    GROUP BY dd.sodh,dd.ngaydh,dd.manhacc

DECLARE @sodh NCHAR(4), @ngaydh datetime, @manhacc NCHAR(3), @slnhap INT

OPEN DonDH_cur
FETCH NEXT FROM DonDH_cur INTO @sodh,@ngaydh,@manhacc,@slnhap
WHILE 0=0
BEGIN
  
    IF @@FETCH_STATUS<>0
        BREAK
    ELSE
    BEGIN
        PRINT @sodh+'  '+CAST(@ngaydh AS CHAR(6))+'  '+@manhacc+'  '+CAST(@slnhap AS CHAR)
    END
    FETCH NEXT FROM DonDH_cur INTO @sodh,@ngaydh,@manhacc,@slnhap
END

CLOSE DonDH_cur

DEALLOCATE DonDH_cur

GO

Bài 5 Lập trình với CSDL

Biến cục bộ

Biến hệ thống

Các toán tử

Cấu trúc điều khiển

Khái niệm về cursor

Các bước sử dụng kiểu dữ liệu cursor

Các hàm chuyển đổi kiểu dữ liệu

Các hàm toán học

Các hàm xử lý chuỗi

Xử lý lỗi TRY…CATCH

Thủ tục RAISERROR

Sử dụng cấu trúc điều khiển

Progressive Web Apps là gì?

Lợi ích của https cho website của bạn và hướng dẫn cài đặt https

Npm là gì ? Npm giúp gì trong dự án của bạn ?

Bài 1 Tổng quan về lập trình web

Bài 2 Ngôn ngữ HTML

Bài 5 Lập trình với CSDL

Biến cục bộ

Biến hệ thống

Các toán tử

Cấu trúc điều khiển

Khái niệm về cursor

Các bước sử dụng kiểu dữ liệu cursor

Các hàm chuyển đổi kiểu dữ liệu

Các hàm toán học

Các hàm xử lý chuỗi

Xử lý lỗi TRY…CATCH

Thủ tục RAISERROR

Sử dụng cấu trúc điều khiển

Chia sẽ bài viết :

Progressive Web Apps là gì?

Lợi ích của https cho website của bạn và hướng dẫn cài đặt https

Npm là gì ? Npm giúp gì trong dự án của bạn ?

Bài 1 Tổng quan về lập trình web

Bài 2 Ngôn ngữ HTML