Question: Write a stored procedure which accepts the camperid, category, chargedate, and amount as input parameters and has one output parameter which is of type varchar2 which will contain a message. o If the camperID is not a valid camperID the output parameter should be set to 'ERROR: No such camper ID.' o If the category is not a valid category the output parameter should be set to 'ERROR: No such charge category.' o If the Amount is not in the correct range the output parameter should be set to 'ERROR: amount must be >0 and no more than $ 40.' o If the Amount would cause the total spent to be more than the budget for that camper the output parameter should be set to 'ERROR: insufficient funds.' Here is my query
create or replace procedure P_STORED
(c_camperid in charges.camperid%type,c_category in charges.camperid%type, c_chargedate in charges.camperid%type, c_amt in charges.camperid%type,
c_message out varchar2 )
as
p_camperid charges.camperid%type;
c_cat charges.category%type;
a_amt charges.amt%TYPE;
c_date charges.chargedate%type;
p_error varchar2(5);
begin
v_error := 'False';
select c.camperid, c.category
into p_camperid, c_cat
from charges c
where c.camperid = c_camperid;
if p_camperid = c.camperid then
v_error := 'true';
c_message := 'Camper ' || c_camperid || ' exists';
else
v_error := 'False';
c_message := 'Error: no such camper ID (' || c_camperid || ')';
end if;
if c_cat = c.category then
v_error := 'true';
c_message := 'Category ' || c_cat || ' exists';
else
v_error := 'False';
c_message := 'Error: no such charge category (' || c_cat || ')';
end if;
if(a_amt >0 and a_amt < 40) then
v_error := 'true';
c_message := 'Amount ' || a_amt|| ' is with in range';
else
v_error := 'False';
c_message := 'amount must be >0 and no more than $ 40.';
end if;
if(campers.spent < campers.budget) then
c_message := 'Amount ' || a_amt|| ' is under budget';
else
c_message := 'ERROR: insufficient funds';
end;
I get this error LINE/COL ERROR
41/5 PLS-00103: Encountered the symbol ";" when expecting one of the following: if Errors: check compiler log
CodePudding user response：
Why would you pass the whole bunch of parameters when all you need is a camper ID (and there's nothing you'd do with the rest of them)? That just doesn't make sense ...
Here's an option which shows how to do it.
Sample table:
SQL> select * from charges;
CAMPERID CATEGORY CHARGEDATE AMT
---------- ---------- ------------------- ----------
1 Category 1 12.10.2021 00:00:00 500
SQL>
Procedure:
SQL> create or replace procedure p_test
2 (par_camperid in charges.camperid%type,
3 par_message out varchar2
4 )
5 as
6 l_camperid charges.camperid%type;
7 begin
8 select c.camperid
9 into l_camperid
10 from charges c
11 where c.camperid = par_camperid;
12
13 par_message := 'Camper ' || par_camperid || ' exists';
14 exception
15 when no_data_found then
16 par_message := 'Error: no such camper ID (' || par_camperid || ')';
17 end;
18 /
Procedure created.
Testing:
SQL> set serveroutput on;
SQL> declare
2 l_msg varchar2(100);
3 begin
4 p_test(1, l_msg);
5 dbms_output.put_line(l_msg);
6 end;
7 /
Camper 1 exists
PL/SQL procedure successfully completed.
SQL> declare
2 l_msg varchar2(100);
3 begin
4 p_test(2, l_msg);
5 dbms_output.put_line(l_msg);
6 end;
7 /
Error: no such camper ID (2)
PL/SQL procedure successfully completed.
SQL>
Code you wrote:
- typos, typos, typos ... is it
p_errororp_erorr? ifshould haveend ififcan't contain aselectstatement (you have to select value separately, and then use it inif)wherecondition is wrong. Never, ever name parameters the same as column names. This:where camperid = camperidis equal towhere 1 = 1and you'd get all rows from the table, not just the one you're looking for
CodePudding user response：
Without table definitions (ddl) and sample data it is difficult to address your issues. From your description and code it appears you have a single table; but reading from a design perspective I see at least 3 tables. You have a long way to go. In the following I will essentially just annotate your code (with some format changes, but only format, and of the the annotations). I will then show one on many possible solutions, keeping the code as succinct as possible.
create or replace
procedure p_stored( -- Bad name. Name should indicate what the procedure does.
c_camperid in charges.camperid%type
, c_category in charges.camperid%type
, c_chargedate in charges.camperid%type
, c_amt in charges.camperid%type
, c_message out varchar2
)
as
-- what do the various prefix values mean. Would be better (IMHO) to have a single
-- prefix indicating the use/location. My preference is l_ for local variables
p_camperid charges.camperid%type;
c_cat charges.category%type;
a_amt charges.amt%TYPE;
c_date charges.chargedate%type;
p_error varchar2(5);
begin
v_error := 'False'; -- v_error not defined. Do you mean p_error?
-- really does not matter As it is not used other
-- setting its value ( multiple times)
select c.camperid, c.category
into p_camperid, c_cat
from charges c
where c.camperid = c_camperid;
if (p_camperid = c.camperid) then -- this would always be true, if not Oracle would have thrown
v_error := 'true'; -- a no_data_found exception on above select. that is if it were valid - it is not
c_message := 'Camper ' || c_camperid || ' exists';
else
v_error := 'False';
c_message := 'Error: no such camper ID (' || c_camperid || ')';
end if;
if c_cat = c.category then -- invalid refrence to table c.xxx does not exist after the Select
v_error := 'true';
c_message := 'Category ' || c_cat || ' exists';
else
v_error := 'False';
c_message := 'Error: no such charge category (' || c_cat || ')';
end if;
if (a_amt >0 and a_amt < 40) then -- a_amt never populated so it is NULL
v_error := 'true';
c_message := 'Amount ' || a_amt|| ' is with in range';
else -- because a_amt is null code falls into here every time
v_error := 'False';
c_message := 'amount must be >0 and no more than $ 40.';
end if;
-- on if below
-- Cannot refer to a table_name.column_name Those values need to be:
-- defined in the procedure
-- populated via select or value set via assignment
-- same applies above to c.xxx references above
-- Even so the comparision does NOT reference the amount value
-- so if spent=50 and budget=100 and amount = 200 your test passes
if (campers.spent < campers.budget) then
c_message := 'Amount ' || a_amt|| ' is under budget'; -- if all syntax/logic errors/ corrected
-- so evetything is valid you get this message every time.
else
c_message := 'ERROR: insufficient funds';
end; -- add an end if before here. must terminate the last if statement.
Now a possible rewrite keeping the assumption that everything necessary exists in this single table. Probably/hopefully a bad assumption, but it can be worked with.
create or replace
procedure validate_camper(
p_camperid in charges.camperid%type
, p_category in charges.camperid%type
, p_chargedate in charges.camperid%type
, p_amt in charges.camperid%type
, p_message out varchar2
)
as
-- define user exceptions
e_no_category exception;
e_amt_range exception;
e_budget_violation exception;
-- local constants
k_nl constant varchar2(2) := chr(13) || chr(10); -- New_Line: or as appropriate for your OS
-- locak variables
l_charges_rec charges%rowtype;
begin
select *
into l_charges_rec
from charges c
where c.camperid = p_camperid;
if p_category <> l_charges_rec.category then
raise e_no_category;
end if;
if p_amt < 0 or p_amt > 40 then
raise e_amt_range;
end if;
if l_charges_rec.spent p_amt > l_charges_rec.budget then
raise e_budget_violation;
end if;
p_message := 'Camper Valid: No errors detected.';
exception
when no_data_found then
p_message := 'Error: No such camper ID (' || p_camperid || ')';
when e_no_category then
p_message := 'Error: No such charge category (' || p_category || ')';
when e_amt_range then
p_message := 'Error: Amount must be >0 and no more than $ 40.';
when e_budget_violation then
p_message := 'Error: Insufficient funds';
when others then
dbms_output.put_line('Unexpected Error=>' || k_nl
|| DBMS_UTILITY.format_error_stack); -- better write to Error_Log table
raise_application_error(-20199
,'Fatial Error occured in validate_camper.' -- Process failed do not hide that from user
|| k_nl || 'Process Unsuccessful' -- or just raise to show actual error
);
end validate_camper;
You may wish to read up on Oracle user defined exceptions.
I realize this is a homework assignment (at least hopefully) and you need to write a procedure. However, just to point out with proper Primary key, Foreign key, and Check constraints defined at the table level this can all be done without any additional code. I will leave those for you to investigate. (Hint: 3 tables).
Note: Not tested.