PostgreSQL 存储过程 #

┌─────────────────────────────────────────────────────────────┐
│                    函数 vs 存储过程                          │
├─────────────────────────────────────────────────────────────┤
│                                                             │
│  函数（FUNCTION）                                           │
│  ├── 必须返回值                                            │
│  ├── 可以在 SQL 语句中调用                                 │
│  ├── 不能执行事务控制                                      │
│  └── 适合计算、转换                                        │
│                                                             │
│  存储过程（PROCEDURE）                                      │
│  ├── 可以不返回值                                          │
│  ├── 使用 CALL 调用                                        │
│  ├── 可以执行事务控制                                      │
│  └── 适合业务逻辑、批量操作                                │
│                                                             │
└─────────────────────────────────────────────────────────────┘

-- 创建简单函数
CREATE FUNCTION add_numbers(a INTEGER, b INTEGER)
RETURNS INTEGER AS $$
BEGIN
    RETURN a + b;
END;
$$ LANGUAGE plpgsql;

-- 调用函数
SELECT add_numbers(10, 20);
-- 输出：30

-- 在查询中使用
SELECT add_numbers(id, 100) FROM employees;

-- 带默认值的参数
CREATE FUNCTION greet(name VARCHAR, greeting VARCHAR DEFAULT 'Hello')
RETURNS VARCHAR AS $$
BEGIN
    RETURN greeting || ', ' || name || '!';
END;
$$ LANGUAGE plpgsql;

SELECT greet('Alice');              -- Hello, Alice!
SELECT greet('Alice', 'Hi');        -- Hi, Alice!

-- 使用命名参数
SELECT greet(greeting := 'Welcome', name := 'Bob');

-- OUT 参数
CREATE FUNCTION get_employee_info(
    emp_id INTEGER,
    OUT emp_name VARCHAR,
    OUT emp_salary DECIMAL
) AS $$
BEGIN
    SELECT name, salary INTO emp_name, emp_salary
    FROM employees WHERE id = emp_id;
END;
$$ LANGUAGE plpgsql;

SELECT * FROM get_employee_info(1);

-- INOUT 参数
CREATE FUNCTION double_value(INOUT value INTEGER) AS $$
BEGIN
    value := value * 2;
END;
$$ LANGUAGE plpgsql;

SELECT double_value(5);  -- 10

-- 返回多行（RETURNS TABLE）
CREATE FUNCTION get_employees_by_dept(dept_name VARCHAR)
RETURNS TABLE (
    id INTEGER,
    name VARCHAR,
    salary DECIMAL
) AS $$
BEGIN
    RETURN QUERY
    SELECT e.id, e.name, e.salary
    FROM employees e
    JOIN departments d ON e.department_id = d.id
    WHERE d.name = dept_name;
END;
$$ LANGUAGE plpgsql;

SELECT * FROM get_employees_by_dept('Engineering');

-- 返回标量
CREATE FUNCTION get_employee_count()
RETURNS INTEGER AS $$
DECLARE
    count INTEGER;
BEGIN
    SELECT COUNT(*) INTO count FROM employees;
    RETURN count;
END;
$$ LANGUAGE plpgsql;

-- 返回记录
CREATE FUNCTION get_employee(emp_id INTEGER)
RETURNS RECORD AS $$
DECLARE
    result RECORD;
BEGIN
    SELECT * INTO result FROM employees WHERE id = emp_id;
    RETURN result;
END;
$$ LANGUAGE plpgsql;

SELECT * FROM get_employee(1) AS (id INTEGER, name VARCHAR, department_id INTEGER, salary DECIMAL, hire_date DATE, is_active BOOLEAN);

-- 返回 SETOF
CREATE FUNCTION get_all_employees()
RETURNS SETOF employees AS $$
BEGIN
    RETURN QUERY SELECT * FROM employees;
END;
$$ LANGUAGE plpgsql;

SELECT * FROM get_all_employees();

CREATE FUNCTION variable_example()
RETURNS VOID AS $$
DECLARE
    -- 基本类型
    v_integer INTEGER := 10;
    v_varchar VARCHAR(100) := 'Hello';
    v_boolean BOOLEAN := TRUE;
    v_decimal DECIMAL(10, 2) := 99.99;
    v_date DATE := CURRENT_DATE;
    
    -- 行类型
    v_employee employees%ROWTYPE;
    
    -- 列类型
    v_name employees.name%TYPE;
    
    -- 记录类型
    v_record RECORD;
    
    -- 数组
    v_array INTEGER[] := ARRAY[1, 2, 3];
    
    -- 常量
    v_constant CONSTANT INTEGER := 100;
BEGIN
    -- 使用变量
    v_name := 'Alice';
    
    -- 从查询赋值
    SELECT * INTO v_employee FROM employees WHERE id = 1;
    
    -- 使用行类型字段
    RAISE NOTICE 'Employee name: %', v_employee.name;
END;
$$ LANGUAGE plpgsql;

-- IF 语句
CREATE FUNCTION check_salary(salary DECIMAL)
RETURNS VARCHAR AS $$
BEGIN
    IF salary >= 80000 THEN
        RETURN 'High';
    ELSIF salary >= 60000 THEN
        RETURN 'Medium';
    ELSE
        RETURN 'Low';
    END IF;
END;
$$ LANGUAGE plpgsql;

-- CASE 语句
CREATE FUNCTION get_department_name(dept_id INTEGER)
RETURNS VARCHAR AS $$
BEGIN
    CASE dept_id
        WHEN 1 THEN RETURN 'Engineering';
        WHEN 2 THEN RETURN 'Sales';
        WHEN 3 THEN RETURN 'Marketing';
        ELSE RETURN 'Unknown';
    END CASE;
END;
$$ LANGUAGE plpgsql;

-- 循环
CREATE FUNCTION loop_example()
RETURNS VOID AS $$
DECLARE
    i INTEGER := 0;
BEGIN
    -- 基本循环
    LOOP
        i := i + 1;
        EXIT WHEN i > 5;
        RAISE NOTICE 'i = %', i;
    END LOOP;
    
    -- WHILE 循环
    i := 0;
    WHILE i < 5 LOOP
        i := i + 1;
        RAISE NOTICE 'while i = %', i;
    END LOOP;
    
    -- FOR 循环（整数）
    FOR i IN 1..5 LOOP
        RAISE NOTICE 'for i = %', i;
    END LOOP;
    
    -- FOR 循环（反向）
    FOR i IN REVERSE 5..1 LOOP
        RAISE NOTICE 'reverse i = %', i;
    END LOOP;
    
    -- FOR 循环（查询结果）
    DECLARE
        emp RECORD;
    BEGIN
        FOR emp IN SELECT * FROM employees LOOP
            RAISE NOTICE 'Employee: %, Salary: %', emp.name, emp.salary;
        END LOOP;
    END;
END;
$$ LANGUAGE plpgsql;

-- CONTINUE
CREATE FUNCTION continue_example()
RETURNS VOID AS $$
BEGIN
    FOR i IN 1..10 LOOP
        CONTINUE WHEN i % 2 = 0;  -- 跳过偶数
        RAISE NOTICE 'Odd number: %', i;
    END LOOP;
END;
$$ LANGUAGE plpgsql;

CREATE FUNCTION safe_divide(a DECIMAL, b DECIMAL)
RETURNS DECIMAL AS $$
BEGIN
    RETURN a / b;
EXCEPTION
    WHEN division_by_zero THEN
        RAISE NOTICE 'Division by zero attempted';
        RETURN NULL;
    WHEN OTHERS THEN
        RAISE NOTICE 'Error: %', SQLERRM;
        RETURN NULL;
END;
$$ LANGUAGE plpgsql;

-- 自定义异常
CREATE FUNCTION check_employee_age(age INTEGER)
RETURNS VOID AS $$
BEGIN
    IF age < 18 THEN
        RAISE EXCEPTION 'Employee must be at least 18 years old';
    ELSIF age > 65 THEN
        RAISE EXCEPTION 'Employee age cannot exceed 65';
    END IF;
    
    RAISE NOTICE 'Age is valid: %', age;
END;
$$ LANGUAGE plpgsql;

CREATE FUNCTION process_employees()
RETURNS VOID AS $$
DECLARE
    emp_cursor CURSOR FOR SELECT * FROM employees;
    emp RECORD;
BEGIN
    OPEN emp_cursor;
    
    LOOP
        FETCH emp_cursor INTO emp;
        EXIT WHEN NOT FOUND;
        
        RAISE NOTICE 'Processing: %, Salary: %', emp.name, emp.salary;
        
        -- 可以执行复杂逻辑
        IF emp.salary < 50000 THEN
            UPDATE employees SET salary = salary * 1.1 WHERE id = emp.id;
        END IF;
    END LOOP;
    
    CLOSE emp_cursor;
END;
$$ LANGUAGE plpgsql;

CREATE FUNCTION process_employees_by_dept(dept_id INTEGER)
RETURNS VOID AS $$
DECLARE
    emp_cursor CURSOR (d_id INTEGER) FOR 
        SELECT * FROM employees WHERE department_id = d_id;
    emp RECORD;
BEGIN
    OPEN emp_cursor(dept_id);
    
    LOOP
        FETCH emp_cursor INTO emp;
        EXIT WHEN NOT FOUND;
        RAISE NOTICE 'Employee: %', emp.name;
    END LOOP;
    
    CLOSE emp_cursor;
END;
$$ LANGUAGE plpgsql;

CREATE FUNCTION process_all_employees()
RETURNS VOID AS $$
DECLARE
    emp RECORD;
BEGIN
    FOR emp IN SELECT * FROM employees ORDER BY salary DESC LOOP
        RAISE NOTICE 'Employee: %, Salary: %', emp.name, emp.salary;
    END LOOP;
END;
$$ LANGUAGE plpgsql;

-- 创建存储过程
CREATE PROCEDURE transfer_salary(
    from_emp_id INTEGER,
    to_emp_id INTEGER,
    amount DECIMAL
) AS $$
DECLARE
    from_salary DECIMAL;
BEGIN
    -- 获取源员工薪资
    SELECT salary INTO from_salary FROM employees WHERE id = from_emp_id;
    
    IF from_salary < amount THEN
        RAISE EXCEPTION 'Insufficient salary';
    END IF;
    
    -- 执行转账
    UPDATE employees SET salary = salary - amount WHERE id = from_emp_id;
    UPDATE employees SET salary = salary + amount WHERE id = to_emp_id;
    
    RAISE NOTICE 'Transferred % from employee % to employee %', amount, from_emp_id, to_emp_id;
END;
$$ LANGUAGE plpgsql;

-- 调用存储过程
CALL transfer_salary(1, 2, 5000.00);

CREATE PROCEDURE batch_update_employees()
AS $$
BEGIN
    -- 开始事务
    -- 存储过程内可以控制事务
    
    UPDATE employees SET salary = salary * 1.1 WHERE department_id = 1;
    
    COMMIT;
    
    UPDATE employees SET salary = salary * 1.05 WHERE department_id = 2;
    
    COMMIT;
    
    -- 可以回滚部分操作
    -- ROLLBACK;
END;
$$ LANGUAGE plpgsql;

CREATE FUNCTION dynamic_query(table_name VARCHAR, column_name VARCHAR, value VARCHAR)
RETURNS SETOF RECORD AS $$
DECLARE
    query TEXT;
    result RECORD;
BEGIN
    query := format('SELECT * FROM %I WHERE %I = %L', table_name, column_name, value);
    
    FOR result IN EXECUTE query LOOP
        RETURN NEXT result;
    END LOOP;
    
    RETURN;
END;
$$ LANGUAGE plpgsql;

-- 使用动态 SQL 更新
CREATE FUNCTION update_column_value(
    table_name VARCHAR,
    column_name VARCHAR,
    old_value VARCHAR,
    new_value VARCHAR
) RETURNS INTEGER AS $$
DECLARE
    query TEXT;
    affected_rows INTEGER;
BEGIN
    query := format(
        'UPDATE %I SET %I = %L WHERE %I = %L',
        table_name, column_name, new_value, column_name, old_value
    );
    
    EXECUTE query;
    GET DIAGNOSTICS affected_rows = ROW_COUNT;
    
    RETURN affected_rows;
END;
$$ LANGUAGE plpgsql;

-- 创建触发器函数
CREATE OR REPLACE FUNCTION update_modified_at()
RETURNS TRIGGER AS $$
BEGIN
    NEW.updated_at := CURRENT_TIMESTAMP;
    RETURN NEW;
END;
$$ LANGUAGE plpgsql;

-- 创建表
CREATE TABLE products (
    id SERIAL PRIMARY KEY,
    name VARCHAR(100),
    price DECIMAL(10, 2),
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

-- 创建触发器
CREATE TRIGGER trg_products_updated_at
    BEFORE UPDATE ON products
    FOR EACH ROW
    EXECUTE FUNCTION update_modified_at();

-- 查看所有函数
SELECT 
    proname AS function_name,
    pg_get_functiondef(oid) AS definition
FROM pg_proc
WHERE pronamespace = (SELECT oid FROM pg_namespace WHERE nspname = 'public');

-- 使用 psql 命令
\df
\df+ function_name

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

-- 删除函数
DROP FUNCTION add_numbers(INTEGER, INTEGER);

-- 如果存在则删除
DROP FUNCTION IF EXISTS add_numbers(INTEGER, INTEGER);

高级阶段
├── 索引
├── 视图
├── 存储过程（本文）
├── 触发器
└── 事务与锁