如何在MySQL中调用程序? [英] How to CALL a PROCEDURE in MySQL?

问题描述

我已经开始研究MySQL中的过程，但是我所有的努力都没有用.这是我的过程创建:

I've started to examine Procedures in MySQL, but all my efforts don't work. Here is my Procedure Creation:

DELIMITER //
CREATE PROCEDURE test(IN a INT)
BEGIN
    SELECT * 
    FROM `table`
    WHERE `id` = a;
END

MySQL返回OK，没有错误.

MySQL returns O.K., no errors.

 DELIMITER ;

MySQL返回OK，没有错误.

MySQL returns O.K., no errors.

但是CALL语句不起作用:

But CALL-Statement does not work:

CALL test(8);

返回错误: #1312-步骤dbxyz.test无法在给定上下文中返回结果集

Returns an error: #1312 - PROCEDURE dbxyz.test can't return a result set in the given context

现在，我不知道自己做错了什么:过程计数错误或调用语句错误.

Now, I don't know what I've made wrong: a mistake in Procedure-Cration or an error in CALL-Statement.

-> ##########################

--> ##########################

截至2014年2月6日: 今天，我试图找出为什么我的存储过程在phpMyAdmin的查询窗口中不起作用. "SELECT *"在存储过程中不起作用，但逐列的SELECT起作用.这是我发现的结果:使用IN和OUT以及INTO.示例:

As of 6th Feb. 2014: Today, I've tried to find out, why my Stored Procedure does not work in Query Window of phpMyAdmin. A 'SELECT *' does not work within a Stored Procedure, but a SELECT column by column does work. Here is what I have found out: Use IN and OUT as well as INTO. Example:

 DELIMITER //
 CREATE PROCEDURE test(IN a INT, OUT b VARCHAR(12), OUT c INT)
 BEGIN
   SELECT b, c
   FROM `table`
   WHERE `id` = a
   INTO b, c;
 END

现在，此存储过程可以在phpMyAdmin中运行:

Now, this stored Procedure is able to run in phpMyAdmin:

 call test(5, @result1, @result2);
 SELECT @result1, @result2

如果仅需要两个或两个以上的结果实例，则也可以使用SET-语句.我们在这里:

If you just need ONE result instad of two or more, you could also use SET - Statement. Here we are:

   DELIMITER //
   CREATE PROCEDURE test(IN a INT, OUT b INT)
   BEGIN
        SET b = ( SELECT b
        FROM table
        WHERE id = a );
   END

推荐答案

另请参见上面的评论:

今天，我试图找出为什么我的存储过程不起作用. "SELECT *"在存储过程中不起作用，但是在SELECT中起作用 逐列工作.这是我发现的:DELIMITER //创建过程测试(IN in INT，OUT b VARCHAR(12)，OUT c INT)开始 从表WHERE id = a INTO b，c中选择b，c;结束现在我可以运行我的 过程:调用test(5，@ result1，@ result2);选择@ result1，@ result2 –彼得，2014年2月6日，11:09

Today, I've tried to find out, why my Stored Procedure does not work. A 'SELECT *' does not work within a Stored Procedure, but a SELECT column by column does work. Here is what I have found out: DELIMITER // CREATE PROCEDURE test(IN a INT, OUT b VARCHAR(12), OUT c INT) BEGIN SELECT b, c FROM table WHERE id = a INTO b, c; END Now I can run my Procedure: call test(5, @result1, @result2); SELECT @result1, @result2 – Peter Feb 6 '14 at 11:09

这篇关于如何在MySQL中调用程序?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！