Procedure with IF NOT EXISTS

Asked

Viewed 1,434 times

4

Hello stackoverflow developers, I started to delve into sql commands a little while ago, I have some stupid error of syntax of this project, but for countless attempts, I could not Scan it, follow the code, I would be very grateful if you can help me.

DELIMITER $$

CREATE procedure Inserir_Disciplina(@disciplina_nome VARCHAR(45) CHARSET UTF8)
BEGIN
IF NOT EXISTS (SELECT * FROM tcc.disciplina WHERE disciplina_nome = @disciplina_nome)
THEN
    INSERT INTO tcc.disciplina(disciplina_nome) 
     VALUES(@disciplina_nome); 
END IF
END$$
DELIMITER ;

Optionally as would this Procedure with an Else?

After Attempts and Mistakes...

I edited the program for the following form, as the friend Christian Passold recommended, is the code half mouth, if help someone:

DELIMITER $$

CREATE PROCEDURE Inserir_Disciplina(IN p_disciplina_nome VARCHAR(45) CHARSET UTF8)
BEGIN
DECLARE numero_de_rows INT DEFAULT (SELECT count(*) FROM tcc.disciplina WHERE disciplina_nome = @disciplina_nome);

  IF(numero_de_rows = 0) THEN
        INSERT INTO tcc.disciplina(disciplina_nome) 
         VALUES(p_disciplina_nome);
    SELECT "Disciplina inserida com sucesso";
    ELSE 
      SELECT "Erro, disciplina ja existente no banco de dados!";
    END IF;

END$$
DELIMITER ;

1 answer

6


DELIMITER $$

Delete the file if it exists, only from security, after creating again.

DROP PROCEDURE IF EXISTS Inserir_Disciplina $


CREATE PROCEDURE Inserir_Disciplina(p_disciplina_nome VARCHAR(45))
BEGIN

You can create a variable containing the number of select records

    DECLARE v_select INT DEFAULT (SELECT count(*) FROM tcc.disciplina WHERE disciplina_nome = p_disciplina_nome);

So, check if it is greater than 0, if it is, inserts.

    IF(v_select > 0) THEN
        INSERT INTO tcc.disciplina(disciplina_nome) 
         VALUES(p_disciplina_nome);
    ELSE 
       #Do something here
    END IF;

END$$
DELIMITER ;

I hope I helped. D

  • Friend, as I debug in a previous, for when I call it return a message like "inserted successfully", or "gave error", something like this... ?

  • Dude, you have to do a TRANSACTION. I made an example for you: http://ideone.com/WxrIA8

Browser other questions tagged

You are not signed in. Login or sign up in order to post.