Core Elements of PL/SQL: Cursor, Stored Procedure, and Function

---

Contents

1️⃣커서(Cursor)
2️⃣저장 프로시저 (Stored Procedure)
3️⃣함수 (Function)

---

오늘은 함수와 프로시저에 대해 공부하는데 그전에 커서에 대해서 먼저 공부해본다. 커서는 데이터베이스에 존재하는 독특한 개념이다. 데이터베이스에서 결과 집합을 한 행씩 처리할 수 있도록 제공하는 특별한 도구라고 할 수 있다. 이를 통해 대량의 데이터를 제어하고 관리할 수 있게 된다.

그 후 함수와 프로시저를 학습할 때는 다음 포인트를 중점적으로 비교하며 접근하면 좋다:

• 함수는 반환값이 있고, 보통 특정 계산이나 값을 반환하기 위해 사용된다.

• 프로시저는 반환값이 없거나, OUT 매개변수를 사용하여 값을 반환하며, 주로 비즈니스 로직을 수행하는 데 적합하다.

---

1️⃣커서(Cursor)

커서(Cursor) - 데이터베이스 결과집합 처리

💡요약: Cursor는 데이터베이스의 SELECT 결과를 순차적으로 탐색하며 데이터를 처리할 수 있는 도구이다. SELECT문의 결과는 항상 Result Set(결과집합) 형태로 반환되며, 이를 다루기 위해 커서를 사용하게 된다. 커서를 통해 데이터를 한 행씩 처리하면서, 원하는 로직을 적용할 수 있게 된다.

✅ 결과집합 (Result Set)

Result Set: SELECT문을 실행하면 관계형 데이터 베이스를 다루고 있기 때문에 SELECT문 관계형 데이터 베이스인 테이블, 일부 부분 집합을 가져오게 된다. 혹은 두 개의 테이블을 합쳐 조인된 테이블을 가져오기도 한다. 그래서 SELECT문을 실행한 결과는 항상 집합의 형태로 나타나게 된다. employee테이블에서 조건을 가지고 데이터를 가져오게 되면 "결과집합"의 형태로 출력 된다.

즉 결과 집합은 SELECT문이 반환하는 데이터의 집합이다. 예를 들어, employee 테이블에서 조건을 이용해 데이터를 조회하면, 결과는 테이블 형태로 반환된다.

✅ 커서(Cursor)

우리가 지금까지 배운 내용은 결과 집합을 화면에 보여주는 것을 배웠다. 나타난 결과 집합을 출력만 하는 것이 아닌 각각의 데이터 결과를 다뤄 볼 수 있을까? 데이터를 다루려고 하면 그 결과를 어딘가에 store해야 한다. 프로그래밍 언어로 치면 변수 같은 곳에 저장을 해야 그 데이터를 다룰 수 있게 된다. select문의 조회 명령문을 통해 가져온 결과집합을 어딘가에 넣어두고 처리한다에서 출발한 개념이 "커서"이다. 즉 레코드 각각에 대한 개별적인 처리가 가능한 결과 집합의 확장이 된다.

• 결과를 저장한 뒤, 한 행(Row)씩 처리할 수 있다.

• 각 레코드에 개별적으로 접근하여 원하는 작업을 수행할 수 있도록 도와준다.
  예)결과집합에서 SCOTT(7788) 행을 읽은 뒤 다음 행으로 이동.

✅ 커서의 역할 (Role of Cursor)

• 데이터를 순서대로 가져와 프로그래밍 언어에서 처리 가능하게 한다.

• 데이터를 화면에 단순히 출력하는 것이 아니라 조작, 계산, 조건 처리를 할 수 있다.

• 레코드 단위로 데이터를 다룰 수 있어 효율적이다.

---

커서(Cursor) #1: 데이터베이스에서 명시적/묵시적 커서의 이해

✅ 커서의 종류 (Types of Cursor)

1. 명시적 커서 (Explicit Cursor): 우리가 일반적으로 커서라고 하면 명시적 커서를 일컫는다. "이러한 커서 A를 선언하였습니다. 그리고 여기에 SELECT문의 결과를 가지고 오세요" 이렇게 사용자가 변수를 정리하듯이 커서를 선언한다. 그리고 가져온 결과에 대해 각각의 레코드에 대해 하나씩 데이터를 가져와서 처리한다. 명시적으로 선언하고 사용하는 것이다.

   • 사용자가 직접 선언하고 관리하는 커서로, SELECT문의 결과를 다루기 위해 선언부터 처리까지 모든 과정을 사용자가 제어한다.

• 예시: CURSOR emp_csr IS SELECT * FROM employees;

2. 묵시적 커서 (Implicit Cursor): 반면에 묵지적 커서는 사용자에게는 보이진 않는다. 오라클이 알아서 최근에 select문한 쿼리 결과을 임시적으로 가지고있는 내부적인 커서이다. ‘SQL’ 이라는 이름으로 속성에 접근할 수 있다. 맨 마지막에 실행된 결과값, 즉 항상 최근에 실행된 SQL 문장에 대한 커서를 가지고 있다고 생각하면 되겠다.

   • 오라클이 내부적으로 자동 생성하는 커서로, SELECT문 또는 DML(INSERT, UPDATE, DELETE) 실행 시 최근 실행된 결과를 임시적으로 저장한다.

• 속성 접근: SQL%ROWCOUNT, SQL%NOTFOUND 등을 사용해 상태를 확인할 수 있다.

---

커서(Cursor) #2: 명시적 커서 (Explicit Cursor)

✅명시적 커서 처리 순서 (Explicit Cursor Workflow)

1. 커서 선언 (DECLARE): 커서는 당연히 사용하기 전에 먼저 선언되어야 한다. 커서 선언은 변수 선언과 마찬가지로 선언된 커서는 한 개의 이름이 할당되고 SELECT 문과 연결된다.

   • 예시: CURSOR emp_csr IS SELECT employee_id FROM employees;

---

2. 커서 열기 (OPEN): 커서를 "연다"라는 뜻은 앞에서 커서로 정의된 쿼리문을 실행시키는 것을 뜻한다. 테이블에 있는 데이터를 커서로 가져오는 명령어이다. 실행 시킨후 해당 커서로 결과 집합을 가져온다. 라고 이해하면 되겠다.

• 예시: OPEN emp_csr;

---

3. 패치 (FETCH): 결과 집합이 커서라는 변수에 들어있는데 쿼리의 결과에 접근하여 그 데이터를 하나씩 가져오는 것이다. "하나씩"가져오는 것이 중요하다.
   한번 패치 후 그 다음 패치 가져오고, 다시 패치 후 그 다음 패치 가져온다. 결과 집합의 5개의 레코드가 있 으면 5번 패치가 가능하다. 결과집합에서 한 행씩 데이터를 가져와 변수에 저장한다.

• 예시: FETCH emp_csr INTO emp_id;

---

4. 커서 닫기 (CLOSE): 패치 후 결과 집합이 empty 가 된다면 커서를 닫고 자원을 반환하게 된다. 결과 집합을 갖고서 첫번째 레코드부터 마지막 레코드까지 하나씩 패치하여 데이터를 하나씩 가져오게된다. 이것이 명시적 커서의 처리방법이다. 커서를 닫고 자원을 해제한다.

• 예시: CLOSE emp_csr;

---

✅ 다시 한번 정리 하자면 selelct문의 결과는 결과를 그냥 보여줄 뿐이다. 그런데 이 결과집합의 각각의 레코드에 대해 무언가 해보고싶다면 커서를 이용하게 된다.

---

✅ 명시적 커서 예제 (Explicit Cursor Example)

DECLARE -- 커서 선언(SELECT문과 연결)
   CURSOR emp_csr IS
      SELECT employee_id FROM employees
      WHERE department_id = 100;

   emp_id employees.employee_id%TYPE;

BEGIN
   OPEN emp_csr; -- 커서 열기 (커서로 정의된 쿼리 실행)

   LOOP
      FETCH emp_csr INTO emp_id; --패치, 현재 데이터 행을 한 행씩 OUTPUT변수에반환
      EXIT WHEN emp_csr%NOTFOUND;
      DBMS_OUTPUT.PUT_LINE(emp_id);
   END LOOP;

   CLOSE emp_csr; -- 커서닫기, 커서 사용을 마치고 자원을 반납
END;

1. 커서 선언: CURSOR emp_csr IS SELECT employee_id FROM employees WHERE department_id = 100;

   • department_id가 100인 직원들의 employee_id를 가져오는 커서를 선언한다.

2. 커서 열기: OPEN emp_csr;

   • 커서가 정의되었으니 이제 오픈을 해야한다. 이 오픈 명령어는 커서의 질의문을 수행하고 그 결과집합을 커서에 저장하게 된다. 그 다음 LOOP를 작동한다.

3. 데이터 처리: FETCH emp_csr INTO emp_id;

   • 결과집합에서 한 행씩 데이터를 emp_id 변수에 저장한다. 또한 항상 맨 위에 있는 레코드 부터 접근하게 된다.

   • 더이상 데이터가 없을때까지(%NOTFOUND) emp_id에 store하게 된다.

4. 커서 닫기: CLOSE emp_csr;

   • 사용이 끝난 커서를 닫고 자원 반환하게 된다.

5. 실행 결과: 110, 109, 108... 등의 숫자가 출력되며, 이는 department_id가 100인 직원들의 employee_id이다.

---

✅명시적 커서의 FOR .. LOOP문 사용 (FOR Loop with Cursor)

FOR 루프를 사용하면 OPEN, FETCH, CLOSE 과정을 자동으로 처리하게 된다.

DECLARE
   CURSOR emp_csr IS
      SELECT employee_id FROM employees
      WHERE department_id = 100;

BEGIN
   FOR item IN emp_csr LOOP
      DBMS_OUTPUT.PUT_LINE(item.employee_id);
   END LOOP;
END;

• FOR 루프는 명시적 커서를 간결하게 작성할 수 있는 방법이다.

• item 변수를 통해 레코드를 자동으로 처리하며, 별도의 OPEN, FETCH, CLOSE가 필요 없게된다.

• 커서는 for문/ loop문 하고 같이 사용하는 경우가 굉장히 많다. 어떤 조건을 줄 때에는 loop가 많이 사용되고 조건 없이 커서의 모든 레코드를 탐색 할때는 for문을 사용하는 것이 유리하다.

---

커서(Cursor) #3: 묵시적 커서 (Implicit Cursor) - 오라클 내부의 자동 커서 처리

💡요약: Implicit Cursor (묵시적 커서)는 오라클 데이터베이스에서 자동으로 생성되며, 최근 실행된 SQL 문장(특히 DML 문장: INSERT, UPDATE, DELETE)에 대한 정보를 저장한다. 이는 내부적으로 사용되며, 명시적으로 선언하지 않아도 자동으로 생성된다.

✅ 묵시적 커서의 특징 (Key Features)

• 묵시적 커서와 사용자의 접점

  • 묵시적 커서는 오라클 내부에서 자동으로 처리되며, 사용자가 직접 접하는 경우는 많지 않다.

  • 주로 간단한 상태 확인(예: SQL 실행 성공 여부)이나 영향을 받은 행의 수를 확인하는 데 사용된.

• 자동 생성 (Automatically Generated)

  • 묵시적 커서는 사용자가 선언하지 않아도 SQL 실행 시 자동으로 생성됩니다.

  • 명시적 커서와 달리 프로그래밍적으로 선언, 열기, 닫기 과정이 필요하지 않습니다.

• DML 문과 주로 사용 (Used with DML Statements)

  • INSERT, UPDATE, DELETE 문 실행 시 생성되며, 쿼리 결과를 임시 저장합니다.

• 최근 SQL 문 정보 저장 (Tracks Last SQL Statement)

  • 항상 최근 실행된 SQL 문에 대한 정보를 속성(SQL%ROWCOUNT, SQL%FOUND, SQL%NOTFOUND 등)을 통해 접근할 수 있습니다.

• 간단한 작업 처리에 유용 (Simple Operations)

  • 묵시적 커서는 데이터를 조회하거나 변경한 결과를 빠르게 확인하기에 적합합니다.

---

✅ 묵시적 커서 속성 (Implicit Cursor Attributes)

• SQL%ROWCOUNT: 영향받은 행의 수 (Number of Rows Affected)

  • 최근 실행된 SQL 문(INSERT, UPDATE, DELETE 등)에 의해 영향을 받은 행의 개수를 나타냅니다.

  • 다른 SQL 문을 실행하면 값이 업데이트됩니다.

DBMS_OUTPUT.PUT_LINE('Rows affected: ' || SQL%ROWCOUNT);

---

• SQL%FOUND: SQL 실행 성공 여부 (SQL Execution Success)

  • 최근 SQL 문에 영향을 받은 행이 1개 이상일 경우 TRUE를 반환한다.

IF SQL%FOUND THEN
   DBMS_OUTPUT.PUT_LINE('Rows affected.');
END IF;

---

• SQL%NOTFOUND: SQL 실행 실패 여부 (SQL Execution Failure)

  • SQL%FOUND와 반대의 개념이다. 최근 SQL 문에 영향을 받은 행이 없을 경우 TRUE를 반환한다.

IF SQL%NOTFOUND THEN
   DBMS_OUTPUT.PUT_LINE('No rows affected.');
END IF;

---

• SQL%ISOPEN: 커서 열림 상태 (Cursor Open Status)

  • 묵시적 커서는 자동으로 닫히기 때문에 항상 FALSE를 반환하게 된다.

IF SQL%ISOPEN THEN
   DBMS_OUTPUT.PUT_LINE('Cursor is open.');
END IF;

---

✅ 묵시적 커서 예제 (Implicit Cursor Example): 묵시적 커서를 사용자가 접하게 되는 경우는 많이 없기 때문에 간단하게 살펴보고 넘어가도록 한다.

1. 테이블 초기화 및 생성

DROP TABLE employees_temp;
CREATE TABLE employees_temp AS
SELECT * FROM employees;

• employees_temp라는 임시 테이블이 이미 존재할 수 있으므로 삭제(DROP TABLE) 후 새로 정의하게 된다.

• CREATE TABLE 문을 사용해 employees 테이블의 데이터를 복사한다.

2. DELETE문과 SQL%ROWCOUNT 사용

DECLARE
   mgr_no NUMBER(6) := 122;
BEGIN
   DELETE FROM employees_temp WHERE manager_id = mgr_no;

   DBMS_OUTPUT.PUT_LINE(
      'Number of employees deleted: ' || TO_CHAR(SQL%ROWCOUNT)
   );
END;

• DECLARE후 mgr_no 를 변수로 선언한뒤 이 변수에 122를 할당한다.

• DELETE 문 수행한다. 이를 통해 employees_temp 테이블에서 manager_id가 122인 레코드를 삭제하게 된다. 6명이면 6명, 9명이면 9명이 삭제가 될 것이다.

• 이럴때 방금 수행한 명령문인 DELECT에 SQL%ROWCOUNT 커서가 잠시 열려 값을 갖고 있는데 SQL%ROWCOUNT의 역할은 방금 수행한 질의문의 영향을 받은 로우의 수를 출력해주는 것이다. 이를 사용해 영향을 받은 행의 개수를 출력하게 된다.

3. 실행 결과: 첨부된 세 번째 이미지를 참고하면,

•   Number of employees deleted: 8
    PL/SQL 프로시저가 성공적으로 완료되었습니다.
  

• manager_id = 122인 레코드 8개가 삭제되었다.

• 이때 이 (SQL%ROWCOUNT) sql 묵시적 커서를 지칭하는 역할을 한다.

---

💡PL/SQL 서브프로그램(Subprogram)

• 서브프로그램(Subprogram)은 메인 프로그램의 작업을 분리하고 반복 작업을 모듈화한 프로그램 블록이다.

• PL/SQL에서는 저장 프로시저(Stored Procedure), 함수(Function), 패키지(Package), 트리거(Trigger)를 서브프로그램(Subprogram)이라고 부른다. 이들은 각각 특정 목적에 맞게 사용된다.

• 서브프로그램은 메인 프로그램에서 자주 사용되는 기능이나 반복적인 작업을 모듈화하여 정의한 후, 필요할 때 호출하여 사용할 수 있는 프로그램 블록을 뜻한다.

• 메인 프로그램의 작업을 분리하여 효율성을 높이고 유지보수를 용이하게 하는 장점이 있다.

• 데이터베이스 내부 구조를 숨기고, 서브프로그램만 노출하여 데이터 보호할 수도 있다.

💡저장 프로시저와 함수의 사용 차이

• 프로그래밍 언어에서는 주로 계산이나 값 반환을 위한 함수(Function)를 많이 사용한다.

• 데이터베이스 프로그래밍에서는 데이터 조작이나 상태 변경 작업이 많아 저장 프로시저(Stored Procedure)가 더 자주 사용된다.

---

2️⃣저장 프로시저 (Stored Procedure)

💡요약: Stored Procedure는 데이터베이스 내에서 반복적으로 수행되는 작업을 효율적으로 처리하기 위해 사용된다. 재사용성과 성능 향상이 주요 장점으로, 잘 튜닝된 SQL을 미리 컴파일해 저장하고 반복적으로 호출할 수 있는 장점이 있다. DML 작업(INSERT, UPDATE, DELETE)뿐만 아니라, 조건부 로직과 파라미터를 활용한 복잡한 작업도 처리 가능하다.

---

✅ 저장 프로시저의 정의 (Stored Procedure)

• 데이터베이스 내에서 사전에 작성된 PL/SQL 문장의 집합으로, 특정 작업을 수행하기 위해 데이터베이스 서버에 저장된 프로그램 블록이다.

• 주로 데이터베이스의 상태 변경 작업(INSERT, UPDATE, DELETE)에 사용된다.

• "일련의 PL/SQL 문장을 사전에 작성하고 컴파일한 다음 실행할 수 있는 상태로 만들어 데이터베이스 서버에 저장해 놓은 프로시저."

---

✅저장 프로시저의 활용

1. DML 문장 작업의 효율화

   • 테이블의 레코드를 삽입(INSERT), 수정(UPDATE), 삭제(DELETE)할 때 사용.

   • 예를 들어, 특정 조건에 따라 급여를 수정하거나 특정 데이터 그룹을 삭제하는 작업을 자동화할 수 있다.

2. 파라미터를 이용한 동적 데이터 처리

   • 저장 프로시저는 입력 파라미터를 통해 데이터를 동적으로 처리하고, 필요 시 결과값을 반환할 수 있다.

   • 호출 시 파라미터만 전달하면, 프로시저 내부에서 해당 데이터를 사용해 작업을 수행할 수 있다.

---

✅ 저장 프로시저를 사용하는 이유와 장점

1. 재사용성

• SQL 문장을 매번 작성할 필요 없이, 자주 사용하는 DML 문장을 저장 프로시저로 정의하여 재사용 가능하기 때문이다.

• 잘 작성된 프로시저는 실수를 줄이고 다음에 또 만들지 않아도 되기 때문에 유지보수를 용이하게 한다.

2. 성능 최적화

• 미리 컴파일된 상태로 저장: 저장 프로시저는 작성 후 컴파일되어 데이터베이스에 저장되므로, 실행 시 실행 계획을 다시 계산하지 않아도 된다. 실행 계획이 변경되지 않으므로, 매번 새로 컴파일할 필요 없이 바로 실행 가능하다는 뜻이다.

• 성능 향상: 질의문 실행 시 매번 새로 작성하는 것보다, 미리 튜닝된 저장 프로시저를 사용하면 성능이 높아지게 된다.

3. 유지보수성

• 중앙 관리 가능: 저장 프로시저를 데이터베이스에 저장해 놓으면, 로직이 한 곳에 통합되므로 유지보수가 용이해진다. 변경이 필요한 경우, 저장 프로시저를 수정하면 된다.

4. 보안 강화

• 사용자에게 저장 프로시저만 노출하여 데이터베이스 구조와 로직을 숨길 수 있다.

• 개발자들이 테이블에 직접 접근하지 않고 저장 프로시저를 통해 작업을 수행하도록 제한하는 것이다.

5. 파라미터 활용

• 저장 프로시저는 입력(IN), 출력(OUT), 입출력(IN OUT) 파라미터를 지원하여, 유연한 데이터 처리가 가능하다.

---

✅ 저장 프로시저와 함수의 차이

---

저장 프로시저(Stored Procedure) #1: 구조와 실행

💡요약: 저장 프로시저는 데이터베이스 상태를 변경하거나 반복 작업을 효율적으로 처리하기 위해 사용되는 서브프로그램이다. 첨부된 이미지들을 참고하여 저장 프로시저의 구조와 실행에 대해 자세히 살펴보자

---

✅ Database Applications와 Stored Procedure의 관계

• 왼쪽의 Database Applications:

  • 데이터베이스 프로그램 코드들이 전시되어 있다. 이 중에서 stored procedure를 호출하게 된다.

  • hire_employees(...): 이 함수는 직원 고용 정보를 employee 테이블에 한명의 개인을 삽입하는 저장 프로시저로 보인다

  • 예를 들어, 이것을 매번쓰는 것이 아닌 새로운 직원 정보를 추가할 때마다 이 저장 프로시저를 호출하여 동일한 코드를 반복 작성하지 않아도 된다. stored procedure begin 과 end사이에 들어가게 된다.

• Stored Procedure의 주요 특징:

  • BEGIN과 END 사이에 데이터베이스 상태를 변경하는 명령문들이 포함된다

  • 프로그램 코드가 저장 프로시저를 호출하여 작업을 위임한다.

  • 함수하고 비슷하긴 하지만 함수는 주로 데이터베이스의 상태를 변경하는 것에 사용하진 않는다. 함수는 주로 데이터 베이스에서 값을 가지고와서 총 합 계산, 평균 계산, 가장 큰 값 가져오기 등 데이터베이스에서 어떤 값을 가져와서 필요한 값을 return 할때 함수를 많이 쓰는 반면, 저장프로시져는 데이터베이스의 상태를 변경, 위임하는데 사용한다.

  • 위에서 언급된 저장 프로시저의 장점 중 “보안성” 설명을 추가하자면 stored procedure의 권한은 dba에게 있다면 나머지 개발자들은 database applications만 불러서 사용하게 된다. 그렇게 되면 실제 데이터구조가 어떤지, 어떤 데이터를 가지고있는지 개발자들은 접근할 필요가 없게 된다.

---

✅저장 프로시저 정의 및 실행 (Defining and Executing a Stored Procedure)

문법 같은 경우는 좋은 예제를 가지고 조금씩 수정하면서 사용하는 것이 제일 좋다. 오라클의 가장 좋은 예제는 오라클 사이트에서 찾아볼 수 있다. 그런 코드를 참고해서 필요한 코드를 작성하면 되겠다.

1. 저장 프로시저 정의

CREATE PROCEDURE emp_register
IS
BEGIN
   INSERT INTO employees (employee_id, first_name, last_name, email, hire_date, job_id)
   VALUES (EMPLOYEES_SEQ.NEXTVAL, 'HONGSEOK', 'NA', 'hsna99@cuk.edu', SYSDATE, 'IT_PROG');
END;

• CREATE PROCEDURE: emp_register라는 이름의 저장 프로시저를 정의한다.

• BEGIN ... END: 데이터베이스 상태를 변경하는 명령문을 포함한다.

  • INSERT INTO employees:

    • 새로운 직원 정보를 employees 테이블에 삽입한다.

    • EMPLOYEES_SEQ.NEXTVAL: 시퀀스의 가장 최근값을 가져오란 뜻이다. employee_id의 다음 값을 생성.

    • first_name, last_name, email, hire_date, job_id이 값들을 INSERT INTO 에 넣는다.

  • 이 저장 프로시저는 단순한 예제로, 실제로는 동적 데이터를 처리하도록 파라미터를 추가해 활용할 수 있다.

---

2. 저장 프로시저 실행

• 저장 프로시저를 호출하여 실행한다.

    EXEC emp_register;
  

---

3. 결과 확인

• 이미지를 보면, emp_register를 실행한 후 데이터가 employees 테이블에 삽입되었음을 확인할 수 있다.

💡저장 프로시저 정리:

• 데이터베이스 상태를 변경하는 작업(INSERT, UPDATE, DELETE)을 효율적으로 처리하기 위해 사용된다.

• 프로그램 코드에서 저장 프로시저를 호출하여 반복적인 작업을 간소화할 수 있다.

• 보안 및 성능 이점이 있으며, 개발자는 저장 프로시저를 호출하여 데이터베이스 작업을 위임받아 수행한다.

• 예제에서 emp_register는 직원 정보를 employees 테이블에 삽입하는 간단한 저장 프로시저를 보여준다.

---

저장 프로시저(Stored Procedure) #2: IN 파라미터 활용

💡요약: 저장 프로시저를 사용할 때 파라미터(IN, OUT, IN OUT)를 활용하여 데이터베이스 작업을 동적으로 수행할 수 있다. 이번 예제에서는 IN 파라미터를 통해 직원 정보를 삽입하는 방법을 살펴본다.

---

1. 저장 프로시저 정의

CREATE PROCEDURE emp_register1
   (f_name VARCHAR2, l_name VARCHAR2, e_mail VARCHAR2, j_id VARCHAR2)
IS
BEGIN
   INSERT INTO employees (employee_id, first_name, last_name, email, hire_date, job_id)
   VALUES (EMPLOYEES_SEQ.NEXTVAL, f_name, l_name, e_mail, SYSDATE, j_id);
   COMMIT;
END;

• 파라미터 정의:

  • 저장 프로시저를 사용할 때는 주로 파라미터를 사용하여 데이터를 준다.

  • employee 한명을 삽입 하려고 할땐 employee 정보를 줘야 하는데 이것을 IN 파라미터로 주게된다. 한국어로는 매개변수이다.

• f_name, l_name, e_mail, j_id와 같은 입력값을 파라미터로 받아 테이블에 데이터를 삽입한다. 즉 emp_register1을 호출할 때 이 파라미터 값들을 주겠다는 뜻이다.

• 각 파라미터는 VARCHAR2 타입으로 정의되었다.

• INSERT INTO:

  • employee_id는 시퀀스 EMPLOYEES_SEQ.NEXTVAL을 사용해 자동으로 생성된다.

  • 나머지 필드는 파라미터로 전달받은 값을 삽입한다.

• COMMIT:

  • 데이터 삽입 후 변경 사항을 저장한다.

---

2. 저장 프로시저 컴파일 및 실행

• emp_register1 저장 프로시저를 생성하고 컴파일하였다.

• 컴파일 성공 메시지가 표시되며, 예제에서 빨간색 점선 박스처럼 프로시저가 완성되었음을 확인할 수 있다. 데이터베이스에서 해당 프로시저를 사용할 준비가 완료되었다는 뜻이다.

---

3.프로시저 호출 및 실행

• EXEC를 사용해 저장 프로시저를 호출하였다.

• 파라미터 값으로 first_name = 'gildong', last_name = 'hong', email = 'hdg@gmail.com', job_id = 'IT_PROG'을 전달한다.

• 실행 결과: 입력한 데이터를 기반으로 새로운 레코드가 employees 테이블에 삽입 되었다. (빨간 점선 박스참조)

---

저장 프로시저(Stored Procedure) #3: OUT 파라미터 활용

💡요약: OUT 파라미터도 존재하는데 주로 IN 파라미터를 많이 쓰게 된다. OUT 파라미터는 저장 프로시저가 수행된 후 호출한 프로그램이 결과 값을 받을 수 있도록 값을 반환하는 역할을 한다. 이는 함수의 반환값과 비슷하지만, 저장 프로시저는 리턴값이 없으므로 OUT 파라미터를 사용하여 결과를 전달하게 된다.

✅ OUT 파라미터의 특징

1. 결과값 반환: OUT 파라미터를 통해 저장 프로시저가 완료된 후 호출한 프로그램이 결과값을 받을 수 있다.

2. 리턴값 대신 사용: 저장 프로시저 자체는 리턴값이 없으므로, OUT 파라미터를 사용하여 값을 반환할 수 있다.

3. 함수와의 차이: 함수는 단일 값을 반환하지만, 저장 프로시저는 여러 OUT 파라미터를 사용해 여러 값을 반환할 수 있다.

---

✅OUT 파라미터를 사용하는 저장 프로시저의 구조

• OUT 파라미터

  • emp_id OUT NUMBER: 호출한 프로그램에 employee_id 값을 반환한다.

  • OUT 키워드는 프로시저가 값을 반환할 수 있도록 지정한다.

• INSERT INTO

  • 새로운 직원 정보를 employees 테이블에 삽입.

  • emp_id는 EMPLOYEES_SEQ.NEXTVAL을 사용하여 자동 생성된 값이다.

---

✅실행화면

• 저장 프로시저를 컴파일하면 성공 메시지가 표시된다.

• 호출 시 OUT 파라미터로 emp_id 값을 확인할 수 있다.

  • 예: 209 값이 반환되었음을 확인.

---

저장 프로시저(Stored Procedure) #4: DROP

• 불필요해진 저장 프로시저는 DROP PROCEDURE를 사용해 삭제할 수 있다.

---

3️⃣함수 (Function)

PL/SQL에서 함수(Function)의 특징과 저장 프로시저와의 차이점

함수와 저장 프로시저는 서로 다른 목적에 맞게 사용되며, 이들의 차이점을 이해하면 더 적합한 데이터베이스 작업을 설계할 수 있다.

---

✅ 함수(Function)의 정의와 특징

• 함수(Function)는 데이터베이스에서 계산 작업을 수행하고 결과를 반환(Return)하는 목적으로 사용된다.

• PL/SQL뿐만 아니라 거의 모든 DBMS에서 함수를 지원한다.

• 주요 목적:

  • 함수는 return값이 제일 중요하다. 항상 존재하기 때문이다. 어떤 계산을 하고 계산된 결과를 return하게 된다.

  • 함수(function)는 계산을 수행하여 호출한 애플리케이션에 반환하거나 결과집합에 통합해 넣을 목적으로 사용한다.

  • DBMS는 문자열 함수, 수학 함수, 집계 함수 등 많은 편리한 함수를 제공하고 사용자가 직접 함수를 정의할 수 있다.

  • 데이터베이스 상태를 변경하지 않고 계산 중심의 작업 수행 호출한 애플리케이션에 반환하거나 결과집합에 통합해 넣을 목적으로 사용한다.

---

✅저장 프로시저(Stored Procedure)와의 차이점

• 함수는 항상 값을 반환한다. (RETURN).

  • SELECT 문에 포함되어 호출된다.

  • 계산 용도로 사용하며 데이터베이스 상태를 변경하지 못한다.

  • 함수는 복잡한 계산이 필요한 경우나 급여의 합계 계산, 평균 또는 최대값과 같은 데이터를 조회하여 특정 결과를 도출해야할때 사용하기 적합하다.

• 저장 프로시저는 데이터베이스 상태를 변경하는 작업(INSERT, UPDATE, DELETE 등)을 수행다.

  • EXEC 또는 CALL로 호출되며, RETURN 값이 없다.

  • 저장 프로시저는 데이터베이스 상태를 변경해야 할때나 새로운 직원 정보 삽입 혹은 오래된 데이터 삭제와 같은 INSERT, UPDATE, DELECTE 와 같은 DML 작업을 수행할 때 사용하기 적합하다.

---

함수(Function) #1: PL/SQL 함수의 구조와 실행

✅ 함수의 구문 형식 (Function Syntax)

CREATE OR REPLACE FUNCTION 함수명(파라미터1 데이터타입, ...)  
RETURN 데이터타입  
IS [AS]  
   변수 선언부 ...;  
BEGIN  
   프로시저 본문 ...;  
   RETURN 변수;  
EXCEPTION  
   예외처리 ...;  
END;

• CREATE OR REPLACE:

  • CREATE만 써도 상관없지만

  • 보통은 CREATE OR REPLACE로 혹시 함수가 이미 존재할 경우 새 함수로 대체(Replace)하게 된다.

  • 기존 함수와 중복될 우려가 있는 경우 보통 이 구문을 사용한다.

• RETURN 데이터타입: 중요💡

  • 함수는 반드시 하나의 값을 반환해야 하며, 반환값의 데이터타입을 지정한다.

  • 예: RETURN NUMBER는 숫자 값을 반환.

• BEGIN ... END:

  • 함수는 BEGIN 과 END사이에서 정의 된다.

  • RETURN 문을 통해 반환값을 명시적으로 지정해야 한다가 함수의 가장 큰 특징이다.

• EXCEPTION:

  • 함수 실행 중 발생할 수 있는 예외를 처리하는 블록이다.

---

✅ 함수 예제: 직원 급여 반환

CREATE FUNCTION emp_salaries (emp_id NUMBER)
RETURN NUMBER IS
   nSalaries NUMBER(9);
BEGIN
   nSalaries := 0;
   SELECT salary INTO nSalaries FROM employees
   WHERE employee_id = emp_id;
   RETURN nSalaries;
END;

• 파라미터:

  • emp_id NUMBER: 함수가 입력받는 매개변수.

  • 직원 ID를 기준으로 급여(salary)를 조회.

• 반환값:

  • RETURN NUMBER: 숫자 데이터타입을 반환.

• 로직:

  1. nSalaries 변수를 초기화(nSalaries := 0;).

  2. SELECT salary INTO nSalaries로 해당 직원의 급여를 조회하여 변수에 저장.

  3. RETURN nSalaries를 통해 조회된 급여를 반환.

보안성 강화:

• SQL문 으로도 할수 있는데 굳이 함수로 하는 이유이다.

• 테이블 구조를 노출하지 않고 함수만 노출함으로써 필요한 데이터만 반환하므로 보안성이 강화된다.

• 권한이 제한된 개발자는 함수만 호출할 수 있어 테이블에 직접 접근하지 못한다.

---

함수 정의 및 호출

✅ 함수 정의 후 객체 생성

• 함수 정의가 완료되면 데이터베이스에 함수 객체로 저장된다.

• 예: emp_salaries 함수가 객체로 생성되었음을 확인 가능하다.

---

✅ 함수 호출

• SELECT 문을 통해 호출:

  • 함수는 반환값이 있으므로 SELECT 문에 포함되어 호출된다.

  • 예제에서는 직원 ID가 100인 직원의 급여를 조회.

• 결과: EMP_SALARIES(100): 24000(직원의 급여)이 반환되었다.

💡정리:

1. PL/SQL 함수는 특정 계산 작업을 수행하고 하나의 값을 반환한다.

2. 함수는 SELECT 문에서 호출되며, 반환값을 통해 다른 SQL 작업에 활용될 수 있다.

3. 이번 예제의 emp_salaries 함수는 직원 ID를 기반으로 급여를 반환하며, 데이터베이스 보안성을 강화할 수 있다.

---

함수(Function) #3: 함수의 또다른 예제: 부서 이름 변환

✅부서 이름을 반환하는 함수

CREATE FUNCTION get_dep_name (dept_id NUMBER)
RETURN VARCHAR2 IS
   sDeptname VARCHAR2(30);
BEGIN
   SELECT department_name INTO sDeptname FROM departments
   WHERE department_id = dept_id;
   RETURN sDeptname;
END;

1. 입력 파라미터:

   • dept_id NUMBER: 부서 번호를 입력받는 매개변수.

   • 이 값을 기준으로 부서 이름(department_name)을 조회한다.

2. 반환 타입:

   • RETURN VARCHAR2: 함수는 문자열 타입을 반환.

3. 로직:

   • sDeptname VARCHAR2(30) 변수 선언: 부서 이름을 저장할 문자열 변수.

   • SELECT department_name INTO sDeptname:

     • departments 테이블에서 department_id와 일치하는 행의 department_name을 가져온다

   • RETURN sDeptname: 조회된 부서 이름을 반환한다.

---

✅함수 호출

SELECT get_dep_name(100) FROM dual;

• 호출 방법: SELECT 문을 사용해 get_dep_name 함수를 호출한다. 입력값 100을 전달하여 부서 이름을 조회한다.

---

✅실행 결과

• 입력값: dept_id = 100

• 출력값: Finance (부서 이름)

• 함수는 부서 번호 100에 해당하는 부서 이름을 반환하였다.

---

함수(Function) #4: 함수와 프로시저를 사용하는 장점

✅ 보안성 (Security)

• 데이터에 대한 직접 접근 제한:

  • 데이터베이스에 직접 접근하는 대신, 프로시저와 함수를 통해 접근하도록 제한할 수 있다. 이를 통해 데이터베이스의 내부 구조를 숨기고 보안을 강화한다.

  • 예: 권한이 제한된 사용자/개발자는 테이블에 직접 접근하지 않고, 제공된 함수나 프로시저만 호출 가능하다.

---

✅ 성능 개선 (Performance Improvement)

• 미리 컴파일된 상태로 저장:

  • 함수와 프로시저는 미리 컴파일되어 SGA(Shared Global Area)의 공유 풀에 저장된다.

  • 이로 인해 반복적인 실행 시 바로 호출 가능하며, 실행 계획을 다시 계산하지 않아도 된다.

• 공유 자원의 활용:

  • 여러 사용자가 동일한 함수나 프로시저를 공유해 사용함으로써 성능을 최적화한다.

---

✅ 재사용성 (Reusability)

• 코드 모듈화:

  • 자주 사용되거나 오류가 발생해서는 안 되는 코드를 미리 함수나 프로시저로 정의하여 모듈화한다.

  • 필요할 때 호출만 하면 되므로 코드의 재사용성이 높아지고 유지보수가 용이하다.

• 가독성 증가:

  • 복잡한 로직을 간단하게 함수나 프로시저로 캡슐화하여 프로그램의 가독성을 향상시킨다.

---

✅ 데이터 무결성 보장 (Integrity)

• 오류 발생 가능성 감소:

  • 직접 SQL 문을 작성하여 사용하는 대신, 프로시저와 함수에 로직을 구현하면 통합된 로직으로 오류 발생 가능성을 줄일 수 있다.

  • 개발자마다 SQL 문을 개별적으로 작성하다 보면 발생할 수 있는 불일치나 오류가 발생할 수 있다. 혹은 감지를 처음엔 못하지만 나중에 발생 할 수도 있다.

• 업무 로직에 따른 일관성 유지:

  • 프로시저와 함수는 업무 로직에 맞추어 프로그래밍되므로 데이터 무결성을 보장할 수 있다.

---