• Skip to primary navigation
  • Skip to main content
  • Skip to primary sidebar
  • Skip to footer navigation

The Geek Diary

  • OS
    • Linux
    • CentOS/RHEL
    • Solaris
    • Oracle Linux
    • VCS
  • Interview Questions
  • Database
    • oracle
    • oracle 12c
    • ASM
    • mysql
    • MariaDB
  • DevOps
    • Docker
    • Shell Scripting
  • Big Data
    • Hadoop
    • Cloudera
    • Hortonworks HDP

Operators in PL/SQL

by admin

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

Filed Under: oracle, PL/SQL

Some more articles you might also be interested in …

  1. How to Modify spfile in Oracle Database
  2. Oracle Database 12c : Creating a Scheduler Job in a Multitenant Database
  3. How to determine the required archivelog files needed for a guaranteed restore point before running flashback database
  4. Access Control List(ACL) in Oracle 11G
  5. Using CASE statements and CASE expressions in PL/SQL
  6. Types of Workload Distribution (Load Balancing) in Oracle RAC
  7. How to add and drop online redo log members and groups in Oracle
  8. Oracle GoldenGate: Replicat Sample Parameter File
  9. Oracle Database 19c: RMAN-06012: channel: d1 not allocated
  10. WebLogic Server Domain: How To Disable the HTTP methods other than GET and POST (such as PUT, DELETE, etc.)

You May Also Like

Primary Sidebar

Recent Posts

  • vgextend Command Examples in Linux
  • setpci command – configure PCI device
  • db_load command – generate db database
  • bsdtar command – Read and write tape archive files

© 2022 · The Geek Diary

  • Archives
  • Contact Us
  • Copyright