# 如何使用procedures.sql?
我稍微修改了search.sql的運作方式，也加了新的功能。以下會說明它的功能和使用方式
## search.sql的內容
* **開頭的註釋**
輸入參數的範例
* **兩個mysql的procedure(`main`,`cat_city_locations`)**
這個檔案的主要功能。執行`main`之後，會依據輸入參數產生`@query`並將`@query`視為mysql的query輸入執行。`cat_city_locations`只特別用於處理地點搜尋。
## 如何使用
1. `source search.sql`
執行search.sql，定義要用到的兩個procedure。
procedure在定義之後應該不會不見，除非它被手動刪除掉，所以基本上可以跳過這步。
2. `set` 要用到的參數
```mysql
set @inputStartDate = "2019-03-20";
#mysql的 DATE格式("YYYY-MM-DD")
set @inputEndDate = "2020-12-31";
#同上
set @inputCityID = "0,4";
#逗點(',')區隔的整數
set @inputCategoryID = "4,5,6";
#同上
set @inputIsFree = "Y"
#'Y'或'N'
```
沒有要用某個參數時，要把它設成`NULL`。這些參數會保留到使用者登出(執行`exit`)。所以要在每次執行前**把沒用到的參數主動設成NULL**
3. 執行main
```mysql
call main();
```
## 目前的功能
1. 搜尋**特定時間**內有多少展演中的文藝表演
2. 以**特定位置**為搜尋目標，找出該地點於某段時間內有哪些文藝表演
3. 根據使用者喜愛的**活動類別**，推薦即將開展或展演中的表演
4. 有些活動是**免費**的，能分辨出來並展示給使用者
## cat_city_locations
### code
```mysql
create procedure cat_city_locations()
begin
declare i int default 1;
declare cnt int default 0;
declare v_location_name text;
declare location_cur cursor for
select location_name
from locations
where city_id = @curr_cityid
;
open location_cur;
select count(UID) into cnt from locations where city_id = @curr_cityid;
while i<cnt do
set @query = concat(@query, " or ");
fetch location_cur into v_location_name;
set @query = concat(@query,
" instr(showInfo.location, \"",
v_location_name,
"\" collate utf8mb4_unicode_ci)");
set i=i+1;
end while;
close location_cur;
# in main():
# ...
if not isnull(@inputCityID) then
select length(@inputCityID)-length(replace(@inputCityID,',',''))+1 into cnt;
set @query = concat(@query, " and ( false ");
while i <= cnt do
select cast(
substring_index(
substring_index(@inputCityID,',',i)
,',',-1)
as unsigned)
into @curr_cityid;
call cat_city_locations();
set i=i+1;
end while;
set @query = concat(@query, ")");
end if;
#...
end //
```
### 說明
#### main
```mysql
select length(@inputCityID)-length(replace(@inputCityID,',',''))+1 into cnt;
```
找出`@inputCityID`裡有多少個輸入(`@inputCityID`的長度-`@inputCityID`去掉逗號之後的長度)
```mysql
set @query = concat(@query, " and ( false ");
```
`@inputCityID`條件的開頭，要和其他條件做and
```mysql
# in while
select
cast(
substring_index(
substring_index(@inputCityID,',',i)
,',',-1)
as unsigned)
into @curr_cityid;
call cat_city_locations();
```
一一把@inputCityID裡的值選出來，並輸入到 `cat_city_locations()`裡
```mysql
# after while
set @query = concat(@query, ")");
```
`@inputCityID`條件的結尾，把開頭的and的括弧關起來
#### cat_city_location
```mysql
declare location_cur cursor for
select location_name
from locations
where city_id = @curr_cityid
;
open location_cur;
```
宣告並開啟一個能夠查locations裡city_id符合`@curr_cityid`的項目並把值一一`fetch`出來的`cursor`
```mysql
# in while:
set @query = concat(@query, " or ");
fetch location_cur into v_location_name;
```
在條件後面接上 or，然後`fetch`下一個`location_name`到`v_location_name`裡
```mysql
# in while:
set @query = concat(@query,
" instr(showInfo.location, \"",
v_location_name,
"\" collate utf8mb4_unicode_ci)");
```
在條件後面接上`v_location_name`對應的條件
##### 產出的條件
```mysql
instr(showInfo.location,"*v_location_name的值*" collate utf8mb4_unicode_ci)
```
`instr()`: 找`showInfo.location`裡有沒有包含`v_location_name`
`collate utf8mb4_unicode_ci`: 轉換`v_location_name`的編碼方式
## 舉例
設定:
```mysql
set @inputStartDate = "2019-03-20";
set @inputEndDate = "2020-12-31";
set @inputCityID = "0,3";#台北、新竹
set @inputCategoryID = "4,5,6";
set @inputIsFree = "Y";
call main();
select @query;
```
output:
```mysql
# 固定的開頭
select *
from artshow, showInfo
where artshow.UID = showInfo.artshowUID
# 如果 @inputStartDate 是NULL,就沒有這個條件
and showInfo.time >= @inputStartDate
# 如果 @inputEndDate 是NULL,就沒有這個條件
and showInfo.time <= @inputEndDate
# 把locations裡的台北和新竹的地名加入條件
and ( false
or instr(showInfo.location, "台北市" collate utf8mb4_unicode_ci)
or instr(showInfo.location, "臺北市" collate utf8mb4_unicode_ci)
or instr(showInfo.location, "仁愛路四段" collate utf8mb4_unicode_ci)
or instr(showInfo.location, "國立歷史博物館" collate utf8mb4_unicode_ci)
or instr(showInfo.location, "忠孝西路一段" collate utf8mb4_unicode_ci)
or instr(showInfo.location, "新生南路" collate utf8mb4_unicode_ci)
or instr(showInfo.location, "杭州南路" collate utf8mb4_unicode_ci)
or instr(showInfo.location, "研究院路" collate utf8mb4_unicode_ci)
or instr(showInfo.location, "新竹市" collate utf8mb4_unicode_ci)
or instr(showInfo.location, "新竹縣" collate utf8mb4_unicode_ci)
or instr(showInfo.location, "北區集賢街" collate utf8mb4_unicode_ci)
or instr(showInfo.location, "文興路" collate utf8mb4_unicode_ci)
or instr(showInfo.location, "竹北市" collate utf8mb4_unicode_ci)
)
# 把 @inputCategoryID 裡的數字加入category的條件
and ( false
or artshow.category = 4
or artshow.category = 5
or artshow.category = 6
)
# 如果 @inputIsFree 是NULL,就沒有這個條件
and showInfo.onsales = @inputIsFree collate utf8mb4_unicode_ci
```
設定:
```mysql
set @inputStartDate = NULL;
set @inputEndDate = "2020-12-31";
set @inputCityID = "0";#台北
set @inputCategoryID = NULL;
set @inputIsFree = "Y";
call main();
select @query;
```
output:
```mysql
# 固定的開頭
select *
from artshow, showInfo
where artshow.UID = showInfo.artshowUID
# @inputStartDate 是NULL,跳過這個條件
# 如果 @inputEndDate 是NULL,就沒有這個條件
and showInfo.time <= @inputEndDate
# 把locations裡的台北的地名加入條件
and ( false
or instr(showInfo.location, "台北市" collate utf8mb4_unicode_ci)
or instr(showInfo.location, "臺北市" collate utf8mb4_unicode_ci)
or instr(showInfo.location, "仁愛路四段" collate utf8mb4_unicode_ci)
or instr(showInfo.location, "國立歷史博物館" collate utf8mb4_unicode_ci)
or instr(showInfo.location, "忠孝西路一段" collate utf8mb4_unicode_ci)
or instr(showInfo.location, "新生南路" collate utf8mb4_unicode_ci)
or instr(showInfo.location, "杭州南路" collate utf8mb4_unicode_ci)
or instr(showInfo.location, "研究院路" collate utf8mb4_unicode_ci)
)
# 跳過category的條件
# 如果 @inputIsFree 是NULL,就沒有這個條件
and showInfo.onsales = @inputIsFree collate utf8mb4_unicode_ci
```
Sign in with Wallet
Connect another wallet