Oracle 데이터베이스 관리를 위한 PL/SQL 기초 안내서

PL/SQL이란?

PL/SQL(Procedural Language/Structured Query Language)은 프로그래밍 언어로서 데이터베이스와 상호작용하기 위해 사용되는 언어입니다. PL/SQL은 SQL을 기반으로 하며, 데이터베이스 객체를 생성하고 관리하는 기능을 제공합니다. PL/SQL은 주로 Oracle 데이터베이스 시스템에서 사용되며, 데이터베이스 관리, 데이터 조작, 프로시저 및 함수 작성 등 다양한 작업을 수행할 수 있습니다.

예를 들어, 학생 데이터베이스를 관리하는 시스템을 만들어야 한다고 가정해 봅시다. PL/SQL을 사용하여 이 작업을 수행할 수 있습니다.

1. 테이블 생성: 우리는 학생 데이터를 저장하기 위한 테이블을 생성해야 합니다. PL/SQL을 사용하여 데이터베이스에 테이블을 생성할 수 있습니다.

CREATE TABLE students (
  id NUMBER,
  name VARCHAR2(50),
  age NUMBER
);

2. 데이터 삽입: 학생 데이터베이스에 학생 정보를 추가해야 합니다. PL/SQL을 사용하여 데이터를 삽입할 수 있습니다.

INSERT INTO students (id, name, age)
VALUES (1, 'John Doe', 20);

3. 데이터 조회: 저장된 학생 데이터를 검색하려면 쿼리를 작성해야 합니다. PL/SQL을 사용하여 데이터베이스에서 데이터를 조회할 수 있습니다.

DECLARE
  student_name VARCHAR2(50);
BEGIN
  SELECT name INTO student_name FROM students WHERE id = 1;
  DBMS_OUTPUT.PUT_LINE('Student name: ' || student_name);
END;

• DECLARE: 선언 섹션은 변수 student_name을 선언합니다. 이 변수는 VARCHAR2 데이터 타입이며, 최대 길이는 50입니다.
• BEGIN: BEGIN 키워드로 시작하여 실행 섹션의 시작을 나타냅니다.
• SELECT 문: SELECT 문은 students 테이블에서 id가 1인 레코드의 name 값을 student_name 변수에 할당합니다. 이를 통해 student_name 변수에 해당 학생의 이름을 저장할 수 있습니다.
• DBMS_OUTPUT.PUT_LINE: DBMS_OUTPUT.PUT_LINE 함수를 사용하여 결과를 출력합니다. 'Student name: ' 문자열과 student_name 변수의 값을 연결하여 학생의 이름을 출력합니다.
• END: END 키워드는 실행 섹션의 끝을 나타냅니다.

<결과>

Student name: John Doe

4. 프로시저 작성: 특정 작업을 수행하기 위해 PL/SQL 프로시저를 작성할 수 있습니다. 예를 들어, 학생의 나이를 증가시키는 프로시저를 작성해보겠습니다.

CREATE OR REPLACE PROCEDURE increase_age(p_id NUMBER)
IS
  current_age NUMBER;
BEGIN
  SELECT age INTO current_age FROM students WHERE id = p_id;
  UPDATE students SET age = current_age + 1 WHERE id = p_id;
  COMMIT;
END;
/

• CREATE OR REPLACE PROCEDURE: 프로시저를 생성하거나 변경합니다.
• increase_age(p_id NUMBER): increase_age라는 프로시저를 정의하며, p_id라는 입력 매개변수를 받습니다. 이 매개변수는 NUMBER 데이터 타입입니다.
• IS: 프로시저의 시작을 나타내는 키워드입니다.
• current_age NUMBER;: current_age라는 변수를 선언합니다. 이 변수는 NUMBER 데이터 타입입니다.
• BEGIN: 실행 섹션의 시작을 나타냅니다.
• SELECT age INTO current_age FROM students WHERE id = p_id;: students 테이블에서 id 값이 p_id와 일치하는 레코드의 age 값을 current_age 변수에 할당합니다. 이를 통해 해당 학생의 현재 나이를 가져올 수 있습니다.
• UPDATE students SET age = current_age + 1 WHERE id = p_id;: students 테이블에서 id 값이 p_id와 일치하는 레코드의 age 값을 current_age + 1로 업데이트합니다. 즉, 학생의 나이를 1 증가시킵니다.
• COMMIT;: 변경된 데이터를 커밋하여 데이터베이스에 영구적으로 반영합니다.
• END;: 실행 섹션의 끝을 나타냅니다.
• /: PL/SQL 블록의 끝을 나타내는 디럭터입니다. PL/SQL 블록은 DECLARE부터 END까지의 선언과 실행 섹션으로 구성됩니다. 블록의 끝을 명시하기 위해 /를 사용합니다.

increase_age(1); 과 같이 호출하면 id가 1인 학생의 나이가 1 증가하게 됩니다.

이렇게 작성된 PL/SQL 프로시저를 호출하여 학생의 나이를 증가시킬 수 있습니다.

이는 PL/SQL의 간단한 예시입니다. PL/SQL은 데이터베이스 시스템과 상호작용하여 데이터 관리와 처리를 수행하는 데 사용되는 강력한 도구입니다.

프로시저란?

프로시저(Procedure)는 PL/SQL에서 사용되는 저장 프로그램 유형입니다. 프로시저는 데이터베이스에서 수행할 작업 또는 작업 집합을 정의하고 실행하기 위해 사용됩니다. 여러 개의 SQL 문이나 로직을 포함하고 있으며, 매개변수를 사용하여 입력을 전달하고 결과를 반환할 수 있습니다.

프로시저를 사용하여 다음과 같은 작업을 수행할 수 있습니다:

1. 데이터 조작: INSERT, UPDATE, DELETE 문을 사용하여 데이터베이스의 데이터를 변경합니다.
2. 데이터 검색: SELECT 문을 사용하여 데이터베이스에서 데이터를 조회합니다.
3. 트랜잭션 관리: COMMIT, ROLLBACK 등을 사용하여 데이터베이스 트랜잭션을 제어합니다.
4. 예외 처리: 예외 상황을 처리하기 위해 예외 핸들러를 사용하여 오류를 처리하거나 특정 조건에 대한 작업을 수행합니다.
5. 비즈니스 로직: 프로시저는 비즈니스 규칙을 구현하는 데 사용될 수 있으며, 데이터의 유효성을 검사하고 특정 작업을 수행할 수 있습니다.

프로시저는 다음과 같은 구조로 작성됩니다:

CREATE [OR REPLACE] PROCEDURE procedure_name
    [(parameter1 [IN | OUT | IN OUT] type1,
      parameter2 [IN | OUT | IN OUT] type2,
      ...
    )]
IS
    [local_variable_declarations]

BEGIN
    executable_statements
    [EXCEPTION
        exception_handling_statements]
END;

• CREATE OR REPLACE PROCEDURE: 프로시저를 생성하거나 변경합니다.
• procedure_name: 프로시저의 이름을 지정합니다.
• parameter: 프로시저에 전달되는 매개변수를 정의합니다. 매개변수는 입력(IN), 출력(OUT), 또는 입력 및 출력(IN OUT)으로 지정할 수 있습니다.
• type: 매개변수의 데이터 유형을 정의합니다.
• local_variable_declarations: 프로시저에서 사용할 지역 변수를 선언합니다.
• executable_statements: 프로시저에서 수행할 SQL 문이나 PL/SQL 블록을 작성합니다.
• EXCEPTION: 예외 상황에 대한 처리를 위한 예외 핸들러를 정의합니다.
• exception_handling_statements: 예외 발생 시 실행할 문장을 작성합니다.

프로시저는 다음과 같은 방법으로 호출됩니다

EXECUTE procedure_name;

또는

CALL procedure_name;

프로시저는 데이터베이스에서 재사용 가능하며, 여러 곳에서 호출하여 사용할 수 있습니다. 이를 통해 코드의 코드의 중복을 줄이고 유지보수성을 향상시킬 수 있습니다. 프로시저는 복잡한 데이터베이스 작업을 모듈화하고 관리하기 위한 강력한 도구입니다.

PL/SQL 또 다른 예시

CREATE OR REPLACE PROCEDURE insert_department AS
BEGIN
  EXECUTE IMMEDIATE 'TRUNCATE TABLE DEPT';

  INSERT /* + APPEND */ INTO DEPT (DEPTNO, DNAME, LOC)
  SELECT DEPTNO, DNAME, LOC FROM TMP_DEPT;

  COMMIT;
END;

• CREATE OR REPLACE PROCEDURE insert_department AS: insert_department라는 프로시저를 생성하거나 변경합니다.
• BEGIN: 실행 섹션의 시작을 나타내는 키워드입니다.
• EXECUTE IMMEDIATE 'TRUNCATE TABLE DEPT';: DEPT 테이블을 잘라내기(truncate)하기 위해 EXECUTE IMMEDIATE 문을 사용합니다.
• INSERT INTO DEPT (DEPTNO, DNAME, LOC) SELECT DEPTNO, DNAME, LOC FROM TMP_DEPT;: TMP_DEPT 테이블에서 데이터를 선택하여 DEPT 테이블에 삽입하는 INSERT 문입니다. DEPTNO, DNAME, LOC 컬럼에 해당하는 값들을 TMP_DEPT 테이블에서 가져와 DEPT 테이블에 삽입합니다.
• /* + APPEND */: 이것은 주석으로, Oracle 데이터베이스에서 사용되는 힌트(Hint) 중 하나입니다. 힌트는 쿼리 옵티마이저에게 쿼리 실행 계획을 제어하거나 최적화하는 데 도움을 주는 지시사항입니다. APPEND 힌트는 INSERT 문에 사용되며, 데이터를 INSERT하는 방법을 변경하여 성능을 향상시킬 수 있습니다. 일반적으로 행 단위로 데이터를 삽입하는 대신 데이터를 덩어리로 삽입하는 방식을 사용합니다. 이렇게 하면 로그 기록과 인덱스 갱신 작업이 최소화되어 성능이 향상될 수 있습니다.
• COMMIT;: 변경된 데이터를 커밋하여 데이터베이스에 영구적으로 반영합니다.
• END;: 실행 섹션의 끝을 나타냅니다.

참고) execute immediate와 그냥 execute의 차이

EXECUTE와 EXECUTE IMMEDIATE는 PL/SQL에서 사용되는 두 가지 다른 실행 방법입니다.

1. EXECUTE: EXECUTE 문은 주로 PL/SQL 블록 내에서 다른 프로시저나 함수를 호출할 때 사용됩니다. 즉, 미리 정의된 PL/SQL 코드를 실행하는 데 사용됩니다. EXECUTE 문은 블록 내에서 다른 PL/SQL 서브프로그램을 호출할 때 사용되며, 일반적으로 매개변수를 전달하고 결과를 반환받을 수 있습니다.

EXECUTE procedure_name;

위의 예시에서 procedure_name은 호출하려는 PL/SQL 프로시저의 이름입니다. EXECUTE 문을 사용하여 해당 프로시저를 실행할 수 있습니다.

2. EXECUTE IMMEDIATE: EXECUTE IMMEDIATE 문은 동적 SQL 문을 실행하는 데 사용됩니다. 동적 SQL은 실행 시점에 동적으로 생성되는 SQL 문으로, 실행되는 데이터나 조건에 따라 SQL 문이 동적으로 변경되는 경우에 사용됩니다. EXECUTE IMMEDIATE 문은 문자열로 된 SQL 문을 실행하기 위해 사용되며, 실행되는 SQL 문은 실행 시점에 결정됩니다.

EXECUTE IMMEDIATE 'SELECT * FROM table_name';

위의 예시에서 'SELECT * FROM table_name'은 실행 시점에 동적으로 결정되는 SQL 문입니다. EXECUTE IMMEDIATE 문을 사용하여 해당 SQL 문을 실행할 수 있습니다.

요약하자면, EXECUTE 문은 미리 정의된 PL/SQL 코드를 실행하는 데 사용되고, EXECUTE IMMEDIATE 문은 실행 시점에 동적으로 생성된 SQL 문을 실행하는 데 사용됩니다.