| Subject | Re: [firebird-support] looping Store Procedure |
|---|---|
| Author | Helen Borrie |
| Post date | 2008-09-04T01:43:41Z |
At 11:10 4/09/2008, you wrote:
returns (.....)
as
declare var1 integer;
declare var2 whatever,
....;
begin
FOR
SELECT COL_1, COL_2, ...
FROM ATABLE
WHERE...
.....
INTO :var1, :var2, ......
DO
BEGIN
[operate on the current set of variable values]
SUSPEND; (only if you are sending a row back to the client)
END
....
end
Of course, there is much more to it than that, more than you could expect from a single support posting. PSQL language is an area of study in its own right. Part Seven of The Firebird Book comprises 120 pages and five chapters on this subject and it already needs at least one more!
You can embed a FOR...SELECT loop inside another one as well, by a variety of means.
The FOR SELECT....INTO...DO syntax is one way. There are other ways. For example, study the PSQL section of the release notes for Firebird 2.x, under the topic 'Explicit Cursors'.
./heLen
>Hi, Allcreate procedure xxxx (....)
>
>Anyone could give me sample rows / script of Store Procedure for looping (SELECT QUERY) ?
returns (.....)
as
declare var1 integer;
declare var2 whatever,
....;
begin
FOR
SELECT COL_1, COL_2, ...
FROM ATABLE
WHERE...
.....
INTO :var1, :var2, ......
DO
BEGIN
[operate on the current set of variable values]
SUSPEND; (only if you are sending a row back to the client)
END
....
end
Of course, there is much more to it than that, more than you could expect from a single support posting. PSQL language is an area of study in its own right. Part Seven of The Firebird Book comprises 120 pages and five chapters on this subject and it already needs at least one more!
You can embed a FOR...SELECT loop inside another one as well, by a variety of means.
The FOR SELECT....INTO...DO syntax is one way. There are other ways. For example, study the PSQL section of the release notes for Firebird 2.x, under the topic 'Explicit Cursors'.
./heLen