MySQL Forums
Forum List  »  Stored Procedures

Re: Function calls stored procedure, creates table, can't access it
Posted by: Ruby Muse
Date: February 09, 2006 03:54PM

Here's a test case:

delimiter //

CREATE FUNCTION F1 (in_val INTEGER) RETURNS INTEGER
BEGIN
DECLARE result INTEGER DEFAULT 0;
CALL P1 (in_val, result);
RETURN result;
END;
//

CREATE PROCEDURE P1 (in_val INTEGER, OUT result INTEGER)
BEGIN
CREATE TEMPORARY TABLE Ptmp (val INTEGER);
CALL P2 (in_val);
CALL P3 (result);
DROP TEMPORARY TABLE IF EXISTS Ptmp;
END;
//

CREATE PROCEDURE P2 (some_val INTEGER)
BEGIN
INSERT INTO Ptmp (val) VALUES (some_val);
END;
//

CREATE PROCEDURE P3 (OUT result INTEGER)
BEGIN
SELECT val INTO result FROM Ptmp;
END;
//

delimiter ;

mysql> describe Ptmp;
ERROR 1146 (42S02): Table 'dev_test.Ptmp' doesn't exist
mysql> select F1 (25);
ERROR 1146 (42S02): Table 'dev_test.Ptmp' doesn't exist
mysql> set @result = 0;
Query OK, 0 rows affected (0.00 sec)

mysql> call P1 (25, @result);
Query OK, 0 rows affected (0.00 sec)

mysql> select @result;
+---------+
| @result |
+---------+
| 25 |
+---------+
1 row in set (0.00 sec)

mysql> create table Ptmp (val integer);
Query OK, 0 rows affected (0.00 sec)

mysql> select F1 (25);
+---------+
| F1 (25) |
+---------+
| 25 |
+---------+
1 row in set (0.00 sec)

Thanks.

Options: ReplyQuote


Subject
Views
Written By
Posted
Re: Function calls stored procedure, creates table, can't access it
1277
February 09, 2006 03:54PM


Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.