Bohemian life

# 3-11: SUBSTR --> 3번째 인자가 음수일 경우 null 값이 반환된다.

SELECT SUBSTR('ABCDEFG',3,4) "Substring"
     FROM DUAL;
 
Substring
---------
CDEF

뒤에서 5번째 위치에서 4자리
SELECT SUBSTR('ABCDEFG',-5,4) "Substring"
     FROM DUAL;

Substring
---------
CDEF

SELECT SUBSTRB('ABCDEFG',5,4.2) "Substring with bytes"
     FROM DUAL;

Substring with bytes
--------------------
CD

# 3-7: Character Functions

  - Case-Manipulations

  - Character-Manipulations

# 3-9: Case-Manipulation Functions

  - LOWER, UPPER, INITCAP

  - 문자열 비교시 유용 <= 문자열은 대소문자 구별하기 때문

==> 문제점: 컬럼을 가공하면 그 컬럼의 인덱스를 사용할 수 없습니다.

==> 해결방법: FBI (Function-Based Index)

* Functions - Built-in Functions     - Single-row Functions  : return one result per row

                                     - Multiple-row Functions: return one result per SET of rows

(=group function)

            - User-defined Functions - PL/SQL로 작성

* Arguments : input values of functions

* Single-row Functions - Character - Case-manipulation

                                   - Character-manipulation

                       - Number

                       - Date

                       - Conversion: Implicit/Explicit data type conversion

                       - General

# 3-5: Single-Row Functions

  1) 데이터 조작

  2) 인자를 받아서 결과값을 반환

  3)

  4) 

  ...

  - Possible Arguements

    1) User-supplied constant

    2) Variable

    3) Column name

    4) Expression (함수가 올 수도 있는데 이 경우를 nested function이라고 합니다)

* &

* &&

SQL> l

SQL> 1

SQL> c/&/&&

SQL> r

col의 값을 입력하십시오: ename

* define/undefine ---->SQL*Plus에서 사용할 변수를 선언하거나, 이미 선언된 변수와 그 변수값을 나열한다.

SQL> define col

DEFINE COL             = "ename" (CHAR)

SQL> /

SQL> undefine col

SQL> /

col의 값을 입력하십시오: sal

SQL> define col = job

SQL> /

SQL> def

SQL> def col

* acc[ept]

SQL> ed sv002.sql

accept col prompt '세번째 컬럼 이름을 넣으세요: '

select empno, ename, &col

from emp

order by &col desc;

SQL> @sv002

세번째 컬럼 이름을 넣으세요: sal

SQL> l

* 문자

SQL> select empno, ename, sal

     from emp

     where ename = &col_name;

col_name의 값을 입력하십시오: KING

==> 에러: ORA-00904: "KING": 부적합한 식별자

SQL> /

col_name의 값을 입력하십시오: 'KING'

구   3:      where ename = &col_name

신   3:      where ename = 'KING'

↓ ↓ ↓ 

SQL> l

SQL> c/&col_name/'&col_name'

* new_value

SQL> ed sv003 --> 자동적으로 sql 확장자가 붙습니다

col avg_sal new_value nv_avg_sal

select avg(sal) as avg_sal

from emp

where deptno = 10;

select *

from emp

where sal > &nv_avg_sal;

SQL> @ sv003

  AVG_SAL

----------

2916.66667

구   3: where sal > &nv_avg_sal

신   3: where sal > 2916.66667

    EMPNO ENAME      JOB              MGR HIREDATE        SAL       COMM     DEPTNO

---------- ---------- --------- ---------- -------- ---------- ---------- ----------

     7566 JONES      MANAGER         7839 81/04/02       2975                    20

     7788 SCOTT      ANALYST         7566 82/12/09       3000                    20

     7839 KING       PRESIDENT            81/11/17       5000                    10

     7902 FORD       ANALYST         7566 81/12/03       3000                    20

SQL> def nv_avg_sal

DEFINE NV_AVG_SAL      = 2916.66667 (NUMBER)

* &n: &1, &2, ..., &9

SQL> ed sv004

select empno, &1

from emp

where sal > &2;

SQL> @ sv004 ename 2000

SQL> alter session set nls_language='KOREAN';
 
세션이 변경되었습니다.

deptno=&dept_no는 입력되는 값을 받아서 where 절에서 deptno=입력값으로 제한하여 select 함 
SQL> select empno,ename,job,hiredate,deptno from emp
  2  where deptno=&dept_no;
dept_no의 값을 입력하십시오: 10
구   2: where deptno=&dept_no
신   2: where deptno=10
 
     EMPNO ENAME      JOB       HIREDATE           DEPTNO
---------- ---------- --------- -------------- ----------
      7782 CLARK      MANAGER   09-6월 -81             10
      7839 KING       PRESIDENT 17-11월-81             10
      7934 MILLER     CLERK     23-1월 -82             10
 
대소문자 구분없이 입력받기 위하여 UPPER 함수를 사용하였음 
SQL> select * from emp
  2  where UPPER(ename)=UPPER('&name');
name의 값을 입력하십시오: SCOTT
구   2: where UPPER(ename)=UPPER('&name')
신   2: where UPPER(ename)=UPPER('SCOTT')
 
     EMPNO ENAME      JOB              MGR HIREDATE              SAL       COMM DEPTNO
---------- ---------- --------- ---------- -------------- ---------- ---------- ----------
      7788 SCOTT      ANALYST         7566 19-4월 -87           3000            20
 
 
SQL> 

SQL> select *
  2  from test
  3  where name='&&name';

name의 값을 입력하십시오: 임효선
구   3: where name='&&name'
신   3: where name='임효선'
 
NAME       HIREDATE
---------- ----------------------------------------
임효선     12-1월 -06
 
SQL> / ☜ 다시 '임효선'을 입력하지 않아도 결과가 출력됨
구   3: where name='&&name'
신   3: where name='임효선'
 
NAME       HIREDATE
---------- ----------------------------------------
임효선     12-1월 -06
 
SQL> / ☜ 다시 '임효선'을 입력하지 않아도 결과가 출력됨
구   3: where name='&&name'
신   3: where name='임효선'
 
NAME       HIREDATE
---------- ----------------------------------------
임효선     12-1월 -06
 
SQL> 

SQL> select empno,ename,job,&&column_name
  2  from emp
  3  order by &order_column;
column_name의 값을 입력하십시오: hiredate   ☜ 처음에만 입력함
구   1: select empno,ename,job,&&column_name
신   1: select empno,ename,job,hiredate
order_column의 값을 입력하십시오: empno
구   3: order by &order_column
신   3: order by empno  
 
     EMPNO ENAME      JOB       HIREDATE
---------- ---------- --------- ----------------------------------------
      7369 SMITH      CLERK     17-12월-80
      7499 ALLEN      SALESMAN  20-2월 -81
      7521 WARD       SALESMAN  22-2월 -81
      7566 JONES      MANAGER   02-4월 -81
      7654 MARTIN     SALESMAN  28-9월 -81
      7698 BLAKE      MANAGER   01-5월 -81
      7782 CLARK      MANAGER   09-6월 -81
      7788 SCOTT      ANALYST   19-4월 -87
      7839 KING       PRESIDENT 17-11월-81
      7844 TURNER     SALESMAN  08-9월 -81
      7876 ADAMS      CLERK     23-5월 -87
      7900 JAMES      CLERK     03-12월-81
      7902 FORD       ANALYST   03-12월-81
      7934 MILLER     CLERK     23-1월 -82
 
14 개의 행이 선택되었습니다.
 
SQL> /
구   1: select empno,ename,job,&&column_name  ☜ 이후 부터는 column_name은 입력하지 않아도 됨
신   1: select empno,ename,job,hiredate
order_column의 값을 입력하십시오: ename
구   3: order by &order_column
신   3: order by ename

     EMPNO ENAME      JOB       HIREDATE
---------- ---------- --------- ----------------------------------------
      7876 ADAMS      CLERK     23-5월 -87
      7499 ALLEN      SALESMAN  20-2월 -81
      7698 BLAKE      MANAGER   01-5월 -81
      7782 CLARK      MANAGER   09-6월 -81
      7902 FORD       ANALYST   03-12월-81
      7900 JAMES      CLERK     03-12월-81
      7566 JONES      MANAGER   02-4월 -81
      7839 KING       PRESIDENT 17-11월-81
      7654 MARTIN     SALESMAN  28-9월 -81
      7934 MILLER     CLERK     23-1월 -82
      7788 SCOTT      ANALYST   19-4월 -87
      7369 SMITH      CLERK     17-12월-80
      7844 TURNER     SALESMAN  08-9월 -81
      7521 WARD       SALESMAN  22-2월 -81
 
14 개의 행이 선택되었습니다.
 
SQL>

SQL> define
DEFINE _DATE           = "14-12월-05" (CHAR)
DEFINE _CONNECT_IDENTIFIER = "orcl" (CHAR)
DEFINE _USER           = "SCOTT" (CHAR)
DEFINE _PRIVILEGE      = "" (CHAR)
DEFINE _SQLPLUS_RELEASE = "1001000300" (CHAR)
DEFINE _EDITOR         = "vi" (CHAR)
DEFINE _O_VERSION      = "Oracle Database 10g Enterprise Edition Release 10.1.0.3.0 - Production With the Partitioning, OLAP and Data Mining options" (CHAR)
DEFINE _O_RELEASE      = "1001000300" (CHAR)
DEFINE COLUMN_NAME     = "hiredate" (CHAR)

SQL> undefine column_name

SQL> define
DEFINE _DATE           = "14-12월-05" (CHAR)
DEFINE _CONNECT_IDENTIFIER = "orcl" (CHAR)
DEFINE _USER           = "SCOTT" (CHAR)
DEFINE _PRIVILEGE      = "" (CHAR)
DEFINE _SQLPLUS_RELEASE = "1001000300" (CHAR)
DEFINE _EDITOR         = "vi" (CHAR)
DEFINE _O_VERSION      = "Oracle Database 10g Enterprise Edition Release 10.1.0.3.0 - Production With the Partitioning, OLAP and Data Mining options" (CHAR)
DEFINE _O_RELEASE      = "1001000300" (CHAR)
DEFINE HIREDATE        = "" (CHAR)
SQL>

SQL> insert into dept values(
  2   &deptno,'&dname','&loc');

deptno의 값을 입력하십시오: 50
dname의 값을 입력하십시오: MANAGER
loc의 값을 입력하십시오: COREA

구   2:  &deptno,'&dname','&loc')
신   2:  50,'MANAGER','COREA')
 
1 개의 행이 만들어졌습니다.
 
SQL> select * from dept;
 
    DEPTNO DNAME          LOC
---------- -------------- -------------
        10 ACCOUNTING     NEW YORK
        20 RESEARCH       DALLAS
        30 SALES          CHICAGO
        40 OPERATIONS     BOSTON
        50 MANAGER        COREA
 
SQL>

SQL> desc test;
 이름                                      널?      유형
 ----------------------------------------- -------- ----------------------------
 NAME                                               VARCHAR2(10)
 HIREDATE                                           DATE

SQL> insert into test values(
  2  '&name','&hiredate');
name의 값을 입력하십시오: 좋은 친구
hiredate의 값을 입력하십시오: 5-3월-06
구   2: '&name','&hiredate')
신   2: '좋은 친구','5-3월-06')
 
1 개의 행이 만들어졌습니다.
 
SQL> /
name의 값을 입력하십시오: 길동무
hiredate의 값을 입력하십시오: 7-4월-06
구   2: '&name','&hiredate')
신   2: '길동무','7-4월-06')
 
1 개의 행이 만들어졌습니다.
 
SQL> select * from test;
 
NAME       HIREDATE
---------- ---------------
임효선     12-1월 -06
좋은 친구  05-3월 -06
길동무     07-4월 -06
 
SQL> 

SQL> insert into test values(&id, '&name','&email');
id의 값을 입력하십시오: 4444
name의 값을 입력하십시오: 홍길동
email의 값을 입력하십시오: nugu@arirang.com
구   2: &id, '&name','&email')
신   2: 4444, '홍길동','nugu@arirang.com')

1 개의 행이 만들어졌습니다.

SQL> select * from test;

        ID NAME                 EMAIL
---------- -------------------- -------------------------
      4444 홍길동               nugu@arirang.com


SQL> insert into test values(&&id,‘&&name’,‘&&email’);
id의 값을 입력하십시오: 5555
name의 값을 입력하십시오: nugu2
email의 값을 입력하십시오: nugu2@arirang.co.kr
구   1: insert into test values(&&id,‘&&name’,‘&&email’)
신   1: insert into test values(5555,‘nugu2’,‘nugu2@arirang.co.kr’)

1 개의 행이 만들어졌습니다.

SQL> select * from test;

        ID NAME                 EMAIL
---------- -------------------- -------------------------
      4444 홍길동               nugu@arirang.com
      5555 nugu2                nugu2@arirang.co.kr

SQL> define
DEFINE _DATE           = "2009-10-21" (CHAR)
DEFINE _CONNECT_IDENTIFIER = "orcl" (CHAR)
DEFINE _USER           = "NUGU" (CHAR)
DEFINE _PRIVILEGE      = "" (CHAR)
DEFINE _SQLPLUS_RELEASE = "1101000600" (CHAR)
DEFINE _EDITOR         = "vi" (CHAR)
DEFINE _O_VERSION      = "Oracle Database 11g Enterprise Edition Release 11.1.0.6.0 - 64bit Product
ion
With the Partitioning, OLAP, Data Mining and Real Application Testing options" (CHAR)
DEFINE _O_RELEASE      = "1101000600" (CHAR)

SQL> insert into test values(&&id, '&&name','&&email');
id의 값을 입력하십시오: 3333
name의 값을 입력하십시오: 좋은이
email의 값을 입력하십시오: aaa@arirang.com
구   1: insert into test values(&&id, '&&name','&&email')
신   1: insert into test values(3333, '좋은이','aaa@arirang.com')

1 개의 행이 만들어졌습니다.

SQL> define
DEFINE _DATE           = "2009-10-21" (CHAR)
DEFINE _CONNECT_IDENTIFIER = "orcl" (CHAR)
DEFINE _USER           = "NUGU" (CHAR)
DEFINE _PRIVILEGE      = "" (CHAR)
DEFINE _SQLPLUS_RELEASE = "1101000600" (CHAR)
DEFINE _EDITOR         = "vi" (CHAR)
DEFINE _O_VERSION      = "Oracle Database 11g Enterprise Edition Release 11.1.0.6.0 - 64bit Product
ion
With the Partitioning, OLAP, Data Mining and Real Application Testing options" (CHAR)
DEFINE _O_RELEASE      = "1101000600" (CHAR)
DEFINE ID              = "3333" (CHAR)
DEFINE NAME            = "좋은이" (CHAR)
DEFINE EMAIL           = "aaa@arirang.com" (CHAR)
SQL>

SQL> select *

     from emp

     order by sal desc;

SQL> save sv001 --> sav[e] : SQL buffer ==> file --> 현재 디렉토리에 저장

file sv001.sql(이)가 생성되었습니다

SQL> cl buff

SQL> get sv001 --> get    : file ==> SQL buffer

SQL> save c:\test_count

file c:\test_count.sql(이)가 생성되었습니다

SQL> define _editor

DEFINE _EDITOR         = "Notepad" (CHAR)

SQL> ed rep_sort_emp.sql

select empno, ename, sal, comm

from   emp

order by sal desc;

SQL> l

SQL> @ rep_sort_emp.sql

SQL> start sv001

SQL> spool에 관한 예제는 치환변수 편에서 확인

SQL> exit

C:\Documents and Settings\user\java>sqlplus java20/java20@211.183.3.52:1521/orcl

SQL> select empno

  2  from emp;

SQL> list

  1  select empno

  2* from emp --> *는 현재행을 의미 --> 모든 편집의 효과는 현재행에 미칩니다

SQL> l         ----------------->여기서 ㅣ은 list 

SQL> i

  3  where deptno = 10

  4 <-- 엔터를 한번 더 입력하면 실행하지 않고 편집을 종료

SQL> l

SQL> /

SQL> l

  1  select empno

  2  from emp

  3* where deptno = 10

SQL> c/10/20

  3* where deptno = 20

SQL> r

  1  select empno

  2  from emp

  3* where deptno = 20

SQL> c/e/a

  3* whare deptno = 20 <-- c는 첫번째로 일치하는 패턴을 변경

SQL> c1a1e <-- /뿐만 아니라 다른 문자도 가능

  3* where deptno = 20

SQL> l

SQL> a , deptno <-- 현재행에 붙임(append)

  3* where deptno = 20, deptno

SQL> c/, deptno/ <-- 지우려면 / 뒤에 그냥 엔터

SQL> 1 <-- 1번행을 현재행으로

SQL> a , deptno

SQL> r

SQL> 1

SQL> i

  2i      , ename, sal

  3i      , hiredate, comm

  4i

SQL> r

SQL> i

  6  and  comm is not null

  7  order by empno;

선택된 레코드가 없습니다.

SQL> 5

SQL> c/20/30

SQL> r

SQL> l 1 3

SQL> l * 6

SQL> l 2 last

SQL> 3

SQL> l * last

SQL> del 1

SQL> l

SQL> 2 , comm as commission

SQL> l

SQL> 1 select empno, deptno

SQL> 1      , ename, sal

SQL> 1

SQL> i

  2i select empno, deptno

  3i l

  4i

SQL> 0 select empno, deptno

SQL> l

SQL> del 3 4

SQL> l

SQL> 3

SQL> del * last

SQL> l

SQL> clear buffer --> cl buff

SQL> l

SQL> cl scr

SQL> / ==> SQL Buffer의 내용을 실행

SQL> r ==> run: SQL Buffer의 내용을 출력하고 실행

SQL> set serveroutput on

SQL> create procedure hello

     is

     begin

dbms_output.put_line('안녕하세요, 여러분!');

     end;

     /

SQL> execute hello

SQL> exec dbms_output.put_line('오라클 만세')

SQL> select empno, ename, sal as salary, job

          , nvl(comm, 0) "commission"

     from emp

     order by deptno;

SQL> set lines 40

SQL> /

SQL> set lines 120

SQL> set feed off

SQL> /

SQL> set feed 3

SQL> select * from emp

     where rownum < 3;

     EMPNO ENAME      JOB              MGR HIREDATE        SAL       COMM     DEPTNO

---------- ---------- --------- ---------- -------- ---------- ---------- ----------

      7369 SMITH      CLERK           7902 80/12/17        800                    20

      7499 ALLEN      SALESMAN        7698 81/02/20       1600        300         30

SQL> select * from emp

     where rownum < 4;

     EMPNO ENAME      JOB              MGR HIREDATE        SAL       COMM     DEPTNO

---------- ---------- --------- ---------- -------- ---------- ---------- ----------

      7369 SMITH      CLERK           7902 80/12/17        800                    20

      7499 ALLEN      SALESMAN        7698 81/02/20       1600        300         30

      7521 WARD       SALESMAN        7698 81/02/22       1250        500         30

3 개의 행이 선택되었습니다. <-- feed 값 3에 의해 3건 이상이 반환되면 메시지 출력됨

SQL> select employee_id, first_name, last_name, email

 , job_id, salary, department_id as dept

     from employees

     order by dept;

SQL> column employee_id format 9999

SQL> col first_name format a15

SQL> col last_name for a15

SQL> col email for a20

SQL> col salary for $999,999.99

SQL> col dept for 999

SQL> col last_name heading 'LAST NAME'

SQL> col salary heading 'Monthly|Salary'

SQL> col last_name clear

SQL> break on job

SQL> select * from emp;

SQL> break on deptno skip 1

SQL> select * from emp;

SQL> tti 'Salary|Report'

SQL> bti 'Confidential'

SQL> show all

SQL> show linesize

linesize 80

SQL> set lines 120

※기본적으로 set linesize 120 ,set pagesize 80 정도 해야지 도스창에서 검색된 db를 깔끔하게 볼수있다.

SQL> ? set

SQL> host dir

SQL> ho (엔터) ==> 운영체제로 잠시 빠져나갑니다.

C:\Documents and Settings\user\ora_java> exit --> 원래의 SQL*Plus로 돌아옵니다

SQL> $ dir --> 윈도우즈 환경에서는 $가 host 명령어 대신 사용

cf.) SQL> !ls -al --> unix-like 환경에서는 $ 대신 ! 사용

SQL> $dir --> 붙여 써도 동일한 결과

SQL> $

C:\Documents and Settings\user\ora_java>notepad login.sql

--> sqlplus 시작시 항상 읽어들이는 파일 login.sql

cf.) glogin.sql <-- sqlplus가 설치된 폴더에 위치

C:\Documents and Settings\user\ora_java>exit

SQL> exit

C:\Documents and Settings\user\ora_java>sqlplus java??/java??@211.183.3.52:1521/orcl

SQL> show linesize

SQL> show pages

SQL> $dir

SQL> show sqlprompt

sqlprompt "SQL> "

SQL> set sqlprompt "Oracle java >> "

SQL> set underline =

SQL> select * from emp;

SQL> set colsep |

SQL> select * from emp;

SQL> set null 모르겠는뎁쇼

SQL> select * from emp;

SQL> exit

C:\Documents and Settings\user\ora_java>sqlplus java??/java??@211.183.3.52:1521/orcl

C:\Documents and Settings\user\ora_java>sqlplus java??/java??@211.183.3.52:1521/orcl

SQL> help

SQL> ? desc

SQL> ? d

SQL> describe emp

SQL> desc dbms_output

SQL> conn java01/java01

==> ERROR: ORA-12560: TNS:프로토콜 어댑터 오류

경고: 이제는 ORACLE에 연결되어 있지 않습니다. ==> 로컬 오라클 서버로 연결을 시도했기 때문

SQL> desc emp

SP2-0640: 연결되지 않았습니다.

SP2-0641: "DESCRIBE"(이)가 서버로 연결하는데 필요합니다

SQL> conn java01/java01@211.183.3.52:1521/orcl

SQL> show user

USER은 "JAVA01"입니다

SQL> conn java??/java??@211.183.3.52:1521/orcl

SQL> disc

SQL> exit 또는 quit

여기 예제는 각자 해봐야 할듯~~~

-------------------------------

# SQL*Plus의 이해

-------------------------------

# SQL*Plus® User's Guide and Reference

==> http://docs.oracle.com/cd/B19306_01/server.102/b14357/toc.htm

# SQL Buffer

  - 최근 실행(입력)된 SQL 문장 또는 PL/SQL 블록을 저장하는 SQL*Plus의 메모리의 일부

  - 새로운 SQL 문장이나 PL/SQL 블록이 입력되면 기존의 SQL 문장 도는 PL/SQL 블록은 지워집니다

# SQL Statements vs. SQL*Plus Commands

  1) Language Environment

  2) ANSI 표준 ORACLE proprietary

  3) DB manipulation X

  4) Stored in SQL buffer X

  5) Termination Character (;) X , enter를 입력하면 바로 명령어가 실행

 cf.) 행연결문자 -  ex) SQL>set lines -

        > 120

  6) X Abbreviation --> ex) describe --> desc

# SQL*Plus Command Category

  - Environment : sho[w], set

  - Format : col[umn], bre[ak], tti[tle], bti[tle], ...

  - File Manipulation : sav[e], get, st[art], @, spo[ol], ed[it], ...

  - Execution : /, r[un], exec[ute], ho[st], ...

  - Edit : l[ist], a[ppend], i[nput], del[ete], c[hange], ...

  - Interaction : 치환변수: &, &&, &n, def[ine], undef[ine], acc[ept], ...

  - Misc. : help, ?, desc[ribe], conn[ect], disc[onnect], exit, quit, cl[ear]...