I am very new to SQL and PL/SQL. I am writing a procedure to take a museum, average the price of art, then increase the average by a percent. The user enters the museum ID (musmID) and percent increase. If the percent is too high there is an exception raised. If the musmID entered does not match one on the table, an exception is raised. Output is museum name, old avg price, and new avg price. So far it will only work when the exception for invalid musmID is not written. I keep getting the same error of PLS-00201:identifier 'MUMSID' must be declared.
Table 1 is arts and column names are musmID(FK) and price. Table 2 is museums and column names are museum and musmID(PK).
My code:
CREATE OR REPLACE PROCEDURE proc_second (p_musmID IN arts.musmID%TYPE,
p_percent IN NUMBER) IS
v_musmID arts.musmID%TYPE;
v_oldavg arts.price%TYPE;
v_newavg arts.price%TYPE;
v_musmName museums.museum%TYPE;
too_high EXCEPTION;
musmID_invalid EXCEPTION;
BEGIN
IF p_percent > 40 THEN
raise too_high;
END IF;
IF musmID != p_musmID THEN
raise musmID_invalid;
END IF;
SELECT musmID, AVG(price) INTO v_musmID, v_oldavg from arts
WHERE musmID = p_musmID
GROUP BY musmID;
SELECT museum INTO v_musmName from museums
WHERE musmId = p_musmID;
v_newavg := v_oldavg * (1 + (p_percent/100));
dbms_output.put_line (v_musmname || ' ' || v_oldavg || ' ' || v_newavg);
EXCEPTION
WHEN too_high THEN
dbms_output.put_line ('Specify less than 40 percent increase');
WHEN musmID_invalid THEN
dbms_output.put_line ('You entered an invalid museum ID');
END;
/
Warning: Procedure created with compilation errors.
These are the errors i am getting.
LINE/COL ERROR
-------- -----------------------------------------------------------------
15/3 PL/SQL: Statement ignored
15/6 PLS-00201: identifier 'MUSMID' must be declared
I know it is probably something basic i am not seeing but so far if i get rid of the errors, the procedure raises the musmID_invalid exception no matter what is entered.
Huh. This code is wrongly arranged, as far as I can tell. You're first checking a variable, and then fetch its value.
I've created two dummy tables, just to make the procedure compile.
SQL> create table arts
2 (musmid number,
3 price number);
Table created.
SQL> create table museums
2 (musmid number,
3 museum number);
Table created.
Now, the procedure: it seems that you don't need V_MUSMID variable at all, because it is equal to P_MUSMID parameter (see the SELECT which selects the AVG(PRICE) and its WHERE clause). It means that you can use P_MUSMID throughout the code, instead of V_MUSMID.
It also means that MUSMID_INVALID will never be raised; you should, though, handle the NO_DATA_FOUND which will be raised if you pass a non-existent P_MUSMID.
So, I removed what I thought you don't need and - this is the remaining code; see if it does any good.
SQL> create or replace procedure proc_second (p_musmid in arts.musmid%type,
2 p_percent in number)
3 is
4 v_oldavg arts.price%type;
5 v_newavg arts.price%type;
6 v_musmname museums.museum%type;
7 too_high exception;
8 begin
9 if p_percent > 40 then
10 raise too_high;
11 end if;
12
13 select m.museum, avg(a.price)
14 into v_musmname, v_oldavg
15 from arts a join museums m on m.musmid = a.musmid
16 where a.musmid = p_musmid
17 group by m.museum;
18
19 v_newavg := v_oldavg * (1 + (p_percent/100));
20
21 dbms_output.put_line (v_musmname || ' ' || v_oldavg || ' ' || v_newavg);
22
23 exception
24 when too_high then
25 dbms_output.put_line ('Specify less than 40 percent increase');
26 when no_data_found then
27 dbms_output.put_line ('You entered an invalid museum ID');
28 end;
29 /
Procedure created.
SQL> exec proc_second(1, 20);
You entered an invalid museum ID
PL/SQL procedure successfully completed.
SQL>