• 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. Oracle ASM – How ASM Disk Resync Works
  2. How to recreate Control file in Oracle Database
  3. How an SQL query is executed in Oracle Database
  4. Beginners Guide to Oracle Database Vault
  5. CRSCTL Command Examples in ASM Standalone Configurations
  6. Starting, Stopping, and Checking the Status of the EM Cloud Control OMS
  7. ORA-354 ORA-353 and ORA-312: Possible corruption in Online Redo Log File Members in a Redo Log Group
  8. How to relocate the redo log files to a different location on disk
  9. Out-of-Place Refresh Option: Oracle 12c New Feature
  10. Stored Procedures and Functions in PL/SQL

You May Also Like

Primary Sidebar

Recent Posts

  • qm Command Examples in Linux
  • qm wait Command Examples in Linux
  • qm start Command Examples in Linux
  • qm snapshot Command Examples in Linux

© 2023 · The Geek Diary

  • Archives
  • Contact Us
  • Copyright