接受用户输入并显示与结果相同的 MySQL 程序 - 不工作 [英] MySQL procedure to accept user input and to show same as result-Not working

问题描述

我一直在努力编写接受用户输入并显示相同结果的 MySQL 存储过程.这是我的示例代码...在下面的代码中，我已经声明了局部变量并将其显示给用户作为第一步.

I have been struggling in writing MySQL stored procedure that accepts user input and shows the same as result. Here is my sample code... In below code I have declared local variables and displaying that back to user as first step.

DELIMITER $$
CREATE PROCEDURE USER_INPUT() 
BEGIN
    declare TITLE VARCHAR(20);
    declare SEVERITY INT;
    declare OPENDATE DATETIME;
    declare CLOSEDATE DATETIME;    
    SET TITLE = 'ABC';
    SET SEVERITY = 1;
    SET OPENDATE = ADDDATE(current_timestamp, -31);
    SET CLOSEDATE =  current_timestamp;
    SELECT 'You Entered '|| TITLE || ' ' || SEVERITY || ' ' || OPENDATE || ' ' || CLOSEDATE;
    SELECT 'You Entered '|| TITLE;
    INSERT INTO BUGS(TITLE, SEVERITY, OPENDATE, CLOSEDATE) VALUES(TITLE, SEVERITY, OPENDATE, CLOSEDATE);
END $$
DELIMITER ;

Invoking as below...
DELIMITER $$
 BEGIN
  CALL USER_INPUT();
 END;
DELIMITER ;

create table `bugs` (
    ID INT(11) NOT NULL AUTO_INCREMENT PRIMARY KEY,
    TITLE VARCHAR(20) NOT NULL,
    SEVERITY INT NOT NULL check(SEVERITY>0 && SEVIRITY<5),
    OPENDATE DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    CLOSEDATE DATETIME default NULL
 )ENGINE=MyISAM DEFAULT CHARSET=latin1;

<小时>

观察...

---

Observation...

1.存储过程运行的输出不符合预期.附上输出屏幕截图.

1.The output of stored procedure run is not as expected. Output screen shot attached.

1. 我试图修改程序，以便它接受用户输入...CREATE PROCEDURE USER_INPUT(@TITLE VARCHAR(20), @SEVERITY INT, @OPENDATE DATETIME, @CLOSEDATE DATETIME)

上面是第一行本身会导致编译错误.即我不确定语法.

The above is very first line itself results in compilation error. i.e. i am unsure about syntax.

1. 虽然输出并不如预期的那样令人惊讶，但插入查询已正确完成工作(验证插入的行具有正确的值)

请建议更改过程代码，以便它接受用户输入(而不是在过程中设置值)并将结果显示给用户.谢谢.

Please suggest changes in the procedure code so that it accepts user input(instead of setting the values inside the procedure) and shows the result back to the user. Thanks.

推荐答案

select 语句正在创建一个奇怪的转义序列，它无意提供您希望的输出.部分原因是您可能尝试做的事情的分离被搞砸了.例如，如果您尝试使用文本创建标题或实际输出变量.查看 concat() 可能取决于您的需要.

The select statement is creating a bizarre escape sequence and it has no intention of providing your output as you wish. Partly because the separations of what you may be trying to do are messed up. Such as, if you are trying to create a header with text or actually output your variables. Look into concat() maybe depending on your needs.

在此处将其拉回 50K 英尺.即使这样做，也不要陷入困境.存储过程并不主要用于用户输入和报告编写(阅读:请不要为此使用它们).所以也许你试图错误地使用它们！

Pulling it back up to 50K feet here. Don't get too bogged down with even doing that. Stored Procs are not mean for User Input and Report Writing primarily (Read: Please don't use them for that). So perhaps you are trying to use them wrong!

接下来，禁止在调用存储过程时使用用户变量(带有 @ 的变量)作为参数，因为您在文本中显示了您称之为 #2 的内容.

Next, the use of User Variables (ones with an @) in calls to the stored proc as parameters is forbidden as you were showing in the text for what you called #2.

此外，您不需要阻止如此复杂的测试调用.请记住，分隔符仅用于阻止您创建存储过程的调用.您不需要为测试调用编写这样的代码.它只会是:

Also, you don't need to block off your test call so complicated. Remember that the delimiter is only useful for blocking off your call to create the stored proc. You don't need to code like that for your test call. It would merely be:

CALL USER_INPUT();

所以，这将是 5 行代码减少到 1 行.

So, that would be 5 lines of code reduced to 1.

DROP PROCEDURE IF EXISTS USER_INPUT;
DELIMITER $$
CREATE PROCEDURE USER_INPUT
(   TITLE VARCHAR(20),
    SEVERITY INT,
    SomeDateNotUsed DATETIME -- not used
) 
BEGIN
    DECLARE OPENDATE DATETIME;
    DECLARE CLOSEDATE DATETIME;

    SET TITLE = 'ABC';
    SET SEVERITY = 1;
    SET OPENDATE = ADDDATE(current_timestamp, -31);
    SET CLOSEDATE =  current_timestamp;
    SET @str=CONCAT(TITLE,'||',SEVERITY,'||',OPENDATE,'||',CLOSEDATE);
    INSERT INTO BUGS(TITLE, SEVERITY, OPENDATE, CLOSEDATE) VALUES(TITLE, SEVERITY, OPENDATE, CLOSEDATE);
    SELECT @str as 'You Entered';
END $$
DELIMITER ;

测试:

CALL USER_INPUT('a',1,'2016-05-14');
+--------------------------------------------------+
| You Entered                                      |
+--------------------------------------------------+
| ABC||1||2016-06-28 17:05:58||2016-07-29 17:05:58 |
+--------------------------------------------------+

这篇关于接受用户输入并显示与结果相同的 MySQL 程序 - 不工作的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！