>>1. EXECUTE BLOCK and there for select and delete
>I am completely unaware about execute block. Would you offer any help,
so I could learn from it.
If PK is your integer primary key for MY_BOOK, then you should be able
to do something like this:
execute block returns(Changes integer) as
declare variable mbPK integer;
begin
Changes = 0;
for select distinct MB.PK
from MY_BOOK MB
join MY_BOOK_HEADER MBH on MBH.PK_BOOK_HEADER = MB.SK_BOOK_HEADER
where MB.BOOK_TYPE = 4
and Upper(Trim(MBH.HEADER_BOOK_CODE)) = Upper(Trim('127518010109038'))
--UPPER and TRIM doesn't make any difference to a constant containing
only digits and no whitespace...
And MBH.FK_BOOK_GROUP = '{79B79C33-CE56-4084-912B-6DD9F70B3DC4}'
into :mbPK do
begin
update My_Book SET BOOK_NAME = 'Book Name1', BOOK_DESCRIPTION =
'Book Description1'
where PK = :mbPK;
Changes = Changes + ROW_COUNT;
end
suspend;
end
'Changes' and 'suspend' is not required, but whenever I use EXECUTE
BLOCK myself, I prefer to add such a variable to ascertain that I only
update the ten records I want to update and not one million due to
forgetting something important in the JOIN or WHERE clauses.
I think execute block can particularly improve performance if
a) You only want to reduce a small fraction of the records in My_Book,
and/or
b) the EXISTS clause is complicated and time consuming
If both My_Book and My_Book_Header are small tables with sensible
indexing, using execute block may be of little or no help.
HTH,
Set
