資料結構
網路
關係型資料庫管理系統
作業系統
Java
MS Excel
iOS
HTML
CSS
Android
Python
C 程式設計
C++
C#
MongoDB
MySQL
Javascript
PHP
物理學
化學
生物學
數學
英語
經濟學
心理學
社會學
時尚研究
法律研究如何在 Oracle 中使用 WITH 子句定義函式？
問題
您希望在 Oracle 中使用 WITH 子句定義一個函式。
解決方案
從 Oracle 資料庫 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;
更新於： 2020年12月5日
5K+ 瀏覽量
廣告