AnalyticDB:Migração de aplicações Oracle para o AnalyticDB for PostgreSQL

FOR i IN REVERSE 1..3 LOOP
    DBMS_OUTPUT.PUT_LINE(TO_CHAR(i));
END LOOP;

-- Boundaries swapped: 1..3 -> 3..1; DBMS_OUTPUT.PUT_LINE -> RAISE
FOR i IN REVERSE 3..1 LOOP
    RAISE '%', i;
END LOOP;

EXECUTE 'UPDATE employees_temp SET commission_pct = :x' USING a_null;

-- USING replaced with string concatenation using quote_literal
EXECUTE 'UPDATE employees_temp SET commission_pct = ' || quote_literal(a_null);

TYPE pair IS RECORD(a int, b int);
TYPE numset_t IS TABLE OF pair;

FUNCTION f1(x int) RETURN numset_t PIPELINED IS
DECLARE
    v_p pair;
BEGIN
    FOR i IN 1..x LOOP
        v_p.a := i;
        v_p.b := i+10;
        PIPE ROW(v_p);
    END LOOP;
    RETURN;
END;

SELECT * FROM f1(10);

-- RECORD type -> CREATE TYPE; PIPE ROW -> RETURN NEXT; PIPELINED -> RETURNS SETOF
CREATE TYPE pair AS (a int, b int);

CREATE OR REPLACE FUNCTION f1(x int) RETURNS SETOF pair AS
$$
DECLARE
    rec pair;
BEGIN
    FOR i IN 1..x LOOP
        rec := row(i, i+10);
        RETURN NEXT rec;
    END LOOP;
    RETURN;
END
$$
LANGUAGE plpgsql;

SELECT * FROM f1(10);

CREATE OR REPLACE FUNCTION test_func(id int, name varchar(10), out_id out int) RETURNS varchar(10)
AS $body$
BEGIN
    out_id := id + 1;
    RETURN name;
END
$body$
LANGUAGE PLPGSQL;

-- RETURN value converted to an OUT argument (out_name)
CREATE OR REPLACE FUNCTION test_func(id int, name varchar(10), out_id out int, out_name out varchar(10))
AS $body$
BEGIN
    out_id := id + 1;
    out_name := name;
END
$body$
LANGUAGE PLPGSQL;

-- Retrieve both output values
SELECT * FROM test_func(1, '1') INTO rec;

sql_str := 'SELECT * FROM test1 WHERE col1 = ' || param1 || ' AND col2 = ''' || param2 || ''' AND col3 = 3';

-- quote_literal wraps param2 safely, handling embedded quotes
sql_str := 'SELECT * FROM test1 WHERE col1 = ' || param1 || ' AND col2 = ' || quote_literal(param2) || ' AND col3 = 3';

SELECT to_date('2019-06-30 16:16:16') - to_date('2019-06-29 15:15:15') + 1 INTO v_days FROM dual;

-- Use extract() to get integer day count from an interval
SELECT extract('days' FROM '2019-06-30 16:16:16'::timestamp - '2019-06-29 15:15:15'::timestamp + '1 days'::interval)::int INTO v_days;

TYPE rec IS RECORD (a int, b int);

CREATE TYPE rec AS (a int, b int);

DECLARE
    TYPE Roster IS TABLE OF VARCHAR2(15);
    names Roster := Roster('D Caruso', 'J Hamil', 'D Piro', 'R Singh');
BEGIN
    FOR i IN names.FIRST .. names.LAST LOOP
        IF names(i) = 'J Hamil' THEN
            DBMS_OUTPUT.PUT_LINE(names(i));
        END IF;
    END LOOP;
END;

-- TABLE OF -> varchar[]; array indexing: names(i) -> names[i]; FIRST..LAST -> 1..array_length
CREATE OR REPLACE FUNCTION f1() RETURNS VOID AS
$$
DECLARE
    names varchar(15)[] := '{"D Caruso", "J Hamil", "D Piro", "R Singh"}';
    len int := array_length(names, 1);
BEGIN
    FOR i IN 1..len LOOP
        IF names[i] = 'J Hamil' THEN
            RAISE NOTICE '%', names[i];
        END IF;
    END LOOP;
    RETURN;
END
$$
LANGUAGE plpgsql;

SELECT f1();

-- Store global variables in a temporary table
-- Note: The id column is the distribution key and cannot be modified
CREATE TEMPORARY TABLE global_variables (
    id int,
    g_count int,
    g_set_id varchar(50),
    g_err_code varchar(100)
);

INSERT INTO global_variables VALUES(0, 1, null, null);

-- Getter function
CREATE OR REPLACE FUNCTION get_variable() RETURNS SETOF global_variables AS
$$
DECLARE
    rec global_variables%rowtype;
BEGIN
    EXECUTE 'SELECT * FROM global_variables' INTO rec;
    RETURN NEXT rec;
END;
$$
LANGUAGE plpgsql;

-- Setter function
CREATE OR REPLACE FUNCTION set_variable(IN param varchar(50), IN value anyelement) RETURNS void AS
$$
BEGIN
    EXECUTE 'UPDATE global_variables SET ' || quote_ident(param) || ' = ' || quote_literal(value);
END;
$$
LANGUAGE plpgsql;

-- Declare tmp_rec record; in the calling function
SELECT * FROM set_variable('g_err_code', 'error'::varchar) INTO tmp_rec;

SELECT * FROM get_variable() INTO tmp_rec;
error_code := tmp_rec.g_err_code;

SELECT emp_id, lead_id, emp_name, PRIOR emp_name AS lead_name, salary
FROM employee
START WITH lead_id = 0
CONNECT BY PRIOR emp_id = lead_id;

CREATE OR REPLACE FUNCTION f1(tablename text, lead_id int, nocycle boolean) RETURNS SETOF employee AS
$$
DECLARE
    idx int := 0;
    res_tbl varchar(265) := 'result_table';
    prev_tbl varchar(265) := 'tmp_prev';
    curr_tbl varchar(256) := 'tmp_curr';
    current_result_sql varchar(4000);
    tbl_count int;
    rec record;
BEGIN
    EXECUTE 'TRUNCATE ' || prev_tbl;
    EXECUTE 'TRUNCATE ' || curr_tbl;
    EXECUTE 'TRUNCATE ' || res_tbl;
    LOOP
        -- Query the current hierarchy level and insert into tmp_curr
        current_result_sql := 'INSERT INTO ' || curr_tbl || ' SELECT t1.* FROM ' || tablename || ' t1';

        IF idx > 0 THEN
            current_result_sql := current_result_sql || ', ' || prev_tbl || ' t2 WHERE t1.lead_id = t2.emp_id';
        ELSE
            current_result_sql := current_result_sql || ' WHERE t1.lead_id = ' || lead_id;
        END IF;
        EXECUTE current_result_sql;

        -- If nocycle is false, remove already-traversed rows
        IF nocycle IS FALSE THEN
            EXECUTE 'DELETE FROM ' || curr_tbl || ' WHERE (lead_id, emp_id) IN (SELECT lead_id, emp_id FROM ' || res_tbl || ')';
        END IF;

        -- Exit when no more rows
        EXECUTE 'SELECT count(*) FROM ' || curr_tbl INTO tbl_count;
        EXIT WHEN tbl_count = 0;

        -- Promote current results to result and prev tables
        EXECUTE 'INSERT INTO ' || res_tbl || ' SELECT * FROM ' || curr_tbl;
        EXECUTE 'TRUNCATE ' || prev_tbl;
        EXECUTE 'INSERT INTO ' || prev_tbl || ' SELECT * FROM ' || curr_tbl;
        EXECUTE 'TRUNCATE ' || curr_tbl;
        idx := idx + 1;
    END LOOP;

    FOR rec IN EXECUTE 'SELECT * FROM ' || res_tbl LOOP
        RETURN NEXT rec;
    END LOOP;
    RETURN;
END
$$
LANGUAGE plpgsql;

SELECT * FROM t WHERE rownum < 10;

SELECT * FROM t LIMIT 10;

SELECT rownum, * FROM t;

SELECT row_number() OVER() AS rownum, * FROM t;

SELECT sysdate FROM dual;

SELECT current_timestamp;

CREATE TABLE dual (dummy varchar(1));
INSERT INTO dual VALUES ('X');

ERROR: function cannot execute on segment because it accesses relation "public.t2"

CREATE OR REPLACE FUNCTION f1(arg int) RETURN int IS
    v int;
BEGIN
    SELECT b INTO v FROM t2 WHERE a = arg;
    RETURN v;
END;

SELECT a, f1(b) FROM t1;

-- Inline the function logic as a join
SELECT t1.a, t2.b FROM t1, t2 WHERE t1.b = t2.a;

SELECT * FROM a, b WHERE a.id = b.id(+);

SELECT * FROM a LEFT JOIN b ON a.id = b.id;

SELECT * FROM test1 t1, test2 t2, test3 t3
WHERE t1.col1(+) BETWEEN NVL(t2.col1, t3.col1) AND NVL(t3.col1, t2.col1);

WITH cte AS (
    SELECT t2.col1 AS low, t2.col2, t3.col1 AS high, t3.col2 AS c2
    FROM t2, t3
)
SELECT * FROM t1
RIGHT OUTER JOIN cte ON t1.col1 BETWEEN coalesce(cte.low, cte.high) AND coalesce(cte.high, cte.low);

CREATE SEQUENCE seq1;
SELECT seq1.nextval FROM dual;

CREATE SEQUENCE seq1;
SELECT nextval('seq1');

FUNCTION test_func() IS
    CURSOR data_cursor IS SELECT * FROM test1;
BEGIN
    FOR i IN data_cursor LOOP
        -- process i
    END LOOP;
END;

CREATE OR REPLACE FUNCTION test_func()
AS $body$
DECLARE
    data_cursor CURSOR FOR SELECT * FROM test1;
    i record;
BEGIN
    OPEN data_cursor;
    LOOP
        FETCH data_cursor INTO i;
        IF NOT FOUND THEN
            EXIT;
        END IF;
        -- process i
    END LOOP;
    CLOSE data_cursor;
END;
$body$
LANGUAGE PLPGSQL;

FUNCTION test_func(level IN number) IS
    CURSOR data_cursor IS SELECT * FROM test1;
BEGIN
    IF level > 5 THEN RETURN; END IF;
    FOR i IN data_cursor LOOP
        -- process i
        test_func(level + 1);
    END LOOP;
END;

-- Named cursor removed; inline query used instead to support recursion
CREATE OR REPLACE FUNCTION test_func(level int) RETURNS void
AS $body$
DECLARE
    i record;
BEGIN
    IF level > 5 THEN
        RETURN;
    END IF;
    FOR i IN SELECT * FROM test1 LOOP
        -- process i
        PERFORM test_func(level + 1);
    END LOOP;
END;
$body$
LANGUAGE PLPGSQL;