PL/SQL Basics

Basic Blocks

DECLARE      
 v_width INTEGER;
 v_height INTEGER := 2;
 v_area INTEGER ;
 BEGIN
v_area := 6;
v_width := v_area / v_height;
DBMS_OUTPUT.PUT_LINE('width = ' || width);
EXCEPTION
WHEN ZERO_DIVIDE THEN
DBMS_OUTPUT.PUT_LINE('Division by zero');
END;

IF clause:

DECLARE
v_A Number := 50;
 v_B Number;
BEGIN
IF v_A > 40 THEN
v_B := v_A - 40;
DBMS_OUTPUT.PUT_LINE('V_B = ' || v_B);
 ELSE
v_B := 0;
DBMS_OUTPUT.PUT_LINE('V_B = ' || v_B);
END IF;
END;

/**IF -THEN-ELSE LADDER*/

DECLARE
 v_Score Number := 85; --Percentage
v_LetterGrade Char(1);
BEGIN
IF v_Score >= 90 THEN
v_LetterGrade := 'A';
ELSIF v_Score >= 80 THEN
v_LetterGrade := 'B';
ELSIF v_Score >= 70 THEN
v_LetterGrade := 'C';
ELSIF v_Score >= 60 THEN
v_LetterGrade := 'D';
ELSE
v_LetterGrade := 'E';
END IF;
DBMS_OUTPUT.PUT_LINE('Your Letter Grade is: ' || v_LetterGrade);
END;

declare
v NUMBER;
begin
if v=1 then
DBMS_OUTPUT.put_line('Equal to 1');
elsif v!= 1 then
DBMS_OUTPUT.put_line('Not equal to 1');
elsif v = v then
DBMS_OUTPUT.put_line('Equal to itself');
else
DBMS_OUTPUT.put_line('Undefined result');
end if;
v:= v+1;
DBMS_OUTPUT.put_line('New value: <'||v||'>');
end;

LOOPS

DECLARE
 hundreds_counter  NUMBER(1,-2);
BEGIN
hundreds_counter := 100;
LOOP
DBMS_OUTPUT.PUT_LINE(hundreds_counter);
hundreds_counter := hundreds_counter + 100;
END LOOP;
EXCEPTION
WHEN OTHERS THEN
DBMS_OUTPUT.PUT_LINE('Done.');
END;

DECLARE
     v_Radius NUMBER := 2;
     MYPI NUMBER:=3.14;
BEGIN
LOOP
DBMS_OUTPUT.PUT_LINE('The AREA of the circle is ' ||
                         v_RADIUS*v_RADIUS * mypi);
  v_Radius := v_Radius + 2;
  EXIT WHEN v_Radius > 10;
  END LOOP;
END;
  /

 BEGIN
FOR just_a_num IN 1..10 LOOP
 dbms_output.put_line(just_a_num);
END LOOP;
END;
 /

DECLARE
V_LOW INTEGER:=1;
V_HIGH INTEGER:=10;
BEGIN
FOR JUST_A_NUM IN V_LOW..V_HIGH LOOP
DBMS_OUTPUT.PUT_LINE(JUST_A_NUM);
END LOOP;
END;


BEGIN
 FOR v_loopcounter IN 1..5 LOOP
 DBMS_OUTPUT.PUT_LINE('Loop counter is ' || v_loopcounter);
END LOOP;
END;
 /


DECLARE
 v_Start Integer := 1;
BEGIN
FOR v_loopcounter IN REVERSE v_Start..5 LOOP
DBMS_OUTPUT.PUT_LINE('Loop counter is ' || v_loopcounter);
END LOOP;
END;
 /
DECLARE
MYPI NUMBER:=3.14;
BEGIN
FOR v_loopcounter IN 1..20 LOOP
IF MOD(v_loopcounter,2) = 0 THEN
 DBMS_OUTPUT.PUT_LINE('The AREA of the circle is ' ||v_loopcounter*v_loopcounter * mypi);
END IF;
IF v_loopcounter = 10 THEN
EXIT;
END IF;
END LOOP;
END;
/



BEGIN
<<outerloop>>
FOR v_outerloopcounter IN 1..2 LOOP
<<innerloop>>
FOR v_innerloopcounter IN 1..4 LOOP
DBMS_OUTPUT.PUT_LINE('Outer Loop counter is ' ||v_outerloopcounter ||
' Inner Loop counter is ' ||v_innerloopcounter);
END LOOP innerloop;
END LOOP outerloop;
END;
 /


DECLARE
v_Status NUMBER := 1;
BEGIN
IF v_Status = 1 THEN
GOTO mybranch;
ELSE
v_Status := 1;
END IF;
<<mybranch>>
NULL;
END;
/


DECLARE
v_Radius NUMBER := 2;
MYPI NUMBER:=3.14;
BEGIN
WHILE v_Radius <=10 LOOP
DBMS_OUTPUT.PUT_LINE('The Area is ' ||mypi * v_Radius * v_Radius);
v_Radius := v_Radius + 2 ;
END LOOP;
END;
/


DECLARE
v_Radius NUMBER := 2;
MYPI NUMBER:=3.14;
BEGIN
WHILE TRUE LOOP
DBMS_OUTPUT.PUT_LINE('The Area is '||mypi * v_Radius * v_Radius);
IF v_Radius = 10 THEN
EXIT;
END IF;
v_Radius := v_Radius + 2 ;
END LOOP;
 END;
 /