Practical 9.sql

USE collgePr1;













CREATE TABLE IF NOT EXISTS STUDENT_24012011080 (
    sr_no VARCHAR(20),
    name VARCHAR(50),
    address VARCHAR(100)
);

INSERT INTO STUDENT_24012011080 VALUES 
('10IT48', 'Rakesh', 'Surat'),
('10IT49', 'Dev', 'Vadodara');
SELECT * FROM STUDENT_24012011080;


DELIMITER $$
CREATE PROCEDURE get_student_10IT48()
BEGIN
    DECLARE v_sr VARCHAR(20);
    DECLARE v_name VARCHAR(50);
    DECLARE v_addr VARCHAR(100);
    DECLARE no_data INT DEFAULT 0;

    -- Exception Handler for "NOT FOUND"
    DECLARE CONTINUE HANDLER FOR NOT FOUND 
    SET no_data = 1;

    -- Try retrieving record
    SELECT sr_no, name, address
    INTO v_sr, v_name, v_addr
    FROM STUDENT_24012011080
    WHERE sr_no = '10IT48';

    -- If no row found
    IF no_data = 1 THEN
        SELECT 'data not found' AS Message;
    ELSE
        -- If row exists
        SELECT v_sr AS SR_No, v_name AS Name, v_addr AS Address;
    END IF;
END$$
DELIMITER ;


CALL get_student_10IT48();



DELIMITER $$
CREATE PROCEDURE demo_too_many_rows()
BEGIN
    DECLARE v_name VARCHAR(50);
    DECLARE msg TEXT;

    -- Handler for “too many rows” (SQLSTATE 21000)
    DECLARE CONTINUE HANDLER FOR SQLSTATE '21000'
    BEGIN
        SET msg = 'Error: TOO MANY ROWS returned. Please refine WHERE condition.';
        SELECT msg AS Message;
    END;

    -- This query must return MULTIPLE rows to trigger the exception
    SELECT name INTO v_name
    FROM STUDENT_24012011080
    WHERE address = 'Surat';

    -- If no error
    SELECT v_name AS Result;

END$$
DELIMITER ;

INSERT INTO STUDENT_24012011080 VALUES
('10IT50', 'Yash', 'Surat'),
('10IT51', 'Niraj', 'Surat');
CALL demo_too_many_rows();



DROP TABLE IF EXISTS emp_mas_24012011080;


CREATE TABLE emp_mas_24012011080(
    emp_no INT PRIMARY KEY,
    name VARCHAR(50)
);
INSERT INTO emp_mas_24012011080 VALUES(101, 'Rakesh');


DELIMITER $$
CREATE PROCEDURE demo_pk_violation1()
BEGIN
    DECLARE msg TEXT DEFAULT '';

    DECLARE EXIT HANDLER FOR SQLSTATE '23000'
    BEGIN
        SELECT 'Error: Primary key constraint violated (Duplicate emp_no).' AS Message;
    END;

    -- Try inserting duplicate primary key
    INSERT INTO emp_mas_24012011080 VALUES(101, 'Duplicate');

    -- This line will run ONLY if no error occurs
    SELECT 'Record Inserted Successfully' AS Status;

END$$
DELIMITER ;

CALL demo_pk_violation1();



DELIMITER $$
CREATE PROCEDURE insert_student_with_check(
    IN p_sr_no INT,
    IN p_name VARCHAR(50),
    IN p_address VARCHAR(100)
)
BEGIN
    -- Check for invalid input
    IF p_sr_no < 0 THEN
        SIGNAL SQLSTATE '45000'
        SET MESSAGE_TEXT = 'User-Defined Exception: sr_no cannot be negative';
    END IF;

    -- If no exception, insert record
    INSERT INTO STUDENT_24012011080(sr_no, name, address)
    VALUES(p_sr_no, p_name, p_address);

    SELECT 'Record inserted successfully' AS Message;
END$$
DELIMITER ;

CALL insert_student_with_check(-5,'Ravi','Surat');

CALL insert_student_with_check(105,'Amit','Baroda');