Mega Code Archive

 

Categories / Oracle PLSQL / PL SQL

 

While loop and number counter

SQL> -- create demo table SQL> create table emp(   2    ID                 VARCHAR2(4 BYTE)         NOT NULL,   3    fname         VARCHAR2(10 BYTE),   4    lname          VARCHAR2(10 BYTE),   5    Start_Date         DATE,   6    End_Date           DATE,   7    Salary             Number(8,2),   8    City               VARCHAR2(10 BYTE),   9    Description        VARCHAR2(15 BYTE)  10  )  11  / Table created. SQL> SQL> SQL> SQL> -- prepare data SQL> insert into emp(ID,  fname, lname, Start_Date,                     End_Date,                       Salary,  City,       Description)   2               values ('01','Jason',    'Martin',  to_date('19960725','YYYYMMDD'), to_date('20060725','YYYYMMDD'), 1234.56, 'Toronto',  'Programmer')   3  / 1 row created. SQL> insert into emp(ID,  fname, lname, Start_Date,                     End_Date,                       Salary,  City,       Description)   2                values('02','Alison',   'Mathews', to_date('19760321','YYYYMMDD'), to_date('19860221','YYYYMMDD'), 6661.78, 'Vancouver','Tester')   3  / 1 row created. SQL> SQL> SQL> declare   2    s Number(8,2);   3    totalValue Number(8,2);   4    begin   5         WHILE totalValue < 100 LOOP   6              Select salary into s from emp;   7              totalValue := totalValue + s;   8         END LOOP;   9  10         dbms_output.put_line(totalValue);  11  end;  12  / PL/SQL procedure successfully completed. SQL> SQL> drop table emp; Table dropped. SQL>