PL/SQL

Simple CASE vs. Searched CASE

The CASE construct in Oracle has two variants – the simple CASE and the searched CASE. We saw examples of both kinds in the topic The Difference Between DECODE and CASE.

Let’s have a closer look to compare simple CASE and searched CASE in structure and functionality.

[click to continue…]

{ 0 comments }

The Difference Between DECODE and CASE

DECODE and CASE statements in Oracle both provide a conditional construct, of this form:
if A = n1 then A1
else if A = n2 then A2
else X

Databases before Oracle 8.1.6 had only the DECODE function. CASE was introduced in Oracle 8.1.6 as a standard, more meaningful and more powerful function.

Everything DECODE can do, CASE can. There is a lot else CASE can do though, which DECODE cannot. We’ll go through detailed examples in this article.

[click to continue…]

{ 71 comments }

WHEN OTHERS THEN NULL

Look at this small piece of code which inserts a row into the EMP table.
[click to continue…]

{ 6 comments }

6 Reasons Why You Should Use PL/SQL Packages

A package is a database object that groups together logically related procedures/functions, and other constructs such as variables, constants, cursors, PL/SQL types and exceptions.

One may well ask: when it’s possible to write standalone procedures/functions and define related variables within them, why have packages at all?

[click to continue…]

{ 0 comments }