So sánh Stored Procedures và Hàm Python: Những Điểm Tương Đồng Đáng Ngạc Nhiên

CREATE PROCEDURE CalculateBonus(
    @EmployeeID INT,
    @PerformanceRating DECIMAL(3,2)
)
AS
BEGIN
    DECLARE @Bonus DECIMAL(10,2);
    SELECT @Bonus = salary * @PerformanceRating * 0.1
    FROM employees 
    WHERE employee_id = @EmployeeID;

    RETURN @Bonus;
END;

def calculate_bonus(employee_id, performance_rating):
    # Giả lập truy vấn cơ sở dữ liệu
    salary = get_employee_salary(employee_id)
    bonus = salary * performance_rating * 0.1
    return bonus

CREATE PROCEDURE GetEmployees(
    @DepartmentID INT = NULL,
    @MinSalary DECIMAL(10,2) = 0,
    @MaxResults INT = 100
)
AS
BEGIN
    SELECT TOP (@MaxResults) *
    FROM employees
    WHERE (@DepartmentID IS NULL OR department_id = @DepartmentID)
    AND salary >= @MinSalary;
END;

def get_employees(department_id=None, min_salary=0, max_results=100):
    query = "SELECT * FROM employees WHERE salary >= %s"
    params = [min_salary]

    if department_id:
        query += " AND department_id = %s"
        params.append(department_id)

    query += f" LIMIT {max_results}"

    return execute_query(query, params)

CREATE PROCEDURE ProcessPayroll(@PayPeriod DATE)
AS
BEGIN
    DECLARE @EmployeeID INT, @Salary DECIMAL(10,2);
    DECLARE employee_cursor CURSOR FOR
        SELECT employee_id, salary FROM employees WHERE active = 1;

    BEGIN TRY
        OPEN employee_cursor;
        FETCH NEXT FROM employee_cursor INTO @EmployeeID, @Salary;

        WHILE @@FETCH_STATUS = 0
        BEGIN
            IF @Salary > 0
                INSERT INTO payroll (employee_id, amount, pay_date)
                VALUES (@EmployeeID, @Salary, @PayPeriod);

            FETCH NEXT FROM employee_cursor INTO @EmployeeID, @Salary;
        END;

        CLOSE employee_cursor;
        DEALLOCATE employee_cursor;
    END TRY
    BEGIN CATCH
        -- Xử lý lỗi
        ROLLBACK TRANSACTION;
        THROW;
    END CATCH;
END;

def process_payroll(pay_period):
    try:
        active_employees = get_active_employees()

        for employee in active_employees:
            if employee['salary'] > 0:
                insert_payroll_record(
                    employee_id=employee['id'],
                    amount=employee['salary'],
                    pay_date=pay_period
                )
    except Exception as e:
        # Xử lý lỗi
        rollback_transaction()
        raise e

CREATE PROCEDURE GetDepartmentStats(
    @DepartmentID INT,
    @EmployeeCount INT OUTPUT,
    @AvgSalary DECIMAL(10,2) OUTPUT
)
AS
BEGIN
    SELECT 
        @EmployeeCount = COUNT(*),
        @AvgSalary = AVG(salary)
    FROM employees
    WHERE department_id = @DepartmentID;

    -- Cũng trả về tập kết quả
    SELECT name, salary 
    FROM employees 
    WHERE department_id = @DepartmentID;
END;

def get_department_stats(department_id):
    employees = get_employees_by_department(department_id)

    employee_count = len(employees)
    avg_salary = sum(emp['salary'] for emp in employees) / len(employees) if employees else 0

    # Trả về tuple (tương tự như tham số OUTPUT)
    return employee_count, avg_salary, employees

CREATE PROCEDURE AuthenticateUser(
    @Username NVARCHAR(50),
    @Password NVARCHAR(255),
    @IsValid BIT OUTPUT,
    @UserRole NVARCHAR(20) OUTPUT
)
AS
BEGIN
    DECLARE @StoredHash NVARCHAR(255);

    SELECT @StoredHash = password_hash, @UserRole = role
    FROM users
    WHERE username = @Username AND active = 1;

    IF @StoredHash IS NOT NULL AND @StoredHash = HASHBYTES('SHA2_256', @Password + 'salt')
        SET @IsValid = 1;
    ELSE
        SET @IsValid = 0;
END;

import hashlib

def authenticate_user(username, password):
    user = get_user_by_username(username)

    if not user or not user['active']:
        return False, None

    # Băm mật khẩu đã cung cấp
    password_hash = hashlib.sha256((password + 'salt').encode()).hexdigest()

    if password_hash == user['password_hash']:
        return True, user['role']
    else:
        return False, None