Sunday, July 24, 2011

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