Procedimientos Almacenados MYSQL

Procedimientos Almacenados MYSQL

===============================
-- Procedimiento Listar
DELIMITER $$

CREATE PROCEDURE sp_getAllCategories()

BEGIN

    SELECT id,descripcion,estado 

    FROM category

    ORDER BY id DESC;

END $$

DELIMITER ;

call sp_getAllCategories();

-- Procedimiento Buscar

delimiter $$
create procedure sp_findCategory(in text varchar(50))
begin
    select name,description
    from category
    where upper(trim(name))  or upper(trim(description)) like concat('%',upper(trim(text)),'%') and active=1

    order by name asc;

end $$

delimiter ;

delimiter $$

create procedure sp_findAllCategories()

begin

select id,name,description,active 

from category

order by name asc;

end $$

delimiter ;

delimiter $$

create procedure sp_insertCategory(in opt int, 

   in id int,

   in name varchar(50),

                                   in description varchar(255),

                                   in active int)

begin

if(opt=1) then

insert into category(name,descripcion)

values(name, description);

else

update category set name=name, description=description

where id=id;

end if;

end$$

delimiter ;

CALL sp_insertCategory(1,1,'bebidas','bebida inkcola', 1);

-- Procedimiento Eliminar

DELIMITER $$

CREATE PROCEDURE sp_deleteCategory(IN categoryId INT)

BEGIN

    DELETE 

    FROM category

    WHERE id = categoryId;

END $$

DELIMITER ;

CALL sp_deleteCategory(2);