출처 : http://naver.kaist.ac.kr/oracle_sql/s08.htm
1. 치환 변수
◈명령이 실행될 때마다 사용자에게 입력값을 요구한다.
◈ SET VERIFY 명령 : SQL*Plus가 치환변수를 값으로 바꾸기 전후의 값을 Display한다
♣ 단일 앰퍼센드 치환 변수
◈SQL*Plus는 사용자 변수를 써서 반환되는 데이터를 동적으로 제한한다.
◈SQL문장에서 변수를 식별하기 위하여 앰퍼센드(&)를 사용한다.
◈각 변수의 값을 정의할 필요는 없다.
◈일시적으로 값을 저장하기 위해 SQL *Plus의 치환변수를 사용한다.
| 표기법 | 설 명 |
| &user_variable | SQL문장의 변수. 변수가 없으면 SQL *Plus는 사용자가 변수값을 쓰도록 프롬프트를 나타낸다. 명령이 실행될 때마다 입력값을 요구한다. |
| &&user_variable | SQl *Plus가 세션 동안 또는 변수가 재설정되거나 삭제될 때까지 변수와 그 값을 갖고 있다는 것만 제외하면 위와 동일한 역할을 한다. 명령이 실행될 때마다 입력값을 묻지 않는다. |
♠실습 : 실행시에 사용자에게 프롬프트를 주어 부서번호를 입력할 수 있는 문장을 작성하라.
직원의 번호, 성과 월급을 포함해야 한다.
| SQL> SELECT dept_id, id, last_name,salary Enter value for department_number: 10 [Enter] |
※ 단일 앰퍼센드를 써서 명령이 실행될 때마다 사용자에게 입력값을 묻는다.
♣ 치환변수에 문자 및 날짜값을 명시
◈문자 및 날짜값은 단일따옴표(')로 에워싸야 한다.
◈사용자가 단일따옴표를 입력하지 않도록 앰퍼센트와 변수에 단일 인용부호를 표시한다,
♠실습 : 사용자가 프롬프트에서 입력한 직급의 모든 사원의 사번, 성 및 월급여를 검색하여 보시오.
| SQL> SELECT title,id,last_name,salary Enter value for job_title: Stock Clerk [Enter] |
♣ 실행시 Column명 및 조건절 입력받기
◈다음을 입력받기 위해 치환변수를 사용할 수 있다.
- WHERE절, ORDER BY절, columnaud, 테이블명, 전체 SELECT 문장
♠실습 : s_ord 테이블에서 컬럼명을 입력받는 경우 1
| SQL> SELECT id,&column_name Enter value for column_name: total [Enter] |
♣ 이중 앰퍼센드 치환 변수
매번 사용자에게 프롬프로하지 않고 변수값을 재사용하려면 이중 앰퍼센드(&&)를 사용한다.
값을 입력하도록 요구하는 프롬프트는 한 번만 보게 된다.
◈SQL *Plus는 DEFINE 명령을 써서 제공된 값을 저장 한다.
♠실습 : 모든 주문에 대한 주문번호와 주문합계를 출력하라. TOTAL column에 따라 정렬하고
TOTAL column을 입력받기 위해 변수를 사용하라.
| SQL> define column_name = total |
2. 사용자 변수의 정의
◈아래 두 개의 SQL*Plus의 명령 중 하나를 써서 변수를 미리 정의한다.
- DEFINE : CHAR 데이터 유형의 사용자 변수 생성
- ACCEPT : 사용자 입력을 읽어 변수에 저장
◈SQL*Plus는 이중 엠퍼센드 치환 변수에 대해 DEFINE명령을 실행한다.
◈DEFINE 명령을 사용할 때 한 개의 공백이 필요하면 공백을 단일 따옴표로 묶어야 한다.
♣ 사용자 변수의 정의
SELECT 문장을 실행하기 전에 사용자 변수를 미리 정의할 수 있다. SQL*Plus는 사용자 변수를 정의하고 설정하기 위한 두 가지의 명령인 DEFINE과 ACCEPT명령을 제공한다. 이 중 엠퍼센드가 있는 문장을 실행하면 DEFINE 명령은 변수를 생성하고 변수의 값을 지정한다.
| 명령어 | 설 명 |
| DEFINE 변수 = 값 | CHAR datatype의 사용자변수를 생성하고 값을 지정 |
| DEFINE 변수 | 변수, 변수값 및 datatype출력 |
| DEFINE | 모든 사용자변수. 변수값과 datatype출력 |
| ACCEPT(아래 참조) | 사용자 입력을 읽어 변수에 저장 |
: SQL *Plus는 이중 앰퍼센트(&&) 치환변수에 대해 DEFINE명령을 실행한다.
: DEFINE명령을 사용할 때 1개의 공백이 필요하면 공백을 단일 따옴표로 묶어야 한다.
♣ ACCEPT 명령
◈Syntax
ACCEPT 변수 [datatype] [FORMAT] [PROMPT text] [HIDE]
① 변수 : 값을 저장하는 변수의 이름으로 존재하지 않으면 SQL *Plus가 생성한다.
② datetype : NUMBER, CHAR 또는 DATE이며, CHAR는 최대 240byte이다.
③ FORMAT : 예를들면, A10 또는 9,999같은 형식을 명시한다.
④ PROMPT : text는 사용자가 값을 입력하도록할 때 출력되는 문장이다.
⑤ HIDE : 예를들면, 암호처럼 사용자가 입력되는 내용을 보이지 않게 한다.
◈ACCEPT명령을 치환변수를 참조할 때 SQL *Plus치환변수에 &를 붙이지 마시오.
◈사용자로부터 입력값을 받아들일 때 원하는 프롬프트를 작성할 경우에 사용.
◈NUMBER 또는 DATE datatype의 변수를 명시적으로 정의할 때 사용.
◈보안상의 이유로 사용자 입력사항(변수명)을 숨기고자할 때 사용.
♠실습 : 입력받은 부서이름에 대한 지역번호와 지역명을 출력한다.
Prompt.sql이란 스크립트 파일을 만들고 원하는 메시지로 프롬프트를 주기 위해
ACCEPT명령을 사용하시오.
| SQL> ed prompt [Enter] SQL> start prompt Provide the department name: sales [Enter] |
♣ UNDFINE 명령
◈변수들은 아래 중 어느 하나가 발생할 때까지 정의된 채로 존재한다.
◈변수에 대해 UNDEFINE 명령을 실행
◈SQL *Plus를 빠져 나올 때까지.
◈DEFINE 명령을 써서 변수내용을 확인할 수 있다.
◈SQL *Plus를 빠져나올 때 해당 세션동안 정의된 변수는 상실 된다.
◈세션마다 특정변수를 정의하기 위해서는 변수가 시스템 시동시에 생성될 수 있게 login.sql 파일을
이용해야한다.
♠ 질의 예제 : 부서 위치를 갖는 변수를 만들고 변수의 값과 일치하는 모든 부서이름을 출력하라.
| SQL> DEFINE dname = sales SQL> SELECT name |
♣ 변수 값을 스크립트 파일에 전달
변수값을 스크립트 파일에 전달하는 스크립트 파일을 만들고 실행하기 위해서는 아래의 단계를 따른다.
① 스크립트 파일에 SELECT문을 적는다.
② SELECT 문장에 각 변수를 참조하는 곳마다 &number표기법을 사용한다.
③ 명령행에서 파일을 구동할 때 파일 이름 다음에 변수값을 명시한다.
각 매개변수의 위치는 중요하다.(변수값들을 구분하기 위해 공백을 사용한다.)
SQL> START 스크립트 파일 이름 변수1 변수2 …..
♠실습 : 직급별로 일련의 보고서를 만들기 위해 param.sql이라는 스크립트 파일을 작성하시오.
직급을 입력받는 프롬프트 대신 파일을 수행할 때 명령 행에 직급을 입력한다.
| SQL> ed param [Enter] SQL> @param |
