mysql 存储过程实例

来源：互联网发布：打轴用什么软件编辑：程序博客网时间：2024/05/18 03:04

需求描述

从T_COMP_PARAM表查询as_source_db为yes的所有不重复的ip:port

表结构

MySQL [dip]> desc T_COMP_PARAM;+-------------+--------------+------+-----+---------+-------+| Field       | Type         | Null | Key | Default | Extra |+-------------+--------------+------+-----+---------+-------+| COMP_ID     | varchar(32)  | YES  |     | NULL    |       || PARAM_NAME  | varchar(32)  | YES  |     | NULL    |       || PARAM_VALUE | varchar(255) | YES  |     | NULL    |       || PARAM_TYPE  | varchar(32)  | YES  |     | NULL    |       || VALID       | varchar(16)  | YES  |     | NULL    |       || INSERT_TIME | datetime     | YES  |     | NULL    |       || UPDATE_TIME | datetime     | YES  |     | NULL    |       || REMARK      | varchar(255) | YES  |     | NULL    |       |+-------------+--------------+------+-----+---------+-------+8 rows in set (0.00 sec)

示例数据

MySQL [dip]> select * from T_COMP_PARAM where comp_id = 'database_2';+------------+-------------------+------------------------+------------+-------+---------------------+-------------+--------+| COMP_ID    | PARAM_NAME        | PARAM_VALUE            | PARAM_TYPE | VALID | INSERT_TIME         | UPDATE_TIME | REMARK |+------------+-------------------+------------------------+------------+-------+---------------------+-------------+--------+| database_2 | component_name    | rkw                    | NORMAL     | YES   | 2017-06-22 15:41:09 | NULL        | NULL   || database_2 | db_password       | lhCq+9gj/tA=           | NORMAL     | YES   | 2017-06-22 15:41:09 | NULL        | NULL   || database_2 | db_user           | kylin                  | NORMAL     | YES   | 2017-06-22 15:41:09 | NULL        | NULL   || database_2 | db_type           | oracle                 | NORMAL     | YES   | 2017-06-22 15:41:09 | NULL        | NULL   || database_2 | db_port           | 1521                   | NORMAL     | YES   | 2017-06-22 15:41:09 | NULL        | NULL   || database_2 | db_ip             | 172.16.1.199           | NORMAL     | YES   | 2017-06-22 15:41:09 | NULL        | NULL   || database_2 | as_source_db      | yes                    | NORMAL     | YES   | 2017-06-22 15:41:09 | NULL        | NULL   || database_2 | db_id             | orcl                   | NORMAL     | YES   | 2017-06-22 15:41:09 | NULL        | NULL   || database_2 | db_connect_mode   | RAW                    | NORMAL     | YES   | 2017-06-22 15:41:09 | NULL        | NULL   || database_2 | encrypt_password  | yes                    | NORMAL     | YES   | 2017-06-22 15:41:09 | NULL        | NULL   || database_2 | db_connect_string | 172.16.1.199:1521/orcl | NORMAL     | YES   | 2017-06-22 15:41:09 | NULL        | NULL   |+------------+-------------------+------------------------+------------+-------+---------------------+-------------+--------+11 rows in set (0.00 sec)

创建存储过程

drop procedure if exists add_test;DELIMITER //CREATE PROCEDURE get_src_db_ip_port()BEGIN    DECLARE b VARCHAR(30);    DECLARE _comp_id VARCHAR(30);      DECLARE _ip VARCHAR(30) default '127.0.0.1';      DECLARE _port VARCHAR(30) default '1521';      DECLARE str VARCHAR(300);    declare tmp varchar(32);    DECLARE s int default 0;      DECLARE cursor_name CURSOR FOR select comp_id from T_COMP_PARAM where comp_id like 'database_%' and param_name = 'as_source_db' and param_value = 'yes';     DECLARE CONTINUE HANDLER FOR SQLSTATE '02000' SET s=1;      set str = "";    set tmp = "";    OPEN cursor_name;      fetch  cursor_name into b;      while s <> 1 do        select ip.comp_id, ip.ip, port.port        into _comp_id, _ip, _port        from         (select comp_id, PARAM_VALUE ip from T_COMP_PARAM where COMP_ID = b and PARAM_NAME = 'db_ip') as ip,         (select comp_id, PARAM_VALUE port from T_COMP_PARAM where COMP_ID = b and PARAM_NAME = 'db_port') as port         where ip.comp_id = port.comp_id;        set tmp =  concat(_ip, ':', _port);        if INSTR(str, tmp) = 0 then            set str = concat(str, tmp, ',');        end if;        fetch cursor_name into b;    END while;    CLOSE cursor_name ;    #set str = SUBSTRING(str, 1, LENGTH(str)-1);    #set str = left(str, length(str)-1);    select substr(str, 1, length(str)-1);  END;//DELIMITER ;

调用存储过程

call get_src_db_ip_port();

阅读全文

0 0