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:
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:
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
