storedfunctions

-- Stored Functions

USE world_peace;

DROP FUNCTION IF EXISTS check_credit;

DELIMITER $$

CREATE FUNCTION check_credit (
	requesting_customer_id CHAR(10),
	request_amount INT
    )
RETURNS BOOLEAN

DETERMINISTIC

BEGIN

	RETURN
	
	(
    SELECT credit_limit FROM customer WHERE customer_id = requesting_get_qoh_ftncustomer_id
	) >= request_amount;

END $$

DELIMITER ;

-- check to see if the function works
SET @approved = check_credit("C000000001", 9000000);
SELECT @approved;

-- -------------------------------------------------

DROP FUNCTION IF EXISTS get_qoh_ftn;

DELIMITER $$

CREATE FUNCTION get_qoh_ftn (
	request_item_id CHAR(10)
    )
RETURNS INT
    
DETERMINISTIC
    
BEGIN

	RETURN 
    (
     SELECT qoh FROM merchandise_item WHERE merchandise_item_id = request_item_id
    );


END $$
    
DELIMITER ;

SET @qty = get_qoh_ftn("KYOTOCBOOK");
SELECT @qty;