视图、函数和过程 - MySQL与PostgreSQL实现对比

引言

数据库编程是现代应用程序开发的重要组成部分，它允许我们创建可重用的数据库对象，如视图、函数和存储过程。这些对象可以帮助我们封装复杂的查询逻辑、提高数据访问效率、增强安全性，并简化应用程序代码。本文将详细介绍视图、函数和存储过程的创建与使用，并对比MySQL和PostgreSQL在这些数据库对象上的实现差异。

1. 视图（Views）

1.1 什么是视图？

视图是一种虚拟表，其内容由查询定义。与真实的表不同，视图并不在数据库中以存储的数据值集形式存在。行和列数据来自由定义视图的查询所引用的表，并且在引用视图时动态生成。

1.2 MySQL视图

基础视图创建：

-- 简单视图
CREATE VIEW active_employees AS
SELECT employee_id, first_name, last_name, email, salary
FROM employees
WHERE status = 'active';

-- 复杂视图
CREATE VIEW department_summary AS
SELECT 
    d.department_id,
    d.department_name,
    COUNT(e.employee_id) as employee_count,
    AVG(e.salary) as avg_salary,
    MIN(e.hire_date) as earliest_hire,
    MAX(e.salary) as max_salary
FROM departments d
LEFT JOIN employees e ON d.department_id = e.department_id
GROUP BY d.department_id, d.department_name;

-- 带检查选项的视图
CREATE VIEW high_earners AS
SELECT employee_id, first_name, last_name, salary
FROM employees
WHERE salary > 75000
WITH CHECK OPTION;  -- 确保通过视图插入或更新的数据满足WHERE条件

MySQL视图特性：

-- 查看视图定义
SHOW CREATE VIEW active_employees;

-- 查看视图信息
SELECT * FROM information_schema.views 
WHERE table_name = 'active_earners';

-- 修改视图
CREATE OR REPLACE VIEW active_employees AS
SELECT employee_id, first_name, last_name, email, salary, hire_date
FROM employees
WHERE status = 'active';

-- 删除视图
DROP VIEW IF EXISTS active_employees;

-- 可更新视图
-- 简单的视图通常是可更新的
UPDATE active_employees 
SET salary = salary * 1.1 
WHERE employee_id = 123;

MySQL视图算法：

-- 指定视图算法
CREATE ALGORITHM=MERGE VIEW emp_dept_view AS
SELECT e.employee_id, e.first_name, e.last_name, d.department_name
FROM employees e
JOIN departments d ON e.department_id = d.department_id;

-- UNDEFINED（默认）：由MySQL选择
-- MERGE：将视图查询合并到外部查询
-- TEMPTABLE：创建临时表存储视图结果

1.3 PostgreSQL视图

PostgreSQL视图创建：

-- 简单视图
CREATE VIEW active_employees AS
SELECT employee_id, first_name, last_name, email, salary
FROM employees
WHERE status = 'active';

-- 复杂视图
CREATE VIEW department_summary AS
SELECT 
    d.department_id,
    d.department_name,
    COUNT(e.employee_id) as employee_count,
    AVG(e.salary) as avg_salary,
    MIN(e.hire_date) as earliest_hire,
    MAX(e.salary) as max_salary
FROM departments d
LEFT JOIN employees e ON d.department_id = e.department_id
GROUP BY d.department_id, d.department_name;

-- 带检查选项的视图
CREATE VIEW high_earners AS
SELECT employee_id, first_name, last_name, salary
FROM employees
WHERE salary > 75000
WITH CHECK OPTION;

PostgreSQL高级视图特性：

-- 物化视图（PostgreSQL特有）
CREATE MATERIALIZED VIEW monthly_sales_summary AS
SELECT 
    DATE_TRUNC('month', sale_date) as month,
    SUM(amount) as total_sales,
    COUNT(*) as transaction_count
FROM sales
GROUP BY DATE_TRUNC('month', sale_date);

-- 刷新物化视图
REFRESH MATERIALIZED VIEW monthly_sales_summary;

-- 并发刷新（不锁定表）
REFRESH MATERIALIZED VIEW CONCURRENTLY monthly_sales_summary;

-- 查看视图定义
\dv  -- psql命令
SELECT table_name, view_definition 
FROM information_schema.views 
WHERE table_name = 'active_employees';

-- 安全视图（with local/security invoker）
CREATE VIEW secure_view AS
SELECT employee_id, first_name, last_name
FROM employees
WHERE current_user = 'admin'
WITH LOCAL CHECK OPTION;

1.4 视图最佳实践

-- 性能优化的视图
CREATE VIEW optimized_view AS
SELECT 
    e.employee_id,
    e.first_name,
    e.last_name,
    d.department_name,
    -- 预计算复杂表达式
    CASE 
        WHEN e.salary > 100000 THEN 'High'
        WHEN e.salary > 75000 THEN 'Medium'
        ELSE 'Low'
    END as salary_level
FROM employees e
JOIN departments d ON e.department_id = d.department_id
WHERE e.status = 'active';

-- 安全性视图（隐藏敏感信息）
CREATE VIEW employee_public_view AS
SELECT 
    employee_id,
    first_name,
    last_name,
    department_id,
    hire_date
FROM employees
WHERE status = 'active';

-- 使用视图进行数据抽象
CREATE VIEW customer_order_summary AS
SELECT 
    c.customer_id,
    c.customer_name,
    COUNT(o.order_id) as total_orders,
    SUM(o.total_amount) as total_spent,
    MAX(o.order_date) as last_order_date
FROM customers c
LEFT JOIN orders o ON c.customer_id = o.customer_id
GROUP BY c.customer_id, c.customer_name;

2. 存储函数（Functions）

2.1 什么是存储函数？

存储函数是数据库中的一段预编译代码，它可以接收参数、执行操作并返回一个值。与存储过程不同，函数总是返回一个值。

2.2 MySQL存储函数

-- 简单函数
DELIMITER //
CREATE FUNCTION calculate_bonus(salary DECIMAL(10,2), bonus_rate DECIMAL(3,2))
RETURNS DECIMAL(10,2)
READS SQL DATA
DETERMINISTIC
BEGIN
    DECLARE bonus_amount DECIMAL(10,2);
    SET bonus_amount = salary * bonus_rate;
    RETURN bonus_amount;
END //
DELIMITER ;

-- 使用函数
SELECT employee_id, first_name, last_name, 
       salary, 
       calculate_bonus(salary, 0.1) as bonus
FROM employees;

-- 复杂函数示例
DELIMITER //
CREATE FUNCTION get_employee_grade(salary DECIMAL(10,2))
RETURNS VARCHAR(10)
READS SQL DATA
DETERMINISTIC
BEGIN
    DECLARE grade VARCHAR(10);
    
    CASE 
        WHEN salary >= 100000 THEN SET grade = 'Senior';
        WHEN salary >= 75000 THEN SET grade = 'Mid';
        WHEN salary >= 50000 THEN SET grade = 'Junior';
        ELSE SET grade = 'Entry';
    END CASE;
    
    RETURN grade;
END //
DELIMITER ;

-- 字符串处理函数
DELIMITER //
CREATE FUNCTION format_full_name(first_name VARCHAR(50), last_name VARCHAR(50))
RETURNS VARCHAR(101)
DETERMINISTIC
BEGIN
    RETURN CONCAT(TRIM(first_name), ' ', TRIM(last_name));
END //
DELIMITER ;

MySQL函数特性：

-- 查看函数
SHOW FUNCTION STATUS WHERE Name = 'calculate_bonus';

-- 查看函数定义
SHOW CREATE FUNCTION calculate_bonus;

-- 修改函数
DROP FUNCTION IF EXISTS calculate_bonus;

DELIMITER //
CREATE FUNCTION calculate_bonus(salary DECIMAL(10,2), bonus_rate DECIMAL(3,2))
RETURNS DECIMAL(10,2)
READS SQL DATA
DETERMINISTIC
COMMENT 'Calculate bonus based on salary and rate'
BEGIN
    DECLARE bonus_amount DECIMAL(10,2);
    SET bonus_amount = salary * bonus_rate;
    RETURN bonus_amount;
END //
DELIMITER ;

-- 函数修饰符说明
-- DETERMINISTIC：相同输入总是产生相同输出
-- NOT DETERMINISTIC：（默认）输出可能因环境而异
-- READS SQL DATA：函数读取数据但不修改
-- MODIFIES SQL DATA：函数可能修改数据
-- CONTAINS SQL：函数包含SQL但不读写数据
-- SQL SECURITY DEFINER：以定义者权限执行
-- SQL SECURITY INVOKER：以调用者权限执行

2.3 PostgreSQL存储函数

-- 使用PL/pgSQL语言的函数
CREATE OR REPLACE FUNCTION calculate_bonus(salary NUMERIC, bonus_rate NUMERIC)
RETURNS NUMERIC AS $$
BEGIN
    RETURN salary * bonus_rate;
END;
$$ LANGUAGE plpgsql;

-- 使用函数
SELECT employee_id, first_name, last_name, 
       salary, 
       calculate_bonus(salary, 0.1) as bonus
FROM employees;

-- 复杂函数示例
CREATE OR REPLACE FUNCTION get_employee_grade(salary NUMERIC)
RETURNS TEXT AS $$
DECLARE
    grade TEXT;
BEGIN
    CASE 
        WHEN salary >= 100000 THEN 
            grade := 'Senior';
        WHEN salary >= 75000 THEN 
            grade := 'Mid';
        WHEN salary >= 50000 THEN 
            grade := 'Junior';
        ELSE 
            grade := 'Entry';
    END CASE;
    
    RETURN grade;
END;
$$ LANGUAGE plpgsql;

-- 返回表类型的函数
CREATE OR REPLACE FUNCTION get_high_earners(min_salary NUMERIC)
RETURNS TABLE (
    employee_id INTEGER,
    first_name TEXT,
    last_name TEXT,
    salary NUMERIC
) AS $$
BEGIN
    RETURN QUERY
    SELECT e.employee_id, e.first_name, e.last_name, e.salary
    FROM employees e
    WHERE e.salary >= min_salary
    ORDER BY e.salary DESC;
END;
$$ LANGUAGE plpgsql;

-- 使用返回表的函数
SELECT * FROM get_high_earners(80000);

-- 字符串处理函数
CREATE OR REPLACE FUNCTION format_full_name(first_name TEXT, last_name TEXT)
RETURNS TEXT AS $$
BEGIN
    RETURN TRIM(first_name || ' ' || last_name);
END;
$$ LANGUAGE plpgsql IMMUTABLE;

-- IMMUTABLE表示函数在相同输入下总是返回相同结果，可以被优化

PostgreSQL高级函数特性：

-- 使用不同语言的函数
-- Python函数（需要plpythonu扩展）
CREATE EXTENSION IF NOT EXISTS plpython3u;

CREATE OR REPLACE FUNCTION python_string_process(input_text TEXT)
RETURNS TEXT AS $$
    return input_text.upper().strip()
$$ LANGUAGE plpython3u;

-- 聚合函数
CREATE AGGREGATE geometric_mean(NUMERIC) (
    SFUNC = float8mul,
    STYPE = NUMERIC,
    FINALFUNC = sqrt,
    INITCOND = '1'
);

-- 窗口函数
CREATE OR REPLACE FUNCTION moving_average(state NUMERIC, value NUMERIC, window_size INTEGER)
RETURNS NUMERIC AS $$
BEGIN
    -- 实现移动平均逻辑
    RETURN state;
END;
$$ LANGUAGE plpgsql WINDOW;

-- 函数重载
CREATE OR REPLACE FUNCTION add_numbers(a INTEGER, b INTEGER)
RETURNS INTEGER AS $$ BEGIN RETURN a + b; END; $$ LANGUAGE plpgsql;

CREATE OR REPLACE FUNCTION add_numbers(a NUMERIC, b NUMERIC)
RETURNS NUMERIC AS $$ BEGIN RETURN a + b; END; $$ LANGUAGE plpgsql;

2.4 函数性能考虑

-- MySQL函数性能优化
DELIMITER //
CREATE FUNCTION optimized_calculation(value1 DECIMAL(10,2), value2 DECIMAL(10,2))
RETURNS DECIMAL(10,2)
READS SQL DATA
DETERMINISTIC
SQL SECURITY INVOKER
BEGIN
    -- 避免复杂计算
    DECLARE result DECIMAL(10,2);
    SET result = value1 + value2;
    RETURN result;
END //
DELIMITER ;

-- PostgreSQL函数性能优化
CREATE OR REPLACE FUNCTION optimized_calculation(val1 NUMERIC, val2 NUMERIC)
RETURNS NUMERIC AS $$
    SELECT val1 + val2;  -- 直接SQL，性能更好
$$ LANGUAGE sql IMMUTABLE;

-- 使用内联函数（PostgreSQL 14+）
CREATE OR REPLACE FUNCTION simple_calculation(a NUMERIC, b NUMERIC)
RETURNS NUMERIC AS $$
    SELECT a * b + 100;
$$ LANGUAGE sql IMMUTABLE PARALLEL SAFE;

3. 存储过程（Stored Procedures）

3.1 什么是存储过程？

存储过程是一组为了完成特定功能的SQL语句集，经编译后存储在数据库中，用户通过指定存储过程的名字并给出参数（如果该存储过程带有参数）来执行它。

3.2 MySQL存储过程

-- 简单存储过程
DELIMITER //
CREATE PROCEDURE GetEmployeeById(IN emp_id INT)
BEGIN
    SELECT employee_id, first_name, last_name, email, salary
    FROM employees
    WHERE employee_id = emp_id;
END //
DELIMITER ;

-- 调用存储过程
CALL GetEmployeeById(123);

-- 带输出参数的存储过程
DELIMITER //
CREATE PROCEDURE GetEmployeeStats(
    OUT total_count INT,
    OUT avg_salary DECIMAL(10,2),
    OUT max_salary DECIMAL(10,2)
)
BEGIN
    SELECT 
        COUNT(*),
        AVG(salary),
        MAX(salary)
    INTO total_count, avg_salary, max_salary
    FROM employees;
END //
DELIMITER ;

-- 调用带输出参数的过程
CALL GetEmployeeStats(@count, @avg_sal, @max_sal);
SELECT @count, @avg_sal, @max_sal;

-- 复杂存储过程示例
DELIMITER //
CREATE PROCEDURE ProcessMonthlyPayroll(IN month_year DATE)
BEGIN
    DECLARE done INT DEFAULT FALSE;
    DECLARE emp_id INT;
    DECLARE emp_salary DECIMAL(10,2);
    DECLARE emp_bonus DECIMAL(10,2);
    DECLARE cur CURSOR FOR 
        SELECT employee_id, salary 
        FROM employees 
        WHERE status = 'active';
    DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE;
    
    -- 创建临时表存储结果
    CREATE TEMPORARY TABLE temp_payroll (
        employee_id INT,
        base_salary DECIMAL(10,2),
        bonus DECIMAL(10,2),
        total_pay DECIMAL(10,2)
    );
    
    OPEN cur;
    
    read_loop: LOOP
        FETCH cur INTO emp_id, emp_salary;
        IF done THEN
            LEAVE read_loop;
        END IF;
        
        -- 计算奖金（示例逻辑）
        SET emp_bonus = emp_salary * 0.1;
        
        -- 插入临时表
        INSERT INTO temp_payroll 
        VALUES (emp_id, emp_salary, emp_bonus, emp_salary + emp_bonus);
    END LOOP;
    
    CLOSE cur;
    
    -- 返回结果
    SELECT * FROM temp_payroll;
    
    -- 清理
    DROP TEMPORARY TABLE temp_payroll;
END //
DELIMITER ;

-- 条件逻辑存储过程
DELIMITER //
CREATE PROCEDURE UpdateEmployeeSalary(
    IN emp_id INT,
    IN new_salary DECIMAL(10,2),
    OUT result_message VARCHAR(255)
)
BEGIN
    DECLARE current_salary DECIMAL(10,2);
    DECLARE EXIT HANDLER FOR SQLEXCEPTION
    BEGIN
        ROLLBACK;
        SET result_message = 'Error occurred during salary update';
    END;
    
    START TRANSACTION;
    
    -- 检查员工是否存在
    SELECT salary INTO current_salary
    FROM employees
    WHERE employee_id = emp_id;
    
    IF current_salary IS NULL THEN
        SET result_message = 'Employee not found';
        ROLLBACK;
    ELSE
        -- 更新薪资
        UPDATE employees
        SET salary = new_salary,
            updated_at = NOW()
        WHERE employee_id = emp_id;
        
        SET result_message = CONCAT('Salary updated from ', current_salary, ' to ', new_salary);
        COMMIT;
    END IF;
END //
DELIMITER ;

3.3 PostgreSQL存储过程

-- PostgreSQL 11+开始支持真正的存储过程
CREATE OR REPLACE PROCEDURE update_employee_salary(
    emp_id INTEGER,
    new_salary NUMERIC
)
LANGUAGE plpgsql
AS $$
DECLARE
    current_salary NUMERIC;
    result_message TEXT;
BEGIN
    -- 检查员工是否存在
    SELECT salary INTO current_salary
    FROM employees
    WHERE employee_id = emp_id;
    
    IF current_salary IS NULL THEN
        RAISE NOTICE 'Employee not found';
        RETURN;
    END IF;
    
    -- 更新薪资
    UPDATE employees
    SET salary = new_salary,
        updated_at = NOW()
    WHERE employee_id = emp_id;
    
    RAISE NOTICE 'Salary updated from % to %', current_salary, new_salary;
END;
$$;

-- 调用存储过程
CALL update_employee_salary(123, 75000);

-- 带事务控制的存储过程
CREATE OR REPLACE PROCEDURE process_monthly_payroll(
    month_year DATE
)
LANGUAGE plpgsql
AS $$
DECLARE
    emp_record RECORD;
    emp_bonus NUMERIC;
BEGIN
    -- 开始事务
    BEGIN
        -- 遍历活跃员工
        FOR emp_record IN 
            SELECT employee_id, salary 
            FROM employees 
            WHERE status = 'active'
        LOOP
            -- 计算奖金
            emp_bonus := emp_record.salary * 0.1;
            
            -- 插入薪资记录
            INSERT INTO payroll_records (
                employee_id, 
                pay_period, 
                base_salary, 
                bonus, 
                total_pay
            ) VALUES (
                emp_record.employee_id,
                month_year,
                emp_record.salary,
                emp_bonus,
                emp_record.salary + emp_bonus
            );
        END LOOP;
        
        -- 提交事务
        COMMIT;
    EXCEPTION
        WHEN OTHERS THEN
            -- 回滚事务
            ROLLBACK;
            RAISE;
    END;
END;
$$;

-- 使用游标的存储过程
CREATE OR REPLACE PROCEDURE process_employees_batch(
    batch_size INTEGER DEFAULT 100
)
LANGUAGE plpgsql
AS $$
DECLARE
    emp_cursor CURSOR FOR
        SELECT employee_id, first_name, last_name
        FROM employees
        WHERE status = 'active'
        ORDER BY employee_id;
    emp_record RECORD;
    counter INTEGER := 0;
BEGIN
    OPEN emp_cursor;
    
    LOOP
        FETCH emp_cursor INTO emp_record;
        EXIT WHEN NOT FOUND;
        
        -- 处理员工记录
        UPDATE employees
        SET last_processed = CURRENT_TIMESTAMP
        WHERE employee_id = emp_record.employee_id;
        
        counter := counter + 1;
        
        -- 批量提交
        IF counter % batch_size = 0 THEN
            COMMIT;
        END IF;
    END LOOP;
    
    CLOSE emp_cursor;
    
    -- 最终提交剩余记录
    COMMIT;
    
    RAISE NOTICE 'Processed % employee records', counter;
END;
$$;

3.4 存储过程最佳实践

-- MySQL最佳实践
DELIMITER //
CREATE PROCEDURE SafeEmployeeUpdate(
    IN p_employee_id INT,
    IN p_first_name VARCHAR(50),
    IN p_last_name VARCHAR(50),
    IN p_email VARCHAR(100),
    OUT p_result VARCHAR(255)
)
MODIFIES SQL DATA
SQL SECURITY DEFINER
BEGIN
    DECLARE EXIT HANDLER FOR SQLEXCEPTION
    BEGIN
        ROLLBACK;
        GET DIAGNOSTICS CONDITION 1
            p_result = MESSAGE_TEXT;
    END;
    
    START TRANSACTION;
    
    -- 输入验证
    IF p_first_name IS NULL OR LENGTH(TRIM(p_first_name)) = 0 THEN
        SET p_result = 'First name cannot be empty';
        ROLLBACK;
        LEAVE proc_label;
    END IF;
    
    -- 更新员工信息
    UPDATE employees
    SET 
        first_name = p_first_name,
        last_name = p_last_name,
        email = p_email,
        updated_at = NOW()
    WHERE employee_id = p_employee_id;
    
    IF ROW_COUNT() = 0 THEN
        SET p_result = 'No employee found with given ID';
        ROLLBACK;
    ELSE
        SET p_result = 'Employee updated successfully';
        COMMIT;
    END IF;
    
    proc_label: BEGIN END;
END //
DELIMITER ;

-- PostgreSQL最佳实践
CREATE OR REPLACE PROCEDURE validate_and_update_employee(
    p_employee_id INTEGER,
    p_first_name TEXT,
    p_last_name TEXT,
    p_email TEXT
)
LANGUAGE plpgsql
SECURITY DEFINER
AS $$
DECLARE
    v_old_email TEXT;
    v_result TEXT;
BEGIN
    -- 检查输入参数
    IF p_first_name IS NULL OR LENGTH(TRIM(p_first_name)) = 0 THEN
        RAISE EXCEPTION 'First name cannot be empty';
    END IF;
    
    -- 记录原始邮箱用于审计
    SELECT email INTO v_old_email
    FROM employees
    WHERE employee_id = p_employee_id;
    
    -- 更新员工信息
    UPDATE employees
    SET 
        first_name = TRIM(p_first_name),
        last_name = TRIM(p_last_name),
        email = LOWER(TRIM(p_email)),
        updated_at = CURRENT_TIMESTAMP
    WHERE employee_id = p_employee_id;
    
    -- 检查是否成功更新
    IF NOT FOUND THEN
        RAISE EXCEPTION 'Employee with ID % not found', p_employee_id;
    END IF;
    
    -- 记录变更日志
    INSERT INTO employee_audit_log (
        employee_id,
        field_changed,
        old_value,
        new_value,
        changed_at,
        changed_by
    ) VALUES (
        p_employee_id,
        'EMAIL',
        v_old_email,
        LOWER(TRIM(p_email)),
        CURRENT_TIMESTAMP,
        CURRENT_USER
    );
    
    COMMIT;
EXCEPTION
    WHEN OTHERS THEN
        ROLLBACK;
        RAISE;
END;
$$;

4. 触发器（Triggers）

4.1 MySQL触发器

-- 创建审计触发器
DELIMITER //
CREATE TRIGGER employee_audit_trigger
AFTER UPDATE ON employees
FOR EACH ROW
BEGIN
    INSERT INTO employee_audit (
        employee_id,
        old_first_name,
        new_first_name,
        old_last_name,
        new_last_name,
        old_salary,
        new_salary,
        change_date,
        changed_by
    ) VALUES (
        NEW.employee_id,
        OLD.first_name,
        NEW.first_name,
        OLD.last_name,
        NEW.last_name,
        OLD.salary,
        NEW.salary,
        NOW(),
        USER()
    );
END //
DELIMITER ;

-- 删除触发器
DROP TRIGGER IF EXISTS employee_audit_trigger;

-- BEFORE触发器示例
DELIMITER //
CREATE TRIGGER employee_validation_trigger
BEFORE INSERT ON employees
FOR EACH ROW
BEGIN
    -- 验证薪资范围
    IF NEW.salary < 30000 THEN
        SET NEW.salary = 30000;
    END IF;
    
    -- 格式化邮箱
    SET NEW.email = LOWER(TRIM(NEW.email));
    
    -- 设置默认入职日期
    IF NEW.hire_date IS NULL THEN
        SET NEW.hire_date = CURDATE();
    END IF;
END //
DELIMITER ;

4.2 PostgreSQL触发器

-- 创建触发器函数
CREATE OR REPLACE FUNCTION audit_employee_changes()
RETURNS TRIGGER AS $$
BEGIN
    -- 处理更新操作
    IF TG_OP = 'UPDATE' THEN
        INSERT INTO employee_audit (
            employee_id,
            old_first_name,
            new_first_name,
            old_last_name,
            new_last_name,
            old_salary,
            new_salary,
            change_date,
            changed_by
        ) VALUES (
            NEW.employee_id,
            OLD.first_name,
            NEW.first_name,
            OLD.last_name,
            NEW.last_name,
            OLD.salary,
            NEW.salary,
            CURRENT_TIMESTAMP,
            CURRENT_USER
        );
        RETURN NEW;
    
    -- 处理插入操作
    ELSIF TG_OP = 'INSERT' THEN
        INSERT INTO employee_audit (
            employee_id,
            new_first_name,
            new_last_name,
            new_salary,
            change_date,
            changed_by,
            operation
        ) VALUES (
            NEW.employee_id,
            NEW.first_name,
            NEW.last_name,
            NEW.salary,
            CURRENT_TIMESTAMP,
            CURRENT_USER,
            'INSERT'
        );
        RETURN NEW;
    
    -- 处理删除操作
    ELSIF TG_OP = 'DELETE' THEN
        INSERT INTO employee_audit (
            employee_id,
            old_first_name,
            old_last_name,
            old_salary,
            change_date,
            changed_by,
            operation
        ) VALUES (
            OLD.employee_id,
            OLD.first_name,
            OLD.last_name,
            OLD.salary,
            CURRENT_TIMESTAMP,
            CURRENT_USER,
            'DELETE'
        );
        RETURN OLD;
    END IF;
    
    RETURN NULL;
END;
$$ LANGUAGE plpgsql;

-- 创建触发器
CREATE TRIGGER employee_audit_trigger
    AFTER INSERT OR UPDATE OR DELETE ON employees
    FOR EACH ROW
    EXECUTE FUNCTION audit_employee_changes();

-- 删除触发器
DROP TRIGGER IF EXISTS employee_audit_trigger ON employees;

-- BEFORE触发器示例
CREATE OR REPLACE FUNCTION validate_employee_insert()
RETURNS TRIGGER AS $$
BEGIN
    -- 验证薪资范围
    IF NEW.salary < 30000 THEN
        RAISE EXCEPTION 'Salary cannot be less than 30000, given: %', NEW.salary;
    END IF;
    
    -- 格式化邮箱
    NEW.email := LOWER(TRIM(NEW.email));
    
    -- 设置默认入职日期
    IF NEW.hire_date IS NULL THEN
        NEW.hire_date := CURRENT_DATE;
    END IF;
    
    RETURN NEW;
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER employee_validation_trigger
    BEFORE INSERT OR UPDATE ON employees
    FOR EACH ROW
    EXECUTE FUNCTION validate_employee_insert();

5. MySQL与PostgreSQL实现差异总结

特性MySQLPostgreSQL
函数返回值必须有RETURN语句多种返回方式
存储过程8.0+完整支持11+支持，之前用函数模拟
触发器CREATE TRIGGER语法CREATE TRIGGER语法
游标支持支持（过程内）支持（函数和过程）
异常处理DECLARE HANDLEREXCEPTION块
事务控制START/COMMIT/ROLLBACKBEGIN/COMMIT/ROLLBACK
语言支持主要是SQL和CPL/pgSQL, Python, Perl等
视图算法MERGE/TEMPTABLE/UNDEFINED规划器自动优化
物化视图不支持原生支持

6. 性能优化和最佳实践

6.1 性能优化建议

-- MySQL性能优化
-- 1. 避免在函数中进行复杂查询
-- 2. 使用适当的索引
-- 3. 限制结果集大小
-- 4. 避免递归调用

-- PostgreSQL性能优化
-- 1. 使用IMMUTABLE函数进行优化
-- 2. 合理使用并行处理
-- 3. 优化触发器逻辑
-- 4. 使用适当的数据类型

-- 通用优化技巧
-- 1. 预编译语句
-- 2. 批量处理
-- 3. 合理的错误处理
-- 4. 适当的日志记录

6.2 安全最佳实践

-- 使用适当的安全上下文
-- MySQL
SQL SECURITY INVOKER  -- 以调用者权限执行
SQL SECURITY DEFINER  -- 以定义者权限执行

-- PostgreSQL
SECURITY DEFINER
SECURITY INVOKER

-- 权限管理
-- 只授予必要的权限
GRANT EXECUTE ON FUNCTION calculate_bonus TO app_user;
GRANT EXECUTE ON PROCEDURE update_employee_salary TO app_user;

相关教程

使用视图来抽象复杂查询，使用函数来封装计算逻辑，使用存储过程来处理业务事务。确保适当的错误处理和安全措施。

总结

视图、函数和存储过程是数据库编程的重要组成部分：

  • 视图：提供数据抽象和安全层，简化复杂查询
  • 函数：封装计算逻辑，返回单一值
  • 存储过程：执行复杂业务逻辑，支持事务控制
  • 触发器：自动执行特定操作，用于审计和数据完整性

在MySQL和PostgreSQL中，这些功能都有很好的支持，但实现细节有所不同。选择使用哪种数据库对象应基于具体的业务需求、性能要求和安全考虑。理解这些对象的特性和最佳实践对于构建高效、安全的数据库应用程序至关重要。