mysql - 如何使光标工作并填写列表，过程唯一参数的一行中的值

Question

我有一个使用游标的过程。它在表中查找 ID 和值。该过程有效，但仅输入单个值。我准备了一个临时表和一个变量，其中连接了临时表中的值。如何通过输入串联值使其工作？

表格如下所示:

(id_ct, no_ct, no_gr, no_per, zone_ID_zone) values
(1, CT31781, 0101, 1, 888888),
(2, CT31781, 0101, 2, 888888),
(3, CT31781, 0101, 3, 789),
(4, CT31780, 0101, 4, 888888),
(5, CT31780, 0101, 5, 888888),
(6, CT31780, 0101, 6, 788);

CREATE PROCEDURE `find_proo88` (in CTS varchar(255))
    begin
    Declare a int default 0;
    Declare i int default 0;
    DEClare b int default 0;
    declare c decimal  (4.2);
    DECLARE V_LISTA  VARCHAR(65000);
    declare v_user int;
    declare v_status varchar(20);
    declare ok int;

    DECLARE CURS cursor for
            SELECT CT.ID_CT, CT.zone_id_zone
            FROM  CT  
        where CT.NO_CT = CTS ;

        declare continue handler for not found
        begin
        set ok=1;
        end;

    open curs;
    START TRANSACTION;
        bucla :loop
            fetch curs into v_user , v_status;
            if ok=1 then leave bucla;
            end if;
            if v_status = "888888" then
            set a=a+1;
            else set i=i+1;
            end if;
        end loop bucla;
      COMMIT;   
      close curs;

        SET  b = a+i;
        set  c = (b-a)/b*100;
        SELECT  CONCAT_WS(" " , v_lista ,cts,"        Perechi Libere " , a ,  "           Perechi Ocupate ", i ,"        Total Perechi " , b  , "          Full ", CONCAT_WS("", c ," % ")) INTO V_LISTA;
    SELECT V_LISTA as "cutii terminale";


end;

Answer 1

我用一个简单的 View 解决了我的问题。 我没有使用光标。

create OR REPLACE view find_proo88 as
select id_ct,no_ct as "Numar Cutie Terminala" , no_gr as "Numar Grup", b.bl_name as "Bloc" , b.bl_sc as "Scara", b.bl_et_ct as "Etajul Cutiei Terminale", s.str_name as "Strada", s.str_no as "No",cl.Rank as Rank,
    count(*) Total,
        sum(case when CT.zone_id_zone = '888888' then 1 else 0 end) 'Perechi libere',
        sum(case when CT.zone_id_zone != '888888' then 1 else 0 end) 'Perechi Ocupate',
        CONCAT_WS(" " ,(TRUNCATE(avg((case when CT.zone_id_zone = '888888' then 1 else 0 end)*100),0) ),"%") 'Grup Liber',
        CONCAT_WS(" " ,(TRUNCATE(avg((case when CT.zone_id_zone != '888888' then 1 else 0 end)*100),0) ),"%") 'Grup Ocupat',
        sum(case when CL.Rank = 'Activ' then 1 else 0 end) 'Rank Activ',
        sum(case when CL.Rank != 'Activ' then 1 else 0 end) 'Rank Inactiv',
        CONCAT_WS(" " ,(TRUNCATE(avg((case when Rank = 'Activ' then 1 else 0 end)*100),0) ),"%") 'R Activ',
        CONCAT_WS(" " ,(TRUNCATE(avg((case when Rank != 'Activ' then 1 else 0 end)*100),0) ),"%")  'R Inactiv'
    from ct
join building b on b.id_build=ct.building_Id_build
join street s on s.id_str=b.street_id_str
join client cl on cl.street_id_str = s.id_str
group by no_ct