__ __ __ __ _____ _ _ _____ _ _ _ | \/ | \ \ / / | __ \ (_) | | / ____| | | | | | \ / |_ __\ V / | |__) | __ ___ ____ _| |_ ___ | (___ | |__ ___| | | | |\/| | '__|> < | ___/ '__| \ \ / / _` | __/ _ \ \___ \| '_ \ / _ \ | | | | | | |_ / . \ | | | | | |\ V / (_| | || __/ ____) | | | | __/ | | |_| |_|_(_)_/ \_\ |_| |_| |_| \_/ \__,_|\__\___| |_____/|_| |_|\___V 2.1 if you need WebShell for Seo everyday contact me on Telegram Telegram Address : @jackleetFor_More_Tools:
-----------
Subroutines
-----------
Author:
Adriano dos Santos Fernandes <adrianosf at gmail.com>
Description:
Support for PSQL subroutines (functions and procedures) inside functions, procedures, triggers
and EXECUTE BLOCK. Subroutines are declared in the main routine and may be used from there or others subroutines.
Syntax:
<declaration item> ::=
DECLARE [VARIABLE] <variable name> <data type> [ := <value> ];
|
DECLARE [VARIABLE] CURSOR <cursor name> FOR (<query>);
|
<subroutine declaration>
|
<subroutine implementation>
<subroutine declaration> ::=
DECLARE FUNCTION <function name> [ (<input parameters>) ]
RETURNS <data type>
[ [ NOT ] DETERMINISTIC ] ;
|
DECLARE PROCEDURE <procedure name> [ (<input parameters>) ] [ RETURNS (<output parameters>) ] ;
<subroutine implementation> ::=
DECLARE FUNCTION <function name> [ (<input parameters>) ]
RETURNS <data type>
[ [ NOT ] DETERMINISTIC ]
AS
...
BEGIN
...
END
|
DECLARE PROCEDURE <procedure name> [ (<input parameters>) ] [ RETURNS (<output parameters>) ]
AS
...
BEGIN
...
END
Limitations:
1) Subroutines may not be nested in another subroutine. They are only supported in the main
routine.
2) Currently, a subroutine may not directly access or use variables or cursors of the
main statements. This may be allowed in the future.
Notes:
1) Starting in FB 4, subroutines may be recursive or call others subroutines.
Examples:
set term !;
-- 1) Sub-procedures in execute block.
execute block returns (name varchar(31))
as
declare procedure get_tables returns (table_name varchar(31))
as
begin
for select rdb$relation_name
from rdb$relations
where rdb$view_blr is null
into table_name
do
suspend;
end
declare procedure get_views returns (view_name varchar(31))
as
begin
for select rdb$relation_name
from rdb$relations
where rdb$view_blr is not null
into view_name
do
suspend;
end
begin
for select table_name
from get_tables
union all
select view_name
from get_views
into name
do
suspend;
end!
-- 2) Sub-function in a stored function.
create or alter function func1 (n1 integer, n2 integer) returns integer
as
declare function subfunc (n1 integer, n2 integer) returns integer
as
begin
return n1 + n2;
end
begin
return subfunc(n1, n2);
end!
select func1(5, 6) from rdb$database!
-- 3) Recursive sub-function in EXECUTE BLOCK.
execute block returns (i integer, o integer)
as
-- Recursive function without forward declaration.
declare function fibonacci(n integer) returns integer
as
begin
if (n = 0 or n = 1) then
return n;
else
return fibonacci(n - 1) + fibonacci(n - 2);
end
begin
i = 0;
while (i < 10)
do
begin
o = fibonacci(i);
suspend;
i = i + 1;
end
end!
-- 4) Example with forward declaration and parameter with default values.
execute block returns (o integer)
as
-- Forward declaration of P1.
declare procedure p1(i integer = 1) returns (o integer);
-- Forward declaration of P2.
declare procedure p2(i integer) returns (o integer);
-- Implementation of P1 should not re-declare parameter default value.
declare procedure p1(i integer) returns (o integer)
as
begin
execute procedure p2(i) returning_values o;
end
declare procedure p2(i integer) returns (o integer)
as
begin
o = i;
end
begin
execute procedure p1 returning_values o;
suspend;
end!