(pl)sql*plus 명령어

프로그램/오라클

(pl)sql*plus 명령어

블루바다747 2008. 5. 8. 10:33

(펌)http://cafe121.daum.net/_c21_/bbs_search_read?grpid=b2P3&fldid=2INg&contentval=0002Wzzzzzzzzzzzzzzzzzzzzzzzzz&nenc=QO_DBdjaFmAc99xE2LnWQQ00&dataid=156&fenc=sn5aKM34sXc0&docid=b2P3|2INg|156|20080501095519&q=%BF%C0%B6%F3%C5%AC%20%B3%AF%C0%DA%C7%FC%BD%C4

오라클 : SQL*PLUS 명령어모음

명령어 버퍼(command buffer) : SQL*PLUS가 현재의 SQL문을 보관하는 기억공간

특징: 각각의 줄은 번호가 부여, 명령어 버퍼의 내용을 편집/호출/저장 가능

편집명령어

L(LIST) : 명령어 버퍼의 내용을 보기

/ 또는 R(RUN) : 버퍼에 저장된 쿼리를 실행 (두 개의 차이점은?)

ED(EDIT) : 버퍼에 저장된 명령어를 메모장에서 편집(주의 ;가 /로 대체) ~ afiedt.buf

HOST : 현 운영체제의 명령어 입력 프롬포트 실행

SAVE : 최근에 수행한 쿼리문 저장(확장자 생략시 sql이 자동 붙음)

지정한 파일이 이미 존재하면? 에러 -> REPLACE 옵션 사용
1. SQL> SAVE q1.sql REPLACE
파일로 저장된 쿼리문 실행? 파일명 앞 @ 붙임
1. SQL> @q1

GET : 저장된 쿼리문을 버퍼에 불러옴(/로 실행)

SPOOL : 화면을 갈무리

시작: SPOOL 파일명
끝: SPOOL OFF

N : N을 편집될 현재의 줄로 만듦(N은 숫자를 의미)

I(INSERT) : 현재의 줄 다음에 새로운 줄을 삽입. 새로운 줄이현재의 줄이 됨

DEL(DELETE) : 현재의 줄을 삭제

A(APPEND) text : 현재의 줄에 텍스트를 첨가 라인의 마지막에 text를 추가

주의 사항: ~~~E라는 줄에 ABC를 추가하여 "~~~E ABC"로 만드려면 공백' ' 을 2칸을 쓴다
1. 예:바뀌기 전 행 내용: select empno, ename
2. SQL> A, sal
3. 1* select empno, ename ,sal

C[HANGE] / old_text/new_text : old_text를 new_text로 변경

C[HANGE] /text/ : 현재 행으로부터 text를 삭제

CL(CLEAR) BUFF(BUFFER) : SQL버퍼로부터 모든 라인을 삭제

형식 지정
- COLUMN - 컬럼에 대한 설정된 값을 확인
  1. SQL> COLUMN empno
- COLUMN HEADING - 컬럼 제목을 설정
  1. SQL> COLUMN empno HEADING '사원번호'
- COLUMN 컬럼명 CLEAR - 컬럼에 설정된 갑을 해제
- COLUMN FORMAT - 컬럼 제목의 출력형식을 변경
  1. SQL> COLUMN dname FORMAT A20 // 컬럼의 너비를 20으로 지정
  2. SQL> COLUMN sal FORMAT 0,000,000 // 숫자 데이터를 총 7자리로 출력, 세자리마다 콤마(,)로 구분
  3. SQL> COLUMN sal FORMAT 999999 // 원상복귀
시스템 변수 설정
- 형식) SET 시스템변수명 값
- HE
- ADING : 컬럼 제목의 출력 여부를 결정(값: on/OFF)
- LINESIZE : 한 화면에 출력되는 라인의 수 결정(값: 숫자)
- PAGESIZE : 한 페이지에 출력되는 페이지 크기 결정(값: 숫자)

테이블 정의 조사하기
- DESC(RIBE) 이용
1. 예)
2. SQL> describe egw_emp
  Name           Type           Nullable Default Comments
  -------------- -------------- -------- ------- --------
  EMP_ID         NUMBER(15)
  LAST_NAME      VARCHAR2(512)
  FIRST_NAME     VARCHAR2(2048) Y
  SALARY         NUMBER(15)     Y
  TITLE          VARCHAR2(32)   Y
  COMMISSION_PCT NUMBER(15)     Y
  DEPT_ID        NUMBER(15)
  START_DATEDATE DATE           Y
- Name: 컬럼명, Type: 컬럼의 데이터형과 길이, Nullable: 널이 가능한 여부, Default: 기본값, Comments: 설명

데이터형
- 숫자(NUMBER) 데이터형 : 0/음수/양수, 고정 소수점/부동 소수점의 수를 저장
  - NUMBER (precision, scale)
  - precision: 38자리까지의 정밀도
  - scale : 양수(소수점 이하 자리 수), 음수(소수점 위의 자리를 0으로 처리하는 자리 수)

실제 데이터	정의	저장되는 데이터
123456.789	Number(8,2)	123456.79
123456.789	Number(8)	123456.7
123456.789	Number(8,-2)	123500
123456.789	Number	123456.789

날짜(DATE) 데이터형 : 날짜 및 시간 데이터를 저장
- 세기(Century), 년(Year), 월(Month), 일(Day), 시간(Hour), 분(Minute), 초(Second)에 대한 정보 포함
- 범위 : B.C. 4712년 1월 1일 ~ A.D. 9999년 12월 31일
- 예) OS의 날자 조회 하기
1. SQL> SELECT sysdate FROM dual;
  SYSDATE
  -----------
  2007-11-08
- DUAL 테이블 :
  - SYS사용자의 소유 테이블(SYS.DUAL), 모든 사용자가 질의 가능
  - DUMMY라는 단 하나의 컬럼에 X라는 단 하나의 로우만 가짐
  - 일시적인 산술연산, 가상 컬럼 등의 값을 보고 싶을 때 이용가능
    - 실습! dual 대신에 실제 존재하는 테이블의 이름을 넣으면?
- 한글 '월'~'일' 사용:
  1. SQL> ALTER SESSION SET NLS_LANGUAGE=Korean;
문자 데이터형(CHAR, VARCHAR2, LONG)
- CHAR 데이터형 : 고정 길이 문자열 저장: 1~255 바이트
  - 입력하고 남은 길이는 빈칸으로 채워짐(저장 공간 낭비 고려 필요)
- VARCHAR2 데이터형 : 2000 바이트까지 저장 (메모 등에 이용)
  - 가변적인 길이의 문자열 저장
- LONG 데이터형 : 2GB까지 길이가 허용
  - 제약조건: 테이블 중의 한 컬럼만이 정의가능, 인덱스로 정의 불가, 프로시저로 파라미터 못 넘김/리턴되는 함수를 이용 불가, WHERE/ORDER BY/GROUP BY/CONNECT BY절에 사용 불가
- CLOB : 4GB까지 문자 저장
이진(Binary) 데이터형
- RAW : 255바이트까지 수용
- LONG RAW: 각각의 로우에 대해 2GB까지 수용
  - 제약조건: 어떠한 내장 함수도 사용 불가
- BLOB : 4GB까지 저장
- BFILE : 외부파일에 4GB까지 저장
ROWID : 로우에 대한 논리적 주소(DB상 유일)
1. SQL> SELECT ROWID FROM DEMONAMO;
  ROWID
  ------------------
  AAGwp+AAqAAAEClAAA
- 문자구성: A~Z, a~z, 0~9, +, /
- 네 부분으로 구성: 6자리(객체번호), 3자리(파일번호), 6자리(블록번호), 3자리(슬롯 번호)
- 대용량 데이터베이스(Very Large Database:VLDB) 지원을 위한 파티셔닝과 관련
- WHERE절, SELECT절에서 사용 가능
- 익스포트/임포트를 통해서만 변경 가능(DML(I/U/D)을 통해서는 불가)

단일행 함수(Single-row function): 적용(단일행)-> 결과(하나)

중첩사용 가능: 가장 안의 함수부터 실행 예) Func1(Func2(Func3( , ) , ) .. ) => Func3() , Func2(), Func1()의 순
문자 처리 함수
- LOWER - 문자열을 모두 소문자로 변경 예) LOWER('SQL Plus') -> sql plus
  - 테스트는 더미테이블(DUAL)로 해볼 수 있다.
    1. SQL> select LOWER('SQL Plus') from dual;
      LOWER('SQLPLUS')
      ----------------
      sql plus
- UPPER - 문자열을 모두 대문자로 변경 예) LOWER('SQL Plus') -> SQL PLUS
- INITCAP - 문자열에 속한 각 단어의 첫 글자를 대문자, 나머지는 소문자로 변경 예) LOWER('SQL Plus') -> Sql Plus
- CONCAT - 두 문자열을 합쳐줌 예) CONCAT('SQL', 'Plus') ------> SQL Plus
  - 합성연산자('||')와 같은 역할을 하나 인수를 2개 밖에 가질 수 없다는 차이가 있음
- SUBSTR - 지정된 위치에서 길이만큼의 문자열을 추출 예) SUBSTR('SQL Plus', 2, 4) ----> QL P
  - 두 번째 인자 음수 가능 : 문자열의 �에서부터 추출
  - 세 번째 인자 생략 가능: 생략시 문자열의 끝가지 추출
- LENGTH - 문자열의 길이 반환 예) LENGTH('SQL Plus') ------> 8
- INSTR - 특정 문자의 위치 반환 예) INSTR('SQL Plus', 'Q') -------> 2
  - 문자가 여러곳에 위치할 시: 첫 번째 자릿수만 반환
  - 0 : 지정된 문자를 찾을 수 없는 경우의 반환값
- LPAD - 왼쪽에 지정된 문자를 지정된 길이만큼 채움 예) LPAD('SQL Plus',15, '#') -----> #######SQL Plus
- RPAD - 오른쪽에 지정된 문자를 지정된 길이만큼 채움 예) RPAD('SQL Plus',15, '#') ----> SQL Plus#######
숫자 처리 함수
- ROUND - 지정된 소수점 위치까지 값을 반올림 예) ROUND(123.45,1) ------> 123.5
- TRUNC - 지정된 소수점 위치까지 값을 절삭(잘라냄) 예) TRUNC(123.45,-1) ------> 120
- FLOOR - 뒤에 나오는 인수보다 같거나 작은 정수를 반환 예) FLOOR(123.45) ------>123
- CEIL - 뒤에 나오는 인수보다 가거나 큰 정수를 반환 예) CEIL(123.45) ------>124
- MOD- 첫 번째 인수를 두 번째 인수로 나누었을 때의 나머지 반환 예) MOD(5, 2) -----> 1 // 짝/홀수 판단에 응용...
날짜 처리 함수 : 내부적으로 숫자값과 같이 저장 -> 날짜 데이터끼리 산술연산 가능
범위 : B.C. 4712년 1월 1일 ~ A.D. 9999년 12월 31일
- SYSDATE - 현재의 날짜와 시간을 보여줌
  1. SQL> SELECT sysdate from dual;
    SYSDATE
    -----------
    2007-11-08
- ADD_MONTHS - 첫 번째 인수가 나타내는 날짜에 두 번째 인수가 나타내는 달수를 더한 날자를 반환
  1. SQL> select add_months(sysdate, -5) from dual;
    ADD_MONTHS(SYSDATE,-5)
    ----------------------
    2007-06-08 오전 11:38:
  - 5개월 전의 날짜
- MONTHS_BETWEEN - 첫 번재 날짜와 두 번째 날짜 사이의 달수를 계산(첫 번재 날짜 - 두 번째 날짜)
  1. SQL> select MONTHS_BETWEEN(SYSDATE,'1992-1-1') from dual;
    MONTHS_BETWEEN(SYSDATE,'1992-1
    ------------------------------
    190.241535991637
  - 1992년 1월 1일부터 현재 시스템 날짜 사이의 달수
- NEXT_DAY - 첫 번째 인수가 나타내는 날짜 다음으로 나타나는 두 번째 인수의 요일에 대한 날짜 반환
- LAST_DAY - 지정된 날짜를 포함하고 있는 달의 끝 날짜를 반환
  1. SQL> select NEXT_DAY(sysdate, 1), LAST_DAY(sysdate) from dual;
    NEXT_DAY(SYSDATE,1) LAST_DAY(SYSDATE)
    ------------------- -----------------
    2007-11-11 오전 11: 2007-11-30 오전 1
  - 참고: 1-> SUNDAY
- ROUND(date, [format]) - 반올림 , 달의 경우 16일이 반올림의 기준일(28,30,31일과 관계 없이)
- TRUNC(date, [format]) - 잘라내기
  1. SQL> select ROUND(sysdate, 'YEAR'), TRUNC(sysdate, 'YEAR') from dual;
    ROUND(SYSDATE,'YEAR') TRUNC(SYSDATE,'YEAR')
    --------------------- ---------------------
    2008-01-01 2007-01-01

데이터 변환 함수

TO_CHAR(date/number, format) - 숫자 데이터/날자 데이터 -> VARCHAR2 형태로 변환(지정된 포맷에 맞추어)

형식요소	설명	범위
SS	초	0~59
SSSSS	자정을 넘긴 초	0~86399
MI	분	0~59
HH / HH12	12시간제 시간	0~12
HH24	24시간제 시간	0~23
DD	월의 날짜	1~28,30,31(달의 마지막날에 따라 다름)
DAY	요일의 전체이름	SUNDAY ~ SATURDAY
DY	세 자로 축약된 요일 이름	SUN ~ SAT
D	주의 날짜	1 ~7
DDD	년도의 날짜	1 ~ 365, 166 (윤년에 따라 다름)
W	달의 몇 번째 주인가 표기	1 ~ 5
WW	년의 몇 번째 주인가 표기	1 ~ 53
MM	월	1 ~ 12
MON	세 자로 축약된 월 이름	JAN ~ DEC
MONTH	월의 전체 이름	JANUARY ~ DECEMBER
RM	월의 로마자 표기	예) 12월 => xii
YY	년도의 마지막 두 자리	예) 1997년 => 97
YYYY	년도의 전체 자리	예) 1997년 => 1997
YEAR	연도의 알파벳 표기	예) 1997년 => NINETEEN NINETY SEVEN
CC	세기	예) 1997년 => 19
Q	분기	1 ~ 4
J	로마 달력으로 BC 4713년 12월 31일 이후의 지금가지의 날 수를 반환환	예) 2454413

형식요소는 작은따옴표(')를 씌워서 사용한다.
1. SQL> SELECT TO_CHAR(SYSDATE, 'J') FROM DUAL;
  TO_CHAR(SYSDATE,'J')
  --------------------
  2454413
YY포맷과 RR 포맷
- RR 포맷 : 현재의 연도의 � 두 자리와 처리하려는 데이터의 연도 �두 자리의 범위가 '0~49'/'50~99'로 같을 경우 같은 세기를 반환
  현재의 연도('0~49')+처리 연도('50~99')-> 이전 세기 반환, 현재의 연도('50~99')+처리 연도('0~49')-> 이후 세기 반환
  - 예: 현재해(2007년) : 06년 -> 2006년, 16년 -> 1916년, 현재해(1982년) : 68년 -> 1968년 / 19년 -> 2019년

TO_NUMBER - 문자열을 숫자 데이터로 바꾸는 기능⇔ TO_CHAR()
1. SQL> select to_number('$145,000','fm$999,999') from dual;
  TO_NUMBER('$145,000','FM$999,9
  ------------------------------
  145000
- '$145,000' (문자형), 'fm$999,999'(포맷) :: fm - 공백을 없애줌
TO_DATE - 문자열을 현재 기본 포맷의 날짜 데이터로 바꾸어 주는 기능⇔ TO_CHAR()
1. SQL> SELECT SYSDATE - to_date('1982-01-06') "Past Days" from dual;
  Past Days
  ----------
  9437.57405
- SYSDATE가 날짜형이므로 '1982-01-06'이 문자형이므로 날짜형으로 변환후 산술연산 실시
CHR(number) - 해당 아스키 코드에 해당하는 문자를 반환
1. SQL> select chr(80)||chr(76)||chr(85)||chr(83) SQL from dual;
  SQL
  ----
  PLUS

일반 단일행 함수
- DECODE : IF-THEN=ELSE 문장과 유사
  - IF column_name or ｅxpression! = condition1 THEN value1
    IF column_name or ｅxpression! = condition2 THEN value2
    ...
    ELSE default value
  - DECODE (column_name or ｅxpression!, condition1, value1, condition2, value2, ... , default value)
    1. SQL> SELECT ename, deptno,
                     DECODE(deptno, 10, 'ACCOUNTING',
                                    20, 'RESEARCH',
                                    30, 'SALES',
                                    40, 'OPERATIONS') dname
               FROM emp;
- CASE
  - DECODE와 비슷(조건부: 다양한 비교 연산자를 이용가능)
    1. SELECT job, sal,
         CASE WHEN job='CLERK' THEN sal*1.20
               WHEN job='ANALYST' THEN sal*1.15
               WHEN job='MANAGER' THEN sal*1.10
               ELSE sal
         END AS salary
      FROM emp;
- NVL (column_name or ｅxpression!, value) - 널(NULL) 값을 특정 값으로 변경
  - column_name or ｅxpression! : NULL값을 포함할 수 있는 값
  - value)
  - 문자/숫자/날짜 데이터에 모두 사용 가능
  - 주의: 첫 번째 인수와 두번째 인수가 반드시 같은 데이터형(항상 나오는 커미션 예제)
  1. SQL> SELECT id, last_name, nvl(to_char(commission_pct), 'No Commission') commission from s_emp;
  - 의미: commission_pct가 있을 경우는 출력하고 없을 경우는 'No Commission'라는 메시지를 출력

복수행 함수(Multiple-row function): 적용(여러 로우)-> 결과(하나)
- 내장 그룹 함수(COMPUTE 명령에 사용가능) : NULL값을 제외하고 결과를 산출(주의!)
  - COMP[UTE] 함수(아래) [LAB[EL] 텍스트] ...
    - OF 계산을 수행할 대상 컬럼명
    - ON 구분이 되는 (BREAK에 사용되는) 컬럼명, 식, 별칭, REPORT
  - COU[NT](컬럼명) : 조건을 만족하는 로우(행)의 개수를 반환
    1. SQL> SELECT COUNT(job) FROM emp;
    2. SQL> SELECT COUNT(DISTINCT job) FROM emp;
    3. SQL> SELECT COUNT(*) FROM emp; // 널 값이 있어도 카운팅 함(그룹함수의 예외)
  - AVG : 평균값
  - SUM : 값의 합계
  - MAX[IMUM] : 최대값
  - MIN[IMUM] : 최소값
  - STD[DEV] : 표준편차
  - VAR[IANCE] : 분산값
- 로우를 그룹으로 만듬
  - DISTINCT
    - 중복행을 제거, 아래 예에서 DISTINCT를 넣고/빼어 실행해 보면 차이를 알 수 있다.
    1. 예) SELECT dep DISTINCT FROM egw_emp;
    2. 의미) 사원테이블(egw_emp)에서 부서를 가져오데 중복은 한번만 표현한다.
  - GROUP BY - 어떤 컬럼을 기준으로 그 컬럼의 값별로 보고자 할 때
    - 예) 소속 부서별 평균 급여? (출력: 부서명 평균급여)
      1. SQL> SELECT deptno, AVG(sal) FROM emp GROUP BY deptno;SQL>
    - SELECT deptno, sal FROM emp GROUP BY deptno;
      SELECT deptno, sal FROM emp; 와 비교를 해보기.
    - 주의: 컬럼의 별칭 사용불가(반드시 컬럼명을 기술)
  - HAVING - GROUP BY 절에 의해 생성된 결과 값 중 원하는 조건에 부합하는 자료만 볼 때
    비교) SELECT ~ WHERE관계와 GROUP BY ~ HAVING 의 관계
    - 부서별 평균 급여? -> 급여 평균이 2000 이상인 부서만?
- 조인(Join)
  - Equi-join- 조인 대상이 되는 두 테이블에서 공통적으로 존재하는 컬럼의 값이 일치되는 행을 연결
    1. SQL> SELECT * FROM emp, dept WHERE emp.deptno = dept.deptno;
    - 컬럼명의 모호성 해결책 : 테이블명.컬럼명
    - 테이블명.컬럼명이 길어짐 대책 : 별칭(alias) 지정
      1. SQL> SELECT * FROM emp e, dept d WHERE e.deptno = d.deptno;
    - WHERE절을 없애고 비교해본다 : 곱집합(Cartesian Product)생성
  - Non-equijoin - 등호(=) 연산자 대신 비교 연산자 사용하는 조인
    - 비교연산자: <, >, <=, >=, BETWEEN ~ AND ~
  - Self-join - 자신의 테이블을 두개 인 것처럼 만들어 자기 자신 테이블과 Equi-join하는 것
    - 별칭을 이용
    1. SQL> SELECT name, manager FROM emp e, emp m WHERE e.mgr = m.empno;
  - Outer join
    - (+) 사용 : 배제된 행을 결과에 포함
    - 예) 위의 Self-join의 경우 사장의 경우 자신의 매니저가 없게 되므로 출력 안됨
      아래와 같이 하면 NULL인 로우도 출력
    1. SQL> SELECT name, manager FROM emp e, emp m WHERE e.mgr = m.empno(+);
- Set 연산자
  - UNION(UNION ALL)
  - INTERSECT
  - MINUS

'프로그램 > 오라클' 카테고리의 다른 글

(sql)랭킹별 상위 5개 자르기 (0)	2008.07.28
(pl)error관련 함수 (0)	2008.07.25
(sql)자동 순서 메기기(시퀀스) (0)	2007.10.29
(sql)case문 사용법 (0)	2007.10.23
(sql)공백사이의 문자 가져오기 (0)	2007.10.23

현재글(pl)sql*plus 명령어

바다로