stránka pro testování přístupu k externím stránkám

---

```
-- Vytvoření tabulek
CREATE TABLE clients (
    client_id NUMBER GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    name VARCHAR2(100) NOT NULL,
    email VARCHAR2(100) UNIQUE NOT NULL,
    created_at DATE DEFAULT SYSDATE,
    status VARCHAR2(10) DEFAULT 'ACTIVE' CHECK (status IN ('ACTIVE', 'BLOCKED'))
);

CREATE TABLE accounts (
    account_id NUMBER GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    client_id NUMBER NOT NULL,
    currency VARCHAR2(3) DEFAULT 'CZK' NOT NULL,
    balance NUMBER(18,2) DEFAULT 0 NOT NULL,
    created_at DATE DEFAULT SYSDATE,
    closed_at DATE,
    CONSTRAINT fk_client_id FOREIGN KEY (client_id) REFERENCES clients(client_id)
);

CREATE TABLE transactions (
    tx_id NUMBER GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    account_id NUMBER NOT NULL,
    tx_type VARCHAR2(10) NOT NULL CHECK (tx_type IN ('DEPOSIT', 'WITHDRAW', 'TRANSFER')),
    amount NUMBER(18,2) NOT NULL,
    tx_timestamp DATE DEFAULT SYSDATE,
    reference VARCHAR2(50),
    related_tx_id NUMBER,
    CONSTRAINT fk_account_id FOREIGN KEY (account_id) REFERENCES accounts(account_id),
    CONSTRAINT fk_related_tx_id FOREIGN KEY (related_tx_id) REFERENCES transactions(tx_id)
);

CREATE TABLE account_balance_history (
    history_id NUMBER GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    account_id NUMBER NOT NULL,
    balance_before NUMBER(18,2),
    balance_after NUMBER(18,2),
    tx_id NUMBER NOT NULL,
    change_timestamp DATE DEFAULT SYSDATE,
    CONSTRAINT fk_history_account_id FOREIGN KEY (account_id) REFERENCES accounts(account_id),
    CONSTRAINT fk_history_tx_id FOREIGN KEY (tx_id) REFERENCES transactions(tx_id)
);

CREATE TABLE transaction_audit_log (
    log_id NUMBER GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    tx_id NUMBER NOT NULL,
    operation_type VARCHAR2(20) NOT NULL,
    operation_timestamp DATE DEFAULT SYSDATE,
    user_info VARCHAR2(100),
    details CLOB,
    CONSTRAINT fk_audit_tx_id FOREIGN KEY (tx_id) REFERENCES transactions(tx_id)
);

CREATE TABLE error_log (
    error_id NUMBER GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    error_timestamp DATE DEFAULT SYSDATE,
    error_code NUMBER,
    error_message VARCHAR2(4000),
    error_stack VARCHAR2(4000),
    module_name VARCHAR2(100),
    procedure_name VARCHAR2(100)
);

-- Vytvoření indexů
CREATE INDEX i_accounts_client_id ON accounts(client_id) TABLESPACE index_data;
CREATE INDEX i_transactions_account_id ON transactions(account_id) TABLESPACE index_data;
CREATE INDEX i_transactions_timestamp ON transactions(tx_timestamp) TABLESPACE index_data;
CREATE INDEX i_transactions_type ON transactions(tx_type) TABLESPACE index_data;
CREATE INDEX i_accounts_currency ON accounts(currency) TABLESPACE index_data;
CREATE INDEX i_accounts_created_at ON accounts(created_at) TABLESPACE index_data;

-- Balíček validací
CREATE OR REPLACE PACKAGE pkg_validation AS
    -- Vlastní výjimky
    invalid_account EXCEPTION;
    insufficient_funds EXCEPTION;
    invalid_amount EXCEPTION;
    account_closed EXCEPTION;
    
    -- Funkce pro validaci
    FUNCTION validate_account(p_account_id NUMBER) RETURN BOOLEAN;
    FUNCTION validate_amount(p_amount NUMBER) RETURN BOOLEAN;
    FUNCTION validate_account_status(p_account_id NUMBER) RETURN BOOLEAN;
    FUNCTION validate_transfer_amount(p_account_id NUMBER, p_amount NUMBER) RETURN BOOLEAN;
END pkg_validation;
/

CREATE OR REPLACE PACKAGE BODY pkg_validation AS
    FUNCTION validate_account(p_account_id NUMBER) RETURN BOOLEAN IS
        l_count NUMBER;
    BEGIN
        SELECT COUNT(*) INTO l_count FROM accounts WHERE account_id = p_account_id;
        RETURN l_count > 0;
    EXCEPTION
        WHEN OTHERS THEN
            RAISE_APPLICATION_ERROR(-20001, 'Chyba při validaci účtu: ' || SQLERRM);
    END validate_account;
    
    FUNCTION validate_amount(p_amount NUMBER) RETURN BOOLEAN IS
    BEGIN
        RETURN p_amount > 0;
    EXCEPTION
        WHEN OTHERS THEN
            RAISE_APPLICATION_ERROR(-20002, 'Chyba při validaci částky: ' || SQLERRM);
    END validate_amount;
    
    FUNCTION validate_account_status(p_account_id NUMBER) RETURN BOOLEAN IS
        l_status VARCHAR2(10);
    BEGIN
        SELECT status INTO l_status FROM accounts WHERE account_id = p_account_id;
        RETURN l_status = 'ACTIVE';
    EXCEPTION
        WHEN NO_DATA_FOUND THEN
            RAISE invalid_account;
        WHEN TOO_MANY_ROWS THEN
            RAISE_APPLICATION_ERROR(-20003, 'Nalezeno více účtů');
        WHEN OTHERS THEN
            RAISE_APPLICATION_ERROR(-20004, 'Chyba při validaci stavu účtu: ' || SQLERRM);
    END validate_account_status;
    
    FUNCTION validate_transfer_amount(p_account_id NUMBER, p_amount NUMBER) RETURN BOOLEAN IS
        l_balance NUMBER;
    BEGIN
        SELECT balance INTO l_balance FROM accounts WHERE account_id = p_account_id;
        RETURN l_balance >= p_amount;
    EXCEPTION
        WHEN NO_DATA_FOUND THEN
            RAISE invalid_account;
        WHEN TOO_MANY_ROWS THEN
            RAISE_APPLICATION_ERROR(-20005, 'Nalezeno více účtů');
        WHEN OTHERS THEN
            RAISE_APPLICATION_ERROR(-20006, 'Chyba při validaci částky pro převod: ' || SQLERRM);
    END validate_transfer_amount;
END pkg_validation;
/

-- Balíček operací s účty
CREATE OR REPLACE PACKAGE pkg_account_ops AS
    -- Vlastní výjimky
    account_not_found EXCEPTION;
    insufficient_balance EXCEPTION;
    invalid_transaction_type EXCEPTION;
    
    -- Procedury
    PROCEDURE create_account(p_client_id NUMBER, p_currency VARCHAR2, p_account_id OUT NUMBER);
    PROCEDURE deposit(p_account_id NUMBER, p_amount NUMBER, p_reference VARCHAR2 DEFAULT NULL);
    PROCEDURE withdraw(p_account_id NUMBER, p_amount NUMBER, p_reference VARCHAR2 DEFAULT NULL);
    PROCEDURE transfer(p_from_account NUMBER, p_to_account NUMBER, p_amount NUMBER, p_reference VARCHAR2 DEFAULT NULL);
END pkg_account_ops;
/

CREATE OR REPLACE PACKAGE BODY pkg_account_ops AS
    PROCEDURE create_account(p_client_id NUMBER, p_currency VARCHAR2, p_account_id OUT NUMBER) IS
        l_client_exists NUMBER;
    BEGIN
        -- Validace klienta
        SELECT COUNT(*) INTO l_client_exists FROM clients WHERE client_id = p_client_id;
        IF l_client_exists = 0 THEN
            RAISE_APPLICATION_ERROR(-20007, 'Klient s ID ' || p_client_id || ' neexistuje');
        END IF;
        
        -- Vytvoření účtu
        INSERT INTO accounts (client_id, currency)
        VALUES (p_client_id, p_currency)
        RETURNING account_id INTO p_account_id;
        
        -- Zápis do auditního logu
        INSERT INTO transaction_audit_log (tx_id, operation_type, user_info, details)
        VALUES (NULL, 'CREATE_ACCOUNT', USER, 'Vytvořen účet pro klienta ' || p_client_id);
        
        COMMIT;
        
    EXCEPTION
        WHEN OTHERS THEN
            ROLLBACK;
            INSERT INTO error_log (error_code, error_message, error_stack, module_name, procedure_name)
            VALUES (SQLCODE, SQLERRM, DBMS_UTILITY.FORMAT_ERROR_BACKTRACE, 'pkg_account_ops', 'create_account');
            RAISE;
    END create_account;
    
    PROCEDURE deposit(p_account_id NUMBER, p_amount NUMBER, p_reference VARCHAR2 DEFAULT NULL) IS
        l_balance_before NUMBER;
        l_balance_after NUMBER;
        l_tx_id NUMBER;
    BEGIN
        -- Validace účtu a částky
        IF NOT pkg_validation.validate_account(p_account_id) THEN
            RAISE account_not_found;
        END IF;
        
        IF NOT pkg_validation.validate_amount(p_amount) THEN
            RAISE invalid_transaction_type;
        END IF;
        
        -- Získání aktuálního stavu účtu
        SELECT balance INTO l_balance_before FROM accounts WHERE account_id = p_account_id FOR UPDATE;
        
        -- Výpočet nového stavu
        l_balance_after := l_balance_before + p_amount;
        
        -- Aktualizace účtu
        UPDATE accounts SET balance = l_balance_after WHERE account_id = p_account_id;
        
        -- Vložení transakce
        INSERT INTO transactions (account_id, tx_type, amount, reference)
        VALUES (p_account_id, 'DEPOSIT', p_amount, p_reference)
        RETURNING tx_id INTO l_tx_id;
        
        -- Zápis do historie
        INSERT INTO account_balance_history (account_id, balance_before, balance_after, tx_id)
        VALUES (p_account_id, l_balance_before, l_balance_after, l_tx_id);
        
        -- Zápis do auditního logu
        INSERT INTO transaction_audit_log (tx_id, operation_type, user_info, details)
        VALUES (l_tx_id, 'DEPOSIT', USER, 'Vklad ' || p_amount || ' na účet ' || p_account_id);
        
        COMMIT;
        
    EXCEPTION
        WHEN OTHERS THEN
            ROLLBACK;
            INSERT INTO error_log (error_code, error_message, error_stack, module_name, procedure_name)
            VALUES (SQLCODE, SQLERRM, DBMS_UTILITY.FORMAT_ERROR_BACKTRACE, 'pkg_account_ops', 'deposit');
            RAISE;
    END deposit;
    
    PROCEDURE withdraw(p_account_id NUMBER, p_amount NUMBER, p_reference VARCHAR2 DEFAULT NULL) IS
        l_balance_before NUMBER;
        l_balance_after NUMBER;
        l_tx_id NUMBER;
    BEGIN
        -- Validace účtu a částky
        IF NOT pkg_validation.validate_account(p_account_id) THEN
            RAISE account_not_found;
        END IF;
        
        IF NOT pkg_validation.validate_amount(p_amount) THEN
            RAISE invalid_transaction_type;
        END IF;
        
        -- Získání aktuálního stavu účtu
        SELECT balance INTO l_balance_before FROM accounts WHERE account_id = p_account_id FOR UPDATE;
        
        -- Kontrola dostatečného stavu
        IF l_balance_before < p_amount THEN
            RAISE insufficient_balance;
        END IF;
        
        -- Výpočet nového stavu
        l_balance_after := l_balance_before - p_amount;
        
        -- Aktualizace účtu
        UPDATE accounts SET balance = l_balance_after WHERE account_id = p_account_id;
        
        -- Vložení transakce
        INSERT INTO transactions (account_id, tx_type, amount, reference)
        VALUES (p_account_id, 'WITHDRAW', p_amount, p_reference)
        RETURNING tx_id INTO l_tx_id;
        
        -- Zápis do historie
        INSERT INTO account_balance_history (account_id, balance_before, balance_after, tx_id)
        VALUES (p_account_id, l_balance_before, l_balance_after, l_tx_id);
        
        -- Zápis do auditního logu
        INSERT INTO transaction_audit_log (tx_id, operation_type, user_info, details)
        VALUES (l_tx_id, 'WITHDRAW', USER, 'Výběr ' || p_amount || ' z účtu ' || p_account_id);
        
        COMMIT;
        
    EXCEPTION
        WHEN OTHERS THEN
            ROLLBACK;
            INSERT INTO error_log (error_code, error_message, error_stack, module_name, procedure_name)
            VALUES (SQLCODE, SQLERRM, DBMS_UTILITY.FORMAT_ERROR_BACKTRACE, 'pkg_account_ops', 'withdraw');
            RAISE;
    END withdraw;
    
    PROCEDURE transfer(p_from_account NUMBER, p_to_account NUMBER, p_amount NUMBER, p_reference VARCHAR2 DEFAULT NULL) IS
        l_balance_before NUMBER;
        l_balance_after NUMBER;
        l_tx_id NUMBER;
        l_tx_id2 NUMBER;
    BEGIN
        -- Validace účtů a částky
        IF NOT pkg_validation.validate_account(p_from_account) THEN
            RAISE account_not_found;
        END IF;
        
        IF NOT pkg_validation.validate_account(p_to_account) THEN
            RAISE account_not_found;
        END IF;
        
        IF NOT pkg_validation.validate_amount(p_amount) THEN
            RAISE invalid_transaction_type;
        END IF;
        
        IF NOT pkg_validation.validate_transfer_amount(p_from_account, p_amount) THEN
            RAISE insufficient_balance;
        END IF;
        
        -- Získání aktuálního stavu účtu
        SELECT balance INTO l_balance_before FROM accounts WHERE account_id = p_from_account FOR UPDATE;
        
        -- Výpočet nového stavu
        l_balance_after := l_balance_before - p_amount;
        
        -- Aktualizace účtu
        UPDATE accounts SET balance = l_balance_after WHERE account_id = p_from_account;
        
        -- Vložení transakce (odesílající účet)
        INSERT INTO transactions (account_id, tx_type, amount, reference, related_tx_id)
        VALUES (p_from_account, 'TRANSFER', p_amount, p_reference, NULL)
        RETURNING tx_id INTO l_tx_id;
        
        -- Zápis do historie
        INSERT INTO account_balance_history (account_id, balance_before, balance_after, tx_id)
        VALUES (p_from_account, l_balance_before, l_balance_after, l_tx_id);
        
        -- Získání aktuálního stavu přijímajícího účtu
        SELECT balance INTO l_balance_before FROM accounts WHERE account_id = p_to_account FOR UPDATE;
        
        -- Výpočet nového stavu
        l_balance_after := l_balance_before + p_amount;
        
        -- Aktualizace účtu
        UPDATE accounts SET balance = l_balance_after WHERE account_id = p_to_account;
        
        -- Vložení transakce (přijímající účet)
        INSERT INTO transactions (account_id, tx_type, amount, reference, related_tx_id)
        VALUES (p_to_account, 'TRANSFER', p_amount, p_reference, l_tx_id)
        RETURNING tx_id INTO l_tx_id2;
        
        -- Zápis do historie
        INSERT INTO account_balance_history (account_id, balance_before, balance_after, tx_id)
        VALUES (p_to_account, l_balance_before, l_balance_after, l_tx_id2);
        
        -- Aktualizace vztahu mezi transakcemi
        UPDATE transactions SET related_tx_id = l_tx_id2 WHERE tx_id = l_tx_id;
        
        -- Zápis do auditního logu
        INSERT INTO transaction_audit_log (tx_id, operation_type, user_info, details)
        VALUES (l_tx_id, 'TRANSFER', USER, 'Převod ' || p_amount || ' z účtu ' || p_from_account || ' na účet ' || p_to_account);
        
        COMMIT;
        
    EXCEPTION
        WHEN OTHERS THEN
            ROLLBACK;
            INSERT INTO error_log (error_code, error_message, error_stack, module_name, procedure_name)
            VALUES (SQLCODE, SQLERRM, DBMS_UTILITY.FORMAT_ERROR_BACKTRACE, 'pkg_account_ops', 'transfer');
            RAISE;
    END transfer;
END pkg_account_ops;
/

-- Balíček pro analytické funkce
CREATE OR REPLACE PACKAGE pkg_reporting AS
    -- Funkce pro analýzu
    FUNCTION get_account_balance_history(p_account_id NUMBER) RETURN SYS_REFCURSOR;
    FUNCTION get_account_transactions_summary(p_account_id NUMBER) RETURN SYS_REFCURSOR;
    FUNCTION get_top_accounts_by_balance(p_limit NUMBER DEFAULT 10) RETURN SYS_REFCURSOR;
    FUNCTION get_transaction_volume_by_date(p_days NUMBER DEFAULT 30) RETURN SYS_REFCURSOR;
END pkg_reporting;
/

CREATE OR REPLACE PACKAGE BODY pkg_reporting AS
    FUNCTION get_account_balance_history(p_account_id NUMBER) RETURN SYS_REFCURSOR IS
        l_cursor SYS_REFCURSOR;
    BEGIN
        OPEN l_cursor FOR
            SELECT h.history_id, h.balance_before, h.balance_after, h.change_timestamp, t.tx_type, t.amount, t.reference
            FROM account_balance_history h
            JOIN transactions t ON h.tx_id = t.tx_id
            WHERE h.account_id = p_account_id
            ORDER BY h.change_timestamp DESC;
        RETURN l_cursor;
    END get_account_balance_history;
    
    FUNCTION get_account_transactions_summary(p_account_id NUMBER) RETURN SYS_REFCURSOR IS
        l_cursor SYS_REFCURSOR;
    BEGIN
        OPEN l_cursor FOR
            SELECT tx_type, COUNT(*) as transaction_count, SUM(amount) as total_amount
            FROM transactions
            WHERE account_id = p_account_id
            GROUP BY tx_type
            ORDER BY tx_type;
        RETURN l_cursor;
    END get_account_transactions_summary;
    
    FUNCTION get_top_accounts_by_balance(p_limit NUMBER DEFAULT 10) RETURN SYS_REFCURSOR IS
        l_cursor SYS_REFCURSOR;
    BEGIN
        OPEN l_cursor FOR
            SELECT account_id, balance
            FROM accounts
            ORDER BY balance DESC
            FETCH FIRST p_limit ROWS ONLY;
        RETURN l_cursor;
    END get_top_accounts_by_balance;
    
    FUNCTION get_transaction_volume_by_date(p_days NUMBER DEFAULT 30) RETURN SYS_REFCURSOR IS
        l_cursor SYS_REFCURSOR;
    BEGIN
        OPEN l_cursor FOR
            SELECT TRUNC(t.transaction_date) as transaction_date, 
                   COUNT(*) as transaction_count, 
                   SUM(t.amount) as total_amount
            FROM transactions t
            WHERE t.transaction_date >= SYSDATE - p_days
            GROUP BY TRUNC(t.transaction_date)
            ORDER BY transaction_date;
        RETURN l_cursor;
    END get_transaction_volume_by_date;
END pkg_reporting;
/

-- Generování testovacích dat
CREATE OR REPLACE PROCEDURE generate_test_data IS
    l_account_id NUMBER;
    l_amount NUMBER;
BEGIN
    -- Vytvoření několika testovacích účtů
    FOR i IN 1..100 LOOP
        INSERT INTO accounts (account_id, balance, created_date)
        VALUES (i, ROUND(DBMS_RANDOM.VALUE(100, 10000), 2), SYSDATE);
        
        -- Vytvoření několika transakcí pro každý účet
        FOR j IN 1..ROUND(DBMS_RANDOM.VALUE(5, 20)) LOOP
            l_amount := ROUND(DBMS_RANDOM.VALUE(10, 1000), 2);
            
            IF DBMS_RANDOM.VALUE < 0.5 THEN
                pkg_account_ops.deposit(i, l_amount, 'Test deposit ' || j);
            ELSE
                pkg_account_ops.withdraw(i, l_amount, 'Test withdrawal ' || j);
            END IF;
        END LOOP;
    END LOOP;
    
    COMMIT;
END generate_test_data;
/

-- Spuštění generování testovacích dat
BEGIN
    generate_test_data;
END;
/

-- Výpočet statistik
CREATE OR REPLACE PROCEDURE calculate_statistics IS
BEGIN
    -- Výpočet celkového počtu transakcí
    DBMS_OUTPUT.PUT_LINE('Total transactions: ' || (SELECT COUNT(*) FROM transactions));
    
    -- Výpočet celkového objemu transakcí
    DBMS_OUTPUT.PUT_LINE('Total transaction volume: ' || (SELECT SUM(amount) FROM transactions));
    
    -- Výpočet průměrného stavu účtu
    DBMS_OUTPUT.PUT_LINE('Average account balance: ' || (SELECT AVG(balance) FROM accounts));
    
    -- Výpočet počtu účtů
    DBMS_OUTPUT.PUT_LINE('Total accounts: ' || (SELECT COUNT(*) FROM accounts));
END calculate_statistics;
/

-- Spuštění statistik
BEGIN
    calculate_statistics;
END;
/

-- Zobrazení výsledků
SELECT * FROM accounts WHERE account_id <= 5;
SELECT * FROM transactions WHERE account_id <= 5 ORDER BY transaction_date DESC;
SELECT * FROM account_balance_history WHERE account_id <= 5 ORDER BY change_timestamp DESC;

-- Zobrazení výsledků z analytických funkcí
DECLARE
    l_cursor SYS_REFCURSOR;
BEGIN
    -- Získání historie pro první účet
    l_cursor := pkg_reporting.get_account_balance_history(1);
    DBMS_OUTPUT.PUT_LINE('Account 1 balance history:');
    -- Zde by bylo možné zpracovat cursor
    
    -- Získání shrnutí transakcí pro první účet
    l_cursor := pkg_reporting.get_account_transactions_summary(1);
    DBMS_OUTPUT.PUT_LINE('Account 1 transaction summary:');
    -- Zde by bylo možné zpracovat cursor
    
    -- Získání top účtů
    l_cursor := pkg_reporting.get_top_accounts_by_balance(5);
    DBMS_OUTPUT.PUT_LINE('Top 5 accounts by balance:');
    -- Zde by bylo možné zpracovat cursor
END;
/
```