Advertisement
Not a member of Pastebin yet?
Sign Up,
it unlocks many cool features!
- -- Написать процедуру, которая выводит информацию о контрактах,
- -- в которых сумма заказа лежит в диапазоне +/- 50$ от введенного значения.
- -- Если контрактов с такой суммой не имеется, должно выводиться
- -- соответствующее сообщение.
- SET SERVEROUTPUT ON
- CREATE OR REPLACE PROCEDURE INFO (COST IN INTEGER) IS
- CURSOR CONTRACT IS SELECT customer_id, order_cost FROM Orders;
- INPUT_ERROR EXCEPTION;
- COST_ERROR EXCEPTION;
- CONTRACT_LIST CONTRACT%ROWTYPE;
- PRAGMA AUTONOMOUS_TRANSACTION;
- BEGIN
- DBMS_OUTPUT.PUT_LINE(' ');
- IF COST IS NULL OR COST <= 0 THEN
- RAISE INPUT_ERROR;
- END IF;
- OPEN CONTRACT;
- FETCH CONTRACT INTO CONTRACT_LIST;
- WHILE CONTRACT%FOUND
- LOOP
- IF CONTRACT_LIST.order_cost >= COST-50 AND CONTRACT_LIST.order_cost <= COST+50 THEN
- DBMS_OUTPUT.PUT_LINE('Customer id= ' || CONTRACT_LIST.customer_id || 'Order cost=' || CONTRACT_LIST.order_cost);
- ELSE
- RAISE COST_ERROR;
- END IF;
- END LOOP;
- CLOSE CONTRACT;
- EXCEPTION
- WHEN INPUT_ERROR THEN
- DBMS_OUTPUT.PUT_LINE('Check the input data. Cost must be not null and positive number.');
- WHEN COST_ERROR THEN
- DBMS_OUTPUT.PUT_LINE('There is no order with such cost.');
- END;
- /
- BEGIN
- INFO(2900);
- END;
- /
Advertisement
Add Comment
Please, Sign In to add comment
Advertisement