Bohemian life

SQL> alter session set nls_language = american;

SQL> alter session set nls_date_format = 'YYYY-MM-DD HH24:MI:SS';

--> format elements: YYYY, MM, DD, HH24, MI, SS 등등

SQL> select * from emp;

SQL> select sysdate from dual;

SQL> alter session set nls_date_format='DD/MON-RR HH:MI [AM]';

SQL> select * from emp;

SQL> select sysdate from dual;

SQL> alter session set nls_date_format='DD-MONTH-RR HH:MI:SS.SSSSS     PM';

SQL> select sysdate from dual;

SQL> alter session set nls_date_format='DD-month-rr';

SQL> select sysdate from dual;

SQL> alter session set nls_date_format='DD-mon-RR';

SQL> select sysdate from dual;

        SQL> desc dual 

SQL> desc sys.dual

SQL> select * from dual;

D

-

X

SQL> select 4770*123+5345/546

    from dual;

SQL> select round(45.995, 2)

    , round(45.995, -2)

    , round(645345.995, -2)

from dual;

   SQL> select trunc(45.995, 2)

    , trunc(45.995, -2) 

    , trunc(645345.995, -2)

from dual;

   SQL> select 7/3, ceil(7/3), floor(7/3)

from dual;

   SQL> select empno, mod(empno, 2), mod(empno, 3) from emp;

   SQL> select empno, mod(empno, 2), mod(empno, 3) from emp where mod(empno, 2) = 0;      --> 사번이 짝수

   SQL> select empno, mod(empno, 2), mod(empno, 3) from emp where mod(empno, 2) = 1;      --> 사번이 홀수

   SQL> select empno, mod(empno, 2), mod(empno, 3) from emp where mod(empno, 3) = 0;      --> 사번이 3의 배수

  문제) 월급을 * 를 이용해서 수평 막대 그래프로 표현하세요.

* 한개가 100입니다. 필요하면 올림을 수행하세요.

ENAME ...... SAL_GRAPH

------ ...... ----------------------

SMITH ...... ********

ALLEN ****************

WARD *************

힌트) CEIL, RPAD 함수를 사용하세요

해답)

SQL> select ename, sal, sal/100, ceil(sal/100)

    from emp;

↓ ↓ ↓ 

SQL> col sal_graph format a60

SQL> select ename, sal, rpad('*', ceil(sal/100)) as sal_graph

    from emp;

↓ ↓ ↓ 

SQL> select ename, sal, rpad('*', ceil(sal/100), '*') as sal_graph

    from emp;

접기

ROUND (number)

문법

MAIN

round_number::=

그림 설명
 

목적

MAIN

ROUND함수는 n값을 소수점 이하를 integer를 기준으로 반올림하여 반환한다. 만약 integer를 생략하면,n은 소수점을 반올림한다. 소수점 왼쪽의 정수부를 반올림하기 위해 음수값을 지정할수 있다.

n은 임의의 수치형 또는 수치형으로 변환가능한 비수치형을 지정할수 있다. 인수 integer는 정수이다. 만약 interger를 생략하면, 함수는 인수의 수치형과 같은 데이터형을 반환한다. 만약 interger를 포함하면, 함수는 NUMBER를 반환한다.

NUMBER값의 경우에, n값은 0으로부터 떨어져 반올림 할수 있다.(예를 들어, x.5가 양수일때 x+1, x.5가 음수일때 x-1). BINARY_FLOAT와 BINARY_DOUBLE값에 대하여, 함수는 가장 가까운 짝수값으로 반올림 한다.

예제

MAIN

다음 예제는 첫번째 소수점으로 반올림(소수점 두번째에서 반올림).

SELECT ROUND(15.193,1) "Round" FROM DUAL;

     Round
----------
      15.2

다음 예제는 정수에서 첫번째 자리에서 반올림.

SELECT ROUND(15.193,-1) "Round" FROM DUAL;

     Round
----------
        20 

다음 예제는 NUMBER값과 부동소수점 값 사이의 반올림 차이를 설명한다. NUMBER값은 절삭하고(양수값에 대하여), 부동 소수점은 가장 가까운 짝수 값으로 반올림 된다.

SELECT ROUND(1.5), ROUND(2.5) FROM DUAL;

ROUND(1.5) ROUND(2.5)
---------- ----------
         2          3

SELECT ROUND(1.5f), ROUND(2.5f) FROM DUAL;

ROUND(1.5F) ROUND(2.5F)
----------- -----------
   2.0E+000    2.0E+000

출처:
http://www.statwith.pe.kr/ORACLE/functions120.htm#i78633

접기

접기

TRUNC (number)

문법

MAIN

trunc_number::=

그림 설명
 

목적

MAIN

TRUNC(NUMBER)함수는 인수 n1을 소수점 자리 파라미터 n2 이하를 절삭한다.만약 인수 n2를 지정하는 않는 경우, 인수 n1의 소수점 이하를 절삭한다. 만약 인수 n2가 음수인 경우는, 소수점 왼쪽의 n2자리(정수부분)에서 절삭한다.

이 함수는 인수로서 임의의 수치 데이터형 또는 암묵적으로 수치 데이터형으로 변환 가능한 비수치 데이터형을 취한다. 만약 인수가 BINARY_FLOT 형태인 경우에는, 이 함수는 BINARY_DOUBLE 타입을 반환한다. 이 외에는 인수와 같은 수치 데이터형을 반환한다.

예제

MAIN

다음 예제는 수치를 절삭(두번째 예제는 정수부 일의 자리에서 절삭):

SELECT TRUNC(15.79,1) "Truncate" FROM DUAL;

  Truncate
----------
      15.7

SELECT TRUNC(15.79,-1) "Truncate" FROM DUAL;

  Truncate
----------
        10

출처:http://www.statwith.pe.kr/ORACLE/functions185.htm#i79729

접기

접기

CEIL

문법

MAIN

ceil::=

그림 설명 : ceil(n)

 

목적

MAIN

CEIL 함수는 인수에서 지정한 수치를 올림하여 정수를 구하는 함수이다. 즉, 인수로 지정한 수치보다 크고 가까운 정수를 반환한다. 음의 수치를 부여하면 음수측의 가장 가까운 정수를 반환한다.

이 함수는 인수로서 임의의 수치 데이터형 또는 암묵적으로 수치 데이터형으로 변환 가능한 비수치 데이터형을 취한다.이 함수는 인수의 수치 데이터형과 같은 데이터형을 반환한다.

 

예제

MAIN

다음 예제는 15.7 이상의 가장 작은 정수를 반환 :

SELECT CEIL(15.7) "Ceiling" FROM DUAL;

   Ceiling
----------
        16

출처:
http://www.statwith.pe.kr/ORACLE/functions016.htm#i97801

접기

접기

FLOOR

문법

MAIN

floor::=

그림 설명
 

목적

MAIN

FLOOR 함수는 지정한 숫자보다 작거나 같은 정수 중에서 최대값을 반환한다.

이 함수는 인수로서 임의의 수치 데이터형 또는 암묵적으로 수치 데이터형으로 변환 가능한 비수치 데이터형을 취한다. 이 함수는 인수의 수치형 형태와 같은 데이터형을 반환한다.

예제

MAIN

다음 예제는 15.7보다 작거나 같은 정수 중에서 최대값을 반환.

SELECT FLOOR(15.7) "Floor" FROM DUAL;

     Floor
----------
        15

출처:
http://www.statwith.pe.kr/ORACLE/functions050.htm#i77449

접기

접기

MOD

문법

MAIN

mod::=

그림 설명

 

목적

MAIN

MOD함수는 n2을 n1으로 나눈 나머지값을 반환한다. n1이 0이면, n2를 반환한다.

이 함수는 인수로서 임의의 수치 데이터형 또는 암묵적으로 수치 데이터형으로 변환 가능한 비수치 데이터형을 취한다. 오라클은 우선 순위가 가장 높은 수치형 인수를 판단하여, 나머지 인수를 그 데이터형에 암묵적으로 변환하고, 그 데이터 형을 반환한다.

예제

MAIN

다음 예제는 11을 4로 나눈 나머지를 반환.

SELECT MOD(11,4) "Modulus" FROM DUAL;

   Modulus
----------
         3

This function behaves differently from the classical mathematical modulus function when m is negative. The classical modulus can be expressed using the MOD function with this formula:

m - n * FLOOR(m/n)

The following table illustrates the difference between the MOD function and the classical modulus:

m	n	MOD(m,n)	Classical Modulus
11	4	3	3
11	-4	3	-1
-11	4	-3	1
-11	-4	-3	-3

출처:http://www.statwith.pe.kr/ORACLE/functions078.htm#i77996

접기

==> http://goo.gl/XQesC

  문제) 책 제목(ex: SQL*Plus User's Guide) 을 파일 이름으로 변환하는 방법?

SQL> select 'SQL*Plus User''s Guide' from dual;

↓ ↓ ↓ 

SQL> select 'SQL*Plus User''s Guide'

         , translate('SQL*Plus User''s Guide', ' */''', '___')

    from dual;

  문제) 다섯 자리 숫자가 포함된 행?

   SQL> drop table t1 purge;

   SQL> create table t1 (col1 varchar2(50));

   SQL> insert into t1 values ('여기는 ... 77785 입니다.');

   SQL> insert into t1 values ('그러나 241 이고 354 이면 ...');

   SQL> insert into t1 values ('잘 진행되면 98097가 가능하기도 ...');

   SQL> commit;

SQL> select * from t1;

SQL> select col1, translate(col1, '0123456789', '----------')

    from t2;

↓ ↓ ↓ 

SQL> select * from t2

    where translate(col1, '0123456789', '----------') like '%-----%';

접기

TRANSLATE

문법

translate::=

그림 설명
 

목적

 TRANSLATE함수는 from_string에서 각 문자를 to_string안의 대응하는 문자로 치환하여 expr을 반환한다. from_string안에 존재하지 않는 expr안의 문자는 치환되지 않는다. 만약 expr이 문자열이라면, 홑 따옴표로 둘러쌓여야 한다. 인수 from_string는 to_string보다 더 많은 문자를 가질수 있다. 이 경우에, from_string의 마지막에 있는 여분의 문자는 to_string안에 상응하는 문자가 없다. 만약 이 여분의 문자가 char안에 있는 경우, 그러한 문자는 반환값으로부터 삭제된다.

반환값으로부터 from_string안의 모든 문자를 삭제하기 위해서 to_string에 대한 빈 문자열을 사용할수 없다. 오라클 데이터 베이스는 공백 문자열을 NULL로써 해석하고, 이 함수가 NULL 인수를 가지면, NULL을 반환한다.

TRANSLATE함수는 REPLACE함수에 의해 제공되는 관련 함수를 제공한다. REPLACE함수는 단일 문자열로부터 다른 단일 문자열의 치환과 문자열의 삭제를 실행할수 있다. TRANSLATE는 하나의 연산에서 단일 조작으로 복수의 단일 문자를 1대 1로 치환할수 있다.

이 함수는 CLOB데이터를 직접적으로 지원하지 않는다. 그러나 암묵적인 데이터 변환으로 CLOB를 인수로서 전달할수 있다.

예제

 다음 문장은 book 타이틀을 파일명으로써 이용되기 위해서 문자열로 변환한다. from_string는 공백,*,/,''(escape문자로써 여분의 apostrophe를 가지는)의 4개의 문자를 포함한다. to_string는 3개의 underscore를 소유한다. from_string의 4번째 문자(')에 대응하는 치환 문자가 없기 때문에, 반환되는 값으로 apostrophe가 삭제되었다.

SELECT TRANSLATE('SQL*Plus User''s Guide', ' */''', '___') FROM DUAL;

TRANSLATE('SQL*PLUSU
--------------------
SQL_Plus_Users_Guide

출처:http://www.statwith.pe.kr/ORACLE/functions181.htm#i1501659

접기

- LTRIM, RTRIM 함수의 기본 예제: 기본 기능은 공백 제거

SQL> select substr('The Book and Movie', 4, 6)||'s Books'

         , rtrim(substr('The Book and Movie', 4, 6))||'s Books'

    from dual;

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

SQL> select ltrim('SSESS', 'S') from dual;

SQL> select trim(leading 'S' from 'SSESS') from dual;

SQL> select rtrim('SSESS', 'S') from dual;

SQL> select trim(trailing 'S' from 'SSESS') from dual;  

SQL> select trim(both 'S' from 'SSESS') from dual;  

SQL> select rtrim(ltrim('SSESS', 'S'), 'S') from dual;

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

SQL> select ltrim('xyxxyXYxy', 'xy') from dual;

    ==> http://goo.gl/kKO19

SQL> select trim('xy' from 'xyxxyXYxy') from dual;

==> 오류: ORA-30001: 트림 설정은 하나 문자만 가지고 있어야 합니다

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

  - LTRIM(char, [set]), RTRIM(char, [set])

     : set에 포함된 문자열이 아닌 문자열을 이루는 문자가 나타나지 않을 때까지 그 문자 하나 하나를 제거합니다.

SQL> select rtrim('abaa abbee aab', 'b a') from dual;

  문제) 숫자로만 이루어진 행 찾기ㅣ

SQL> drop table t1 purge;

SQL> create table t1 (col1 varchar2(10));

SQL> insert into t1 values ('77785');

SQL> insert into t1 values ('HJ241');

SQL> insert into t1 values ('9090P');

SQL> insert into t1 values ('130914');

SQL> insert into t1 values ('PUB01');

SQL> commit;

  - 해답 1)

SQL> select col1, ltrim(col1, '0123456789') from t1;

↓ ↓ ↓ 

SQL> select col1 from t1

    where ltrim(col1, '0123456789') is null;

  - 해답 2)

SQL> select col1, col1, col1

    from t1;

↓ ↓ ↓ 

SQL> select col1, upper(col1), lower(col1)

    from t1;

↓ ↓ ↓ 

SQL> select col1

    from t1

    where upper(col1) = lower(col1);

  - 해답 3)

SQL> select col1

    from t1

    where regexp_like(col1, '^[0-9]+$');

---

RTRIM

접기

RTRIM

문법

rtrim::=

 

목적

 RTRIM함수는 인수 char의 오른쪽 끝에서 부터 set으로 지정된 모든 문자를 제거한다. 이 함수는 쿼리의 출력 포    맷에 유용하다.

만약 set을 지정하지 않았다면, 기본적으로 공백 한개가 지정된다. 만약 char이 문자 리터럴이라면, 작은 따옴표로 둘러 쌓아주어야만한다. RTRIM함수는 LTRIM과 유사하다.

char과 set의 데이터 형은 CHAR,VARCHAR2,NCHAR,NVARCHAR2,CLOB,NCLOB이다.

예제

다음 예제는 문자열의 오른쪽편으로부터 '/=.'을 제거한다:

SELECT RTRIM('BROWNING: ./=./=./=./=./=.=','/=.') "RTRIM example" FROM DUAL;
 
RTRIM exam
----------
BROWNING:

출처:http://www.statwith.pe.kr/ORACLE/functions126.htm#i1018967

접기

---

LTRIM

접기

LTRIM

문법

ltrim::=

 

목적

 LTRIM함수는 문자열 char 좌측으로부터 set으로 지정된 모든 문자를 제거한다. 만약 set이 지정되지 않았다면, 디폴트로 한개의 공백이 지정된다. 만약 char이 문자열이라면, 따옴표를 해야만한다. 오라클 데이터 베이스는 char 첫 문자에서부터 스캔을 하여 set으로 지정된 모든 문자를 삭제한다. 지정한 set이외의 문자 발견시 결과를 반환한다.

char과 set의 데이터 형은 CHAR,VARCHAR2,NCHAR,NVARCHAR2,CLOB,NCLOB이다.

예제

다음 예제는 좌측에서부터 'x'와'y' 문자를 제거한다. 대문자 'X'에서 반환한다.

SELECT LTRIM('xyxXxyLAST WORD','xy') "LTRIM example"
   FROM DUAL;

LTRIM example
------------
XxyLAST WORD


출처:

http://www.statwith.pe.kr/ORACLE/functions073.htm#i77875

접기

SQL> select ename, lpad(ename, 10, '-'), rpad(ename, 10, '+')

    from emp;

↓ ↓ ↓ 

SQL> select ename, lpad(ename, 10, ' '), rpad(ename, 10)

    from emp;

SQL> select ename, substr(lpad(ename, 10, ' '), 10, 1), substr(ename, -1, 1)

====================================================================================================

접기

RPAD

문법

rpad::=

 

목적

 RPAD함수는 인수 expr1 오른편으로 인수 expr2로 지정한 문자를 길이 필요에 따라 반복하여 n만큼 붙여준다. 이 함수는 쿼리의 출력 포맷을 맞추는데 도움이 된다.

expr1과 expr2의 데이터 형식은 CHAR,VARCHAR2,NCHAR,NVARCHAR2,CLOB,NCLOB이다. 인수 n은 NUMBER 정수이거나, NUMBER 정수로 암묵적으로 변환될수 있는 값이다.

만약 expr2를 지정하지 않았다면, 기본적으로 공백 1개가 된다. 만약 expr1이 n보다 긴경우에는, 함수는 n에 적합한 expr1의 일부를 반환한다.

인수 n은 터미날 스크린에 표시되는 반환값의 전체 길이이다. 대부분 문자 세트에서, 반환값에서 문자의 수이다. 그러나, 멀티바이트 문자 세트에서는 문자열의 표시되는 길이가 문자열에서 문자의 수와 다를수 있다.

예제

다음 예제는 공백에 * 을 붙여서 급여액의 간단한 그래프를 생성한다:

SELECT last_name, RPAD(' ', salary/1000/1, '*') "Salary"
   FROM employees
   WHERE department_id = 80
   ORDER BY last_name;

LAST_NAME                 Salary
------------------------- ---------------
Abel                       **********
Ande                       *****
Banda                      *****
Bates                      ******
Bernstein                  ********
Bloom                      *********
Cambrault                  **********
Cambrault                  ******
Doran                      ******
Errazuriz                  ***********
Fox                        ********
Greene                     ********
Hall                       ********
Hutton                     *******
Johnson                    *****
King                       ********* 

. . .

출처:http://www.statwith.pe.kr/ORACLE/functions125.htm#i78723

접기

접기

LPAD

문법

lpad::=

 

목적

 LPAD함수는 지정된 자리수 n으로부터 expr1을 채우고,왼편의 남은 공간에 expr1을 채운다. 이 함수는 쿼리의 출력 서식에 대하여 유용하다.

인수 expr1 과 인수 expr2의 데이터 형식은 CHAR,VARCHAR2,NCHAR,NVARCHAR2,CLOB,NCLOB이다. 반환되는 문자열은 VARCHAR2데이터형이고, 인수 expr1과 같은 문자이다. 인수 n은 숫자 정수 또는 암묵적으로 정수로 변환가능한 값이다.

만약 expr2를 지정하지 않는다면, 기본값은 Blank이다. 만약 expr1이 n보다 크다면, 이 함수는 n에 적합한 expr1의 일부를 반환한다.

인수n은 터미널 스크린에서 표시되는 것과 같은 반환값의 전체 Length이다. 대부분의 문자세트에서, 이것은 반환되는 값에서 문자의 숫자이다. 그러나 multibyte문자세트에서는, 문자열의 표시되는 Length가 문자열에서 문자의 숫자로부터 다를수있다.

예제

다음 예제는 문자열의 좌측에 '*.'을 채운다.

SELECT LPAD('Page 1',15,'*.') "LPAD example"
   FROM DUAL;

LPAD example
---------------
*.*.*.*.*Page 1

출처:http://www.statwith.pe.kr/ORACLE/functions072.htm#i1371196

접기

SQL> SELECT INSTR('CORPORATE FLOOR', 'OR', 3, 2) "Instring" FROM DUAL;

SQL> SELECT INSTR('CORPORATE FLOOR', 'OR', -3, 2) "Reversed Instring" FROM DUAL;

SQL> SELECT INSTR('CORPORATE FLOOR', 'OR', -3, 1) "Reversed Instring" FROM DUAL;

문제) 이름에 S가 포함된 사원?

SQL> select *

    from emp

    where ename like '%S%';

* INSTR 이용

SQL> select ename, ename

    from emp;

↓ ↓ ↓ 

SQL> select ename, instr(ename, 'S')

    from emp;

↓ ↓ ↓ 

SQL> select *

    from emp

    where instr(ename, 'S') > 0;

instr

접기

INSTR

문법

instr::=

그림 설명
 

목적

INSTR함수는 문자열중에서 지정한 문자가 처음 나타나는 위치를 숫자로 반환.

The INSTR함수는 string에서 substring를 찾는다. 이 함수는 처음 발생한 문자 string의 위치를 나타내는 정수를 반환한다. INSTR함수는 입력 문자 세트에 의해서 정의된 문자를 이용하여 문자열을 산출한다.

INSTRB함수는 문자 대신에 byte를 사용한다.

INSTRC함수는 완전한 UNICODE문자를 사용한다.

INSTR2함수는 UCS2 코드 포인트를 사용한다.

INSTR4함수는 UCS4 코드 포인트를 사용한다.

• position은 오라클 데이터 베이스가 검색을 시작하는 string에서 문자의 위치를 나타내는 0이외의 정수이다. 만약 Position이 음수라면, 오라클은 string의 끝에서 역으로 카운트하여 검색한다.

• occurrence은 오라클이 검색하여 string의 발생 횟수(정수)이다. Occurrence값은 양의 정수이다.

string과 substring의 데이터 형은 CHAR,VARCHAR2,NCHAR,NVARCHAR2,CLOB,NCLOB이다. 반환값의 데이터형은 NUMBER이다.

Position과 occurrence는 NUMBER 데이터형이거나, 암묵적으로 NUMBER형으로 변환가능한 데이터형일 필요가 있고, 정수로 변환될 필요가있다. Position 과 occurrence의 기본값은 1이고, 오라클은 string의 처음 문자에서 검색을 시작하여, substring이 처음 나타나는 위치를 검색한다. 반환되는 값은 position의 값과 상관없이 string의 시작에 비례하고, 문자에서 표현된다. 만약 검색이 실패한다면(substring이 string의 position 문자 뒤에 occurrence번 나타나지 않는다면), 반환값은 0이다.

 

예제

다음 예제는 문자열 CORPORATE FLOOR을 검색하여, 3번째 문자에서 시작하여, "OR"이 두번째 발생하는 위치를 반환한다.

SELECT INSTR('CORPORATE FLOOR','OR', 3, 2)
  "Instring" FROM DUAL;
 
  Instring
----------
        14

다음 예제는 문자열 끝에서 시작하여서 3번째 문자위치에서 역으로 검색하여서 "OR"이 두번째 나타나는 위치를 반환한다.

SELECT INSTR('CORPORATE FLOOR','OR', -3, 2)
"Reversed Instring"
     FROM DUAL;
 
Reversed Instring
-----------------
               2

다음 예제는 2 바이트를 가정한다.

SELECT INSTRB('CORPORATE FLOOR','OR',5,2) "Instring in bytes"
   FROM DUAL;

Instring in bytes
-----------------
               27

출처:http://www.statwith.pe.kr/ORACLE/functions058.htm#i77598

접기

SQL> select length('한글'), length('오라클') from dual;

LENGTH('한글') LENGTH('오라클')

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

            2                3

SQL> select length('한국Oracle') from dual;

LENGTH('한국ORACLE')

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

                  8

문제) 회사에서 이름이 5자인 사원들의 모든 정보를 출력해다오, 오라클아!

SQL> select ename, length(ename)

    from emp;

방법1)

SQL> select *

    from emp

    where length(ename) = 5;

방법2)

select *

from emp

where ename like '_____';

접기

LENGTH

length::=

목적

LENGTH함수는 인수 char의 길이를 반환한다. LENGTHB는 charcter 대신 byte사용한다. LENGTHC는 unicode사용한다. LENGTH는 UCS2 codepoint사용한다.LENGTH4는 UCS4 codepoint사용한다.

인수 char의 데이터 형은 CHAR, VARCHAR2, NCHAR, NVARCHAR2, CLOB, NCLOB이다. 반환된 값의 데이터 형은 NUMBER이다. 만약 인수 char의 데이터형이 CHAR이라면, 길이는 모든 trailing공백을 포함한다. 만약 char이 Null이라면, Null을 반환한다.







예제




다음 예제는 단독 바이트 데이터 베이스 문자 세트를 이용한 LENGTH 함수이다.

SELECT LENGTH('CANDIDE') "Length in characters"
   FROM DUAL;

Length in characters
--------------------
                   7

다음 예제는 더블-바이트 데이터 베이스 문자 세트를 가정한다.

SELECT LENGTHB ('CANDIDE') "Length in bytes"
   FROM DUAL;
 
Length in bytes
---------------
             14

출처:http://www.statwith.pe.kr/ORACLE/functions066.htm#i77725

접기

# 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('오라클 만세')