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; /