Oracle 存过 与Postgresql 的存过的差别

一、Oracle 存储过程

CREATE OR REPLACE PROCEDURE display_employees_with_cursor AS  
    -- 声明游标  
    CURSOR emp_cursor IS  
        SELECT employee_id, first_name, salary FROM employees;  
  
    -- 声明变量来存储从游标中检索的数据  
    v_employee_id   employees.employee_id%TYPE;  
    v_first_name    employees.first_name%TYPE;  
    v_salary        employees.salary%TYPE;  
BEGIN  
    -- 打开游标  
    OPEN emp_cursor;  
  
    -- 循环遍历游标中的每一行  
    LOOP  
        -- 从游标中检索数据  
        FETCH emp_cursor INTO v_employee_id, v_first_name, v_salary;  
  
        -- 退出循环的条件：如果没有更多的数据可检索（即游标已到达末尾）  
        EXIT WHEN emp_cursor%NOTFOUND;  
  
        -- 在这里处理每一行的数据。例如，我们可以简单地将它们打印到控制台（注意：在Oracle PL/SQL中，通常不会直接打印到控制台，但这里只是作为示例）  
        DBMS_OUTPUT.PUT_LINE('Employee ID: ' || v_employee_id || ', Name: ' || v_first_name || ', Salary: ' || TO_CHAR(v_salary));  
    END LOOP;  
  
    -- 关闭游标  
    CLOSE emp_cursor;  
EXCEPTION  
    WHEN OTHERS THEN  
        -- 异常处理：如果发生任何错误，关闭游标并重新抛出异常  
        IF emp_cursor%ISOPEN THEN  
            CLOSE emp_cursor;  
        END IF;  
        RAISE;  
END display_employees_with_cursor;

Oracle的变量申明弱限制，Pgsql 需要强制先申明后使用。

二、Pgsql 存过过程

CREATE OR REPLACE FUNCTION process_with_cursor()  
RETURNS void AS 
$$
  
DECLARE  
    row_record RECORD;  
    my_cursor CURSOR FOR SELECT * FROM your_table_name; -- 替换 your_table_name 为你的表名  
BEGIN  
    -- 打开游标  
    OPEN my_cursor;  
  
    -- 循环遍历游标中的每一行  
    LOOP  
        -- 从游标中获取一行数据  
        FETCH my_cursor INTO row_record;  
  
        -- 退出循环的条件：如果没有更多的数据可检索（即游标已到达末尾）  
        EXIT WHEN NOT FOUND;  
  
        -- 在这里处理每一行的数据。例如，我们可以将数据输出到日志中  
        RAISE NOTICE 'Processing row: ID = %, Name = %', row_record.id, row_record.name; -- 假设表有 id 和 name 列  
    END LOOP;  
  
    -- 关闭游标  
    CLOSE my_cursor;  
EXCEPTION  
    WHEN OTHERS THEN  
        -- 如果发生异常，确保游标被关闭  
        IF my_cursor%ISOPEN THEN  
            CLOSE my_cursor;  
        END IF;  
        -- 重新抛出异常  
        RAISE;  
END;  
 
$$
 LANGUAGE plpgsql;

调用这个函数的 SQL 语句如下：

SELECT process_with_cursor();

但是请注意，这个函数不会返回任何结果集给调用者，它只是将处理过程中的信息输出到 PostgreSQL 的日志中。

如果你想要使用 PostgreSQL 16 引入的过程（Procedures），过程与函数类似，但过程不返回任何值（即它们没有返回类型）。以下是一个使用过程的样例：

sql
复制代码

CREATE OR REPLACE PROCEDURE process_with_cursor_procedure()  
LANGUAGE plpgsql AS 
$$
  
DECLARE  
    row_record RECORD;  
    my_cursor CURSOR FOR SELECT * FROM your_table_name; -- 替换 your_table_name 为你的表名  
BEGIN  
    -- ... 与上面的函数相同的游标处理逻辑 ...  
END;  
 
$$
;

调用这个过程的 SQL 语句是：

CALL process_with_cursor_procedure();

三、差异

Oracle 11g的存储过程（Stored Procedure）和PostgreSQL 16的存储过程（在PostgreSQL中通常称为函数或过程，但从PostgreSQL 11开始正式引入了存储过程的概念）之间存在一些异同点。以下是它们之间的一些主要区别：

相似点：
1、封装逻辑：两者都允许将复杂的SQL逻辑封装在可重用的单元中，以便在多个地方调用。
2、参数化：存储过程和函数都可以接受参数，这使得它们更加灵活和可重用。
3、性能优化：由于存储过程和函数是预编译的，因此在多次调用时可以提供更好的性能。
4、安全性：它们都可以用来隐藏数据库表结构或复杂性，从而提供某种程度的安全性。
5、错误处理：两者都支持异常处理，可以在存储过程或函数内部捕获和处理错误。

不同点：
1、语法差异：Oracle和PostgreSQL使用不同的SQL方言，因此在编写存储过程或函数时会有语法差异。
Oracle使用PL/SQL作为存储过程和函数的编程语言。
PostgreSQL使用PL/pgSQL（或其他支持的过程语言）作为函数的编程语言，而从PostgreSQL 11开始，也支持使用SQL语言编写存储过程。

2、返回类型：
在Oracle中，存储过程通常不返回值（或结果集），但可以通过输出参数返回数据。函数可以返回一个值或结果集。
在PostgreSQL中，函数可以返回一个值、一个表或一组行（即结果集）。从PostgreSQL 11开始引入的存储过程也不直接返回值，但可以通过输出参数或OUT参数返回数据。

3、调用方式：
在Oracle中，存储过程通常使用EXECUTE命令或直接在PL/SQL块中调用。
在PostgreSQL中，函数可以使用SELECT语句调用（如果它们返回结果集），而存储过程可以使用CALL语句调用。

4、错误处理：虽然两者都支持异常处理，但具体的语法和用法可能有所不同。

5、工具和生态系统：Oracle和PostgreSQL有不同的工具和生态系统，这可能会影响存储过程和函数的开发、部署和管理。

6、移植性：由于语法和功能的差异，Oracle的存储过程可能无法直接移植到PostgreSQL，而需要进行一些修改。同样，PostgreSQL的函数或存储过程也可能需要进行修改才能在Oracle上运行。

总的来说，虽然Oracle 11g的存储过程和PostgreSQL 16的存储过程（或函数）在概念上相似，但在具体实现和使用上存在一些差异。