Oracle Database Programming: PL/SQL Program Control Structure

Oracle Database Programming: PL/SQL program control structure: condition control: if statement: if condition 1 then if condition 1 is true execute the statement here ...... Elsif condition 2 then if condition 2 is true, execute the statement here ...... If both conditions 1 and 2 of else are not true, execute the statement here ...... End if; case statement: case selector when condition 1 then if condition 1 is true, execute the statement here ...... When condition 2 then if condition 2 is true, execute the statement here ...... If none of the else conditions 1 and 2 are true, execute the statement here ...... The end case; case expression can also act on SQL statements.

Loop Control: loop: declare v_num number: = 1; begin loop dbms_output.put_line (v_num); v_num: = v_num + 1; exit when v_num> = 10; end loop; end; while loop: declare v_num number: = 1; begin while v_num <10 loop dbms_output.put_line (v_num); v_num: = v_num + 1; end loop; end; for loop: begin for v_num in 1 .. 10 loop dbms_output.put_line (v_num); end loop; end; Exception Handling: exception: declare v_name emp. ename % type; begin select ename into v_name from emp where empno = & enter employee ID; dbms_output.put_line (v_name); dbms_output.put_line (33/0 ); exception when no_data_found then partition ('no corresponding employee '); then ('continued'); when zero_divide then dbms_output.put_line ('divisor cannot be 0'); end; custom exception: declare v_num number; v_e1 exception; begin if v_num is null then raise v_e1; end if; exception when v_e1 then dbms_output.put_line ('you are wrong '); end; propagation exception: begin raise_application_error (-20001, 'you are wrong, go out'); end; declare v_name emp. ename % type; begin select ename into v_name from emp where empno = & enter employee ID; alias (v_name); dbms_output.put_line (33/0); exception when others then dbms_output.put_line (sqlerrm); end;

 

All exceptions can be captured by others and handled by sqlerrm.