7mVHQGkV

1DELIMITER //
2CREATE
3PROCEDURE COMPUTADORES()
4BEGIN
5  DECLARE
6    bDone INTEGER(11) ;
7    DECLARE id INTEGER(11) ;
8    DECLARE var DOUBLE(10,2);
9    DECLARE curs CURSOR FOR
10  SELECT
11    ID_COMPUTADOR_DESC
12  FROM
13    tb_computador_desc
14  ORDER BY
15    1 ASC ; 
16    DECLARE CONTINUE
17  HANDLER FOR NOT FOUND
18SET
19  bDone = 1 ;
20DROP TEMPORARY TABLE IF EXISTS
21  tblResults ;
22CREATE TEMPORARY TABLE IF NOT EXISTS tblResults(
23  ID_COMPUTADOR_DESC INTEGER(11),
24  CONTEUDO VARCHAR(200),
25  GARANTIA INTEGER(11),
26  URL_IMG VARCHAR(200),
27  VALOR DECIMAL(10, 2)
28) ; OPEN curs ;
29SET
30  bDone = 0 ; REPEAT FETCH curs
31INTO
32  id;
33SET var = (SELECT SUM(TH.VALOR) * 1.2 VALOR FROM TB_COMPUTADOR TC INNER JOIN TB_HARDWARE TH ON TC.FKID_HARDWARE = TH.ID_HARDWARE WHERE FKID_COMPUTADOR_DESC = @id GROUP BY TC.FKID_COMPUTADOR_DESC);
34INSERT
35INTO
36  tblResults(ID_COMPUTADOR_DESC, CONTEUDO, GARANTIA, URL_IMG, VALOR)
37VALUES(
38SELECT
39  ID_COMPUTADOR_DESC,
40  CONTEUDO,
41  GARANTIA,
42  URL_IMG,
43  @var
44FROM
45  TB_COMPUTADOR_DESC
46WHERE
47  ID_COMPUTADOR_DESC = @id
48); UNTIL bDone
49END REPEAT; 
50CLOSE curs ;
51SELECT
52  *
53FROM
54  tblResults ;
55END //
56delimiter ;
57	
58TB_COMPUTADOR_DESC
59ID_COMPUTADOR_DESC  int(11) NO  PRI     auto_increment  
60CONTEUDO    varchar(500)    YES             
61GARANTIA    varchar(45) YES             
62URL_IMG varchar(150)    YES             
63FKID_CLASSE int(11) NO  MUL         
64
65TB_COMPUTADOR
66FKID_COMPUTADOR_DESC    int(11) NO  MUL         
67FKID_HARDWARE   int(11) YES MUL 
68
69TB_HARDWARE
70ID_HARDWARE int(11) NO  PRI     auto_increment  
71NOME_HARDWARE   varchar(45) YES             
72BENCHMARK   int(11) YES             
73VALOR   double  YES             
74DESCRICAO   varchar(1000)   YES             
75FKID_CAT_HARDWARE   int(11) NO  MUL         
76URL_IMG varchar(45) YES