Conditional Control


if <condition> then
     ...
elsif conditions then
      ...
else
      ...
end if;
EX:
if upper(v_last_name) = 'GETZ' then
   v_mgr := 102;
end if;
case selector
    when expression then result
      when expression then result
...
    else result
end;
EX:
declare
        v_grade char(1) := 'B';--upper(g_p_grade);
        v_appraisal varchar2(20);
begin
       v_appraisal :=
case v_grade
       when 'A' then 'Excellent'
           when 'B' then 'Very Good'
               when 'C' then 'Good'
                 else 'No grade'
end;
dbms_output.put_line('grade ' || v_grade || ' apraisal ' || v_appraisal);
end;
Loop:
Basic loop, for loop, while loop
Loop
       ...
      Exit when condition;
End loop
        While condition loop
       ...
End loop
        For counter in [reverse] lower bound ... upper bound loop
End for
Cursors:
Declare -> open -> fetch -> if empty then -> close
Create cursor -> open it –> load the current row into variables -> test for empty -> close
EX:
declare
      cursor emp_cursor is
 select employee_id, last_name from employees;
        cursor dept_cursor is
 select * from departments where location_id=170;
begin
      open emp_cursor;
       open dept_cursor;
loop
            exit when emp_cursor%NOTFOUND;
     fetch emp_cursor into v_id , v_last;
end loop;
      close emp_cursor;
           close dept_cursor;
end;
Cursor Attributes:
<cursor_name>%ISOPEN
<cursor_name>%NOTFOUND
<cursor_name>%FOUND
<cursor_name>%ROWCOUNT
EX:
/**
SQL%ROWCOUNT number of rows affected by the most recent sql satement
SQL%FOUND returns true if the most recent sql statement affected any rows
SQL%NOTFOUND returns true if the most recent sql statement affected no rows
SQL%ISOPEN returns true if the cursor is open, if close then returns false
**/
declare
          v_employee_id employees.employee_id%TYPE := 176;
           rows_deleted varchar2(30);
begin
           delete from employees where employee_id = v_eployee_id ;
              rows_deleted := sql%ROWCOUNT || ' rows deleted';
                      dbms_output.put_line(rows_deleted);
end;
Cursor and Records:
EX:
declare
      cursor emp_cursor is
        select employee_id, last_name from employees;
         emp_record emp_cursor%ROWTYPE;
begin
         open emp_cursor;
    loop
          fetch emp_cursor into emp_record;
              exit when emp_cursor%NOTFOUND;
               insert into temp_list
           (emp_id, emp_name)
      values
             (emp_Record.employee_id, emp_record.last_name);
end loop;
commit;
close emp_cursor;
end;
EX:
Declare
        type cur_type is ref cursor;
            c1 cur_type;
           r1 v$datafile%ROWTYPE;
Begin
          open c1 for select * from v$datafile;
loop
           fetch c1 into r1;
exit when c1%NOTFOUND;
                Dbms_output.put_line(r1.name);
end loop;
/*
For r1 in c1
Loop
Dbms_output.put_line(r1.name);
End loop;
*/
End;
Cursors with parameters:
EX:
declare
        cursor emp_cursor (p_deptno number, p_job varchar2) is
          select employee_id, last_name from employees
           where department_id = p_deptno
        and job_id = p_job;
begin
              open emp_cursor (80,'sales_rep');
    close emp_cursor;
             open emp_cursor (60,'it_prog');
-- ..
end;
Exception Handling:
1- predefined oracle server error
2- non predefined oracle server error
3- user defined error

predefined oracle server errors:

no_data_found            //single row select returned no data
too_many_rows       //single row select returned more than one row
invalid_cursor           //illegal cursor operation
zero_devide                  //attempted to devide by zero
dup_val_on_index         //attempted to insert a duplicate value
cursor_already_open
invalid_number                 //convertion of char string to a number
login_denied
EX:
begin
--...
null;
       exception
       when no_data_found then
--...
null;
           when too_many_rows then
--...
null;
end;
user defined oracle server errors:
EX:
declare
         e_invalid_department exception;
begin
         update departments set department_name = g_v_department_name
          where department_id = g_v_department_number;
        if sql%NOTFOUND then
      raise e_invalid_department;
end if;
commit;
exception
       when e_invalid_department then
         dbms_output.put_line ('no such dept id ');
end;
EX:
delete from employees where manager_id = v_mngr;
     if sql%NOTFOUND then
        raise_application_error (-20202, 'not valid manager');
 end if;
--OR
...
      Exception
           when no_data_found then
           Raise_application_error ('no data found');
End;

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: