A conditional statement

     
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>

Related examples in the same category

1.	IF...ELSIF...ELSE... END IF
2.	The IF statement contains more than one statement per condition.
3.	IF THEN and END IF
4.	Adding ELSE to the IF block
5.	IF, ELSIF ELSE and END IF
6.	Using nested IF statements
7.	Using IF...ELSIF to determine a grade
8.	if then else
9.	Check number value in if statement
10.	Exit a loop with condition
11.	If ladder
12.	Compare three variables with if statement
13.	If count() is 0, insert data
14.	If condition meets, throw exception
15.	Nested if statement
16.	If...then...Else
17.	Using a Boolean variable instead of the comparison operation
18.	An if-then-elsif-then-else statement where the first two comparisons are true and the third false
19.	Create a function and call it in an if statement

A conditional statement : IF « PL SQL « Oracle PL / SQL

Related examples in the same category