EGL 및 SQL을 사용하여 관계형 데이터베이스에 액세스 >
< 이전 | 다음 >

학습 4: prepare를 사용하여 동적으로 조회 생성

EGL prepare 문을 사용하여 동적으로 SQL 문을 생성할 수 있습니다.

이전 학습에서 데이터베이스에서 가격이 매개변수 이상인 항목을 선택하는 함수를 작성했습니다. 
function printExpensiveItems(maxPrice Price in)
  
tempItem Item;
open expensiveItems for tempItem with
  #sql{
    select
      EGL.ITEM.ITEM_ID, EGL.ITEM.NAME, EGL.ITEM.IMAGE, 
      EGL.ITEM.PRICE, EGL.ITEM.DESCRIPTION
    from EGL.ITEM
    where
      EGL.ITEM.PRICE >= :maxPrice
    order by
      EGL.ITEM.ITEM_ID asc
  };
  
forEach (tempItem)
  SysLib.writeStdout(tempItem.Name :: " " ::
    tempItem.Description :: " $" :: tempItem.Price);
end
매개변수 이하인 가격의 항목을 표시하려는 경우를 고려해보십시오. 또는, 최대 및 최소 가격 사이의 항목을 표시하려는 경우를 고려해보십시오. SQL 문의 WHERE절에서의 비교는 하드 코딩되어 있으므로 open 문에서 명시적 SQL을 사용하여 세 함수(각 경우에 대해 하나씩)를 코딩해야 합니다.
보다 유연하게 SELECT 문을 작성하는 방법으로는 EGL prepare 문을 사용하여 런타임 시 조회를 조합하는 방법이 있습니다. prepare 문을 사용할 때 우선 다음과 같이 SQL 코드 텍스트가 들어 있는 STRING 변수를 작성합니다. 
selectStatement STRING = 
  "select * from EGL.ITEM where EGL.ITEM.PRICE >= 500";
그런 다음, prepare 문을 사용하여 문자열을 SQL 문으로 변환합니다. 명령문의 이름과 선택적으로 레코드 변수를 지정하여 명령문이 작동할 SQLRecord 부분을 표시해야 합니다. 
tempItem Item;
prepare preparedStatement from selectStatement for tempItem;
그런 다음, 다음과 같이 EGL get, open 또는 execute 문의 명시적 SQL 대신 prepared 문을 사용할 수 있습니다(이후에 execute에 대해 자세히 학습). 
open expensiveItems for tempItem with preparedStatement;

prepared 문의 일반 오류

이 방식으로 문자열에서 SQL 문을 작성하는 것은 SQL에 능숙한 프로그래머를 위한 고급 기술입니다. EGL은 설계 시 SQL 문이 정확한지 확인하지 않으므로 런타임 시 문자열이 올바른 SQL 문으로 해석되는지 확인해야 합니다. 다음은 prepared 문을 사용하여 작업할 때 일반적으로 발생하는 실수 목록입니다.

호스트 변수
prepared 문에서는 명시적 SQL 문에서와 같이 호스트 변수를 사용할 수 없습니다. 예를 들어 다음과 같습니다. 
tempItem Item;
maxPrice Price = 500;
selectStatement STRING = 
  "select * from EGL.ITEM where EGL.ITEM.PRICE >= :maxPrice";
prepare preparedStatement from selectStatement for tempItem;
이 경우, 실제 SQL 문은 :maxPrice 문자열을 호스트 변수로 사용하여 maxPrice 변수의 값으로 바꾸지 않고 있는 그대로를 포함합니다. 결과적으로 잘못된 SQL 문이 생성됩니다.
대신, 변수값을 문자열에 삽입해야 합니다. 
tempItem Item;
maxPrice Price = 500;
selectStatement STRING = 
  "select * from EGL.ITEM where EGL.ITEM.PRICE >= " :: maxPrice;
prepare preparedStatement from selectStatement for tempItem;
이 경우, EGL이 문자열을 작성할 때 maxPrice 변수의 값이 해석되어 다음 SQL 문이 작성됩니다. 
select * from EGL.ITEM where EGL.ITEM.PRICE >= 500
나중에 prepared 문에서 변수를 사용하는 방법의 대안에 대해 설명할 것입니다.
공백 지정
SQL 문에서 키워드 사이에 공백을 두어 분리하도록 하십시오. 예를 들어, 여러 문자열 값을 병합 연산자(::=)로 병합하여 준비하는 다음과 같은 SQL 문이 있습니다. 
incorrectSQL STRING;
incorrectSQL = "select * from EGL.ITEM where";
incorrectSQL ::= "EGL.ITEM.PRICE >= :maxPrice";
incorrectSQL ::= "order by EGL.ITEM.PRICE";
이 예제는 다음과 같은 잘못된 SQL 문을 생성합니다. 
select * from EGL.ITEM whereEGL.ITEM.PRICE >= :maxPriceorder by EGL.ITEM.PRICE
이 명령문은 WHERE 뒤와 ORDER BY 앞에 공백이 필요합니다.
올바르고 적절한 명령문
EGL은 prepared 문의 유효성을 검증하지 않으므로 명령문이 올바른 SQL이 되었는지 사용자가 확인해야 합니다.

명시적 SQL과 같이 prepared SQL 문은 함께 사용되는 EGL 문에 적합해야 합니다. 예를 들어, EGL get 및 open 문은 결과 세트를 예상합니다. 이 명령문 중 하나와 함께 prepared 문을 사용하려면 SQL 코드가 결과 세트를 리턴해야 합니다. 이러한 이유로 인해 SQL INSERT 또는 DELETE 문을 준비하여 해당 명령문을 get 또는 open과 사용할 수 없습니다.

학습 4A: prepared 문 사용

이 학습에서는 하나 또는 둘 모두가 널(null)이 될 수 있는 두 개의 매개변수를 승인하도록 이전 학습에서 작성한 프로그램을 변경할 것입니다. 함수는 널(null)이 아닌 매개변수를 기반으로 조회를 작성하여 특정 가격 범위의 항목을 리턴합니다.

  1. selectItems.egl 파일을 여십시오.
  2. 다음과 같이 널 입력 가능 Price 매개변수를 받는 printItemRange라는 함수를 추가하십시오. 
    function printItemRange(minPrice Price? in, maxPrice Price? in)

end

    매개변수 유형 다음의 물음표(?)는 변수가 널값을 받을 수 있다는 것을 나타냅니다. 이 방식으로 함수가 결과 범위의 최소값 및 최대값 중 하나 또는 둘 모두를 승인하게 됩니다.

  3. 다음과 같이 SQL 문을 보유할 STRING 변수를 작성하고 SELECT 문의 시작을 이 변수에 삽입하십시오. 
    selectStatement STRING = "select * from EGL.ITEM where ";
    다음 단계는 적절한 WHERE절을 판별하는 것입니다. 다음과 같이 네 가지의 가능한 경우가 있습니다.
    • 두 매개변수 모두 널입니다. 이 경우, 함수는 가격이 0 이상인 모든 항목을 인쇄하므로 WHERE절이 where EGL.ITEM.PRICE > 0이어야 합니다.
    • 두 매개변수가 지정됩니다. 이 경우, 함수가 SQL BETWEEN 키워드를 사용하여 매개변수와 같거나 그 사이에 있는 값을 인쇄합니다. WHERE절은 where EGL.ITEM.PRICE between :minPrice and :maxPrice여야 합니다.
    • 최대값 매개변수만 지정되었습니다. 이 경우, 함수는 가격이 최대값 이하인 모든 항목을 인쇄합니다. where EGL.ITEM.PRICE <= :maxPrice.
    • 최소값 매개변수만 지정되었습니다. 이 경우, 함수는 가격이 최소값 이상인 모든 항목을 인쇄합니다. where EGL.ITEM.PRICE >= :minPrice.
    EGL에서 여러 방식으로 이 결정을 내릴 수 있지만, 세 개의 중첩된 if 문을 사용하는 방법이 간단합니다.
  4. 다음 코드를 함수에 추가하여 WHERE절을 완료하십시오. 
    if (minPrice == NULL && maxPrice == NULL)
  // Two empty parameters;
  // return all rows with PRICE greater than zero.
  selectStatement ::= "EGL.ITEM.PRICE > 0 ";
else

  if (minPrice != NULL && maxPrice != NULL)
    // Both parameters were specified.
    selectStatement ::= "EGL.ITEM.PRICE between " :: minPrice :: " and " :: maxPrice :: " ";
  else
    // Only one parameter was specified.
    if (minPrice == NULL)
      // Maximum was specified.
      selectStatement ::= "EGL.ITEM.PRICE <= " :: maxPrice :: " ";
    else
      // Minimum was specified.
      selectStatement ::= "EGL.ITEM.PRICE >= ":: minPrice :: " ";
    end
  end
  
end
  5. 다음과 같이 ORDER BY절로 명령문을 완료하십시오. 
    selectStatement ::= "order by EGL.ITEM.PRICE";
  6. SQL 문이 올바른지 확인하려면 콘솔에 인쇄하십시오. 
    SysLib.writeStdout("Completed query: "::selectStatement);
  7. prepared 문이 액세스할 테이블에서 작성된 레코드를 참조하여 사용할 명령문을 준비하십시오. 
    tempItem Item;
prepare preparedStatement 
  from selectStatement
  for tempItem;
  8. open 문에서 명시적 SQL 대신 prepared 문을 사용하십시오. 
    open expensiveItems for tempItem with preparedStatement;
  9. 다음과 같이 조회 결과를 인쇄하십시오. 
    forEach (tempItem)
  SysLib.writeStdout(tempItem.Name :: " " ::
    tempItem.Description :: " $" :: tempItem.Price);
end
    전체 함수는 다음과 같습니다. 
      function printItemRange(minPrice Price? in, maxPrice Price? in)
    
    selectStatement STRING = "select * from EGL.ITEM where ";
    
    if (minPrice == NULL && maxPrice == NULL)
      // Two empty parameters;
      // return all rows with PRICE greater than zero.
      selectStatement ::= "EGL.ITEM.PRICE > 0 ";
    else
    
      if (minPrice != NULL && maxPrice != NULL)
        // Both parameters were specified.
        selectStatement ::= "EGL.ITEM.PRICE between " :: minPrice :: " and " :: maxPrice :: " ";
      else
        // Only one parameter was specified.
        if (minPrice == NULL)
          // Maximum was specified.
          selectStatement ::= "EGL.ITEM.PRICE <= " :: maxPrice :: " ";
        else
          // Minimum was specified.
          selectStatement ::= "EGL.ITEM.PRICE >= ":: minPrice :: " ";
        end
      end
      
    end
    
    // Regardless of parameters, sort by price.
    selectStatement ::= "order by EGL.ITEM.PRICE";
    
    // Print completed SQL code.
    SysLib.writeStdout("Completed query: "::selectStatement);
    
    // Prepare the statement to be used.
    tempItem Item;
    prepare preparedStatement 
      from selectStatement
      for tempItem;
    
    // Use the prepared statement in place of explicit SQL.
    open expensiveItems for tempItem with preparedStatement;
    
    // Print the results to the console.
    forEach (tempItem)
      SysLib.writeStdout(tempItem.Name :: " " ::
        tempItem.Description :: " $" :: tempItem.Price);
    end
    
  end
  10. 다음과 같이 프로그램의 main 함수에서 함수를 호출하십시오. 
      function main()
    minPrice, maxPrice Price?;
    minPrice = 50;
    maxPrice = 500;
    try
      printItemRange(minPrice, maxPrice);
    onException(exception SQLException)
      handleDBException(exception);
    end
  end
  11. 프로그램을 생성하고 실행하십시오.
위 예제의 매개변수 값을 사용하여 함수가 가격이 50 - 500인 항목을 인쇄합니다. 
Completed query: select * from EGL.ITEM where EGL.ITEM.PRICE 
  between 50.00 and 500.00 order by EGL.ITEM.PRICE
Serial Mouse Great little mouse.  All kinds'a applications. $65.22
Keyboard Got QWERTY?  If not, go get this awesome flat keyboard. $78.99
Watch Keep time - and look rakish! $99.01
Shredder Don't let that politically-sensitive document fall into the wrong hands! $198.99
Combination Safe Keep your valuables safe and secure.  Put 'em in here. $222.22
다음 예제에서와 같이 매개변수 값을 편집하여 널 값을 포함하는 다른 범위를 시도할 수 있습니다. 
function main()
  minPrice, maxPrice Price?;
  minPrice = 500;
  maxPrice = null;
  try
    printItemRange(minPrice, maxPrice);
  onException(exception SQLException)
    handleDBException(exception);
  end
end
500과 널 값을 전달하면 다음과 같은 결과가 발생합니다. 
Completed query: select * from EGL.ITEM where 
EGL.ITEM.PRICE >= 500.00 order by EGL.ITEM.PRICE
Desktop PC Lots'a power, at the right price.  Includes monitor. $888.55
Laptop PC Great little machine.  Take it anywhere with you. $1111.11

selectItems.egl 파일의 전체 코드가 생성되었습니다. 파일에 빨간색 X 기호로 표시된 오류가 나타나면 코드가 학습 4A에서 완성된 selectItems.egl 파일의 코드와 일치하는지 확인하십시오.

학습 4B: prepare 문의 호스트 변수

이전 예제의 SQL 문은 WHERE절의 변수가 고정되었다는 점에서 한계가 있습니다. 명령문에서 사용할 변수를 알기 전에 prepared 문을 작성할 수도 있습니다. 이 유형의 명령문은 다소 복잡하지만 명령문을 준비한 다음에 세부사항을 입력하려는 경우에는 유연성을 제공할 수 있습니다.

이전 예제에서 나머지 조회 문자열과 병합하기 위해 변수값을 해석해야 했습니다. 
selectStatement STRING = "select * from EGL.ITEM where ";
...
selectStatement ::= "EGL.ITEM.PRICE between " :: minPrice :: " and " :: maxPrice :: " ";
prepared 문을 작성할 때 명령문에 물음표(?)를 두어 나중에 값을 입력할 것이라는 것을 나타낼 수 있습니다. 
selectStatement2 STRING = "select * from EGL.ITEM where " ::
  "EGL.ITEM.PRICE between ? and ? " ::
  "order by EGL.ITEM.PRICE";

tempItem Item;
prepare preparedStatement2 
  from selectStatement2
  for tempItem;
이제, prepared 문에는 두 호스트 변수의 위치가 있으며 BETWEEN절에서 물음표로 표시됩니다. prepared 문을 사용할 준비가 되면 using 문을 사용하여 값을 삽입하십시오. 예를 들어, 5 - 500의 가격이 있는 행을 검색하려면 다음과 같이 리터럴 5와 500을 명령문에 전달하십시오. 
open expensiveItems2 for tempItem with preparedStatement2
  using 5, 500;
물론 다음과 같이 using 키워드와 함께 변수를 사용할 수도 있습니다. 
open expensiveItems2 for tempItem with preparedStatement2
  using minPrice, maxPrice;
명령문을 한 번 조합한 다음 서로 다른 값을 사용하여 여러 번 호출하려는 경우, 이 방식으로 prepared 문에서 호스트 변수를 사용하면 매우 유용합니다. 예를 들어, 이 함수는 같은 prepared 문을 세 번 사용하여 서로 다른 세 범위의 값을 인쇄합니다. 
function printRanges()
  
  tempItem Item;
  selectStatement string = "select * from EGL.ITEM where " ::
    "EGL.ITEM.PRICE between ? and ? " ::
    "order by EGL.ITEM.PRICE";

  prepare preparedStatement from selectStatement for tempItem;

  SysLib.writeStdout("\nPrinting values between 0 and 200");
  open expensiveItems for tempItem with preparedStatement using 0, 200;
  forEach(tempItem)
    SysLib.writeStdout(tempItem.Name :: " " :: tempItem.Description ::
      " $" :: tempItem.Price);
  end

  SysLib.writeStdout("\nPrinting values between 200 and 500");
  open expensiveItems for tempItem with preparedStatement using 200, 500;
  forEach(tempItem)
    SysLib.writeStdout(tempItem.Name :: " " :: tempItem.Description ::
      " $" :: tempItem.Price);
  end

  SysLib.writeStdout("\nPrinting values between 500 and 1,000");
  open expensiveItems for tempItem with preparedStatement
    using 500, 1000;
  forEach(tempItem)
    SysLib.writeStdout(tempItem.Name :: " " :: tempItem.Description ::
      " $" :: tempItem.Price);
  end

end

이 방식으로 호스트 변수를 사용하면 각 경우의 명령문을 수정하는 대신 단일 prepared 문을 사용하여 이전 섹션에서 프로그램을 다시 쓸 수 있습니다.

  1. 이전 섹션에서와 같이 selectItems.egl 파일에서 최대값 및 최소값 매개변수를 승인하는 새 함수를 추가하십시오. 
    function printItemRange2(minPrice Price? in, maxPrice Price? in)

end
    이 함수에서는 먼저 명령문을 준비한 다음 변수를 나중에 삽입합니다.
  2. 다음과 같이 prepared 문을 보유할 string 변수를 작성한 다음 prepare 문을 사용하여 준비하십시오. 
    selectStatement STRING = "select * from EGL.ITEM where " ::
  "EGL.ITEM.PRICE between ? and ? " ::
  "order by EGL.ITEM.PRICE";

tempItem Item;
prepare preparedStatement 
  from selectStatement
  for tempItem;
    이 prepared 문에 두 매개변수를 전달하여 검색의 경계를 나타낼 것입니다. 최대 가격이 지정되지 않은 경우 일단 명령문을 준비하고 나면 EGL.ITEM.PRICE between ? and ?절을 EGL.ITEM.PRICE >= ?로 변환할 수 없으므로 검색할 상위 경계값을 알아야 합니다. 따라서, 테이블의 가장 비싼 가격을 판별하여 상위 경계값으로 사용해야 합니다. SQL MAX() 함수를 사용하여 이 상위 경계값을 쉽게 판별할 수 있습니다.
  3. 다음과 같이 Item 변수를 작성하고 MAX() 함수를 사용하여 데이터베이스에서 가장 비싼 가격의 항목을 검색하여 레코드 변수의 Price 필드에 두십시오. 
    get mostExpensiveItem 
  into mostExpensiveItem.Price
  with
    #sql{
      select
        max(EGL.ITEM.PRICE)
      from EGL.ITEM
    };
    이제 mostExpensiveItem 레코드는 Items 테이블의 가장 높은 가격을 포함합니다.
  4. 지정된 매개변수를 판별하고 그에 알맞는 prepared 문을 사용하십시오. 
    if (minPrice == NULL && maxPrice == NULL)
  // Two empty parameters;
  open expensiveItems for tempItem with preparedStatement 
    using 0, mostExpensiveItem.Price;
else

  if (minPrice != NULL && maxPrice != NULL)
    // Both parameters were specified.
    open expensiveItems for tempItem with preparedStatement 
      using minPrice, maxPrice;
  else
    // Only one parameter was specified.
    if (minPrice == NULL)
      // Maximum was specified.
      open expensiveItems for tempItem with preparedStatement 
        using 0, maxPrice;
    else
      // Minimum was specified.
      open expensiveItems for tempItem with preparedStatement 
        using minPrice, mostExpensiveItem.Price;
    end
  end
  
end
  5. 다음과 같이 forEach를 사용하여 결과를 인쇄하십시오. 
    forEach (tempItem)
  SysLib.writeStdout(tempItem.Name :: " " ::
    tempItem.Description :: " $" :: tempItem.Price);
end
    전체 함수는 다음과 같습니다. 
    function printItemRange2(minPrice Price? in, maxPrice Price? in)
  
  selectStatement STRING = "select * from EGL.ITEM where " ::
    "EGL.ITEM.PRICE between ? and ? " ::
    "order by EGL.ITEM.PRICE";
  
  // Prepare the statement to be used.
  tempItem Item;
  prepare preparedStatement 
    from selectStatement
    for tempItem;
  
  mostExpensiveItem Item;
  get mostExpensiveItem 
    into mostExpensiveItem.Price
    with
    #sql{
      select
        max(EGL.ITEM.PRICE)
      from EGL.ITEM
    };
    
  if (minPrice == NULL && maxPrice == NULL)
    // Two empty parameters;
    open expensiveItems for tempItem with preparedStatement 
      using 0, mostExpensiveItem.Price;
  else
  
    if (minPrice != NULL && maxPrice != NULL)
      // Both parameters were specified.
      open expensiveItems for tempItem with preparedStatement 
        using minPrice, maxPrice;
    else
      // Only one parameter was specified.
      if (minPrice == NULL)
        // Maximum was specified.
        open expensiveItems for tempItem with preparedStatement 
          using 0, maxPrice;
      else
        // Minimum was specified.
        open expensiveItems for tempItem with preparedStatement 
          using minPrice, mostExpensiveItem.Price;
      end
    end
    
  end
  
  // Print the results to the console.
  forEach (tempItem)
    SysLib.writeStdout(tempItem.Name :: " " ::
      tempItem.Description :: " $" :: tempItem.Price);
  end
  
end
  6. 다음과 같이 이전 함수가 아니라 새 함수를 사용하도록 main 함수를 수정하십시오. 
    function main()
  minPrice, maxPrice Price?;
  minPrice = 50;
  maxPrice = 500;
  try
    printItemRange2(minPrice, maxPrice);
  onException(exception SQLException)
    handleDBException(exception);
  end
end
  7. 널(null) 값을 포함하는 서로 다른 값의 minPrice 및 maxPrice를 사용하여 새 프로그램을 저장하고 생성하여 실행하십시오. 결과는 이전 함수와 같지만 함수가 결과에 도달하는 방식은 다릅니다.
selectItems.egl 파일의 전체 코드가 생성되었습니다. 파일에 빨간색 X 기호로 표시된 오류가 나타나면 코드가 학습 4B에서 완성된 selectItems.egl 파일의 코드와 일치하는지 확인하십시오.

학습 체크포인트

올바른 prepared 문을 작성하는 것은 복잡할 수 있습니다. 하지만 그 결과로 생성된 명령문은 명시적 SQL 문으로 하드 코딩된 명령문보다 더 유연합니다. 이후의 학습에서는 다른 방식으로 SQL 문을 사용자 정의할 수 있는, SQLRecord 파트의 기본 SQL 코드 설정에 대해 설명합니다.

prepared 문에 대한 자세한 정보는 prepare를 참조하십시오.
< 이전 | 다음 >