I am trying to get an output such as (A – PC 1001 for $2114)by creating a cursor to select from a view I created. "A" is the maker "1001" is the model and "$2114" is the price. I think I am having issues saving them in the loop but I am not sure. I am also curious how to access the model from the view since it is (a.model) but trying to figure out how to get cursor working first. Was having issues declaring the variable and accessing the alias I think. The concat statement is incomplete because I was having issues with other variables.

This is an example of a view I am trying to access with the cursor.

CREATE VIEW PCView
AS SELECT maker, a.model, speed, ram, hd, price
FROM PC a, Product b
WHERE a.model=b.model;

Output

 ----------------------------------------------------------- 
| @PCList                                                                                                               
|
 ----------------------------------------------------------- 
| - PCfor 0- PCfor 0- PCfor 0- PCfor 0- PCfor 0- PCfor 0- PCfor 0- PCfor 0- 
PCfor 0- PCfor 0- PCfor 0- PCfor 0- PCfor 0 |
 ----------------------------------------------------------- 

CodePudding user response：

In Mysql the procedure works, if i fix some problems

CREATE TABLe PC(maker varchar(10), model varchar(10))

INSERT INTO PC VALUES('Me','pc1')

CREATE TABLE Product(model varchar(10), price decimal(10,2))

INSERT INTO Product VALUES('pc1',10.2)

CREATE VIEW PCView
AS SELECT maker, a.model, price
FROM PC a JOIN Product b
ON a.model=b.model;

CREATE PROCEDURE createPCList (
    INOUT PCList varchar(4000)
)
 BEGIN
     DECLARE finished INTEGER DEFAULT 0;
     DECLARE _maker VARCHAR(100) DEFAULT "";
     DECLARE _price INTEGER DEFAULT 0;
-- declare cursor for PCView
     DECLARE curPC CURSOR FOR SELECT maker, price FROM PCView;

-- declare NOT FOUND handler
    DECLARE CONTINUE HANDLER 
        FOR NOT FOUND SET finished = 1;
SET PCList = '';
    OPEN curPC;

    getPC: LOOP
        FETCH curPC INTO _maker,_price;

        IF finished = 1 THEN 
            LEAVE getPC;
        END IF;
        -- build list
        SET PCList = CONCAT(_maker,"- PC ", "for ", _price, PCList);

    END LOOP getPC;
    CLOSE curPC;

END

call createPCList(@pclist)

SELECT @pclist

SELECT * FROM PCView

fiddle