PL/SQL - SPLessons

PL/SQL Sequential Control

Chapter 14

SPLessons 5 Steps, 3 Clicks
5 Steps - 3 Clicks

PL/SQL Sequential Control

PL/SQL Sequential Control

shape Description

The PLSQL Sequential Control structure provides structural techniques to run the executable articulations inside the program. The IF statement will check the condition and decides to execute a bit of code. While using the loop statement, the code should be executed at-least once. Apart from this criteria, to control the program, PL/SQL will provide 2 more conditions to control the sequential statements, such as

GOTO Statement

shape Description

The GOTO proclamation will pass the control to a label unconditionally. The label must go before a PL/SQL piece or an executable articulation and should be exceptional in scope. While running the GOTO statement, the entire control will be passed to the label block or statements.
Limitations of GOTO statements

shape Syntax

GOTO label;

Label => Identifies either a statement or a block.

shape Conceptual
figure

shape Examples

The below example illustrates the GOTO statement

SQL> declare
  2  p varchar2(30);
  3  n pls_integer:=37;
  4  begin
  5  for j in 2..round (sqrt(n)) loop
  6  if n mod j = 0 then
  7  p:='is not a prime number';
  8  goto print_now;
  9  end if;
 10  end loop;
 11  p:='is a prime number';
 12  <<print_now>>
 13  dbms_output.put_line(to_char(n)||p);
 14  end;
 15  /
37 is a prime number
PL/SQL procedure successfully completed.

The above example describes the GOTO statement whether the given number is prime number or not. i.e,37 is a prime number or not. As 37 is prime number, the result will be displayed as prime.

NULL Statement

shape Description

The NULL statement will pass the control to the following conditions and in some other cases it will refer these directions as operations. NULL statements are used in

shape Syntax

BEGIN
–Sql statement
–pl/sql statement
EXCEPTION
WHEN OTHERS THEN
NULL;
END;

Exception => Is a run time error.

NULL => The value is assigned to zero.

shape Examples

The below example illustrates the NULL statements.

SQL> declare
  2  v_job_id varchar2(10);
  3  v_emp_id number(6):=110;
  4  begin
  5  select job_id into v_job_id
  6  from employees
  7  where employee_id=v_emp_id;
  8  if v_job_id='SA_REP' THEN
  9  UPDATE employees
 10  SET commission_pct=commission_pct*1.2;
 11  ELSE
 12  NULL;  --Employee is not a sales rep
 13  end if;
 14  end;
 15  /
PL/SQL Program successfully completed.

In the above example, the NULL STATEMENT will update the values of employees in the employee table.

Summary

shape Key Points

  • PLSQL Sequential Control – Is a technique to run the executable statements inside the program.
  • Goto statements – Pass the control to a label unconditionally.
  • Null statements – Pass the control to the next iteration after referring the current condition.