Advertisement
Not a member of Pastebin yet?
Sign Up,
it unlocks many cool features!
- 1)
- DROP PROCEDURE IF EXISTS insert_category;
- DELIMITER //
- CREATE PROCEDURE insert_category
- (
- var_category_id INT,
- var_category_name varchar(50)
- )
- BEGIN
- DECLARE sql_error TINYINT DEFAULT FALSE;
- DECLARE CONTINUE HANDLER FOR SQLEXCEPTION
- SET sql_error = TRUE;
- START TRANSACTION;
- UPDATE categories
- SET
- category_id = var_category_id
- WHERE
- category_name = var_category_name;
- IF sql_error = FALSE THEN
- COMMIT;
- ELSE
- ROLLBACK;
- END IF;
- END//
- DELIMITER ;
- -- Test fail:
- call insert_category (3,'Basses');
- -- Test pass:
- call insert_category (4,'Piano');
- ====================================================================
- 2)
- DROP FUNCTION IF EXISTS discount_price;
- DELIMITER //
- CREATE FUNCTION discount_price
- (
- item_id_param INT
- )
- RETURNS DECIMAL(9,2)
- BEGIN
- DECLARE discount_price_var DECIMAL(9,2);
- SELECT item_price-discount_amount
- INTO discount_price_var
- FROM order_items
- WHERE item_id= item_id_param;
- RETURN discount_price_var;
- END//
- DELIMITER ;
- -- Test pass:
- SELECT
- DISCOUNT_PRICE(item_id) AS discount_price
- FROM
- order_items
- WHERE
- item_id = 3;
- ========================================================================
Advertisement
Add Comment
Please, Sign In to add comment
Advertisement