Friday, 9 May 2014

Working with Cursors



Select into can be used when we are fetching a single row from the select statement

DBMS_OUTPUT.put_line (
     l_last_name);

Select into can be used to select 2 column values into two variables, but the number of rows outputed should not be more then one other wise it gives too many rows exception

Select into is a inplicit cursor that is oracle opens a curson implicitly


  l_department_name  
     departments.department_name%TYPE --- used for defining of the type of the column name

l_employee   employees%ROWTYPE -- used for defining of the rowtype

l_employee_id   employee_id_cur%ROWTYPE--- also can be defined of the type cursor


Using for loops with Cursor

If you declare a for loop using cursor then no need to explicitly open and close the cursor oracle does this for you

Best thing about using for loop with cursor is oracle automatically optimizes the code to use bulk collect so it will collect all the rows before firing the sql

No comments:

Post a Comment