Sharing & Linking: [Database-Tự Học SQL] COM2034_SQL Server - Lab 5: Stored Procedure Trong SQL Server

CÁC BƯỚC THỰC HIỆN STORED-PROCEDURE:

1. Khởi tạo
2. Biên dịch (chọn toàn bộ nội dung của stored-procedure -> nhấn F5)
3.Thực thi (EXEC procedure_name;)

LÝ THUYẾT:

Cú pháp tạo mới stored-procedure:

CREATE PROCEDURE procedure_name
AS
	BEGIN
		sql_statement  // khối lệnh cần xử lý
	END
GO;

Hoặc (chúng ta có thể viết đầy đủ PROCEDURE hoặc viết kiểu rút gọn PROC)

CREATE PROC procedure_name
AS
	BEGIN
		sql_statement  // khối lệnh cần xử lý
	END
GO;

Cú pháp sửa stored-procedure đã tồn tại:

ALTER PROCEDURE procedure_name
AS
	BEGIN
		sql_statement  // khối lệnh cần xử lý
	END
GO;

Cú pháp viết gộp stored-procedure:

CREATE OR ALTER PROCEDURE procedure_name // tạo mới stored-procudure, nếu đã tồn tại thì thay đổi nội dung stored-produre thành nội dung mới
AS
	BEGIN
		sql_statement  // khối lệnh cần xử lý
	END
GO;

Cú pháp xóa stored-procedure đã tồn tại:

DROP PROCEDURE procedure_name; // tên stored-procedure cần xóa

Cách thực thi stored-procedure:

EXEC procedure_name; // tên stored-procedure cần thực thi

Kiểm tra nội của stored-procedure đã tồn tại:

sp_helptext procedure_name; // tên stored-procedure cần kiểm tra

NỘI DUNG:

Bài 1:

Viết stored-procedure:

+ In ra dòng ‘Xin chào’ + @ten với @ten là tham số đầu vào là tên Tiếng Việt có dấu của bạn. Gợi ý:

- sử dụng UniKey để gõ Tiếng Việt ♦

- chuỗi unicode phải bắt đầu bởi N (vd: N’Tiếng Việt’) ♦

- dùng hàm cast (<biểuThức> as <kiểu>) để đổi thành kiểu <kiểu> của<biểuThức>.

+ Nhập vào 2 số @s1,@s2. In ra câu ‘Tổng là : @tg’ với @tg=@s1+@s2.

+ Nhập vào số nguyên @n. In ra tổng các số chẵn từ 1 đến @n.

+ Nhập vào 2 số. In ra ước chung lớn nhất của chúng theo gợi ý dưới đây:

- b1. Không mất tính tổng quát giả sử a <= A

- b2. Nếu A chia hết cho a thì : (a,A) = a ngược lại : (a,A) = (A%a,a) hoặc (a,A) = (a,A-a)

- b3. Lặp lại b1,b2 cho đến khi điều kiện trong b2 được thỏa

Bài 2:

Sử dụng cơ sở dữ liệu QLDA, Viết các Proc:

- Nhập vào @Manv, xuất thông tin các nhân viên theo @Manv.

- Nhập vào @MaDa (mã đề án), cho biết số lượng nhân viên tham gia đề án đó

- Nhập vào @MaDa và @Ddiem_DA (địa điểm đề án), cho biết số lượng nhân viên tham gia đề án có mã đề án là @MaDa và địa điểm đề án là @Ddiem_DA

- Nhập vào @Trphg (mã trưởng phòng), xuất thông tin các nhân viên có trưởng phòng là @Trphg và các nhân viên này không có thân nhân.

- Nhập vào @Manv và @Mapb, kiểm tra nhân viên có mã @Manv có thuộc phòng ban có mã @Mapb hay không

Bài 3:

Sử dụng cơ sở dữ liệu QLDA, Viết các Proc

- Thêm phòng ban có tên CNTT vào csdl QLDA, các giá trị được thêm vào dưới dạng tham số đầu vào, kiếm tra nếu trùng Maphg thì thông báo thêm thất bại.

- Cập nhật phòng ban có tên CNTT thành phòng IT.

- Thêm một nhân viên vào bảng NhanVien, tất cả giá trị đều truyền dưới dạng tham số đầu vào với điều kiện:

+ nhân viên này trực thuộc phòng IT

+ Nhận @luong làm tham số đầu vào cho cột Luong, nếu @luong<25000 thì nhân viên này do nhân viên có mã 009 quản lý, ngươc lại do nhân viên có mã 005 quản lý

+ Nếu là nhân viên nam thi nhân viên phải nằm trong độ tuổi 18-65, nếu là nhân viên nữ thì độ tuổi phải từ 18-60

THỰC HIỆN:

Bài 1:

Viết stored-procedure:

a. In ra dòng ‘Xin chào’ + @ten với @ten là tham số đầu vào là tên Tiếng Việt có dấu của bạn. Gợi ý:

- sử dụng UniKey để gõ Tiếng Việt ♦

- chuỗi unicode phải bắt đầu bởi N (vd: N’Tiếng Việt’) ♦

- dùng hàm cast (<biểuThức> as <kiểu>) để đổi thành kiểu <kiểu> của<biểuThức>.

Khởi tạo stored-procedure

CREATE PROC sp_Bai1a @ten NVARCHAR(50)
AS
BEGIN
	PRINT 'Xin chào ' + CAST(@ten AS NVARCHAR);
END	
GO

Ngoài ra chúng ta cũng có viết CREATE OR ALTER PROC .... có nghĩa rằng khi thực thi nó sẽ kiểm tra xem tên của stored-procedure có tồn tại chưa nếu không có thì tạo mới nếu đã có rồi thì cập nhật

Code:

CREATE OR ALTER PROC sp_Bai1a @ten NVARCHAR(50)
AS
BEGIN
	PRINT 'Xin chào ' + cast(@ten AS NVARCHAR);
END	
GO

Thực thi stored-procedure

EXEC sp_Bai1a 'tony Tèo';

b. Nhập vào 2 số @s1,@s2. In ra câu ‘Tổng là : @tg’ với @tg=@s1+@s2.

Code:

CREATE OR ALTER PROCEDURE sp_Sum @So1 INT = 5, @So2 INT = 4
AS
BEGIN
	DECLARE @tong INT;
	SET @tong = @So1 + @So2;
	PRINT 'Tong là: ' + cast(@tong AS NVARCHAR);
END
GO

Thực thi stored-procedure

EXEC sp_Sum // nếu không truyền đối số vào thì chương trình sẽ lấy giá trị đã khởi tạo của trong chương trình để thực hiện phép tính
EXEC sp_Sum 7,8;

c. Nhập vào số nguyên @n. In ra tổng các số chẵn từ 1 đến @n.

Code:

CREATE PROC sp_TongChan @n INT
AS
BEGIN
	DECLARE @tong INT = 0, @i INT = 1;
	WHILE (@i <= @n)
	BEGIN
		IF(@i % 2= 0)
		BEGIN
			SET @tong = @tong + @i
		END
		SET @i= @i +1
	END
	select @tong AS 'Tong chan'
END
GO

d. Nhập vào 2 số. In ra ước chung lớn nhất của chúng theo gợi ý dưới đây:

- b1. Không mất tính tổng quát giả sử a <= A

- b2. Nếu A chia hết cho a thì : (a,A) = a ngược lại : (a,A) = (A%a,a) hoặc (a,A) = (a,A-a)

- b3. Lặp lại b1,b2 cho đến khi điều kiện trong b2 được thỏa

Code:

CREATE PROC sp_UCLN @s3 INT,@s4 INT
AS
BEGIN
	
	WHILE (@s4 % @s3 != 0)
	BEGIN
		IF (@s4 > @s3)
		SET @s4 = @s4-@s3
		ELSE
		SET @s3 = @s3-@s4
	END
	PRINT N'Ước chung lớn nhất là ' + CAST(@s3 AS VARCHAR(10))
END
GO

Bài 2:

Sử dụng cơ sở dữ liệu QLDA, Viết các Proc:

- Nhập vào @Manv, xuất thông tin các nhân viên theo @Manv.

Code:

CREATE PROCEDURE sp_ThongtinNV @MaNV NVARCHAR(9)
AS
BEGIN
 SELECT * FROM NHANVIEN WHERE MaNV = @MaNV
END
GO

- Nhập vào @MaDa (mã đề án), cho biết số lượng nhân viên tham gia đề án đó

Code:

CREATE PROC sp_Bai2b @MaDa INT
AS
BEGIN
	SELECT MADA, count(*) AS 'SL nhan vien tg du an' 
	FROM PHANCONG
    WHERE MADA = @MaDa
	GROUP BY MADA
	
END
GO

- Nhập vào @MaDa và @Ddiem_DA (địa điểm đề án), cho biết số lượng nhân viên tham gia đề án có mã đề án là @MaDa và địa điểm đề án là @Ddiem_DA

Code:

CREATE PROC sp_Bai2c @MaDa INT, @Ddiem_DA NVARCHAR(15)
AS
BEGIN
	SELECT PHANCONG.MADA, count(*) AS 'SL'
	FROM DEAN, PHANCONG, CONGVIEC
	WHERE DEAN.MADA = CONGVIEC.MADA and
    	  PHANCONG.MADA = CONGVIEC.MADA and
		  DDIEM_DA = @Ddiem_DA	and
		  DEAN.MADA = @MaDa	
	GROUP BY PHANCONG.MADA
END
GO

- Nhập vào @Trphg (mã trưởng phòng), xuất thông tin các nhân viên có trưởng phòng là @Trphg và các nhân viên này không có thân nhân.

Code:

CREATE PROC sp_Bai2d @Trphg NVARCHAR(9)
AS
BEGIN 
	SELECT *
	FROM NHANVIEN
	WHERE MA_NQL = @Trphg and
	 NHANVIEN.MANV not in (SELECT MA_NVIEN
						   FROM THANNHAN
						   WHERE THANNHAN.MA_NVIEN = NHANVIEN.MANV)
END
GO

- Nhập vào @Manv và @Mapb, kiểm tra nhân viên có mã @Manv có thuộc phòng ban có mã @Mapb hay không

Code:

CREATE PROC sp_Bai2e @Manv NVARCHAR(9), @Mapb INT
AS
BEGIN
	IF exists (SELECT * FROM nhanvien WHERE phg= @Mapb and manv = @Manv)
		PRINT N'Có thuộc'
	ELSE
		PRINT N'Không Thuộc'
END
GO

Bài 3:

Sử dụng cơ sở dữ liệu QLDA, Viết các Proc

- Thêm phòng ban có tên CNTT vào csdl QLDA, các giá trị được thêm vào dưới dạng tham số đầu vào, kiếm tra nếu trùng Maphg thì thông báo thêm thất bại.

Code:

CREATE PROC sp_InsertPhongBan @TenPHG NVARCHAR(15),
	@MaPHG INT, @TRPHG NVARCHAR(9), @NG_NHANCHUC date
AS
BEGIN
	IF EXISTS(SELECT * FROM PHONGBAN WHERE MAPHG = @MaPHG)
		PRINT 'Da ton tai, khong them vao duoc';
	ELSE
		INSERT INTO PHONGBAN
		VALUES(@TenPHG, @MaPHG,@TRPHG,@NG_NHANCHUC)
END
GO

Thực thi stored-produre:

Code:

EXEC sp_InsertPhongBan 'Hanh Chinh',2,'007','2020-06-04'

- Cập nhật phòng ban có tên CNTT thành phòng IT.

Code:

CREATE PROC sp_phongbandoiten @tenphg NVARCHAR(30)
AS
BEGIN
	update phongban SET tenphg = @tenphg
	WHERE tenphg = N'Công Nghệ Thông Tin'
END
Exec sp_phongbandoiten 'IT'
GO

Thực thi stored-produre:

Code:

Exec sp_phongbandoiten 'IT'

- Thêm một nhân viên vào bảng NhanVien, tất cả giá trị đều truyền dưới dạng tham số đầu vào với điều kiện:

+ nhân viên này trực thuộc phòng IT

+ Nhận @luong làm tham số đầu vào cho cột Luong, nếu @luong<25000 thì nhân viên này do nhân viên có mã 009 quản lý, ngươc lại do nhân viên có mã 005 quản lý

+ Nếu là nhân viên nam thi nhân viên phải nằm trong độ tuổi 18-65, nếu là nhân viên nữ thì độ tuổi phải từ 18-60

Code:

CREATE PROC sp_InsertNhanVien
@honv NVARCHAR(15),@tenlotnv NVARCHAR(15), @tennv NVARCHAR(15),@manv NVARCHAR(9),
@ngsinh date,@dchi NVARCHAR(30), @phai NVARCHAR(3),@luong float, @ma_nql NVARCHAR(9), @phg INT
AS
BEGIN
	IF (@phg = (SELECT maphg FROM phongban WHERE tenphg ='IT'))
	BEGIN
		IF(@luong < 25000)
		BEGIN
			SET @ma_nql = '009'
			insert into nhanvien
			values(@honv,@tenlotnv,@tennv,@manv,@ngsinh,@dchi,@phai,@luong,@ma_nql,@phg)
		END
		ELSE
		BEGIN
			SET @ma_nql = '005'
			insert into nhanvien
			values(@honv,@tenlotnv,@tennv,@manv,@ngsinh,@dchi,@phai,@luong,@ma_nql,@phg)
		END
		IF (@phai = 'Nam')
		BEGIN
			IF(datedIFf(yy,@ngsinh,getdate()) between 18 and 65)
			BEGIN
				insert into nhanvien
				values(@honv,@tenlotnv,@tennv,@manv,@ngsinh,@dchi,@phai,@luong,@ma_nql,@phg)
			END
			ELSE
			PRINT N'Không thỏa'
		END
		ELSE
		BEGIN
			IF(datedIFf(yy,@ngsinh,getdate()) between 18 and 60)
			BEGIN
				insert into nhanvien
				values(@honv,@tenlotnv,@tennv,@manv,@ngsinh,@dchi,@phai,@luong,@ma_nql,@phg)
			END
			ELSE
			PRINT N'Không Thỏa'
		END
	END
	ELSE
	PRINT N'NHAN VIEN PHONG IT MOI THEM DUOC'
END
GO

Thực thi stored-produre:

Code:

Exec sp_InsertNhanVien N'Nguyễn',N'Văn',N'Tèo','010','1975-04-30','30 Lê Văn Sỹ','Nam',25000,1,6

Xong!

Sharing & Linking

Pages

[Database-Tự Học SQL] COM2034_SQL Server - Lab 5: Stored Procedure Trong SQL Server

Để có những bài viết tốt hơn, hãy cho chúng tôi bình luận của bạn về bài viết này. Cám ơn các bạn!

No comments:

Post a Comment