Oracle PL/SQL/PL SQL/IF

Материал из SQL эксперт
Перейти к: навигация, поиск

A conditional statement

   <source lang="sql">
    

SQL> SQL> SQL> CREATE TABLE lecturer (

 2    id               NUMBER(5) PRIMARY KEY,
 3    first_name       VARCHAR2(20),
 4    last_name        VARCHAR2(20),
 5    major            VARCHAR2(30),
 6    current_credits  NUMBER(3)
 7    );

Table created. SQL> SQL> INSERT INTO lecturer (id, first_name, last_name, major,current_credits)

 2                VALUES (10001, "Scott", "Lawson","Computer Science", 11);

1 row created. SQL> SQL> INSERT INTO lecturer (id, first_name, last_name, major, current_credits)

 2                VALUES (10002, "Mar", "Wells","History", 4);

1 row created. SQL> SQL> INSERT INTO lecturer (id, first_name, last_name, major,current_credits)

 2                VALUES (10003, "Jone", "Bliss","Computer Science", 8);

1 row created. SQL> SQL> INSERT INTO lecturer (id, first_name, last_name, major,current_credits)

 2                VALUES (10004, "Man", "Kyte","Economics", 8);

1 row created. SQL> SQL> INSERT INTO lecturer (id, first_name, last_name, major,current_credits)

 2                VALUES (10005, "Pat", "Poll","History", 4);

1 row created. SQL> SQL> INSERT INTO lecturer (id, first_name, last_name, major,current_credits)

 2                VALUES (10006, "Tim", "Viper","History", 4);

1 row created. SQL> SQL> INSERT INTO lecturer (id, first_name, last_name, major,current_credits)

 2                VALUES (10007, "Barbara", "Blues","Economics", 7);

1 row created. SQL> SQL> INSERT INTO lecturer (id, first_name, last_name, major,current_credits)

 2                VALUES (10008, "David", "Large","Music", 4);

1 row created. SQL> SQL> INSERT INTO lecturer (id, first_name, last_name, major,current_credits)

 2                VALUES (10009, "Chris", "Elegant","Nutrition", 8);

1 row created. SQL> SQL> INSERT INTO lecturer (id, first_name, last_name, major,current_credits)

 2                VALUES (10010, "Rose", "Bond","Music", 7);

1 row created. SQL> SQL> INSERT INTO lecturer (id, first_name, last_name, major,current_credits)

 2                VALUES (10011, "Rita", "Johnson","Nutrition", 8);

1 row created. SQL> SQL> INSERT INTO lecturer (id, first_name, last_name, major,current_credits)

 2                VALUES (10012, "Sharon", "Clear","Computer Science", 3);

1 row created. SQL> SQL> CREATE TABLE MyTable (

 2    num_col    NUMBER,
 3    char_col   VARCHAR2(60)
 4    );

Table created. SQL> SQL> SQL> SQL> DECLARE

 2    v_Totallecturer NUMBER;
 3  BEGIN
 4    SELECT COUNT(*)
 5      INTO v_Totallecturer
 6      FROM lecturer;
 7
 8    IF v_Totallecturer = 0 THEN
 9      INSERT INTO MyTable (char_col) VALUES ("There are no lecturers registered");
10    ELSIF v_Totallecturer < 5 THEN
11      INSERT INTO MyTable (char_col) VALUES ("There are only a few lecturers registered");
12    ELSIF v_Totallecturer < 10 THEN
13      INSERT INTO MyTable (char_col) VALUES ("There are a little more lecturers registered");
14    ELSE
15      INSERT INTO MyTable (char_col) VALUES ("There are many lecturers registered");
16    END IF;
17  END;
18  /

PL/SQL procedure successfully completed. SQL> SQL> select * from MyTable;

NUM_COL CHAR_COL

------------------------------------------------------------
        There are many lecturers registered

SQL> SQL> drop table MyTable; Table dropped. SQL> drop table lecturer; Table dropped. SQL>



 </source>
   
  


Adding ELSE to the IF block

   <source lang="sql">
   

SQL> SQL> -- Adding ELSE to the IF block. SQL> set echo on SQL> DECLARE

 2    v_HoursWorked Number := 50 ;
 3    v_OverTime Number ;
 4  BEGIN
 5    IF v_HoursWorked > 40 THEN
 6      v_OverTime := v_HoursWorked - 40;
 7         DBMS_OUTPUT.PUT_LINE("Hours overtime worked = " || v_OverTime);
 8    ELSE
 9      v_OverTime := 0;
10    END IF;
11  END;
12  /

Hours overtime worked = 10 PL/SQL procedure successfully completed. SQL>



 </source>
   
  


An if-then-elsif-then-else statement where the first two comparisons are true and the third false

   <source lang="sql">
   

SQL> SQL> DECLARE

 2    equal BOOLEAN NOT NULL := TRUE;
 3  BEGIN
 4    IF 1 = 1 THEN
 5      dbms_output.put_line("Condition one met!");
 6    ELSIF equal THEN
 7      dbms_output.put_line("Condition two met!");
 8    ELSIF 1 = 2 THEN
 9      dbms_output.put_line("Condition three met!");
10    END IF;
11  END;
12  /

Condition one met! PL/SQL procedure successfully completed.



 </source>
   
  


Check number value in if statement

   <source lang="sql">
    

SQL> SQL> SQL> SET ECHO ON SQL> SQL> DECLARE

 2      v_MyNumber NUMBER := 0;
 3  BEGIN
 4      LOOP
 5          IF v_MyNumber = 7 THEN
 6               EXIT;
 7       END IF;
 8      v_MyNumber := v_MyNumber + 1;
 9      END LOOP;
10  END;
11  /

PL/SQL procedure successfully completed. SQL> SQL> --



 </source>
   
  


Compare three variables with if statement

   <source lang="sql">
   

SQL> CREATE TABLE book (

 2    isbn      CHAR(10) PRIMARY KEY,
 3    category  VARCHAR2(20),
 4    title     VARCHAR2(100),
 5    num_pages NUMBER,
 6    price     NUMBER,
 7    copyright NUMBER(4),
 8    emp1   NUMBER,
 9    emp2   NUMBER,
10    emp3   NUMBER
11  );

Table created. SQL> SQL> INSERT INTO book (isbn, category, title, num_pages, price, copyright, emp1, emp2, emp3)

 2             VALUES ("1", "Database", "Oracle", 563, 39.99, 1999, 1, 2, 3);

1 row created. SQL> INSERT INTO book (isbn, category, title, num_pages, price, copyright, emp1, emp2)

 2             VALUES ("2", "Database", "MySQL", 765, 44.99, 1999, 4, 5);

1 row created. SQL> INSERT INTO book (isbn, category, title, num_pages, price, copyright, emp1, emp2, emp3)

 2             VALUES ("3", "Database", "SQL Server", 404, 39.99, 2001, 6, 7, 8);

1 row created. SQL> SQL> DECLARE

 2    e_Duplicateemp EXCEPTION;
 3
 4    v_emp1 book.emp1%TYPE;
 5    v_emp2 book.emp2%TYPE;
 6    v_emp3 book.emp3%TYPE;
 7  BEGIN
 8    SELECT emp1, emp2, emp3
 9      INTO v_emp1, v_emp2, v_emp3
10      FROM book
11      WHERE title = "XML";
12
13    IF (v_emp1 = v_emp2) OR (v_emp1 = v_emp3) OR(v_emp2 = v_emp3) THEN
14       RAISE e_Duplicateemp;
15    END IF;
16  EXCEPTION
17    WHEN e_Duplicateemp THEN
18      INSERT INTO log_table (info) VALUES ("XML has duplicate emp");
19  END;
20  /

SQL> SQL> drop table book; Table dropped.



 </source>
   
  


Create a function and call it in an if statement

   <source lang="sql">
   

SQL> CREATE TABLE emp (

 2    id         NUMBER PRIMARY KEY,
 3    fname VARCHAR2(50),
 4    lname  VARCHAR2(50)
 5  );

Table created. SQL> SQL> CREATE TABLE books (

 2    isbn      CHAR(10) PRIMARY KEY,
 3    category  VARCHAR2(20),
 4    title     VARCHAR2(100),
 5    num_pages NUMBER,
 6    price     NUMBER,
 7    copyright NUMBER(4),
 8    emp1   NUMBER,
 9    emp2   NUMBER,
10    emp3   NUMBER
11  );

Table created. SQL> SQL> SQL> CREATE OR REPLACE FUNCTION Threeemp(p_ISBN IN books.isbn%TYPE)

 2    RETURN BOOLEAN AS
 3
 4    v_emp3 books.emp3%TYPE;
 5  BEGIN
 6    SELECT emp3 INTO v_emp3 FROM books WHERE isbn = p_ISBN;
 7
 8    IF v_emp3 IS NULL THEN
 9      RETURN FALSE;
10    ELSE
11      RETURN TRUE;
12    END IF;
13  END Threeemp;
14  /

Function created. SQL> SQL> set serveroutput on SQL> SQL> BEGIN

 2    FOR v_Rec IN (SELECT ISBN, title FROM books) LOOP
 3      IF Threeemp(v_Rec.ISBN) THEN
 4        DBMS_OUTPUT.PUT_LINE(""" || v_Rec.title || "" has 3 emp");
 5      END IF;
 6    END LOOP;
 7  END;
 8  /

PL/SQL procedure successfully completed. SQL> drop table books; Table dropped. SQL> drop table emp; Table dropped.



 </source>
   
  


Exit a loop with condition

   <source lang="sql">
    

SQL> SQL> SET ECHO ON SQL> SQL> DECLARE

 2      v_MyNumber NUMBER := 0;
 3  BEGIN
 4      LOOP
 5          IF v_MyNumber > 7 THEN
 6              EXIT;
 7          ENd if;
 8          v_MyNumber := v_MyNumber + 1;
 9      END LOOP;
10  END;
11  /

PL/SQL procedure successfully completed. SQL> SQL> SQL> SQL> --



 </source>
   
  


If condition meets, throw exception

   <source lang="sql">
   

SQL> CREATE TABLE EMP(

 2      EMPNO NUMBER(4) NOT NULL,
 3      ENAME VARCHAR2(10),
 4      JOB VARCHAR2(9),
 5      MGR NUMBER(4),
 6      HIREDATE DATE,
 7      SAL NUMBER(7, 2),
 8      COMM NUMBER(7, 2),
 9      DEPTNO NUMBER(2)
10  );

Table created. SQL> SQL> INSERT INTO EMP VALUES(7369, "SMITH", "CLERK", 7902,TO_DATE("17-DEC-1980", "DD-MON-YYYY"), 800, NULL, 20); 1 row created. SQL> INSERT INTO EMP VALUES(7499, "ALLEN", "SALESMAN", 7698,TO_DATE("20-FEB-1981", "DD-MON-YYYY"), 1600, 300, 30); 1 row created. SQL> INSERT INTO EMP VALUES(7521, "WARD", "SALESMAN", 7698,TO_DATE("22-FEB-1981", "DD-MON-YYYY"), 1250, 500, 30); 1 row created. SQL> INSERT INTO EMP VALUES(7566, "JONES", "MANAGER", 7839,TO_DATE("2-APR-1981", "DD-MON-YYYY"), 2975, NULL, 20); 1 row created. SQL> INSERT INTO EMP VALUES(7654, "MARTIN", "SALESMAN", 7698,TO_DATE("28-SEP-1981", "DD-MON-YYYY"), 1250, 1400, 30); 1 row created. SQL> INSERT INTO EMP VALUES(7698, "BLAKE", "MANAGER", 7839,TO_DATE("1-MAY-1981", "DD-MON-YYYY"), 2850, NULL, 30); 1 row created. SQL> INSERT INTO EMP VALUES(7782, "CLARK", "MANAGER", 7839,TO_DATE("9-JUN-1981", "DD-MON-YYYY"), 2450, NULL, 10); 1 row created. SQL> INSERT INTO EMP VALUES(7788, "SCOTT", "ANALYST", 7566,TO_DATE("09-DEC-1982", "DD-MON-YYYY"), 3000, NULL, 20); 1 row created. SQL> INSERT INTO EMP VALUES(7839, "KING", "PRESIDENT", NULL,TO_DATE("17-NOV-1981", "DD-MON-YYYY"), 5000, NULL, 10); 1 row created. SQL> INSERT INTO EMP VALUES(7844, "TURNER", "SALESMAN", 7698,TO_DATE("8-SEP-1981", "DD-MON-YYYY"), 1500, 0, 30); 1 row created. SQL> INSERT INTO EMP VALUES(7876, "ADAMS", "CLERK", 7788,TO_DATE("12-JAN-1983", "DD-MON-YYYY"), 1100, NULL, 20); 1 row created. SQL> INSERT INTO EMP VALUES(7900, "JAMES", "CLERK", 7698,TO_DATE("3-DEC-1981", "DD-MON-YYYY"), 950, NULL, 30); 1 row created. SQL> INSERT INTO EMP VALUES(7902, "FORD", "ANALYST", 7566,TO_DATE("3-DEC-1981", "DD-MON-YYYY"), 3000, NULL, 20); 1 row created. SQL> INSERT INTO EMP VALUES(7934, "MILLER", "CLERK", 7782,TO_DATE("23-JAN-1982", "DD-MON-YYYY"), 1300, NULL, 10); 1 row created. SQL> SQL> create or replace procedure sal_check( p_deptno in number)is

 2          avg_sal number;
 3          max_sal number;
 4  begin
 5          select avg(sal), max(sal)
 6          into   avg_sal,  max_sal
 7          from emp
 8          where deptno = p_deptno;
 9          if ( max_sal/2 > avg_sal)then
10             raise_application_error(-20001,"Rule violated");
11          end if;
12  end;
13  /

Procedure created. SQL> SQL> drop table emp; Table dropped.



 </source>
   
  


If count() is 0, insert data

   <source lang="sql">
   

SQL> CREATE TABLE emp (

 2    id         NUMBER PRIMARY KEY,
 3    fname VARCHAR2(50),
 4    lname  VARCHAR2(50)
 5  );

Table created. SQL> SQL> INSERT INTO emp (id, fname, lname)VALUES (1, "A", "B"); 1 row created. SQL> INSERT INTO emp (id, fname, lname)VALUES (2, "C", "D"); 1 row created. SQL> INSERT INTO emp (id, fname, lname)VALUES (3, "Enn", "F"); 1 row created. SQL> INSERT INTO emp (id, fname, lname)VALUES (4, "G", "H"); 1 row created. SQL> INSERT INTO emp (id, fname, lname)VALUES (5, "G", "Z"); 1 row created. SQL> SQL> SQL> DECLARE

 2
 3     v_count PLS_INTEGER := 0;
 4
 5  BEGIN
 6     SELECT COUNT(1) INTO v_count FROM emp WHERE id = 55;
 7
 8     IF v_count = 0
 9     THEN
10        INSERT INTO emp VALUES (54, "R", "W");
11        DBMS_OUTPUT.PUT_LINE("Added emp");
12     ELSE
13        DBMS_OUTPUT.PUT_LINE("emp already exists");
14     END IF;
15
16     COMMIT;
17
18  EXCEPTION
19     WHEN OTHERS
20     THEN
21        DBMS_OUTPUT.PUT_LINE(SQLERRM);
22  END;
23  /

Added emp PL/SQL procedure successfully completed. SQL> drop table emp; Table dropped. SQL>



 </source>
   
  


IF, ELSIF ELSE and END IF

   <source lang="sql">
   

SQL> SQL> -- The emptype function. SQL> CREATE OR REPLACE FUNCTION emptype (paytype CHAR)

 2          RETURN VARCHAR2 IS
 3  BEGIN
 4       IF paytype = "H" THEN
 5            RETURN "Hourly";
 6       ELSIF paytype = "S" THEN
 7            RETURN "Salaried";
 8       ELSIF paytype = "E" THEN
 9            RETURN "Executive";
10       ELSE
11            RETURN "Invalid Type";
12       END IF;
13  EXCEPTION
14       WHEN OTHERS THEN
15            RETURN "Error Encountered";
16  END emptype;
17  /

Function created. SQL> SQL> select emptype("H") from dual; EMPTYPE("H")


Hourly SQL> SQL> select emptype("S") from dual; EMPTYPE("S")


Salaried SQL> SQL> select emptype("E") from dual; EMPTYPE("E")


Executive SQL>



 </source>
   
  


IF...ELSIF...ELSE... END IF

   <source lang="sql">
    

SQL> SQL> CREATE TABLE place (

 2    room_id          NUMBER(5) PRIMARY KEY,
 3    building         VARCHAR2(15),
 4    room_number      NUMBER(4),
 5    number_seats     NUMBER(4),
 6    description      VARCHAR2(50)
 7    );

Table created. SQL> SQL> INSERT INTO place (room_id, building, room_number, number_seats, description)

 2             VALUES (20001, "Building 7", 201, 1000, "Large Lecture Hall");

1 row created. SQL> SQL> INSERT INTO place (room_id, building, room_number, number_seats, description)

 2             VALUES (20002, "Building 6", 101, 500, "Small Lecture Hall");

1 row created. SQL> SQL> INSERT INTO place (room_id, building, room_number, number_seats, description)

 2             VALUES (20003, "Building 6", 150, 50, "Discussion Room A");

1 row created. SQL> SQL> INSERT INTO place (room_id, building, room_number, number_seats, description)

 2             VALUES (20004, "Building 6", 160, 50, "Discussion Room B");

1 row created. SQL> SQL> INSERT INTO place (room_id, building, room_number, number_seats,description)

 2             VALUES (20005, "Building 6", 170, 50, "Discussion Room C");

1 row created. SQL> SQL> INSERT INTO place (room_id, building, room_number, number_seats, description)

 2             VALUES (20006, "Music Building", 100, 10, "Music Practice Room");

1 row created. SQL> SQL> INSERT INTO place (room_id, building, room_number, number_seats, description)

 2             VALUES (20007, "Music Building", 200, 1000, "Concert Room");

1 row created. SQL> SQL> INSERT INTO place (room_id, building, room_number, number_seats, description)

 2             VALUES (20008, "Building 7", 300, 75, "Discussion Room D");

1 row created. SQL> SQL> INSERT INTO place (room_id, building, room_number, number_seats,description)

 2             VALUES (20009, "Building 7", 310, 50, "Discussion Room E");

1 row created. SQL> SQL> SQL> select * from place;

ROOM_ID BUILDING        ROOM_NUMBER NUMBER_SEATS DESCRIPTION

--------------- ----------- ------------ --------------------------------------------------
                1. Building 7 201.00 1000.00 Large Lecture Hall
                2. Building 6 101.00 500.00 Small Lecture Hall
                3. Building 6 150.00 50.00 Discussion Room A
                4. Building 6 160.00 50.00 Discussion Room B
                5. Building 6 170.00 50.00 Discussion Room C
                6. Music Building 100.00 10.00 Music Practice Room
                7. Music Building 200.00 1000.00 Concert Room
                8. Building 7 300.00 75.00 Discussion Room D
                9. Building 7 310.00 50.00 Discussion Room E

9 rows selected. SQL> SQL> DECLARE

 2    v_NumberSeats place.number_seats%TYPE;
 3    v_Comment VARCHAR2(35);
 4  BEGIN
 5    SELECT number_seats
 6      INTO v_NumberSeats
 7      FROM place
 8      WHERE room_id = 20008;
 9    IF v_NumberSeats < 50 THEN
10      v_Comment := "Fairly small";
11    ELSIF v_NumberSeats < 100 THEN
12      v_Comment := "A little bigger";
13    ELSE
14      v_Comment := "Lots of room";
15    END IF;
16  END;
17  /

PL/SQL procedure successfully completed. SQL> SQL> select * from place;

ROOM_ID BUILDING        ROOM_NUMBER NUMBER_SEATS DESCRIPTION

--------------- ----------- ------------ --------------------------------------------------
                1. Building 7 201.00 1000.00 Large Lecture Hall
                2. Building 6 101.00 500.00 Small Lecture Hall
                3. Building 6 150.00 50.00 Discussion Room A
                4. Building 6 160.00 50.00 Discussion Room B
                5. Building 6 170.00 50.00 Discussion Room C
                6. Music Building 100.00 10.00 Music Practice Room
                7. Music Building 200.00 1000.00 Concert Room
                8. Building 7 300.00 75.00 Discussion Room D
                9. Building 7 310.00 50.00 Discussion Room E

9 rows selected. SQL> SQL> SQL> drop table place; Table dropped. SQL>



 </source>
   
  


If ladder

   <source lang="sql">
    

SQL> SQL> SQL> CREATE TABLE lecturer (

 2    id               NUMBER(5) PRIMARY KEY,
 3    first_name       VARCHAR2(20),
 4    last_name        VARCHAR2(20),
 5    major            VARCHAR2(30),
 6    current_credits  NUMBER(3)
 7    );

Table created. SQL> SQL> INSERT INTO lecturer (id, first_name, last_name, major,current_credits)

 2                VALUES (10001, "Scott", "Lawson","Computer Science", 11);

1 row created. SQL> SQL> INSERT INTO lecturer (id, first_name, last_name, major, current_credits)

 2                VALUES (10002, "Mar", "Wells","History", 4);

1 row created. SQL> SQL> INSERT INTO lecturer (id, first_name, last_name, major,current_credits)

 2                VALUES (10003, "Jone", "Bliss","Computer Science", 8);

1 row created. SQL> SQL> INSERT INTO lecturer (id, first_name, last_name, major,current_credits)

 2                VALUES (10004, "Man", "Kyte","Economics", 8);

1 row created. SQL> SQL> INSERT INTO lecturer (id, first_name, last_name, major,current_credits)

 2                VALUES (10005, "Pat", "Poll","History", 4);

1 row created. SQL> SQL> INSERT INTO lecturer (id, first_name, last_name, major,current_credits)

 2                VALUES (10006, "Tim", "Viper","History", 4);

1 row created. SQL> SQL> INSERT INTO lecturer (id, first_name, last_name, major,current_credits)

 2                VALUES (10007, "Barbara", "Blues","Economics", 7);

1 row created. SQL> SQL> INSERT INTO lecturer (id, first_name, last_name, major,current_credits)

 2                VALUES (10008, "David", "Large","Music", 4);

1 row created. SQL> SQL> INSERT INTO lecturer (id, first_name, last_name, major,current_credits)

 2                VALUES (10009, "Chris", "Elegant","Nutrition", 8);

1 row created. SQL> SQL> INSERT INTO lecturer (id, first_name, last_name, major,current_credits)

 2                VALUES (10010, "Rose", "Bond","Music", 7);

1 row created. SQL> SQL> INSERT INTO lecturer (id, first_name, last_name, major,current_credits)

 2                VALUES (10011, "Rita", "Johnson","Nutrition", 8);

1 row created. SQL> SQL> INSERT INTO lecturer (id, first_name, last_name, major,current_credits)

 2                VALUES (10012, "Sharon", "Clear","Computer Science", 3);

1 row created. SQL> SQL> select * from lecturer;

     ID FIRST_NAME           LAST_NAME            MAJOR                          CURRENT_CREDITS

-------------------- -------------------- ------------------------------ ---------------
                1. Scott Lawson Computer Science 11.00
                2. Mar Wells History 4.00
                3. Jone Bliss Computer Science 8.00
                4. Man Kyte Economics 8.00
                5. Pat Poll History 4.00
                6. Tim Viper History 4.00
                7. Barbara Blues Economics 7.00
                8. David Large Music 4.00
                9. Chris Elegant Nutrition 8.00
                10. Rose Bond Music 7.00
                11. Rita Johnson Nutrition 8.00
     ID FIRST_NAME           LAST_NAME            MAJOR                          CURRENT_CREDITS

-------------------- -------------------- ------------------------------ ---------------
                1. Sharon Clear Computer Science 3.00

12 rows selected. SQL> SQL> SQL> set serveroutput on SQL> SQL> DECLARE

 2    v_Major lecturer.major%TYPE;
 3    v_CourseName VARCHAR2(10);
 4  BEGIN
 5    SELECT major
 6      INTO v_Major
 7      FROM lecturer
 8      WHERE ID = 10011;
 9
10    IF v_Major = "Computer Science" THEN
11      v_CourseName := "CS  101";
12    ELSIF v_Major = "Economics" THEN
13      v_CourseName := "ECN 203";
14    ELSIF v_Major = "History" THEN
15      v_CourseName := "HIS 101";
16    ELSIF v_Major = "Music" THEN
17      v_CourseName := "MUS 100";
18    ELSIF v_Major = "Nutrition" THEN
19      v_CourseName := "NUT 307";
20    ELSE
21      v_CourseName := "Unknown";
22    END IF;
23    DBMS_OUTPUT.PUT_LINE(v_CourseName);
24  END;
25  /

NUT 307 PL/SQL procedure successfully completed. SQL> SQL> SQL> SQL> drop table lecturer; Table dropped. SQL>



 </source>
   
  


IF THEN and END IF

   <source lang="sql">
   

SQL> SQL> -- Calculating overtime hours with IF. SQL> set echo on SQL> DECLARE

 2    v_HoursWorked Number := 50 ;
 3    v_OverTime Number := 0 ;
 4  BEGIN
 5    IF v_HoursWorked > 40 THEN
 6      v_OverTime := v_HoursWorked - 40;
 7         DBMS_OUTPUT.PUT_LINE("Hours overtime worked = " || v_OverTime);
 8    END IF;
 9  END;
10  /

Hours overtime worked = 10 PL/SQL procedure successfully completed.



 </source>
   
  


If...then...Else

   <source lang="sql">
   

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","YYYYMM

DD"), 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","YYYYMM

DD"), to_date("19860221","YYYYMMDD"), 6661.78, "Vancouver","Tester")

 3  /

1 row created. SQL> SQL> SQL> declare

 2    dateValue date;
 3    nameValue VARCHAR2(10 BYTE);
 4    s Number(8,2);
 5    begin
 6         select start_date into dateValue from emp where rownum = 1;
 7         select salary into s from emp where rownum = 1;
 8         IF dateValue > "11-APR-63" then
 9              s :=  s * 1.15; -- Increase salary by 15%
10         ELSE
11              s := s * 1.05;  -- Increase salary by 5%
12         END IF;
13
14         dbms_output.put_line(s);
15  end;
16  /

1419.74 PL/SQL procedure successfully completed. SQL> SQL> drop table emp; Table dropped.



 </source>
   
  


Nested if statement

   <source lang="sql">
   

SQL> 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","YYYYMM

DD"), 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","YYYYMM

DD"), to_date("19860221","YYYYMMDD"), 6661.78, "Vancouver","Tester")

 3  /

1 row created. SQL> SQL> SQL> declare

 2    dateValue date;
 3    nameValue VARCHAR2(10 BYTE);
 4    s Number(8,2);
 5    begin
 6         select start_date into dateValue from emp where rownum = 1;
 7         select salary into s from emp where rownum = 1;
 8         select lname into nameValue from emp where rownum = 1;
 9          IF dateValue > "11-APR-63" then
10              If nameValue = "PAKMAN" then
11                   s :=  s * 1.15;
12              ELSE
13                   s :=  s * 1.10;
14              END IF;
15          ELSE
16              s := s * 1.05;
17          END IF;
18
19         dbms_output.put_line(s);
20  end;
21  /

1358.02 PL/SQL procedure successfully completed. SQL> SQL> drop table emp; Table dropped.



 </source>
   
  


The IF statement contains more than one statement per condition.

   <source lang="sql">
    

SQL> SQL> CREATE TABLE place (

 2    room_id          NUMBER(5) PRIMARY KEY,
 3    building         VARCHAR2(15),
 4    room_number      NUMBER(4),
 5    number_seats     NUMBER(4),
 6    description      VARCHAR2(50)
 7    );

Table created. SQL> SQL> INSERT INTO place (room_id, building, room_number, number_seats, description)

 2             VALUES (20001, "Building 7", 201, 1000, "Large Lecture Hall");

1 row created. SQL> SQL> INSERT INTO place (room_id, building, room_number, number_seats, description)

 2             VALUES (20002, "Building 6", 101, 500, "Small Lecture Hall");

1 row created. SQL> SQL> INSERT INTO place (room_id, building, room_number, number_seats, description)

 2             VALUES (20003, "Building 6", 150, 50, "Discussion Room A");

1 row created. SQL> SQL> INSERT INTO place (room_id, building, room_number, number_seats, description)

 2             VALUES (20004, "Building 6", 160, 50, "Discussion Room B");

1 row created. SQL> SQL> INSERT INTO place (room_id, building, room_number, number_seats,description)

 2             VALUES (20005, "Building 6", 170, 50, "Discussion Room C");

1 row created. SQL> SQL> INSERT INTO place (room_id, building, room_number, number_seats, description)

 2             VALUES (20006, "Music Building", 100, 10, "Music Practice Room");

1 row created. SQL> SQL> INSERT INTO place (room_id, building, room_number, number_seats, description)

 2             VALUES (20007, "Music Building", 200, 1000, "Concert Room");

1 row created. SQL> SQL> INSERT INTO place (room_id, building, room_number, number_seats, description)

 2             VALUES (20008, "Building 7", 300, 75, "Discussion Room D");

1 row created. SQL> SQL> INSERT INTO place (room_id, building, room_number, number_seats,description)

 2             VALUES (20009, "Building 7", 310, 50, "Discussion Room E");

1 row created. SQL> SQL> SQL> CREATE TABLE MyTable (

 2    num_col    NUMBER,
 3    char_col   VARCHAR2(60)
 4    );

Table created. SQL> SQL> SQL> select * from place;

ROOM_ID BUILDING        ROOM_NUMBER NUMBER_SEATS DESCRIPTION

--------------- ----------- ------------ --------------------------------------------------
                1. Building 7 201.00 1000.00 Large Lecture Hall
                2. Building 6 101.00 500.00 Small Lecture Hall
                3. Building 6 150.00 50.00 Discussion Room A
                4. Building 6 160.00 50.00 Discussion Room B
                5. Building 6 170.00 50.00 Discussion Room C
                6. Music Building 100.00 10.00 Music Practice Room
                7. Music Building 200.00 1000.00 Concert Room
                8. Building 7 300.00 75.00 Discussion Room D
                9. Building 7 310.00 50.00 Discussion Room E

9 rows selected. SQL> SQL> DECLARE

 2    v_NumberSeats place.number_seats%TYPE;
 3    v_Comment VARCHAR2(35);
 4  BEGIN
 5    SELECT number_seats
 6      INTO v_NumberSeats
 7      FROM place
 8      WHERE room_id = 20008;
 9    IF v_NumberSeats < 50 THEN
10      v_Comment := "Fairly small";
11      INSERT INTO MyTable (char_col) VALUES ("Nice and cozy");
12    ELSIF v_NumberSeats < 100 THEN
13      v_Comment := "A little bigger";
14      INSERT INTO MyTable (char_col) VALUES ("Some breathing room");
15    ELSE
16      v_Comment := "Lots of room";
17    END IF;
18  END;
19  /

PL/SQL procedure successfully completed. SQL> SQL> select * from mytable;

NUM_COL CHAR_COL

------------------------------------------------------------
        Some breathing room

SQL> SQL> select * from place;

ROOM_ID BUILDING        ROOM_NUMBER NUMBER_SEATS DESCRIPTION

--------------- ----------- ------------ --------------------------------------------------
                1. Building 7 201.00 1000.00 Large Lecture Hall
                2. Building 6 101.00 500.00 Small Lecture Hall
                3. Building 6 150.00 50.00 Discussion Room A
                4. Building 6 160.00 50.00 Discussion Room B
                5. Building 6 170.00 50.00 Discussion Room C
                6. Music Building 100.00 10.00 Music Practice Room
                7. Music Building 200.00 1000.00 Concert Room
                8. Building 7 300.00 75.00 Discussion Room D
                9. Building 7 310.00 50.00 Discussion Room E

9 rows selected. SQL> SQL> drop table mytable; Table dropped. SQL> SQL> drop table place; Table dropped. SQL>



 </source>
   
  


Using a Boolean variable instead of the comparison operation

   <source lang="sql">
   

SQL> SQL> DECLARE

 2    equal BOOLEAN NOT NULL := TRUE;
 3  BEGIN
 4    IF equal THEN
 5      dbms_output.put_line("Condition met!");
 6    END IF;
 7  END;
 8  /

Condition met! PL/SQL procedure successfully completed.



 </source>
   
  


Using IF...ELSIF to determine a grade

   <source lang="sql">
   

SQL> SQL> -- Using IF...ELSIF to determine a grade. SQL> DECLARE

 2     variable_Score Number := 85;
 3     variable_LetterGrade Char(1);
 4  BEGIN
 5     IF variable_Score >= 90 THEN
 6          variable_LetterGrade := "A";
 7     ELSIF variable_Score >= 80 THEN
 8          variable_LetterGrade := "B";
 9     ELSIF variable_Score >= 70 THEN
10          variable_LetterGrade := "C";
11     ELSIF variable_Score >= 60 THEN
12          variable_LetterGrade := "D";
13     ELSE
14          variable_LetterGrade := "E";
15     END IF;
16          DBMS_OUTPUT.PUT_LINE("Your Letter Grade is: " || variable_LetterGrade);
17  END;
18  /

Your Letter Grade is: B PL/SQL procedure successfully completed. SQL> SQL>



 </source>
   
  


Using nested IF statements

   <source lang="sql">
   

SQL> SQL> SQL> -- Using nested IF statements. SQL> DECLARE

 2    v_HoursWorked Number := 80 ;
 3    v_OverTime Number := 0 ;
 4    v_PayType char(1) := "E";
 5
 6  BEGIN
 7     IF v_HoursWorked > 40 THEN
 8       IF v_PayType = "H" THEN
 9         v_OverTime := v_HoursWorked - 40;
10         DBMS_OUTPUT.PUT_LINE("Hours overtime worked = " || v_OverTime);
11      ELSE
12         IF v_PayType = "S" THEN
13            DBMS_OUTPUT.PUT_LINE("Employee is Salaried");
14         ELSE
15            DBMS_OUTPUT.PUT_LINE("Employee is Executive Management");
16         END IF;
17      END IF;
18     END IF;
19  END;
20  /

Employee is Executive Management PL/SQL procedure successfully completed.



 </source>