Subquery and NVL. NVL() for empty rows. Decode Function. Syntax The syntax for the Oracle/PLSQL DECODE function is: DECODE( expression , search , result [, search , result]... [, default] ) Parameters or Arguments expression is the value to compare. search is the value that is compared against expression. result is the value returned, if expression is equal to search. default is optional.
Frequently Asked Questions Question: One of our viewers wanted to know how to use the DECODE function to compare two dates (ie: date1 and date2), where if date1 > date2, the DECODE function should return date2. Answer: To accomplish this, use the DECODE function as follows: DECODE((date1 - date2) - ABS(date1 - date2), 0, date2, date1) The formula below would equal 0, if date1 is greater than date2: (date1 - date2) - ABS(date1 - date2) Helpful Tip #1: One of our viewers suggested combining the SIGN function with the DECODE function as follows: The date example above could be modified as follows: PLSQL: Substr Function. Separating Text. Oracle Tip: Create functions to join and split strings in SQL. Learn how to take a comma delimited list of values in a single string and use it as a table of values.
A common task when selecting data from a database is to take a set of values a query returns and format it as a comma delimited list. Another task that's almost as common is the need to do the reverse: Take a comma delimited list of values in a single string and use it as a table of values. Many scripting languages, such as Perl and Python, provide functions that do this with their own language-specific list of values; so it's surprising that, as of yet, this functionality isn't a standard part of SQL functions. I've seen some pretty ugly looking code that involved complex declarations with MAX and DECODE, but that solution usually only returns a limited set of values.
With some of the new Oracle9i and above features, it's possible to do this yourself. SQL> select join(cursor(select ename from emp)) from dual; The following code will perform this function: There's another extra benefit. Oracleinternals - Hidden features in Oracle. Oracle - Is there a function to split a string in plsql. PL/SQL Tutorial - PL/SQL variable declaration, constants. PL/SQL Placeholders Placeholders are temporary storage area.
Placeholders can be any of Variables, Constants and Records. Oracle defines placeholders to store data temporarily, which are used to manipulate data during the execution of a PL SQL block. Depending on the kind of data you want to store, you can define placeholders with a name and a datatype. Few of the datatypes used to define placeholders are as given below. PL/SQL Variables These are placeholders that store the values that can change through the PL/SQL Block. PL/SQL Collections and Records. Arrays must be (have consecutive subscripts).
So, you cannot delete individual elements from an array. Initially, nested tables are dense, but they can become (have nonconsecutive subscripts). So, you can delete elements from a nested table using the built-in procedure DELETE . That might leave gaps in the index, but the built-in function NEXT lets you iterate over any series of subscripts. Understanding Varrays Items of type VARRAY are called . Text description of the illustration pls81017_varray_of_size_10.gif A varray has a maximum size, which you must specify in its type definition. Understanding Associative Arrays (Index-By Tables) Associative arrays are sets of key-value pairs, where each key is unique and is used to locate a corresponding value in the array.
Assigning a value using a key for the first time adds that key to the associative array. For example, here is the declaration of an associative array type, and two arrays of that type, using keys that are strings: Nested Tables. PLSQL: Sequences (autonumber) Learn how to create and drop sequences in Oracle with syntax and examples.
Description In Oracle, you can create an autonumber field by using sequences. PLSQL: Declare a Cursor. Learn how to declare a cursor in Oracle/PLSQL with syntax and examples.
Description A cursor is a SELECT statement that is defined within the declaration section of your PLSQL code. We'll take a look at three different syntaxes to declare a cursor. Cursor without parameters (simplest) Declaring a cursor without any parameters is the simplest cursor. Syntax. CURSORS. PL/SQL BLOCKS.