BASES DE DATOS CASO PRACTICO
Enviado por miguelito.000 • 31 de Enero de 2013 • 775 Palabras (4 Páginas) • 766 Visitas
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;
-> set micomision =micomision + (suma*0.2);
-> select count(1)>0 into existe from comisiones where vendedor=mivendedor;
-> if existe
...