Oracle中KEY关键字的作用及PostgreSQL等价替代与代码迁移方案

一、Oracle中KEY关键字的主要用途 #

• 约束定义：作为PRIMARY KEY、FOREIGN KEY约束的核心组成部分，用于声明主键（保证行唯一性）和外键（建立表间关联，维护参照完整性）。例如：

  CREATE TABLE employees (
    employee_id NUMBER PRIMARY KEY,
    department_id NUMBER,
    FOREIGN KEY (department_id) REFERENCES departments(department_id)
  );
  

• 游标锁定优化：在FOR UPDATE OF KEY子句中使用，锁定游标返回行的主键列，而非整行，减少锁粒度，提升并发性能。例如：

  DECLARE
    CURSOR emp_cursor IS
      SELECT employee_id, salary FROM employees WHERE department_id = 10
      FOR UPDATE OF KEY;
  BEGIN
    -- 游标处理逻辑
  END;
  

二、PostgreSQL中的等价替代 #

• 约束场景：PostgreSQL完全支持PRIMARY KEY和FOREIGN KEY语法，与Oracle几乎一致，无需修改约束定义逻辑。
• 游标锁定场景：PostgreSQL没有FOR UPDATE OF KEY的直接语法，若需类似锁定主键列的效果，可直接指定主键列进行锁定（PostgreSQL行锁机制默认锁定整行，但显式指定主键列可明确锁定范围），例如：

  DECLARE
    CURSOR emp_cursor IS
      SELECT employee_id, salary FROM employees WHERE department_id = 10
      FOR UPDATE OF employee_id; -- 明确锁定主键列
  BEGIN
    -- 游标处理逻辑
  END;
  

三、Oracle游标变量函数迁移至PostgreSQL的等价方案 #

PostgreSQL不支持Oracle风格的游标变量（REF CURSOR），需根据函数的功能采用以下两种常见方案：

方案1：返回结果集（替代返回游标变量的函数） #

若Oracle函数用于返回查询结果的游标，PostgreSQL可通过返回SETOF类型或TABLE结构实现等价功能。

Oracle原示例代码：

CREATE OR REPLACE PACKAGE emp_pkg IS
  TYPE emp_cursor IS REF CURSOR RETURN employees%ROWTYPE;
  FUNCTION get_emp_by_dept(p_dept_id NUMBER) RETURN emp_cursor;
END emp_pkg;

CREATE OR REPLACE PACKAGE BODY emp_pkg IS
  FUNCTION get_emp_by_dept(p_dept_id NUMBER) RETURN emp_cursor IS
    v_cursor emp_cursor;
  BEGIN
    OPEN v_cursor FOR
      SELECT employee_id, first_name, last_name, salary
      FROM employees
      WHERE department_id = p_dept_id;
    RETURN v_cursor;
  END get_emp_by_dept;
END emp_pkg;

PostgreSQL等价实现：

-- 方式1：直接返回TABLE结构
CREATE OR REPLACE FUNCTION get_emp_by_dept(p_dept_id INTEGER)
RETURNS TABLE(employee_id INTEGER, first_name VARCHAR, last_name VARCHAR, salary NUMERIC) AS $$
BEGIN
  RETURN QUERY
    SELECT employee_id, first_name, last_name, salary
    FROM employees
    WHERE department_id = p_dept_id;
END;
$$ LANGUAGE plpgsql;

-- 方式2：返回自定义类型的集合（适合复用类型场景）
CREATE TYPE emp_record AS (
  employee_id INTEGER,
  first_name VARCHAR,
  last_name VARCHAR,
  salary NUMERIC
);

CREATE OR REPLACE FUNCTION get_emp_by_dept(p_dept_id INTEGER)
RETURNS SETOF emp_record AS $$
BEGIN
  RETURN QUERY
    SELECT employee_id, first_name, last_name, salary
    FROM employees
    WHERE department_id = p_dept_id;
END;
$$ LANGUAGE plpgsql;

方案2：遍历结果集（替代内部使用游标变量的逻辑） #

若Oracle函数内部通过游标变量循环处理数据，PostgreSQL可直接用FOR ... IN SELECT语法遍历结果，无需显式游标变量。

Oracle原示例代码：

CREATE OR REPLACE PROCEDURE update_emp_salary(p_dept_id NUMBER) IS
  v_cursor emp_pkg.emp_cursor;
  v_emp employees%ROWTYPE;
BEGIN
  OPEN v_cursor FOR SELECT * FROM employees WHERE department_id = p_dept_id;
  LOOP
    FETCH v_cursor INTO v_emp;
    EXIT WHEN v_cursor%NOTFOUND;
    UPDATE employees SET salary = salary * 1.05 WHERE employee_id = v_emp.employee_id;
  END LOOP;
  CLOSE v_cursor;
END;

PostgreSQL等价实现：

CREATE OR REPLACE PROCEDURE update_emp_salary(p_dept_id INTEGER) AS $$
DECLARE
  v_emp employees%ROWTYPE;
BEGIN
  -- 直接遍历查询结果，无需显式游标变量
  FOR v_emp IN SELECT * FROM employees WHERE department_id = p_dept_id LOOP
    UPDATE employees SET salary = salary * 1.05 WHERE employee_id = v_emp.employee_id;
  END LOOP;
END;
$$ LANGUAGE plpgsql;

内容的提问来源于stack exchange，提问作者Ananthu rajeev