------------------------------------------------------------------------------------------
-- Avoiding an Error
------------------------------------------------------------------------------------------
CREATE or replace FUNCTION get_speed(p_distance number, p_time number) RETURN number IS
	v_speed number :=0;
BEGIN
	IF p_time = 0 THEN	
		DBMS_OUTPUT.PUT_LINE('Time must be greater than 0 ');
	ELSE
		v_speed := p_distance / p_time;
	END IF;
	RETURN v_speed;
END;
/

------------------------------------------------------------------------------------------
-- Raising an Exception
------------------------------------------------------------------------------------------
CREATE or replace FUNCTION get_speed(p_distance number, p_time number) RETURN number IS
	v_speed number;
BEGIN
	v_speed := p_distance / p_time;
	RETURN v_speed;
EXCEPTION									-- Exception block
	WHEN  ZERO_DIVIDE  THEN
		DBMS_OUTPUT.PUT_LINE('Time must be greater than 0 ');
		RETURN 0;
END;
/

------------------------------------------------------------------------------------------
-- Raising an Exception 
--   Capturing all other exceptions with OTHERS and SQLCODE, SQLERRM
------------------------------------------------------------------------------------------
CREATE or replace FUNCTION get_speed(p_distance number, p_time number) RETURN number IS
	v_speed number;
BEGIN
	v_speed := p_distance / p_time;
	RETURN v_speed;
EXCEPTION									-- Exception block
	WHEN  ZERO_DIVIDE  THEN
		DBMS_OUTPUT.PUT_LINE('Time must be greater than 0 ');
		RETURN 0;
	WHEN  OTHERS  THEN
		DBMS_OUTPUT.PUT_LINE('Unknown Error ' || SQLCODE || ' '|| SQLERRM);
		RETURN 0;
END;
/

------------------------------------------------------------------------------------------
-- Creating and Raising your own Exceptions 
--   Declare it
--   Raise it
--   Handle it
------------------------------------------------------------------------------------------
CREATE or REPLACE FUNCTION get_speed(p_distance number, p_time number) RETURN number IS
	v_speed 		number;
	x_time_negative		exception;				    -- declare custom exception
	x_value_too_large	exception;
----
BEGIN
	IF  p_time < 0  THEN
		RAISE  x_time_negative;				    -- raise custom exception
	END IF;
	IF  p_distance > 10000000 or p_time > 100000  THEN
		RAISE  x_value_too_large;
	END IF;
	v_speed := p_distance / p_time;
	RETURN v_speed;
--
EXCEPTION
	WHEN  ZERO_DIVIDE  THEN
		DBMS_OUTPUT.PUT_LINE('Time cannot be 0 ');
		RETURN 0;
	WHEN  X_TIME_NEGATIVE  THEN				    -- handle custom exception
		DBMS_OUTPUT.PUT_LINE('Time must be positive ');
		RETURN -1;
	WHEN  X_VALUE_TOO_LARGE  THEN
		DBMS_OUTPUT.PUT_LINE('Distance and/or Time value is too high ');
		RETURN -1;
	WHEN  OTHERS  THEN
		DBMS_OUTPUT.PUT_LINE('Unknown Error ' || SQLCODE || ' - '|| SQLERRM );
		RETURN -99;
END;
/

select get_speed(10000000, 3) from dual