code_junkie

Oracle PL/SQL - Are NO_DATA_FOUND Exceptions bad for stored procedure performance?

Nov 11th, 2011
78
0
Never
Not a member of Pastebin yet? Sign Up, it unlocks many cool features!
text 3.83 KB | None | 0 0
  1. SELECT COUNT(*) INTO var WHERE condition;
  2. IF var > 0 THEN
  3. SELECT NEEDED_FIELD INTO otherVar WHERE condition;
  4. ....
  5.  
  6. SELECT NEEDED_FIELD INTO var WHERE condition;
  7. EXCEPTION
  8. WHEN NO_DATA_FOUND
  9. ....
  10.  
  11. SQL>create table t (NEEDED_FIELD number, COND number);
  12.  
  13. SQL>insert into t (NEEDED_FIELD, cond) values (1, 0);
  14.  
  15. declare
  16. otherVar number;
  17. cnt number;
  18. begin
  19. for i in 1 .. 50000 loop
  20. select count(*) into cnt from t where cond = 1;
  21.  
  22. if (cnt = 1) then
  23. select NEEDED_FIELD INTO otherVar from t where cond = 1;
  24. else
  25. otherVar := 0;
  26. end if;
  27. end loop;
  28. end;
  29. /
  30.  
  31. declare
  32. otherVar number;
  33. begin
  34. for i in 1 .. 50000 loop
  35. begin
  36. select NEEDED_FIELD INTO otherVar from t where cond = 1;
  37. exception
  38. when no_data_found then
  39. otherVar := 0;
  40. end;
  41. end loop;
  42. end;
  43. /
  44.  
  45. DECLARE
  46. CURSOR foo_cur IS
  47. SELECT NEEDED_FIELD WHERE condition ;
  48. BEGIN
  49. FOR foo_rec IN foo_cur LOOP
  50. ...
  51. END LOOP;
  52. EXCEPTION
  53. WHEN OTHERS THEN
  54. RAISE;
  55. END ;
  56.  
  57. DECLARE
  58. BEGIN
  59. FOR foo_rec IN (SELECT NEEDED_FIELD WHERE condition) LOOP
  60. ...
  61. END LOOP;
  62. EXCEPTION
  63. WHEN OTHERS THEN
  64. RAISE;
  65. END ;
  66.  
  67. SELECT MAX(column)
  68. INTO var
  69. FROM table
  70. WHERE conditions;
  71.  
  72. IF var IS NOT NULL
  73. THEN ...
  74.  
  75. BEGIN
  76. FOR rec IN (SELECT a.needed_field,b.other_field
  77. FROM table1 a
  78. LEFT OUTER JOIN table2 b
  79. ON a.needed_field = b.condition_field
  80. WHERE a.column = ???)
  81. LOOP
  82. IF rec.other_field IS NOT NULL THEN
  83. -- whatever processing needs to be done to other_field
  84. END IF;
  85. END LOOP;
  86. END;
  87.  
  88. begin
  89. for i in 2 .. 10000 loop
  90. insert into t (NEEDED_FIELD, cond) values (i, 10);
  91. end loop;
  92. end;
  93.  
  94. declare
  95. otherVar number;
  96. cnt number;
  97. begin
  98. for i in 1 .. 5000 loop
  99. select count(*) into cnt from t where cond = 0;
  100.  
  101. if (cnt = 1) then
  102. select NEEDED_FIELD INTO otherVar from t where cond = 0;
  103. else
  104. otherVar := 0;
  105. end if;
  106. end loop;
  107. end;
  108. /
  109.  
  110. PL/SQL procedure successfully completed.
  111.  
  112. Elapsed: 00:00:04.34
  113.  
  114. declare
  115. otherVar number;
  116. begin
  117. for i in 1 .. 5000 loop
  118. begin
  119. select NEEDED_FIELD INTO otherVar from t where cond = 0;
  120. exception
  121. when no_data_found then
  122. otherVar := 0;
  123. end;
  124. end loop;
  125. end;
  126. /
  127.  
  128. PL/SQL procedure successfully completed.
  129.  
  130. Elapsed: 00:00:02.10
  131.  
  132. SELECT NEEDED_FIELD INTO var WHERE condition;
  133. EXCEPTION
  134. WHEN NO_DATA_FOUND....
  135.  
  136. EXCEPTION
  137. WHEN OTHERS THEN
  138. RAISE;
  139.  
  140. DECLARE
  141. CURSOR foo_cur IS
  142. SELECT NEEDED_FIELD WHERE condition ;
  143. BEGIN
  144. OPEN foo_cur;
  145. FETCH foo_cur INTO foo_rec;
  146. IF foo_cur%FOUND THEN
  147. ...
  148. END IF;
  149. CLOSE foo_cur;
  150. EXCEPTION
  151. WHEN OTHERS THEN
  152. CLOSE foo_cur;
  153. RAISE;
  154. END ;
  155.  
  156. DECLARE CURSOR loopCursor IS SELECT NEEDED_FIELD FROM TABLE WHERE condition;
  157. CURSOR condition1Cursor(p_parm TABLE.NEEDED_FIELD%TYPE) IS
  158. SELECT OTHER_FIELD INTO workVar FROM TABLE
  159. WHERE condition_field = p_parm;
  160. BEGIN
  161. FOR thisIteration IN loopCursor LOOP
  162. OPEN condition1Cursor(thisIteration.NEEDED_FIELD);
  163. FETCH condition1Cursor INTO testVar;
  164. IF condition1Cursor%FOUND THEN
  165. ....
  166. END IF;
  167. END LOOP;
  168. END;
  169.  
  170. declare
  171. cursor cur_name is select * from emp;
  172. begin
  173. for cur_rec in cur_name Loop
  174. dbms_output.put_line(cur_rec.ename);
  175. end loop;
  176. End ;
  177.  
  178. declare
  179. cursor cur_name is select * from emp;
  180. cur_rec emp%rowtype;
  181. begin
  182. Open cur_name;
  183. Loop
  184. Fetch cur_name into Cur_rec;
  185. Exit when cur_name%notfound;
  186. dbms_output.put_line(cur_rec.ename);
  187. end loop;
  188. Close cur_name;
  189. End ;
  190.  
  191. declare
  192. otherVar number;
  193. begin
  194. for i in 1 .. 5000 loop
  195. begin
  196. for foo_rec in (select NEEDED_FIELD from t where cond = 0) loop
  197. otherVar := foo_rec.NEEDED_FIELD;
  198. end loop;
  199. otherVar := 0;
  200. end;
  201. end loop;
  202. end;
Add Comment
Please, Sign In to add comment