Hàm - Thủ tục - Trigger trong SQL

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, 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 có cung cấp cá tính năng hỗ trợ người dùng viết – sử dụng các hàm như trong các ngôn ngữ lập trình khác.

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

a) Cách 1:

SELECT get_dtb('B1900') FROM DIEM;

b) Cách 2:

DECLARE    
   dtb number(3);    
BEGIN    
   dtb := get_dtb('B1900');    
   dbms_output.put_line('Điểm trung bình: ' || dtb);    
END;

Để thựcc 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:

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 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 đặt thù – có thể sử dụng bằng cách gọi chính tên thủ tục đã đặt.

Cú pháp:

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:

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:

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 trigger 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:

DROP TRIGGER <tên trigger> ;

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

DROP TRIGGER temp ;

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ẻ 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.

Bài viết mang tính chất “ghi chú, lưu trữ, chia sẻ và phi lợi nhuận”.
Nếu bạn thấy hữu ích, đừng quên chia sẻ với bạn bè và đồng nghiệp của mình nhé!

Happy coding! 😎 👍🏻 🚀 🔥