For each supplier(name) show the names of the items it supplies. EXERCISES CURSOR NESTED MySQL

Question

For each supplier(name) show the names of the items it supplies.

Files.sql

I happen to have the following data in my database

Database creation and deletion

DROP DATABASE IF EXISTS almacen;

CREATE DATABASE IF NOT EXISTS almacen;

Create and Add data to the table articulos

USE almacen;

CREATE TABLE articulos(
    cod_art VARCHAR(6) PRIMARY KEY,
    descripcion VARCHAR(45),
    precio FLOAT,
    stock INT,
    caducidad BOOLEAN,
    cod_prov VARCHAR(4),
    observaciones VARCHAR(45)
);

INSERT INTO articulos VALUES('DR-001','Lejia',0.80,18,FALSE,'A002','Muy Toxica'),
                            ('DR-002','Suavizante',2.40,10,FALSE,'A001','No volcar'),
                            ('DR-003','Quitamancha',5.00,18,FALSE,'B001','Muy Toxico'),
                            ('PA-001','Pan',1.00,35,TRUE,'B001','No tocar con las manos'),
                            ('PA-002','Palmerita',2.10,8,TRUE,'A001','Aplicar max 5 paquetes'),
                            ('PA-003','Napolitana',1.00,44,TRUE,'A002','Chocolate crema'),
                            ('VE-001','Lechuga',1.50,27,TRUE,'B002','No meter en la nevera'),
                            ('VE-002','Zanahoria',3.00,330,TRUE,'A001','No meter en la nevera');

Create and Add data to the clientes table

CREATE TABLE clientes(
    cod_cliente INT AUTO_INCREMENT PRIMARY KEY,
    dni VARCHAR(9),
    nombreCli VARCHAR(25),
    ap1 VARCHAR(35),
    ap2 VARCHAR(35),
    descuento INT
);

INSERT INTO clientes VALUES(null,'45098765G','Juan','Perez','Soler',5),
                           (null,'33456739H','Antonio','Garcia','Toro',8),
                           (null,'23478962S','Ramon','Gonzalez','Tamudo',0),
                           (null,'45765432F','Jesus','Duarte','Fortes',4),
                           (null,'29876098G','Gabriel','Lopez','Vazquez',2);

Create and Add data to the detallesFacturas

CREATE TABLE detallesFacturas(
    nFactura INT,
    cod_art VARCHAR(6),
    cantidad INT,
    desc_especial INT,
    PRIMARY KEY (nFactura,cod_art)
);

INSERT INTO detallesFacturas VALUES(6,'DR-003',5,2),
                                   (6,'PA-002',2,6),
                                   (7,'DR-003',4,0),
                                   (7,'DR-002',2,2),
                                   (8,'VE-002',3,5),
                                   (8,'PA-001',7,5),
                                   (1,'DR-001',2,2),
                                   (1,'PA-002',5,0),
                                   (1,'DR-002',10,4),
                                   (1,'VE-001',3,0),
                                   (2,'DR-002',2,4),
                                   (2,'VE-002',3,5),
                                   (2,'PA-001',2,2),
                                   (3,'PA-002',4,4),
                                   (3,'VE-002',1,3),
                                   (3,'DR-001',3,2),
                                   (4,'PA-001',2,0),
                                   (4,'PA-002',3,5),
                                   (4,'VE-002',2,2),
                                   (5,'VE-001',4,6),
                                   (5,'PA-001',3,3);

Create and Add data to the facturas

CREATE TABLE facturas(
    nFactura INT AUTO_INCREMENT PRIMARY KEY,
    nCliente INT,
    fecha DATE,
    forma_pago VARCHAR(20)
);

INSERT INTO facturas VALUES(null,3,'2007-10-10','Contado'),
                           (null,2,'2007-10-11','Contado'),
                           (null,5,'2007-10-11','Cheque'),
                           (null,1,'2007-10-12','Transferencia'),
                           (null,5,'2007-10-14','Contado'),
                           (null,1,'2007-10-16','Cheque'),
                           (null,2,'2007-10-16','Transferencia'),
                           (null,4,'2007-10-17','Contado');

Create and Add data to the proveedores

CREATE TABLE proveedores(
    cod_prov VARCHAR(4) PRIMARY KEY,
    nombre_prov VARCHAR(30),
    telefono VARCHAR(9),
    ciudad VARCHAR(20),
    descuento_1 INT,
    descuento_2 INT
);

INSERT INTO proveedores VALUES('A001','Distribuciones Sur','956525354','Ceuta',5,10),
                              ('A002','Todomasa SL','952875309','Malaga',3,6),
                              ('A003','La mancha pastelera','953709912','Jaen',15,20),
                              ('B001','Romelsa','912345762','Madrid',10,20),
                              ('B002','Gegatur SL','933988344','Barcelona',7,13);

Cursor.sql

USE almacen;
DELIMITER $$
DROP PROCEDURE IF EXISTS art $$
CREATE PROCEDURE art()
BEGIN

    DECLARE fin boolean;
    DECLARE c1_codProv VARCHAR(4);
    DECLARE c1_nomProv VARCHAR(30);
    DECLARE c2_Articulos VARCHAR(20);
    DECLARE cur_proveedor CURSOR FOR SELECT cod_prov,nombre_prov FROM proveedores;
    DECLARE cur_articulos CURSOR FOR SELECT descripcion FROM articulos WHERE cod_prov=c1_codProv;
    DECLARE CONTINUE HANDLER FOR NOT FOUND SET fin=true;
 
    SET fin=0;
    
    OPEN cur_proveedor;
    FETCH cur_proveedor INTO c1_codProv,c1_nomProv;
    WHILE fin=false DO
    
        SELECT c1_nomProv;
        FETCH cur_proveedor INTO c1_codProv,c1_nomProv;
        
        OPEN cur_articulos;
        FETCH cur_articulos INTO c2_Articulos;
        
        WHILE fin=false DO
        
            SELECT c2_Articulos;
            FETCH cur_articulos INTO c2_Articulos;
            
        END WHILE;
        
        SET fin = false;
        CLOSE cur_Articulos;
        FETCH cur_proveedor INTO c1_codProv,c1_nomProv;
        
     END WHILE;
     
     CLOSE cur_proveedor;
     
END $$
DELIMITER ;

call art();

And my cursor fails me because it does not return the expected result. For example DISTRIBUCIONES SUR should give me SUAVIZANTE, PALMERITA and ZANAHORIA.

RUN EXAMPLE

if we are just starting with procedures and functions and we recently saw this and it is required to be a nested cursor — Javier G.Raya, Apr 04 '22 at 08:40
'DISTRIBUCIONES SUR; - what's there isn't a select which references this. Also using the same handler for 2 cursors isn't going to be good if one cursor is shorter than the other. — P.Salmon, Apr 04 '22 at 08:40
Does this answer your question? [Nested Cursors in Mysql](https://stackoverflow.com/questions/9699896/nested-cursors-in-mysql) — P.Salmon, Apr 04 '22 at 08:42
does not answer it because I already know how it works, mine is an error of logic. — Javier G.Raya, Apr 04 '22 at 08:43
I understand that cursor is required. but as a secondary solution, you can use comma-separated with a simple select and get the same results. in case you guys decide to do something different. it can still be a Stored-procedure. — Demeteor, Apr 04 '22 at 08:50
DECLARE cur_articulos CURSOR FOR SELECT descripcion FROM articulos WHERE cod_prov=c1_codProv; silently fails.. — P.Salmon, Apr 04 '22 at 09:57

Javier G.Raya · Accepted Answer · 2022-04-04T11:40:23.383

1

I found my bug. It turns out that I had a fetch left over and that's why it cut me off and didn't give it to me correctly because it overwrote what I had before.

The fetch that was left over is the one that is commented on

USE almacen;
DELIMITER $$
DROP PROCEDURE IF EXISTS art $$
CREATE PROCEDURE art()
BEGIN

    DECLARE fin boolean DEFAULT FALSE;
    DECLARE c1_codProv VARCHAR(4);
    DECLARE c1_nomProv VARCHAR(30);
    DECLARE c2_Articulos VARCHAR(20);
    DECLARE cur_proveedor CURSOR FOR SELECT cod_prov,nombre_prov FROM proveedores;
    DECLARE cur_articulos CURSOR FOR SELECT descripcion FROM articulos WHERE cod_prov=c1_codProv;
    DECLARE CONTINUE HANDLER FOR NOT FOUND SET fin=true;
    
    OPEN cur_proveedor;
    FETCH cur_proveedor INTO c1_codProv,c1_nomProv;
    WHILE fin=FALSE DO
    
        SELECT c1_nomProv;
       # FETCH cur_proveedor INTO c1_codProv,c1_nomProv;
        
        OPEN cur_articulos;
        FETCH cur_articulos INTO c2_Articulos;
        
        WHILE fin=FALSE DO
        
            SELECT c2_Articulos;
            FETCH cur_articulos INTO c2_Articulos;
            
        END WHILE;
        
        SET fin = FALSE;
        CLOSE cur_Articulos;
        FETCH cur_proveedor INTO c1_codProv,c1_nomProv;
        
     END WHILE;
     SET fin = FALSE;
     CLOSE cur_proveedor;
     
END $$
DELIMITER ;

call art();

RUN EXAMPLE :

edited Apr 04 '22 at 11:40

answered Apr 04 '22 at 10:39

Javier G.Raya

230
1
3
15

Hello, could you accept your answer as the correct one so others can be help in similar cases? – Demeteor Apr 04 '22 at 11:58
1

yes, but I have to wait 48 hours. @Demeteor, when it comes to my question and I answer my question you have to wait 48 hours. – Javier G.Raya Apr 04 '22 at 11:59
okay, I did not know that! thanks for letting me know – Demeteor Apr 04 '22 at 12:03
you're welcome my friend :) – Javier G.Raya Apr 04 '22 at 12:04
I am baffled by this - I see that it works but DECLARE cur_articulos CURSOR FOR SELECT descripcion FROM articulos WHERE cod_prov=c1_codProv; does not populate the cursor since the condition is never true at this point and there is no other documented mechanism I am aware of for populating a cursor that I can find. If you have a reference for one please enlighten me. – P.Salmon Apr 04 '22 at 12:45
I was taught to declare an important variable for the first cursor and then reuse that variable filtering on the second cursor, this is how we were taught in class. – Javier G.Raya Apr 04 '22 at 12:53
In this case I filter by the code of the supplier which I put it through the fetch passes to the second cursor and reuses that primary key variable to filter. The more nested cursors there are, the easier it supposedly becomes because it is easier to see the error in the query. – Javier G.Raya Apr 04 '22 at 12:59
Looks like an undocumented feature ie I cannot find a reference to the process you describe in the mysql manual .--do you have a corroborating reference. – P.Salmon Apr 04 '22 at 13:15
I have the teacher's notes but it is in Spanish. – Javier G.Raya Apr 04 '22 at 13:44
1

No worries , I'll raise a question. – P.Salmon Apr 04 '22 at 15:09
@demeteoro I have already marked it as solved – Javier G.Raya Apr 06 '22 at 09:15