Closures in PL/SQL

Yesterday I read a few chapters of Conrad Barski's wonderful Land of Lisp. I nearly finished it months ago, but with the new job in the APEX team there was little time. While I love Lisp, PL/SQL is my main language and I sometimes miss the expressiveness of other languages - see Meta-PL/SQL. Well, if there is a will, there is a way, so I built me some closures. Compared to Lisp, the concept does not map naturally to PL/SQL and the implementation is only a toy. Anyway, here are a few examples:

declare

    l lambda := lambda('begin :r := :a + :b; end;',
                       'b',1);
begin
    dbms_output.put_line(l.exec('a',3));                                                                                                                                                                            
    dbms_output.put_line(l.exec('a',9));
    l.close;
end;
/

4
10


PL/SQL procedure successfully completed.

Map and reduce are popular patterns. Here's reduce:

declare
  type v2_tbl is table of varchar2(32767);
  function reduce (
    l in lambda,
    t in v2_tbl,
    v in varchar2)
    return varchar2
  is
    l_result varchar2(32767) := v;
  begin
    if t is not null then
        for i in 1 .. t.count loop
            l_result := l.exec(
                          'a',t(i),
                          'b',l_result);
        end loop;
    end if;
    l.close;
    return l_result;
  end;
begin
  dbms_output.put_line (
    reduce (
      lambda(q'{begin
                  dbms_output.put_line('adding '||:a||' to '||:b);
                  :r := :a+:b;
                end;}'),
      v2_tbl(1,2,3,4,5),
      0));
end;
/

adding 1 to 0

adding 2 to 1

adding 3 to 3

adding 4 to 6

adding 5 to 10

15

It is also possible to write state-saving code, for example a number generator:


declare
  l lambda := lambda(q'{
                declare
                  v_result number := :a+1;
                begin
                  :r := v_result;
                  dbms_sql.bind_variable(:c,'a',v_result,32767);
                end;}',
                'a',0);
begin
  l.bind('c',l.c);
  for i in 1 .. 10 loop
    dbms_output.put_line(l.exec);
  end loop;
end;
/
1
2
3
4
5
6
7
8
9
10                                                                                 


The code for lambda:

create or replace type lambda as object (                                                                                                                                                                           

    c number,

    constructor function lambda (

        p_sql in varchar2,

        p_n1  in varchar2 default null,

        p_v1  in varchar2 default null,

        p_n2  in varchar2 default null,

        p_v2  in varchar2 default null,

        p_n3  in varchar2 default null,

        p_v3  in varchar2 default null )

        return self as result,

    member procedure bind (

        self in lambda,

        p_n in varchar2,

        p_v in varchar2 ),

    member procedure exec (

        self in lambda ),

    member function exec (

        self     in lambda,

        p_n1     in varchar2 default null,

        p_v1     in varchar2 default null,

        p_n2     in varchar2 default null,

        p_v2     in varchar2 default null,

        p_n3     in varchar2 default null,

        p_v3     in varchar2 default null,

        p_result in varchar2 default 'r')

        return varchar2,

    member function value (

        self in lambda,

        p_n in varchar2 )

        return varchar2,

    member procedure close (

        self in lambda )

)

/

show err

create or replace type body lambda as

    constructor function lambda (

        p_sql in varchar2,

        p_n1  in varchar2 default null,

        p_v1  in varchar2 default null,

        p_n2  in varchar2 default null,

        p_v2  in varchar2 default null,

        p_n3  in varchar2 default null,

        p_v3  in varchar2 default null )

        return self as result

    is

    begin

        c := dbms_sql.open_cursor;

        dbms_sql.parse(c,p_sql,dbms_sql.native);

        if p_n1 is not null then

            self.bind(p_n1,p_v1);

        end if;

        if p_n2 is not null then

            self.bind(p_n2,p_v2);

        end if;

        if p_n3 is not null then

            self.bind(p_n3,p_v3);

        end if;

        return;

    end;

    member procedure bind (

        self in lambda,

        p_n in varchar2,

        p_v in varchar2 )

    is

    begin

        dbms_sql.bind_variable(self.c,p_n,p_v,32767);

    end;

    member procedure exec (

        self in lambda )

    is

        l_num_processed number;

    begin

        l_num_processed := dbms_sql.execute(c);

    end;

    member function exec (

        self     in lambda,

        p_n1     in varchar2 default null,

        p_v1     in varchar2 default null,

        p_n2     in varchar2 default null,

        p_v2     in varchar2 default null,

        p_n3     in varchar2 default null,

        p_v3     in varchar2 default null,

        p_result in varchar2 default 'r')

        return varchar2

    is

    begin

        self.bind(p_result,null);

        if p_n1 is not null then

            self.bind(p_n1,p_v1);

        end if;

        if p_n2 is not null then

            self.bind(p_n2,p_v2);

        end if;

        if p_n3 is not null then

            self.bind(p_n3,p_v3);

        end if;

        self.exec;

        return self.value(p_result);

    end;

    member function value (

        self in lambda,

        p_n in varchar2 )

        return varchar2

    is

        l_val varchar2(32767);

    begin

        dbms_sql.variable_value(self.c,p_n,l_val);

        return l_val;

    end;

    member procedure close (

        self in lambda )

    is

        l_c number := self.c;

    begin

        dbms_sql.close_cursor(l_c);

    end;

end;

/

show err