PL SQL Tutorial
PL SQL includes conditional (IF,CASE) structures as well as sequential control(GOTO,NULL)
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;
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.
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 tests multiple conditions in simple manner. It's a replacement for IF-ELSIF statement in Pl-SQL.
There are two types of CASE statements
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;
GOTO statement is used unconditionally jump to a named location. named locations are identified by << label >>.
Syntax:
GOTO Label1;Usage of GOTO Statement;
<< Label1 >>
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
ADS