JFIF$        dd7 

Viewing File: /usr/share/doc/firebird/sql.extensions/README.subroutines.txt

-----------
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!
Back to Directory  nL+D550H?Mx ,D"v]qv;6*Zqn)ZP0!1 A "#a$2Qr D8 a Ri[f\mIykIw0cuFcRı?lO7к_f˓[C$殷WF<_W ԣsKcëIzyQy/_LKℂ;C",pFA:/]=H  ~,ls/9ć:[=/#f;)x{ٛEQ )~ =𘙲r*2~ a _V=' kumFD}KYYC)({ *g&f`툪ry`=^cJ.I](*`wq1dđ#̩͑0;H]u搂@:~וKL Nsh}OIR*8:2 !lDJVo(3=M(zȰ+i*NAr6KnSl)!JJӁ* %݉?|D}d5:eP0R;{$X'xF@.ÊB {,WJuQɲRI;9QE琯62fT.DUJ;*cP A\ILNj!J۱+O\͔]ޒS߼Jȧc%ANolՎprULZԛerE2=XDXgVQeӓk yP7U*omQIs,K`)6\G3t?pgjrmۛجwluGtfh9uyP0D;Uڽ"OXlif$)&|ML0Zrm1[HXPlPR0'G=i2N+0e2]]9VTPO׮7h(F*癈'=QVZDF,d߬~TX G[`le69CR(!S2!P <0x<!1AQ "Raq02Br#SCTb ?Ζ"]mH5WR7k.ۛ!}Q~+yԏz|@T20S~Kek *zFf^2X*(@8r?CIuI|֓>^ExLgNUY+{.RѪ τV׸YTD I62'8Y27'\TP.6d&˦@Vqi|8-OΕ]ʔ U=TL8=;6c| !qfF3aů&~$l}'NWUs$Uk^SV:U# 6w++s&r+nڐ{@29 gL u"TÙM=6(^"7r}=6YݾlCuhquympǦ GjhsǜNlɻ}o7#S6aw4!OSrD57%|?x>L |/nD6?/8w#[)L7+6〼T ATg!%5MmZ/c-{1_Je"|^$'O&ޱմTrb$w)R$& N1EtdU3Uȉ1pM"N*(DNyd96.(jQ)X 5cQɎMyW?Q*!R>6=7)Xj5`J]e8%t!+'!1Q5 !1 AQaqё#2"0BRb?Gt^## .llQT $v,,m㵜5ubV =sY+@d{N! dnO<.-B;_wJt6;QJd.Qc%p{ 1,sNDdFHI0ГoXшe黅XۢF:)[FGXƹ/w_cMeD,ʡcc.WDtA$j@:) -# u c1<@ۗ9F)KJ-hpP]_x[qBlbpʖw q"LFGdƶ*s+ډ_Zc"?%t[IP 6J]#=ɺVvvCGsGh1 >)6|ey?Lӣm,4GWUi`]uJVoVDG< SB6ϏQ@ TiUlyOU0kfV~~}SZ@*WUUi##; s/[=!7}"WN]'(L! ~y5g9T̅JkbM' +s:S +B)v@Mj e Cf jE 0Y\QnzG1д~Wo{T9?`Rmyhsy3!HAD]mc1~2LSu7xT;j$`}4->L#vzŏILS ֭T{rjGKC;bpU=-`BsK.SFw4Mq]ZdHS0)tLg