Data Structure
Networking
RDBMS
Operating System
Java
MS Excel
iOS
HTML
CSS
Android
Python
C Programming
C++
C#
MongoDB
MySQL
Javascript
PHP
Physics
Chemistry
Biology
Mathematics
English
Economics
Psychology
Social Studies
Fashion Studies
Legal Studies
- Selected Reading
- UPSC IAS Exams Notes
- Developer's Best Practices
- Questions and Answers
- Effective Resume Writing
- HR Interview Questions
- Computer Glossary
- Who is Who
How to define a function Using the WITH clause in Oracle?
Problem:
You want to define a function in Oracle using WITH clause.
Solution
Starting with Oracle Database 12.1, you can define functions as well as procedures within the same SQL statement in which the SELECT statement appears. This allows the context switch between the PL/SQL and SQL engines by allowing both steps to take place in the SQL engine and, in turn, provides for a performance gain.
The function or procedure needs to be defined using the WITH clause. Remember, In previous versions of the Oracle platform, only subqueries could be defined in the WITH clause.
Example
WITH FUNCTION func_amount(p_emailid IN VARCHAR2) RETURN NUMBER IS l_amt NUMBER; BEGIN SELECT SUM(oi.quantity*p.unit_price) AS AMT INTO l_amt FROM sample_customers C, sample_orders O, sample_order_items OI, sample_products P WHERE C.customer_id =o.customer_id AND o.order_id = oi.order_id AND oi.product_id = p.product_id AND c.email_address = p_emailid; RETURN l_amt; END; SELECT func_amount ('tammy.bryant@internalmail') AS TOTAL_AMOUNT FROM DUAL;
The WITH FUNCTION feature is useful in many different situations especially when you need to use a function for better once off performance.
The main downside that I see to this feature is that you lose the benefits of a reusable function in favor of obtaining improved performance through reduced context shifts between the SQL and PL/SQL engines. It is advisable to do a cost analysis and weigh the benefits against the possible need to reuse the function in other contexts.
Data Preparation: Data used for the problem is shown below.
Example
DROP TABLE sample_customers; DROP TABLE sample_orders; DROP TABLE sample_order_items; DROP TABLE sample_products; create table sample_customers ( customer_id integer generated by default on null as identity, email_address varchar2(255 char) not null, full_name varchar2(255 char) not null) ; insert into sample_customers (CUSTOMER_ID,EMAIL_ADDRESS,FULL_NAME) values (1,'tammy.bryant@internalmail','Tammy Bryant'); insert into sample_customers (CUSTOMER_ID,EMAIL_ADDRESS,FULL_NAME) values (2,'roy.white@internalmail','Roy White'); insert into sample_customers (CUSTOMER_ID,EMAIL_ADDRESS,FULL_NAME) values (3,'gary.jenkins@internalmail','Gary Jenkins'); insert into sample_customers (CUSTOMER_ID,EMAIL_ADDRESS,FULL_NAME) values (4,'victor.morris@internalmail','Victor Morris'); insert into sample_customers (CUSTOMER_ID,EMAIL_ADDRESS,FULL_NAME) values (5,'beverly.hughes@internalmail','Beverly Hughes'); insert into sample_customers (CUSTOMER_ID,EMAIL_ADDRESS,FULL_NAME) values (6,'evelyn.torres@internalmail','Evelyn Torres');
Example
create table sample_products ( product_id integer generated by default on null as identity , product_name varchar2(255 char) not null, unit_price number(10,2)); insert into sample_products (PRODUCT_ID,PRODUCT_NAME,UNIT_PRICE) values (1,'tennis raquet',29.55); insert into sample_products (PRODUCT_ID,PRODUCT_NAME,UNIT_PRICE) values (2,'tennis net',16.67); insert into sample_products (PRODUCT_ID,PRODUCT_NAME,UNIT_PRICE) values (3,'tennis ball',44.17); insert into sample_products (PRODUCT_ID,PRODUCT_NAME,UNIT_PRICE) values (4,'tennis shoe',43.71); insert into sample_products (PRODUCT_ID,PRODUCT_NAME,UNIT_PRICE) values (5,'tennis bag',38.28); insert into sample_products (PRODUCT_ID,PRODUCT_NAME,UNIT_PRICE) values (6,'soccer ball',19.16); insert into sample_products (PRODUCT_ID,PRODUCT_NAME,UNIT_PRICE) values (7,'soccer net',19.58);
Example
create table sample_orders ( order_id integer generated by default on null as identity, customer_id integer not null, store_id integer not null) ; Insert into sample_orders (ORDER_ID,CUSTOMER_ID,STORE_ID) values (1,3,1); Insert into sample_orders (ORDER_ID,CUSTOMER_ID,STORE_ID) values (2,45,1); Insert into sample_orders (ORDER_ID,CUSTOMER_ID,STORE_ID) values (3,18,1); Insert into sample_orders (ORDER_ID,CUSTOMER_ID,STORE_ID) values (4,45,1); Insert into sample_orders (ORDER_ID,CUSTOMER_ID,STORE_ID) values (5,2,1); Insert into sample_orders (ORDER_ID,CUSTOMER_ID,STORE_ID) values (6,74,1); Insert into sample_orders (ORDER_ID,CUSTOMER_ID,STORE_ID) values (7,9,1); Insert into sample_orders (ORDER_ID,CUSTOMER_ID,STORE_ID) values (8,109,1); Insert into sample_orders (ORDER_ID,CUSTOMER_ID,STORE_ID) values (9,127,1);
Example
create table sample_order_items ( order_id integer not null, product_id integer not null, unit_price number(10,2) not null, quantity integer not null); insert into sample_order_items (ORDER_ID,PRODUCT_ID,UNIT_PRICE,QUANTITY) values (1,33,37,4); insert into sample_order_items (ORDER_ID,PRODUCT_ID,UNIT_PRICE,QUANTITY)values (1,11,30.69,2); insert into sample_order_items (ORDER_ID,PRODUCT_ID,UNIT_PRICE,QUANTITY) values (2,41,8.66,3); insert into sample_order_items (ORDER_ID,PRODUCT_ID,UNIT_PRICE,QUANTITY) values (2,32,5.65,5); insert into sample_order_items (ORDER_ID,PRODUCT_ID,UNIT_PRICE,QUANTITY) values (3,41,8.66,5); insert into sample_order_items (ORDER_ID,PRODUCT_ID,UNIT_PRICE,QUANTITY) values (4,20,28.21,2); insert into sample_order_items (ORDER_ID,PRODUCT_ID,UNIT_PRICE,QUANTITY) values (4,38,22.98,4); insert into sample_order_items (ORDER_ID,PRODUCT_ID,UNIT_PRICE,QUANTITY) values (4,46,39.16,4); insert into sample_order_items (ORDER_ID,PRODUCT_ID,UNIT_PRICE,QUANTITY) values (5,40,34.06,4); insert into sample_order_items (ORDER_ID,PRODUCT_ID,UNIT_PRICE,QUANTITY) values (5,32,5.65,3); insert into sample_order_items (ORDER_ID,PRODUCT_ID,UNIT_PRICE,QUANTITY) values (6,6,38.28,3); COMMIT;
4K+ Views
