MySQL Proceduri & View-uri

📚 Introducere

Procedurile stocate și view-urile sunt componente esențiale ale programării SQL avansate, permițând organizarea logicii de business direct în baza de date.

⚙️ Stored Procedures

Cod SQL reutilizabil cu logică complexă
Parametri de intrare și ieșire
Control flow (IF, LOOP, WHILE)
Pot modifica date

👁️ Views

Tabele virtuale bazate pe query-uri
Simplifică query-uri complexe
Securitate - ascunde coloane sensibile
Accesate ca tabele normale

STORED PROCEDURES

⚙️ Proceduri Stocate

O procedură stocată este un set de instrucțiuni SQL salvate în baza de date și executate ca o singură unitate.

Prima Procedură

-- Schimbă delimitatorul temporar
DELIMITER //

CREATE PROCEDURE GetAllUsers()
BEGIN
    SELECT id, username, email, created_at
    FROM users
    WHERE status = 'active'
    ORDER BY created_at DESC;
END //

DELIMITER ;

-- Apelează procedura
CALL GetAllUsers();

💡 De ce DELIMITER?

MySQL folosește ; pentru a termina instrucțiunile. În proceduri avem mai multe ;, așa că schimbăm temporar delimitatorul la //.

STORED PROCEDURES

📥 Parametri și Variabile

Tip	Descriere	Folosire
`IN`	Parametru de intrare (default)	Primește valori de la apelant
`OUT`	Parametru de ieșire	Returnează valori către apelant
`INOUT`	Ambele direcții	Primește și returnează valori

Exemplu IN, OUT

DELIMITER //

CREATE PROCEDURE GetUserStats(
    IN  p_user_id    BIGINT,
    OUT p_total      DECIMAL(10,2),
    OUT p_count      INT
)
BEGIN
    SELECT 
        SUM(total_amount),
        COUNT(*)
    INTO p_total, p_count
    FROM orders
    WHERE user_id = p_user_id
    AND status = 'completed';
END //

DELIMITER ;

-- Apelare cu parametri OUT
SET @total = 0;
SET @count = 0;
CALL GetUserStats(1, @total, @count);
SELECT @total, @count;

STORED PROCEDURES

🔀 Control Flow

IF...THEN...ELSE

Condiții IF

DELIMITER //

CREATE PROCEDURE GetCustomerLevel(
    IN  p_user_id BIGINT,
    OUT p_level   VARCHAR(20)
)
BEGIN
    DECLARE v_total DECIMAL(12,2);
    
    SELECT COALESCE(SUM(total_amount), 0)
    INTO v_total
    FROM orders
    WHERE user_id = p_user_id;
    
    IF v_total >= 10000 THEN
        SET p_level = 'PLATINUM';
    ELSEIF v_total >= 5000 THEN
        SET p_level = 'GOLD';
    ELSEIF v_total >= 1000 THEN
        SET p_level = 'SILVER';
    ELSE
        SET p_level = 'BRONZE';
    END IF;
END //

DELIMITER ;

Bucle: WHILE

WHILE Loop

DELIMITER //

CREATE PROCEDURE GenerateTestData(IN p_count INT)
BEGIN
    DECLARE v_i INT DEFAULT 1;
    
    WHILE v_i <= p_count DO
        INSERT INTO test_data (name, value)
        VALUES (CONCAT('Item_', v_i), RAND() * 100);
        SET v_i = v_i + 1;
    END WHILE;
END //

DELIMITER ;

Error Handling

DECLARE HANDLER

DELIMITER //

CREATE PROCEDURE SafeTransfer(
    IN  p_from   BIGINT,
    IN  p_to     BIGINT,
    IN  p_amount DECIMAL(10,2),
    OUT p_status VARCHAR(50)
)
BEGIN
    DECLARE EXIT HANDLER FOR SQLEXCEPTION
    BEGIN
        ROLLBACK;
        SET p_status = 'ERROR: Transaction failed';
    END;
    
    START TRANSACTION;
    
    UPDATE accounts SET balance = balance - p_amount
    WHERE id = p_from;
    
    UPDATE accounts SET balance = balance + p_amount
    WHERE id = p_to;
    
    COMMIT;
    SET p_status = 'SUCCESS';
END //

DELIMITER ;

FUNCTIONS

🔧 Funcții Stocate

Funcțiile returnează o singură valoare și pot fi folosite direct în query-uri SQL.

Funcție

DELIMITER //

CREATE FUNCTION CalculateAge(p_birthdate DATE)
RETURNS INT
DETERMINISTIC
BEGIN
    RETURN TIMESTAMPDIFF(YEAR, p_birthdate, CURDATE());
END //

DELIMITER ;

-- Folosire în query
SELECT name, CalculateAge(birthdate) AS age
FROM users;

💡 Caracteristici Funcții

DETERMINISTIC - același rezultat pentru aceleași argumente
READS SQL DATA - citește date din tabele
Pot fi folosite în SELECT, WHERE, ORDER BY

VIEWS

👁️ View-uri

Un VIEW este un tabel virtual bazat pe rezultatul unui query SQL.

View Simplu

-- View pentru utilizatori activi
CREATE VIEW v_active_users AS
SELECT id, username, email, created_at
FROM users
WHERE status = 'active'
AND deleted_at IS NULL;

-- Folosire ca un tabel normal
SELECT * FROM v_active_users;
SELECT * FROM v_active_users WHERE created_at > '2024-01-01';

View cu JOIN-uri

View Complex

CREATE VIEW v_order_details AS
SELECT 
    o.id AS order_id,
    o.created_at AS order_date,
    u.username,
    p.name AS product_name,
    o.quantity,
    o.total_amount,
    o.status
FROM orders o
JOIN users u ON o.user_id = u.id
JOIN products p ON o.product_id = p.id;

-- Query-uri complexe devin simple
SELECT * FROM v_order_details
WHERE username = 'john_doe';

View pentru Securitate

Ascunde Date Sensibile

CREATE VIEW v_users_public AS
SELECT 
    id, username,
    CONCAT(LEFT(email, 3), '***@***') AS masked_email,
    created_at
FROM users
WHERE is_public = TRUE;

-- Acordă acces doar la view
GRANT SELECT ON mydb.v_users_public TO 'readonly'@'%';

Administrare View-uri

Administrare

-- Listează view-urile
SHOW FULL TABLES WHERE Table_type = 'VIEW';

-- Vezi definiția
SHOW CREATE VIEW v_order_details;

-- Modifică
CREATE OR REPLACE VIEW v_active_users AS
SELECT id, username, email FROM users;

-- Șterge
DROP VIEW IF EXISTS v_active_users;

VIEWS

✏️ Updatable Views

Anumite view-uri pot fi modificate direct cu INSERT, UPDATE, DELETE.

✅ Condiții pentru Updatable Views

✓

Bazat pe un singur tabel

✓

Fără funcții de agregare (SUM, COUNT)

✓

Fără DISTINCT, GROUP BY, HAVING, UNION

✓

Fără subquery-uri în SELECT

WITH CHECK OPTION

CREATE VIEW v_active_products AS
SELECT id, name, price, status
FROM products
WHERE status = 'active'
WITH CHECK OPTION;

-- ✅ Funcționează
UPDATE v_active_products SET price = 29.99 WHERE id = 1;

-- ❌ Eroare - ar face produsul invizibil
UPDATE v_active_products SET status = 'inactive' WHERE id = 1;
-- ERROR: CHECK OPTION failed

TRIGGERS

⚡ Triggers

Un trigger este cod care se execută automat înainte sau după INSERT, UPDATE, DELETE.

Audit Trigger

DELIMITER //

CREATE TRIGGER tr_orders_after_insert
AFTER INSERT ON orders
FOR EACH ROW
BEGIN
    INSERT INTO audit_log (table_name, action, record_id, new_values)
    VALUES (
        'orders',
        'INSERT',
        NEW.id,
        JSON_OBJECT('user_id', NEW.user_id, 'total', NEW.total_amount)
    );
END //

DELIMITER ;

Trigger pentru Validare

BEFORE INSERT

DELIMITER //

CREATE TRIGGER tr_orders_before_insert
BEFORE INSERT ON orders
FOR EACH ROW
BEGIN
    -- Validează cantitatea
    IF NEW.quantity <= 0 THEN
        SIGNAL SQLSTATE '45000'
        SET MESSAGE_TEXT = 'Quantity must be > 0';
    END IF;
    
    -- Setează status default
    IF NEW.status IS NULL THEN
        SET NEW.status = 'pending';
    END IF;
END //

DELIMITER ;

⚠️ Atenție la Triggere

Încetinesc operațiile INSERT/UPDATE/DELETE
Sunt invizibile - debugging dificil
Pot cauza cascade neașteptate

✨ Best Practices

✅ Proceduri

✓

Prefixe consistente: sp_ proceduri, fn_ funcții

✓

Comentarii pentru documentație

✓

Tranzacții pentru operații multiple

✓

Error handling cu DECLARE HANDLER

✗

Evită cursori când poți folosi operații pe seturi

✅ View-uri

✓

Prefix v_ pentru identificare

✓

Pentru query-uri folosite frecvent

✓

Pentru securitate - ascunde date sensibile

✓

WITH CHECK OPTION pentru updatable views

✗

Evită view-uri pe view-uri (nested)

📚 Resurse