在mysql中进行验证后,创建一个带有某些输入参数的过程并稍后在db中插入时出错

gj3fmq9x  于 2021-06-18  发布在  Mysql
关注(0)|答案(2)|浏览(311)

我的问题是,我试图创建一个过程来接收输入参数并检查数据是否存在,以便能够插入到一个表或另一个表中。我尝试了不同的方法,但找不到声明语法中出现的错误。
拜托,我需要帮助。
万分感谢。
/接下来我将显示程序的代码/

DELIMITER //

CREATE PROCEDURE InsertMensaje(IN pNOMBRE varchar(50), IN pTLFN varchar(9), IN pEMAIL varchar(50), IN pASN varchar(15), IN pMSG varchar(500))

BEGIN

    /*declare valiable*/
    DECLARE vIDCLI int;

/*THESE OPTIONS I HAVE ALSO TESTED*/
/*SET vIDCLI = (select id_cliente from tb_cliente where email = pEMAIL);*/
/*select id_cliente into vIDCLI from tb_cliente where email = pEMAIL;*/

/* DECLARE vAUX varchar(50)*/ 
/* SET vAUX = (select email from tb_cliente where email = pEMAIL);*/
/* select email into vAUX from tb_cliente where email = pEMAIL; */

/* IF (vAUX <=> NULL) THEN  */

    /*check if the pEMAIL parameter exists*/
    IF EXISTS (select id_cliente from tb_cliente where email = pEMAIL) THEN

    /*retrieve the ID and assign it to the variable*/
    select id_cliente into vIDCLI from tb_cliente where email = pEMAIL;

        /*insert in BD menssage table*/
        INSERT INTO `tb_msg`(`asn`, `msg`, `id_cli`) VALUES ('pASN','pMSG','vIDCLI');

    ELSE
        /*insert new cliente*/
        INSERT INTO `tb_cliente`(`nombre`, `tlfn`, `email`) 
                        VALUES ('pNOMBRE','pTLFN','pEMAIL');

        /*recovernew IdCli*/
        SET vIDCLI = (select id_cliente from tb_cliente where email = pEMAIL);

        /*insert in message table*/
        INSERT INTO `tb_msg`(`asn`, `msg`, `id_cli`) VALUES ('pASN','pMSG',vIDCLI);
    END IF;
END; //

DELIMITER //

mysql曾说过:
1064-第25行“if exists(select id\u cliente from tb\u cliente where email=pemail)”附近的语法有问题

sczxawaw

sczxawaw1#

你不能使用 IF EXISTS 在存储过程中,当您试图这样做时。
在声明变量时 vIDCLI ,可以将其默认值设置为 null 现在,获取选择结果 Into 变量。
然后检查变量
vIDCLI IS NOT NULL (内部) if 条件)。
请尝试以下操作:

/*declare variable - default it to null*/
DECLARE vIDCLI int(11) DEFAULT NULL;

select id_cliente into vIDCLI 
from tb_cliente 
where email = pEMAIL;

/*check if the pEMAIL parameter exists*/
IF vIDCLI IS NOT NULL THEN

存储过程中还有其他错误。在修复它们之后,纠正后的程序如下所示:

DELIMITER //

CREATE PROCEDURE InsertMensaje(IN pNOMBRE varchar(50), IN pTLFN varchar(9), IN pEMAIL varchar(50), IN pASN varchar(15), IN pMSG varchar(500))

BEGIN

    /*declare variable - default it to null*/
    DECLARE vIDCLI int(11) DEFAULT NULL;

   select id_cliente into vIDCLI 
   from tb_cliente where email = pEMAIL;

/* DECLARE vAUX varchar(50)*/ 
/* SET vAUX = (select email from tb_cliente where email = pEMAIL);*/
/* select email into vAUX from tb_cliente where email = pEMAIL; */

/* IF (vAUX <=> NULL) THEN  */

    /*check if the pEMAIL parameter exists*/
    IF vIDCLI IS NOT NULL THEN

        /*insert in BD menssage table*/
        INSERT INTO `tb_msg`(`asn`, `msg`, `id_cli`) VALUES ('pASN','pMSG','vIDCLI');

    ELSE
        /*insert new cliente*/
        INSERT INTO `tb_cliente`(`nombre`, `tlfn`, `email`) 
                        VALUES ('pNOMBRE','pTLFN','pEMAIL');

        /*recovernew IdCli*/
        select id_cliente into vIDCLI 
        from tb_cliente where email = pEMAIL;

        /*insert in message table*/
        INSERT INTO `tb_msg`(`asn`, `msg`, `id_cli`) VALUES ('pASN','pMSG',vIDCLI);
    END IF;
END//

DELIMITER ;
5jvtdoz2

5jvtdoz22#

虽然我不是db开发人员,但您可能可以这样做(这只是一个想法,确切的语法可能不同!):

select count(id_cliente) into cnt_clients from tb_cliente where email = pEMAIL;
IF cnt_clients > 0 /* EXISTS case */
...
ELSE /* NOT EXISTS case */

相关问题