mysql使⽤存储过程，给临时列创建索引排序, CREATE PROCEDURE `testOrgan`()
BEGIN
DROP TABLE IF    EXISTS tmp_table;
CREATE TEMPORARY TABLE `tmp_table`  (
`city_code` varchar(32) DEFAULT NULL COMMENT '城市代码',
`ins_code` varchar(32) DEFAULT NULL COMMENT '城市代码',
`organ_type` varchar(12) DEFAULT NULL COMMENT '机构类型，H：医院；P：药店',
`organ_name` varchar(64) NOT NULL COMMENT '机构名称',
`organ_address` varchar(512) DEFAULT NULL COMMENT '机构地址',
`organ_access_url` varchar(512) DEFAULT NULL COMMENT '机构跳转地址',
`state` varchar(1) DEFAULT NULL COMMENT '状态',
`longitude` double DEFAULT NULL COMMENT '经度',
`latitude` double DEFAULT NULL COMMENT '维度',
`area` varchar(32) DEFAULT NULL COMMENT '区域',
`organ_category` varchar(32) DEFAULT NULL COMMENT '机构类⽬：医院资质、药店品牌',
`adCode` varchar(32) DEFAULT NULL COMMENT '区域编码',
`pyCode` varchar(32) DEFAULT NULL COMMENT '拼⾳⾸字母',
`organ_catagery` varchar(20) DEFAULT NULL COMMENT '机构类别，现在区分少⼉与成⼈。少⼉：children',
`dis` double DEFAULT NULL COMMENT '距离',
KEY `citycode` (`city_code`),
KEY `inscode` (`ins_code`) USING BTREE,
KEY `diS` (`dis`) USING BTREE
) ENGINE = InnoDB CHARACTER SET = utf8 COLLATE = utf8_bin COMMENT = '' ROW_FORMAT = Compact;
insert into tmp_table(
city_code,
ins_code,
organ_type,
organ_name,
organ_address,
organ_access_url,
longitude,
latitude,
area,
organ_category,
adCode,
pyCode,
organ_catagery,
dis )  SELECT
city_code,
ins_code,
organ_type,
organ_name,
organ_address,
organ_access_url,
longitude,
latitude,
area,
organ_category,
adCode,
pyCode,
organ_catagery,
ROUND(
6378.393 * 2 * ASIN(
SQRT(
POW(
SIN(
(
30.0
* PI() / 180 - latitude * PI() / 180
) / 2
),
2
) +
COS(30.0 * PI() / 180) * COS(latitude * PI()
/
180) * POW(
SIN(
(
130.0 * PI() / 180 - longitude
* PI() / 180
) / 2
),
2
)
)
) * 1000 * 1
)/1000 as dis
FROM
wh_institution
WHERE
city_code = 'SZ0755' and ins_code = 'SZHRSS'  and organ_type =                'P' and state = '1' ;
SELECT
city_code,
ins_code,
organ_type,
organ_name,
organ_address,
organ_access_url,
longitude,
latitude,
area,
organ_category,
adCode,
pyCode,
organ_catagery,
dis from tmp_table order by dis;
END
>mysql存储过程题目