BASES DE DATOS CASO PRACTICO

mysql> use casopractico

Database changed

mysql> create table ventas(vendedor int(11), producto int(11), importe float);

Query OK, 0 rows affected (0.16 sec)

mysql> create table comisiones (vendedor int(11), comision float);

Query OK, 0 rows affected (0.16 sec)

mysql> DELIMITER $$

mysql> DROP PROCEDURE IF EXISTS `test`.`sp_comisiones`$$

Query OK, 0 rows affected, 1 warning (0.05 sec)

mysql> CREATE PROCEDURE `test`.`sp_comisiones` (IN mivendedor INT)

-> BEGIN

-> DECLARE micomision INT DEFAULT 0;

-> DECLARE suma INT;

-> DECLARE existe BOOL;

-> select IFNULL(sum(importe),0) into suma from ventas where producto = 1 and vendedor = mivendedor;

-> SET micomision = micomision + (suma * 0.15);

-> select IFNULL(sum(importe),0) into suma from ventas where producto = 2 and vendedor = mivendedor;

-> SET micomision = micomision + (suma * 0.1);

-> select IFNULL(sum(importe),0) into suma from ventas where producto = 3 and vendedor = mivendedor;

-> SET micomision = micomision + (suma * 0.2);

-> select count(1)>0 into existe from comisiones where vendedor = mivendedor;

-> ifexiste then

-> UPDATE comisiones set comision = comision+micomision where vendedor = mivendedor;

-> else

-> insert into comisiones (vendedor, comision) values (mivendedor, micomision);

-> end if;

-> END$$

ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'then

UPDATE comisiones set comision = comision+micomision where vendedor = miven' at line 13

mysql> DELIMITER ;

mysql> delimiter //

mysql> drop procedure if exists comi//

Query OK, 0 rows affected, 1 warning (0.00 sec)

mysql> create procedure comi (in mivendedor int)

-> begin

-> declare micomision int default 0;

-> declare suma int;

-> declare existe bool;

-> select ifnull(sum(importe),0) into suma from ventas where producto =1 and vendedor=mivendedor;

-> set micomision =micomision + (suma*0.15);

-> select ifnull(sum(importe),0) into suma from ventas where producto =2 and vendedor=mivendedor;

-> set micomision =micomision + (suma*0.1);

-> select ifnull(sum(importe),0) into suma from ventas where producto =3 and vendedor=mivendedor;

...