The AND operator in PL/SQL is used to combine two or more conditions. It returns TRUE only when all the specified conditions are TRUE. If any condition is FALSE, the entire expression returns FALSE.
- Check multiple conditions together.
- All conditions must be TRUE for the result to be TRUE.
- Works with IF statements and SQL queries.
- Filters records based on multiple conditions.
Syntax:
BEGIN
IF condition_1 AND condition_2 THEN
statements;
END IF;
END;
/
Explanation:
- condition_1: Specifies the first condition.
- AND: Checks whether both conditions are TRUE.
- condition_2: Specifies the second condition.
- statements: Specifies the actions to execute when both conditions are TRUE.
Example: First, create an employees table with the following columns: emp_id, emp_name, salary, department and city.
Example 1: Check Multiple Conditions Using IF Statement
This example checks whether an employee's salary is greater than 50000 and their department is IT. The message is displayed only when both conditions are TRUE.
Query:
DECLARE
v_salary NUMBER := 60000;
v_department VARCHAR2(30) := 'IT';
BEGIN
IF v_salary > 50000 AND v_department = 'IT' THEN
DBMS_OUTPUT.PUT_LINE('Employee meets both conditions.');
ELSE
DBMS_OUTPUT.PUT_LINE('Employee does not meet both conditions.');
END IF;
END;
/
Output:
Employee meets both conditions.
Example 2: Retrieve Employees Based on Salary and Department
This example retrieves employees whose salary is greater than 50000 and whose department is IT. The AND operator ensures that only employees satisfying both conditions are returned.
Query:
DECLARE
v_cursor SYS_REFCURSOR;
BEGIN
OPEN v_cursor FOR
SELECT emp_id, emp_name, salary, department
FROM employees
WHERE salary > 50000 AND department = 'IT';
DBMS_SQL.RETURN_RESULT(v_cursor);
END;
/
Output:
