Oracle到Postgres语句迁移

Question

我正在尝试将一些语句从Oracle移植到Postgres。

在Oracle方面，我甚至很难理解的一个具体问题是：

rating_scheme_oid NUMBER;
rs_cnt number;

据我所知，这些似乎只是定义变量，所以我想我可以将其转化为：

CREATE TYPE rating_scheme_oid NUMERIC;
CREATE TYPE rs_cnt NUMERIC;

然而，这似乎不起作用：ERROR: syntax error at or near "NUMERIC"

如果在这个领域拥有专业知识的人能够描述他们正在做的事情以及可能的解决方案，那就太好了。

以下是完整的上下文(这是工作的Oracle语句)：

        DECLARE
        CURSOR update_cursor
        IS
        SELECT oid, subscription_id, configuration_oid
        FROM scope
        WHERE subscription_id != 1 and subclass_type = 'W' and configuration_oid is not null;

        TYPE type
        IS
        TABLE OF update_cursor%ROWTYPE;
        update_array type;

        rating_scheme_oid NUMBER;
        rs_cnt number;

        BEGIN
        OPEN update_cursor;
        LOOP
        FETCH update_cursor BULK COLLECT INTO update_array LIMIT 1000;
        FOR i IN 1 .. update_array.COUNT LOOP

        BEGIN
        select count(*) into rs_cnt from workspace_config where workspace_oid = update_array(i).oid and OBJECTIVE_STATE_SCHEME_OID is null;
        IF rs_cnt = 1
        THEN

        SELECT OID_SEQ.nextval into rating_scheme_oid from DUAL;

        INSERT INTO RATING_SCHEME (OID, SUBSCRIPTION_ID, VERSION, NAME, CREATION_DATE, WORKSPACE_OID, UUID)
        VALUES (rating_scheme_oid, update_array(i).subscription_id, 1, null, SYSDATE, update_array(i).oid, sys_guid());

        UPDATE WORKSPACE_CONFIG set OBJECTIVE_STATE_SCHEME_OID = rating_scheme_oid where oid = update_array(i).configuration_oid;

        INSERT INTO RATING (OID, SUBSCRIPTION_ID, WORKSPACE_OID, VERSION, CREATION_DATE, RATING_SCHEME_OID, NAME, ORDINAL_VALUE, UUID)
        VALUES (OID_SEQ.nextval, update_array(i).subscription_id, update_array(i).oid, 1, SYSDATE, rating_scheme_oid, 'Defined', 0, sys_guid());

        INSERT INTO RATING (OID, SUBSCRIPTION_ID, WORKSPACE_OID, VERSION, CREATION_DATE, RATING_SCHEME_OID, NAME, ORDINAL_VALUE, UUID)
        VALUES (OID_SEQ.nextval, update_array(i).subscription_id, update_array(i).oid, 1, SYSDATE, rating_scheme_oid, 'Committed', 1, sys_guid());

        INSERT INTO RATING (OID, SUBSCRIPTION_ID, WORKSPACE_OID, VERSION, CREATION_DATE, RATING_SCHEME_OID, NAME, ORDINAL_VALUE, UUID)
        VALUES (OID_SEQ.nextval, update_array(i).subscription_id, update_array(i).oid, 1, SYSDATE, rating_scheme_oid, 'In-Progress', 2, sys_guid());

        INSERT INTO RATING (OID, SUBSCRIPTION_ID, WORKSPACE_OID, VERSION, CREATION_DATE, RATING_SCHEME_OID, NAME, ORDINAL_VALUE, UUID)
        VALUES (OID_SEQ.nextval, update_array(i).subscription_id, update_array(i).oid, 1, SYSDATE, rating_scheme_oid, 'Measuring', 3, sys_guid());

        INSERT INTO RATING (OID, SUBSCRIPTION_ID, WORKSPACE_OID, VERSION, CREATION_DATE, RATING_SCHEME_OID, NAME, ORDINAL_VALUE, UUID)
        VALUES (OID_SEQ.nextval, update_array(i).subscription_id, update_array(i).oid, 1, SYSDATE, rating_scheme_oid, 'Achieved', 4, sys_guid());

        INSERT INTO RATING (OID, SUBSCRIPTION_ID, WORKSPACE_OID, VERSION, CREATION_DATE, RATING_SCHEME_OID, NAME, ORDINAL_VALUE, UUID)
        VALUES (OID_SEQ.nextval, update_array(i).subscription_id, update_array(i).oid, 1, SYSDATE, rating_scheme_oid, 'Closed', 5, sys_guid());

        END IF;

        END;
        END LOOP;
        COMMIT;
        EXIT WHEN update_cursor%NOTFOUND;
        END LOOP;
        CLOSE update_cursor;
        END;

Answer 1

将代码直接替换为PL/pgSQL是这样的。

下面是(显然不是)测试，并可能包含排字，所以您需要从这里计算出细节。但这应该能让你开始。

DECLARE
  l_oid_array int[];
  l_subscription_id_array int[];
  l_config_oid_array int[];

  l_rating_scheme_oid int;  
  l_cnt bigint;  
BEGIN
  SELECT array_agg(oid order by oid), 
         array_agg(subscription_id order by oid), 
         array_agg(configuration_oid order by oid)
     into l_oid_array, l_subscription_id_array, l_config_oid_array
  FROM scope
  WHERE subscription_id <> 1 
    and subclass_type = 'W' 
    and configuration_oid is not null;
    
  FOR i IN 1 .. cardinality(l_oid_array) LOOP
  
    select count(*) into l_cnt
    from workspace_config 
    where workspace_oid = workspace_oid = l_oid_array[i] 
      and OBJECTIVE_STATE_SCHEME_OID is null;
      
    IF l_cnt = 1 THEN
    THEN

      l_rating_scheme_oid := nextval('oid_seq');

      INSERT INTO RATING_SCHEME (OID, SUBSCRIPTION_ID, VERSION, NAME, CREATION_DATE, WORKSPACE_OID, UUID)
      VALUES (rating_scheme_oid, l_subscription_id_array[i], 1, null, current_date, l_oid_array[i], uuid_generate_v4());

      UPDATE WORKSPACE_CONFIG set OBJECTIVE_STATE_SCHEME_OID = rating_scheme_oid where oid = l_config_oid_array[i];

      INSERT INTO RATING (OID, SUBSCRIPTION_ID, WORKSPACE_OID, VERSION, CREATION_DATE, RATING_SCHEME_OID, NAME, ORDINAL_VALUE, UUID)
      VALUES (nextval('oid_seq'), l_subscription_id_array[i], l_oid_array[i], 1, current_date, l_rating_scheme_oid, 'Defined', 0, uuid_generate_v4());

      INSERT INTO RATING (OID, SUBSCRIPTION_ID, WORKSPACE_OID, VERSION, CREATION_DATE, RATING_SCHEME_OID, NAME, ORDINAL_VALUE, UUID)
      VALUES (nextval('oid_seq'), l_subscription_id_array[i], l_oid_array[i], 1, current_date, l_rating_scheme_oid, 'Committed', 1, uuid_generate_v4());

      INSERT INTO RATING (OID, SUBSCRIPTION_ID, WORKSPACE_OID, VERSION, CREATION_DATE, RATING_SCHEME_OID, NAME, ORDINAL_VALUE, UUID)
      VALUES (nextval('oid_seq'), l_subscription_id_array[i], l_oid_array[i], 1, current_date, l_rating_scheme_oid, 'In-Progress', 2, uuid_generate_v4());

      INSERT INTO RATING (OID, SUBSCRIPTION_ID, WORKSPACE_OID, VERSION, CREATION_DATE, RATING_SCHEME_OID, NAME, ORDINAL_VALUE, UUID)
      VALUES (nextval('oid_seq'), l_subscription_id_array[i], l_oid_array[i], 1, current_date, l_rating_scheme_oid, 'Measuring', 3, uuid_generate_v4());

      INSERT INTO RATING (OID, SUBSCRIPTION_ID, WORKSPACE_OID, VERSION, CREATION_DATE, RATING_SCHEME_OID, NAME, ORDINAL_VALUE, UUID)
      VALUES (nextval('oid_seq'), l_subscription_id_array[i], l_oid_array[i], 1, current_date, l_rating_scheme_oid, 'Achieved', 4, uuid_generate_v4());

      INSERT INTO RATING (OID, SUBSCRIPTION_ID, WORKSPACE_OID, VERSION, CREATION_DATE, RATING_SCHEME_OID, NAME, ORDINAL_VALUE, UUID)
      VALUES (nextval('oid_seq'), l_subscription_id_array[i], l_oid_array[i], 1, current_date, l_rating_scheme_oid, 'Closed', 5, uuid_generate_v4());
    END IF;
    
  END LOOP;

END;

要获得uuid_generate_v4()函数，需要对uuid扩展进行安装

在Oracle和Postgres中，在循环中执行事情通常是效率最低的方法。通常情况下，一个简单的insert into select ...更有效率