HÀM - THỦ TỤC - TRINGGER

Ngày đăng: 17/11/2020 - Cập nhật: 17/11/2020

Mục Lục

1. Hàm trong Quản trị SQL
2. Tìm hiểu về thủ tục trong SQL
3. Tìm hiểu về Trigger
Tổng kết

Khi code với các ngôn ngữ lập trình, chúng ta hãy viết các hàm để xử lý các vấn đề nào đó. Nhưng nói đến hàm trong SQL nghe có vẻ xa lạ quá nhỉ?

Chưa hết đâu, trong SQL không chỉ có hàm mà còn cả thủ tục và trigger nữa … Bạn có muốn tìm hiểu qua một lần không?

1. Hàm trong Quản trị SQL

Khi lập trình với các ngôn ngữ, chúng ta thường xây dựng các hàm để thực hiện các công việc có tính lặp đi lặp lại nhằm rút ngắn thời gian và thuận tiện cho quá trình làm việc.

Với SQL, các hệ quản trị hầu hết đều có hỗ trợ hàm như trong các ngôn ngữ lập trình khác để giải quyết vấn đề này.

Với Oracle, hàm có cú pháp như sau:

CREATE [OR REPLACE] FUNCTION <tên hàm>
   [ (tham số 1[, tham số 2], [, ...]) ]
RETURN <kiểu dữ kiệu trả về>
IS | AS
[các khai báo]
BEGIN
   [câu lệnh thực thi]
[EXCEPTION   <câu lệnh thực thi>]
END [tên hàm];

Ví dụ: tạo hàm lấy điểm trung bình của sinh viên từ một bảng Điểm có sẵn

		
		CREATE or REPLACE function get_dtb(masv) return diem.mamh%type

		IS

		    dtb  number;

		BEGIN

		    dtb:= 0;

		    for mamh in

		        (SELECT * FROM Diem WHERE mamh = maMon)

		    loop

		        dtb := dtb + diem.diemlan1;

		    end loop;

		    return dtb;

		END get_dtb;

Để sử dụng hàm get_dtb, ta chỉ cần truyền vào tham số là mã sinh viên mà ta muốn xem điểm

Cách 1:

	
	SELECT get_dtb('B1900') FROM DIEM;

Cách 2:

		
		DECLARE    

		   dtb number(3);    

		BEGIN    

		   dtb := get_dtb('B1900');    

		   dbms_output.put_line('Điểm trung bình: ' || dtb);    

		END;

Để thực hiện được quyền tạo hàm, người dùng phải được cấp quyền này bởi DBA trong hệ thống. Một hàm đã được tạo ra thì hoàn toàn có thể xoá đi.

Cú pháp xóa hàm trong SQL:

DROP FUNCTION <tên hàm>

Ví dụ: xoá hàm get_dtb vừa tạo

	
	DROP FUNCTION get_dtb;

2. Tìm hiểu về thủ tục trong SQL

Thủ tục và hàm tính chất cũng tương tự nhau. Nếu bạn nắm được cách tạo – sử dụng FUNCTION trong SQL thì làm việc với PROCEDURE cũng không phải là chuyện khó khăn gì.

Thủ tục là một nhóm các câu lệnh PL / SQL được viết ra theo một cú pháp đặc thù – có thể sử dụng bằng cách gọi chính tên thủ tục đã đặt.

Cú pháp thủ tục trong SQL:

CREATE [OR REPLACE] PROCEDURE <tên thủ tục>
    [ (tham số 1[, tham số 2] [ ,...]) ]
IS
    [các khai báo]
BEGIN
    <các lệnh thực thi>
[EXCEPTION
    <các lệnh thực thi>]
END [tên thủ tục];

Có ba loại thủ tục cần xác định trước khi khởi tạo:

IN: Là một tham số mặc định. Các giá trị được chuyển tự động cho chương trình con

OUT: Phải được chỉ định - trả về một giá trị tương ứng cho lời gọi.

IN OUT: Phải được chỉ định - chuyển một giá trị ban đầu cho chương trình con và trả về một giá trị được cập nhật tương ứng cho lời gọi.

Ví dụ: tạo thủ tục tìm kiếm mã môn học trong bảng MONHOC có sẵn

		
		CREATE or REPLACE PROCEDURE TIMKIEM(mamon monhoc.mamh%type)

		IS

		BEGIN

		  select mamh tenmh, sotc

		  where mamh = mamon;

		  exception

		    when no_data_found then

		      dbms_output.put_line('không tìm thấy');

		    when others then

		      dbms_output.put_line('Không xác định được lỗi gì =))');

		END;

		-- Thực thi thủ tục TIMKIEM

		DECLARE

		    mamon monhoc.mamh%TYPE;

		    tenmon monhoc.tenmh%TYPE;

		    sotinchi monhoc.sotc%TYPE;

		BEGIN

		    TIMKIEM('&Nhap_ma_mon_hoc', mamon);

		    dbms_output.put_line(

		        'ten mon:' || to_char(tenmon) ||

		        ' so tc: ' || to_char(sotincchi) ||

		    );

		END;

Tương tự như FUNCTION, PROCEDURE cũng có thể xoá sau khi tạo.

Cú pháp xóa thủ tục trong SQL:

DROP PROCEDURE <tên thủ tục>;

Ví dụ: Xoá thủ tục TIMKIEM ở ví dụ trên

	
	DROP PROCEDURE TIMKIEM;

3. Tìm hiểu về Trigger

Đối với việc quản trị CSDL thì Trigger là một thứ gì đó hay ho và thú vị vô cùng.

Bạn có thể tưởng tượng rằng khi thực hiện một truy vấn INSERT, UPDATE, DELETE sẽ có một “thủ tục ngầm” theo dõi – cho phép - hoặc ngăn chặn các truy vấn đó thực thi -> các thủ tục ngầm đó được gọi là Trigger – thứ mà bạn sẽ không bao giờ được nhìn thấy nếu là một người dùng bình thường hoặc một lập trình viên chỉ được phép truy vấn dữ liệu.

Trigger có 6 loại tuỳ thuộc vào mục đích mà chúng được tạo ra:

BEFORE INSERT TRIGGER

AFTER INSERT TRIGGER

BEFORE UPDATE TRIGGER

AFTER UPDATE TRIGGER

BEFORE DELETE TRIGGER

AFTER DELETE TRIGGER

Cú pháp TRINGGER trong SQL:

CREATE [ OR REPLACE ] TRIGGER <tên trigger>
BEFORE [ | AFTER] INSERT or UPDATE or DELETE
ON <tên bảng>
[ FOR EACH ROW ]
DECLARE
   -- khai báo biến
BEGIN
   -- lệnh trigger
EXCEPTION
   WHEN ...
   -- xử lý ngoại lệ
END;

Ví dụ: Tạo một TRINGGER sau khi cập nhật TENHEDT từ bảng HEDT có sẵn.

		
		CREATE TABLE temp(

		    user_name nvarchar2(20),

		    modify_time date,

		    ma nvarchar2(5),

		    tencu nvarchar2(50),

		    tenmoi nvarchar2(50)

		);

		CREATE OR REPLACE trigger temp

		AFTER UPDATE of tenhedt ON hedt

		FOR EACH ROW

		INSERT INTO temp(user_name, modify_time, ma, tencu, tenmoi)

		VALUES (User, Sysdate, :new.mahedt, :old.tenhedt, :new.tenhedt);

		END;

		SELECT * FROM temp;

		SELECT * FROM hedt;

		UPDATE sv_manager.hedt

		-- sv_manager là tên schema

		SET tenhedt = 'Đại học Chính quy'

		WHERE mahedt = 'DH2020';

Trong ví dụ trên, bảng TEMP được tạo ra để lưu các giá trị mà bạn muốn “theo dõi”. Khi trường TENHEDT của bảng HEDT được cập nhật, thì các trường như user_name, modify_time, ma, tencu, tenmoi sẽ được lưu vào bảng TEMP.

Việc lưu trữ này thực sự rất cần thiết cho DBA để quản lý mọi hành vi của người dùng liên quan đến CSDL.

5 loại Trigger còn lại có tính chất và cách thực hiện tương tự. Bạn có thể tìm hiểu và nghiên cứu sâu thêm về nó nếu có đam mê hoặc thấy nó quá thí vị.

Một trigger khi không còn đáp ứng các nhu cầu theo dõi có thể bị xoá đi tương tự như FUNCTION và PROCEDURE.

Cú pháp xóa TRINGGER trong SQL:

DROP TRIGGER <tên trigger>;

Ví dụ: Xoá trigger temp của ví dụ bên trên.

	
	DROP TRIGGER temp;

Tổng kết

Công việc quản trị CSDL càng đi sâu càng thấy đau đầu và cực kỳ áp lực – bù lại được cái lương cực kỳ cao.

Những gì mình chia sẻ trong Series HỌC LẬP TRÌNH SQL này chỉ là bảng lề để bạn làm quen với CSDL, mong rằng nó có thể giúp bạn khai phá được những điều mới mẻ - những tiềm năng và hứng thú khác trong lập trình nói riêng và SQL nói chung.

---

HỌC VIỆN ĐÀO TẠO CNTT NIIT - ICT HÀ NỘI

Học Lập trình chất lượng cao (Since 2002). Học làm Lập trình viên. Hành động ngay!

Đc: Tầng 3, 25T2, N05, Nguyễn Thị Thập, Cầu Giấy, Hà Nội

SĐT: 02435574074 - 0914939543

Email: hello@niithanoi.edu.vn

Website: https://niithanoi.edu.vn

Fanpage: https://facebook.com/NIIT.ICT/

#niit #niithanoi #niiticthanoi #hoclaptrinh #khoahoclaptrinh #hoclaptrinhjava #hoclaptrinhphp #java #php #python

Về trang trước

Gửi email in trang