14.08.2022 2:28, Adriano dos Santos Fernandes пишет:
Hi!
When one starts with a DSQL command and need to adapt it to EXECUTE
BLOCK (for example to use sub routines or use a single parameter in many
places), work is difficult when there are many parameters and output
fields. Everything must be explicitly declared.
I propose new DSQL statement that improve a lot this workflow (and
others when not all power of EXECUTE BLOCK is necessary, but it's
verbosity is inevitable).
I'm calling it EXECUTE SQL, and it's to use with SELECT, UPDATE, DELETE
and MERGE, with or without RETURNING. It seats between lack of resources
+ simplicity of direct SQL command and power + verbosity of EXECUTE BLOCK.
Syntax:
execute sql [ ( <input parameter list> ) ]
[ <subroutines> ]
do <sql command>
Here is how it can be used:
execute sql (p1 integer = ?, p2 integer = ?)
declare function subfunc (i1 integer) returns integer
as
begin
return i1;
end
declare procedure subproc (i1 integer) returns (o1 integer)
as
begin
o1 = i1;
suspend;
end
do
select subfunc(:p1) + o1
from subproc(:p2 + ?)
Note that parameters may be declared or directly (only in the DO
command) used like now.
Output is not declared. It's inferred from the DO command.
Statement type of the DO command is returned.
Adriano
Very good feature, reminds me of the functionality from Oracle
WITH Procedure p1 ...
AS
...
FUNCTION f1 ...
AS
...
SELECT...
However, it's not entirely clear to me how client applications handle
named parameters (which will be replaced with "?")? It seems to me
unnamed parameters "?" (which in client applications will most likely be
named) leave either in the <input parameter list> or in the request
itself. I vote for them to remain only in the request.
Just try to implement EXECUTE STATEMENT with your syntax and support for
named parameters, then it will become clear what I'm talking about. My
option is this.
Syntax:
execute sql
[<subroutines>]
do <sql command>
Here is how it can be used:
execute sql
declare function subfunc (i1 integer) returns integer
as
begin
return i1;
end
declare procedure subproc (i1 integer) returns (o1 integer)
as
begin
o1 = i1;
suspend;
end
do
select subfunc(?) + o1
from subproc(?)
Firebird-Devel mailing list, web interface at
https://lists.sourceforge.net/lists/listinfo/firebird-devel
Firebird-Devel mailing list, web interface at
https://lists.sourceforge.net/lists/listinfo/firebird-devel
