Popular Posts

There was an error in this gadget

Jan 15, 2011

USING PL\SQL

DROP TABLE areas;
CREATE TABLE areas(
radius NUMBER(7,2),
area NUMBER(14,2)
);


DROP TABLE T_EMPLOYEE;
CREATE TABLE T_EMPLOYEE(
employee_id number,
employee_name varchar(50)
);

INSERT INTO T_EMPLOYEE VALUES('1','ABUL');
INSERT INTO T_EMPLOYEE VALUES('2','ABDUL');
INSERT INTO T_EMPLOYEE VALUES('3','ABir');
INSERT INTO T_EMPLOYEE VALUES('4','Alief');
INSERT INTO T_EMPLOYEE VALUES('5','ARIF');
INSERT INTO T_EMPLOYEE VALUES('6','ALIM');

CREATE TABLE RADIUS_VALS(
radius number(7,3)
);

INSERT INTO RADIUS_VALS VALUES('3');
INSERT INTO RADIUS_VALS VALUES('13');
INSERT INTO RADIUS_VALS VALUES('23');
INSERT INTO RADIUS_VALS VALUES('33');
INSERT INTO RADIUS_VALS VALUES('43');
INSERT INTO RADIUS_VALS VALUES('53');
INSERT INTO RADIUS_VALS VALUES('63');



<<<<<<<<<<<<<<<[Simple PL/SQL]>>>>>>>>>>>>>>>>>>>>>>>

DECLARE
    pi constant NUMBER(9,7) := 3.141534;
    radius INTEGER(5);
    area INTEGER(14,2);
BEGIN
    radius := 3;
    area := pi*power(radius,2);
    INSERT INTO AREAS VALUES(radius,area);
   
end;
/


<<<<<<<<<<<<<<<[EXCEPTION Handling]>>>>>>>>>>>>>>>>>>>>>>>


///////OUT PUT:ZERO VALUE INSERTED TO AREAS...AREA IS:...//////////////

SET SERVEROUTPUT ON;
DECLARE
      pi     constant NUMBER(9,7) := 3.1415927;
      radius INTEGER(5);
      area   NUMBER(14,2);
      some_variable   NUMBER(14,2);
BEGIN
      radius := 3;
      loop
            some_variable := 1/(radius-4);
            area := pi*power(radius,2);
                     insert into AREAS values (radius, area);
            radius := radius+1;
            exit when area >100;
      end loop;
 EXCEPTION
      when ZERO_DIVIDE
     then insert into AREAS values (0,0);
     DBMS_OUTPUT.PUT_LINE('Zero value inserted to AREAS. Area Is: '||area);
 end;
/



//////////////////////OUT PUT:HIGH AREA//////////////////////

SET SERVEROUTPUT ON;
DECLARE
      pi     constant NUMBER(9,7) := 3.1415927;
      radius INTEGER(5);
      area   NUMBER(14,2);
      some_variable   NUMBER(14,2);
    too_high_area  exception;
BEGIN
      radius := 3;
      loop
            some_variable := 1/(radius-4);
            area := pi*power(radius,2);
                     insert into AREAS values (radius, area);
            radius := radius+1;
       
    if area * 30 >100 then raise too_high_area;
    end if;

            exit when area >100;
      end loop;
 EXCEPTION
      when ZERO_DIVIDE
     then insert into AREAS values (0,0);
     DBMS_OUTPUT.PUT_LINE('Zero value inserted to AREAS. Area Is: '||area);
         when too_high_area then DBMS_OUTPUT.PUT_LINE('High AREA');

 end;
/







////////////////OUT PUT:NO DATA FOUND///////////////

DECLARE
    v_radius  NUMBER(10,3);
    v_area  NUMBER(7,3);
    too_high_area  exception;
BEGIN
    select radius, area into v_radius, v_area
        from areas where radius = '198';
    if v_radius * 30 > 20 then raise too_high_area;
    end if;
exception
    when NO_DATA_FOUND
    then DBMS_OUTPUT.PUT_LINE('No data found');
    when too_high_area then DBMS_OUTPUT.PUT_LINE('High AREA');
end;   
/





<<<<<<<<<[%type]>>>>>>>>>>>>>

DECLARE
    pi constant NUMBER(9,7) := 3.141532;
    radius areas.radius%type;
    area  areas.area%type;
BEGIN
    radius := 3;
    area := pi*power(radius,2);
    INSERT INTO AREAS VALUES(radius, area);
end;
/


<<<<<<<<<<<<<<<<<<<<[%rowtype]>>>>>>>>>>>>>>>>>>>>

DECLARE
    pi constant NUMBER(9,7) := 3.141523;
    value areas%rowtype;
BEGIN
    value.radius := 3;
    value.area := pi*power(value.radius,2);
    INSERT INTO AREAS VALUES(value.radius, value.area);
end;
/

<<<<<<<<<<<<<<<<<<<<[%rowtype](Select)>>>>>>>>>>>>>>>>>>>>

DECLARE
    pi constant NUMBER(9,7) := 3.141523;
    value areas%rowtype;
BEGIN
    SELECT MAX(RADIUS) INTO value.radius FROM areas;
    value.area := pi*power(value.radius,2);
    INSERT INTO AREAS VALUES(value.radius+1, value.area);
end;
/


<<<<<<<<<<<<<<<<<<[SELECT](NORMAL)>>>>>>>>>>>>>>>>>>>>>

SET SERVEROUTPUT ON;
DECLARE
    v_radius NUMBER;
    v_area NUMBER;
BEGIN
    SELECT radius, area into v_radius, v_area  FROM areas where radius=4;
    DBMS_OUTPUT.PUT_LINE('Radius Is:'|| v_radius||'AREA IS:'||v_area);
END;
/


<<<<<<<<<<<<<<<<[CURSOR]>>>>>>>>>>>>>>>>

SET SERVEROUTPUT ON;
DECLARE
    CURSOR empCursor IS select employee_id, employee_name from t_employee WHERE employee_name like 'A%';
    empvalue empCursor%rowtype;
BEGIN
    OPEN empCursor;
    FETCH empCursor INTO empvalue;
    DBMS_OUTPUT.PUT_LINE('EMPLOYEE ID IS:'||empvalue.employee_id);
    DBMS_OUTPUT.PUT_LINE('EMPLOYEE NAME IS:'||empvalue.employee_name);
    DBMS_OUTPUT.PUT_LINE('BY: itsjubayer.blogspot.com');
    CLOSE empCursor;
END;
/


<<<<<<<<<<<<<<<<<[SELECT ROWS USING CURSOR]>>>>>>>>>>>>>>>>>>>>>>>>>>

DECLARE
    CURSOR empCursor IS select employee_id, employee_name from t_employee;
    empvalue empCursor%rowtype;
    empvalue1 empCursor%rowtype;
BEGIN
    OPEN empCursor;
   
    FETCH empCursor INTO empvalue;
    DBMS_OUTPUT.PUT_LINE('EMPLOYEE ID IS:'||empvalue.employee_id);
    FETCH empCursor INTO empvalue;
    DBMS_OUTPUT.PUT_LINE('EMPLOYEE ID IS:'||empvalue.employee_id);
        FETCH empCursor INTO empvalue;
    DBMS_OUTPUT.PUT_LINE('EMPLOYEE ID IS:'||empvalue.employee_id);
    FETCH empCursor INTO empvalue;
    DBMS_OUTPUT.PUT_LINE('EMPLOYEE ID IS:'||empvalue.employee_id);
    FETCH empCursor INTO empvalue;
    DBMS_OUTPUT.PUT_LINE('EMPLOYEE ID IS:'||empvalue.employee_id);
    FETCH empCursor INTO empvalue;
    DBMS_OUTPUT.PUT_LINE('EMPLOYEE ID IS:'||empvalue.employee_id);
    CLOSE empCursor;
END;
/


<<<<<<<<<<<<[USING %FOUND, %NOTFOUND, %ISOPEN, %ROWCOUNT]>>>>>>>
// %NOTFOUND

DECLARE
    pi constant NUMBER(9,7) := 3.1415927;
    area NUMBER(14,2);
    CURSOR rad_cursor IS
        SELECT * FROM RADIUS_VALS;
    rad_val rad_cursor%ROWTYPE;
BEGIN
    OPEN rad_cursor;
    LOOP
    /* Within the loop, fetch a racord. */
    fetch rad_cursor into rad_val;
    /* IF the fetch attempt revels no more. */
    /* records in the cursor, then exit the loop. */
    EXIT WHEN rad_cursor%NOTFOUND;
    /* If the fetch attempt returned a record. */
    /* then process the radius value and insert */
    /* a record into the AREAS table . */
    area := pi*power(rad_val.radius, 2);
    INSERT INTO AREAS VALUES (rad_val.radius, 2);
    DBMS_OUTPUT.PUT_LINE(concat('Radius is: ', rad_val.radius));
    DBMS_OUTPUT.PUT_LINE(concat('Areas is: ',area));
END LOOP;
close rad_cursor;
end;
/



// %NOTFOUND

DECLARE
    pi constant NUMBER(9,7) := 3.1415927;
    area NUMBER(14,2);
    CURSOR rad_cursor IS
        SELECT * FROM RADIUS_VALS;
    rad_val rad_cursor%ROWTYPE;
BEGIN
    OPEN rad_cursor;
    LOOP
    fetch rad_cursor into rad_val;
    EXIT WHEN rad_cursor %NOTFOUND=true;
    area := pi*power(rad_val.radius, 2);
    INSERT INTO AREAS VALUES (rad_val.radius, 2);
    DBMS_OUTPUT.PUT_LINE(concat('Radius is: ', rad_val.radius));
    DBMS_OUTPUT.PUT_LINE(concat('Areas is: ',area));
END LOOP;
close rad_cursor;
end;
/








// %FOUND

DECLARE
    pi constant NUMBER(9,7) := 3.1415927;
    area NUMBER(14,2);
    CURSOR rad_cursor IS
        SELECT * FROM RADIUS_VALS;
    rad_val rad_cursor%ROWTYPE;
BEGIN
    OPEN rad_cursor;
    LOOP
    fetch rad_cursor into rad_val;
    EXIT WHEN rad_cursor %FOUND=false;
    area := pi*power(rad_val.radius, 2);
    INSERT INTO AREAS VALUES (rad_val.radius, 2);
    DBMS_OUTPUT.PUT_LINE(concat('Radius is: ', rad_val.radius));
    DBMS_OUTPUT.PUT_LINE(concat('Areas is: ',area));
END LOOP;
close rad_cursor;
end;
/




<<<<<<<<<<<<<<<<<<<<<<<<<<<<<[LOOP]>>>>>>>>>>>>>>>>>>>>>>>>>>

DECLARE
      pi     constant NUMBER(9,7) := 3.1415927;
      radius INTEGER(5);
      area   NUMBER(14,2);
BEGIN
    /*  Specify the criteria for the number of loop executions. */
      for radius in 1..7 loop
    /*  Calculate the area using the current Radius value.      */
          area := pi*power(radius,2);
    /*  Insert the area and radius values into the AREAS table.   */
      INSERT INTO AREAS values (radius, area);
    /*  Signal the end of the loop.                      */
 end loop;
    DBMS_OUTPUT.PUT_LINE('Successful');
end;
/


<<<<<<<<<<<<<<<<<<<<<<<<[WHILE LOOP]>>>>>>>>>>>>>>>>>>>>>>>>>

DECLARE
    pi     constant NUMBER(9,7) := 3.1415927;
    radius INTEGER(5);
    area   NUMBER(14,2);
BEGIN
   radius := 3;
   WHILE radius<=7
      /*  Begin the commands to be executed.             */
   LOOP
    if radius mod 2!=0 then
    area := pi*power(radius,2);
    DBMS_OUTPUT.PUT_LINE(concat('Radius is:',radius));
    DBMS_OUTPUT.PUT_LINE(concat('Areas is:', area)); 
    end if;      
    radius := radius+1;
   END LOOP;
end;
/










<<<<<<<<<<<<<<<<<<<<<<<<[IF/ELSE]>>>>>>>>>>>>>>>>>>>>>>>

DECLARE
    v_gift varchar2(20);
    c_retailprice NUMBER(5,2) := 29.95;
BEGIN
    if c_retailprice > 56 THEN
        v_gift := 'FREE SHIPPING';
    ELSIF c_retailprice > 25 THEN
        v_gift := 'BOOKCOVER';
    ELSE
        v_gift := 'BOOKMARKER';
END IF;
DBMS_OUTPUT.PUT_LINE('THE gift for a book costing'||c_retailprice||'is a'||v_gift);
END;
/

<<<<<<<<<<<<<<<<<[BASIC LOOP]>>>>>>>>>>>>>>>>>   

DECLARE
    v_counter number(1) :=0;
BEGIN
    LOOP
        v_counter := v_counter+1;
        DBMS_OUTPUT.PUT_LINE('THE COURRENT value of the counter is: '|| v_counter);
        EXIT WHEN v_counter = 4;
    END LOOP;
END;
/


<<<<<<<<<<<<<<<<<<[FOR LOOP]>>>>>>>>>>>>>>>>>>>
SYSTAX:
FOR counter IN [reverse] lower_limit .. upper_limit
LOOP
sequence of statements;
END LOOP;



BEGIN
    FOR i IN 1..10 LOOP
    DBMS_OUTPUT.PUT_LINE('THE CURRENT VALUE OF THE COUNTER IS ' ||i);
    END LOOP;
END;
/





<<<<<<<<<<<<<<<[WHILE LOOP(if condition is true then exit)]>>>>>>>>>>>>>>
Syntax:

    WHILE condition LOOP
        Statements;
    END LOOP;



DECLARE
    v_counter NUMBER(2) := 0;
BEGIN
    WHILE v_counter < 15
        LOOP
    DBMS_OUTPUT.PUT_LINE('THE CURRENT VALUE OF THE COUNTER IS '|| v_counter);
    v_counter := v_counter + 1;
       END LOOP;
END;
/


<<<<<<<<<<<<<<<<<<<<<[INSERT to table using simple loop]>>>>>>>>>>>>>>>>>>>>>>

DECLARE
    pi constant number(9,7) := 3.1415247;
    radius INTEGER(5);
    area NUMBER(14,2);
BEGIN
    radius := 3;
    LOOP
    area := pi*power(radius,2);
    INSERT INTO AREAS VALUES(radius, area);
    radius := radius+1;
    exit when area > 100;
end loop;
        DBMS_OUTPUT.PUT_LINE('Value Inserted');
end;
/

<<<<<<<<<<<<<<<<<<<<<[INSERT to table using for loop]>>>>>>>>>>>>>>>>>>>>>>


DECLARE
    pi constant number(9,7) := 3.1415162;
    radius INTEGER(5);
    area NUMBER(14,2);
    vcount number(3) default 0;
BEGIN
    for radius in 1..7
    LOOP
        area := pi*power(radius,2);
        vcount := vcount + 1;
        insert into areas values (radius, area);
    DBMS_OUTPUT.PUT_LINE('VALUE INSERTED: '||vcount);
    end LOOP;
    DBMS_OUTPUT.PUT_LINE('INSERTED SUCCESSFUL');
end;
/


<<<<<<<<<<<<<<<<<[INSERT to table using for loop(REVERSE)]>>>>>>>>>>>>>>>>>>

DECLARE
    pi constant NUMBER(9,7) := 3.1415927;
    radius integer(5);
    area number(14,2);
BEGIN
    for radius in 1..7
    LOOP
    area := pi*power(radius,2);
    INSERT INTO AREAS VALUES(radius, area);
    DBMS_OUTPUT.PUT_LINE('Radius is: '||radius);
    END LOOP;
end;
/


<<<<<<<<<<<<<<<<<[Simple While LOOP]>>>>>>>>>>>>>>>>>>>>

DECLARE
    pi constant NUMBER(9,7) := 3.1415166;
    radius INTEGER(5) default 0;
    area NUMBER(14,2) default 0;
BEGIN
    DBMS_OUTPUT.PUT_LINE('****************************************');
    radius := 3;
    while area<100
      LOOP
        area := pi*power(radius,2);
        DBMS_OUTPUT.PUT_LINE(concat('Radius is: ', radius));
        DBMS_OUTPUT.PUT_LINE(concat('Areas is: ', area));
        radius := radius+1;
    END LOOP;
    DBMS_OUTPUT.PUT_LINE('BY:JUBAYER');
end;
/





<<<<<<<<<<<<<<<<<<<<<<<[Nested LOOPS]>>>>>>>>>>>>>>>>>>>>>>>>>>>

DECLARE
    v_counter NUMBER(2) := 0;
BEGIN
    while v_counter < 3 LOOP
    FOR i IN 1 .. 2 LOOP
       DBMS_OUTPUT.PUT_LINE('THE courrent value of the for loop is '||i);
    END LOOP;
    DBMS_OUTPUT.PUT_LINE('The current value of the WHILE counter is '|| v_counter);
    v_counter := v_counter + 1;
     END LOOP;
END;
/











 <<<<<<<<<<<<<<<<<<<<<<<[CONTINUE]>>>>>>>>>>>>>>>>>>>>>>>>>>>

DECLARE
                  pi  constant NUMBER(9,7) := 3.1415927;
                 radius INTEGER(5);
                 area   NUMBER(14,2);
BEGIN
                radius := 0;
  LOOP
              radius := radius+1;
             continue when radius = 2;
             area := pi*power(radius,2);
             DBMS_OUTPUT.PUT_LINE(concat(radius, area));
  exit when area >100;
  END LOOP;
end;
/

No comments:

Post a Comment