Bài giảng Chuyên đề hệ quản trị cơ sở dữ liệu - Phần 3: Thủ tục lưu trữ và Hàm - Bùi Thị Hồng Phúc

Nội dung text: Bài giảng Chuyên đề hệ quản trị cơ sở dữ liệu - Phần 3: Thủ tục lưu trữ và Hàm - Bùi Thị Hồng Phúc

1. Thủ tục lưu trữ và Hàm Store Procedures
2. Nội dung Định nghĩa thủ tục lưu trữ,hàm Ưu điểm của thủ tục lưu trữ,hàm Tạo lập thực thi thủ tục lưu trữ,hàm Tham số trong thủ tục lưu trữ,hàm Biến cục bộ Cấu trúc điều khiển
3. Định nghĩa thủ tục lưu trữ, hàm  Là một nhóm các câu lệnh  Được biên dịch trước → thực hiện một nhiệm vụ cụ thể.  Được viết bởi: − Người phát triển CSDL. − DBA – Database Administrator  Hỗ trợ cho công việc quản trị CSDL
4. Thuận lợi Lập trình theo module Thực thi nhanh hơn các lệnh T-SQL Làm giảm lưu lượng trên mạng Tăng cường bảo mật
5. Xem các thủ tục lưu trữ của một database select routine_type, routine_name from information_schema.routines where routine_schema='csdl_qldthi';
6. Khai báo thủ tục CREATE [DEFINER = { user | CURRENT_USER }] PROCEDURE sp_name ([proc_parameter[, ]]) [characteristic ] routine_body
7. Khai báo hàm CREATE [DEFINER = { user | CURRENT_USER }] FUNCTION sp_name ([func_parameter[, ]]) RETURNS type [characteristic ] routine_body
8. Ý nghĩa các tham số Trong đó: proc_parameter: là kiểu của tham số [ IN | OUT | INOUT ] param_name type type: Kiểu dữ liệu hợp lệ trong MySQL routine_body: Những câu lệnh hợp lệ
9. Khai báo thủ tục – ví dụ DELIMITER $$ DROP PROCEDURE IF EXISTS `Hello` $$ CREATE DEFINER=`root`@`localhost` PROCEDURE `Hello`() BEGIN select 'Hello World'; END $$ DELIMITER ;
10. Gọi thủ tục CALL sp_name([parameter[, ]]) CALL sp_name[()] Ví dụ CALL Hello() CALL Hello('Marry')
11. Tham số trong thủ tục lưu trữ,hàm Làm cho các chương trình mềm dẻo hơn và hữu dụng hơn Được đặt trong cặp dấu ngoặc đơn sau thủ tục với cú pháp: mode parameter_name datatype Lưu ý: Mode có 3 giá trị IN, OUT, INOUT riêng đối với function chỉ có tham số kiểu IN
12. Các kiểu tham số Có 3 kiểu tham số: Chỉ đọc (Read-only) (mặc định) Chỉ ghi (Write – only) Vừa đọc vừa ghi (read - write)
13. Khai báo thủ tục - vd1 DELIMITER $$ DROP PROCEDURE IF EXISTS `Hello_in` $$ CREATE DEFINER=`root`@`localhost` PROCEDURE `Hello_in`(chao varchar(100)) BEGIN set chao = 'Daisy'; select concat('Hello ',chao); END $$ DELIMITER ;
14. Gọi thủ tục set @name='Marry'; CALL Hello_in(@name); select @name; Câu hỏi: Thực thi từng câu query Nhận xét sự thay đổi của biến @name???
15. Khai báo hàm – vd1 DELIMITER $$ DROP FUNCTION IF EXISTS `test_baitap`.`test_f` $$ CREATE FUNCTION `test_baitap`.`test_f` (name varchar(50)) RETURNS varchar(50) BEGIN declare str1 varchar(50); select concat('hello ',name) into str1; return str1; END $$ DELIMITER ;
16. Gọi hàm – vd1 SELECT test_f('mary') Hoặc Set @a =``; Set @a:=test_f('mary'); Hoặc Set @a=``; select @a:= test_f('mary');
17. Khai báo thủ tục – ví dụ 2 DELIMITER $$ DROP PROCEDURE IF EXISTS `Hello_out` $$ CREATE DEFINER=`root`@`localhost` PROCEDURE `Hello_out`(str1 varchar(100),out str2 varchar(100)) BEGIN set str2 = 'Daisy'; select concat(str1,' ',str2); END $$ DELIMITER ;
18. Gọi thủ tục – ví dụ 2 set @str1='Hello'; CALL Hello_out(@str1,@name); select @name; Câu hỏi: Thực thi từng câu query Nhận xét sự thay đổi của biến @name
19. Khai báo thủ tục – vd3 DELIMITER $$ DROP PROCEDURE IF EXISTS `Hello_out` $$ CREATE DEFINER=`root`@`localhost` PROCEDURE `Hello_out`(inout chao varchar(100)) BEGIN set chao = 'Daisy'; select concat('Hello ',chao); END $$ DELIMITER ;
20. Gọi thủ tục set @chao='Marry'; CALL Hello_in_out(@chao); select @chao; Câu hỏi: Thực thi từng câu query Nhận xét sự thay đổi của biến @name
21. Biến cục bộ Được khai báo bên trong thủ tục Cú pháp: DECLARE variable_name [,variable_name ] datatype [DEFAULT value]; Vd: declare name varchar(50) default 'hey'
22. Conditional Control (Điều khiển rẽ nhánh)
23. Cấu trúc điều khiển Thực thi code dựa trên giá trị của – Một biểu thức – Sự kết hợp cuả nhiều biểu thức sử dụng toán tử logic MySQL hỗ trợ 2 dạng của cấu trúc điều khiển – IF .THEN – CASE
24. Cấu trúc IF IF expression THEN commands [ELSEIF expression THEN commands ] [ELSE commands] END IF;
25. Cấu trúc IF – dạng 1 IF expression THEN commands2 END IF; Ví dụ 1: DELIMITER $$ DROP PROCEDURE IF EXISTS `test`.`IF_DANG1` $$ CREATE PROCEDURE `test`.`IF_DANG1` (num1 int) BEGIN if num1 = 0 then select 'một số bằng o'; end if; END $$ DELIMITER ;
26. Cấu trúc IF- dạng 1 Yêu cầu: Tạo thủ tục như trên Gọi thủ tục: CALL IF_DANG1(1); CALL IF_DANG1(0) Xem kết quả và cho nhận xét??
27. Cấu trúc IF – dạng 2 IF expression THEN commands ELSE commands END IF;
28. Cấu trúc IF – dạng 2- ví dụ DELIMITER $$ DROP PROCEDURE IF EXISTS `test`.`IF_DANG2` $$ CREATE PROCEDURE `test`.`IF_DANG2` (num1 int) BEGIN if num1=0 or num1=1 then select 'số bằng 0 hoặc 1'; else select 'trường hợp khác'; end if; END $$ DELIMITER ;
29. Cấu trúc IF – dạng 2 – ví dụ Yêu cầu: Tạo thủ tục như trên Gọi thủ tục: CALL IF_DANG1(1); CALL IF_DANG1(0) Xem kết quả và cho nhận xét??
30. Cấu trúc IF – dạng 3 IF expression THEN commands2 ELSEIF expression THEN commands3 ELSE commands4 END IF;
31. Cấu trúc IF – dạng 2 – ví dụ DELIMITER $$ DROP PROCEDURE IF EXISTS `test`.`IF_DANG3` $$ CREATE PROCEDURE `test`.`IF_DANG3` (NAME varchar(50)) BEGIN if name='Lan' then select 'Hello Lan'; elseif name='Hue' then select 'Hello Hue'; else select 'I don`t know you'; end if; END $$ DELIMITER ;
32. Cấu trúc IF – dạng 3 – ví dụ Yêu cầu: Tạo thủ tục như trên Gọi thủ tục: CALL IF_DANG1(`Lan`); CALL IF_DANG1(`Hồng`); Xem kết quả và cho nhận xét??
33. Cấu trúc Case CASE WHEN expression THEN commands; WHEN expression THEN commands ; ELSE commands; END CASE;
34. Cấu trúc Case – Ví dụ DELIMITER $$ DROP PROCEDURE IF EXISTS `test`.`case` $$ CREATE PROCEDURE `test`.`case` (name varchar(50)) BEGIN CASE WHEN name ='lan' THEN SELECT 'Hello Lan'; WHEN name='hue' THEN SELECT 'Hello Hue'; ELSE SELECT 'i don`t know you.'; END CASE; END $$ DELIMITER ;
35. Cấu trúc CASE– ví dụ Yêu cầu: Tạo thủ tục như trên Gọi thủ tục: CALL test.`case`('lan') CALL test.`case`(hue') CALL test.`case`(`an') Xem kết quả và cho nhận xét??
36. Cấu trúc vòng lặp While,Repeat,Loop
37. Cấu trúc vòng lặp Cho phép bạn xử lý những câu lệnh lặp đi, lặp lại nhiều lần cho đến khi thoả mãn điều kiện dừng.
38. While WHILE expression DO Statements END WHILE Thực hiện statements cho đến khi nào expression còn đúng
39. While – ví dụ DELIMITER $$ DROP PROCEDURE IF EXISTS WhileLoopProc$$ CREATE PROCEDURE WhileLoopProc() BEGIN DECLARE x INT; DECLARE str VARCHAR(255); SET x = 1; SET str = ''; WHILE x <= 5 DO SET str = CONCAT(str,x,','); SET x = x + 1; END WHILE; SELECT str; END$$ DELIMITER ;
40. Cấu trúc While– ví dụ Yêu cầu: Tạo thủ tục như trên Gọi thủ tục: CALL WhileLoopProc() Xem kết quả??
41. Repeat REPEAT Statements; UNTIL expression END REPEAT Thực hiện lại statements cho đến khi expression là true
42. Repeat – ví dụ DELIMITER $$ DROP PROCEDURE IF EXISTS RepeatLoopProc$$ CREATE PROCEDURE RepeatLoopProc() BEGIN DECLARE x INT; DECLARE str VARCHAR(255); SET x = 1; SET str = ''; REPEAT SET str = CONCAT(str,x,','); SET x = x + 1; UNTIL x > 5 END REPEAT; SELECT str; END$$ DELIMITER ;
43. Cấu trúc Repeat– ví dụ Yêu cầu: Tạo thủ tục như trên Gọi thủ tục: CALL RepeatLoopProc() Xem kết quả??
44. LOOP [begin_label:] LOOP statement_list END LOOP [end_label] Trong đó: [begin_label:] Nhãn của Loop Ví dụ:
45. DELIMITER $$ DROP PROCEDURE IF EXISTS LOOPLoopProc$$ CREATE PROCEDURE LOOPLoopProc() BEGIN DECLARE x INT; DECLARE str VARCHAR(255); SET x = 1; SET str = ''; loop_label: LOOP IF x > 10 THEN LEAVE loop_label; END IF; SET x = x + 1; IF (x mod 2) THEN ITERATE loop_label; ELSE SET str = CONCAT(str,x,','); END IF; END LOOP; SELECT str; END$$ DELIMITER ;
46. Cấu trúc Loop– ví dụ Yêu cầu: Tạo thủ tục như trên Gọi thủ tục: CALL LOOPLoopProc() Xem kết quả??