brunch

매거진 오라클 DB의 첫걸음

라이킷 14 댓글

You can make anything
by writing

C.S.Lewis

계정을 잊어버리셨나요?

by GTPlus Oct 16. 2023

SQL*Plus 대화식 명령어

Oracle Database SQL*Plus 명령어 활용

안녕하세요. 지티플러스입니다.

이번 글에서는 사용자 변수 정의 및 치환 변수를 사용한 대화식 명령어 작성에 대해 다룹니다.

대화식 명령어 작성

일반 사용자 입력이 허용되는 명령 파일을 설정할 수 있도록 해주는 SQL*Plus 기능들입니다.

사용자 변수 정의

명령어 값 치환

START 명령어를 사용한 값 제공

값 프롬프트

◆ 사용자 변수 정의

SQL*Plus DEFINE 명령어를 사용하여 단일 명령 파일에서 반복 사용하기 위한 변수(사용자 변수)를 정의할 수 있습니다. 또한 사용자 변수를 정의하여 제목에서 사용하거나 긴 문자열을 단축 이름을 갖는 변수 값으로 정의하여 입력 작업량을 줄일 수 있습니다.

사용자 변수인 EMPLOYEE를 정의하여 “SMITH”라는 값을 부여하려면 다음 명령어를 입력합니다.

변수 정의를 확인하려면 DEFINE 명령어 다음에 변수 이름을 입력합니다.

모든 사용자 변수 정의를 보려면 명령 프롬프트에서 DEFINE만 입력합니다. DEFINE 명령어를 통해 명시적으로 정의하는 사용자 변수는 CHAR 값으로만 처리됩니다. 즉, 변수에 할당하는 값은 항상 CHAR 데이터 유형으로 처리됩니다.

ACCEPT 명령어를 사용하여 암시적으로 NUMBER 데이터 유형의 사용자 변수를 정의할 수 있습니다. ACCEPT 명령어는 뒷부분에서 설명하겠습니다.

사용자 변수를 삭제하려면 SQL*Plus UNDEFINE 명령어와 그 뒤에 변수 이름을 입력합니다.

◆ 치환 변수 사용

SALES 파일에서와 같은 질의를 작성하여 직무가 SALESMAN인 고용인뿐만 아니라 다양한 직무의 고용인 목록을 보려면 명령어를 실행할 때마다 서로 다른 CHAR 값을 WHERE 절로 편집해도 되지만 더 쉬운 방법이 있습니다.

WHERE 절에서 SALESMAN이라는 값 대신 치환 변수를 사용함으로써 명령어 자체에 값을 입력하여 얻은 것과 동일한 결과를 얻을 수 있습니다.

치환 변수는 사용자 변수 이름 앞에 하나 또는 두 개의 앰퍼샌드(&)를 붙인 것입니다. 명령어에 치환 변수가 나오면, 변수 자체가 아니라 치환 변수 값이 포함된 것처럼 명령어가 실행됩니다.

예를 들어, 변수 SORTCOL 값이 JOB이고 변수 MYTABLE 값이 EMP라고 가정합니다.

위의 명령어는 다음 명령어와 동일하게 실행됩니다.

BREAK 명령어는 SORTCOL에 지정된 열 값이 중복되지 않도록 합니다.

◆ 치환 변수 사용법

명령 프롬프트에서 입력되는 첫 번째 단어를 제외하고 SQL과 SQL*Plus 명령어의 어느 곳에나 치환 변수를 사용할 수 있습니다. 명령어에 정의되지 않은 치환 변수가 있으면 해당 값을 입력하라는 프롬프트가 나타납니다.

프롬프트에서는 공백과 문장 부호가 포함된 문자열을 비롯하여 어떠한 문자열도 입력할 수 있습니다. 참조가 포함된 SQL 명령어에서 인용 부호를 붙여야 하는 변수에 이를 붙이지 않았다면 프롬프트 나타날 때 인용 부호를 붙여야 합니다.

단말기 입력이나 출력을 파일로 재지정한 경우에도 키보드로부터 응답을 읽어 들입니다. 단말기를 사용할 수 없으면(예를 들어, 명령 파일을 일괄처리 방식으로 실행할 경우) 재지정된 파일이 사용됩니다.

프롬프트에서 값을 입력하면 입력한 값을 치환하기 전에 한 번, 그리고 치환한 후에 한 번, 모두 두 번의 치환 변수가 포함된 행이 화면에 표시됩니다. 이 행이 표시되지 않도록 하려면 SET 명령어의 VERIFY 변수를 OFF로 설정합니다.

◆ 치환 변수 사용 예제

수치 열에 있는 하위 그룹 통계(최댓값)를 계산하는 데 사용될 STATS라는 명령 파일을 다음과 같이 작성합니다.

그런 다음 STATS 명령 파일을 실행하고 프롬프트에 다음 값을 입력하여 결과를 확인합니다.

치환 변수 바로 뒤에 문자를 추가하려면 마침표를 사용하여 문자와 변수를 구별합니다.

예를 들어, 위의 명령어는 다음과 같이 해석됩니다.

◆ 불필요한 프롬프트 화면 표시 방지

STATS 파일을 “숫자” 열의 최솟값, 총합, 평균이 포함되도록 확장한다고 가정합니다. 치환 변수 사용 예제에서 GROUP_COL 값과 NUMBER_COL 값을 입력하기 위해 두 번의 프롬프트가 나오고 각 GROUP_COL 또는 NUMBER_COL 앞에는 하나의 앰퍼샌드(&)가 붙어있습니다. 각각의 앞에 단일 앰퍼샌드를 사용하여 위의 세 함수를 STATS 명령 파일에 추가하면 해당 숫자 열 값을 입력하기 위해 프롬프트가 네 번 나타나게 됩니다.

STATS 파일의 각 GROUP_COL과 NUMBER_COL 앞에 하나의 앰퍼샌드를 더 붙이면 그룹 열과 숫자 열에 대해 프롬프트가 다시 나오지 않게 됩니다. 앞에 두 개의 앰퍼샌드가 붙은 치환 변수는 자동으로 DEFINE 되지만 하나의 앰퍼샌드만 있는 치환 변수는 DEFINE 되지 않습니다. 변수를 DEFINE 한 경우에는 변수를 참조하는 각 치환 변수(& 변수 또는 && 변수의 형태)로 변수 값이 치환됩니다. 변수를 UNDEFINE 할 때까지 이 세션에서는 변수 값을 입력하라는 프롬프트가 표시되지 않습니다.

◆ 이중 앰퍼샌드(&) 사용

이중 앰퍼샌드를 사용하여 STATS 명령 파일을 확장한 다음 실행하려면 먼저 치환 전후의 각 행이 화면에 표시되지 않도록 설정합니다.

다음 명령어를 입력하여 STATS 파일을 읽어 들이고 편집합니다.

마지막으로 STATS2 명령 파일을 실행하고 프롬프트에서 다음 값을 입력하여 결과를 확인합니다.

NUMBER_COL과 GROUP_COL 값에 대한 프롬프트가 한 번만 나오는 것에 유의해야 합니다. 현재 세션 동안 STATS2를 다시 실행하면 TABLE 값을 묻는 프롬프트가 표시됩니다. (이는 앰퍼샌드(&)가 하나이므로 변수가 DEFINE 되지 않기 때문입니다.) 그러나 GROUP_COL이나 NUMBER_COL 값을 묻는 프롬프트는 표시되지 않습니다. (이는 이름에 이중 앰퍼샌드(&)가 붙어 있고 따라서 변수가 DEFINE 되기 때문입니다.)

시스템 변수인 VERIFY를 다시 ON으로 설정한 후 계속 진행합니다.

◆ 제한 사항

버퍼 편집 명령어인 APPEND, CHANGE, DEL, INPUT이나 치환이 의미를 갖지 않는 REMARK 등의 명령어에서는 치환 변수를 사용할 수 없습니다. 버퍼 편집 명령어 APPEND, CHANGE, INPUT은 “&” 또는 “&&”로 시작되는 텍스트를 다른 문자열처럼 문자 그대로 취급합니다.

◆ 시스템 변수

SQL*Plus SET 명령어와 함께 지정되는 다음 시스템 변수들은 치환 변수에 영향을 줍니다.

SET DEFINE : 치환 문자(기본적으로 앰퍼샌드 “&”가 사용됨)를 정의하고 치환 여부를 설정합니다.

SET ESCAPE : 치환 문자 앞에 사용할 수 있는 확장 문자를 정의합니다. 확장 문자는 치환 문자를 변수 치환 요청이 아니라 보통 문자와 마찬가지로 취급하도록 SQL*Plus에 지시합니다. 기본 확장 문자는 백슬래시(＼)입니다.

SET VERIFY ON : 치환 전후 명령 파일의 각 행 화면에 표시합니다.

SET CONCAT : 변수 또는 매개변수 바로 뒤에 오는 문자와 치환 변수 또는 매개변수 이름을 구분하는 문자를 정의합니다. 기본적으로 마침표(.)가 사용됩니다.

◆ START 명령어를 통한 매개변수 전달

START 명령어를 통해 명령 파일에 있는 매개변수에 값을 전달하여 치환 변수와 관련된 값 입력 프롬프트가 나타나지 않도록 할 수 있습니다.

그렇게 하려면 앰퍼샌드(&) 뒤에 명령 파일에 있는 숫자를 붙여 치환 변수 자리에 놓습니다. 이 명령 파일을 실행할 때마다 START 명령어를 파일에 있는 각 &1을 START 파일 이름 후의 첫 번째 값(인수)으로, 각 &2를 두 번째 값으로 대체하는 방식으로 대체합니다.

예를 들어, 명령 파일 MYFILE에 다음 명령어를 포함시킬 수 있습니다.

SELECT * FROM EMP

WHERE JOB=’&1’

AND SAL=&2

다음 START 명령어에서 명령 파일 MYFILE의 &1은 CLERK로, &2는 7900으로 대체됩니다.

START 명령어와 함께 인수를 사용하면 명령 파일의 각 매개변수는 해당 인수 값으로 정의됩니다.

◆ START 명령어를 통한 매개변수 전달

SALES 파일을 기초로 하여 화면에 표시될 직책을 지정하는 매개변수를 사용하는 새로운 명령 파일을 작성하려면 다음을 입력합니다.

매개변수 CLERK과 함께 START 명령어를 실행합니다.

매개변수를 값으로 대체하기 전과 후의 매개변수를 포함하는 SQL 명령행이 화면에 표시되고 그다음으로 결과가 화면에 나타납니다.

명령 파일에서는 매개변수를 몇 개든지 사용할 수 있습니다. 명령 파일에서는 각 매개변수를 몇 번이든 참조할 수 있으며 어떤 순서로든 포함시킬 수 있습니다.

주의: RUN 또는 슬래시(/)를 사용하여 명령어를 실행할 때는 매개변수를 사용할 수 없습니다. 명령 파일에 명령어를 저장하고 START나 @를 사용하여 실행해야 합니다.

계속 진행하기 전에 다음 명령어를 입력하여 ENAME 열을 원래의 머리글로 되돌려 놓아야 합니다.

◆ 사용자와의 통신

SQL*Plus PROMPT, ACCEPT, PAUSE 명령어는 일반 사용자와 통신할 수 있도록 도와줍니다. 이러한 명령어를 사용하여 메시지를 화면에 보내고, 단순한 [ENTER]를 비롯한 사용자 입력을 받을 수 있습니다. 또한 PROMPT와 ACCEPT 명령어를 사용하여 치환 변수에 대해 자동으로 생성되는 값의 입력 프롬프트를 사용자 정의할 수 있습니다.

◆ 사용자 변수 프롬프트 및 승인

PROMPT와 ACCEPT 명령어를 통해서 일반 사용자에게 메시지를 보내고 일반 사용자가 입력하는 값을 받아들일 수 있습니다. 지정한 메시지를 화면에 표시하는 PROMPT 명령어는 사용자에게 지시 사항이나 정보를 전달하는 데 사용됩니다. ACCEPT 명령어는 사용자에게 값을 입력하라는 프롬프트를 표시하고 입력된 값을 지정한 사용자 변수에 저장합니다. 한 행 이상의 값에 대한 프롬프트인 경우, ACCEPT 명령어와 함께 PROMPT를 사용합니다.

◆ 프롬프트 표시 및 입력 승인

사용자에게 보고서 제목을 제공하도록 지시하고 이후의 질의에서 사용하기 위해 입력된 사항을 MYTITLE 변수에 저장하려면 먼저 버퍼를 비웁니다.

그리고 다음과 같이 명령 파일을 설정합니다.

TITLE 명령어는 보고서의 제목을 설정합니다.

마지막으로, 명령 파일을 실행하고 제목 프롬프트에 다음을 입력하여 결과를 확인합니다.

계속 진행하기 전에 명령 파일에 입력한 TTITLE 명령어를 다음과 같이 OFF로 설정합니다.

◆ 치환 변수 프롬프트 사용자 정의

치환 변수 값을 입력하라는 프롬프트를 사용자 정의하려면 다음 예와 같이 치환 변수와 함께 PROMPT와 ACCEPT를 사용합니다.

◆ PROMPT와 ACCEPT를 치환 변수와 함께 사용하기

프롬프트 표시 및 입력 승인에서 볼 수 있는 것처럼 치환 변수를 사용하면 값을 입력하라는 프롬프트가 자동으로 생성됩니다. 명령 파일에 치환 변수를 참조하는 질의와 함께 PROMPT와 ACCEPT를 포함시켜 이 프롬프트를 대체할 수 있습니다. 그러한 파일을 작성하려면 다음을 입력합니다.