Debian环境下PostgreSQL存储过程编写全教程

一、环境准备：在Debian上安装PostgreSQL

在编写存储过程前，需确保Debian系统已安装PostgreSQL数据库。使用以下命令完成安装：

sudo apt-get update
sudo apt-get install postgresql postgresql-contrib

安装完成后，启动PostgreSQL服务并设置开机自启：

sudo systemctl start postgresql
sudo systemctl enable postgresql

二、连接到PostgreSQL数据库

使用psql命令行工具连接数据库（默认以postgres超级用户身份登录）：

sudo -u postgres psql

若需创建新数据库或用户，可在psql中执行以下命令：

-- 创建数据库
CREATE DATABASE mydb;

-- 创建用户并授予权限
CREATE USER myuser WITH ENCRYPTED PASSWORD 'mypassword';
GRANT ALL PRIVILEGES ON DATABASE mydb TO myuser;

退出psql：输入\q并按回车键。

三、存储过程基础语法

PostgreSQL通过CREATE FUNCTION语句创建存储过程（从PostgreSQL 11开始支持CREATE PROCEDURE语法，用于不返回结果集的过程）。基本语法结构如下：

CREATE OR REPLACE FUNCTION function_name(
    parameter1 datatype [DEFAULT default_value],
    parameter2 datatype [DEFAULT default_value]
)
RETURNS return_datatype
LANGUAGE plpgsql
AS $$
DECLARE
    -- 变量声明（可选）
    variable1 datatype;
    variable2 datatype;
BEGIN
    -- 业务逻辑（SQL语句、控制结构、异常处理等）
    RETURN value; -- 仅RETURNS指定类型时需要
END;
$$;

• CREATE OR REPLACE FUNCTION：创建新函数或替换已有同名函数；
• parameter：输入参数（IN为默认模式，可省略）；
• RETURNS：指定返回值类型（若不返回结果，用RETURNS VOID）；
• LANGUAGE plpgsql：指定使用PL/pgSQL过程语言（PostgreSQL内置，支持复杂逻辑）；
• DECLARE：声明局部变量（可选）；
• BEGIN...END：包裹业务逻辑代码块。

四、简单存储过程示例

1. 无参存储过程：插入测试数据

假设有一个employees表（需提前创建）：

CREATE TABLE employees (
    id SERIAL PRIMARY KEY,
    name VARCHAR(100),
    salary NUMERIC(10, 2)
);

创建无参存储过程insert_employee，向表中插入一条测试数据：

CREATE OR REPLACE FUNCTION insert_employee()
RETURNS VOID
LANGUAGE plpgsql
AS $$
BEGIN
    INSERT INTO employees (name, salary) VALUES ('Alice', 5000.00);
    RAISE NOTICE 'Inserted a new employee: Alice';
END;
$$;

调用方式：

SELECT insert_employee();
-- 或使用CALL（PostgreSQL 11+推荐）
CALL insert_employee();

执行后，employees表将新增一条id=1、name='Alice'、salary=5000.00的记录。

2. 带输入参数的存储过程：计算两数之和

创建存储过程add_numbers，接收两个整数参数并返回它们的和：

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

调用方式：

SELECT add_numbers(10, 20); -- 返回30

3. 带输出参数的存储过程：获取员工总数

创建存储过程get_employee_count，通过输出参数返回employees表的记录数：

CREATE OR REPLACE FUNCTION get_employee_count(out_count OUT INTEGER)
LANGUAGE plpgsql
AS $$
BEGIN
    SELECT COUNT(*) INTO out_count FROM employees;
END;
$$;

调用方式：

CALL get_employee_count(out_count => employee_count);
SELECT employee_count; -- 显示员工总数

五、控制结构与异常处理

1. 条件判断（IF-ELSE）

创建存储过程check_salary，根据员工薪水输出不同提示：

CREATE OR REPLACE FUNCTION check_salary(emp_id INTEGER)
RETURNS VOID
LANGUAGE plpgsql
AS $$
DECLARE
    emp_sal NUMERIC(10, 2);
BEGIN
    SELECT salary INTO emp_sal FROM employees WHERE id = emp_id;
    IF emp_sal > 10000 THEN
        RAISE NOTICE 'Employee % has a high salary: %.2f', emp_id, emp_sal;
    ELSIF emp_sal > 5000 THEN
        RAISE NOTICE 'Employee % has a medium salary: %.2f', emp_id, emp_sal;
    ELSE
        RAISE NOTICE 'Employee % has a low salary: %.2f', emp_id, emp_sal;
    END IF;
END;
$$;

调用方式：

CALL check_salary(1); -- 根据id=1的员工薪水输出提示

2. 循环（FOR）

创建存储过程batch_insert，批量插入10条员工记录：

CREATE OR REPLACE FUNCTION batch_insert()
RETURNS VOID
LANGUAGE plpgsql
AS $$
BEGIN
    FOR i IN 1..10 LOOP
        INSERT INTO employees (name, salary) VALUES ('Employee_' || i, 3000.00 + i * 100);
    END LOOP;
    RAISE NOTICE 'Batch inserted 10 employees';
END;
$$;

调用方式：

CALL batch_insert();

3. 异常处理（TRY-CATCH）

创建存储过程safe_update，尝试更新员工信息，若员工不存在则捕获异常：

CREATE OR REPLACE PROCEDURE safe_update_employee(emp_id INTEGER, new_name VARCHAR, new_salary NUMERIC)
LANGUAGE plpgsql
AS $$
BEGIN
    UPDATE employees SET name = new_name, salary = new_salary WHERE id = emp_id;
    IF NOT FOUND THEN
        RAISE NOTICE 'Employee with ID % not found', emp_id;
    ELSE
        RAISE NOTICE 'Employee % updated successfully', emp_id;
    END IF;
EXCEPTION
    WHEN OTHERS THEN
        RAISE NOTICE 'An error occurred: %', SQLERRM; -- 输出错误信息
END;
$$;

调用方式：

CALL safe_update_employee(999, 'Nonexistent', 10000); -- 若id=999不存在，捕获异常
CALL safe_update_employee(1, 'Alice Updated', 5500); -- 正常更新

六、存储过程管理

1. 查看存储过程

使用以下SQL语句查看数据库中的存储过程：

-- 查看所有函数（包括存储过程）
SELECT proname, prosrc, proretset, proargtypes FROM pg_proc WHERE pronamespace = (SELECT oid FROM pg_namespace WHERE nspname = 'public');

-- 查看特定存储过程的定义
\d+ function_name

2. 修改存储过程

使用CREATE OR REPLACE FUNCTION或CREATE OR REPLACE PROCEDURE语句修改已有存储过程（需保持参数列表和返回类型一致）：

CREATE OR REPLACE FUNCTION add_numbers(a INTEGER, b INTEGER)
RETURNS INTEGER
LANGUAGE plpgsql
AS $$
BEGIN
    RETURN a * b; -- 修改为计算乘积
END;
$$;

3. 删除存储过程

使用DROP FUNCTION或DROP PROCEDURE语句删除存储过程：

DROP FUNCTION IF EXISTS add_numbers(INTEGER, INTEGER);
DROP PROCEDURE IF EXISTS safe_update_employee(INTEGER, VARCHAR, NUMERIC);

七、实战案例：批量更新用户标签

假设有一个user_log表（需提前创建）：

CREATE TABLE user_log (
    id SERIAL PRIMARY KEY,
    user_id INTEGER,
    tag VARCHAR(50)
);

创建存储过程batch_update_tags，批量更新指定用户的标签：

CREATE OR REPLACE PROCEDURE batch_update_tags(tag_name VARCHAR, user_ids INTEGER[])
LANGUAGE plpgsql
AS $$
BEGIN
    FOREACH uid IN ARRAY user_ids LOOP
        UPDATE user_log SET tag = tag_name WHERE user_id = uid;
    END LOOP;
    RAISE NOTICE 'Successfully updated % records', array_length(user_ids, 1);
END;
$$;

调用方式：

CALL batch_update_tags('VIP', ARRAY[1, 2, 3]); -- 将id=1,2,3的用户标签设为VIP

注意事项

1. 权限控制：确保存储过程创建用户有足够的权限（如CREATE权限）；
2. 事务管理：存储过程中的所有操作在同一个事务中执行，若发生错误，整个事务将回滚；
3. 性能优化：避免在循环中执行SQL语句（如批量操作可使用FOREACH结合数组）；
4. 调试技巧：使用RAISE NOTICE输出调试信息，帮助定位问题；
5. 版本兼容：PostgreSQL 11及以上版本支持CREATE PROCEDURE语法，若使用旧版本，需用CREATE FUNCTION替代。