2015年4月9日 星期四

(溫故知新) Store Procedure 練習

【Store Procedure 練習】

以下是運動中心的使用者資料與使用記錄

USER_PROFILE (使用者資料)

USER_ID

(使用者編號)

USER_NAME

(使用者姓名)

USER_SEX

(使用者性別)

USER_PHONE

(使用者電話)

USER_ADDRESS (使用者地址)

2013000001

張三

M

0911120111

台北市士林區

2013000002

李四

M

0911120112

新北市土城區

2013000003

王五

M

0911120113

新北市三重區

2013000004

陳二

F

0911120114

台北市信義區

2013000005

孫九

F

0911120115

台北市中山區

USE_RECORD (使用記錄)

USER_ID

(使用者編號)

USE_DATE_START

(使用開始日)

USE_TIME_START

(使用開始時間)

USE_DATE_END

(使用結束日)

USE_TIME_END

(使用結束時間)

USE_PLACE

(使用地方)

2013000001

20131101

080500

20131101

121000

1

2013000001

20131102

083300

20131102

121900

1

2013000001

20131102

130000

20131102

162300

1

2013000001

20131101

130550

20131101

235000

3

2013000005

20131102

130300

20131102

140000

1

2013000002

20131102

103000

20131102

162400

3

2013000002

20131102

182400

20131102

203000

2

2013000002

20131102

230130

20131103

033030

1

2013000003

20131101

090100

20131101

100600

2

2013000003

20131102

152000

20131102

202400

1

2013000003

20131102

100000

20131103

010000

2

(1) 2013/11/02 曾經在運動中心運動的有哪些人 ?

(2) 2013/11/02 12:00:00~2013/11/03 12:00:00 曾經在運動中心運動的有哪些人?

以下以 MySQL 為環境,使用 MySQL Workbench 建立Table
-- MySQL 語法 先建立 Table USER_PROFILE
CREATE TABLE IF NOT EXISTS `USER_PROFILE` (
`ID` int(11) NOT NULL AUTO_INCREMENT,
`USER_ID` varchar(11) NOT NULL,
`USER_NAME` varchar(10) NOT NULL,
`USER_SEX` varchar(10) NOT NULL,
`USER_PHONE` varchar(10) NOT NULL,
`USER_ADDRESS` varchar(10) NOT NULL,
PRIMARY KEY (`ID`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 ;
-- MySQL 語法 插入資料
INSERT INTO `USER_PROFILE` (`USER_ID`, `USER_NAME`, `USER_SEX`,`USER_PHONE`,`USER_ADDRESS`) VALUES
('2013000001', '張三', 'M','0911120111','台北市士林區'),
('2013000002', '李四', 'M','0911120112','新北市土城區'),
('2013000003', '王五', 'M','0911120113','新北市三重區'),
('2013000004', '陳二', 'F','0911120114','台北市信義區'),
('2013000005', '孫九', 'F','0911120115','台北市中山區');
-- MySQL 語法 驗證資料
SELECT * FROM `user_profile`;
-- MySQL 語法 先建立 Table USE_RECORD
CREATE TABLE IF NOT EXISTS `USE_RECORD` (
`ID` int(11) NOT NULL AUTO_INCREMENT,
`USER_ID` varchar(11) NOT NULL,
`USE_DATE_START` varchar(10) NOT NULL,
`USE_TIME_START` varchar(10) NOT NULL,
`USE_DATE_END` varchar(10) NOT NULL,
`USE_TIME_END` varchar(10) NOT NULL,
`USE_PLACE` varchar(2) NOT NULL,
PRIMARY KEY (`ID`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 ;
-- MySQL 語法 插入資料
INSERT INTO `USE_RECORD` (`USER_ID`, `USE_DATE_START`, `USE_TIME_START`,`USE_DATE_END`,`USE_TIME_END`,`USE_PLACE`) VALUES
('2013000001', '20131101', '080500','20131101','121000','1'),
('2013000001', '20131102', '083300','20131102','121900','1'),
('2013000001', '20131102', '130000','20131102','162300','1'),
('2013000001', '20131101', '130550','20131101','235000','3'),
('2013000005', '20131102', '130300','20131102','140000','1'),
('2013000002', '20131102', '103000','20131102','162400','3'),
('2013000002', '20131102', '182400','20131102','203000','2'),
('2013000002', '20131102', '230130','20131103','033030','1'),
('2013000003', '20131101', '090100','20131101','100600','2'),
('2013000003', '20131102', '152000','20131102','202400','1'),
('2013000003', '20131102', '100000','20131103','010000','2');
-- MySQL 語法 驗證資料
SELECT * FROM `use_record`;
問題解答 My_SQL Store Procedure語法
(1) 先判斷有沒有相關 procedure 存在,若有則刪除,在建立一新的 procedure
drop procedure if exists `test`.`qery_user_by_date`;
delimiter $$
CREATE PROCEDURE `test`.qery_user_by_date (in whattime varchar(14))
begin
SELECT distinct p.USER_NAME as '姓名'
FROM user_profile p
INNER join use_record r
on p.USER_ID = r.USER_ID
where r.USE_DATE_START = whattime;
end $$
delimiter ;
驗證部分:
call `test`.qery_user_by_date('20131102');
(2) 先判斷有沒有相關 procedure 存在,若有則刪除,在建立一新的 procedure
drop procedure if exists `test`.`qery_user_by_datetime`;
delimiter $$
CREATE PROCEDURE `test`.qery_user_by_datetime (in whattimestart varchar(14),in whattimeend varchar(14))
begin
SELECT distinct p.USER_NAME as '姓名'
FROM `user_profile` p
INNER join `use_record` r
on p.USER_ID = r.USER_ID
where
CONCAT(r.USE_DATE_START,USE_TIME_START) between whattimestart and whattimeend
or
CONCAT(r.USE_DATE_END,USE_TIME_END) between whattimestart and whattimeend;
end $$
delimiter ;
驗證部分:
call `test`.qery_user_by_datetime('201311021200','201311031200');
其中 delimiter $$ 用法可參考:
http://www.cnblogs.com/rootq/archive/2009/05/27/1490523.html
其中有詳盡的說明,主要是用於於命令列中,定義 MySQL 語法的結束符號,讓我們可以一次輸入多行的MySQL 語句。

沒有留言 :

張貼留言