Thủ tục lưu trữ và Hàm
Đị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
Bạn đang xem trước 20 trang tài liệu Thủ tục lưu trữ và Hàm, để xem tài liệu hoàn chỉnh bạn click vào nút DOWNLOAD ở trên
Thủ tục lưu trữ và Hàm
Store Procedures
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
Đị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
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
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';
Khai báo thủ tục
CREATE [DEFINER = { user |
CURRENT_USER }] PROCEDURE
sp_name ([proc_parameter[,...]])
[characteristic ...] routine_body
Khai báo hàm
CREATE [DEFINER = { user |
CURRENT_USER }] FUNCTION sp_name
([func_parameter[,...]]) RETURNS type
[characteristic ...] routine_body
Ý 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ệ
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 ;
Gọi thủ tục
CALL sp_name([parameter[,...]])
CALL sp_name[()]
Ví dụ
CALL Hello()
CALL Hello('Marry')
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
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)
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 ;
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???
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 ;
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');
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 ;
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
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 ;
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
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'
Conditional Control
(Điều khiển rẽ nhánh)
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
Cấu trúc IF
IF expression THEN commands [ELSEIF
expression THEN commands ....] [ELSE
commands] END IF;
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 ;
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??
Cấu trúc IF – dạng 2
IF expression THEN commands
ELSE commands
END IF;
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 ;
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??
Cấu trúc IF – dạng 3
IF expression THEN commands2
ELSEIF expression THEN commands3
ELSE commands4 END IF;
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 ;
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??
Cấu trúc Case
CASE
WHEN expression THEN commands;
…………
WHEN expression THEN commands…;
ELSE commands;
END CASE;
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 ;
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??
Cấu trúc vòng lặp
While,Repeat,Loop
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.
While
WHILE expression DO
Statements
END WHILE
Thực hiện statements cho đến khi nào
expression còn đúng
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 ;
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ả??
Repeat
REPEAT
Statements;
UNTIL expression
END REPEAT
Thực hiện lại statements cho đến khi
expression là true
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 ;
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ả??
LOOP
[begin_label:] LOOP statement_list END
LOOP [end_label]
Trong đó:
[begin_label:] Nhãn của Loop
Ví dụ:
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 ;
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ả??
