PL SQL Tutorial


PL SQL Control Statements Tutorial

PL SQL includes conditional (IF,CASE) structures as well as sequential control(GOTO,NULL)

IF Statement

    IF statement tests the condition

Syntax:
			IF condition(s) THEN
				
				pl-sql statements;
			
			END IF;
			
		
Syntax: IF ELSE STATEMENT
			IF condition(s) THEN
				
				pl-sql statements;
			ELSE
			        pl sql statements;
			END IF;
			
		
Syntax: IF ELSIF STATEMENT
			IF condition(s) THEN
				
				pl-sql statements;
			ELSIF condition(s) THEN
			        pl sql statements;
			[ELSE
				ELSE
				pl sql statements; 
			END IF;
			
		

CASE Expressions

    CASE Expressions tests multiple conditions in simple manner. It's a replacement for IF-ELSIF statement in Pl-SQL.

There are two types of CASE statements

  • Simple
  • Searched

Simple CASE statement

set serveroutput on;

declare
  n number default 1;
  country char(2) :='uk';

begin

    country :=UPPER(cOuntry);    

    case country
    when 'US' then
        dbms_output.put_line('United States of America');
    when 'UK' then
        dbms_output.put_line('United Kingdom');
    when  'IN' then
        dbms_output.put_line('India');
    else
        dbms_output.put_line('Other country');
END CASE;


end;		
		

ADS