CODEWITHSUNDEEP
sqldatabaseoracle-databaseplsql
fawad
fawad

Reputation: 141

performance anaysis (select in loop vs function call in loop )

i will like to ask a performance related question here my question is which approach is best

1 adding subquery in loop

declare
test varchar2(50);

FOR Lcntr IN 1..20

LOOP

   update emp set empno='50'  where empname=test;

END LOOP;

2 adding function call in loop or making the function of the above query and calling it in loop

declare

test varchar2(50);

FOR Lcntr IN 1..20
LOOP

  temp:=update('argument');

END LOOP;

Upvotes: 0

Views: 135

Answers (1)

Michael Piankov
Michael Piankov

Reputation: 1997

If your function update just call the same sql update, it dosen't matter are you call it directly of from stored function. In common the best way is use one sql statement (update or merge) for update whole dataset what you need.

But you update are look like strange: In first pl/sql block you declare variable test. And test is equal null. And after that you try update table by comparing with null - no any rows will be affected.

In second pl\sql block you declare variable test too, but use varable temp. It will raise error in compilation.

Upvotes: 1

Related Questions