On 14-08-2022 01:28, Adriano dos Santos Fernandes wrote:
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.
An interesting idea. I do find the ability to specify question mark
parameters at the top and inside the body slightly confusing, but I
understand why it is handy to allow this.
I do think this statement will complicate the parser I have in Jaybird
to detect statement types and whether or not it has RETURNING, but I can
adjust.
Would this still allow the full SELECT syntax (including WITH clauses,
OFFSET/FETCH, GROUP BY, etc), and things like RETURNING for INSERT, etc?
I assume this is a toplevel statement only, so it can't occur as a
derived table or subquery inside another statement. Is that correct?
Mark
--
Mark Rotteveel
Firebird-Devel mailing list, web interface at
https://lists.sourceforge.net/lists/listinfo/firebird-devel
