如何在Oracle中使用WITH子句定义函数？

问题：

您想使用WITH子句在Oracle中定义一个函数。

解

从Oracle Database 12.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;

WITH FUNCTION功能在许多不同的情况下很有用，尤其是当您需要使用某个功能以获得更好的一次性性能时。

我看到的此功能的主要缺点是，您失去了可重用功能的好处，而是通过减少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;