CREATE OR REPLACE FUNCTION get_customer_names ( p_product_id IN NUMBER ) RETURN VARCHAR2 IS v_names VARCHAR2(4000) := ''; BEGIN FOR r IN (SELECT DISTINCT c.cust_name FROM orders o JOIN customers c ON o.cust_id = c.cust_id WHERE o.prod_id = p_product_id) LOOP v_names := v_names || r.cust_name || ', '; END LOOP; -- 去掉最后一个逗号和空格 v_names := SUBSTR(v_names, 1, LENGTH(v_names)-2);

RETURN v_names;

END; /

CREATE OR REPLACE PROCEDURE proc_product_customers ( p_product_id IN NUMBER ) IS v_customer_names VARCHAR2(4000); BEGIN v_customer_names := get_customer_names(p_product_id); dbms_output.put_line('Customers who ordered product ' || p_product_id || ': ' || v_customer_names); END; /

执行过程：

EXEC proc_product_customers(100);

输出：

Customers who ordered product 100: John Smith, Emily Brown, David Lee.