How do I achieve the outcome of call check

Hello I want to achieve the outcome of call check_shipments;

Table Definitions:

How do I achieve the said storedprocedure with cursors using this code: Please tell me what should I change within the code

DELIMITER $$

CREATE PROCEDURE check_shipments()
BEGIN
DECLARE no_more_products int DEFAULT 0;
DECLARE p_id int DEFAULT 0;
DECLARE ship_times int DEFAULT 0;
DECLARE cur_shipments CURSOR FOR
    SELECT prod_id FROM shipments;
DECLARE CONTINUE HANDLER FOR NOT FOUND
    SET no_more_products = 1;
CREATE table IF NOT EXISTS products_Info(prod_id int, prod_shipments int);
OPEN cur_shipments;
FETCH cur_shipments INTO p_id;
REPEAT
    SELECT count(prod_id) INTO ship_times FROM shipments where prod_id = p_id;
    IF ship_times > 0 THEN
        INSERT INTO products_Info VALUES(p_id, ship_times);
    END IF;
    FETCH cur_shipments INTO p_id;
UNTIL no_more_products = 1 END REPEAT;
CLOSE cur_shipments;
SELECT * FROM products_Info;
drop table products_Info;
END $$

DELIMITER ;

But the outcome of my storedprocedure is like this:

Help is muchly appreaciated since I am still new to learning database stuff.

CodePudding user response：

You don't need a procedure for this. A query with a count() and a group by clause will give you what you want:

SELECT prod_id, count(prod_id) as prod_shipments 
  FROM shipments 
  group by prod_id 
  order by prod_id;

Result:

prod_id	prod_shipments
1	3
2	2

CodePudding user response：

We want to count the number of lines with each id so we use

 INSERT INTO products_Info VALUES(p_id, 1)
>                ON DUPLICATE KEY UPDATE 
>                prod_shipments = prod_shipments   1;

DELIMITER $$ 

CREATE PROCEDURE check_shipments()
BEGIN
DECLARE no_more_products int DEFAULT 0;
DECLARE p_id int DEFAULT 0;
DECLARE ship_times int DEFAULT 0;
DECLARE cur_shipments CURSOR FOR
    SELECT prod_id FROM shipments;
DECLARE CONTINUE HANDLER FOR NOT FOUND
    SET no_more_products = 1;
CREATE table IF NOT EXISTS products_Info(
       prod_id int primary key, 
       prod_shipments int);
OPEN cur_shipments;
FETCH cur_shipments INTO p_id;
REPEAT
    SELECT count(prod_id) INTO ship_times FROM shipments where prod_id = p_id;
    IF ship_times > 0 THEN
        INSERT INTO products_Info VALUES(p_id, 1)
           ON DUPLICATE KEY UPDATE 
           prod_shipments = prod_shipments   1;
    END IF;
    FETCH cur_shipments INTO p_id;
UNTIL no_more_products = 1 END REPEAT;
CLOSE cur_shipments;
SELECT * FROM products_Info;
drop table products_Info;
END $$

DELIMITER ;

call check_shipments

prod_id | prod_shipments
------: | -------------:
      1 |              3
      2 |              2

db<>fiddle here

CodePudding user response：

DECLARE cur_shipments CURSOR FOR SELECT prod_id FROM shipments;

loads 5 rows to cursor, you loop 4 times, but insert 5 times because first fetch is outside loop and since there is no primary/unique key on pid 5 rows are inserted..

You could

DECLARE cur_shipments CURSOR FOR SELECT DISTINCT prod_id FROM shipments;

so cursor only contains 2 rows

add primary key to products shipment drop the select (count)... statement and change the insert to an insert on duplicate key

 REPEAT
    
                INSERT INTO products_Info(prod_id ,prod_shipments) VALUES(p_id, 1)
    ON DUPLICATE key update prod_shipments = prod_shipments   1;
    
        FETCH cur_shipments INTO p_id;
    UNTIL no_more_products = 1 END REPEAT;