PostgreSQL > Oracle DB 변환 2

1. 매개변수 선언

PostgreSQL에서 매개변수는 보통 데이터 타입만 지정하면 되며, 입력으로만 사용됩니다.

Oracle에서는 매개변수의 방향(입력 IN, 출력 OUT, 또는 입력/출력 IN OUT)을 명시적으로 선언해야 합니다.

IN 키워드를 사용하여 user_id 매개변수가 입력 전용임을 명시합니다.
반환 타입을 RETURN VARCHAR2로 선언하여 Oracle의 데이터 타입 규칙을 따릅니다.

-- PostgreSQL
CREATE OR REPLACE FUNCTION get_user_name(user_id integer) RETURNS text AS $$
DECLARE
    user_name text;
BEGIN
    SELECT name INTO user_name FROM users WHERE id = user_id;
    RETURN user_name;
END;
$$ LANGUAGE plpgsql;

-- Oracle
CREATE OR REPLACE FUNCTION get_user_name(user_id IN NUMBER) RETURN VARCHAR2 AS
    user_name VARCHAR2(100);
BEGIN
    SELECT name INTO user_name FROM users WHERE id = user_id;
    RETURN user_name;
END;

2. 예외 처리

PostgreSQL에서는 EXCEPTION 블록을 사용하여 오류를 처리합니다.

Oracle PL/SQL에서는 EXCEPTION 블록을 사용하여 특정 예외가 발생했을 때의 동작을 정의할 수 있습니다.
NO_DATA_FOUND 예외를 사용하여 데이터가 없을 때 'Department not found'을 반환합니다.

-- PostgreSQL
CREATE OR REPLACE FUNCTION get_department(dept_id integer) RETURNS text AS $$
DECLARE
    department_name text;
BEGIN
    SELECT name INTO department_name FROM departments WHERE id = dept_id;
    RETURN department_name;
EXCEPTION WHEN OTHERS THEN
    RETURN 'Department not found';
END;
$$ LANGUAGE plpgsql;

-- Oracle
CREATE OR REPLACE FUNCTION get_department(dept_id IN NUMBER) RETURN VARCHAR2 AS
    department_name VARCHAR2(100);
BEGIN
    SELECT name INTO department_name FROM departments WHERE id = dept_id;
    RETURN department_name;
EXCEPTION
    WHEN NO_DATA_FOUND THEN
        RETURN 'Department not found';
END;

3. 변수 초기화와 선언

변수 선언 시 Oracle에서는 데이터 타입과 함께 초기값을 할당할 수 있는 반면, PostgreSQL에서는 타입 추론을 통해 보다 유연하게 변수를 선언할 수 있습니다.
변수 age를 선언할 때 Oracle에서는 자료형을 명확히 지정해야 합니다.

-- PostgreSQL
CREATE OR REPLACE FUNCTION calculate_age(birthdate date) RETURNS integer AS $$
DECLARE
    age integer;
BEGIN
    age := EXTRACT(year FROM age(birthdate));
    RETURN age;
END;
$$ LANGUAGE plpgsql;

-- Oracle
CREATE OR REPLACE FUNCTION calculate_age(birthdate IN DATE) RETURN NUMBER AS
    age NUMBER;
BEGIN
    age := EXTRACT(year FROM SYSDATE) - EXTRACT(year FROM birthdate);
    RETURN age;
END;

4. 구문의 명확성

별도의 BEGIN ... EXCEPTION ... END; 블록을 사용하여 오류 처리를 분리하고 처리합니다.

-- PostgreSQL
CREATE OR REPLACE FUNCTION process_data() RETURNS void AS $$
BEGIN
    -- Complex SQL Queries
    -- Error Handling
    RAISE NOTICE 'Process Complete';
END;
$$ LANGUAGE plpgsql;

-- Oracle
CREATE OR REPLACE FUNCTION process_data RETURN VARCHAR2 AS
BEGIN
    -- Complex SQL Queries
    -- Simulate complex processing (here we assume it succeeds)
    
    -- 이 부분은 성공적으로 처리되었다고 가정하고 'Process Complete' 메시지를 반환합니다.
    RETURN 'Process Complete';

EXCEPTION
    WHEN OTHERS THEN
        -- 오류가 발생하면, 오류 메시지를 반환합니다.
        RETURN 'Error occurred: ' || SQLERRM;
END;

5. 반환 타입 선언

함수 정의에 있어서 반환되는 데이터 타입을 RETURN VARCHAR2와 같이 명시적으로 선언합니다.
PostgreSQL에서는 데이터 타입을 더 일반적인 방식으로 선언할 수 있지만, Oracle에서는 타입을 구체적으로 명시해야 할 때가 많습니다.

-- PostgreSQL
CREATE OR REPLACE FUNCTION get_status() RETURNS text AS $$
BEGIN
    RETURN 'Active';
END;
$$ LANGUAGE plpgsql;

-- Oracle
CREATE OR REPLACE FUNCTION get_status RETURN VARCHAR2 AS
BEGIN
    RETURN 'Active';
END;

6. 트랜잭션 관리

PostgreSQL에서는 함수 내부에서 COMMIT이나 ROLLBACK을 사용할 수 없습니다. 반면, Oracle의 프로시저에서는 명시적인 트랜잭션 관리가 가능합니다.

-- PostgreSQL
CREATE OR REPLACE FUNCTION update_inventory() RETURNS void AS $$
BEGIN
    -- Update operations
    -- PostgreSQL does not allow COMMIT or ROLLBACK within functions
    RAISE NOTICE 'Inventory updated';
END;
$$ LANGUAGE plpgsql;

-- Oracle
CREATE OR REPLACE FUNCTION update_inventory RETURN VARCHAR2 AS
BEGIN
    -- Update operations
    -- Assuming updates are done here
    RETURN 'Inventory updated';
EXCEPTION
    WHEN OTHERS THEN
        RETURN 'Update failed: ' || SQLERRM;
END;

7. 지역 변수의 사용

Oracle PL/SQL에서는 지역 변수를 선언할 때 각 변수에 대해 명시적으로 데이터 타입을 지정해야 합니다. PostgreSQL에서는 타입 추론이 더 자유롭습니다.

-- PostgreSQL
CREATE OR REPLACE FUNCTION get_full_name(user_id integer) RETURNS text AS $$
DECLARE
    first_name text;
    last_name text;
BEGIN
    SELECT first_name, last_name INTO first_name, last_name FROM users WHERE id = user_id;
    RETURN first_name || ' ' || last_name;
END;
$$ LANGUAGE plpgsql;

-- Oracle
CREATE OR REPLACE FUNCTION get_full_name(user_id IN NUMBER) RETURN VARCHAR2 AS
    first_name VARCHAR2(100);
    last_name VARCHAR2(100);
BEGIN
    SELECT first_name, last_name INTO first_name, last_name FROM users WHERE id = user_id;
    RETURN first_name || ' ' || last_name;
END;

8. 다중 결과 처리

PostgreSQL에서는 RETURNS TABLE 구문을 사용하여 직접 테이블과 같은 형태의 데이터를 반환할 수 있습니다.

Oracle에서는 SYS_REFCURSOR 타입을 사용하여 프로시저 외부로 결과를 전달합니다.
이 타입은 쿼리 결과를 참조하는 커서를 반환하고, 클라이언트나 애플리케이션에서 이 커서를 통해 결과를 순차적으로 읽을 수 있습니다.

-- PostgreSQL
CREATE OR REPLACE FUNCTION get_employees(dept_id int)
RETURNS TABLE(employee_id int, employee_name text) AS $$
BEGIN
    RETURN QUERY SELECT id, name FROM employees WHERE department_id = dept_id;
END;
$$ LANGUAGE plpgsql;

-- SELECT * FROM get_employees(1);과 같이 호출하면 직접 테이블 데이터를 반환합니다.

-- Oracle
CREATE OR REPLACE FUNCTION get_employees(dept_id NUMBER)
RETURN SYS_REFCURSOR AS
    v_cursor SYS_REFCURSOR;
BEGIN
    OPEN v_cursor FOR SELECT id, name FROM employees WHERE department_id = dept_id;
    RETURN v_cursor;
END;

-- 이 함수는 클라이언트에서 커서를 열고 각 행을 반복적으로 읽어야 결과를 얻을 수 있습니다.

9. 오류 처리

PostgreSQL에서는 간단한 오류 처리를 통해 주로 알림(NOTICE)을 발생시킵니다.

Oracle에서는 SQLERRM 함수를 사용하여 오류 메시지를 얻을 수 있으며, 처리 후 예외를 재발생시킬 수 있습니다.

-- PostgreSQL 
CREATE OR REPLACE FUNCTION process_data() RETURNS void AS $$
BEGIN
    -- Process data
EXCEPTION WHEN others THEN
    RAISE NOTICE 'An error occurred.';
END;
$$ LANGUAGE plpgsql;

-- Oracle
CREATE OR REPLACE FUNCTION process_data RETURN VARCHAR2 AS
BEGIN
    -- Complex SQL Queries
    -- Simulate complex processing
    RETURN 'Process Complete';

EXCEPTION
    WHEN OTHERS THEN
        RETURN 'Error occurred: ' || SQLERRM;
END;

10. 동적 SQL 실행

PostgreSQL에서는 EXECUTE 문을 사용하여 문자열로 구성된 쿼리를 실행할 수 있습니다. 이는 특히 사용자 입력에 따라 SQL 쿼리가 동적으로 변해야 할 때 유용합니다.

CREATE OR REPLACE FUNCTION execute_dynamic_sql(query text)
RETURNS void AS $$
BEGIN
    EXECUTE query;
END;
$$ LANGUAGE plpgsql;

Oracle에서는 EXECUTE IMMEDIATE를 사용하여 동적 SQL을 실행합니다. 함수 내에서 동적 SQL을 실행하고 결과를 문자열로 반환하거나 오류 메시지를 다루는 예제는 다음과 같습니다.

CREATE OR REPLACE FUNCTION execute_dynamic_sql(query VARCHAR2) RETURN VARCHAR2 AS
BEGIN
    EXECUTE IMMEDIATE query;
    RETURN 'Query executed';
EXCEPTION
    WHEN OTHERS THEN
        RETURN SQLERRM;
END;

이 함수는 SELECT execute_dynamic_sql('INSERT INTO my_table (id, name) VALUES (1, ''Alice'')') FROM dual;와 같이 호출되어 결과나 오류 메시지를 반환합니다.

11. 데이터 조작과 변수 사용

PostgreSQL에서 record 타입을 사용하여 임시 데이터를 조작하는 예시입니다.

DO $$
DECLARE
    emp_record RECORD;
BEGIN
    FOR emp_record IN SELECT * FROM employees LOOP
        RAISE NOTICE 'Employee Name: %', emp_record.name;
    END LOOP;
END $$;

Oracle에서 비슷한 기능을 수행하기 위해 커서와 레코드를 사용합니다.
이 PL/SQL 블록은 모든 직원을 순회하며 이름을 출력합니다.

DECLARE
    CURSOR emp_cursor IS SELECT * FROM employees;
    emp_record emp_cursor%ROWTYPE;
BEGIN
    OPEN emp_cursor;
    LOOP
        FETCH emp_cursor INTO emp_record;
        EXIT WHEN emp_cursor%NOTFOUND;
        DBMS_OUTPUT.PUT_LINE('Employee Name: ' || emp_record.name);
    END LOOP;
    CLOSE emp_cursor;
END;

이 PL/SQL 블록은 모든 직원을 순회하며 이름을 출력합니다.

12. 동적 SQL 실행

동적 SQL을 생성하고 실행하는 PostgreSQL 함수 예시입니다.

CREATE OR REPLACE FUNCTION execute_dynamic_sql(query text)
RETURNS void AS $$
BEGIN
    EXECUTE query;
END;
$$ LANGUAGE plpgsql;

Oracle에서도 프로시저 대신 함수를 사용하여 동적 SQL을 실행하는 예시를 제공할 수 있습니다.
Oracle에서 함수를 정의하여 SQL 문을 동적으로 실행할 수 있으나, 함수가 데이터베이스 상태를 변경하는 작업(예: 데이터 삽입, 수정, 삭제)을 수행하게 되면 일반적인 함수의 사용 목적과 다소 어긋나게 됩니다. 이러한 이유로, Oracle에서는 데이터 변경 작업을 수행하는 로직을 주로 프로시저 내에서 구현합니다.

-- 프로시저에서 동적 SQL 실행하는 예시
CREATE OR REPLACE PROCEDURE execute_dynamic_sql(query VARCHAR2) AS
BEGIN
    EXECUTE IMMEDIATE query;
END;

-- 함수에서 동적 SQL 실행하는 예시
CREATE OR REPLACE FUNCTION execute_dynamic_sql(query VARCHAR2) RETURN VARCHAR2 AS
BEGIN
    EXECUTE IMMEDIATE query;
    RETURN 'Query executed';
EXCEPTION
    WHEN OTHERS THEN
        RETURN SQLERRM;
END;

이 함수는 입력받은 query를 EXECUTE IMMEDIATE로 실행하고, 성공적으로 실행되면 'Query executed'라는 문자열을 반환합니다.
오류가 발생할 경우, SQLERRM을 사용하여 오류 메시지를 반환합니다.

함수 사용 방법

SELECT execute_dynamic_sql('INSERT INTO my_table (id, name) VALUES (1, ''Alice'')') FROM dual;

dual은 Oracle에서 단일 행을 반환하는 데 사용되는 특수 테이블이며, 이 함수는 SELECT 문 내에서 호출될 수 있습니다.
함수가 문자열을 반환하므로 그 결과를 직접 확인할 수 있습니다.

주의 사항

함수 사용의 적절성: Oracle에서 함수는 일반적으로 데이터를 조회하거나 계산하는 데 사용됩니다. 데이터를 변경하는 작업을 함수에서 수행하는 것은 일반적인 작업이 아니므로, 가능하다면 프로시저 사용을 권장합니다.

보안: 동적 SQL을 사용할 때는 SQL 인젝션 같은 보안 취약점에 주의해야 합니다. 사용자 입력을 통해 동적 SQL을 구성할 경우, 가능하면 바인딩 변수를 사용하는 방법을 고려해야 합니다.