Bài giảng Hệ quản trị cơ sở dữ liệu - Bài tập Stored Procedure

GVHDTH : Tiết Gia Hồng Quản trị CSDL 1 Stored Procedure I. Viết các thủ tục đơn giản (không truy xuất dữ liệu) 1. Viết stored-procedure in ra dòng: ‘Xin chào’ + @ten. *Chú ý: với @ten là tham số đầu vào là họ tên của bạn (viết tiếng việt). 2. Viết stored-procedure tính tổng 2 số a, b và in kết quả theo định dạng sau: ‘Tổng 2 số’ + @a + ‘và’ + @b ‘là:’ + @kq 3. Viết stored-procedure tính tích 2 số a, b và in kết quả theo định dạng sau: ‘Tích 2 số’ + @a + ‘và’ + @b ‘là:’ + @kq 4. Viết stored-procedure tính thương 2 số a, b và in kết quả theo định dạng sau: ‘Thương 2 số’ + @a + ‘và’ + @b ‘là:’ + @kq 5. Viết stored-procedure tìm số lớn nhất trong 3 số a, b, c và in kết quả theo định dạng sau: ‘Số lớn nhất trong 3 số’ + @a + @b + ‘và’ + @c ‘là:’ + @kq 6. Viết stored-procedure tìm số nhỏ nhất trong 3 số a, b, c và in kết quả theo định dạng sau: ‘Số nhỏ nhất trong 3 số’ + @a + @b + ‘và’ + @c ‘là:’ + @kq 7. Viết stored-procedure truyền vào số nguyên n in ra số lượng số chẳn và tổng các số chẳn. 8. Viết stored-procedure truyền vào 2 số nguyên, tìm ước chung lớn nhất của 2 số nguyên trên. 9. Viết stored-procedure truyền vào n, tính tổng các số nguyên thuộc [1, n] 10. Viết stored-procedure truyền vào n tính tổng các số chính phương thuộc [1, n]. II. Viết các thủ tục có truy xuất dữ liệu  Stored-procedure không có tham số 1. Viết stored-procedure xuất danh sách các sản phẩm chưa cung cấp mô tả. 2. Viết stored- procedure xuất danh sách các danh mục chưa có sản phẩm nào. 3. Viết stored- procedure xuất danh sách khách hàng có đơn đặt hàng chưa giao với số lượng sản phẩm mua > 1. 4. Viết stored-procedure xuất danh sách các khách hàng đã xem và chưa mua sản phẩm. GVHDTH : Tiết Gia Hồng Quản trị CSDL 2 5. Viết stored-procedure xuất danh sách các đơn hàng đã đặt quá 5 ngày mà chưa được giao. 6. Viết stored-procedure xuất danh sách các khách hàng mua trên 3 sản phẩm và có điểm tích lũy <5.  Stored-procedure có tham số vào 1. Viết stored-procedure truyền vào mã sản phẩm, xuất ra thông tin sản phẩm 2. Viết stored-procedure truyền vào tình trạng, xuất ra thông tin các danh mục thuộc tình trạng đó. 3. Viết stored-procedure truyền vào ngày bắt đầu, ngày kết thúc, xuất danh sách sản phẩm khuyến mãi trong khoảng thời gian trên.(Nếu không nhập ngày bắt đầu thì lấy ngày đầu tiên của tháng hiện hành, nếu ngày kết thúc không nhập thì lấy ngày hiện hành) 4. Viết stored-procedure truyền vào ngày bắt đầu, ngày kết thúc, xuất danh sách các đơn hàng trong khoảng thời gian trên. .(Nếu không nhập ngày bắt đầu thì lấy ngày đầu tiên của tháng hiện hành, nếu ngày kết thúc không nhập thì lấy ngày hiện hành) 5. Viết stored-procedure truyền vào tên khách hàng, xuất thông tin khách hàng, số đơn đặt hàng, tổng số sản phẩm mà khách hàng đã mua. 6. Viết stored-procedure truyền vào tên sản phẩm, xuất thông tin sản phẩm, số lượng sản phẩm đã bán và tổng doanh thu khi bán sản phẩm đó. 7. Viết stored-procedure phát sinh mã của quan hệ bất kì. (cho phép chèn vào dòng trống. Ví dụ: mã hiện có là 1,2,5 mã mới phát sinh là 3) 8. Viết stored-procedure truyền vào tên khách hàng và quận, xuất thông tin khách hàng, tổng trị giá đơn đặt hàng của các khách hàng có tên và quận thỏa điều kiện vào.  Stored-procedure có tham số vào và ra 1. Viết stored-procedure kiểm tra thông tin đăng nhập Input: tên đăng nhập, mật khẩu Output: 1: Đăng nhập thành công 2: Sai tên đăng nhập 3: Sai mật khẩu -1: Lỗi hệ thống 2. Viết stored-procedure thêm một sản phẩm mới Input: thông tin sản phẩm Output: 1: Thêm sản phẩm thành công 2: Mã sản phẩm đã tồn tại -1: Lỗi hệ thống GVHDTH : Tiết Gia Hồng Quản trị CSDL 3 3. Viết stored-procedure lấy thông tin đơn giá của sản phẩm Input: mã sản phẩm Output: 1: Lấy thông tin thành công. Trả về đơn giá sản phẩm 2: Sản phẩm không tồn tại -1: Lỗi hệ thống 4. Viết stored-procedure xóa thông tin sản phẩm Input: mã sản phẩm Output: 1: Xóa sản phẩm thành công 2: Sản phẩm không tồn tại 3: Sản phẩm đang bị tham chiếu -1: Lỗi hệ thống 5. Viết stored-procedure cập nhật trị giá hóa đơn Input: ngày bắt đầu, ngày kết thúc Output: danh sách các hóa đơn đã cập nhật trị giá 6. Viết stored-procedure phiếu đặt hàng Input: mã khách hàng Output: 1: Thêm thành công 2: Khách hàng không tồn tại -1: Thêm không thành công Nội dung:  Kiểm tra khách hàng tồn tại? o Nếu khách hàng không tồn tại  return 1. Thoát khỏi thủ tục.  Thêm phiếu đặt hàng o Mã đặt hàng tự phát sinh o Ngày đặt : ngày hiện hành o Trị giá sản phẩm : NULL o Tình trạng : đang chờ giao o Phí vận chuyển = lấy thông tin quận và dựa vào đặt tả để tính. 7. Viết stored-procedure thêm chi tiết đặt hàng Input: mã sản phẩm, số lượng, mã đơn hàng Output: 0: Số lượng sản phẩm yêu cầu mua không phù hợp. 1: Thêm thành công. In danh sách các hóa đơn đã cập nhật trị giá 2: Mã sản phẩm không tồn tại. 3: Số lượng sản phẩm không đủ cung cấp. GVHDTH : Tiết Gia Hồng Quản trị CSDL 4 4: Mã đơn hàng không tồn tại. -1: Thêm không thành công Nội dung:  Kiểm tra số lượng sản phẩm cần mua có >0? o Nếu số lượng <= 0  return 0. Thoát khỏi thủ tục.  Kiểm tra mã sản phẩm có tồn tại? o Nếu mã sản phẩm không tồn tại  return 2. Thoát khỏi thủ tục.  Kiểm tra số lượng sản phẩm có đủ cung cấp ko (số lượng tồn > số lượng)? o Nếu số sản phẩm không đủ cung cấp  return 3. Thoát khỏi thủ tục.  Kiểm tra mã đặt hàng có tồn tại? o Nếu mã đặt hàng không tồn tại  return 4. Thoát khỏi thủ tục.  Thêm chi tiết phiếu đặt hàng o Mã chi tiết phiếu đặt hàng tự phát sinh o Đơn giá: lấy từ bảng sản phẩm dựa vào mã sản phẩm.  Cập nhật lại trị giá hóa đơn TriGiaSp = TriGiaSp + Dongia*(100-phantramgiamgia)/100 * SoLuong 8. Viết stored-procedure đăng bán sản phẩm Input: mã sản phẩm, tên sản phẩm, mô tả, đơn giá, số lượng tồn, số lượng tối thiểu, mã danh mục Output: 1: Đăng bán thành công 2: Đơn giá <=0 3: Số lượng tồn <= 0 4: Số lượng tối thiểu <= 0 5: Danh mục không tồn tại -1: Đăng bán không thành công Nội dung:  Kiểm tra đơn giá >0? o Nếu đơn giá <=0  return 2. Thoát khỏi thủ tục  Kiểm tra số lượng tồn >0? o Nếu số lượng tồn <=0  return 3. Thoát khỏi thủ tục  Kiểm tra số lượng tối thiểu >0? o Nếu số lượng tối thiểu <=0  return 4. Thoát khỏi thủ tục  Kiểm tra mã danh mục có tồn tại không? o Nếu mã danh mục không tồn tại  return 5. Thoát khỏi thủ tục