問題：撰寫一個存盤程序，它接受camperid、category、chargeate 和amount 作為輸入引數，并有一個varchar2 型別的輸出引數，它將包含一條訊息。o 如果露營者 ID 不是有效的露營者 ID，則輸出引數應設定為“錯誤：無此類露營者 ID”。o 如果類別不是有效類別，則輸出引數應設定為“錯誤：無此類收費類別”。o 如果金額不在正確范圍內，則輸出引數應設定為“錯誤：金額必須 > 0 且不超過 40 美元”。o 如果金額會導致總花費超過該露營者的預算，則輸出引數應設定為“錯誤：資金不足”。這是我的查詢
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;
我收到此錯誤 LINE/COL ERROR
41/5 PLS-00103：遇到符號“;” 當期望以下之一時：如果錯誤：檢查編譯器日志
uj5u.com熱心網友回復：
當您只需要一個露營者 ID 時，為什么要傳遞一大堆引數（而您不需要對其余引數做任何處理）？那簡直沒道理...
這是一個顯示如何執行此操作的選項。
示例表：
SQL> select * from charges;
CAMPERID CATEGORY CHARGEDATE AMT
---------- ---------- ------------------- ----------
1 Category 1 12.10.2021 00:00:00 500
SQL>
程式：
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.
測驗：
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>
你寫的代碼：
- 錯別字，錯字，錯字...是
p_error還是p_erorr？ if應該有end ifif不能包含select陳述句（必須單獨選擇值，然后在 中使用if）where條件不對。永遠不要將引數命名為與列名相同的名稱。這：where camperid = camperid等于where 1 = 1并且您將獲得表中的所有行，而不僅僅是您要查找的行
uj5u.com熱心網友回復：
如果沒有表定義 (ddl) 和示例資料，就很難解決您的問題。從您的描述和代碼來看，您似乎只有一個表；但閱讀從設計角度來看，我看到至少3個表。你還有很長的路要走。在下文中，我將基本上只注釋您的代碼（有一些格式更改，但僅限于格式和注釋）。然后我將展示許多可能的解決方案，并盡可能保持代碼簡潔。
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.
現在一個可能的重寫保持這個單一表中存在所有必要的假設。可能/希望是一個錯誤的假設，但可以使用它。
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;
您可能希望閱讀有關 Oracle用戶定義例外的資訊。
我意識到這是一項家庭作業（至少希望如此），您需要撰寫一個程式。但是，只需指出在表級別定義正確的主鍵、外鍵和檢查約束，這一切都可以在沒有任何額外代碼的情況下完成。我會把這些留給你去調查。（提示：3 桌）。
注：未測驗。
轉載請註明出處，本文鏈接：https://www.uj5u.com/houduan/321358.html
上一篇：不是有效的月份，而是數字資料型別Java-Oracle
下一篇：如何合并ggpplot中的圖例