Tanned system with Stored Procedure

Question

Tanned system with Stored Procedure

Navigation

#1 by (1 votes)

0

I'm trying to make a stored procedure for a simple tanned system, my table is organized like this:

Tanned

id INT (11) - Auto Increment
post INT (11) - foreign key (id - post)
user INT (11) - foreign key (id - users)

Users

id INT (11) - Auto Increment
name VARCHAR (15)

Post

id INT (11) - Auto Increment
content VARCHAR (200)

My logic that I'm trying to apply:

@valor: 1 = liked, 2 = did not like
@post: post id
@user: user id

1. If there is no value that matches the id of the user and the id id it creates one and appends the value to the type.

2. If it exists and the type is equal to the value sent it deletes.

3. If it exists and the type is different from the value, it updates.

CREATE DEFINER='root'@'localhost' PROCEDURE 'Teste'(
    IN '@valor' INT,
    IN '@post' INT,
    IN '@user' INT
)
IF NOT EXISTS (SELECT * FROM curtida WHERE user = '@user' AND  post = '@post')
    BEGIN
        INSERT INTO curtida (tipo, post, user) VALUES ('@valor', '@post', '@user');
    END
ELSE    
    IF (SELECT tipo FROM curtida WHERE user = '@user' AND post = '@post') = '@valor'
        BEGIN
            DELETE FROM curtida WHERE user = '@user' AND post = '@post';
        END
    ELSE
        BEGIN
            UPDATE FROM curtida SET tipo = '@valor' WHERE user = '@user' AND post = '@post';
        END

sql innodb stored-procedures mariadb

asked by anonymous 26.10.2018 / 23:02

1 answer

Select from column B if the field selected in column A is null Go through color gradient according to the value

score 1 · Accepted Answer

The main error would be the BEGIN and END you were using in IF .

You can view the here documentation.

How it was:

 DELIMITER $$
 CREATE DEFINER = 'root'@'localhost' PROCEDURE 'Teste'(IN '@valor' INT, IN '@post'  INT,IN '@user'  INT)
 BEGIN
     IF NOT EXISTS(SELECT * FROM curtida WHERE user = '@user' AND post = '@post') THEN
         INSERT INTO curtida (tipo, post, user) VALUES ('@valor', '@post', '@user');
     ELSEIF ((SELECT tipo FROM curtida WHERE user = '@user' AND post = '@post') = '@valor') THEN
             DELETE FROM curtida WHERE user = '@user' AND post = '@post';
     ELSE
         UPDATE curtida SET tipo = '@valor' WHERE user = '@user' AND post = '@post';
     END IF;
 END $$
 DELIMITER ;