(溫故知新) 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 語句。