Pseudocolumns in Oracle are special columns that behave like regular table columns but are not stored as part of the table. They are used to retrieve specific data or to perform certain operations. Here are some common uses of pseudocolumns in Oracle:
- ROWNUM: ROWNUM is a pseudocolumn that assigns a unique number to each row in the result set of a query. It is often used for limiting the number of rows returned or for pagination.
Example:
SELECT * FROM employees WHERE ROWNUM <= 10;
This query returns the first 10 rows from the employees table. - ROWID: ROWID is a pseudocolumn that represents the unique address of a row in the database. It can be used to quickly access a specific row or to identify and remove duplicate rows.
Example:
DELETE FROM employees WHERE ROWID IN ( SELECT ROWID FROM ( SELECT ROWID, ROW_NUMBER() OVER (PARTITION BY first_name, last_name ORDER BY ROWID) AS row_num FROM employees ) WHERE row_num > 1 );
This query deletes duplicate rows from the employees table based on first_name and last_name columns. - LEVEL: LEVEL is a pseudocolumn used with hierarchical queries to indicate the level of a row in a hierarchical tree structure. It is used in conjunction with the CONNECT BY clause to retrieve hierarchical data.
Example:
SELECT LEVEL, employee_id, manager_id FROM employees CONNECT BY PRIOR employee_id = manager_id START WITH manager_id IS NULL;
This query retrieves the hierarchical structure of employees and their managers. - NEXTVAL and CURRVAL: NEXTVAL and CURRVAL are pseudocolumns associated with sequences in Oracle. NEXTVAL is used to generate the next value in a sequence, while CURRVAL retrieves the current value of a sequence.
Example:
INSERT INTO employees (employee_id, first_name, last_name) VALUES (employee_seq.NEXTVAL, 'Glenn', 'Gould'); SELECT employee_seq.CURRVAL FROM DUAL;
The first query inserts a new employee with the next value from the employee_seq sequence, and the second query retrieves the current value of the sequence. - SYS_GUID(): SYS_GUID() is a pseudocolumn that generates a unique identifier (GUID) value. It is useful when you need a unique value across tables or databases.
Example:
INSERT INTO employees (employee_id, first_name, last_name) VALUES (SYS_GUID(), 'John', 'Doe');
This query inserts a new employee with a unique identifier generated by SYS_GUID().