如何在Oracle中使用WITH子句定义函数？
问题：
您想使用WITH子句在Oracle中定义一个函数。
解
从OracleDatabase12.1开始，您可以在出现SELECT语句的同一SQL语句中定义函数和过程。通过允许两个步骤都在SQL引擎中进行，从而允许在PL/SQL和SQL引擎之间进行上下文切换，从而提高了性能。
需要使用WITH子句定义函数或过程。请记住，在Oracle平台的早期版本中，只能在WITH子句中定义子查询。
示例
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;WITHFUNCTION功能在许多不同的情况下很有用，尤其是当您需要使用某个功能以获得更好的一次性性能时。
我看到的此功能的主要缺点是，您失去了可重用功能的好处，而是通过减少SQL和PL/SQL引擎之间的上下文转换来获得改进的性能。建议进行成本分析，并权衡收益与其他情况下重用该功能的需求。
数据准备：用于该问题的数据如下所示。
示例
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');
示例
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);
示例
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);示例
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;