Operators, Condition statements, CASE and looping structures

Operators:

Operator Type	Operator	Description	Example
Arithmetic	+	Addition	result := 10 + 5;
	-	Subtraction	result := 10 - 5;
	*	Multiplication	result := 10 * 5;
	/	Division	result := 10 / 5;
	MOD	Modulus (remainder)	result := 10 MOD 3;
Relational	=	Equal to	result := (a = b);
	!=/<>	Not equal to	result := (a != b);
	>	Greater than	result := (a > b);
	<	Less than	result := (a < b);
	>=	Greater than or equal to	result := (a >= b);
	<=	Less than or equal to	result := (a <= b);
Logical	AND	Logical AND	result := (a > b) AND (c < a);
	OR	Logical OR	result := (a > b) OR (b > 5);
	NOT	Logical NOT	result := NOT (a > b);
Concatenation	`		`
Assignment	:=	Assignment	a := 10;

 Example 1:

DECLARE

a NUMBER := 10;

b NUMBER := 5;

c NUMBER;

d NUMBER;

e NUMBER;

f NUMBER;

BEGIN

c := a + b;

d := a - b;

e := a * b;

f := a / b;

DBMS_OUTPUT.PUT_LINE('Addition: ' || c);

DBMS_OUTPUT.PUT_LINE('Subtraction: ' || d);

DBMS_OUTPUT.PUT_LINE('Multiplication: ' || e);

DBMS_OUTPUT.PUT_LINE('Division: ' || f);

END;

Example 2:

DECLARE

-- Constants and variables

pi CONSTANT NUMBER := 3.14159;

radius NUMBER := 7;

height NUMBER := 10;

area NUMBER;

volume NUMBER;

result BOOLEAN;

first_name VARCHAR2(50) := 'John';

last_name VARCHAR2(50) := 'Doe';

full_name VARCHAR2(100);

BEGIN

-- Arithmetic operations

area := pi * radius * radius;  -- Area of a circle

volume := pi * radius * radius * height;  -- Volume of a cylinder

-- Relational operations

result := (area > 100);  -- Check if the area is greater than 100

-- Logical operations

result := (area > 100) AND (volume < 1000);  -- Check if area > 100 and volume < 1000

-- Concatenation

full_name := first_name || ' ' || last_name;  -- Combine first and last names

-- Output results

DBMS_OUTPUT.PUT_LINE('Pi: ' || pi);

DBMS_OUTPUT.PUT_LINE('Radius: ' || radius);

DBMS_OUTPUT.PUT_LINE('Height: ' || height);

DBMS_OUTPUT.PUT_LINE('Area of Circle: ' || area);

DBMS_OUTPUT.PUT_LINE('Volume of Cylinder: ' || volume);

DBMS_OUTPUT.PUT_LINE('Area > 100: ' || result);

DBMS_OUTPUT.PUT_LINE('Full Name: ' || full_name);

END;

Conditional Statements:

·        IF-THEN

·        IF-THEN-ELSE

·        IF-THEN-ELSIF-ELSE

·        CASE

IF-THEN Example

DECLARE

salary NUMBER := 4000;

BEGIN

IF salary > 3000 THEN

DBMS_OUTPUT.PUT_LINE('Salary is greater than 3000.');

END IF;

END;

IF-THEN-ELSE Example

DECLARE

salary NUMBER := 2500;

BEGIN

IF salary > 3000 THEN

DBMS_OUTPUT.PUT_LINE('Salary is greater than 3000.');

ELSE

DBMS_OUTPUT.PUT_LINE('Salary is 3000 or less.');

END IF;

END;

IF-THEN-ELSIF-ELSE Example

DECLARE

salary NUMBER := 2500;

BEGIN

IF salary > 5000 THEN

DBMS_OUTPUT.PUT_LINE('Salary is greater than 5000.');

ELSIF salary > 3000 THEN

DBMS_OUTPUT.PUT_LINE('Salary is between 3001 and 5000.');

ELSE

DBMS_OUTPUT.PUT_LINE('Salary is 3000 or less.');

END IF;

END;

CASE Statement Example

The CASE statement works like a switch-case structure found in other languages

DECLARE

grade CHAR(1) := 'B';

BEGIN

CASE grade

WHEN 'A' THEN

DBMS_OUTPUT.PUT_LINE('Excellent');

WHEN 'B' THEN

DBMS_OUTPUT.PUT_LINE('Good');

WHEN 'C' THEN

DBMS_OUTPUT.PUT_LINE('Average');

ELSE

DBMS_OUTPUT.PUT_LINE('Invalid grade');

END CASE;

END;

Loop Structures:

loops are used to execute a block of statements repeatedly. 

• BASIC Loop
• WHILE Loop
• FOR Loop
  

Basic Loop Example

DECLARE

counter NUMBER := 1;

BEGIN

LOOP

DBMS_OUTPUT.PUT_LINE('Counter: ' || counter);

counter := counter + 1;

 IF counter > 5 THEN

EXIT;  -- Exit the loop when counter is greater than 5

END IF;

END LOOP;

END;

While Loop Example:

DECLARE

counter NUMBER := 1;

BEGIN

WHILE counter <= 5 LOOP

DBMS_OUTPUT.PUT_LINE('Counter: ' || counter);

counter := counter + 1;

END LOOP;

END;

For Loop Example:

BEGIN

FOR i IN 1..5 LOOP

DBMS_OUTPUT.PUT_LINE('Value of i: ' || i);

END LOOP;

END;

DML in PLSQL Example

BEGIN

-- Insert a new employee record

INSERT INTO employees (employee_id, first_name, last_name, salary) VALUES (1003, 'Alice', 'Johnson', 5500);

-- Update the salary of the inserted employee

UPDATE employees SET salary = salary + 500 WHERE employee_id = 1003;

-- Delete the employee record

DELETE FROM employees WHERE employee_id = 1003;

-- Commit the transaction to make changes permanent

COMMIT;

DBMS_OUTPUT.PUT_LINE('DML operations completed successfully.');

END;

DRL in PLSQL Example:

DECLARE

-- Variables for direct assignment and column datatype

v_employee_salary employees.salary%TYPE;

v_employee_name employees.first_name%TYPE;

-- Variable for row datatype

v_employee_record employees%ROWTYPE;

BEGIN

-- Direct Assignment: Retrieve a single column value

SELECT salary   INTO v_employee_salary  FROM employees   WHERE employee_id = 1001;

-- Output the retrieved salary

DBMS_OUTPUT.PUT_LINE('Employee Salary: ' || v_employee_salary);

-- Retrieve another column value using direct assignment

SELECT first_name INTO v_employee_name FROM employees  WHERE employee_id = 1001;

-- Output the retrieved first name

DBMS_OUTPUT.PUT_LINE('Employee First Name: ' || v_employee_name);

-- Row Datatype: Retrieve an entire row

SELECT *  INTO v_employee_record FROM employees  WHERE employee_id = 1001;

-- Output the retrieved row data

DBMS_OUTPUT.PUT_LINE('Employee ID: ' || v_employee_record.employee_id);

DBMS_OUTPUT.PUT_LINE('Employee Name: ' || v_employee_record.first_name || ' ' || v_employee_record.last_name);

DBMS_OUTPUT.PUT_LINE('Employee Salary: ' || v_employee_record.salary);

END;