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


Hiểu và trình bày được các phương thức, thuộc tính của các đối tượng truy xuất cơ sở dữ liệu, cách thức truy xuất, duyệt nội dung cơ sở dữ liệu; các đối tượng hiển thị nội dung các bản ghi trong cơ sở dữ liệu; các cú pháp lệnh truy xuất cơ sở dữ liệu


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

Toán tử so sánh sql

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

Chia sẽ bài viết :


Bài viết liên quan

Progressive Web Apps là gì?

Web Apps Progressive là một cấp độ hoàn toàn mới của chăm sóc về chất lượng trải nghiệm người dùng đầu cuối của bạn. Các nghiên cứu khác cho thấy kết ...

Bài 2 Ngôn ngữ HTML

HTML (HyperText Markup Language) là một ngôn ngữ đánh dấu siêu văn. Trang HTML được lưu với phần mở rộng là .htm hoặc .html

Bài 3 Ngôn ngữ lập trình Javascript

Là một ngôn ngữ kịch bản (scripting language) dùng để tương tác với các trang HTML dựa trên đối tượng (object-based scripting language). Code của Java ...