JNB
rss

skin by 이글루스

오라클

SQL*Plus 11.05.28 8:09

young@young-K52F:~$ sqlplus
 
 
SQL*Plus: Release 11.2.0.1.0 Production on Thu May 26 15:58:19 2011
 
Copyright (c) 1982, 2009, Oracle.  All rights reserved.
 
Enter user-name: sys as sysdba
Enter password: 
 
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
 --실습할 hr 계정이 잠겨있기때문에 풀어주어야 한다.--

SQL> alter user hr account unlock identified by pass;
 
User altered.
 
SQL> grant resource, connect to hr;
 SQL> conn hr
Enter password: 
Connected.
SQL> 
--hr 스키마에 존재하는 table들 보기
SQL> SELECT * FROM TAB;

TNAME                   TABTYPE    CLUSTERID
------------------------------ ------- ----------
COUNTRIES               TABLE
DEPARTMENTS               TABLE
EMPLOYEES               TABLE
EMP_DETAILS_VIEW           VIEW
JOBS                   TABLE
JOB_HISTORY               TABLE
LOCATIONS               TABLE
NULL_TEST               TABLE
REGIONS                TABLE
TEST                   TABLE

10 rows selected.


SQL> SELECT employee_id, first_name, last_name
  2   FROM employees
  3     WHERE customer_id = 100
  4  ;
   WHERE customer_id = 100
         *
ERROR at line 3:
ORA-00904: "CUSTOMER_ID": invalid identifier

--SQL 구문 수정하기
SQL> edit
Wrote file afiedt.buf

  1  SELECT employee_id, first_name, last_name
  2   FROM employees
  3*    WHERE employee_id = 100
  4  /

EMPLOYEE_ID FIRST_NAME         LAST_NAME
----------- -------------------- -------------------------
    100 Steven         King
--각 행마다 보기
SQL> 1
  1* SELECT employee_id, first_name, last_name
SQL> 2
  2*  FROM employees
SQL> 3
  3*    WHERE employee_id = 100
SQL> 4
SP2-0226: Invalid line number
SQL> 2
  2*  FROM employees
--테이블 구조 보기--
SQL> desc employees;
 Name                       Null?    Type
 ----------------------------------------- -------- ----------------------------
 EMPLOYEE_ID                   NOT NULL NUMBER(6)
 FIRST_NAME                        VARCHAR2(20)
 LAST_NAME                   NOT NULL VARCHAR2(25)
 EMAIL                       NOT NULL VARCHAR2(25)
 PHONE_NUMBER                        VARCHAR2(20)
 HIRE_DATE                   NOT NULL DATE
 JOB_ID                    NOT NULL VARCHAR2(10)
 SALARY                         NUMBER(8,2)
 COMMISSION_PCT                     NUMBER(2,2)
 MANAGER_ID                        NUMBER(6)
 DEPARTMENT_ID                        NUMBER(4)
--버퍼에 있는 SQL구문 보기--
SQL> L
  1  SELECT employee_id, first_name, last_name
  2   FROM employees
  3*    WHERE employee_id = 100
SQL> 1
  1* SELECT employee_id, first_name, last_name
--1행에 칼럼 email 추가.
SQL> APPEND, email
  1* SELECT employee_id, first_name, last_name, email
SQL> /

EMPLOYEE_ID FIRST_NAME         LAST_NAME
----------- -------------------- -------------------------
EMAIL
-------------------------
    100 Steven         King
SKING

--좀더 편하게 보기 위해서 linesize 지정--
SQL> set linesize 100
SQL> /

EMPLOYEE_ID FIRST_NAME         LAST_NAME           EMAIL
----------- -------------------- ------------------------- -------------------------
    100 Steven         King               SKING


SQL> LIST
  1  SELECT employee_id, first_name, last_name, email
  2   FROM employees
  3*    WHERE employee_id = 100
--employee_id를 101번으로 바꾸기---
– ‘=’앞뒤에는 항상 공백을 주어야 한다.--
SQL> CHANGE /employee_id = 100/employee_id = 101
  3*    WHERE employee_id = 101
SQL>
--RUN으로 구문 실행하기--
SQL> RUN
  1  SELECT employee_id, first_name, last_name, email
  2   FROM employees
  3*    WHERE employee_id = 101

EMPLOYEE_ID FIRST_NAME         LAST_NAME           EMAIL
----------- -------------------- ------------------------- -------------------------
    101 Neena         Kochhar           NKOCHHAR
--실행은 ‘/’로도 할수 있다. --
SQL> /

EMPLOYEE_ID FIRST_NAME         LAST_NAME           EMAIL
----------- -------------------- ------------------------- -------------------------
    101 Neena         Kochhar   

--SQL> LIST
  1  SELECT employee_id, first_name, last_name, email
  2   FROM employees
  3*    WHERE employee_id = 101
– SQL구문을 sql파일로 저장하기--
SQL> SAVE save_sql.sql
Created file save_sql.sql

SQL> GET save_sql.sql
  1  SELECT employee_id, first_name, last_name, email
  2   FROM employees
  3*    WHERE employee_id = 101
SQL> --실행하기--
SQL> START save_sql.sql

EMPLOYEE_ID FIRST_NAME         LAST_NAME           EMAIL
----------- -------------------- ------------------------- -------------------------
    101 Neena         Kochhar           NKOCHHAR

SQL> --화면출력 저장하기--
SQL> SPOOL save_results.txt
--실행--
SQL> /

EMPLOYEE_ID FIRST_NAME         LAST_NAME           EMAIL
----------- -------------------- ------------------------- -------------------------
    101 Neena         Kochhar           NKOCHHAR
--화면출력 저장 중지--
SQL> SPOOL OFF
--리눅스 시스템 명령어 ! Cat으로 파일에 저장된 내용보기
SQL> !cat save_results.txt
SQL> /

EMPLOYEE_ID FIRST_NAME           LAST_NAME                 EMAIL                                   
----------- -------------------- ------------------------- -------------------------               
        101 Neena                Kochhar                   NKOCHHAR                                

SQL> SPOOL OFF

--linesize 란 시스템 정의 변수는 한행에 올수 있는 최대 문자수를 의미한다.--
SQL> show linesize
linesize 80
SQL>
SQL> LIST
  1  SELECT employee_id, first_name, last_name, email
  2   FROM employees
  3*    WHERE employee_id = 101
SQL> /

EMPLOYEE_ID FIRST_NAME         LAST_NAME
----------- -------------------- -------------------------
EMAIL
-------------------------
    101 Neena         Kochhar
NKOCHHAR

--LINESIZE 를 좀더 크게 해서 더 편안하게 결과를 볼수 있다.--
SQL> SET LINESIZE 100
SQL> /

EMPLOYEE_ID FIRST_NAME         LAST_NAME           EMAIL
----------- -------------------- ------------------------- -------------------------
    101 Neena         Kochhar           NKOCHHAR
--set colsep 로 칼럼마다 구분도 할수 있다.--
SQL> set colsep "|"
SQL> /

EMPLOYEE_ID|FIRST_NAME        |LAST_NAME          |EMAIL
-----------|--------------------|-------------------------|-------------------------
    101|Neena        |Kochhar          |NKOCHHAR

--다른 예제--
SQL> LIST
  1  SELECT employee_id, first_name, last_name, email
  2   FROM employees
  3*    where employee_id  between 101 and 103
SQL> /

EMPLOYEE_ID|FIRST_NAME        |LAST_NAME          |EMAIL
-----------|--------------------|-------------------------|-------------------------
    101|Neena        |Kochhar          |NKOCHHAR
    102|Lex         |De Haan          |LDEHAAN
    103|Alexander        |Hunold           |AHUNOLD


SQL> --숫자 칼렴을 두자리로 변경하면 세자리 숫자를 볼수 없다.--
SQL> COLUMN employee_id FORMAT 99
SQL> /

EMPLOYEE_ID|FIRST_NAME        |LAST_NAME          |EMAIL
-----------|--------------------|-------------------------|-------------------------
    ###|Neena        |Kochhar          |NKOCHHAR
    ###|Lex         |De Haan          |LDEHAAN
    ###|Alexander        |Hunold           |AHUNOLD


SQL> COLUMN employee_id FORMAT 999
SQL> /

EMPLOYEE_ID|FIRST_NAME        |LAST_NAME          |EMAIL
-----------|--------------------|-------------------------|-------------------------
    101|Neena        |Kochhar          |NKOCHHAR
    102|Lex         |De Haan          |LDEHAAN
    103|Alexander        |Hunold           |AHUNOLD

--COLUMN CLEAR하기--
SQL> CLEAR COLUMN
columns cleared
SQL> /

EMPLOYEE_ID FIRST_NAME         LAST_NAME           EMAIL
----------- -------------------- ------------------------- -------------------------
    101 Neena         Kochhar           NKOCHHAR
    102 Lex          De Haan           LDEHAAN
    103 Alexander         Hunold            AHUNOLD

SQL> COLUMN FIRST_NAME FORMAT A10
SQL> COLUMN LAST_NAME A10
SP2-0158: unknown COLUMN option "A10"
SQL> COLUMN LAST_NAME FORMAT A10
SQL> COLUMN EMAIL FORMAT A20
--좀더 깔끔하게 결과를 볼수 있다. A다음의 숫자는 문자수를 의미한다.--
SQL> /
-




--컬럼 이름 바꾸기--
SQL> COLUMN EMAIL HEADING "이메일"






SQL> SHOW PAGESIZE
pagesize 14
--한페이지당 총 14행이 온다는 의미다.--
SQL> LIST
  1  SELECT employee_id, first_name, last_name, email
  2*  FROM employees
SQL> /

EMPLOYEE_ID FIRST_NAME LAST_NAME  이메일
----------- ---------- ---------- --------------------
    198 Donald     OConnell   DOCONNEL
    199 Douglas    Grant      DGRANT
    200 Jennifer   Whalen      JWHALEN
    201 Michael    Hartstein  MHARTSTE
    202 Pat        Fay      PFAY
    203 Susan      Mavris      SMAVRIS
    204 Hermann    Baer      HBAER
    205 Shelley    Higgins      SHIGGINS
    206 William    Gietz      WGIETZ
    100 Steven     King      SKING
    101 Neena      Kochhar      NKOCHHAR
--총 14개 행이다.--
EMPLOYEE_ID FIRST_NAME LAST_NAME  이메일
----------- ---------- ---------- --------------------
    102 Lex        De Haan      LDEHAAN
    103 Alexander  Hunold      AHUNOLD
    104 Bruce      Ernst      BERNST
    105 David      Austin      DAUSTIN
    106 Valli      Pataballa  VPATABAL
    107 Diana      Lorentz      DLORENTZ
    108 Nancy      Greenberg  NGREENBE
    109 Daniel     Faviet      DFAVIET
    110 John       Chen      JCHEN
    111 Ismael     Sciarra      ISCIARRA
    112 Jose Manue Urman      JMURMAN
................

SQL> SET PAGESIZE 5
SQL> /

EMPLOYEE_ID FIRST_NAME LAST_NAME  이메일
----------- ---------- ---------- --------------------
    198 Donald     OConnell   DOCONNEL
    199 Douglas    Grant      DGRANT

EMPLOYEE_ID FIRST_NAME LAST_NAME  이메일
----------- ---------- ---------- --------------------
    200 Jennifer   Whalen      JWHALEN
    201 Michael    Hartstein  MHARTSTE



SQL> --칼럼 세팅 없애기--
SQL> CLEAR columns
columns cleared
SQL>


        

    
Copyright 1999-2019 Zeroboard / skin by JY