| |
|
If this is your first visit, be sure to check out the FAQ by clicking the link above.
You may have to register before you can post: click the register link above to proceed.
To start viewing messages, select the forum that you want to visit from the selection below.
|
 |
11-18-09, 19:26
|
|
Registered User
|
|
Join Date: Nov 2009
Posts: 7
|
|
|
Recursive Stored Procedure
|
|
Hello,
I am trying to compute the fibonacci sequence using recursive stored procedures.
I am trying to call my procedure recursively, I can call it but I don't know how to get the output value.
This is my procedure:
Code:
CREATE PROCEDURE fib(IN i INTEGER, OUT fib BIGINT)
BEGIN
DECLARE sql VARCHAR(200);
DECLARE aux1 BIGINT;
IF i=0 OR i=1
THEN SET fib = i;
ELSE
--Im trying many ways, but I think one of those may do the trick, Im missing
--something
--SET sql = 'CALL fib(1,aux1)';
--SET sql = 'CALL fib(?,?)';
EXECUTE IMMEDIATE sql; -- USING 1, aux1;
SET fib = aux1;
END IF;
END@
How can I get the out parameter from my recursive call?
thank you,
Oscar
|
|
11-18-09, 21:58
|
|
Registered User
|
|
Join Date: Jul 2009
Posts: 150
|
|
|
|
11-18-09, 22:11
|
|
:-)
|
|
Join Date: Jun 2003
Location: Toronto, Canada
Posts: 4,449
|
|
|
|
Did you try simply ?
By the way, procedure calls can nest no more than 64 levels deep.
|
|
11-18-09, 22:30
|
|
Registered User
|
|
Join Date: Jul 2009
Posts: 150
|
|
Two rules you have to know:
Self-referencing function: The body of an SQL function (that is, the expression or NULL in the RETURN clause of the CREATE FUNCTION statement) cannot contain a recursive invocation of itself or to another function that invokes it, because such a function would not exist to be referenced
Version resolution:
Normally, the currently active version of a native SQL
| procedure will be used on a CALL statement. However, if the CALL
| statement is a recursive call inside the body of the same stored
| procedure, and the original CALL statement uses a version that is
| different from the currently active version, the active version will not
| be used. The version from the original CALL statement will be used for any
| recursive CALL statements until the entire stored procedure finishes
| executing. This preserves the semantics of the version that is used by the
| original CALL statement. This includes the case where the recursive call
| is indirect. For example, assume that procedure SP1 call procedure SP2,
| which in turn recursively calls SP1. The second invocation of procedure
| SP1 will use the version of the procedure that is active at the time of
| the original CALL statement that invoked procedure SP1.
Kara
|
|
11-19-09, 05:45
|
|
Registered User
|
|
Join Date: Nov 2009
Posts: 7
|
|
Hi!
Thank you for your reply!
@Kara
Thanks for the link. I took a look at it but it seems he is calling the same select many times, isn't he? (I'm a beginner in DBs world).
About your second post, what does "active version" means? Is it the version of "compilation"?
@n_i
yes, I tried that solution, but once it is recursive, I get the following error:
"Unable to find a FIB procedure that accepts compatible parameters. "
I think it is because it is not compiled yet, so not yet deployed, then the DB can't "find" it.
The dynamic SQL was the way I found to call it, but I can't get the output result.
I just need the syntax to call it...
|
|
11-19-09, 07:12
|
|
:-)
|
|
Join Date: Jun 2003
Location: Toronto, Canada
Posts: 4,449
|
|
Something along these lines:
Code:
DECLARE p_in, p_out BIGINT;
SET sql = 'CALL fib(?,?)';
PREPARE s FROM sql;
EXECUTE s INTO p_out USING p_in;
Not tested.
|
|
11-19-09, 07:21
|
|
Registered User
|
|
Join Date: Nov 2009
Posts: 7
|
|
It worked!
thank you a lot!
|
|
| Thread Tools |
Search this Thread |
|
|
|
| Display Modes |
 Linear Mode
|
Posting Rules
|
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts
HTML code is Off
|
|
|
|
|
dBforums
>
Recursive Stored Procedure
