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;
			
		

PLSQL IF,IF-ELSE,IF-ELIF statements Examples

		DECLARE
		   C CHAR(1);
		BEGIN
		   IF C IS NULL THEN
		      DBMS_OUTPUT.PUT_LINE('C IS NULL');
		   END IF;
		   
		   C:= 'E';
		   IF C IS NOT NULL THEN
		    DBMS_OUTPUT.PUT_LINE('C IS NOT NULL');
		    END IF;
		    
		   IF  MOD(100,2)=0 THEN
		      DBMS_OUTPUT.PUT_LINE('EVEN');
		   END IF;
		END;		
		

OUTPUT:

C IS NULL
C IS NOT NULL
EVEN
		

DECLARE
   RATING CHAR(1);
   MOVIE  VARCHAR2(20);
   MSG  VARCHAR2(25);
 -- movie ratings  H, F, A,P
BEGIN
    MOVIE := 'STAR WARS II';
    RATING := 'H';
    IF RATING = 'H' THEN MSG:='HIT';
    ELSIF RATING ='F' THEN MSG:='FLOP';
    ELSIF RATING ='A' THEN MSG:='AVERAGE';
    ELSIF RATING ='P' THEN MSG:='POOR';
    END IF;
    
    DBMS_OUTPUT.PUT_LINE('MOVIE: '||MOVIE ||' RATING ...'||MSG);
END;		
		
		

OUTPUT:

MOVIE: STAR WARS II RATING ...HIT


PL/SQL procedure successfully completed.
	
	

PL SQL Program to Check a Leaf Year

     Leap Year has 366 days,Year is Divisible by 4, evenly divisible by 100,evenly divisble by 400 then it is a Leaf Year ,Otherwise Not a Leaf Year

SET SERVEROUTPUT ON
DECLARE
   YEAR_TO_CHECK NUMBER(4) := EXTRACT(YEAR FROM SYSDATE);
   MSG VARCHAR2(25):= ' IS NOT A LEAR YEAR';
BEGIN

--YEAR_TO_CHECK:=2000;

        IF MOD(YEAR_TO_CHECK,4) = 0  AND MOD(YEAR_TO_CHECK,100) = 0 AND MOD(YEAR_TO_CHECK,400) = 0 
        THEN
        
                    MSG := YEAR_TO_CHECK||' IS A LEAP YEAR';
                
        ELSE
                    MSG := YEAR_TO_CHECK || MSG;
        END IF;
        
        DBMS_OUTPUT.PUT_LINE(MSG);

END;

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;		
		

PL/SQL GOTO Statement

GOTO statement is used unconditionally jump to a named location. named locations are identified by << label >>.
Syntax:

			GOTO Label1; 

			
			<< Label1 >>
			

Usage of GOTO Statement;

DECLARE
   SAY VARCHAR2(10):='HELLO';
   N NUMBER :=1;
BEGIN
  DBMS_OUTPUT.PUT_LINE('GOTO LABEL DEMO');
 <<START_LABEL>>
          DBMS_OUTPUT.PUT_LINE(SUBSTR(SAY,1,N));
          N:=N+1;
          IF N>LENGTH(SAY) THEN RETURN; END IF;
  GOTO START_LABEL;
END;

SQL>/
GOTO LABEL DEMO
-----------------
H
HE
HEL
HELL
HELLO