מדריך PL-SQL – לולאות ותנאים

תנאים

אנו מציינים תנאים ב-PL/SQL ע"י המילים השמורות IF … ELSEIF … ELSE … END IF
כמובן, שאת הביטויים ELSE ו- ELSEIF ניתן להשמיט מהתנאי, אם אין לנו צורך בהם.

IF  THEN
   --Action
ELSEIF  THEN 
     --Action 
ELSE 
     --Action
END IF; 

לולאות

לולאה פשוטה

loop
    -- Sequence of statements; 
end loop; 

לולאה זו תסתיים אך ורק כאשר נשתמש בביטוי EXIT WHEN :

loop
    -- Sequence of statements; 
    EXIT WHEN 
end loop; 

לכן חשוב לא לזכוח לציין את תנאי העצירה, כדי לא להכנס ללולאה אינסופית.

סכום המספרים בין 1 ל-100:

--SUM OF 100 NUMBERS

Declare
    a number;
    s1 number default 0;
Begin
    a:=1;
    loop
        s1:=s1+a;
        exit when (a=100);
        a:=a+1;
    end loop;
    dbms_output.put_line('Sum between 1 to 100 is '||s1);
End;

WHILE LOOP

הלולאה תתבצע כל עוד התנאי המצויין מתקיים.

While [condition]
loop
    --sequence of statements
end loop;

דוגמא נוספת:

--SUM OF 100 ODD NUMBERS .. WHILE LOOP
declare
        n number;
        endvalue number;
        sum1 number default 0;
begin
        endvalue:=&endvalue;
        n:=1;
        while (n < endvalue)
        loop
             sum1:=sum1+n;
             n:=n+2;
        end loop;
        dbms_output.put_line('Sum of odd numbers between 1 and ' || endvalue || ' is ' || sum1);
end; 

FOR LOOP

זוהי לולאת מנייה, שתתבצע כל עוד המונה נמצא בטווח המספרים.

FOR i in 1..10 
loop
    --sequence of statements
end loop; 

לולאה המחשבת את סכום המספרים הזוגיים, מ-1 ועד למספר הנקלט מהמשתמש:

--SUM OF odd NUMBERS USING USER INPUT...for loop
declare
        n number;
        sum1 number default 0;
        endvalue number;
begin 
        endvalue:=&endvalue;
        n:=1;
        for n in 1.. endvalue
        loop
             if mod(n,2)=1
             then
                  sum1:=sum1+n;
             end if
        end loop;
        dbms_output.put_line('sum = ' || sum1);
end;

GOTO
הפקודה מציינת שיש לעבור למקום מסויים בקוד:

GOTO X; 
<< X >> 

--SEQUENTIAL CONTROL GOTO.....
declare
     --takes the default datatype of the column of the table price
     cost price.minprice%type;
begin
     select stdprice into cost from price where prodial in (Select prodid from product where prodese = "shampoo");
     if cost > 7000 then
         goto Upd;
     end if;
     << Upd >>
     Update price set minprice = 6999 where prodid=111;
end;

דוגמאות נוספות

--CALCULATION OF NET SALARY
declare
        ename varchar2(15); 
        basic number;
        da number;
        hra number;
        pf number;
        netsalary number;
begin 
        ename:=&ename;
        basic:=&basic;

        da:=basic * (41/100);
        hra:=basic * (15/100);
 
        if (basic < 3000)
        then
             pf:=basic * (5/100);
        elsif (basic >= 3000 and basic <= 5000)
        then 
             pf:=basic * (7/100);
        elsif (basic >= 5000 and basic <= 8000)
        then
             pf:=basic * (8/100);
        else
             pf:=basic * (10/100); 
        end if;
        netsalary:=basic + da + hra -pf; 
        dbms_output.put_line('Employee name : ' || ename);
        dbms_output.put_line('Providend Fund : ' || pf);
        dbms_output.put_line('Net salary : ' || netsalary);
end;

--MAXIMUM OF 3 NUMBERS
Declare
    a number;
    b number;
    c number;
Begin
    dbms_output.put_line('Enter a:');
    a:=&a;
    dbms_output.put_line('Enter b:');
    b:=&b;
    dbms_output.put_line('Enter c:');
    c:=&b;
    if (a>b) and (a>c) then
        dbms_output.putline('A is Maximum');
    elsif (b>a) and (b>c) then
        dbms_output.putline('B is Maximum');
    else
        dbms_output.putline('C is Maximum');
    end if;
End;

--QUERY EXAMPLE--IS SMITH EARNING ENOUGH
declare
        s1 emp.sal %type;
begin
        select sal into s1 from emp
        where ename = 'SMITH';
        if(no_data_found) 
        then
           raise_application_error
           (20001,'smith is not present');
        end if;

        if(s1 > 10000)
        then
           raise_application_error
           (20002,'smith is earning enough');
        end if;
  
        update emp set sal=sal + 500
        where ename='SMITH';
end;

--PRIME NO OR NOT
DECLARE
    no NUMBER (3) := &no;
    a NUMBER (4);
    b NUMBER (2);
BEGIN
    FOR i IN 2..no - 1
    LOOP
        a := no MOD i;
        IF a = 0
        THEN
            GOTO out;
        END IF;
    END LOOP;
    <>
    IF a = 1
    THEN
        DBMS_OUTPUT.PUT_LINE (no || ' is a prime number');
    ELSE
        DBMS_OUTPUT.PUT_LINE (no || ' is not a prime number');
    END IF;
END;

--SIMPLE EXAMPLE OF LOOP STATEMENT I.E. EXIT WHEN
Declare
     a number:= 100;
begin
     loop
          a := a+25;
         exit when a=250;
     end loop;
     dbms_output.put_line (to_Char(a));
end;

--EXAMPLE OF WHILE LOOP
Declare
     i number:=0;
     j number:= 0;
begin
     while i <=100 loop
          j := j+1;
         i := i +2;
     end loop;

     dbms_output.put_line(to_char(i));
end;

/*CALCULATE THE AREA OF A CIRCLE FOR A VALUE OF RADIUS
 VARYING FROM 3 TO 7. STORE THE RADIUS AND THE 
 CORRESPONDING VALUES OF CALCULATED AREA IN A TABLE AREAS.*/

Declare
     pi constant number(4,2) := 3.14;
     radius number(5);
     area number(14,2);

Begin
     radius := 3;
     While radius <=7
     Loop
          area := pi* power(radius,2);
          Insert into areas values (radius, area);
          radius:= radius+1;
     end loop;
end;

--INVERTING A NUMBER 5639 TO 9365
Declare
     given_number varchar(5) := '5639';
     str_length number(2);
     inverted_number varchar(5);

Begin
     str_length := length(given_number);
     For cntr in reverse 1..str_length
     loop
          inverted_number := inverted_number || substr(given_number, cntr, 1);
     end loop;
     dbms_output.put_line('The Given no is ' || given_number);
     dbms_output.put_line('The inverted number is ' || inverted_number);
end;