Tech Honey

The #1 Website for Oracle PL/SQL, OA Framework and HTML

FOR Loop in Oracle PLSQL

by

A FOR Loop in Oracle PLSQL is used to execute a portion of code i.e. the body of loop, a fixed number of times.

The Syntax for the FOR LOOP in Oracle PLSQL is:
FOR loop_counter IN [REVERSE] low_number .. high_number
LOOP
{
Statements to be executed;
}
END LOOP;

Example of a FOR Loop in Oracle PLSQL is:

FOR cnt IN 1..35
LOOP
{
  sum := sum + 1;
}
END LOOP;

The above loop will execute the “sum := sum + 1; statement 35 times. The loop will start counting from 1 and ends at 35.

Example of a FOR Loop IN REVERSE in Oracle PLSQL is:

FOR cnt IN REVERSE 1..30
LOOP
{
  sum := sum + 1;
}
END LOOP;

The above loop will execute the “sum := sum+1; statement 30 times. The loop will start counting from 30 and ends at 1 looping backwards.

GOTO Statement in Oracle PLSQL

by

A GOTO Statement in Oracle PLSQL is used to redirect the code execution to a “Label” as specified in the GOTO statement.

Syntax of the GOTO Statement in Oracle PLSQL is:

GOTO label_name;

Somewhere further in the code, we need to place the label “label_name” and provide the code to be executed.

Example of GOTO in Oracle PLSQL is:

GOTO techhoney;
techhoney:
{
  statements;
}

CASE Statement in Oracle SQL PLSQL

by

A CASE Statement in Oracle SQL / PLSQL is having the functionality of IF-THEN-ELSE Statement.

Syntax of the CASE Statement in Oracle SQL / PLSQL is:
CASE [expression]
WHEN condition_1 THEN result_1
WHEN consition_2 THEN result_2
WHEN condition_3 THEN result_3
.
.
WHEN condition_N THEN result_N
ELSE default_result
END;

  • expression is an optional value, if provided; it is used to compare with various conditions (e.g. condition_1, condition_2, . . condition_N)
  • condtion_1 to condition_N must have the same data type, also the conditions are evaluated in the order in which they are listed, hence once a condition evaluates to true the CASE statement returns the result and does not evaluate further conditions.
  • result_1 to result_N must also have the same data type, these are the values that will be returned once the condition evaluates to true.
  • If no condition evaluates to true then the ‘default_result’ from the ELSE clause will be returned by CASE statement
  • If the ELSE clause is omitted and none of the condition evaluates to true then NULL will be returned by CASE Statement.

Let’s see an example to understand how to use CASE in Oracle SQL / PLSQL SELECT Statement:

Suppose we have a table named ‘employee’ as shown below:

Employee_ID Employee_Name Salary Department Commission
101 Emp A 10000 Sales
102 Emp B 20000 IT 20
103 Emp C 28000 IT 20
104 Emp D 30000 Support 5
105 Emp E 32000 Sales 10
106 Emp F 20000 Sales 5
107 Emp G 12000 Sales
108 Emp H 12000 Support

Now, if we write our query using CASE Statement in Oracle SQL / PLSQL as:

SELECT employee_id
       ,employee_name
       ,salary
       ,CASE
         WHEN salary = 30000 THEN 'Salary Between 20000 and 30000'
         ELSE 'Salary More Than 30000'
         END SALARY_STATUS
FROM employee;

We will get the following result:

EMPLOYEE_ID EMPLOYEE_NAME SALARY SALARY_STATUS
101 Emp A 10000 Salary Less than 20000
102 Emp B 20000 Salary Less than 20000
103 Emp C 28000 Salary More Than 30000
104 Emp D 30000 Salary Between 20000 and 30000
105 Emp E 32000 Salary Between 20000 and 30000
106 Emp F 20000 Salary Less than 20000
107 Emp G 12000 Salary Less than 20000
108 Emp H 12000 Salary Less than 20000

Here we can observe, that the CASE statement returns the text literals ‘Salary less than 20000’, ‘Salary between 20000 and 30000’ and ‘Salary More Than 30000’ for each record based on the salary amount as specified in the CASE Statement.

IF THEN ELSE Statement in Oracle PLSQL

by

An IF-THEN-ELSE Statement in Oracle PLSQL is basically a conditional statement that evaluates an expression, if the expression evaluates to true, then the ‘THEN’ portion of the code is executed, if the expression evaluates to false, then ‘ELSE’ part of the code gets executed.

There are 3 syntaxes of IF-THEN-ELSE Statement in Oracle PLSQL:

Syntax 1: IF-THEN Statement in Oracle PLSQL
IF condition THEN
Business_Logic
END IF;

Example: IF-THEN Statement in Oracle PLSQL

IF department = 'IT' THEN
  commission := 10;
END IF;

Syntax 2: IF-THEN-ELSE Statement in Oracle PLSQL
IF condition THEN
Business_Logic_1
ELSE
Business_Logic_2
END IF;

Example: IF-THEN-ELSE Statement in Oracle PLSQL

IF department = 'IT' THEN
  commission := 10;
ELSE
  commission := 20;
END IF;

Syntax 3: IF-THEN-ELSEIF Statement in Oracle PLSQL
IF condition_1 THEN
Business_Logic_1
ELSEIF condition_2 THEN
Business_Logic_2
ELSEIF condition_3 THEN
Business_Logic_3
.
.
ELSEIF condition_N THEN
Business_Logic_N
ELSE
Default_Business_Logic
END IF;

Example: IF-THEN-ELSEIF Statement in Oracle PLSQL

IF department = 'IT' THEN
  commission :=10;
ELSEIF department = 'Sales' THEN
  commission := 20;
ELSEIF department = 'Support' THEN
  commission := 30;
ELSE
  commission := 5;
END IF;

Let’s see an example to understand how to use IF-THEN-ELSE Statement:

CREATE OR REPLACE FUNCTION SalaryLevel
   ( emp_id_in IN NUMBER)
   RETURN VARCHAR2
IS
   salary_value NUMBER(6);
   SalLevel VARCHAR2(20);
   CURSOR cur_sal IS
     SELECT salary
     FROM employee
     WHERE employee_id = emp_id_in;
BEGIN
   OPEN cur_sal;
   FETCH cur_sal INTO salary_value;
   CLOSE cur_sal;

   IF salary_value <= 15000 THEN
      SalLevel := 'Low Income';
   ELSIF salary_value > 15000 AND salary_value <= 30000 THEN
      SalLevel := 'Average Income';
   ELSIF salary_value > 30000 AND salary_value <= 45000 THEN
      SalLevel := 'Moderate Income';
   ELSE
      SalLevel := 'High Income';
   END IF;

   RETURN SalLevel;
END;

IS NOT NULL in Oracle SQL PLSQL

by

In Oracle SQL / PLSQL IS NOT NULL is used to check whether the value of a literal IS NOT NULL or not.

Example 1:
Syntax to use IS NOT NULL in IF statement is:

IF literal_name IS NOT NULL THEN
<business_logic>
END IF;

Here if the literal_name does not have NULL value then the IF condition will evaluate to TRUE, then the “THEN” section of the code will get executed.

Example 2:

Using IS NOT NULL is Oracle SQL / PLSQL SELECT statement:

Suppose we have a table named ‘employee’ as shown below:

Employee_ID Employee_Name Salary Department Commission
101 Emp A 10000 Sales
102 Emp B 20000 IT 20
103 Emp C 28000 IT 20
104 Emp D 30000 Support 5
105 Emp E 32000 Sales 10
106 Emp F 20000 Sales 5
107 Emp G 12000 Sales
108 Emp H 12000 Support

Suppose we want to see the records having commission as NOT NULL, then we can achieve the same as:

SELECT *
FROM employee
WHERE commission IS NOT NULL;

We will get the following results:

Employee_ID Employee_Name Salary Department Commission
102 Emp B IT 20000 20
103 Emp C IT 28000 20
104 Emp D Support 30000 5
105 Emp E Sales 32000 10
106 Emp F Sales 20000 5

Here we can see that we have successfully retrieved records having NOT NULL commission using IS NOT NULL in Oracle SQL PLSQL SELECT statement.