将oracle迁移到PostgreSQL时出现错误：“ERROR:语法错误在或靠近"@”第16行: from csm016@fbi a，csm013@fbi b“

Question

CREATE OR REPLACE FUNCTION ecs_pkg.get_busi_type ( acct_no_in text, cont_no_in text, p_message OUT text ) AS $body$
DECLARE

    V_SEQ_NO varchar(6);
    v_date_time_crte timestamp:=clock_timestamp();
    w_cust_type varchar(5);
    w_count integer;

BEGIN
    RAISE NOTICE 'write_notepad start';
    w_cust_type := 'NIL';

    -- verify account number and check for customer type ( VOICE or DATA )
    select count(*), max(t.cust_type) into STRICT w_count, w_cust_type
    from (SELECT 'VOICE' as cust_type
          from csm016@fbi a, csm013@fbi b
          where a.acct_no = acct_no_in and a.cont_no = cont_no_in
          and b.acct_no = a.acct_no
          and b.acct_catg in ('B','BT','C','CB','CR','G','GK','IB','IC','L','MB','MC','ME','VB','VG','Z')

union

          SELECT 'DATA' as cust_type
          from dcsm016@fbi a, dcsm013@fbi b
          where a.acct_no = acct_no_in and a.cont_no = cont_no_in
          and b.acct_no = a.acct_no
          and b.busi_code in ('BBS','DATA','METRONET')
          
union

          select 'ISP' as cust_type
          from icsm016@fbi a, icsm013@fbi b
          where a.acct_no = acct_no_in and a.cont_no = cont_no_in
          and b.acct_no = a.acct_no
          ) t;

    --//if w_count = 0 then
    --//    return -2;
    --//end if;
    p_message := w_cust_type;
END;


$body$
LANGUAGE PLPGSQL
 STABLE;

Answer 1

@fbi指的是名为fbi的非本地数据库。Postgres不支持此语法。您可能希望研究dblink来实现外部表。

诚挚的问候,

比亚尔尼