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
PLSQL Sequential Control Statements
- GOTO Statement
- NULL Statement
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
- A GOTO statement will not pass the control to exception handler.
- A GOTO statement will not pass the control from exception handler to the current block.
- A GOTO statement cannot pass the control out of a sub-program.
- A GOTO statement will not pass the control to a CASE statement, IF statement, Sub-block or Loop statement.
Syntax
GOTO label;
Label => Identifies either a statement or a block.
Conceptual
figure
Examples
The below example illustrates the GOTO statement
[c]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.[/c]
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.
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
- To develop stub sub-programs and place holders.
- To provide the readability and manageability of the data.
- To provide a target to the GOTO statement.
- To build awareness regarding the possibilities of the statement.
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.
Examples
The below example illustrates the NULL statements.
[c]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.[/c]
In the above example, the NULL STATEMENT will update the values of employees in the employee table.
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.