Skip to content

Instantly share code, notes, and snippets.

@c7h c7h/gist:5943777
Last active Dec 19, 2015

Embed
What would you like to do?
different usage of SQL's stored functions and stored procedures
-- schema for exchange:
CREATE TABLE exchange(cur VARCHAR(3), rate DECIMAL(12,3));
INSERT INTO exchange VALUES ("eur", 1.00), ("usd", 1.28), ("czk", 25.98);
-- stored procedure
DELIMITER :-)
CREATE PROCEDURE exrate(IN cur_in DECIMAL(12,3), IN currency VARCHAR(3), OUT cur_out DECIMAL(12,3))
BEGIN
DECLARE course DECIMAL(12,3) DEFAULT 1;
SET course = (SELECT rate FROM exchange WHERE cur = currency);
SET cur_out = cur_in * course;
END:-)
DELIMITER ;
-- usage:
SET @FOO = 0;
CALL exrate(2.30, "usd", @FOO);
SELECT @FOO;
--################
-- stored function
DELIMITER :-)
CREATE FUNCTION exrate2(cur_in DECIMAL(12,3), currency VARCHAR(3)) RETURNS DECIMAL(12,3)
BEGIN
RETURN (SELECT rate FROM exchange WHERE cur = currency) * cur_in;
END:-)
DELIMITER ;
-- usage:
SELECT exrate(2.30, "usd");
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
You can’t perform that action at this time.