Run Code
|
API
|
Code Wall
|
Misc
|
Feedback
|
Login
|
Theme
|
Privacy
|
Patreon
funkcia_transakcie
--PostgreSQL 9.5 --'\\' is a delimiter CREATE TABLE ucty ( majitel_uctu TEXT, zostatok NUMERIC, ciastka NUMERIC ); INSERT INTO ucty VALUES ('Rob', 100); INSERT INTO ucty VALUES ('Maria', 200); UPDATE ucty SET zostatok = zostatok - 14.00 WHERE majitel_uctu = 'Rob'; UPDATE ucty SET zostatok = zostatok + 14.00 WHERE majitel_uctu = 'Maria'; CREATE OR REPLACE FUNCTION transakcia( i_platca TEXT, i_prijemca TEXT, -- Numeric(15,2) znamena ze max 15 cisel a 2 decimalne i_ciastka NUMERIC(15, 2)) RETURNS TEXT AS $$ DECLARE platca_zostatok NUMERIC; BEGIN SELECT zostatok INTO platca_zostatok FROM ucty WHERE majitel_uctu = i_platca FOR UPDATE; IF NOT FOUND THEN RETURN 'Ucet platcu nenajdeny'; END IF; IF platca_zostatok < i_ciastka THEN RETURN 'Nedostatok prostriedkov'; END IF; UPDATE ucty SET zostatok = zostatok + i_ciastka WHERE majitel_uctu = i_prijemca; IF NOT FOUND THEN RETURN 'Prijemca neexistuje'; END IF; UPDATE ucty SET zostatok = zostatok - i_ciastka WHERE majitel_uctu = i_platca; RETURN 'OK'; END; $$ LANGUAGE plpgsql; SELECT * FROM ucty; SELECT transakcia('Rob','Maria',14.00); SELECT * FROM ucty; SELECT * FROM transakcia('Fred','Maria',14.00); SELECT * FROM transakcia('Rob','Fred',14.00); SELECT * FROM transakcia('Rob','Maria',500.00);
run
|
edit
|
history
|
help
0
Movie Rating SQL Analysis Project
2
Black Wall
SQL Practice Sample: Movie Database
SQL Practice Sample: Movie Database
Query integer in text field - Seemingly doesn't work as expected
UNIX_TIMESTAMP
SQL social network practice by Han Wang 20200720 - 2
FlujoPadre
prueb final