March 28, 2012 | In: Programovanie
Vytvorenie stored procedury v oracli
ORACLE verzia 11g – vytvorenie storovanej procedury
parametre zadame bez ziadnych dlzok atd. Urcie nie VARCHAR(20), NUMBER(10) atd zle
parametre mozeme este rozlisit ci ide vstupny IN, vystupny OUT a este na vstupno/vystupny IN OUT
CREATE OR REPLACE PROCEDURE PREVOD (odkial NUMBER, kam NUMBER,
suma NUMBER, mena NUMBER, var_s NUMBER, konst_s NUMBER) AS
// deklaracia lokalnych premennych bez klucoveho slova DECLARE
POCET_DOBRYCH_UCTOV NUMBER;
BEGIN
//telo procedurySELECT COUNT(*) INTO POCET_DOBRYCH_UCTOV
FROM account WHERE (numbr = odkial
AND balance >= suma)
OR (numbr = kam);IF POCET_DOBRYCH_UCTOV < 2 THEN
ROLLBACK;
ELSE
UPDATE account SET
balance = balance + suma
WHERE numbr = kam;UPDATE account SET
balance = balance - suma
WHERE numbr = odkial;INSERT INTO transactin (typ_pladby,counter_part,amt,currency,
variable_symbol,const_symbol,specifics,
datum,description,account_id) VALUES
(0,kam,suma,mena,var_s,konst_s,0,SYSDATE,'aaa',1);INSERT INTO transactin (typ_pladby,counter_part,amt,currency,
variable_symbol,const_symbol,specifics,
datum,description,account_id) VALUES
(1,odkial,suma,mena,var_s,konst_s,0,SYSDATE,'bbb',2);
COMMIT;
END IF;
NULL;
END;
