迁移 Oracle 到 PostgreSQL: 存储过程和函数

七月 17, 2023

PL/SQL是Oracle内置的数据库编程语言，提供了几种方法来存储和运行数据库中可重用的业务逻辑。过程和函数是使用CREATE PROCEDURE和CREATE FUNCTION语句创建的可重用代码片段。

Oracle 用法

存储过程和存储函数是由 SQL 和 PL/SQL 语句组成的 PL/SQL 代码单元，用于解决特定问题或执行一组相关任务。

过程用于使用 PL/SQL 执行数据库操作。

函数用于执行计算并返回结果。

创建过程和函数的权限

若要在自己的架构中创建过程和函数，Oracle 数据库用户需要CREATE PROCEDURE系统权限。

若要在其他架构中创建过程或函数，数据库用户需要CREATE ANY PROCEDURE权限。

要运行过程或函数，数据库用户需要EXECUTE权限。

包规格和包体

除了存储过程和函数之外，Oracle 还提供包来封装相关的过程、函数和其他程序对象。

包规格声明并描述了所有相关的PL/SQL元素。

包体包含可执行代码。

若要运行在包中创建的存储过程或函数，请指定包名称和存储过程或函数名称。

EXEC PKG_EMP.CALCULTE_SAL('100');

例子

使用CREATE OR REPLACE PROCEDURE语句创建 Oracle 存储过程。可选的OR REPLACE子句将覆盖具有相同名称的现有存储过程（如果存在）。

CREATE OR REPLACE PROCEDURE EMP_SAL_RAISE
(P_EMP_ID IN NUMBER, SAL_RAISE IN NUMBER)
AS
  V_EMP_CURRENT_SAL NUMBER;
BEGIN
  SELECT SALARY INTO V_EMP_CURRENT_SAL
    FROM EMPLOYEES WHERE EMPLOYEE_ID=P_EMP_ID;
  UPDATE EMPLOYEES
    SET SALARY=V_EMP_CURRENT_SAL+SAL_RAISE
    WHERE EMPLOYEE_ID=P_EMP_ID;
  DBMS_OUTPUT.PUT_LINE('New Salary For Employee ID: ' || P_EMP_ID ||
                       ' Is ' || (V_EMP_CURRENT_SAL + SAL_RAISE));
EXCEPTION WHEN OTHERS THEN
  RAISE_APPLICATION_ERROR(-20001, 'An error was encountered - ' ||
                          SQLCODE || ' -ERROR-' || SQLERRM);
  ROLLBACK;
  COMMIT;
END;
/
-- Run
EXEC EMP_SAL_RAISE(200, 1000);

使用CREATE OR REPLACE FUNCTION语句创建一个函数。

CREATE OR REPLACE FUNCTION EMP_PERIOD_OF_SERVICE_YEAR
(P_EMP_ID NUMBER)
RETURN NUMBER
AS
  V_PERIOD_OF_SERVICE_YEARS NUMBER;
BEGIN
  SELECT EXTRACT(YEAR FROM SYSDATE) - EXTRACT(YEAR FROM TO_DATE(HIRE_DATE))
    INTO V_PERIOD_OF_SERVICE_YEARS
    FROM EMPLOYEES
    WHERE EMPLOYEE_ID=P_EMP_ID;
  RETURN V_PERIOD_OF_SERVICE_YEARS;
END;
/

SELECT EMPLOYEE_ID, FIRST_NAME,
       EMP_PERIOD_OF_SERVICE_YEAR(EMPLOYEE_ID) AS PERIOD_OF_SERVICE_YEAR
  FROM EMPLOYEES;

EMPLOYEE_ID  FIRST_NAME  PERIOD_OF_SERVICE_YEAR
174          Ellen       13
166          Sundar      9
130          Mozhe       12
105          David       12
204          Hermann     15
116          Shelli      12
167          Amit        9
172          Elizabeth   10

使用CREATE OR REPLACE PACKAGE语句创建包。

CREATE OR REPLACE PACKAGE PCK_CHINOOK_REPORTS
AS
  PROCEDURE GET_ARTIST_BY_ALBUM(P_ARTIST_ID ALBUM.TITLE%TYPE);
  PROCEDURE CUST_INVOICE_BY_YEAR_ANALYZE;
END;

使用CREATE OR REPLACE PACKAGE BODY语句创建新包。

CREATE OR REPLACE PACKAGE BODY PCK_CHINOOK_REPORTS
AS
  PROCEDURE GET_ARTIST_BY_ALBUM(P_ARTIST_ID ALBUM.TITLE%TYPE)
  IS
    V_ARTIST_NAME ARTIST.NAME%TYPE;
  BEGIN
    SELECT ART.NAME INTO V_ARTIST_NAME
      FROM ALBUM ALB JOIN ARTIST ART USING(ARTISTID)
      WHERE ALB.TITLE = P_ARTIST_ID;
    DBMS_OUTPUT.PUT_LINE('ArtistName: ' || V_ARTIST_NAME);
  END;

  PROCEDURE CUST_INVOICE_BY_YEAR_ANALYZE
  AS
    V_CUST_GENRES VARCHAR2(200);
  BEGIN
    FOR V IN
      (SELECT CUSTOMERID, CUSTNAME, LOW_YEAR, HIGH_YEAR, CUST_AVG
         FROM TMP_CUST_INVOICE_ANALYSE)
    LOOP
      IF SUBSTR(V.LOW_YEAR, -4) > SUBSTR(V.HIGH_YEAR , -4) THEN
        SELECT LISTAGG(GENRE, ',') WITHIN GROUP (ORDER BY GENRE)
          INTO V_CUST_GENRES FROM (
          SELECT DISTINCT FUNC_GENRE_BY_ID(TRC.GENREID) AS GENRE
            FROM TMP_CUST_INVOICE_ANALYSE TMPTBL
			JOIN INVOICE INV USING(CUSTOMERID)
            JOIN INVOICELINE INVLIN ON INV.INVOICEID = INVLIN.INVOICEID
            JOIN TRACK TRC ON TRC.TRACKID = INVLIN.TRACKID
            WHERE CUSTOMERID=V.CUSTOMERID);
        DBMS_OUTPUT.PUT_LINE('Customer: ' || UPPER(V.CUSTNAME) ||
                             ' - Offer a Discount According To Preferred Genres: ' ||
                             UPPER(V_CUST_GENRES));
      END IF;
    END LOOP;
  END;
END;

EXEC PCK_CHINOOK_REPORTS.GET_ARTIST_BY_ALBUM();
EXEC PCK_CHINOOK_REPORTS.CUST_INVOICE_BY_YEAR_ANALYZE;

前面的示例演示了基本的 Oracle PL/SQL 过程和函数功能。Oracle PL/SQL 提供了大量不在本文档范围内的特性和功能。

有关详细信息，请参阅 Oracle 文档中的 CREATE FUNCTION 和 CREATE PROCEDURE 。

PostgreSQL 用法

PostgreSQL 使用CREATE FUNCTION语句为存储过程和存储函数提供支持。需要强调的是，PostgreSQL使用的过程语句仅支持CREATE FUNCTION语句。CREATE PROCEDURE语句与此 PostgreSQL 版本不兼容。

PL/pgSQL 是用于从Oracle PL/SQL代码迁移的主要数据库编程语言。PostgreSQL 支持的编程语言不限于PL/pgSQL，也支持这些语言：

PL/pgSQL
PL/Python
PL/Perl

Oracle PL/SQL 和 PostgreSQL PL/pgSQL 之间的互换性

PostgreSQL PL/pgSQL语言通常被认为是从Oracle PL/SQL代码迁移的理想候选者，因为PostgreSQL PL/pgSQL代码支持许多Oracle PL/SQL语法元素。

例如，PostgreSQL PL/pgSQL支持Oracle CREATE OR REPLACE PROCEDURE语句。还支持许多其他PL / SQL语法元素，使PostgreSQL和PL / pgSQL在从Oracle迁移时成为自然的替代方案。

PostgreSQL 创建函数权限

要创建函数，用户必须具有该语言的USAGE权限。创建函数时，可以指定语言参数，如示例中所示。

例子

将 Oracle 存储过程和函数转换为 PostgreSQL PL/pgSQL。

使用 PostgreSQL 的CREATE FUNCTION命令创建一个名为FUNC_ALG的新函数。

CREATE OR REPLACE FUNCTION FUNC_ALG(P_NUM NUMERIC)
RETURNS NUMERIC
AS $$
BEGIN
  RETURN P_NUM * 2;
END;$$
LANGUAGE PLPGSQL;

使用CREATE OR REPLACE语句创建新函数或替换现有函数，但有以下限制：

您无法更改函数名称或参数类型。
该语句不允许更改现有函数返回类型。
用户必须拥有函数才能替换它。
INPUT参数（P_NUM）的实现方式与 Oracle PL/SQL 的INPUT参数类似。
两个美元符号用于防止需要使用单引号字符串转义元素。使用两个美元符号时，使用单引号（ ’ ）时无需在代码中使用转义字符。两个美元符号出现在关键字AS之后和函数关键字END之后。
使用LANGUAGE PLPGSQL参数指定所创建函数的语言。

将Oracle EMP_SAL_RAISE PL/SQL函数转换为PostgreSQL PL/pgSQL。

CREATE OR REPLACE FUNCTION EMP_SAL_RAISE
(IN P_EMP_ID DOUBLE PRECISION,
IN SAL_RAISE DOUBLE PRECISION)
RETURNS VOID
AS $$
DECLARE
  V_EMP_CURRENT_SAL DOUBLE PRECISION;
BEGIN
  SELECT SALARY INTO STRICT V_EMP_CURRENT_SAL
    FROM EMPLOYEES WHERE EMPLOYEE_ID = P_EMP_ID;

  UPDATE EMPLOYEES
    SET SALARY = V_EMP_CURRENT_SAL + SAL_RAISE
    WHERE EMPLOYEE_ID = P_EMP_ID;

  RAISE DEBUG USING MESSAGE := CONCAT_WS('',
    'NEW SALARY FOR EMPLOYEE ID: ', P_EMP_ID, 'IS ',
    (V_EMP_CURRENT_SAL + SAL_RAISE));
EXCEPTION
  WHEN OTHERS THEN
    RAISE USING ERRCODE := '20001', MESSAGE :=
      CONCAT_WS('', 'AN ERROR WAS ENCOUNTERED - ',
	            SQLSTATE, ' -ERROR-', SQLERRM);
END;$$
LANGUAGE PLPGSQL;

select emp_sal_raise(200, 1000);

将Oracle EMP_PERIOD_OF_SERVICE_YEAR PL/SQL函数转换为PostgreSQL PL/pgSQL。

CREATE OR REPLACE FUNCTION
EMP_PERIOD_OF_SERVICE_YEAR (IN P_EMP_ID DOUBLE PRECISION)
RETURNS DOUBLE PRECISION
AS $$
DECLARE
  V_PERIOD_OF_SERVICE_YEARS DOUBLE PRECISION;
BEGIN
  SELECT
    EXTRACT (YEAR FROM NOW()) - EXTRACT (YEAR FROM (HIRE_DATE))
    INTO STRICT V_PERIOD_OF_SERVICE_YEARS
    FROM EMPLOYEES
    WHERE EMPLOYEE_ID = P_EMP_ID;
  RETURN V_PERIOD_OF_SERVICE_YEARS;
END;$$
LANGUAGE PLPGSQL;

SELECT EMPLOYEE_ID, FIRST_NAME,
  EMP_PERIOD_OF_SERVICE_YEAR(EMPLOYEE_ID) AS PERIOD_OF_SERVICE_YEAR
  FROM EMPLOYEES;

Oracle 包规格和包体

PostgreSQL 不支持 Oracle 包对象和包体。所有 PL/SQL 对象都必须转换为 PostgreSQL 函数。以下示例介绍了转换时如何处理 Oracle 包对象和包体名称。

Oracle 包名称：PCK_CHINOOK_REPORTS。Oracle 包过程：GET_ARTIST_BY_ALBUM。

EXEC PCK_CHINOOK_REPORTS.GET_ARTIST_BY_ALBUM('');

你可以在转换到 PostgreSQL 代码时，使用符号$分隔包和过程名称。

SELECT PCK_CHINOOK_REPORTS$GET_ARTIST_BY_ALBUM('');

例子

将 Oracle 包和包体转换为 PostgreSQL PL/pgSQL。

在以下示例中，Oracle 包名称为PCK_CHINOOK_REPORTS，Oracle 包过程为GET_ARTIST_BY_ALBUM。

CREATE OR REPLACE FUNCTION
  chinook."PCK_CHINOOK_REPORTS$GET_ARTIST_BY_ALBUM"
  (p_artist_id text)
  RETURNS void
LANGUAGE plpgsql
AS $function$
DECLARE
  V_ARTIST_NAME CHINOOK.ARTIST.NAME%TYPE;
BEGIN
  SELECT art.name INTO STRICT V_ARTIST_NAME
    FROM chinook.album AS alb
    JOIN chinook.artist AS art
    USING (artistid)
    WHERE alb.title = p_artist_id;
  RAISE DEBUG USING MESSAGE := CONCAT_WS('', 'ArtistName: ', V_ARTIST_NAME);
END;
$function$;

-- Procedures (Packages) Verification
set client_min_messages = 'debug';
-- Equivalent to Oracle SET SERVEROUTPUT ON
select chinook.pck_chinook_reports$get_artist_by_album(' Fireball');

在以下示例中，Oracle 包名称为PCK_CHINOOK_REPORTS，Oracle 包过程为CUST_INVOICE_BY_YEAR_ANALYZE。

CREATE OR REPLACE FUNCTION chinook."pck_chinook_reports$cust_invoice_by_year_analyze" ()
RETURNS void
LANGUAGE plpgsql
AS $function$
DECLARE
  v_cust_genres CHARACTER VARYING(200);
  v RECORD;
BEGIN
  FOR v IN
    SELECT customerid, custname, low_year, high_year, cust_avg
      FROM chinook.tmp_cust_invoice_analyse
  LOOP
    IF SUBSTR(v.low_year, - 4) > SUBSTR(v.high_year, - 4) THEN
      -- Altering Oracle LISTAGG Function With PostgreSQL STRING_AGG Function
      select string_agg(genre, ',') into v_cust_genres
        from (select distinct chinook.func_genre_by_id(trc.genreid) as genre
        from chinook.tmp_cust_invoice_analyse tmptbl
        join chinook.INVOICE inv using(customerid)
        join chinook.INVOICELINE invlin on inv.invoiceid = invlin.invoiceid
        join chinook.TRACK trc on trc.trackid = invlin.trackid
        where customerid=v.CUSTOMERID) a;

      -- PostgreSQL Equivalent To Oracle DBMS_OUTPUT.PUT_LINE()\
      RAISE DEBUG USING MESSAGE := CONCAT_WS('', 'Customer: ',
        UPPER(v.custname), ' - Offer a Discount According To Preferred Genres: ', 
        UPPER(v_cust_genres));
    END IF;
  END LOOP;
END;
$function$;

-- Running
SELECT chinook.pck_chinook_reports$cust_invoice_by_year_analyze();

PostgreSQL 版本 10 中LATERAL FROM子句使用的集合返回函数的新行为。

以前

CREATE TABLE emps (id int, manager int);
INSERT INTO tab VALUES (23, 24), (52, 23), (21, 65);
SELECT x, generate_series(1,5) AS g FROM tab;

id |g
---|--
23 |1
23 |2
23 |3
23 |4
23 |5
52 |1
52 |2
52 |3
52 |4
52 |5
21 |1
21 |2
21 |3
21 |4
21 |5

新增功能

SELECT id, g FROM emps, LATERAL generate_series(1,5) AS g;

id |g
---|--
23 |1
23 |2
23 |3
23 |4
23 |5
52 |1
52 |2
52 |3
52 |4
52 |5
21 |1
21 |2
21 |3
21 |4
21 |5

在这里，规划器可以选择将设置-返回函数放在 nestloop 连接的外部，因为它对 emps 表没有实际的横向依赖关系。

有关详细信息，请参阅 PostgreSQL 文档中的 CREATE FUNCTION、PL/pgSQL — SQL过程语言、过程语言和查询语言（SQL）函数。

Oracle 用法

创建过程和函数的权限

包规格和包体

PostgreSQL 用法

Oracle 包规格和包体

搜索

分类

标签