如何在 Oracle 中使用 WITH 子句定义函数?
oraclesoftware & codingprogramming更新于 2025/6/2 20:37:17
问题:
您想使用 WITH 子句在 Oracle 中定义一个函数。
解决方案
从 Oracle Database 12.1 开始，您可以在 SELECT 语句出现的同一 SQL 语句中定义函数和过程。这允许 PL/SQL 和 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;
