1
    select REPLACE(UUID(), '-','')

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
DROP FUNCTION IF EXISTS `F_GETJSON`;

DELIMITER ;;
CREATE DEFINER=`root`@`%` FUNCTION `F_GETJSON`() RETURNS text CHARSET utf8 COLLATE utf8_bin
BEGIN
  DECLARE jsonStr TEXT DEFAULT "";
SELECT
	CONCAT('{"apiList":[',
	GROUP_CONCAT(
		CONCAT('{"version":"',version,'"'),',',
		CONCAT('"name":"',name,'"'),
		CONCAT(',"desc":"',description),'"',
		CONCAT(',"type":"',type),'"',
		CONCAT(',"typeDesc":"',type_desc),'"',
		CONCAT(',"path":"',path),'"',
		CONCAT(',"requestSample":"',request_sample),'"',
		CONCAT(',"responseSample":"',response_sample),'"}')
,']}')
INTO jsonStr FROM api;
RETURN jsonStr;
END
;;
DELIMITER ;

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
DROP PROCEDURE IF EXISTS `SP_GETJSON`;
DELIMITER ;;
CREATE DEFINER=`root`@`%` PROCEDURE `SP_GETJSON`()
BEGIN
DECLARE v_json TEXT DEFAULT "";
DECLARE v_param TEXT DEFAULT "";
DECLARE v_api_id VARCHAR(50) DEFAULT "";
DECLARE v_name VARCHAR(500) DEFAULT "";
DECLARE v_version VARCHAR(500) DEFAULT "";
DECLARE v_desc VARCHAR(500) DEFAULT "";
DECLARE v_type VARCHAR(500) DEFAULT "";
DECLARE v_type_desc VARCHAR(500) DEFAULT "";
DECLARE v_path VARCHAR(500) DEFAULT "";
DECLARE v_request_sample VARCHAR(500) DEFAULT "";
DECLARE v_response_sample VARCHAR(500) DEFAULT "";
DECLARE v_finished INTEGER DEFAULT 0;
DECLARE name_cursor CURSOR FOR 
SELECT api_id, name, version, description, type, type_desc, path, request_sample, response_sample from api;

DECLARE CONTINUE HANDLER 
FOR NOT FOUND SET v_finished = 1;

set v_json = CONCAT(v_json,"{\"apiList\": [");

open name_cursor;

get_name: LOOP
	FETCH name_cursor into v_api_id,v_name,v_version,v_desc,v_type,v_type_desc,v_path,v_request_sample,v_response_sample;
	if v_finished = 1 THEN
	LEAVE get_name;
	END IF;

	set v_json = CONCAT(v_json,
		'{"version":"',IFNULL(v_version,""),'"',',',
		'"name":"',IFNULL(v_name,""),'"',
		',"desc":"',IFNULL(v_desc,""),'"',
		',"type":"',IFNULL(v_type,""),'"',
		',"typeDesc":"',IFNULL(v_type_desc,""),'"',
		',"path":"',IFNULL(v_path,""),'"');

	set v_json = CONCAT(v_json,
		',"requestSample":"',IFNULL(v_request_sample,""),'"',
		',"responseSample":"',IFNULL(v_response_sample,""),'"');

	SELECT
	CONCAT(',"paramList":[',
	GROUP_CONCAT(
		CONCAT('{"name":"',IFNULL(p.name,""),'"'),',',
		CONCAT('"desc":"',IFNULL(p.description,"")),'"}')
	,']')
	into v_param FROM param p where p.api_id = v_api_id;

	set v_json = CONCAT(v_json, IFNULL(v_param,""));
	set v_json = CONCAT(v_json,"},");


END LOOP get_name;

CLOSE name_cursor;

set v_json = CONCAT(v_json,"]}");

select v_json;

END
;;
DELIMITER ;