Function Pipelined | Table

In Oracle, a table is a basic object that holds data in rows and columns. It is the primary way to store and manage data in a relational database. A table can have one or more columns, each of which has a specific data type and a name. Tables can also have constraints, indexes, and other attributes to define the data's properties and relationships.

A pipeline function, on the other hand, is a special type of function in Oracle that can be used to transform data and return it as a collection. Unlike regular functions, which return a single value, pipeline functions return a set of rows that can be queried and processed like a table.

A pipeline function can take one or more input parameters, and it produces a result set that can be used in SQL queries or other PL/SQL code. Pipeline functions can be used to perform complex transformations on data and return the results in a structured format that can be easily consumed by other code.

Here is an example of a pipeline function:

CREATE OR REPLACE FUNCTION get_employees_by_dept(dept_id IN NUMBER)

RETURN sys.odcivarchar2list PIPELINED

IS

  l_employee_name VARCHAR2(100);

BEGIN

  FOR r IN (SELECT first_name || ' ' || last_name AS full_name

            FROM employees

            WHERE department_id = dept_id)

  LOOP

    PIPE ROW(r.full_name);

  END LOOP;

  RETURN;

END;

In this example, we are creating a pipeline function called get_employees_by_dept that takes a department ID as input and returns a list of employee names in that department. The sys.odcivarchar2list PIPELINED specifies that the function returns a collection of strings.

The function queries the employees table to retrieve the employee names in the specified department, and then iterates through the results using a FOR loop. For each row, it calls the PIPE ROW function to add the row to the result set. Finally, it returns the result set using the RETURN statement.

Once the function is created, it can be used in SQL queries like a regular table:

SELECT * FROM TABLE(get_employees_by_dept(10));

This query will return a list of employee names in the department with ID 10.

ROWID Usages

 In Oracle, ROWID is a pseudo column that is used to uniquely identify a specific row in a table. It represents the physical address of a row and consists of the data file number, the data block number, and the location of the row within the block.

The ROWID column can be useful in several ways in DQL (Data Query Language), DML (Data Manipulation Language), and programming of PL/SQL. Here are some examples:

Retrieving specific rows:

Suppose you have a table with thousands of rows, and you need to retrieve a specific row or set of rows based on a condition. You can use the ROWID column to retrieve the rows efficiently. For example:

SELECT * FROM mytable WHERE ROWID = 'AAABBBCCC';

Updating specific rows:

If you want to update specific rows, you can also use the ROWID column in the UPDATE statement. For example:

UPDATE mytable SET column1 = 'value' WHERE ROWID = 'AAABBBCCC';

Deleting specific rows:

Similarly, you can use the ROWID column to delete specific rows. For example:

DELETE FROM mytable WHERE ROWID = 'AAABBBCCC';

PL/SQL programming:

In PL/SQL, you can use the ROWID column to loop through a table and perform operations on each row. For example:

DECLARE

  CURSOR c1 IS SELECT ROWID, column1 FROM mytable;

BEGIN

  FOR r1 IN c1 LOOP

    IF r1.column1 = 'value' THEN

      -- do something with the row

      UPDATE mytable SET column2 = 'newvalue' WHERE ROWID = r1.ROWID;

    END IF;

  END LOOP;

END;

In this example, we are using a cursor to loop through the table and retrieve each row's ROWID and column1 value. We then use the ROWID to update the row's column2 value if the column1 value is 'value'.

Overall, the ROWID column is a powerful tool in Oracle that can be used to efficiently retrieve, update, and delete specific rows, as well as perform operations on each row in a table using PL/SQL.

Analytical Functions

Oracle provides several analytical functions that enable users to perform complex calculations on sets of rows returned by a query. These functions allow users to compute cumulative, aggregate, and ranking values without the need for self-joins or subqueries.

Here are the commonly used Oracle analytical functions with examples:

RANK(): The RANK() function returns the rank of each row within a result set, with ties receiving the same rank and the next rank being skipped.

Example:

select salary, rank() over(order by salary desc) as r from employees;

DENSE_RANK(): The DENSE_RANK() function returns the rank of each row within a result set, with ties receiving the same rank and no rank being skipped.

Example:

select salary,dense_rank() over(order by salary desc) as dr from employees;

ROW_NUMBER(): The ROW_NUMBER() function returns a unique number for each row within a result set.

Example:

select employee_id,last_name, salary, ROW_NUMBER() over (order by salary desc) row_num from employees

LAG(): The LAG() function returns the value of a column from a previous row in the result set.

Example:

select employee_id,last_name,job_id,salary, lag(salary, 1, 0) over(order by salary) as sal_prev from employees;

LEAD(): The LEAD() function returns the value of a column from a following row in the result set.

Example:

select employee_id,last_name,job_id,salary, lead(salary, 1, 0) over (order by salary) as sal_prev from   employees;

FIRST_VALUE(): The FIRST_VALUE() function returns the value of the first row within a partition of the result set.

Example:

select employee_id,department_id,salary, first_value(salary ignore nulls) over (partition by department_id order by salary asc nulls last) value_result from employees;

LAST_VALUE(): The LAST_VALUE() function returns the value of the last row within a partition of the result set.

Example:

select employee_id,  department_id,  salary,   last_value(salary ignore nulls) over(partition by department_id order by salary asc nulls last) value_result  from employees;

SUM(): The SUM() function returns the sum of a column within a result set.

Example:

select employee_id,sum(salary) over(order by employee_id) as sum_sal from employees;

AVG(): The AVG() function returns the average of a column within a result set.

Example:

select employee_id,department_id,salary,avg(salary) over(partition by department_id order by department_id)as avg_dept_wise from employees;

MAX(): The MAX() function returns the maximum value of a column within a result set.

Example:

select employee_id,department_id,salary,max(salary) over(partition by department_id order by department_id) max_dept_wise from employees;

MIN(): The MIN() function returns the minimum value of a column within a result set.

Example:

select employee_id,department_id,salary,min(salary) over(partition by department_id order by department_id) min_dept_wise from employees;

These are some of the commonly used Oracle analytical functions with examples.

Views - Simple | Complex | Materialized

 In Oracle, there are several types of views. A view is a virtual table that does not store data but retrieves data from one or more tables. Views provide a way to simplify complex queries by presenting data in a more manageable format. The following are the most common types of views in Oracle:

Simple View:

A simple view is a view that is based on a single table. A simple view can be created using the following 

Syntax:

CREATE VIEW view_name AS

SELECT column1, column2, column3, ...

FROM table_name

WHERE condition;

Example:

if we want to create a view that displays the names and salaries of employees whose salaries are greater than 5000 from the employees table, we can use the following syntax:

CREATE VIEW high_salary_employees AS

SELECT first_name, last_name, salary

FROM employees

WHERE salary > 5000;

Complex View:

A complex view is a view that is based on multiple tables or subqueries. A complex view can be created using the following 

Syntax:

CREATE VIEW view_name AS

SELECT column1, column2, column3, ...

FROM table1

JOIN table2 ON condition

WHERE condition;

Example:

if we want to create a view that displays the names and salaries of employees who work in the IT department from the employees and departments tables, we can use the following syntax:

CREATE VIEW it_department_employees AS

SELECT e.first_name, e.last_name, e.salary, d.department_name

FROM employees e

JOIN departments d ON e.department_id = d.department_id

WHERE d.department_name = 'IT';

Materialized View:

A materialized view is a view that stores data physically in the database. Unlike simple and complex views, materialized views can improve query performance by precomputing and caching the results of the view. A materialized view can be created using the following 

Syntax:

CREATE MATERIALIZED VIEW [schema.]view_name

REFRESH [FAST | COMPLETE | FORCE]

ON [COMMIT | DEMAND | SYSTEM]

[ENABLE | DISABLE] QUERY REWRITE

AS select_statement;

Explanation of the syntax elements:

• [schema.]view_name - the name of the materialized view, optionally prefixed with the schema name
• REFRESH - specifies the refresh method of the materialized view
• FAST | COMPLETE | FORCE - specifies the refresh method options
• ON - specifies when the materialized view is refreshed
• COMMIT | DEMAND | SYSTEM - specifies the refresh method options
• [ENABLE | DISABLE] QUERY REWRITE - specifies whether query rewrite is enabled or disabled for the materialized view
• AS select_statement - the SQL query that defines the materialized view

Example: 01

If we want to create a materialized view that displays the number of employees in each department from the employees and departments tables, we can use the following syntax:

CREATE MATERIALIZED VIEW employee_count_by_department AS

SELECT d.department_name, COUNT(e.employee_id) AS employee_count

FROM employees e

JOIN departments d ON e.department_id = d.department_id

GROUP BY d.department_name

ORDER BY d.department_name;

Example: 02

Suppose we have two tables in the database - employees and departments. We want to create a materialized view that shows the total salary of each department. Here is the example SQL code to create such a materialized view:

CREATE MATERIALIZED VIEW emp_salary_by_dept

REFRESH FAST ON DEMAND

ENABLE QUERY REWRITE

AS

SELECT d.department_name, SUM(e.salary) as total_salary

FROM employees e, departments d

WHERE e.department_id = d.department_id

GROUP BY d.department_name;

In this example, we've created a materialized view named emp_salary_by_dept. The REFRESH clause specifies that the materialized view will be refreshed FAST and ON DEMAND. The ENABLE QUERY REWRITE clause enables query rewrite for the materialized view, which means that Oracle can use the materialized view to rewrite queries for improved performance.

The AS clause defines the SQL query that defines the materialized view. In this case, we're selecting the department_name column from the departments table and the sum of the salary column from the employees table, grouping the results.

Example:03

Let's say we have a table called sales that contains information about sales made by different employees in different regions. We can create a materialized view that calculates the total sales made by each employee by region as follows:

CREATE MATERIALIZED VIEW mv_sales_by_employee_region

REFRESH FAST ON COMMIT

AS

SELECT employee_id, region, SUM(sales_amount) AS total_sales

FROM sales

GROUP BY employee_id, region;

In the above example, the materialized view mv_sales_by_employee_region is created with the REFRESH FAST ON COMMIT option, which means that the materialized view is refreshed automatically whenever there is a commit on the base table sales. The SELECT statement in the AS clause defines the columns and the data that are included in the materialized view. The GROUP BY clause groups the sales data by employee and region, and the SUM function calculates the total sales for each employee by region.

Example:04

Let's say we have two tables, employees and departments, with the following data:

Employees Table:

employee_id first_name last_name salary department_id

        1                 John         Smith         5000     1

        2                 Jane         Doe                6000     1

        3                 Bob                 Johnson 7000     2

        4                 Sally         Smith         8000     2

Departments Table:

department_id department_name

        1                     Sales

        2                     Marketing

We can create a materialized view that shows the total salary of each department using the following SQL statement:

CREATE MATERIALIZED VIEW department_salary_summary

REFRESH COMPLETE ON DEMAND

AS

SELECT d.department_name, SUM(e.salary) AS total_salary

FROM employees e

JOIN departments d ON e.department_id = d.department_id

GROUP BY d.department_name;

In this example, the material    zed view is called department_salary_summary, and it will be refreshed completely on demand. The query in the materialized view calculates the total salary of each department by joining the employees and departments tables and grouping the data by department.

Once the materialized view is created, we can query it just like a regular table:

SELECT * FROM department_salary_summary;

Note - that the first time we query the materialized view, it will be populated with data from the underlying tables. Subsequent queries will use the cached data until the materialized view is refreshed.

Temporary vs External Tables

 Temporary Table:

A temporary table is a table that is created and used only for the duration of a transaction or a session. It is typically used to store intermediate results that are needed to perform complex queries or operations. Temporary tables are automatically dropped at the end of a transaction or a session. Temporary tables are useful in scenarios where you need to perform multiple queries on the same set of data or when you want to store intermediate results for further processing.

Syntax:

CREATE GLOBAL TEMPORARY TABLE table_name

(

  column1 datatype [ NULL | NOT NULL ],

  column2 datatype [ NULL | NOT NULL ],

  ...

) ON COMMIT [ PRESERVE ROWS | DELETE ROWS ];

Here, table_name is the name of the temporary table that you want to create, and datatype represents the data type of the columns in the table. You can define as many columns as needed, each separated by a comma.

The ON COMMIT clause is used to specify when the data in the temporary table should be cleared. You can choose to PRESERVE ROWS, which means that the data will be preserved until the end of the session, or DELETE ROWS, which means that the data will be deleted as soon as the transaction ends.

Example:

Here's an example of creating a temporary table:

CREATE GLOBAL TEMPORARY TABLE temp_customers

(

  id NUMBER(10) NOT NULL,

  name VARCHAR2(50) NOT NULL,

  email VARCHAR2(50),

  phone VARCHAR2(20),

  city VARCHAR2(30),

  country VARCHAR2(30)

) ON COMMIT PRESERVE ROWS;

This creates a temporary table called temp_customers with six columns: id, name, email, phone, city, and country. The data in this table will be preserved until the end of the session.

You can insert data into this table just like you would with a regular table:

INSERT INTO temp_customers (id, name, email, phone, city, country)

VALUES (1, 'John Doe', 'johndoe@example.com', '555-1234', 'New York', 'USA');

And you can select data from the temporary table just like any other table:

SELECT * FROM temp_customers;

External Table:

An external table is a table-like object that represents data stored outside of a database. It is a read-only table, which means that you cannot modify the data in the external table. You can create an external table to access data in a flat file, a remote database, or any other data source accessible by the database server. The structure of an external table is defined by a set of metadata that describes the data's layout, format, and location. An external table is accessed just like a regular database table, and you can query it using SQL.

Syntax:

CREATE TABLE table_name (

  column1 datatype [ NULL | NOT NULL ],

  column2 datatype [ NULL | NOT NULL ],

  ...

) 

ORGANIZATION EXTERNAL (

  TYPE type_name

  DEFAULT DIRECTORY directory_name

  ACCESS PARAMETERS (

    param1_name [ = value1 ],

    param2_name [ = value2 ],

    ...

  )

  LOCATION ( 'file_name' [ , 'file_name' ]* )

);

Let's break down the different parts of this syntax:

• CREATE TABLE: The statement used to create a new table in the database.
• table_name: The name of the table you want to create.
• column1, column2, etc.: The columns of the table, along with their data types.
• NULL | NOT NULL: An optional constraint that specifies whether a column can contain NULL values or not.

After the column definitions, you specify the ORGANIZATION EXTERNAL clause to indicate that you want to create an external table.

• TYPE type_name: The type of external table you want to create. For example, you can specify ORACLE_LOADER for a table that reads data from a flat file using SQL*Loader.
• DEFAULT DIRECTORY directory_name: The directory in the file system where the external data files are located. This directory must be defined in the database using the CREATE DIRECTORY statement.
• ACCESS PARAMETERS: A set of parameters that configure how the external table accesses the external data files.
• param1_name [ = value1 ], param2_name [ = value2 ], ...: The name and value of each parameter. The parameters you use depend on the type of external table you're creating.
• LOCATION ( 'file_name' [ , 'file_name' ]* ): The name or names of the external data files. You can specify multiple files separated by commas.

Example: 01 

Here's an example of how you might define an external table that reads data from a CSV file using the ORACLE_LOADER type:

CREATE TABLE my_external_table (

  id NUMBER,

  name VARCHAR2(100),

  salary NUMBER

)

ORGANIZATION EXTERNAL (

  TYPE ORACLE_LOADER

  DEFAULT DIRECTORY my_data_dir

  ACCESS PARAMETERS (

    RECORDS DELIMITED BY NEWLINE

    FIELDS TERMINATED BY ','

    MISSING FIELD VALUES ARE NULL

  )

  LOCATION ('employees.csv')

);

In this example, we're creating an external table called my_external_table that has three columns: id, name, and salary. The table is configured to use the ORACLE_LOADER type to read data from a CSV file called employees.csv located in the my_data_dir directory. We're also specifying some access parameters to indicate that the data is delimited by newlines and fields are separated by commas.

Example: 02

Suppose you have a CSV file containing sales data. You can create an external table to read data from that CSV file using the following SQL statement:

CREATE TABLE sales_ext (

    id NUMBER,

    product VARCHAR2(50),

    sale_date DATE,

    sale_amount NUMBER

)

ORGANIZATION EXTERNAL (

    TYPE CSV

    DEFAULT DIRECTORY data_dir

    LOCATION ('sales.csv')

);

In summary, an external table is used to access data stored outside the database, while a temporary table is used to store intermediate results within the database for the duration of a transaction or a session.