Missing if in procedure
I did the following procedure, but in END//
appears 'missing if'
, does anyone know why?
William helped me with this problem, but whenever I try to register, the procedure only goes to the last else
.
I called this way:
call prc_cadastrar_usuario(18823123132, 'Jorgin', 'Alho', 'jorginalho', 'alho', @a);
DELIMITER //
drop procedure if exists prc_cadastrar_usuario //
CREATE PROCEDURE prc_cadastrar_usuario (
in n_cpf varchar(11),
in n_nome nvarchar(30),
in n_sobrenome nvarchar(30),
in n_usuario nvarchar(30),
in n_senha nvarchar(255),
out msg nvarchar(200)
)
BEGIN
declare v_cpf varchar(11);
declare v_nome nvarchar(30);
declare v_sobrenome nvarchar(30);
declare v_usuario nvarchar(30);
declare v_senha nvarchar(255);
-- seleciona no banco os dados que entraram
set v_cpf := (select cpf from tbl_usuario where cpf = n_cpf);
set v_usuario := (select usuario from tbl_login where usuario = n_usuario);
-- verifica se os dados da select são iguais aos que entraram
if ( (v_cpf <> n_cpf) && (v_usuario <> n_usuario) ) then
insert into tbl_usuario (cpf, nome, sobrenome) values (n_cpf, n_nome, n_sobrenome);
insert into tbl_login (usuario, senha, cpf_login_fk) values (n_usuario, n_senha, n_cpf);
set msg := 'Cadastrado!';
elseif ( (v_cpf = n_cpf) && (v_usuario <> n_usuario) ) then
set msg := 'CPF já cadastrado!';
elseif ( (v_cpf <> n_cpf) && (v_usuario = n_usuario) ) then
set msg := 'Usuário já cadastrado!';
else
set msg := 'Erro ao cadastrar';
end if;
END//
DELIMITER ;
Tables:
create table if not exists tbl_usuario(
cpf varchar(11) not null,
constraint cpf_pk primary key(cpf),
nome nvarchar(30),
sobrenome nvarchar(30)
);
create table if not exists tbl_login(
id_login int not null auto_increment,
constraint id_login_pk primary key(id_login),
usuario nvarchar(30),
senha nvarchar(255),
cpf_login_fk varchar(11) not null unique,
constraint cpf_usuario_login_fk foreign key (cpf_login_fk) references tbl_usuario on delete cascade on update cascade
);
0
1 answers
This is because NULL compared to something is always NULL. Reference
Let'S say the CPF is not registered, in this case the Query "select cpf from tbl_usuario where cpf = n_cpf" returns NULL and when you compare v_cpf n_cpf is NULL (neither false nor true) and v_cpf = n_cpf is NULL as well, then falls to else.
Here a solution:
set v_cpf := (select cpf from tbl_usuario where cpf = n_cpf);
set v_usuario := (select usuario from tbl_login where usuario = n_usuario);
if ( (v_cpf is null ) && (v_usuario is null) ) then
/*insere novo registro*/
elseif ( (v_cpf is not null ) && (v_usuario is null) ) then
/*'CPF já cadastrado!*/
elseif ( (v_cpf is null ) && (v_usuario is not null) ) then
/*'Usuario já cadastrado!*/
else
/* Usuario e CPF já cadastrados!*/
end if;
2
Author: William John Adam Trindade, 2018-07-06 19:23:40