CODEWITHSUNDEEP
sqlplsqloracle-sqldeveloper
Stuart Bachellier
Stuart Bachellier

Reputation: 33

Need help creating a procedure with plsql

I need to create a procedure where it counts how many cars a customer has and how much the customer has spent on all those cars. I managed to get the select statement working separately but implementing it into the procedure has me stumped.

CREATE OR REPLACE PROCEDURE getcars (
    v_customername     IN    saleinv.custname%TYPE
    , v_totalcars    OUT   NUMBER
    , v_moneyspent   OUT   NUMBER
) AS
BEGIN
    SELECT
    sale.custname
    , COUNT(sale.carserial)
    , round(SUM(sale.carsaleprice + serv.partscost + serv.laborcost), 2)
    INTO v_customername, v_totalcars, v_moneyspent
FROM
    saleinv   sale
    FULL OUTER JOIN servinv   serv ON sale.custname = serv.custname
    WHERE sale.custname = v_customername
GROUP BY
    sale.custname;
END;
/

Here are the errors I am getting:

Error(6,5): PL/SQL: SQL Statement ignored

Error(10,10): PLS-00403: expression 'V_CUSTOMERNAME' cannot be used as an INTO-target of a SELECT/FETCH statement

Error(10,52): PL/SQL: ORA-00904: : invalid identifier

Upvotes: 0

Views: 60

Answers (1)

sticky bit
sticky bit

Reputation: 37472

Just don't fetch into v_customername. It's an IN parameter. And sale.custname cannot be different from the inputted value due to the WHERE clause which demands sale.custname = v_customername anyway.

...
SELECT count(sale.carserial),
       round(sum(sale.carsaleprice + serv.partscost + serv.laborcost), 2)
       INTO v_totalcars,
            v_moneyspent
       FROM ...
...

Upvotes: 1

Related Questions