Oracle 데이터베이스에서 테이블 목록을 가져오고 각 테이블의 필드 정보와 총 행 수를 기반으로 페이징된 SELECT 문을 동적으로 생성

 

 Oracle 데이터베이스에서 테이블 목록을 가져오고 각 테이블의 필드 정보와 총 행 수를 기반으로 페이징된 SELECT 문을 동적으로 생성하는 Node.js 예제를 아래와 같이 정리했습니다.

목표

1. 테이블 목록 가져오기: 특정 사용자가 소유한 테이블을 조회합니다.
2. 각 테이블의 컬럼 정보와 총 행 수 구하기: COUNT(*)를 사용하여 각 테이블의 총 레코드 수를 가져옵니다.
3. 페이징 처리: 각 테이블에 대해 10,000개 레코드씩 페이징하여 ROWNUM을 이용해 SELECT 문을 생성합니다.

 

728x90

Node.js 코드 예제

const oracledb = require('oracledb');

// Oracle DB 연결 정보
const dbConfig = {
  user: 'your_username',
  password: 'your_password',
  connectString: 'your_connect_string'  // 예: 'localhost:1521/XE'
};

async function getPagedSelectQuery() {
  let connection;

  try {
    // Oracle DB 연결
    connection = await oracledb.getConnection(dbConfig);

    // 데이터베이스에서 테이블 목록 가져오기
    const tablesResult = await connection.execute(
      `SELECT table_name FROM all_tables WHERE owner = :owner`,
      [dbConfig.user.toUpperCase()]  // 해당 사용자 소유의 테이블을 가져오기
    );

    const tables = tablesResult.rows;

    // 각 테이블의 필드 정보와 페이징된 SELECT 문 생성
    for (const table of tables) {
      const tableName = table[0];
      console.log(`Table: ${tableName}`);

      // 테이블의 컬럼 정보 가져오기
      const columnsResult = await connection.execute(
        `SELECT column_name FROM all_tab_columns WHERE table_name = :tableName AND owner = :owner`,
        [tableName, dbConfig.user.toUpperCase()]
      );

      const columns = columnsResult.rows;
      const columnsList = columns.map(col => col[0]).join(', ');

      // 총 행 수 구하기
      const countResult = await connection.execute(
        `SELECT COUNT(*) FROM ${tableName}`
      );

      const totalRows = countResult.rows[0][0];
      console.log(`Total rows in ${tableName}: ${totalRows}`);

      // 페이징을 위한 ROWNUM을 사용한 SELECT 문 생성
      const pageSize = 10000;
      const totalPages = Math.ceil(totalRows / pageSize);

      for (let page = 1; page <= totalPages; page++) {
        const startRow = (page - 1) * pageSize + 1;
        const endRow = page * pageSize;

        const pagedQuery = `
          SELECT * FROM (
            SELECT ${columnsList}, ROWNUM AS rnum
            FROM ${tableName}
            WHERE ROWNUM <= :endRow
          )
          WHERE rnum >= :startRow
        `;
        
        const pagedQueryResult = await connection.execute(pagedQuery, [endRow, startRow]);
        console.log(`Paged Query (Page ${page}): ${pagedQuery}`);
        console.log(`Results:`, pagedQueryResult.rows);
        console.log('----------------------------------------');
      }
    }
  } catch (err) {
    console.error('Error:', err);
  } finally {
    if (connection) {
      try {
        await connection.close();
      } catch (err) {
        console.error('Error closing connection:', err);
      }
    }
  }
}

// 실행
getPagedSelectQuery();

728x90

코드 설명

1. 테이블 목록 가져오기: all_tables에서 사용자 소유의 테이블 목록을 조회합니다.
2. 각 테이블의 컬럼 정보: all_tab_columns에서 해당 테이블의 컬럼 정보를 조회하고, 이를 기반으로 SELECT 문을 동적으로 생성합니다.
3. 총 행 수 구하기: 각 테이블에 대해 COUNT(*) 쿼리로 총 행 수를 구합니다.
4. 페이징 처리: 총 행 수에 따라 페이지 수를 계산하고, 각 페이지에 대해 10,000개씩 데이터를 조회할 수 있는 ROWNUM을 사용한 쿼리를 생성합니다.

쿼리 예시

예를 들어, EMPLOYEES 테이블에 25,000개의 행이 있을 경우, 생성되는 쿼리는 다음과 같습니다:

SELECT * FROM (
  SELECT EMPLOYEE_ID, FIRST_NAME, LAST_NAME, HIRE_DATE, ROWNUM AS rnum
  FROM EMPLOYEES
  WHERE ROWNUM <= 10000
)
WHERE rnum >= 1;

SELECT * FROM (
  SELECT EMPLOYEE_ID, FIRST_NAME, LAST_NAME, HIRE_DATE, ROWNUM AS rnum
  FROM EMPLOYEES
  WHERE ROWNUM <= 20000
)
WHERE rnum >= 10001;

SELECT * FROM (
  SELECT EMPLOYEE_ID, FIRST_NAME, LAST_NAME, HIRE_DATE, ROWNUM AS rnum
  FROM EMPLOYEES
  WHERE ROWNUM <= 30000
)
WHERE rnum >= 20001;

동작 설명

• 각 페이지마다 10,000개의 레코드를 가져올 수 있도록 ROWNUM을 활용해 페이징된 SELECT 문을 생성합니다.
• 총 레코드 수에 따라 필요한 만큼의 페이지 쿼리가 생성됩니다.

이 코드는 테이블이 많고 데이터가 큰 경우 유용하게 사용할 수 있습니다.