Quick Search:
 
 The Oracle PL/SQL SET Keyword      [Return To Index] Jump to:  
FULL A - Z Oracle Function Library (formerly named "Morgan's Library")

Term: SET

Definition:
In Oracle PL/SQL, the SET keyword appears as part of the UPDATE statement, as an operator, as a built in function and also as a Collection comparison conditional operator.

1. As part of the UPDATE statement:

The SET statement assigns a value to a column. The value may be a sting, date, number, or an evaluated variable or construct. If the value is a string, DATE, CLOB or other non-numeric data then the value must be enclosed in quotes.

Example Syntax:

UPDATE <table_name>

SET <column_name> = <value>


Example Usage:

UPDATE user_list

SET start_date = '2011-10-08'

WHERE last_name = 'Johnson'


If the value is purely numeric then it does not need to be enclosed in quotes:

UPDATE user_list

SET visits = 150

WHERE last_name = 'Johnson'


In this example the value is an evaluated variable, where the 'visits' value is incremented by 1:

UPDATE user_list

SET visits = (visits + 1)

WHERE last_name = 'Johnson'



2. As an Operator:

In Oracle PL/SQL, SET operators are used to combine result set of more than one query into one result. The SQL queries using the SET operators are known as Compound queries. Set operators can be UNION, UNION ALL, INTERSECT, and MINUS. All involved SELECTs must have same number of selected columns and also be of the same data type.

Example Syntax:

Query 1

[UNION | UNION ALL | MINUS | INTERSECT]

Query 2


Example Usage:

The SQL query below combines the result set of two queries using UNION set operator.

SELECT EMPLOYEE_ID, JOB_ID, SALARY

FROM EMP

UNION

SELECT EMPLOYEE_ID, JOB_ID, SALARY

FROM JOB_HISTORY



3. As a Built In Function:

As a function, SET is used to remove duplicate elements from a nested table. It takes a Nested Table input and returns a nested table type with distinct element values.

Example Syntax:

SET(nested table)


Example Usage:

The SQL query below removes duplicate elements from SET_ORDERS nested table column of ORDERS_ARCHIVE table.

SELECT SET(SET_ORDERS)

FROM  ORDERS_ARCHIVE



4. As a Collection Comparison Conditional Operator:

SET can also be used to compare Nested Table data for a unique elements check. It returns BOOLEAN output TRUE, if the Nested Table column contains a unique set of elements, otherwise it returns FALSE.

Example Syntax:

IS [NOT] A SET


Example Usage:

The SQL query below check whether SET_ORDERS column contains a unique set of elements.

SELECT ORD_ID,ORD_DATE, ORD_NAME, LOCATION, 

FROM ORDER_ARCHIVE

WHERE SET_ORDERS IS A SET

Related Code Snippets:
 
   Home |    Search |    Code Library |    Sponsors |    Privacy |    Terms of Use |    Contact Us © 2003 - 2024 psoug.org