DB 문서들
DSN 갤러리
Oracle Tutorials 9237 게시물 읽기
 News | Q&A | Columns | Tutorials | Devel | Files | Links
No. 9237
SQL* SQL*Plus (I)
2002-01-05 08:54


원본 출처 :

>>Access data with sql and sql*plus
 1.sql command 는 sql buffer(sql*plus가 관리하는 memory, not oracle memory)
   에 저장되는데 한번에 한명령만 저장된다.
   (새로운 sql command가 들어올 때까지 남아있다)
   (sql 대신 pl/sql로 oracle에 접근될 수 있다)
   sql*plus command는 sql command와는 다르고 따라서 sql buffer에 저장되지도 않는다.
 2.sql is database language (connected from any tool or application) that consists of staements to :
       - query data from the database
       - add, modify and remove data
       - create, modify and remove data structures
       - regulate data access
 3.sql*plus is an application development tool
       - execute sql statement
       - edit sql command with a line editor
       - control environment settings
       - format query results into basic reports
       - create files for manipulating a number of sql statements and sql*plus commands at a time
       - interact with end user
       - access remote databases
       - perform a variety of other general functions
 4.sql*plus command
    - execution : / (without displaying command), run (display command), exec(ute)
    - edit : l(ist), l n, l n m, a(ppend), c(hange), del, i(nput), i n, n text,
             0 text(첫 line에 삽입),e(dit),r(un)
    - environment : set, show, pause
    - report format : column, clear, break, compute, ttitle, btitle
    - file manipulation : save filename(.sql) (replace), get filename(.sql), start filename(.sql), @, @@, spool
    - interactive : define, undefine, prompt, accept, varable, print
    - database access : connect, copy, disconnect
    - miscellaneous : sqlplus, exit, help, desc(ribe), host, remark, runform, timing, time, whenever

   => 예상문제 : sql*plus상에서 문장을 다음 line으로 continue시키기 위해서 어떻게?
                 하이픈 즉, '-'를 사용해서 enter후 계속 기술한다.

>>Retrieve data
 * The CASE Development Life Cycle
   Strategy : begin to define and model the business information
   Analysis : complete the data model
   Design : Trnslate the data model into table instance charts
   Build (,user document) : 
            create database tables and other data structures to implement the database design
            create application, manipulate data within the tables
            retrieve and display data

  1.display data (sql*plus commands are not case sensitive.
   Function or others : distinct, alias ( (name) " User Defined Alias" ), 
   Data Manipulate Language :
     select distinct (name || '-' || last_name) 'alias' from table 
     where condition =, <>, >, >=, <, <=, (not) between ..and.. ,
                     (not) in (..),
                     is null, is not null,
                     like '_string%',
     (string 비교시 single quotation만 사용 : '%'는 모든 문자 대체, '_'는 한문자 대체)
           and, or 
     group by column
     having sum(column) > 5000
     order by column (asc, desc) ;
     (sql command는 ;로 끝내지만 sql*plus command는 ;없이 return 한다)
   disply data about oracle database : (using data dictionary : user_objects)

   => 예상문제 : where condition에서 escape문자(\) 의 역할은 :
                 where col1 like '%X\_Y%' 인경우 '\'가 특수문자를 인식하기 위한 것이므로
                 '_'는 한 문자를 대체하지 않고 실제로 string '_'를 찾는다.
                 즉, where condition은 'X_Y'의 값을 가진 앞, 뒤로 어떤 값이 있던지 상관없이
                 (any value)X_Y(any value)로 된 모든 문자를 찾는다.
   => 예상문제 : [sub query guide line]
                 enclosed in parentheses
                 on the right side of the operator
                 cannot contain an order by clause
   => 예상문제 : SQL 문에서 sub query를 사용하는 clause
      - having, where
      - from ( of select or delete)
      - update, into (of insert), set (of update)
   => 예상문제 : distinct의 사용은 - eliminate duplicate rows in the result set
   => 예상문제 : select statement의 문장 사용순서
        SELECT    title, SUM(salary) PAYROLL
        FROM      s_emp
        WHERE     title NOT LIKE 'VP%'
        GROUP BY  title
        HAVING    SUM(salary) > 5000
        ORDER BY  SUM(salary) ;
   => 예상문제 : column label and column aliases
        label default : date, character data는 left | numeric data는 right justification
                        display is uppercase
        alias : rename a column heading, useful with calculation, 
                immediately follows column name(column_name AS alias)
                double quotation mark로 alias 줄 경우 -> contain spaces, special characters, case sensitive
   => 예상문제 : order by 성격
                select시 without order by인 경우 똑같은 SQL statement를 수행할 지라도 그 순서는
                매번 다를수 있다.
                NULL values는 asc인 경우 displayed last
                column alias or position으로 order by를 줄 수 있다.
                (order by col1, col2 : order by 1, 2 : order by alias1, alias2)
                sort by a column that is not in the select list
                (select col1, col2 from table_A where (condition) order by col3)
                (즉, sort는 select lists에 없는 column으로도 가능하다)

>>Build reports with sql*plus
  * sql*plus commands about report
    environment commands => format commands => file commands => report file creation
  1.control the session environment
    set  feedback  n (display n records returned by query : default 6)
                      ex) 6 rows selected. ( => 6개이하의 row이면 이 값이 보이지 않는다)
         feedback on/off
         pause message (display message after scrolling through one screen full of output : no default value)
         pause on/off
         heading on/off (determine whether column headings are displayed)
         pagesize n (set the number of lines per page to n : default 14)
                    (pause value가 off이면 화면상에서는 별 의미가 없다)
         linesize n (set the number of characters per line to n : default 80)
         space n (set the number of spaces between columns in output : default 1, maximum 10)
                 ( => column 과 column 사이의 표시를 위한 빈공간이 n space만큼 벌어진다)
         echo on/off (determine whether each command in a text file is listed as it is executed : default off)
                 ( => file로 sql을 실행시켰을때 실행된 sql문장및 command를 display할 것인가 아닌가를 결정)
         verify on/off (determine whether the text of sql statements is displayed before and after sql*plus
                        substitution parameters are replaced by actual values : default on)
                 ( => accept command로 vaule를 받아들이는 경우
                      실행전 문장과 대체된 value를 가진 실행할 문장을 보여줄 것인가 말것인 표시)
  2.format command
    ttitle (on/off) 'message|new line' : place a header at the top of every page => Date  Ttitle  Page?
    btitle (on/off) 'message' : place a footer at the bottom of every page       =>       Btitle
    column : control the display attributes for columns
    break : group subsets of data together
    compute : produce summary calcurations
    clear : clear system variable (column column_name clear, clear break)
   => column column_name heading 'COLUMN|NAME' format (An) ($9,.)
   => break on column1 column2 skip ? page
      (column1, column2의 순서대로 display를 suppress하고 supress당 간격을 ?line만큼 띄우고
       set command의 pagesize에서 정한 1page길이만큼 page를 만든다.
       즉, page option은 출력될 page의 크기를 suppress당 1page로 하고 남는길이만큼 pagesize의
       길이에 맞추어 공백을 출력한다)

  3.compute function and file management
    count(count of not null value), num(count of rows), max, min, sum, avg, std, var
   => compute sum of coumpute_column on break_column
   => clear compute

   => 예상문제 : 일반적으로 모든 datatype에 사용할 수 있는 group function은 max, min 이다.
   => 예상문제 : 일반적으로 Numeric datatype에 사용할 수 있는 group function은 avg, sum 이다.
   => 예상문제 : group function의 성격
                 operate on sets of rows to give one result per group
                 appear in both SELECT lists and HAVING clauses
                 by default, all the rows in a table are treated as one group
                 all group function except COUNT(*) ignore null values

  < coumpte sum and grand total print >
    SQL> break on break_column on REPORT
    SQL> compute sum of compute_column on break_column REPORT
    SQL> select ....  order by break_column

  4.file management
    save, get, start, edit
    spool filename(.lst)
    spool off

  5.define commnd on SQL*PLUS (sql*plus 상에서 define으로 정할 수 있는 variable의 datatype은 CHAR 뿐)
    DEF[INE] [variable]|[variable = text]
    DEFINE specifies a user variable and assigns it a CHAR value, or lists
    the value and variable type of a single variable or all variables.
    DEFINE으로 정의하는 variable datatype : char, number, date
    start command로 file을 실행할 시 변수를 parameter로 넘겨줄 수 있다.
    즉, SQL> start batch.sql value1, value2...
    위 처럼 사용이 가능한데 maximum of 9 parameters (name : &1, &2....&9)
   => (1) DEFINE DEPTNO = 20
      (2) DEFINE DEPTNO , DEFINE DEPTNO = "20" (CHAR)    --> (1, 2)는 동일한 값을 갖는다

   => 예상문제 : Which datatype is created with the DEFINE command ?
        CHAR datatype only

  < sqlplus 시작시 기본환경설정 /user/login.sql
    모든 sqlplus 시작시 default 설정 (login.sql을 사용안할때) 
    기본 eidtor 설정 define _editor 'editor name'
    setting된 editor 보기 define _editor => DEFINE _EDITOR         = "vi" (CHAR)  >

>>Manipulate Data
  1.Insert : add a new row
    Insert into emp ( id, last_name, first_name ) values ( 33, '홍', '길동' ) ;
       => 지정된 column list와 value list가 match
    Insert into emp values ( auto_id, '홍', '길동', NULL, sysdate, '', NULL ) ;
       => emp table에 모든 column에 대하여 순서대로 value match (NULL과 empty string은 동일)
          auto_id, sysdate는 function
    Table의 not null column인 경우 insert시 반드시 포함
  2.Sequence : automatically generate a new value for a primary key column
    create sequence id_seq increment by 1 start with 1 minvalue 1 maxvalue 999999
                           cycle nocache order ;
    Insert into emp ( id, last_name, first_name ) values ( id_seq.NextVal, '홍', '길동' ) ;
       => seq_name.NextVal (+?)
          seq_name.CurrVal(current value after NextVal at least ones in the current session)

    increment by, start width는 생략시 default 1
    cache인 경우 갯수를 지정하지 않으면 default 20개를 memory allocate

   => 예상문제 : sequence의 nextval, currval을 사용할 수 없는 경우
      - select list of view
      - select with DISTINCT
      - select with GROUP BY, HAVING, ORDER BY
      - subquery in SELECT, DELETE, UPDATE
   => 예상문제 : sequence의 nextval, currval을 사용하는경우
      - select list
      - insert into value절 (또는 insert into절의 subquery)
      - update set절

  3.Update : modify an existing row(s)
    update emp set last_name = '김' ( where id = 33 ) ;
    update emp set ( last_name, first_name ) = ( '이', '주일' ) where id = 33 ;
  4.Delete : remove an existing row(s)
    delete from emp where id = 33 ; (id가 33인 data들 삭제)
    delete from emp ; (emp table의 모든 data 삭제)

   => 예상문제 : 한 Table에서 중복된 Row를 Delete하기
        DELETE FROM emp A WHERE rowid > 
        (SELECT min(rowid) FROM emp B WHERE B.empno = A.empno);

  5.Commit : make all pending changes permanent
    - Data changes have been written to the database files. 
      The previous state of the data is permanently lost.
    - All users can view the results of the transaction.
    - The locks on the affected rows are released.
      The rows now available for other users to perform new data changes.
    => use commit command after update or delete or insert
  6.Rollback : discard all pending changes
    - Data changes have been undone. The previous state of the data is restored.
    - The locks on the affected rows are released.
      The rows are now available for other users to preform new data changes.
  7.Savepoint : alter the logic of transactions with savepoints
    - A savepoint marks an intermediate point in the processing of a transaction.
    > insert 1
    > savepoint a
    > delete 1
    > insert 2
    > savepoint b
    > update 1
    > rollback to savepoint a ;
    > commit ;
    => savepoint a 이후 발생한 delete 1, insert 2, update 1은 모두 rollback되고 단지,
       insert 1만 commit되어 Database에 반영된다

   => 예상문제 : savepoint a 이후 savepoint a를 지정하면 첫번째 savepoint a는 삭제된다.
                - if you create a savepoint with same name
                - , the earlier savepoint is deleted

 * DDL Command를 실행시키면, 그전에 server는 implicit savepoint를 만들고 commit을 시도한다.
 * DDL Command 즉, create table 이나 create sequence같은 Data Definition Language는
   자동으로 commit command를 수행한다. (DCL:grant,revoke같은 Data Control Language도 마찬가지)
   transaction 중, commit or rollback 없이 정상적으로 (ex> exit ) sqlplus를 종료하면
   자동으로 commit command를 수행한다.
   sqlplus가 비정상적으로 종료되거나 system failures등이 발생한 경우에는
   자동으로 rollbacm command를 수행한다.
 * scripts to manipulate data (VALUE를 입력받아 처리하는 자동처리 FILE 예)
   EX) set echo off
       set feedback off
       accept input_id prompt 'Enter the Employee ID : '       
       accept input_deptid prompt  'Enter the Department ID : '       
       select id, dept_id, last_name, first_name from emp
       where id = &input_id and dept_id = &input_deptid ;

      => 예상문제 : commit; command없이 commit이 되는경우
                    DDL command를 수행하는 경우
                    DCL command를 수행하는 경우
                    sql*plus를 exit하는 경우
                    (정상적으로 session이 종료될 때 : sql*plus상에서 다른 DB로 connect한 후
                     작업을 하고 exit로 원래의 sql*plus상태로 돌아오는 경우도 포함)
      => 예상문제 : rollback; command없이 rollback이 되는경우
                    system failures시
                    비정상적으로 sql*plus가 종료되는 경우
      => 예상문제 : &variable, &&variable
                    &variable - variable in a SQL stmt
                        if the variable does not exist, SQL*PLUS prompt the user for a vlaue 
                        SQL*PLUS discards a new variable once it used
                        실행시 마다 변수값을 받는다.
                    &&variable - retains the variable 

       value for the session or until the variable is reset or deleted
                        reuse the variable value without prompting the user each time
                        최초 한번만 prompt로 value substitution

>>Create tables and data structures
  1.Table Naming Rule and table control
    - table names and column names은 30자 이내의 문자로 첫번째 문자는 반드시 alphabetic으로
      하고 그뒤엔 숫자, _, $, #등이 허용된다

      => 예상문제 : column name '300_id'로 한다면 column name의 시작이 숫자이므로 error 발생

    - oracle reserved words는 사용이 금지되고 한 user에서 동일한 이름으로 2개이상의 object를
      만들 수 없다.
      (sequence라는 이름으로 table을 만들 수 없고, Index_tab이라는 이름의 Index를 만든후
       같은 이름의 Index_tab이라는 Table을 만들 수 없다)
    - datatype : char(fixed length character), varchar2 (variable length character)
                 varchar(same as the varchar2 datatype), date (date and time)
                 long (maximum 2GB까지의 varchar2, 한 Table당 only one column is allowed), number(p, s)
                 raw, long raw (byte-oriented or binary data, not interprted by oracle server)

      => 예상문제 : datatype중 number의 default precision은 ?
      => 예상문제 : datatype중 number(5)에 56480.456, 56480.512를 입력하면 ?
                    56480, 56481
                    -- 위 datatype은 소숫점을 지정하지 않았으므로 소수점 첫자리에서 반올림 표시
      => 예상문제 : datatype중 56480.456, 580.1512등 number type의 아무형태나 받을수 있으려면 ?
                    datatype을 number 로 선언한다.

    - table creation)
        create table table_A 
        ( id     number(6) constraint id_nn Not Null,  <= not null 을 사용자의 constraint 표시
          user_id varchar2(6) ,                        <= null 허용
          last_name varchar2(10) Not Null,             <= not null 표시 (system에서 자동으로 not null constraint 생성)
          first_name varchar2(10) default('X'),        <= default value로 'X'를 생성
          constraint emp_id_pk primary key (id),       <= primary key 지정 (column name 포함)
          constraint emp_userid_unq unique (user_id),  <= unique key 지정 (null 허용) (column이 not null지정되면 not null unique)
          constraint emp_id_check check                <= check constraint 지정 (id 값을 100000과 900000사이로 제한)
                     ( id between 100000 and 900000 ) ) ; 
          --> not null constraint를 직접 생성하려면 not null column 바로 밑에서 해야한다.

      => 예상문제 : 만일 Create table table_name as select ... 문으로 table 생성시 만들어진 Table에는 NOT NULL constraint
           만 존재한다. 즉, column의 not null, null 정의만 따라오고 나머지 constraint는 무시된다.

    - table creation using sub query)
        create table table_A as select * from table_name ;
        match number of columns,
        only the NOT NULL constraint is inherited from the subquery table,
        define columns with column names, default values, integrity constraint only
        (datatype을 바꾸거나 size조정등은 할 수 없다)
    - table modify) 
        alter table table_A add ( dept_id varchar2(20) ) ;    <= column 추가
        alter table table_A modify ( user_id varchar2(8) not null) ;  <= column length 및 not null 추가

   => 예상문제 : Table Modify시 가능한 경우
      - increase the width or precision of a numeric column
      - decrease the width of a column if contains only null value or has no rows
      - change datatype if contains null values or has no rows
      - convert the datatype CHAR to/from VARCHAR2 : contains null values or do not change the size
      - a change to the default value only affects subsequent insertions
        (기존의 default value는 나두고 나중에 생성되는 default value만 영향을 준다면 가능)
        (not null column을 null column으로 바꿀수는 없다)

    - constraint add and drop)
        alter table emp_dept add constraint emp_id_fk  <= table_A의 id와 emp_dept 의 dept_user_id사이에 foreign key 관계설정
              foreign key ( dept_user_id ) references table_A (id) ;
        alter table emp_dept drop constraint emp_id_fk ; <= emp_dept의 foreign key drop

   => 예상문제 : Table에서 Delete시 Foreign Key 로 인해 Delete 안되는 경우 해결책은?
      - Child Table의 동일 row delete 후 작업
      - Foreign key 생성시 ON DELETE CASCADE option 을 사용하면 가능하나 이경우 Parent Table과
        Child Table의 해당 row가 동시에 삭제된다.
        즉,  alter table emp_dept add constraint emp_id_fk
             foreign key ( dept_user_id ) references table_A (id) on delete cascade ;
        로 생성되는 경우 parent인 table_A에서 어떤 row가 삭제되면 연결되어 있는 child인
        emp_dept에서도 동일 row가 자동으로 삭제된다.
   => 예상문제 : Table의 not null column에 foreign key가 잡혀있는 경우 insert 작업시 그
                 column에 NULL을 입력하면 어떤 ERROR가 발생하는가 ?
                 -- mandatory (NOT NULL) column is missing or NULL during insert
                 즉, not null error가 발생하지 FK error는 발생하지 않는다. 사실, not null
                 column이 아니라면 NULL을 입력해도 FK error는 발생하지 않는다.

    - table drop)
        drop table table_A ;
    - table or column에 comment 주기
        comment on table table_name is 'description';
        comment on column table_name.column_name is 'description';
        comment of up to 2000 bytes about a column, table, view, snapshot
        관련view : all(user)_col_comments, all(user)_tab_comments
        (column comments -> _col_ , table comments -> _tab_ )
    - object 이름 바꾸기 : rename old_name to new_name ;

   => 예상문제 : rename을 사용할 수 있는 예
      - table, view, sequence, synonym

   2.Constraint 확인
    Constraint view : user_constraints  <= user defined constraints
                      user_cons_columns <= user defined constraints column lists
    View view : user_views  <= user defined views
    - view creation)
        create(or replace) [force|noforce] view emp_view (v_id, v_user, v_lastname) 
              <= 3개 column에 대하여 다른 이름으로 view 생성
        as select id, user_id, last_name from table_A  <= table_A에서 3개 column 추출
           where last_name = '홍'     <= last_name이 홍인 data만 view에 연결
        with check option ;           <= where condition이외의 data로 view(emp_view)에 변경을 줄 수 없도록 제한
       (즉, update command로 emp_view의 data를 last_name이 '홍'이 아닌 값으로 수정할 수 없다)
        [with read only]

      => 예상문제 : 생성된 view에서 row를 삭제할 수 없는 경우는 create view시
          join condition, Group function, GROUP BY clause, DISTINCT command를 사용한 경우
      => 예상문제 : force option 사용시
          regardless of whether the base table exist
      => 예상문제 : 아래 문장중 틀린곳은 ?
          create view test_vw as select * from emp where emp_name like 'J%' order by emp_name ;
          -- subquery내에서 order by절은 사용할 수 없다.
          - view drop)        drop view emp_view ;

  3.Sequence ( >> Manipulate Data 의 2.sequence 참조 )
    - sequence creation
      create sequence id_seq       <= id_seq라는 이름으로 sequence 생성
      increment by 1               <= 1씩 증가
      start with 1                 <= 1부터 시작
      minvalue 1                   <= 최소값은 1
      maxvalue 999999 (nomaxvalue) <= 최대값은 999999 이거나 규정하지 않음
      (no)cycle                    <= 최대값이 되면 처음부터 시작할 것인가 아닌가
      (no)cache (20)               <= Memory에 지정한 갯수만큼 sequnce값을 가지고 있을것인가 아닌가
      (no)order ;                  <= 순서대로 1씩 증가한 값을 가져올 것인가 아니면 무작위로 가져올 것인가
  4.Improve Query
   - By rowid :
       select last_name from emp wghere rowid = '0000100A.0010.0003' ;
   - Full Table Scan :
       select * from emp ;
   - By index : (where condition에 비교되는 column에 index가 생성되어 있는 경우, 아니면 full table scan)
       select * from emp where last_name = '홍' ;
  5. (1) Index creation하는 경우
   - the column is used frequently in WHERE caluses or in a join condition
   - every value within the column is unique
   - the column contains a wide range of values (선택성이 좋은 경우 즉, unique에 가까울수록 좋다)
   - the column is sparsely populated, that is, is has a large number of NULL values
   - the table large (at lease several hundred to a thousand rows) and most queries are expected
     to retrieve fewer than 10-15% of the rows
     (때때로 more indexes does not always speed up queries)
     (2) Index creation안하는 경우
   - the table is small
   - the columns are not often used as a condition in the query
   - most queries are expected to retrieve more than 10-15% of the rows
   - the table is updated frequently
   ==> index에 따른 영향
       - The oracle server automatically updates indexes
       - There is no impact on SQL Syntax
       - There may be an impact on performance if data is frequently updated
     (3) Index creation
        create index emp_lastname_ind on emp ( last_name ) ;       <= last_name에 중복값 허용
        create unique index emp_lastname_uk on emp ( last_name ) ; <= last_name값이 중복되지 않는경우
        create index  index_name on table_name (col1, col2);       <= 복합 index(Composite index creation)
        drop index emp_lastname_uk ; <= index drop
       - primary key constraint나 unique constraint를 생성하면 explicitly하게 index를 생성하지
         않아도 system이 자동으로 index를 생성한다.
         (즉, foreign key column 나 non unique-index는 manually 생성한다)
    Index View : user_indexes     <= user defined indexes  
                        user_ind_columns <= user defined indexes column lists
   => 예상문제 : 일반적으로 Index를 생성하는 경우
      - column is used frequently in the "WHERE" clause or in a "join" condition
      - column contains a wide range of values
      - column contains a large number of null values
      - table is large and most queries are expected to retrieve less than 10-15% of the rows
        (결합 index가 필요한 경우 : Two or more columns are frequently used together in a WHERE clause or join condition)
   => 예상문제 : 일반적으로 Index를 생성하지 않는 경우
      - table is small (table이 작으면 full table scan이 더 빠르다)
      - columns are not often used as a condition in the query
      - Most queries ard expected to retrieve more than 10-15% of the rows
      - The table is updated frequently
        (index는 column value가 변경되면 index도 변경되므로 peformance에 영향을 줄 수 있다)

  1. DML (Data Manipulate Language)
  2. DDL (Data Definition Language)
  3. DCL (Data Control Language)
9243오라클 8i 데이터베이스 생성 방법
9242오라클 8i 인스톨
9238SQL, SQL*Plus (II)
9237SQL* SQL*Plus (I)
9236PL/SQL 요약
9199Oracle 에러별 원인 및 조치사항 몇가지
Valid XHTML 1.0!
All about the DATABASE... Copyleft 1999-2022 DSN, All rights reserved.
작업시간: 0.040초, 이곳 서비스는
	PostgreSQL v13.3으로 자료를 관리합니다