프로시져 vs 함수

요약

• SQL에 포함할 수 있는 Subprogram(procedure, function)을 만들고자 할 경우만 function을 만들자
• 나머지 다른 경우는 프로시져를 만들자.
• SQL에 포함할 수 있는 함수가 되려면 아래 Requirements를 준수하자.

Requirements for Calling PL/SQL Functions from SQL Expressions

https://www.oraclechennai.com/Calling-Stored-Functions-from-SQL-Expressions.html

To be callable from SQL expressions, a user-defined PL/SQL function must meet the following basic requirements:

• It must be a stored function, not a function defined within a PL/SQL block or subprogram.
• It must be a row function, not a column (group) function; in other words, it cannot take an entire column of data as its argument.
• All its formal parameters must be IN parameters; none can be an OUT or IN OUT parameter.
• The datatypes of its formal parameters must be SQL built-in types, such as CHAR, DATE, or NUMBER, not PL/SQL types, such as BOOLEAN, RECORD, or TABLE.
• Its return type (the datatype of its result value) must be an SQL built-in type.

---

SQL 식에서 호출 가능한 사용자 정의 PL/SQL 함수는 다음의 기본 요구 사항을 충족해야 합니다:

• PL/SQL 블록이나 서브프로그램 내에서 정의된 것이 아닌, 저장된 함수여야 합니다.
• 컬럼(그룹) 함수가 아닌 로우 함수여야 하며, 다시 말해 전체 열 데이터를 인수로 받을 수 없습니다.
• 모든 형식 매개변수는 IN 매개변수여야 하며, OUT 또는 IN OUT 매개변수일 수 없습니다.
• 형식 매개변수의 데이터 유형은 BOOLEAN, RECORD 또는 TABLE과 같은 PL/SQL 형식이 아닌 CHAR, DATE 또는 NUMBER와 같은 SQL 내장 형식이어야 합니다.
• 반환 유형(결과 값의 데이터 유형)은 SQL 내장 형식이어야 합니다.

• 위 Requirements를 지켜야 SQL에 포함할 수 있는 함수를 만들 수 있다.

SQL에 포함할 수 있는 함수 예제

  create or replace function number_of_days_worked(
    p_employee_id in employees.employee_id%type
  )
    return number
  is 
    v_days number;
  begin
    select ceil(sysdate - hire_date) into v_days
    from employees
    where employee_id = p_employee_id;   

    return v_days;
  end;
  /

  select employee_id, number_of_days_worked(employee_id) "근속일수"
  from employees;

  exec dbms_output.put_line(number_of_days_worked(101))

SQL에 포함할 수 없는 함수 예제 1

  create or replace function findEmployeeById_func(
    p_employee_id in employees.employee_id%type
  )
    return employees%rowtype
  is 
    emp_row employees%rowtype;
  begin
    select * into emp_row
    from employees
    where employee_id = p_employee_id;   

    return emp_row;
  end;
  /

  -- 에러임
  select findEmployeeById_func(100)
  from dual;

  set serveroutput on

  -- 성공함
  declare
    ret employees%rowtype;
  begin
    ret := findEmployeeById_func(100);
    dbms_output.put_line(ret.last_name);
    dbms_output.put_line(ret.salary);
  end;
  /

SQL에 포함할 수 없는 함수 예제 2

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

    return 0; 
  end;
  /

  -- 변수명에 변수를 넣을 수 있는가??
  select employee_id, employees_salary_func(employee_id, 변수명)
  from employees;


  -- 성공함
  declare
    v_ret employees.salary%type;
    v_sal employees.salary%type;
  begin
    v_ret := employees_salary_func(100, v_sal);
    dbms_output.put_line(v_sal);
  end;
  /