pgSQL存储过程小结

1. 创建存储过程

语法解析

sqlCopy Code

CREATE OR REPLACE PROCEDURE procedure_name(parameter_list)

LANGUAGE plpgsql

AS $$

BEGIN

    -- procedure body

END;

$$;

• CREATE OR REPLACE: 创建新的存储过程，如果存在同名存储过程则替换。
• PROCEDURE procedure_name: 指定存储过程的名称。
• parameter_list: 定义输入（IN）、输出（OUT）或输入输出（INOUT）参数，格式为 param_name data_type。
• LANGUAGE plpgsql: 指定使用 PL/pgSQL 作为存储过程的语言。
• AS

......

: 用于包围存储过程体，$$ 是一个定界符，可以使用其他定界符。

2. 示例：插入员工记录

sqlCopy Code

CREATE OR REPLACE PROCEDURE add_employee(

    emp_name VARCHAR,

    emp_age INT

)

LANGUAGE plpgsql

AS $$

BEGIN

    INSERT INTO employees (name, age) VALUES (emp_name, emp_age);

END;

$$;

• INSERT INTO employees (name, age): 将 emp_name 和 emp_age 插入到 employees 表的 name 和 age 列中。

3. 调用存储过程

sqlCopy Code

CALL add_employee('Jane Smith', 28);

• CALL: 用于执行存储过程。
• 'Jane Smith', 28: 传递给存储过程的参数。

4. 控制结构

条件语句示例

sqlCopy Code

IF new_age < 0 THEN

    RAISE EXCEPTION 'Age cannot be negative';

ELSE

    UPDATE employees SET age = new_age WHERE id = emp_id;

END IF;

• IF ... THEN ... ELSE ... END IF: 条件语句，根据条件执行不同的代码块。
• RAISE EXCEPTION: 抛出异常，终止存储过程并返回错误消息。

循环示例

sqlCopy Code

FOR emp_record IN SELECT name FROM employees LOOP

    RAISE NOTICE 'Employee Name: %', emp_record.name;

END LOOP;

• FOR ... IN ... LOOP: 循环结构，迭代查询结果。
• RAISE NOTICE: 输出信息，用于调试或记录。

5. 异常处理

sqlCopy Code

EXCEPTION

    WHEN unique_violation THEN

        RAISE NOTICE 'Employee % already exists!', emp_name;

• EXCEPTION: 开始异常处理块。
• WHEN unique_violation: 指定处理特定类型的异常（如唯一性约束冲突）。
• SQLERRM: 提供错误信息的内置变量。

6. 返回结果

OUT 参数示例

sqlCopy Code

CREATE OR REPLACE PROCEDURE count_employees(OUT emp_count INT)

LANGUAGE plpgsql

AS $$

BEGIN

    SELECT COUNT(*) INTO emp_count FROM employees;

END;

$$;

• OUT emp_count INT: 定义一个输出参数，存储员工总数。
• SELECT COUNT(*) INTO emp_count: 将查询结果存入输出参数。

7. 注意事项

• 确保事务控制：在执行多步操作时，考虑使用 BEGIN 和 COMMIT。
• 确保错误处理：合理处理异常以增强存储过程的健壮性。