---
# System prepended metadata

title: 如何使用procedures.sql?

---

# 如何使用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
```
    