- %TYPE: Declares a variable with the same data type as a specific column in a table or another variable. Used for single column data.
- %ROWTYPE: Declares a record variable that matches the structure of a table or cursor row, containing all columns.

Key Differences:
- Scope: %TYPE is for a single column’s data type; %ROWTYPE is for an entire row’s structure.
- Usage: %TYPE ensures type consistency for variables; %ROWTYPE simplifies handling multiple columns.

Use Cases:
- %TYPE: Store a single column value (e.g., employee name).
- %ROWTYPE: Process entire rows (e.g., employee record).
Example Sql Server:

-- T-SQL equivalent (no direct %TYPE/%ROWTYPE, use explicit declarations)
DECLARE @EmpName VARCHAR(50); -- Like %TYPE
DECLARE @EmpID INT, @EmpSalary DECIMAL(10,2); -- Like %ROWTYPE subset
SELECT @EmpName = Name, @EmpID = EmployeeID, @EmpSalary = Salary
FROM Employees WHERE EmployeeID = 1;
PRINT @EmpName;

Example MySql:

-- MySQL (no direct equivalent, use stored procedure variables)
DELIMITER //
CREATE PROCEDURE UseTypeRowType()
BEGIN
    DECLARE emp_name VARCHAR(50); -- Like %TYPE
    DECLARE emp_id INT;
    DECLARE emp_salary DECIMAL(10,2); -- Like %ROWTYPE subset
    SELECT Name, EmployeeID, Salary INTO emp_name, emp_id, emp_salary
    FROM Employees WHERE EmployeeID = 1;
    SELECT emp_name;
END //
DELIMITER ;

To optimize a PL/SQL procedure processing millions of rows:
1. Use Bulk Operations: Replace row-by-row processing with FORALL and BULK COLLECT.
2. Optimize Queries: Add indexes on frequently queried columns and analyze query plans.
3. Partition Tables: Split large tables into partitions for faster access.
4. Parallel Execution: Enable parallel DML or use DBMS_PARALLEL_EXECUTE.
5. Minimize Context Switches: Reduce PL/SQL-SQL switches by combining operations.
6. Use Temporary Tables: Store intermediate results for complex transformations.
7. Tune PGA/SGA: Adjust memory settings for large datasets.
8. Profile Performance: Use DBMS_PROFILER or SQL Trace to identify bottlenecks.
9. Limit Commits: Use fewer, larger commits to reduce redo log overhead.
10. Cache Data: Use result caching for frequently accessed data.

Use Case: Optimize a procedure that updates customer records in batches.
Example Sql Server:

-- T-SQL batch processing
CREATE PROCEDURE ProcessLargeData
AS
BEGIN
    SET NOCOUNT ON;
    DECLARE @BatchSize INT = 10000;
    WHILE EXISTS (SELECT 1 FROM LargeTable WHERE Processed = 0)
    BEGIN
        UPDATE TOP (@BatchSize) LargeTable
        SET Processed = 1
        WHERE Processed = 0;
    END;
END;

Example MySql:

-- MySQL batch processing
DELIMITER //
CREATE PROCEDURE ProcessLargeData()
BEGIN
    DECLARE batch_size INT DEFAULT 10000;
    REPEAT
        UPDATE LargeTable
        SET Processed = 1
        WHERE Processed = 0
        LIMIT batch_size;
    UNTIL ROW_COUNT() = 0 END REPEAT;
END //
DELIMITER ;

- BEFORE INSERT: Executes before a row is inserted, allowing modification of the inserted data or validation. Can prevent the insert by raising an error.
- AFTER INSERT: Executes after the row is inserted, typically for auditing or cascading actions.

Use Cases:
- BEFORE INSERT: Validate or transform data (e.g., ensure salary is positive).
- AFTER INSERT: Log the insert to an audit table (e.g., record new employee creation).

Note: MySQL does not support BEFORE/AFTER triggers for views; SQL Server supports INSTEAD OF triggers.
Example Sql Server:

-- BEFORE INSERT equivalent (use INSTEAD OF)
CREATE TRIGGER trg_BeforeInsertEmployee
ON Employees
INSTEAD OF INSERT
AS
BEGIN
    INSERT INTO Employees (EmployeeID, Name, Salary)
    SELECT EmployeeID, Name, CASE WHEN Salary < 0 THEN 0 ELSE Salary END
    FROM inserted;
END;
-- AFTER INSERT
CREATE TRIGGER trg_AfterInsertEmployee
ON Employees
AFTER INSERT
AS
BEGIN
    INSERT INTO EmployeeAudit (EmployeeID, Operation, ChangeDate)
    SELECT EmployeeID, 'INSERT', GETDATE()
    FROM inserted;
END;

Example MySql:

-- BEFORE INSERT
DELIMITER //
CREATE TRIGGER trg_BeforeInsertEmployee
BEFORE INSERT ON Employees
FOR EACH ROW
BEGIN
    IF NEW.Salary < 0 THEN
        SET NEW.Salary = 0;
    END IF;
END //
-- AFTER INSERT
CREATE TRIGGER trg_AfterInsertEmployee
AFTER INSERT ON Employees
FOR EACH ROW
BEGIN
    INSERT INTO EmployeeAudit (EmployeeID, Operation, ChangeDate)
    VALUES (NEW.EmployeeID, 'INSERT', NOW());
END //
DELIMITER ;

Debugging a PL/SQL procedure involves systematic steps to identify and resolve issues:
1. Check Logic: Review the procedure’s logic, especially conditions, loops, and variable assignments.
2. Use DBMS_OUTPUT: Add `DBMS_OUTPUT.PUT_LINE` to log variable values or execution flow.
3. Enable Debugging: Use tools like Oracle SQL Developer or PL/SQL Developer to set breakpoints and step through code.
4. Query Intermediate Results: Insert temporary SELECT statements or log to a table to inspect data at key points.
5. Handle Exceptions: Add exception blocks to catch and log errors.
6. Trace Execution: Use `DBMS_TRACE` or `DBMS_PROFILER` to analyze performance and execution paths.
7. Validate Inputs: Ensure input parameters are correct and handle edge cases.
8. Test Incrementally: Test smaller parts of the procedure in isolation.

Tools: SQL Developer, TOAD, or DBMS_UTILITY.FORMAT_ERROR_STACK for error details.

Best Practice: Log debugging information to a table for complex procedures and clean up logs after resolution.
Example Sql Server:

-- T-SQL debugging
CREATE PROCEDURE ProcessData
AS
BEGIN
    BEGIN TRY
        DECLARE @DebugMsg VARCHAR(200);
        SET @DebugMsg = 'Starting procedure';
        PRINT @DebugMsg;
        -- Logic here
        SET @DebugMsg = 'Processing complete';
        PRINT @DebugMsg;
    END TRY
    BEGIN CATCH
        PRINT 'Error: ' + ERROR_MESSAGE();
    END CATCH;
END;

Example MySql:

-- MySQL debugging
DELIMITER //
CREATE PROCEDURE ProcessData()
BEGIN
    DECLARE debug_msg VARCHAR(200);
    SET debug_msg = 'Starting procedure';
    INSERT INTO DebugLog (Message, LogTime) VALUES (debug_msg, NOW());
    -- Logic here
    SET debug_msg = 'Processing complete';
    INSERT INTO DebugLog (Message, LogTime) VALUES (debug_msg, NOW());
END //
DELIMITER ;

In PL/SQL, exceptions are handled using EXCEPTION blocks to catch and manage errors. Steps include:
1. Predefined Exceptions: Handle built-in errors (e.g., NO_DATA_FOUND).
2. Custom Exceptions: Define user-defined exceptions with RAISE.
3. Exception Block: Use BEGIN...EXCEPTION...END to catch errors.
4. Log Errors: Store error details in a log table.

Custom Exception Scenario: Raise an exception if an employee’s salary is below a minimum threshold during an update.
Example Sql Server:

-- T-SQL equivalent
BEGIN TRY
    DECLARE @MinSalary DECIMAL(10,2) = 30000;
    IF EXISTS (SELECT 1 FROM Employees WHERE Salary < @MinSalary)
        THROW 50001, 'Salary below minimum threshold', 1;
    UPDATE Employees SET Salary = Salary * 1.1;
END TRY
BEGIN CATCH
    INSERT INTO ErrorLog (ErrorMessage, ErrorTime)
    VALUES (ERROR_MESSAGE(), GETDATE());
    PRINT ERROR_MESSAGE();
END CATCH;

Example MySql:

-- MySQL equivalent
DELIMITER //
CREATE PROCEDURE UpdateSalary()
BEGIN
    DECLARE EXIT HANDLER FOR SQLEXCEPTION
    BEGIN
        INSERT INTO ErrorLog (ErrorMessage, ErrorTime)
        VALUES (@error_msg, NOW());
        SELECT @error_msg;
    END;
    SET @error_msg = 'Salary below minimum threshold';
    IF EXISTS (SELECT 1 FROM Employees WHERE Salary < 30000) THEN
        SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = @error_msg;
    END IF;
    UPDATE Employees SET Salary = Salary * 1.1;
END //
DELIMITER ;

This PL/SQL block updates salaries by 10% for employees who haven’t received a raise in the past year, checking the last raise date in a salary history table or assuming no updates in the past year if no history exists. It uses a transaction for consistency and logs changes.
Example Sql Server:

-- SQL Server (T-SQL equivalent)
BEGIN TRY
    BEGIN TRANSACTION;
    UPDATE Employees
    SET Salary = Salary * 1.10,
        LastRaiseDate = GETDATE()
    WHERE LastRaiseDate IS NULL OR LastRaiseDate < DATEADD(YEAR, -1, GETDATE());
    COMMIT;
END TRY
BEGIN CATCH
    ROLLBACK;
    PRINT ERROR_MESSAGE();
END CATCH;

Example MySql:

-- MySQL (Stored Procedure equivalent)
DELIMITER //
CREATE PROCEDURE UpdateSalaries()
BEGIN
    START TRANSACTION;
    UPDATE Employees
    SET Salary = Salary * 1.10,
        LastRaiseDate = CURDATE()
    WHERE LastRaiseDate IS NULL OR LastRaiseDate < DATE_SUB(CURDATE(), INTERVAL 1 YEAR);
    COMMIT;
END //
DELIMITER ;
CALL UpdateSalaries();

A palindrome number reads the same forwards and backwards (e.g., 121). This PL/SQL function converts the number to a string, reverses it, and checks if it matches the original.
Example Sql Server:

-- T-SQL function
CREATE FUNCTION IsPalindrome (@Number INT)
RETURNS BIT
AS
BEGIN
    DECLARE @NumStr VARCHAR(20) = CAST(@Number AS VARCHAR);
    DECLARE @Reversed VARCHAR(20) = REVERSE(@NumStr);
    RETURN CASE WHEN @NumStr = @Reversed THEN 1 ELSE 0 END;
END;
-- Test
SELECT dbo.IsPalindrome(121); -- Returns 1

Example MySql:

-- MySQL function
DELIMITER //
CREATE FUNCTION IsPalindrome(Number INT)
RETURNS BOOLEAN
DETERMINISTIC
BEGIN
    DECLARE num_str VARCHAR(20);
    DECLARE reversed VARCHAR(20);
    SET num_str = CAST(Number AS CHAR);
    SET reversed = REVERSE(num_str);
    RETURN num_str = reversed;
END //
DELIMITER ;
-- Test
SELECT IsPalindrome(121); -- Returns 1

- Cursors allow row-by-row processing of query results in PL/SQL.
- Explicit Cursor: Defined and controlled by the developer (DECLARE, OPEN, FETCH, CLOSE). Used for complex processing.
- Implicit Cursor: Automatically created by Oracle for DML or SELECT...INTO statements. Managed by the database, less control.

Key Differences:
- Control: Explicit cursors offer full control; implicit cursors are automatic.
- Performance: Implicit cursors are faster for simple queries; explicit cursors are better for iterative processing.
- Reusability: Explicit cursors can be reused; implicit cursors are single-use.

Use Case: Explicit cursor to process employee records; implicit cursor for single-row fetches.
Example Sql Server:

-- T-SQL explicit cursor equivalent
DECLARE @EmpID INT, @EmpName VARCHAR(50);
DECLARE emp_cursor CURSOR FOR
    SELECT EmployeeID, Name FROM Employees;
OPEN emp_cursor;
FETCH NEXT FROM emp_cursor INTO @EmpID, @EmpName;
WHILE @@FETCH_STATUS = 0
BEGIN
    PRINT @EmpName;
    FETCH NEXT FROM emp_cursor INTO @EmpID, @EmpName;
END;
CLOSE emp_cursor;
DEALLOCATE emp_cursor;

Example MySql:

-- MySQL cursor
DELIMITER //
CREATE PROCEDURE ProcessEmployees()
BEGIN
    DECLARE done INT DEFAULT FALSE;
    DECLARE emp_id INT;
    DECLARE emp_name VARCHAR(50);
    DECLARE emp_cursor CURSOR FOR
        SELECT EmployeeID, Name FROM Employees;
    DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE;
    OPEN emp_cursor;
    read_loop: LOOP
        FETCH emp_cursor INTO emp_id, emp_name;
        IF done THEN
            LEAVE read_loop;
        END IF;
        SELECT emp_name;
    END LOOP;
    CLOSE emp_cursor;
END //
DELIMITER ;

This PL/SQL block deletes duplicate rows based on a composite key (e.g., Name and DepartmentID), keeping the row with the lowest EmployeeID. It uses a subquery or ROW_NUMBER() to identify duplicates.
Example Sql Server:

-- T-SQL
WITH Duplicates AS (
    SELECT EmployeeID,
           ROW_NUMBER() OVER (PARTITION BY Name, DepartmentID ORDER BY EmployeeID) AS rn
    FROM Employees
)
DELETE FROM Duplicates WHERE rn > 1;

Example MySql:

-- MySQL
CREATE TEMPORARY TABLE Duplicates AS
    SELECT EmployeeID
    FROM (
        SELECT EmployeeID,
               ROW_NUMBER() OVER (PARTITION BY Name, DepartmentID ORDER BY EmployeeID) AS rn
        FROM Employees
    ) t
    WHERE rn > 1;
DELETE FROM Employees WHERE EmployeeID IN (SELECT EmployeeID FROM Duplicates);
DROP TEMPORARY TABLE Duplicates;

Bulk inserts in PL/SQL use collections (e.g., nested tables, varrays) with the FORALL statement to perform multiple DML operations in a single SQL call, reducing context switches.

Steps:
1. Define a collection type (or use TABLE OF).
2. Populate the collection with data.
3. Use FORALL to execute the INSERT for all elements.

Benefits: Faster than row-by-row inserts due to batch processing.
Example Sql Server:

-- T-SQL bulk insert (using table-valued parameter)
CREATE TYPE EmployeeType AS TABLE (ID INT, Name VARCHAR(50));
GO
CREATE PROCEDURE BulkInsertEmployees @Employees EmployeeType READONLY
AS
BEGIN
    INSERT INTO Employees (EmployeeID, Name)
    SELECT ID, Name FROM @Employees;
END;
-- Test
DECLARE @EmpTable EmployeeType;
INSERT INTO @EmpTable VALUES (1, 'John'), (2, 'Jane');
EXEC BulkInsertEmployees @EmpTable;

Example MySql:

-- MySQL (no FORALL, use temporary table)
DELIMITER //
CREATE PROCEDURE BulkInsertEmployees()
BEGIN
    CREATE TEMPORARY TABLE TempEmployees (ID INT, Name VARCHAR(50));
    INSERT INTO TempEmployees VALUES (1, 'John'), (2, 'Jane');
    INSERT INTO Employees (EmployeeID, Name)
    SELECT ID, Name FROM TempEmployees;
    DROP TEMPORARY TABLE TempEmployees;
END //
DELIMITER ;

To log errors in a stored procedure:
1. Create an error log table with columns for error message, timestamp, and context.
2. Use an EXCEPTION block (PL/SQL) or TRY-CATCH (T-SQL) to capture errors.
3. Insert error details into the log table, including stack trace or procedure name.
4. Optionally, use autonomous transactions (PL/SQL) to ensure logging even if the transaction rolls back.

Best Practice: Index the log table and periodically archive old logs.
Example Sql Server:

-- Error log table
CREATE TABLE ErrorLog (
    LogID INT IDENTITY PRIMARY KEY,
    ErrorMessage NVARCHAR(4000),
    ErrorTime DATETIME,
    ProcedureName NVARCHAR(128)
);
-- Procedure with error logging
CREATE PROCEDURE ProcessData
AS
BEGIN
    BEGIN TRY
        -- Simulate error
        SELECT 1/0;
    END TRY
    BEGIN CATCH
        INSERT INTO ErrorLog (ErrorMessage, ErrorTime, ProcedureName)
        VALUES (ERROR_MESSAGE(), GETDATE(), OBJECT_NAME(@@PROCID));
        THROW;
    END CATCH;
END;

Example MySql:

-- Error log table
CREATE TABLE ErrorLog (
    LogID INT AUTO_INCREMENT PRIMARY KEY,
    ErrorMessage VARCHAR(4000),
    ErrorTime DATETIME,
    ProcedureName VARCHAR(128)
);
-- Procedure with error logging
DELIMITER //
CREATE PROCEDURE ProcessData()
BEGIN
    DECLARE EXIT HANDLER FOR SQLEXCEPTION
    BEGIN
        INSERT INTO ErrorLog (ErrorMessage, ErrorTime, ProcedureName)
        VALUES (@error_msg, NOW(), 'ProcessData');
        SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = @error_msg;
    END;
    SET @error_msg = 'Division by zero';
    SELECT 1/0;
END //
DELIMITER ;

To find the second highest salary, use DENSE_RANK() to handle ties, or MAX with a subquery to find the highest salary less than the maximum. DENSE_RANK() is preferred for accuracy.
Example Sql Server:

-- Using DENSE_RANK
WITH RankedSalaries AS (
    SELECT EmployeeID, Name, Salary,
           DENSE_RANK() OVER (ORDER BY Salary DESC) AS SalaryRank
    FROM Employees
)
SELECT EmployeeID, Name, Salary
FROM RankedSalaries
WHERE SalaryRank = 2;

Example MySql:

-- Using DENSE_RANK
WITH RankedSalaries AS (
    SELECT EmployeeID, Name, Salary,
           DENSE_RANK() OVER (ORDER BY Salary DESC) AS SalaryRank
    FROM Employees
)
SELECT EmployeeID, Name, Salary
FROM RankedSalaries
WHERE SalaryRank = 2;

This query identifies continuous date ranges of working days by excluding weekends (Saturday, Sunday) and holidays from a holiday table. It uses LEAD to detect breaks in sequences and groups continuous ranges.
Example Sql Server:

-- Assume Holidays table exists
WITH WorkDays AS (
    SELECT WorkDate,
           ROW_NUMBER() OVER (ORDER BY WorkDate) - 
           ROW_NUMBER() OVER (PARTITION BY CASE WHEN DATEPART(WEEKDAY, WorkDate) IN (1, 7) 
                                               OR WorkDate IN (SELECT HolidayDate FROM Holidays) 
                                               THEN 1 ELSE 0 END ORDER BY WorkDate) AS Grp
    FROM (
        SELECT DATEADD(DAY, n-1, '2025-01-01') AS WorkDate
        FROM (SELECT ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) AS n
              FROM sys.all_objects) nums
        WHERE n <= 365
    ) Dates
    WHERE DATEPART(WEEKDAY, WorkDate) NOT IN (1, 7) 
    AND WorkDate NOT IN (SELECT HolidayDate FROM Holidays)
)
SELECT MIN(WorkDate) AS StartDate, MAX(WorkDate) AS EndDate
FROM WorkDays
GROUP BY Grp
ORDER BY StartDate;

Example MySql:

-- Assume Holidays table exists
WITH RECURSIVE Dates AS (
    SELECT '2025-01-01' AS WorkDate
    UNION ALL
    SELECT DATE_ADD(WorkDate, INTERVAL 1 DAY)
    FROM Dates
    WHERE WorkDate < '2025-12-31'
), WorkDays AS (
    SELECT WorkDate,
           ROW_NUMBER() OVER (ORDER BY WorkDate) - 
           ROW_NUMBER() OVER (PARTITION BY CASE WHEN DAYOFWEEK(WorkDate) IN (1, 7) 
                                               OR WorkDate IN (SELECT HolidayDate FROM Holidays) 
                                               THEN 1 ELSE 0 END ORDER BY WorkDate) AS Grp
    FROM Dates
    WHERE DAYOFWEEK(WorkDate) NOT IN (1, 7)
    AND WorkDate NOT IN (SELECT HolidayDate FROM Holidays)
)
SELECT MIN(WorkDate) AS StartDate, MAX(WorkDate) AS EndDate
FROM WorkDays
GROUP BY Grp
ORDER BY StartDate;

This stored procedure uses dynamic SQL to count rows in a specified table, validating the table’s existence to prevent SQL injection. It returns the count as a result.
Example Sql Server:

CREATE PROCEDURE GetRowCount
    @TableName NVARCHAR(128)
AS
BEGIN
    SET NOCOUNT ON;
    IF EXISTS (SELECT 1 FROM sys.tables WHERE name = @TableName)
    BEGIN
        DECLARE @SQL NVARCHAR(200) = 'SELECT COUNT(*) AS RowCount FROM ' + QUOTENAME(@TableName);
        EXEC sp_executesql @SQL;
    END
    ELSE
        THROW 50001, 'Table does not exist', 1;
END;
-- Test
EXEC GetRowCount @TableName = 'Employees';

Example MySql:

DELIMITER //
CREATE PROCEDURE GetRowCount(IN TableName VARCHAR(128))
BEGIN
    SET @sql = CONCAT('SELECT COUNT(*) AS RowCount FROM ', QUOTE_IDENTIFIER(TableName));
    IF EXISTS (SELECT 1 FROM information_schema.tables WHERE table_name = TableName) THEN
        PREPARE stmt FROM @sql;
        EXECUTE stmt;
        DEALLOCATE PREPARE stmt;
    ELSE
        SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'Table does not exist';
    END IF;
END //
DELIMITER ;
-- Test
CALL GetRowCount('Employees');

Conditional logic in SQL can be implemented using:
1. CASE: In queries to return values based on conditions.
2. IF/ELSE: In stored procedures or functions for control flow.

Use Cases:
- CASE: Categorize data (e.g., salary ranges).
- IF/ELSE: Control execution in procedures (e.g., validate inputs).

Best Practice: Use CASE for inline logic; reserve IF/ELSE for procedural logic.
Example Sql Server:

-- CASE in query
SELECT EmployeeID, Name, Salary,
       CASE WHEN Salary > 50000 THEN 'High' ELSE 'Low' END AS SalaryCategory
FROM Employees;
-- IF/ELSE in procedure
CREATE PROCEDURE UpdateSalary
    @EmployeeID INT,
    @NewSalary DECIMAL(10,2)
AS
BEGIN
    IF @NewSalary < 30000
        THROW 50001, 'Salary too low', 1;
    ELSE
        UPDATE Employees SET Salary = @NewSalary WHERE EmployeeID = @EmployeeID;
END;

Example MySql:

-- CASE in query
SELECT EmployeeID, Name, Salary,
       CASE WHEN Salary > 50000 THEN 'High' ELSE 'Low' END AS SalaryCategory
FROM Employees;
-- IF/ELSE in procedure
DELIMITER //
CREATE PROCEDURE UpdateSalary(IN EmployeeID INT, IN NewSalary DECIMAL(10,2))
BEGIN
    IF NewSalary < 30000 THEN
        SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'Salary too low';
    ELSE
        UPDATE Employees SET Salary = NewSalary WHERE EmployeeID = EmployeeID;
    END IF;
END //
DELIMITER ;

A PL/SQL package groups related procedures, functions, and variables. It has a specification (interface) and body (implementation).

Steps:
1. Create the package specification with declarations.
2. Create the package body with implementations.
3. Call procedures/functions using the package name.
Example Sql Server:

-- T-SQL (no packages, use schema for grouping)
CREATE PROCEDURE EmployeePkg_GetEmployee
    @EmployeeID INT
AS
BEGIN
    SELECT EmployeeID, Name, Salary FROM Employees WHERE EmployeeID = @EmployeeID;
END;
CREATE FUNCTION EmployeePkg_IsHighEarner (@Salary DECIMAL(10,2))
RETURNS BIT
AS
BEGIN
    RETURN CASE WHEN @Salary > 50000 THEN 1 ELSE 0 END;
END;
-- Call
EXEC EmployeePkg_GetEmployee @EmployeeID = 1;
SELECT dbo.EmployeePkg_IsHighEarner(60000);

Example MySql:

-- MySQL (no packages, use stored procedures)
DELIMITER //
CREATE PROCEDURE EmployeePkg_GetEmployee(IN EmployeeID INT)
BEGIN
    SELECT EmployeeID, Name, Salary FROM Employees WHERE EmployeeID = EmployeeID;
END //
CREATE FUNCTION EmployeePkg_IsHighEarner(Salary DECIMAL(10,2))
RETURNS BOOLEAN
DETERMINISTIC
BEGIN
    RETURN Salary > 50000;
END //
DELIMITER ;
-- Call
CALL EmployeePkg_GetEmployee(1);
SELECT EmployeePkg_IsHighEarner(60000);

Transposing rows to columns converts row-based data (e.g., sales by month) into columns. Use PIVOT (SQL Server) or CASE (both SQL Server and MySQL) for this.

PIVOT: Native operator for pivoting.
CASE: Manual aggregation for flexibility.

Use Case: Display monthly sales as columns for each employee.
Example Sql Server:

-- PIVOT
SELECT * FROM (
    SELECT EmployeeID, Month, Sales
    FROM Sales
) AS Source
PIVOT (
    SUM(Sales)
    FOR Month IN (Jan, Feb, Mar)
) AS PivotTable;
-- CASE
SELECT EmployeeID,
    SUM(CASE WHEN Month = 'Jan' THEN Sales END) AS Jan,
    SUM(CASE WHEN Month = 'Feb' THEN Sales END) AS Feb,
    SUM(CASE WHEN Month = 'Mar' THEN Sales END) AS Mar
FROM Sales
GROUP BY EmployeeID;

Example MySql:

-- CASE (MySQL lacks PIVOT)
SELECT EmployeeID,
    SUM(CASE WHEN Month = 'Jan' THEN Sales END) AS Jan,
    SUM(CASE WHEN Month = 'Feb' THEN Sales END) AS Feb,
    SUM(CASE WHEN Month = 'Mar' THEN Sales END) AS Mar
FROM Sales
GROUP BY EmployeeID;

INSTEAD OF triggers in SQL Server allow DML operations (INSERT, UPDATE, DELETE) on views by replacing the operation with custom logic. They are useful for complex views (e.g., joins) that are not directly updatable.

Steps:
1. Create a view combining multiple tables.
2. Define an INSTEAD OF trigger to handle the DML operation.
3. Implement logic to update underlying tables.

Note: MySQL does not support INSTEAD OF triggers; use stored procedures instead.
Example Sql Server:

-- View
CREATE VIEW EmployeeDepartment AS
SELECT e.EmployeeID, e.Name, e.Salary, d.DepartmentName
FROM Employees e
JOIN Departments d ON e.DepartmentID = d.DepartmentID;
-- INSTEAD OF INSERT trigger
CREATE TRIGGER trg_InsteadOfInsert
ON EmployeeDepartment
INSTEAD OF INSERT
AS
BEGIN
    INSERT INTO Employees (EmployeeID, Name, Salary, DepartmentID)
    SELECT i.EmployeeID, i.Name, i.Salary, d.DepartmentID
    FROM inserted i
    JOIN Departments d ON i.DepartmentName = d.DepartmentName;
END;
-- Test
INSERT INTO EmployeeDepartment (EmployeeID, Name, Salary, DepartmentName)
VALUES (1, 'John', 50000, 'HR');

Example MySql:

-- MySQL (use stored procedure)
CREATE VIEW EmployeeDepartment AS
SELECT e.EmployeeID, e.Name, e.Salary, d.DepartmentName
FROM Employees e
JOIN Departments d ON e.DepartmentID = d.DepartmentID;
DELIMITER //
CREATE PROCEDURE InsertEmployeeDepartment(
    IN p_EmployeeID INT, IN p_Name VARCHAR(50),
    IN p_Salary DECIMAL(10,2), IN p_DepartmentName VARCHAR(50)
)
BEGIN
    INSERT INTO Employees (EmployeeID, Name, Salary, DepartmentID)
    SELECT p_EmployeeID, p_Name, p_Salary, DepartmentID
    FROM Departments WHERE DepartmentName = p_DepartmentName;
END //
DELIMITER ;
-- Test
CALL InsertEmployeeDepartment(1, 'John', 50000, 'HR');

Row-by-row cursor processing is slow for large datasets. Optimization strategies include:
1. Use Bulk Collect: Fetch rows in batches with BULK COLLECT.
2. Use FORALL: Perform DML operations in bulk.
3. Replace Cursor with SQL: Use a single SQL statement if possible.
4. Limit Fetches: Use LIMIT with BULK COLLECT to manage memory.
5. Parallel Processing: Enable parallel DML or use DBMS_PARALLEL_EXECUTE.
6. Index Tables: Ensure indexes on columns used in cursor queries.
7. Tune Fetch Size: Adjust the cursor’s fetch size for optimal performance.

Use Case: Optimize a cursor updating millions of rows.
Example Sql Server:

-- T-SQL batch processing
CREATE PROCEDURE OptimizeCursor
AS
BEGIN
    DECLARE @BatchSize INT = 10000;
    WHILE EXISTS (SELECT 1 FROM Employees WHERE Processed = 0)
    BEGIN
        UPDATE TOP (@BatchSize) Employees
        SET Processed = 1
        WHERE Processed = 0;
    END;
END;

Example MySql:

-- MySQL batch processing
DELIMITER //
CREATE PROCEDURE OptimizeCursor()
BEGIN
    DECLARE batch_size INT DEFAULT 10000;
    REPEAT
        UPDATE Employees
        SET Processed = 1
        WHERE Processed = 0
        LIMIT batch_size;
    UNTIL ROW_COUNT() = 0 END REPEAT;
END //
DELIMITER ;

Dynamic SQL in PL/SQL executes SQL statements constructed at runtime using EXECUTE IMMEDIATE or DBMS_SQL.

Steps:
1. Build the SQL statement as a string.
2. Use EXECUTE IMMEDIATE to run it.
3. Use bind variables to prevent SQL injection.

Precautions:
- Prevent SQL Injection: Use bind variables instead of concatenating user input.
- Validate Inputs: Check table/column names against system catalogs.
- Handle Errors: Use EXCEPTION blocks to catch runtime errors.
- Limit Scope: Avoid dynamic SQL for static queries.
- Test Thoroughly: Verify dynamic SQL with edge cases.

Use Case: Dynamically update a table based on user input.
Example Sql Server:

-- T-SQL dynamic SQL
CREATE PROCEDURE DynamicUpdate
    @TableName NVARCHAR(128),
    @ColumnName NVARCHAR(128),
    @Value NVARCHAR(100)
AS
BEGIN
    DECLARE @SQL NVARCHAR(500);
    SET @SQL = N'UPDATE ' + QUOTENAME(@TableName) + 
                N' SET ' + QUOTENAME(@ColumnName) + N' = @Val';
    EXEC sp_executesql @SQL, N'@Val NVARCHAR(100)', @Value;
END;
-- Test
EXEC DynamicUpdate @TableName = 'Employees', @ColumnName = 'Salary', @Value = '50000';

Example MySql:

-- MySQL dynamic SQL
DELIMITER //
CREATE PROCEDURE DynamicUpdate(
    IN TableName VARCHAR(128),
    IN ColumnName VARCHAR(128),
    IN Value VARCHAR(100)
)
BEGIN
    SET @sql = CONCAT('UPDATE ', QUOTE_IDENTIFIER(TableName), 
                      ' SET ', QUOTE_IDENTIFIER(ColumnName), ' = ?');
    PREPARE stmt FROM @sql;
    SET @val = Value;
    EXECUTE stmt USING @val;
    DEALLOCATE PREPARE stmt;
END //
DELIMITER ;
-- Test
CALL DynamicUpdate('Employees', 'Salary', '50000');

An autonomous transaction in PL/SQL is a nested transaction that commits or rolls back independently of the main transaction. It uses the PRAGMA AUTONOMOUS_TRANSACTION directive.

Use Case: Log errors to a table even if the main transaction rolls back (e.g., auditing failed operations).

Precautions:
- Use sparingly, as they can complicate transaction management.
- Ensure the autonomous transaction commits or rolls back explicitly.
- Avoid excessive nesting to prevent deadlocks.
Example Sql Server:

-- T-SQL (no autonomous transactions, use table variables or separate connection)
CREATE PROCEDURE LogError
    @ErrorMsg NVARCHAR(4000)
AS
BEGIN
    INSERT INTO ErrorLog (ErrorMessage, ErrorTime)
    VALUES (@ErrorMsg, GETDATE());
END;
CREATE PROCEDURE ProcessData
AS
BEGIN
    BEGIN TRY
        SELECT 1/0;
    END TRY
    BEGIN CATCH
        EXEC LogError @ErrorMsg = ERROR_MESSAGE();
        THROW;
    END CATCH;
END;

Example MySql:

-- MySQL (no autonomous transactions, use separate procedure)
DELIMITER //
CREATE PROCEDURE LogError(IN ErrorMsg VARCHAR(4000))
BEGIN
    INSERT INTO ErrorLog (ErrorMessage, ErrorTime)
    VALUES (ErrorMsg, NOW());
END //
CREATE PROCEDURE ProcessData()
BEGIN
    DECLARE EXIT HANDLER FOR SQLEXCEPTION
    BEGIN
        CALL LogError(@error_msg);
        SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = @error_msg;
    END;
    SET @error_msg = 'Division by zero';
    SELECT 1/0;
END //
DELIMITER ;

To find gaps in a sequence of invoice numbers, compare the sequence with expected values using LEAD or a generated sequence to identify missing numbers.

Approach:
1. Use LEAD to check for non-consecutive invoice numbers.
2. Generate a sequence and LEFT JOIN to find missing numbers.

Best Practice: Index the invoice number column for performance.
Example Sql Server:

-- Using LEAD
WITH InvoiceGaps AS (
    SELECT InvoiceNumber, LEAD(InvoiceNumber) OVER (ORDER BY InvoiceNumber) AS NextInvoice
    FROM Invoices
)
SELECT InvoiceNumber, NextInvoice, (NextInvoice - InvoiceNumber - 1) AS GapSize
FROM InvoiceGaps
WHERE NextInvoice - InvoiceNumber > 1;
-- Using sequence
WITH Numbers AS (
    SELECT ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) AS n
    FROM sys.all_objects
)
SELECT n AS MissingInvoice
FROM Numbers n
LEFT JOIN Invoices i ON i.InvoiceNumber = n
WHERE i.InvoiceNumber IS NULL AND n <= (SELECT MAX(InvoiceNumber) FROM Invoices);

Example MySql:

-- Using LEAD
WITH InvoiceGaps AS (
    SELECT InvoiceNumber, LEAD(InvoiceNumber) OVER (ORDER BY InvoiceNumber) AS NextInvoice
    FROM Invoices
)
SELECT InvoiceNumber, NextInvoice, (NextInvoice - InvoiceNumber - 1) AS GapSize
FROM InvoiceGaps
WHERE NextInvoice - InvoiceNumber > 1;
-- Using sequence
WITH RECURSIVE Numbers AS (
    SELECT 1 AS n
    UNION ALL
    SELECT n + 1 FROM Numbers WHERE n < (SELECT MAX(InvoiceNumber) FROM Invoices)
)
SELECT n AS MissingInvoice
FROM Numbers n
LEFT JOIN Invoices i ON i.InvoiceNumber = n
WHERE i.InvoiceNumber IS NULL;

To ensure a PL/SQL block is idempotent (runs only once for specific parameters):
1. Use a Control Table: Log execution details (e.g., parameters, timestamp) and check before running.
2. Check State: Verify if the operation’s outcome already exists (e.g., record exists).
3. Use Transactions: Ensure atomicity to prevent partial execution.
4. Add Constraints: Use unique constraints to prevent duplicate inserts.

Use Case: Prevent duplicate processing of a payment request.
Example Sql Server:

-- Control table
CREATE TABLE ExecutionLog (
    ParamID INT PRIMARY KEY,
    ExecutionTime DATETIME
);
-- Idempotent procedure
CREATE PROCEDURE ProcessPayment
    @PaymentID INT
AS
BEGIN
    IF NOT EXISTS (SELECT 1 FROM ExecutionLog WHERE ParamID = @PaymentID)
    BEGIN
        BEGIN TRANSACTION;
        INSERT INTO ExecutionLog (ParamID, ExecutionTime)
        VALUES (@PaymentID, GETDATE());
        -- Process payment
        INSERT INTO Payments (PaymentID, Amount) VALUES (@PaymentID, 100);
        COMMIT;
    END;
END;

Example MySql:

-- Control table
CREATE TABLE ExecutionLog (
    ParamID INT PRIMARY KEY,
    ExecutionTime DATETIME
);
-- Idempotent procedure
DELIMITER //
CREATE PROCEDURE ProcessPayment(IN PaymentID INT)
BEGIN
    IF NOT EXISTS (SELECT 1 FROM ExecutionLog WHERE ParamID = PaymentID) THEN
        START TRANSACTION;
        INSERT INTO ExecutionLog (ParamID, ExecutionTime)
        VALUES (PaymentID, NOW());
        -- Process payment
        INSERT INTO Payments (PaymentID, Amount) VALUES (PaymentID, 100);
        COMMIT;
    END IF;
END //
DELIMITER ;

Multi-table inserts in PL/SQL can be handled using a single transaction to ensure consistency:
1. Use a Transaction: Wrap inserts in BEGIN TRANSACTION/COMMIT.
2. Validate Data: Check constraints before inserting.
3. Handle Errors: Use EXCEPTION or TRY-CATCH to rollback on failure.
4. Use Sequences: Generate IDs for related tables.

Use Case: Insert an order and its details atomically.
Example Sql Server:

-- T-SQL
BEGIN TRY
    BEGIN TRANSACTION;
    DECLARE @OrderID INT;
    INSERT INTO Orders (CustomerID, OrderDate)
    VALUES (101, GETDATE());
    SET @OrderID = SCOPE_IDENTITY();
    INSERT INTO OrderDetails (OrderID, ProductID, Quantity)
    VALUES (@OrderID, 501, 10);
    COMMIT;
END TRY
BEGIN CATCH
    ROLLBACK;
    PRINT ERROR_MESSAGE();
END CATCH;

Example MySql:

-- MySQL
DELIMITER //
CREATE PROCEDURE InsertOrder()
BEGIN
    DECLARE order_id INT;
    START TRANSACTION;
    INSERT INTO Orders (CustomerID, OrderDate)
    VALUES (101, NOW());
    SET order_id = LAST_INSERT_ID();
    INSERT INTO OrderDetails (OrderID, ProductID, Quantity)
    VALUES (order_id, 501, 10);
    COMMIT;
END //
DELIMITER ;
CALL InsertOrder();

To find employees with no time sheet entries, use a LEFT JOIN or NOT EXISTS to identify employees missing from the TimeSheets table.

Approach:
- LEFT JOIN: Join Employees with TimeSheets and filter for NULLs in TimeSheets.
- NOT EXISTS: Check for employees with no matching TimeSheets rows.

Best Practice: Index foreign keys (EmployeeID) for performance.
Example Sql Server:

-- Using LEFT JOIN
SELECT e.EmployeeID, e.Name
FROM Employees e
LEFT JOIN TimeSheets t ON e.EmployeeID = t.EmployeeID
WHERE t.EmployeeID IS NULL;
-- Using NOT EXISTS
SELECT EmployeeID, Name
FROM Employees e
WHERE NOT EXISTS (
    SELECT 1 FROM TimeSheets t WHERE t.EmployeeID = e.EmployeeID
);

Example MySql:

-- Using LEFT JOIN
SELECT e.EmployeeID, e.Name
FROM Employees e
LEFT JOIN TimeSheets t ON e.EmployeeID = t.EmployeeID
WHERE t.EmployeeID IS NULL;
-- Using NOT EXISTS
SELECT EmployeeID, Name
FROM Employees e
WHERE NOT EXISTS (
    SELECT 1 FROM TimeSheets t WHERE t.EmployeeID = e.EmployeeID
);