Not a member of Pastebin yet?
Sign Up,
it unlocks many cool features!
- SELECT COUNT(*) INTO var WHERE condition;
- IF var > 0 THEN
- SELECT NEEDED_FIELD INTO otherVar WHERE condition;
- ....
- SELECT NEEDED_FIELD INTO var WHERE condition;
- EXCEPTION
- WHEN NO_DATA_FOUND
- ....
- SQL>create table t (NEEDED_FIELD number, COND number);
- SQL>insert into t (NEEDED_FIELD, cond) values (1, 0);
- declare
- otherVar number;
- cnt number;
- begin
- for i in 1 .. 50000 loop
- select count(*) into cnt from t where cond = 1;
- if (cnt = 1) then
- select NEEDED_FIELD INTO otherVar from t where cond = 1;
- else
- otherVar := 0;
- end if;
- end loop;
- end;
- /
- declare
- otherVar number;
- begin
- for i in 1 .. 50000 loop
- begin
- select NEEDED_FIELD INTO otherVar from t where cond = 1;
- exception
- when no_data_found then
- otherVar := 0;
- end;
- end loop;
- end;
- /
- DECLARE
- CURSOR foo_cur IS
- SELECT NEEDED_FIELD WHERE condition ;
- BEGIN
- FOR foo_rec IN foo_cur LOOP
- ...
- END LOOP;
- EXCEPTION
- WHEN OTHERS THEN
- RAISE;
- END ;
- DECLARE
- BEGIN
- FOR foo_rec IN (SELECT NEEDED_FIELD WHERE condition) LOOP
- ...
- END LOOP;
- EXCEPTION
- WHEN OTHERS THEN
- RAISE;
- END ;
- SELECT MAX(column)
- INTO var
- FROM table
- WHERE conditions;
- IF var IS NOT NULL
- THEN ...
- BEGIN
- FOR rec IN (SELECT a.needed_field,b.other_field
- FROM table1 a
- LEFT OUTER JOIN table2 b
- ON a.needed_field = b.condition_field
- WHERE a.column = ???)
- LOOP
- IF rec.other_field IS NOT NULL THEN
- -- whatever processing needs to be done to other_field
- END IF;
- END LOOP;
- END;
- begin
- for i in 2 .. 10000 loop
- insert into t (NEEDED_FIELD, cond) values (i, 10);
- end loop;
- end;
- declare
- otherVar number;
- cnt number;
- begin
- for i in 1 .. 5000 loop
- select count(*) into cnt from t where cond = 0;
- if (cnt = 1) then
- select NEEDED_FIELD INTO otherVar from t where cond = 0;
- else
- otherVar := 0;
- end if;
- end loop;
- end;
- /
- PL/SQL procedure successfully completed.
- Elapsed: 00:00:04.34
- declare
- otherVar number;
- begin
- for i in 1 .. 5000 loop
- begin
- select NEEDED_FIELD INTO otherVar from t where cond = 0;
- exception
- when no_data_found then
- otherVar := 0;
- end;
- end loop;
- end;
- /
- PL/SQL procedure successfully completed.
- Elapsed: 00:00:02.10
- SELECT NEEDED_FIELD INTO var WHERE condition;
- EXCEPTION
- WHEN NO_DATA_FOUND....
- EXCEPTION
- WHEN OTHERS THEN
- RAISE;
- DECLARE
- CURSOR foo_cur IS
- SELECT NEEDED_FIELD WHERE condition ;
- BEGIN
- OPEN foo_cur;
- FETCH foo_cur INTO foo_rec;
- IF foo_cur%FOUND THEN
- ...
- END IF;
- CLOSE foo_cur;
- EXCEPTION
- WHEN OTHERS THEN
- CLOSE foo_cur;
- RAISE;
- END ;
- DECLARE CURSOR loopCursor IS SELECT NEEDED_FIELD FROM TABLE WHERE condition;
- CURSOR condition1Cursor(p_parm TABLE.NEEDED_FIELD%TYPE) IS
- SELECT OTHER_FIELD INTO workVar FROM TABLE
- WHERE condition_field = p_parm;
- BEGIN
- FOR thisIteration IN loopCursor LOOP
- OPEN condition1Cursor(thisIteration.NEEDED_FIELD);
- FETCH condition1Cursor INTO testVar;
- IF condition1Cursor%FOUND THEN
- ....
- END IF;
- END LOOP;
- END;
- declare
- cursor cur_name is select * from emp;
- begin
- for cur_rec in cur_name Loop
- dbms_output.put_line(cur_rec.ename);
- end loop;
- End ;
- declare
- cursor cur_name is select * from emp;
- cur_rec emp%rowtype;
- begin
- Open cur_name;
- Loop
- Fetch cur_name into Cur_rec;
- Exit when cur_name%notfound;
- dbms_output.put_line(cur_rec.ename);
- end loop;
- Close cur_name;
- End ;
- declare
- otherVar number;
- begin
- for i in 1 .. 5000 loop
- begin
- for foo_rec in (select NEEDED_FIELD from t where cond = 0) loop
- otherVar := foo_rec.NEEDED_FIELD;
- end loop;
- otherVar := 0;
- end;
- end loop;
- end;
Add Comment
Please, Sign In to add comment