[pl/sql] 6가지 유형의 Select문과 적절한 변수 설정

이상원·2023년 11월 17일

PL/SQL

목록 보기

3/5

해당 예제는 EMPLOYEES 라는 테이블에서 진행하고 테이블 구조는 다음과 같습니다.

샘플 데이터

1. 값 하나를 리턴하는 Select 문

create or replace procedure p1 (
    p_employee_id in  employees.employee_id%type,
    p_salary      out employees.salary%type
  )
  is
    v_salary employees.salary %type;
  begin
    select salary into v_salary
    from employees
    where employee_id = p_employee_id;

    p_salary := v_salary;
  end;

사원의 id를 입력하면 그 사원의 급여를 반환하는 프로시져
급여 값 하나를 리턴한다.

테스트

declare
    v_id  number := 100;
    v_ret number;
  begin
    p1(v_id, v_ret);
    dbms_output.put_line(v_id||'의 급여는 '||v_ret);
  end;

결과

2. 행 하나, 모든 열을 리턴하는 SELECT 문

create or replace procedure p1 (
    p_employee_id in  employees.employee_id%type,
    p_last_name   out employees.last_name%type,
    p_salary      out employees.salary%type,
    p_job_id      out employees.job_id%type
  )
  is
    employee_row employees%rowtype;
  begin
    select * into employee_row
    from employees
    where employee_id = p_employee_id;

    p_last_name := employee_row.last_name;
    p_salary    := employee_row.salary;
    p_job_id    := employee_row.job_id;
  end;
  /

위 프로시져는 사원의 id를 입력하면 *(아스티리스크)로 select문을 반환하고 그 결과 중 사원의 이름, 급여, 직책id를 반환하는 프로시져
3가지 변수가 필요하여 3개의 매개변수를 out 키워드로 넣어주었으나 100개가 필요하면 100개를 모두 입력할 수 없음.

create or replace procedure p1 (
    p_employee_id  in  employees.employee_id%type,
    p_employee_row out employees%rowtype
  )
  is
  begin
    select * into p_employee_row
    from employees
    where employee_id = p_employee_id;
  end;
  /

이렇게 행 하나를 반환 받을 때는 tablename%rowtype 을 사용할 수 있다.

테스트

declare
    v_employee_id employees.employee_id%type := 100;
    employee_row  employees%rowtype ;
  begin
    p1(v_employee_id, employee_row);
    dbms_output.put_line(v_employee_id);
    dbms_output.put_line(employee_row.last_name);
    dbms_output.put_line(employee_row.salary);
    dbms_output.put_line(employee_row.job_id);
  end;
  /

결과

3. 행 하나, 특정 열을 리턴하는 SELECT 문

프로시져안에서 레코드 선언

create or replace procedure p1(
    p_employee_id  in  employees.employee_id%type
  )
  is
    TYPE employee_record_type IS RECORD (
      last_name employees.last_name%type,
      salary    employees.salary%type,
      job_id    employees.job_id%type);

    employee_row employee_record_type;
  begin
    select last_name, salary, job_id into employee_row
    from employees
    where employee_id = p_employee_id;

    dbms_output.put_line(employee_row.last_name);
    dbms_output.put_line(employee_row.salary);
    dbms_output.put_line(employee_row.job_id);
  end;
  /

위 프로시져는 특정한 컬럼을 Record로 받아서 처리한다.
Type 키워드로 타입을 선언

Package에 Record를 넣기

특정한 컬럼이 많을 때 코드가 길어지는 단점이 있다.
자주 사용할 것 같은 Record를 다음과 같이 Package에 레코드를 넣어 처리할 수 있다.

create or replace package pack_datatypes
  is

    TYPE employee_record_type IS RECORD (
      last_name employees.last_name%type,
      salary    employees.salary%type,
      job_id    employees.job_id%type);

  end;
  /

  create or replace procedure p1(
    p_employee_id  in  employees.employee_id%type
  )
  is
    employee_row pack_datatypes.employee_record_type;
  begin
    select last_name, salary, job_id into employee_row
    from employees
    where employee_id = p_employee_id;

    dbms_output.put_line(employee_row.last_name);
    dbms_output.put_line(employee_row.salary);
    dbms_output.put_line(employee_row.job_id);
  end;
  /

VIEW 사용하기

위에서 '자주 사용할 것 같은' 이라는 말을 썼는데 이 정도 말만 들어도 VIEW가 떠오를 것이다.
패키지를 사용할 수도 있지만 VIEW를 사용하여 첫번째 방식처럼 사용할 수도 있다.
권장되는 방식은 패키지를 사용하는 두 번째 방식.

create or replace view view_for_row_type
  as
  select last_name, salary, job_id 
  from employees;

  create or replace procedure p1 (
    p_employee_id  in  employees.employee_id%type,
    p_employee_row out view_for_row_type%rowtype
  )
  is
    employee_row view_for_row_type%rowtype;
  begin
    select last_name, salary, job_id into p_employee_row
    from employees
    where employee_id = p_employee_id;
  end;
  /

테스트

  declare
    v_employee_id employees.employee_id%type := 100;
    employee_row  view_for_row_type%rowtype; 
  begin
    p1(v_employee_id, employee_row);
    dbms_output.put_line(v_employee_id);
    dbms_output.put_line(employee_row.last_name);
    dbms_output.put_line(employee_row.salary);
    dbms_output.put_line(employee_row.job_id);
  end;
  /

결과

PL/SQL Collection Types

출처: Oracle Collections and Records

4. 여러 행, 열 하나를 리턴하는 Select 문

create or replace procedure p1 (
    p_department_id in employees.department_id%type
  )
  is
    TYPE employees_salary_tab_type IS TABLE OF employees.salary%type
     INDEX BY pls_integer;

    emp_sal_tab employees_salary_tab_type;
  begin
    select salary BULK COLLECT INTO emp_sal_tab
    from employees
    where department_id = p_department_id;

    for i in emp_sal_tab.first .. emp_sal_tab.last loop
      dbms_output.put_line(emp_sal_tab(i));
    end loop;
  end;
  /

  exec p1(20)
  exec p1(50)

IS TABLE OF, INDEX BY pls_integer 키워드로 테이블 타입을 생성(배열처럼 활용됨)
into 키워드는 하나의 값에만 적용할 수 있기때문에 BULK COLLECT INTO 키워드를 사용하여 맵핑
3번의 경우와 마찬가지로 패키지로 미리 타입을 정의할 수 있다.
↓↓

  create or replace package pack_datatypes
  is

    TYPE employee_record_type IS RECORD (
      last_name employees.last_name%type, 
      salary    employees.salary%type,
      job_id    employees.job_id%type);

    TYPE employees_salary_tab_type IS TABLE OF employees.salary%type
     INDEX BY pls_integer;

  end;
  /
----------------------------------------------------------------------
  create or replace procedure p1 (
    p_department_id in  employees.department_id%type,
    p_emp_sal_tab   out pack_datatypes.employees_salary_tab_type
  )
  is
  begin
    select salary BULK COLLECT INTO p_emp_sal_tab
    from employees
    where department_id = p_department_id;
  end;
  /
-----------------------------------------------------------------------

  create or replace procedure p1_print (
    p_department_id in employees.department_id%type    
  )
  is
    emp_sal_tab pack_datatypes.employees_salary_tab_type;
  begin
    p1(p_department_id, emp_sal_tab);

    for i in emp_sal_tab.first .. emp_sal_tab.last loop
      dbms_output.put_line(emp_sal_tab(i));
    end loop;
  end;
  /

패키지에 테이블 타입 추가
p1 프로시져로 결과를 매핑
p1_print 프로시져로 결과 출력
이런 식으로 refactoring 하는 것이 유지보수하기에 좋다!

테스트 맟 결과

5. 여러 행, 모든 열를 리턴하는 Select 문

create or replace procedure p1(
    p_department_id in employees.department_id%type
  )
  is
    TYPE employees_table_type IS TABLE OF employees%rowtype
      INDEX BY pls_integer;
    
    employees_tab employees_table_type;
  begin
    select * BULK COLLECT INTO employees_tab
    from employees
    where department_id = p_department_id;

    for i in employees_tab.first .. employees_tab.last loop
      dbms_output.put_line(employees_tab(i).last_name||', '||employees_tab(i).salary);
    end loop;
  end;
  /

4번과 비슷하지만 IS TABLE OF 다음에 employees%rowtype으로 모든행의 타입을 받아온다
package에 넣는 것은 생략

테스트

begin
    p1(20);
    dbms_output.put_line('---------');
    p1(50);
end;

결과

6. 여러 행, 특정 열를 리턴하는 Select 문

create or replace procedure p1(
    p_department_id in employees.department_id%type
  )
  is

    TYPE employee_record_type IS RECORD (
      last_name employees.last_name%type, 
      salary    employees.salary%type,
      job_id    employees.job_id%type);

    TYPE employees_table_type IS TABLE OF employee_record_type
      INDEX BY pls_integer;

    employees_tab employees_table_type;
  begin
    select last_name, salary, job_id BULK COLLECT INTO employees_tab
    from employees
    where department_id = p_department_id;

    for i in employees_tab.first .. employees_tab.last loop
      dbms_output.put_line(employees_tab(i).last_name||', '||employees_tab(i).salary);
    end loop;
  end;
  /

특정 열만 뽑기 위해 특정 열을 레코드 타입으로 선언하고 그렇게 선언한 타입을 IS TABLE OF로 넣는다
package에 넣는 것은 생략

테스트

begin
    p1(20);
    dbms_output.put_line('---------');
    p1(50);
end;

결과

이상원

Sang9riG9ru

이전 포스트

[pl/sql] CRUD하는 Stand-alone Subprogram(stored procedure / stored function) 및 Package를 만들자

다음 포스트

[pl/sql] 6가지 유형의 Select문과 적절한 변수 설정

PL/SQL

1. 값 하나를 리턴하는 Select 문

2. 행 하나, 모든 열을 리턴하는 SELECT 문

3. 행 하나, 특정 열을 리턴하는 SELECT 문

프로시져안에서 레코드 선언

Package에 Record를 넣기

VIEW 사용하기

PL/SQL Collection Types

4. 여러 행, 열 하나를 리턴하는 Select 문

5. 여러 행, 모든 열를 리턴하는 Select 문

6. 여러 행, 특정 열를 리턴하는 Select 문

[pl/sql] CRUD하는 Stand-alone Subprogram(stored procedure / stored function) 및 Package를 만들자

[pl/sql] 트리거(trigger)란

0개의 댓글