The Geek Diary

Operators in PL/SQL

by

The operations in an expression are performed in a particular order depending on their precedence (priority). The following table shows the default order of operations from high priority to low priority:

Operator Operation
** Exponentiation
+, – Identity, negation
*, / Multiplication, division
+, -, || Addition, subtraction, concatenation
=,<, >, <=, >=, <>, !=, ~=, ^=, IS NULL, LIKE, BETWEEN, IN Comparison
NOT Logical negation
AND Conjunction
OR Inclusion

Operators in PL/SQL: Examples

1. Increment the counter for a loop:

loop_count := loop_count + 1;

2. Set the value of a Boolean flag:

good_sal := sal BETWEEN 50000 AND 150000;

3. Validate whether an employee number contains a value:

valid := (empno IS NOT NULL);

When you are working with nulls, you can avoid some common mistakes by keeping in mind the following rules:

  • Comparisons involving nulls always yield NULL.
  • Applying the logical operator NOT to a null yields NULL.
  • In conditional control statements, if the condition yields NULL, its associated sequence of statements is not executed.

Programming Guidelines

Make code maintenance easier by:

  • Documenting code with comments
  • Developing a case convention for the code
  • Developing naming conventions for identifiers and other objects
  • Enhancing readability by indenting

Follow programming guidelines shown above to produce clear code and reduce maintenance when developing a PL/SQL block.

Code Conventions

The following table provides guidelines for writing code in uppercase or lowercase characters to help distinguish keywords from named objects.

Category Case Convention Examples
SQL statements Uppercase SELECT, INSERT
PL/SQL keywords Uppercase DECLARE, BEGIN, IF
Data types Uppercase VARCHAR2, BOOLEAN
Identifiers and parameters Lowercase v_sal, emp_cursor, g_sal, p_empno
Database tables Lowercase, plural employees, departments
Database columns Lowercase, singular employee_id, department_id

Indenting Code

For clarity and enhanced readability, indent each level of code. To show structure, you can divide lines by using carriage returns and you can indent lines by using spaces and tabs. Compare the following IF statements for readability:

IF x>y THEN max:=x;ELSE max:=y;END IF;
IF x > y THEN
max := x;
ELSE
max := y;
END IF;

Some more examples for indenting the code:

BEGIN
    IF x=0 THEN
        y:=1;
    END IF;
END;
/
DECLARE
    v_deptno NUMBER(4);
    v_location_id NUMBER(4);
BEGIN
    SELECT  department_id,
            location_id
    INTO    deptno,
            v_location_id
    FROM    departments
    WHERE   department_name
            = 'Sales';
...
END;
/

You May Also Like