Quick Search:
CODE
Oracle PL/SQL Code Library
JOBS
Find Or Post Oracle Jobs
FORUM
Oracle Discussion & Chat
 The Oracle PL/SQL SET Keyword      [Return To Index] Jump to:  
  Looking for the original pages? (formerly called "Morgan's Library") You can find them here.

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      :      Code Library      :      Sponsors      :      Privacy      :      Terms of Use      :      Contact Us 72 users online    © 2009 psoug.org
PSOUG LOGIN
Username: 
Password: 
Forgot your password?