Database View Fundamentals

Views provide a virtual table interface based on the result of an SQL query. They simplify complex operations and enhance data security.

Basic view operations:

-- Create or replace a view
CREATE [OR REPLACE] VIEW view_name [(column_list)] 
AS SELECT_statement [ WITH [ CASCADED | LOCAL ] CHECK OPTION ];

-- Display view creation statement
SHOW CREATE VIEW view_name;

-- Query view data
SELECT * FROM view_name;

-- Modify view using CREATE OR REPLACE
CREATE [OR REPLACE] VIEW view_name [(column_list)] 
AS SELECT_statement [ WITH [ CASCADED | LOCAL ] CHECK OPTION ];

-- Modify view using ALTER
ALTER VIEW view_name [(column_list)] 
AS SELECT_statement [ WITH [ CASCADED | LOCAL ] CHECK OPTION ];

-- Remove view
DROP VIEW [IF EXISTS] view_name [, view_name] ...;

View Constraint Validasion

The WITH CHECK OPTION clause ensures data modifications comply with view definitions. Two validation scopes exist:

• CASCADED: Validates all underlying views and base tables
• LOCAL: Validates only the current view constraints

View Updatability Rules

Views are updatable only when a one-to-one relationship exists between view rows and base table rows. Non-updatable views contain:

• Aggregate functions (SUM, COUNT, etc.)
• DISTINCT keyword
• GROUP BY or HAVING clauses
• UNION operations

Example of non-updatable view:

CREATE VIEW student_count AS SELECT COUNT(*) FROM students;
-- INSERT INTO student_count VALUES(10); -- This will fail

Benefits of Using Views

Views offer several advantages:

• Simplification of complex queries
• Enhanced data security through restricted access
• Logical data independence from structural changes

Stored Procedure Overview

Stored procedures are precompiled SQL code blocks stored in the database. They accept parameters, execute business logic, and can return results.

Stored Procedure Operations

-- Create procedure
DELIMITER $$
CREATE PROCEDURE procedure_name ([parameter_list])
BEGIN
    -- SQL statements
END$$
DELIMITER ;

-- Execute procedure
CALL procedure_name([parameters]);

-- List procedures in database
SELECT * FROM INFORMATION_SCHEMA.ROUTINES 
WHERE ROUTINE_SCHEMA = 'database_name';

-- Show procedure definition
SHOW CREATE PROCEDURE procedure_name;

-- Delete procedure
DROP PROCEDURE [IF EXISTS] procedure_name;

-- Example procedure
DELIMITER $$
CREATE PROCEDURE count_students()
BEGIN
    SELECT COUNT(*) FROM students;
END$$
DELIMITER ;

CALL count_students();
DROP PROCEDURE IF EXISTS count_students;

Variable Types in Stored Procedures

System Variables

-- Display variables
SHOW [SESSION | GLOBAL] VARIABLES;
SHOW [SESSION | GLOBAL] VARIABLES LIKE 'pattern';
SELECT @@[SESSION | GLOBAL] variable_name;

-- Set variables
SET [SESSION | GLOBAL] variable_name = value;
SET @@[SESSION | GLOBAL] variable_name = value;

User-Defined Variables

-- Assign values
SET @variable_name = expression;
SET @variable_name := expression;

-- Retrieve values
SELECT @variable_name;

Local Variables

-- Declare variables
DECLARE variable_name data_type [DEFAULT value];

-- Assign values
SET variable_name = value;
SET variable_name := value;
SELECT column_name INTO variable_name FROM table_name;

Conditional Logic with IF Statements

IF condition1 THEN
    -- Statements for condition1
ELSEIF condition2 THEN
    -- Statements for condition2
ELSE
    -- Default statements
END IF;

-- Grade classification example
DELIMITER $$
CREATE PROCEDURE evaluate_grade()
BEGIN
    DECLARE score INT DEFAULT 58;
    DECLARE result VARCHAR(10);
    
    IF score >= 85 THEN
        SET result := 'Excellent';
    ELSEIF score >= 60 THEN
        SET result := 'Pass';
    ELSE
        SET result := 'Fail';
    END IF;
    
    SELECT result;
END$$
DELIMITER ;

Procedure Parameters

CREATE PROCEDURE procedure_name (
    [ IN | OUT | INOUT ] parameter_name data_type
)
BEGIN
    -- SQL statements
END;

-- Convert 200-point scale to 100-point scale
DELIMITER $$
CREATE PROCEDURE convert_score(INOUT input_score DOUBLE)
BEGIN
    SET input_score := input_score * 0.5;
END$$
DELIMITER ;

SET @score := 180;
CALL convert_score(@score);
SELECT @score; -- Returns 90

CASE Statement Structures

Two CASE syntax formats:

-- Format 1: Value comparison
CASE expression
    WHEN value1 THEN statements1
    [WHEN value2 THEN statements2] ...
    [ELSE statements]
END CASE;

-- Format 2: Condition evaluation
CASE
    WHEN condition1 THEN statements1
    [WHEN condition2 THEN statements2] ...
    [ELSE statements]
END CASE;

-- Quarter determination example
DELIMITER $$
CREATE PROCEDURE determine_quarter(IN month_num INT)
BEGIN
    DECLARE quarter_desc VARCHAR(10);
    
    CASE
        WHEN month_num IN (1,2,3) THEN
            SET quarter_desc := 'Q1';
        WHEN month_num IN (4,5,6) THEN
            SET quarter_desc := 'Q2';
        WHEN month_num IN (7,8,9) THEN
            SET quarter_desc := 'Q3';
        WHEN month_num IN (10,11,12) THEN
            SET quarter_desc := 'Q4';
    END CASE;
    
    SELECT CONCAT('Month ', month_num, ' belongs to ', quarter_desc);
END$$
DELIMITER ;

Loop Control Structures

WHILE Loop

WHILE condition DO
    -- SQL statements
END WHILE;

-- Sum calculation example
DELIMITER $$
CREATE PROCEDURE calculate_sum(IN limit_value INT)
BEGIN
    DECLARE total INT DEFAULT 0;
    
    WHILE limit_value > 0 DO
        SET total := total + limit_value;
        SET limit_value := limit_value - 1;
    END WHILE;
    
    SELECT CONCAT('Sum: ', total);
END$$
DELIMITER ;

REPEAT Loop

REPEAT
    -- SQL statements
UNTIL condition
END REPEAT;

-- Sum calculation with REPEAT
DELIMITER $$
CREATE PROCEDURE sum_with_repeat(IN limit_value INT)
BEGIN
    DECLARE total INT DEFAULT 0;
    
    REPEAT
        SET total := total + limit_value;
        SET limit_value := limit_value - 1;
    UNTIL limit_value = 0
    END REPEAT;
    
    SELECT CONCAT('Sum: ', total);
END$$
DELIMITER ;

LOOP with Control Statements

[label:] LOOP
    -- SQL statements
END LOOP [label];

-- LEAVE: Exit loop
-- ITERATE: Skip remaining statements and continue loop

-- Even number sum example
DELIMITER $$
CREATE PROCEDURE sum_even_numbers(IN limit_value INT)
BEGIN
    DECLARE total INT DEFAULT 0;
    
    number_loop: LOOP
        IF limit_value = 0 THEN
            LEAVE number_loop;
        END IF;
        
        IF limit_value % 2 = 1 THEN
            SET limit_value := limit_value - 1;
            ITERATE number_loop;
        END IF;
        
        SET total := total + limit_value;
        SET limit_value := limit_value - 1;
    END LOOP number_loop;
    
    SELECT CONCAT('Even sum: ', total);
END$$
DELIMITER ;

Cursor Implementation

Cursors enable row-by-row processing of query results:

-- Declare cursor
DECLARE cursor_name CURSOR FOR SELECT_statement;

-- Open cursor
OPEN cursor_name;

-- Fetch records
FETCH cursor_name INTO variable [, variable] ...;

-- Close cursor
CLOSE cursor_name;

Exception Handling

DECLARE action HANDLER FOR condition_list statement;

-- Action types:
-- CONTINUE: Continue execution
-- EXIT: Terminate execution

-- Condition types:
-- SQLSTATE 'code': Specific error codes
-- SQLWARNING: Warnings (01xxx codes)
-- NOT FOUND: Missing data (02xxx codes)
-- SQLEXCEPTION: General exceptions

-- Data migration example with cursor
DELIMITER $$
CREATE PROCEDURE migrate_users(IN max_age INT)
BEGIN
    DECLARE user_name VARCHAR(100);
    DECLARE user_profession VARCHAR(100);
    DECLARE done INT DEFAULT FALSE;
    
    DECLARE user_cursor CURSOR FOR
        SELECT name, profession FROM users WHERE age <= max_age;
        
    DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE;
    
    DROP TABLE IF EXISTS new_users;
    CREATE TABLE new_users (
        id INT PRIMARY KEY AUTO_INCREMENT,
        name VARCHAR(100),
        profession VARCHAR(100)
    );
    
    OPEN user_cursor;
    
    read_loop: LOOP
        FETCH user_cursor INTO user_name, user_profession;
        IF done THEN
            LEAVE read_loop;
        END IF;
        INSERT INTO new_users VALUES(NULL, user_name, user_profession);
    END LOOP;
    
    CLOSE user_cursor;
END$$
DELIMITER ;

Stored Functions

Functions return single values and can be used in SQL expressions:

CREATE FUNCTION function_name ([parameter_list])
RETURNS data_type [characteristics]
BEGIN
    -- SQL statements
    RETURN value;
END;

-- Sum calculation function
DELIMITER $$
CREATE FUNCTION calculate_total(limit_value INT)
RETURNS INT DETERMINISTIC
BEGIN
    DECLARE total INT DEFAULT 0;
    
    WHILE limit_value > 0 DO
        SET total := total + limit_value;
        SET limit_value := limit_value - 1;
    END WHILE;
    
    RETURN total;
END$$
DELIMITER ;

SELECT calculate_total(100); -- Returns 5050

Trigger Implementation

Triggers automatically execute in response to table events:

-- Create trigger
CREATE TRIGGER trigger_name
BEFORE | AFTER INSERT | UPDATE | DELETE
ON table_name FOR EACH ROW
BEGIN
    -- Trigger statements
END;

-- Display triggers
SHOW TRIGGERS;

-- Remove trigger
DROP TRIGGER [schema_name.]trigger_name;