CREATE OR REPLACE PROCEDURE get_productdetails(
p_reqid OUT requirement.req_id%type,
p_pid OUT requirement.p_id%type,
p_rstaffid OUT requirement.r_staff_id%type,
p_demand requirement.demand%type)
IS
CURSOR c_demand IS
SELECT req_id,p_id,r_staff_id from requirement where demand = upper(p_demand);
BEGIN
FOR i in c_demand Loop
DBMS_OUTPUT.PUT_LINE('Requirement ID :'||i.req_id);
DBMS_OUTPUT.PUT_LINE('Product ID'||i.p_id);
DBMS_OUTPUT.PUT_LINE('Staff ID :'||i.r_staff_id);
END LOOP;
END get_productdetails;
用戶必須僅在“HIGH/LOW/AVG”中輸入需求，否則它應該拋出例外，要求以該格式輸入資料你能幫我寫一個相應的例外嗎
uj5u.com熱心網友回復：
由于我無權訪問您的表結構，因此下面的代碼未經測驗。一個好的起點是oracle官方檔案。在您的情況下，需要一個用戶定義的例外，您需要在宣告部分宣告，然后RAISE最后在EXCEPTION塊中捕獲。
CREATE OR REPLACE PROCEDURE get_productdetails(
p_reqid OUT requirement.req_id%type,
p_pid OUT requirement.p_id%type,
p_rstaffid OUT requirement.r_staff_id%type,
p_demand requirement.demand%type)
IS
-- declare the user-defined exception
invalid_input EXCEPTION;
CURSOR c_demand IS
SELECT req_id,p_id,r_staff_id from requirement where demand = upper(p_demand);
BEGIN
IF p_demand NOT IN ('HIGH','LOW','AVG') THEN
-- raise the exception if p_demand not one of the 3 accepted values
RAISE invalid_input;
END IF;
FOR i in c_demand Loop
DBMS_OUTPUT.PUT_LINE('Requirement ID :'||i.req_id);
DBMS_OUTPUT.PUT_LINE('Product ID'||i.p_id);
DBMS_OUTPUT.PUT_LINE('Staff ID :'||i.r_staff_id);
END LOOP;
EXCEPTION WHEN invalid_input THEN
dbms_output.put_line('Only HIGH/LOW/AVG are valid input');
END get_productdetails;
轉載請註明出處，本文鏈接：https://www.uj5u.com/houduan/324102.html
上一篇：執行觸發器陳述句時出現錯誤