Công nghệ thông tin - Quản lý cơ sở dữ liệu

Chia sẻ: Nguyễn Kim Thành | Ngày: | Loại File: PDF | Số trang:20

Thêm vào BST

Báo xấu

94
lượt xem 6
download

Download Vui lòng tải xuống để xem tài liệu đầy đủ

Giới thiệu Các DBMS hiện nay đều cho phép người dùng tạo các chương trình con: nhóm các câu lệnh để có thể gọi lại Hai loại chương trình con: Thủ tục (procedure): không trả về giá trị Hàm (function): có trả về giá trị Tác dụng: Tương tự các hàm của ngôn ngữ lập trình, các chương trình con của SQL giúp tái sử dụng và phân chia các câu lệnh dễ dàng hơn Giảm lưu lượng trao đổi dữ liệu giữa client và DBMS Tăng tốc độ thực thi do DBMS không cần phân tích lại các câu...

Chủ đề:

Bình luận(0) Đăng nhập để gửi bình luận!

Lưu

Nội dung Text: Công nghệ thông tin - Quản lý cơ sở dữ liệu

Stored Routines EE4509, EE6133 – HK2 2011/2012 1 TS. Đào Trung Kiên – ĐH Bách khoa Hà Nội
Giới thiệu Các DBMS hiện nay đều cho phép người dùng tạo các chương  trình con: nhóm các câu lệnh để có thể gọi lại Hai loại chương trình con:  Thủ tục (procedure): không trả về giá trị  Hàm (function): có trả về giá trị  Tác dụng:  Tương tự các hàm của ngôn ngữ lập trình, các chương trình con của  SQL giúp tái sử dụng và phân chia các câu lệnh dễ dàng hơn Giảm lưu lượng trao đổi dữ liệu giữa client và DBMS  Tăng tốc độ thực thi do DBMS không cần phân tích lại các câu lệnh mỗi  lần thực thi EE4509, EE6133 – HK2 2011/2012 2 TS. Đào Trung Kiên – ĐH Bách khoa Hà Nội
MySQL EE4509, EE6133 – HK2 2011/2012 3 TS. Đào Trung Kiên – ĐH Bách khoa Hà Nội
Cú pháp Tạo thủ tục:  create procedure tên-thủ-tục([các-tham-số]) begin các-câu-lệnh; end Xoá thủ tục đã tạo:  drop procedure tên-thủ-tục; Gọi thủ tục:  call tên-thủ-tục([các-tham-số]); EE4509, EE6133 – HK2 2011/2012 4 TS. Đào Trung Kiên – ĐH Bách khoa Hà Nội
Ví dụ - thủ tục không có tham số delimiter // Đổi ký hiệu phân tách câu lệnh (để dùng dấu “;” bên trong thủ tục con) create procedure all_students() begin Tạo thủ tục select * from Student; end // delimiter ; Đổi lại ký hiệu phân tách thành dấu “;” call all_students(); Gọi thủ tục (in ra bảng Student) EE4509, EE6133 – HK2 2011/2012 5 TS. Đào Trung Kiên – ĐH Bách khoa Hà Nội
Ví dụ - thủ tục có tham số delimiter // create procedure students_of (class_name varchar(50)) begin select * from Student where class = class_name; end// delimiter ; call students_of('A'); EE4509, EE6133 – HK2 2011/2012 6 TS. Đào Trung Kiên – ĐH Bách khoa Hà Nội
Tham số vào và ra Các tham số không chỉ dùng để truyền tham số cho thủ  tục (IN), mà còn có thể dùng để truyền giá trị từ thủ tục ra ngoài (OUT) Mỗi tham số của thủ tục có thể được khai báo bằng một  trong các loại: in: tham số vào (truyền giá trị cho thủ tục) – mặc định  out: tham số ra (truyền giá trị từ thủ tục ra ngoài)  inout: tham số cả vào và ra  Cách khai báo tham số:  create procedure tên-thủ-tục ( [in/out/inout] tên-tham-số kiểu-tham-số, [in/out/inout] tên-tham-số kiểu-tham-số,...) ... EE4509, EE6133 – HK2 2011/2012 7 TS. Đào Trung Kiên – ĐH Bách khoa Hà Nội
Ví dụ - tham số ra delimiter // create procedure student_count (out stdcnt int) begin select count(*) into stdcnt from Student; end// delimiter ; call student_count(@a); select @a; EE4509, EE6133 – HK2 2011/2012 8 TS. Đào Trung Kiên – ĐH Bách khoa Hà Nội
Hàm Tương tự như thủ tục nhưng có giá trị trả về  Cách khai báo:  create function tên-hàm (các-tham-số) returns kiểu-kết-quả begin các-câu-lệnh; end Các tham số được khai báo tương tự thủ tục  Để kết thúc và trả về kết quả trong hàm, dùng câu  lệnh: return giá-trị; EE4509, EE6133 – HK2 2011/2012 9 TS. Đào Trung Kiên – ĐH Bách khoa Hà Nội
Ví dụ - hàm delimiter // create function update_class( in class_name varchar(20)) returns int begin update Student set class = class_name; return row_count(); end// delimiter ; set @rows = update_class('B'); select @rows; EE4509, EE6133 – HK2 2011/2012 10 TS. Đào Trung Kiên – ĐH Bách khoa Hà Nội
SQL Server EE4509, EE6133 – HK2 2011/2012 11 TS. Đào Trung Kiên – ĐH Bách khoa Hà Nội
Khái quát Khái niệm và việc sử dụng chương trình con trong  SQL Server cũng tương tự như trong MySQL, nhưng các câu lệnh không hoàn toàn giống nhau Tạo thủ tục:  create procedure tên-thủ-tục [các-tham-số] as begin các-câu-lệnh; end; Gọi thủ tục:  exec tên-thủ-tục [các-tham-số]; EE4509, EE6133 – HK2 2011/2012 12 TS. Đào Trung Kiên – ĐH Bách khoa Hà Nội
Ví dụ - thủ tục create procedure count_student_of @class_name varchar(20), @stdcnt int out as begin select @stdcnt = count(*) from Student where class = @class_name; end; declare @classA_stdcnt int; exec count_student_of 'A', @stdcnt = @classA_stdcnt out; select @classA_stdcnt; EE4509, EE6133 – HK2 2011/2012 13 TS. Đào Trung Kiên – ĐH Bách khoa Hà Nội
Một số chú ý Không cần thay đổi ký hiệu phân tách như trong  MySQL Các tham số phải có ký hiệu @ ở trước  Từ khoá “out” khai báo ở sau tên và kiểu của các  tham số Hàm (giống MySQL):  Khai báo bằng cách thêm “returns …”  Trả về giá trị bằng “return …”  EE4509, EE6133 – HK2 2011/2012 14 TS. Đào Trung Kiên – ĐH Bách khoa Hà Nội
Trigger EE4509, EE6133 – HK2 2011/2012 15 TS. Đào Trung Kiên – ĐH Bách khoa Hà Nội
Khái niệm Trigger là [một đối tượng bao gồm] các câu lệnh SQL,  được thực thi tự động khi xảy ra các sự kiện mà nó đăng ký Giúp đảm bảo tính toàn vẹn và nhất quán của dữ liệu  Ví dụ:  Khi xoá một tác giả (sự kiện)   xoá các sách tương ứng (trigger) Khi thêm một thuê bao mới (sự kiện)   khuyến mại $50 vào tài khoản (trigger) Trigger có thể được đăng ký thực thi trước hoặc sau khi  sự kiện xảy ra Các DBMS hiện nay đều hỗ trợ  EE4509, EE6133 – HK2 2011/2012 16 TS. Đào Trung Kiên – ĐH Bách khoa Hà Nội
Trong MySQL Tạo:  create trigger tên-trigger thời-điểm sự-kiện on tên-quan-hệ for each row câu-lệnh-được-gọi; thời-điểm: before / after  sự-kiện: insert / delete / update  Nếu nhiều câu lệnh cần thực thi: dùng “begin ... end”  Xoá:  drop trigger tên-trigger; Ví dụ:  create trigger DelAuthorBooks after delete on Author  for each row ...; create trigger PromoteNewAcc after insert on Account  for each row ...; EE4509, EE6133 – HK2 2011/2012 17 TS. Đào Trung Kiên – ĐH Bách khoa Hà Nội
Trong MySQL (tiếp) Để tham chiếu tới các hàng dữ liệu trước và sau khi  sự kiện xảy ra, dùng các từ khoá “old” và “new” update: old, new  insert: chỉ có new  delete: chỉ có old  Ví dụ:  create trigger DelAuthorBooks after delete  on Author for each row delete from Book where author_id = old.id; create trigger PromoteNewAcc after insert  on Account for each row set new.balance = new.balance + 50; EE4509, EE6133 – HK2 2011/2012 18 TS. Đào Trung Kiên – ĐH Bách khoa Hà Nội
Trong SQL Server Cú pháp tạo trigger:  create trigger tên-trigger on tên-quan-hệ [thời-điểm] sự-kiện as câu-lệnh-được-gọi; thời-điểm: for / after / instead of   for, after: sau khi sự kiện xảy ra  instead of: thực hiện trigger thay cho sự kiện  Không có “before”  dùng “instead of” và thực hiện lại sự kiện cần thực hiện sự-kiện: insert / delete / update  Tham chiếu các hàng dữ liệu được thêm/xoá bằng các  từ khoá “inserted” và “deleted” thay vì “new” và “old” EE4509, EE6133 – HK2 2011/2012 19 TS. Đào Trung Kiên – ĐH Bách khoa Hà Nội
Bài tập Viết hàm trả về số sinh viên xếp loại giỏi của một lớp 1. có tên cho từ tham số Tạo trigger thực hiện: khi thêm một nhân viên vào một 2. dự án, cập nhật lại thời gian làm việc cho dự án đó của từng thành viên Tạo trigger thực hiện: khi bỏ một nhân viên của một dự 3. án, cập nhật lại thời gian làm việc cho dự án đó của từng thành viên; nếu không còn nhân viên nào, xoá dự án khỏi danh sách EE4509, EE6133 – HK2 2011/2012 20 TS. Đào Trung Kiên – ĐH Bách khoa Hà Nội