mysql - registerWithCredentials() 存储过程

Question

我已经在 MysQL 中为我的 Wordpress 数据库创建了一个存储过程。此过程的目的是使用凭据(user_login,user_pass,user_nicename,user_email，和显示名称。)这个过程的 sql 语法没问题，但是当我调用这个过程时，我无法得到任何响应，一些东西会告诉新用户是否被添加到数据库中。我不知道问题出在哪里，因为当我在调用该过程后检查数据库时，我没有看到任何更改。

-- --------------------------------------------------------------------------------
-- Routine DDL
-- Note: comments before and after the routine body will not be stored by the server
-- --------------------------------------------------------------------------------
DELIMITER $$

CREATE DEFINER=`root`@`127.0.0.1` PROCEDURE `registerWithCredentials`(IN in_userlogin VARCHAR(50), IN in_userpass VARCHAR(32),IN in_usernicename VARCHAR(50) ,IN in_useremail VARCHAR(50), IN in_displayname VARCHAR(50))
proc_main:BEGIN
BEGIN
DECLARE ID int(10);
DECLARE CheckExists INT;

SET CheckExists=0;

select count(*) into CheckExists from wp_users where user_login like in_userlogin or user_email like in_useremail;
if (CheckExists>0)
then 

  leave proc_main;
end if;


if (in_userpass is not null or in_userpass !='') and (in_useremail is not null or in_useremail !='')
then
  insert into prod_huselbrand_db.wp_users (
    user_login
    ,user_pass
    .user_nicename
    ,user_email
    ,display_name
  ) VALUES (
    in_userlogin  -- username - IN varchar(50)
    -- email - IN varchar(50)
    ,MD5(in_userpass)   -- passwd - IN blob , 
   ,in_usernicename
,in_useremail
,in_displayname
  );
else
  leave proc_main;
end if;
set ID:=LAST_INSERT_ID(); 

end;
END proc_main

Answer 1

我认为您需要在代码末尾进行选择，以便可以从存储过程中输出一些内容。在你的情况下，我会输出新注册用户的数据。例如:

-- --------------------------------------------------------------------------------
-- Routine DDL
-- Note: comments before and after the routine body will not be stored by the server
-- --------------------------------------------------------------------------------
DELIMITER $$

CREATE DEFINER=`root`@`127.0.0.1` PROCEDURE `registerWithCredentials`(IN in_userlogin VARCHAR(50), IN in_userpass VARCHAR(32),IN in_usernicename VARCHAR(50) ,IN in_useremail VARCHAR(50), IN in_displayname VARCHAR(50))
proc_main:BEGIN
BEGIN
DECLARE ID int(10);
DECLARE CheckExists INT;

SET CheckExists=0;

select count(*) into CheckExists from wp_users where user_login like in_userlogin or user_email like in_useremail;
if (CheckExists>0)
then 

  leave proc_main;
end if;


if (in_userpass is not null or in_userpass !='') and (in_useremail is not null or in_useremail !='')
then
  insert into prod_huselbrand_db.wp_users (
    user_login
    ,user_pass
    .user_nicename
    ,user_email
    ,display_name
  ) VALUES (
    in_userlogin  -- username - IN varchar(50)
    -- email - IN varchar(50)
    ,MD5(in_userpass)   -- passwd - IN blob , 
   ,in_usernicename
,in_useremail
,in_displayname
  );
else
  leave proc_main;
end if;
set ID:=LAST_INSERT_ID();

select ID, user_login, user_pass, user_nicename, user_email, display_name 
      from prod_huselbrand_db.wp_users where ID = ID;

end;
END proc_main