是否可以将变量传递给mysql中的存储过程调用? [英] Is it possible to pass a variable to a stored procedure call in mysql?

问题描述

我正在尝试执行以下操作，并且想知道在MySQL中是否可行:

I am trying to do the following and I am wondering if that is possible in MySQL:

CREATE PROCEDURE `sp_test`(
  ulon int(4),
  usrname varchar(20),
  usrst varchar(20),
  usrdt varchar(10)
)
BEGIN
  DECLARE bid int(11);
START TRANSACTION; 
  SELECT t_bid INTO bid FROM generalb WHERE dt=usrdt;
INSERT 
  INTO 
    rslt
    (
       rbid,
       rusrname,
       rusrst,
       usrdt 
    ) 
  VALUES
    (
      bid,
      usrname,
      usrst,
      usrdt
    );
    call sp_nextproc_13(bid, ulon);
COMMIT;
END;

看这一行:

call sp_nextproc_13(bid, ulon);

我如何实现这样的目标:

How could I achieve something like this:

致电sp_nextproc_ @ bid(bid，ulon);

call sp_nextproc_@bid(bid, ulon);

我需要能够动态调用过程，因为在获得出价之前我不知道过程的名称.如果有人知道答案，非常感谢

I need to be able to call a procedure dynamically as I do not know the name of the procedure until I get the bid. If anyone knows the answer, thanks so much

最新更新:

我进行了以下更改:

CREATE PROCEDURE `sp_test`(
      ulon int(4),
      usrname varchar(20),
      usrst varchar(20),
      usrdt varchar(10)
    )
    BEGIN
      DECLARE bid int(11);
    START TRANSACTION; 
      SELECT t_bid INTO bid FROM generalb WHERE dt=usrdt;
    INSERT 
      INTO 
        rslt
        (
           rbid,
           rusrname,
           rusrst,
           usrdt 
        ) 
      VALUES
        (
          bid,
          usrname,
          usrst,
          usrdt
        );

        SET @sql=concat('call sp_nextproc_',bid,'(?,?)');
        PREPARE stmt FROM @sql;
        SET @var1=bid;
        SET @var2=ulon;
        EXECUTE stmt USING @var1, @var2;
    COMMIT;
    END;

如果我对某些值进行了硬编码，则可以正常工作.但是，如果我不这样做，它就不会.请参见以下内容:

IF I hardcode some values, it works. However if I don't, it doesn't. Please see below:

如果我打电话:

呼叫sp_test(2，'John'，'test'，'AAAA');

CALL sp_test(2, 'John','test','AAAA');

此处错误:

SELECT t_bid INTO bid FROM generalb WHERE dt=usrdt;

列t_bid不能为空

如果我像这样对值进行硬编码:

If I hardcode the value like this:

SELECT t_bid INTO bid FROM generalb WHERE dt='AAAA';

错误消失.

第二个错误在以下行:

        SET @var1=bid;
        SET @var2=ulon;
        EXECUTE stmt USING @var1, @var2;

错误缺少值@ var1和@ var2.

如果我对var1和var2进行硬编码，那么它将起作用:

If I hardcode var1 and var2, it works:

        SET @var1=13;
        SET @var2=2;
        EXECUTE stmt USING @var1, @var2;

我尝试使用@，并且在变量上不使用@，但这没有用.我做错了什么? :(

I tried using @ and without it on the variables but that didn't work. What I am doing wrong? :(

推荐答案

    delimiter $$
    create procedure sp_test
    (
    ulon int(4)
    )
    BEGIN
        DECLARE bid int(11);
        set bid=8888;
...
...
...
...
        set @sql=concat('call sp_nextproc_',bid,'(?,?)');
        prepare stmt from @sql;
        set @var1=bid;
        set @var2=ulon;
        EXECUTE stmt using @var1,@var2;
    END
    $$
    -- ------------
    delimiter $$
    CREATE PROCEDURE `sp_nextproc_8888`(
    IN bid int(11),
    IN ulon int(4)
    )
    BEGIN
        select bid,ulon;
    END
    $$

---

-- test with these:
-- call sp_test(9999);
-- call sp_nextproc_8888(111,222);

这篇关于是否可以将变量传递给mysql中的存储过程调用?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！