From: Alexander K. <as...@id...> - 2004-03-15 09:36:23
|
>Here is an example DSQL statement which now works: [skip] > for select ass_episodeslate from i_asset > into :ass_nola as cursor asset_cursor do This reminded me of a feature I wanted since the first time I have seen Interbase/Firebird. While I understand performance and aestetic reasons in favor of 'for' statements and against cursors, I think cursors have one essential advantage which can be exploited without sacrificing any benefits of 'for'. Consider the following typical code [tildas are here to preserve identation]: create procedure MyProc as ~~declare field_a varchar(100); ~~declare field_b varchar(150); ~~declare field_c varchar(120); begin ~~for select field_a, field_b, field_c from my_table ~~into :field_a, :field_b, :field_c ~~do begin ~~~~/* some processing */ ~~~~field_a = field_b || field_c; ~~~~update my_table2 set field_a = :field_a; ~~end end Notice that the list of selected fields is repeated three times in three different places, and must be maintained for every change of the underlying table structure. Notice further that the types and sizes in variable declarations must match the field declarations precisely. And is the do not, the resulting bug will be subtle and hard to find. I have been bitten by this more than once. Remember that in production databases, field lists can easily contain 20-30 and more items, and maintenance problems grow proportionally. Now this is my dream solution: create procedure MyProc as ~~declare c cursor; begin ~~for select field_a, field_b, field_c form my_table into :c do begin ~~~~/* some processing */ ~~~~c.field_a = c.field_b || c.field_c; ~~~~update my_table2 set field_a = c.field_a; ~~end end Most of aforementioned difficulties are alleviated, and no ugly OPENs and FETCHes introduced. The performance of this code should, with any reasonable implementation, be equal to the first example. Of course, the perticular syntax is subject to debate, it may be spelled 'for select ... into cursor :c' or 'for select ... as cursor :c' or whatever. Actually, it is concievable even to allow 'for select * from my_table into :c', thus elimination the last field list, but this holds obvious dangers, so I am not sure here. Similiar functionality is present in all "major" databases, and lack of it is the source of constant aggravation for me when porting code to Firebird. (And yes, I do this at work ;) --- Professional hosting for everyone - http://www.host.ru |