오라클 함수

안녕하세요, ko 님! 공식 서포터즈 codesweaver 입니다.

@NamedStoredProcedureQuery 를 활용하여 프로시저를 사용하실 수 있습니다. 다음과 같은 방법을 사용합니다.

// oracle
CREATE OR REPLACE PROCEDURE add_employee (
  p_first_name IN VARCHAR2,
  p_last_name IN VARCHAR2,
  p_email IN VARCHAR2
)
AS
BEGIN
  INSERT INTO employees (first_name, last_name, email)
  VALUES (p_first_name, p_last_name, p_email);
  COMMIT;
END;

// java
@NamedStoredProcedureQuery(
  name = "addEmployee",
  procedureName = "add_employee",
  parameters = {
    @StoredProcedureParameter(mode = ParameterMode.IN, name = "p_first_name", type = String.class),
    @StoredProcedureParameter(mode = ParameterMode.IN, name = "p_last_name", type = String.class),
    @StoredProcedureParameter(mode = ParameterMode.IN, name = "p_email", type = String.class)
  }
)

// 
StoredProcedureQuery storedProcedure = entityManager.createNamedStoredProcedureQuery("addEmployee");
storedProcedure.setParameter("p_first_name", "John");
storedProcedure.setParameter("p_last_name", "Doe");
storedProcedure.setParameter("p_email", "john.doe@example.com");
storedProcedure.execute();

QueryDSL 에서는 다음과이 nativeQuery 로 프로시져를 사용합니다.

// oracle 
CREATE OR REPLACE PROCEDURE get_employee (
  p_id IN NUMBER,
  o_first_name OUT VARCHAR2,
  o_last_name OUT VARCHAR2,
  o_email OUT VARCHAR2
)
AS
BEGIN
  SELECT first_name, last_name, email
  INTO o_first_name, o_last_name, o_email
  FROM employees
  WHERE id = p_id;
END;

// java
QEmployee employee = QEmployee.employee;
NumberPath<Long> idPath = employee.id;

StringPath firstNamePath = Expressions.stringPath("o_first_name");
StringPath lastNamePath = Expressions.stringPath("o_last_name");
StringPath emailPath = Expressions.stringPath("o_email");

JPAQuery<?> query = new JPAQuery<>(entityManager);
query.nativeQuery("CALL get_employee(:id, :o_first_name, :o_last_name, :o_email)")
  .setParameter("id", 1)
  .setParameter(firstNamePath, "")
  .setParameter(lastNamePath, "")
  .setParameter(emailPath, "");

String firstName = query.fetchOne().get(firstNamePath);
String lastName = query.fetchOne().get(lastNamePath);
String email = query.fetchOne().

감사합니다.