Oracle数据库11g：SQL计划管理（三）(1)(4)

-----
-- Query: SPM_3_1.sql
-----
VARIABLE cust_last_name VARCHAR2(30);
VARIABLE beg_prod_id NUMBER;
VARIABLE end_prod_id NUMBER;
BEGIN
:cust_last_name := 'Sand%';
:beg_prod_id := 100;
:end_prod_id := 500;
END;
/
PRINT :cust_last_name :beg_prod_id :end_prod_id
SELECT /*SPM_3.1*/
S.cust_id
,C.cust_last_name
,S.prod_id
,P.prod_name
,SUM(S.amount_sold)
,SUM(S.quantity_sold)
FROM
sh.sales S
,sh.customers C
,sh.products P
WHERE S.cust_id = C.cust_id
AND S.prod_id = P.prod_id
AND S.prod_id BETWEEN :beg_prod_id AND :end_prod_id
AND C.cust_last_name LIKE :cust_last_name
GROUP BY
S.cust_id
,C.cust_last_name
,S.prod_id
,P.prod_name
ORDER BY
S.cust_id
,C.cust_last_name
,S.prod_id
,P.prod_name
;