SQL문 정리

WiNMasTeR의 블로그예요. ^^ | 윈마스터
http://blog.naver.com/jgalgil/120033629705

SQL문 정리

원문출처:http://www.cyberlab.pe.kr/dev_tip_board/read.neo?id=2&cn=2&tn=4&ln=2&pn=20&lv=0&topic=oracle

 

SELECT

DML(데이터 조작어)

INSERT, UPDATE, DELETE

DDL(데이터 정의어) IMPLICIT COMMIT

CREATE, ALTER, DROP, RENAME, TRUNCATE

TCL(트랜잭션 제어)

COMMIT, ROLLBACK, SAVEPOINT

DCL(데이터 제어어)IMPLICIT COMMIT

GRANT, REVOKE

 

[1] Writing Basic SQL Statements

1. SELECT 기본 문장( 선택, 프로잭션, 조인)

SELECT [DISTINCT] { *, column [alias], ... }

FROM table ;

 

2. SELECT 예제

SELECT * FROM dept ;

SELECT deptno, loc FROM dept ;

SELECT ename, sal, 12 * (sal + 100) FROM emp ;

* Null 값과 연산을 하면 Null이 나온다.

 

3. Column Alias 예제

SELECT ename AS nme, sal salary

FROM emp ;

SELECT ename "Name", sal*12 "Annual Salary"

FROM emp ;

* 대소문자를 구분하고 공백있는 컬럼 Alias를 만들고 싶을땐 " "로 막는다.

* AS는 안 써도 된다.

* WHERE, GROUP BY절에는 안된다. ORDER BY 절에는 사용 가능.

4. Concatenation 연산자 (|| : pipeline 2개)

SELECT ename||job "Employees" FROM emp ;

--> ename 데이터와 job 데이터가 붙어서 출력된다.

 

5. 문자열을 데이터로 출력할때

SELECT ename||' '||'is a'||' '||job "Employee Details"

FROM emp ;

* 문자열은 ' '로 막고, 컬럼 Alias는 " "로 막는다.

 

6. DISTINCT keyword : 중복된 Row를 하나로 만들어 준다. 자동 SORTING[ASC]

SELECT DISTINCT deptno

FROM emp ;

* DISTINCT 대신 UNIQUE를 써도 된다.

 

7. SQL*Plus Log On 방법

* UserName, PassWord, HostString에 일일이 입력해도 되지만,

UserName에 username/password@HostString이라고 입력하면 된다.

* UNIX상에서 command로 들어 갈 때는 sqlplus username/password 만 입력하면된다.

8. 테이블 구조보는 SQL Command (DESC)

SQL> DESC dept : Column Name, Null?, Data Type display

 

9. SQL*Plus Editing Commands( 다음 행까지 계속하려면 -(하이픈)으로 연결한다.)

① A[PPEND] text : 현재 line의 마지막 문장 뒤에 text를 붙인다.

② C[HANGE]/old/new : 현재 line의 old text를 new text로 바꾼다.

③ C[HANGE]/text/ : 현재 line을 text를 삭제한다.

④ CL[EAR] BUFF[ER] : buffer의 내용을 모두 지운다.

⑤ DEL : 현재 line을 지운다.

⑥ DEL n : n번째 line을 지운다.

⑦ DEL m n : m ~ n번째 line을 지운다.

⑧ I[NPUT] : 현재 line 다음에 line이 제한없이 추가된다.

⑨ I[NPUT] text : 현재 line 다음에 line이 추가되면서 text가 들어간다.

⑩ L[IST] : buffer전체를 보여준다.

⑪ L[IST] n : n번째 line을 보여준다.

⑫ R[UN] or / : SQL, PL/SQL문장을 실행하라!

⑬ n : n번째 line을 display하면서 Editing 상태로 해준다.

⑭ n text : n번째 line이 text로 바뀐다.

⑮ 0 text : 1번째 line이 추가되면서 text가 1번째 line으로 들어간다.

* Bald로 표시된 명령어는 line번호를 먼저 수행한 후 실행해야 한다.

10. SQL*Plus File Commands

① SAV[E] filename [REP[LACE]|APP[END]] : buffer의 내용을 filename.sql로 저장한다.

② GET filename : filename.sql을 buffer로 불러온다.

③ START filename : filename.sql을 실행하라.

④ @filename : START filename과 같다.

⑤ ED[IT] : buffer의 내용을 edit program으로 실행한다.

⑥ ED[IT] filename : filename.sql을 edit program으로 실행한다.

⑦ SPO[OL] filename : retrieve data를 filename.lst로 저장한다.

⑧ SPOOL OFF : SPOOL을 끝내라.

⑨ SPOOL OUT : retrieve data를 system printer로 출력하라.

⑩ EXIT : SQL*Plus를 종료한다.

* SPOOL 사용법

SQL> spool filename

SQL> select ...

SQL> spool off

 

11. Special Tip

* 잠시 host상태로 나가고 싶을 때.

SQL> ! ( $)

-- host 상에서 다시 SQL로 들어가려면 exit(lo)

-- unix 상에서 env를 치면 오라클 환경을 볼 수 있다.

 

* SQL> define -editor

--> Editor가 vi인지..다른 edit프로그램인지를 보여준다.

 

* line size 바꾸기

SQL> SET PAGESIZE 20 -- 한 page를 20line으로 보여준다.

-- log off하면 사라진다.

 

* NLS값 보기

SQL> select * from V$NLS_PARAMETERS

 

* NLS값 바꾸기

SQL> ALTER SESSION SET NLS_DATE_FORMAT = 'YYYY-MM-DD'

-- SESSION : 현 session동안만 YYYY-MM-DD 포맷으로 사용한다는 뜻.

sqlplus가 종료되면 원상태로 복구된다.

 

[2]Restricting and Sorting Data

1. 비교연산자

= : Equal to

> : Grater than

>= : Greater than or equal to

< : Less than

<= : Less than or equal to

<> : Not equal to

예) SELECT ename, sal, comm

FROM emp

WHERE sal <= comm ;

 

2. 비교연산자 2

BETWEEN A AND B , IN(list), LIKE, IS NULL

 

3. BETWEEN 연산자( NOT BETWEEN )

SELECT ename, sal

FROM emp

WHERE sal BETWEEN 1000 AND 1500 ; --> sal >= 1000 and sal <= 1500

 

4. IN 연산자( NOT IN )

SELECT emp, ename, sal, mgr

FROM emp

WHERE mgr IN (7902, 7566, 7788) ; --> mgr = 7902 or mgr = 7566 or mgr = 7788

 

5. LIKE 연산자( NOT LIKE )

예1) SELECT ename

FROM emp

WHERE ename LIKE 'S%' ; --> ename이 S로 시작하는 모든 데이터를 찾는다.

예2) SELECT ename

FROM emp

WHERE ename LIKE '_A%' ; --> 두 번째 글자가 A인 모든 데이터를 찾는다.

예3) SELECT ename

FROM emp

WHERE ename LIKE '%A/_%B' ESCAPE '/' ;

--> '/'는 Escape문자로 정의되었기 때문에 '_'도 문자로 인식한다.

즉, ename이 A_로 포함하는 모든 데이터를 찾는다.

* ESCAPE는 모든 문자가 가능하다.

 

6. IS NULL 연산자

SELECT ename, mgr

FROM emp

WHERE mgr IS NULL ; --> mgr이 null인 데이터를 찾는다.

 

7. Logical 연산자

AND : 두 개의 조건이 모두 만족해야 OK

OR : 한 개의 조건만 만족하면 OK

NOT

 

8. AND 연산자

SELECT empno, ename, job, sal

FROM emp

WHERE sal >= 1100

AND job = 'CLERK' ;

 

9. OR 연산자

SELECT empno, ename, job, sal

FROM emp

WHERE (sal >= 1100 OR job = 'CLERK') ;

 

10. NOT 연산자

SELECT ename, job

FROM emp

WHERE job NOT IN ('CLERK', 'MANAGER') ;

--> NOT (job = 'CLERK OR job = 'MANAGER')

 

11. 연산자 우선순위( 산술 > 문자 > 비교 > 논리 )

1 : 모든 비교 연산자

2 : NOT

3 : AND --> False > Null > True

4 : OR --> True > Null > False

예) A AND B에서...

- A가 False이고 B가 Null이면... False 이다.

- A가 True이고 B가 Null이면.... Null이다.

 

12. SORT (ORDER BY)

* ASC는 default값이다.(작은 값부터..)

* 값이 Null일 때 가장 큰 값이 된다. (ASC일 때...맨 뒤에 붙는다.)

* column alias도 sorting이 된다.

예1) SELECT ename, job, deptno, hiredate "Date"

FROM emp

ORDER BY hiredate["Date" or 4 ] ;

 

예2) SELECT ename, job, deptno, hiredate

FROM emp

ORDER BY hiredate DESC ;

 

예3) SELECT empno, ename, sal*12 annsal

FROM emp

ORDER BY empno, annsal ; * select 절에 없는 열을 기준으로 정렬 가능.

예4) SELECT ename, deptno, sal

FROM emp

ORDER BY deptno, sal DESC

 

[3] Single-Row Functions

1. Character Functions

①LOWER( column|expression )

LOWER('String') --> string : 소문자로 변환

②UPPER( column|expression )

UPPER('String') --> STRING : 대문자로 변환

③INITCAP( column|expression )

INITCAP('string') --> String : 첫글자만 대문자이고 나머지글자는 소문자로 변환

④CONCAT( column1|expression1 ,column2|expression2 )

CONCAT('Good','String') --> GoodString : ||와 같은 역할을 한다.

⑤SUBSTR(column|expression, m [,n]) : m값이 음수면 문자값의 끝부터..)

SUBSTR('String',1,3) --> Str : 1번째부터 3개의 문자를 리턴한다.

⑥LENGTH( column|expression )

LENGTH('String') --> 6 : 문자열의 길이를 리턴한다.

⑦INSTR( column|expression, )

INSTR('String','r') --> 3 : 문자열에 'r'이 몇번째 위치하고 있나를 리턴한다.

⑧LPAD( column|expression,n,'string' ) : n 은 전체 길이

LPAD('String',10,'*') --> ****String

: 10자리수중에 모자란 자리수를 '*'로 왼쪽에 채운다.(문자,숫자 가능!!!)

⑨ RPAD('String',10,'*') --> String****

: 10자리수중에 모자란 자리수를 '*'로 오른쪽에 채운다.(문자,숫자 가능!!!)

⑩ LTRIM(' String') --> 'String' : 문자열의 왼쪽 공백을 버린다.

⑪ RTRIM('String ') --> 'String' : 문자령의 오른쪽 공백을 버린다.

* TRIM(leading/tailing/both, trim_character FROM trim_source )

TRIM( 'S' FROM 'SSMITH') --> MITH

2. Number Functions

① ROUND(45.926, 2) --> 45.93 : 소수점 두자리수까지 보여주면서 반올림한다.

② TRUNC(45.926, 2) --> 45.92 : 소수점 두자리까지만 보여주고 나머지는 버린다.

③ MOD(1600,300) --> 100 : 1600을 300으로 나누고 나머지를 리턴한다.

* ROUND예제(WHOLE NUMBER:정수)

SELECT ROUND(45.923,2), ROUND(45.923,0), ROUND(45.923,-1) FROM DUAL

==> 45.92 46 50

* TRUNC예제

SELECT TRUNC(45.923,2), TRUNC(45.923), TRUNC(45.923,-1) FROM DUAL

==> 45.92 45(n이 생략된면 일의 자리까지 남기고 버린다.) 40

* SYSTEM 날짜를 갖고 오는 방법.

SELECT sysdate FROM dual

 

3. Date 계산( 날짜를 숫자로 저장)

date + number : date에 number만큼 후의 날자를 보여준다.

date - number : date에 number만큼 전의 날자를 보여준다.

date1 - date2 : date1에서 date2 까지의 총 일수를 보여준다.( date1+date2는 X )

date1 + 숫자/24 : date1에서 시간을 더해 날짜를 보여준다.

4. Date Functions

MONTHS_BETWEEN('01-SEP-95','11-JAN-94') --> 19.6774194

; 두날짜 사이의 달수를 보여준다.

ADD_MONTHS('11-JAN-94', 6) --> 11-JUL-94

; 날짜에 6개월을 더한 날자를 보여준다.

NEXT_DAY('01-SEP-95','FRIDAY') --> '08-SEP-95'

; 해당일 다음에 오는 FRIDAY의 일자를 보여준다.

('SUNDAY'는 1, 'MONDAY'는 2...이런식으로 숫자를 써줘도 된다.)

LAST_DAY('01-SEP-95') --> '30-SEP-95'

; 해당월의 마지막날자를 보여준다.

ROUND('25-JUL-95','MONTH')--> 01-AUG-95 ROUND('25-JUL-95','YEAR')--> 01-JAN-96

TRUNC('25-JUL-95','MONTH') --> 01-JUL-95 TRUNC('25-JUL-95','YEAR') --> 01-JAN-95

 

5. Conversion Functions

nlsparams : 십진수, 그룹구분자, 지역 통화 기호, 국제 통화 기호

TO_CHAR(date,['format'],[nlsparams]) : date를 format에 맞게 문자열로 변환한다.

- Date Format Elements

YYYY --> 1999 (년출력) , YEAR --> nineteen ninety-nine (년출력)

MM --> 12 (월출력) , MONTH --> DECEMBER (월출력), MON --> DEC

D --> 요일을 숫자로 리턴한다.(일요일은 1, 월요일은 2...)

DD --> 07 (달의 일출력)

DDD --> 200 (연의 일출력)그 해의 총 몇 일째인가를 리턴한다.

DAY --> MONDAY (요일출력) DY-->MON

CC --> 20 (몇 세기인지를 보여준다.)

WW --> 그 해의 몇 번째 주인가를 리턴한다.

W --> 그 달의 몇 번째 주인가를 리턴한다.

 

* Element들을 소문자로 쓰면 소문자로 나오고 대문자로 쓰면 대문자로 출력된다.

HH or HH12 or HH24 / MI(0-59분) / SS(0-59초)

 

* 문자열은 " " 묶어 추가한다 DD " of " MONTH --> 12 of DECEMBER

*숫자 접미어는 숫자를 문자로 표기. TH(4->4TH)/ SP(4->FOUR)/ SPTH or THSP(4->FOURTH)

ddspth : 14-> fothteenth

* / . , : 구두점은 결과에 그대로 출력한다. * 공백, 선행제로를 제거하는 fm요소가 있다.

TO_CHAR(number,'format',[nlsparams]) : number를 format에 맞게 문자열로 변환한다.

- Number Format Elements

9 : 999,999 --> 1,234 $: 부동 달러 기호 $99999 -> $1234

0 : 099999 --> 001234 99.999EEEE -> 1.234E+03 B: 0값을 공백으로

L : L99,999 --> FF1,234 (NLS_CURRENCY에 설정되어있는 값이 L로 보여진다.)

 

TO_NUMBER(char,['format'],[nlsparams]) : 숫자형태의 문자열을 숫자로 변한한다.

TO_DATE(char,['format'],[nlsparams]):날자형태의 문자열을 format에 맞게 날자형식으로 변환 한다.

 

6. NVL Funcion : 값이 null일 때 설정값을 보여준다.

NVL(number_column, 0) : null일 때 0을 보여준다.

NVL(date_column, '01-JAN-95') : null일 때 '01-JAN-95'를 보여준다.

NVL(character_column, 'Empty') : null일 때 'Empty'를 보여준다.

* column Type과 표현식의 type이 반드시 일치해야 한다.

7. DECODE Function : CASE or IF-THEN-ELSE 형식과 비슷하다.

*DECODE(col/expression, search1, result1 [,search2,result2,…] [,default])

F1 (F2 (F3 (col,arg1),arg2),arg3)

 

[4] Displaying Data from Multiple Tables (JOIN)

1. EquiJoin : column1과 column2는 Primary Key와 Foreign Key관계인 경우

SELECT emp.empno, emp.ename, emp.deptno, dept.deptno, dept.loc

FROM emp, dept

WHERE emp.deptno = dept.deptno

 

2. Non-EquiJoin : Join하는 Table 사이에 서로 대응하는 Key가 없는 경우

where절의 Join조건에 '='을 제외한 비교연산자를 사용한다.

SELECT e.ename, e.sal, s.grade

FROM emp e, salgrade s

WHERE e.sal BETWEEN s.losal AND s.hisal

 

3. Outer Join : 서로 = 되지 않는 row 까지도 모두 보여준다.

정보가 없는쪽 컬럼 뒤에 (+)를 붙인다.( =, and 만 사용가능)

SELECT e.ename, d.deptno, d.dname

FROM emp e, dept d

WHERE e.deptno (+) = d.deptno

ORDER BY e.deptno

 

4. Self Join : 같은 Table을 그것이 마치 2개의 Table인 것처럼 Join해서 사용한다.

SELECT worker.ename, manager.ename

FROM emp worker, emp manager

WHERE worker.mgr = manager.empno

 

5. SET OPERATORS

UNION : 중복된 row는 제외하고 보여준다. UNION ALL : 중복된 row까지 모두 보여준다. INTERSECT : A,B의 중복된 row만 보여준다.MINUS : A,B의 중복된 row를 제외한 A row를 보여준다.

 

[5] Aggregating Data Using Group Functions(그룹함수를 사용한 데이터집계)

1. Group Function : 행집합에 적용하여 그룹당 하나의 결과를 생성한다.

AVG([DISTINCT|ALL] n) : 평균값

COUNT({*|[DISTINCT|ALL] expr}) : row수

MAX([DISTINCT|ALL] expr) : 최대값

MIN([DISTINCT|ALL] expr) : 최소값

SUM([DISTINCT|ALL] n) : 합

STDDEV([DISTINCT|ALL] x) : 표준편차

VARIANCE([DISTINCT|ALL] x) : 분산

* count(*)를 제외한 모든 Group Function은 Null을 배제하고 수행한다.

Null을 포함하고 싶다면 NVL함수를 사용한다.

* DISTINCT나 ALL을 쓰지 않으면 Default가 ALL이다.

* AVG,SUM, STDDEV, VARIANCE는 반드시 숫자형이다.

 

2. 어떤 컬럼에 해당하는 데이터별 그룹함수를 사용할 때

SELECT [deptno,] COUNT(ename)

FROM emp --> 이문장은 성립되지 않는다. GROUP BY가 없다.

<추가>

GROUP BY deptno

*일반칼럼과 그룹함수를 같이 쓰면 group by절에 일반칼럼 명시(열 별칭 사용못함)

*GROUP BY 열을 SELECT 절에 포함시키지 않아도 된다

 

3. 그룹함수는 WHERE절에 올수가 없다.

SELECT deptno, AVG(sal)

FROM emp

WHERE AVG(sal) > 2000

GROUP BY deptno

--> 이문장은 성립되지 않는다. WHERE절에 그룹함수가 올 수 없다.

 

SELECT deptno, AVG(sal)

FROM emp

GROUP BY deptno

HAVING AVG(sal) > 2000

 

4. HAVING : 그룹함수를 조건절에 적용시키기 위해서 사용한다.

SELECT job, SUM(sal)

FROM emp

WHERE job NOT LIKE 'SALES%'

GROUP BY job

HAVING SUM(sal) > 5000

ORDER BY SUM(sal)

*절 평가 순서 : ① WHERE -> ② GROUP BY절 -> ③ HAVING절

*그룹함수는 두번까지 중첩될수 있습니다. MAX( AVG(SAL))

 

[6] Subqueries( WHERE 절, HAVING 절, FROM 절 )

1. Subquery 규칙

- 반드시 ()로 묶어야 한다.

- 반드시 비교연산자 오른쪽에 위치해야 한다.

- ORDER BY 절에는 사용할 수 없다.

- 서브쿼리 결과가 한개일때는 Single-Row 비교연산자를 사용해야 한다.

( =, >, >=, <, <=, <> )

- 서브쿼리 결과가 여러개일때는 Multi-Row 연산자를 사용해햐 한다.

 

2. Subquery 사용예1

* 반드시 비교연산자 오른쪽에 써야한다.

SELECT ename FROM emp

WHERE sal > (SELECT sal FROM emp

WHERE empno = 7566)

 

3. Subquery 사용예2

SELECT ename, job

FROM emp

WHERE job = (SELECT job

FROM emp

WHERE empno = 7369)

AND

sal > (SELECT sal

FROM emp

WHERE empno = 7876)

 

4. Subquery 사용예3

SELECT job, AVG(sal)

FROM emp

GROUP BY job

HAVING AVG(sal) > (SELECT MIN(AVG(sal))

FROM emp

GROUP BY job)

 

5. Subquery 사용예4 (Subquery 결과가 여러개가 나올때 비교연산자 사용법)

SELECT empno, ename

FROM emp

WHERE sal IN (SELECT MIN(sal) ( =ANY 와 같음 )

FROM emp

GROUP BY deptno)

 

6. ANY(동의어:SOME)연산자를 사용한 Subquery : 조건중에 한개만 만족하면 OK

SELECT empno, ename, job

FROM emp

WHERE sal < ANY (SELECT sal --> OR

FROM emp

WHERE job = 'CLERK')

AND job <> 'CLERK'

 

7. ALL연산자를 사용한 Subquery : 모든 조건을 만족해야 OK

SELECT empno, ename, job

FROM emp

WHERE sal > ALL (SELECT avg(sal) --> AND

FROM emp

GROUP BY deptno

 

[7] Multiple-Column Subqueries

1. Multiple-Column Subquery (Pairwise Subquery)

: 서브쿼리의 결과가 두개 이상의 컬럼형식으로 나올 때 비교하는 컬럼도 서브쿼리 컬럼

갯수와 형식이 같아야한다.

SELECT ename, deptno, sal, comm

FROM emp

WHERE (sal, NVL(comm,-1)) IN

(SELECT sal, NVL(comm,-1)

FROM emp

WHERE deptno = 30)

 

2. NonPairwise Subquery

SELECT ename, deptno, sal, comm

FROM emp

WHERE sal IN (SELECT sal FROM emp

WHERE deptno = 30)

AND

NVL(comm,-1) IN (SELECT NVL(comm,-1) FROM emp

WHERE deptno = 30)

 

3. Subquery 안에 Null값이 있을 때...

* 매니저가 아닌 사원을 보여주기?

SELECT e.ename

FROM emp e

WHERE e.empno NOT IN

(SELECT m.mgr FROM emp m)

IN은 Null value가 나와도 한개의 조건만 만족하면 OK이지만,

NOT IN은 !=ALL과 같아서 모든조건이 TRUE여만 TRUE인 것이다.

* NOT IN 을 !=ANY 로 바꿔주는게 정답에 가깝다.

 

4. FROM절에 사용되는 Subquery

SELECT a.ename, a.sal, a.deptno, b.salavg

FROM emp a,

(SELECT deptno, avg(sal) salavg

FROM emp

GROUP BY deptno) b

WHERE a.deptno = b.deptno

AND a.sal > b.salavg

 

5. EXISTS : EXISTS 뒤에 나오는 서브쿼리의 결과가 한건이 row라도 있다면 O.K

SELECT dname, deptno

FROM dept

WHERE EXISTS (SELECT * FROM emp

WHERE dept.deptno = emp.deptno)

: 행의 존재유무만 확인

 

[8] Producing Readable Output with SQL*Plus

1. Substitution Variable (치환변수)

& : 변수가 한 번 사용되고 메모리에서 사라진다.

&& : 한 번 입력한 변수는 CLEAR하기 전까지 계속 메모리에 남아있다.

DEFINE variable=value : CHAR 데이터 유형의 사용자 변수를 생성하여 값을 변수에 할당한다.

DEFINE : 현재 DEFINE 된 모든 것들을 보여준다.

DEFINE 변수명 : 지정한 변수명의 DEFINE 사항을 보여준다.

UNDEFINE 변수명 : 지정한 변수명의 DEFINE 상태를 CLEAR한다.

ACCEPT : 변수를 입력하라고 물어보는 Prompt의 Text를 변경할수 있다. USER가

입력하는 값을 HIDE시킬수가 있다. DataType의 Format을 변경할수가 있다.

* SET VERIFY 는 SQL*PLUS 가 치환변수를 값으로 바꾸기 전후의 명령 텍스트 표시를 토글한다.

2. & 치환변수 사용예

SELECT empno, ename, sal, deptno

FROM emp

WHERE empno = &employee_num

 

3. && 치환변수 사용 예 --> 한번만 물어보게 된다.

SELECT empno, ename, job, &&column_name

FROM emp

ORDER BY &column_name

4. 변수 값이 character or date value일때 꼭 single quotation mark로 묶어 줘야 한다.

SELECT ename, deptno, sal*12

FROM emp

WHERE job = '&job_title'

 

5. 종합적인 예제(실행중에 열이름, 표현식, 텍스트를 지정)

--> 어떤 절에나 사용해도 된다.

SELECT empno, ename, job, &column_name

FROM emp

WHERE &condition

ORDER BY &order_column

 

6. ACCEPT 사용예 (file로 만들어서 START시켜야 한다.)

*ACCEPT variable [datatype] [FORMAT format] [PROMPT text] [HIDE]

ACCEPT dept PROMPT 'Provide the department name: '

SELECT *

FROM dept

WHERE dname = UPPER('&dept')

/

Provide the department name: Sales

처리된 결과값...

 

7. 파일로 만들어서 치환변수를 여러개 처리할때 예제

test.sql

SELECT &1 FROM &2

SQL> @test empno emp

==> &1에 empno, &2에 emp가 들어간다.(define)

 

SELECT &2 FROM &4

SQL> @test e empno b emp

==> &1에 e, &2에 empno,&3에 b, &4에 emp가 들어간다.(define)

 

8. SET command 변수

① ARRAY[SIZE] {20 | n} : 데이터 fatch size

② COLSEP {_ | text} : column seperator

③ FEED[BACK] {6 | n | OFF | ON} : n 개이상의 레코드 반환시 레코드 수를 표시

④ HEA[DING] {OFF | ON} : column heading 출력

⑤ LIN[ESIZE] {80 | n} : 가로 80 으로 출력

⑥ PAGES[IZE] {50 | n} : 세로 50 으로 출력

⑦ LONG {80 | n} : long date type의 데이터를 출력할때 80byte까지만 보여주겠다.

⑧ PAU[SE] {OFF | ON | text} : text에 문자열을 넣으면 pause될 때마다 메시지를 보여준다.

⑨ TERM[OUT] {OFF | ON} : 결과를 화면에 보여주겠는가?

⑩ VERIFY {OFF | ON} : 치환변수의 old값과 new값을 보여주면서 결과가 처리된다.

* SHOW {SET command 명} : SET command명의 현재 상태를 보여준다. SQL> SHOW ECHO)

* SHOW ALL : 모든 SET command의 현재 상태를 보여준다.

* SET 상태를 바꿔 주려면... SQL> SET PAUSE ON <-- 이런식으로 하면 된다.

* DEFINE command나 SET command는 자신의 환경파일(login.sql)에 일괄적으로 처리 할수 있다.

* login.sql은 oracle superuser용 환경파일이다.

* SQL Plus command가 다음 줄로 이어질 때는 (-)으로 연결한다.

9. REPORT 출력 예

① SET PAGESIZE 37 --> 세로 37로 출력

② SET LINESIZE 60 --> 가로 60으로 출력

③ SET FEEDBACK OFF

④ TTITLE 'Employee|Report' --> Top Title을 Employee 다음 줄에 Report를 쓰겠다.

⑤ BTITLE 'Confidential' --> Bottom Title을 Confidential로 쓰겠다.

⑥ COLUMN job HEADING 'Job|Category' FORMAT A15 : | 은 text를 두줄로 찍는다.

--> job column Heading을 Job 다음줄에 Category로쓰고 15byte의 자리수로

만들겠다.

형식 : COL[UMN] [{column | alias } [option]]

COL[UMN] [column], COL[UMN] column CLE[AR], CLE[AR] COL[UMN]

 

옵션 : CLE[AR], FOR[MAT] format, HEA[DING] text, JUS[TIFY] {align}, NUL[L] text,

NOPRI[NT]:열을 숨김니다 <> PRI[NT],

TRU[NCATED] : 첫 행 끝에 표시되는 문자열을 잘라버린다.

WRA[PPED] : 문자열의 끝을 다음 행으로 줄바꿈합니다.

⑦ REM ** Insert SELECT statement --> 주석문

*BREAKE 명령?

 

[9] Multipulating Data (DML)

*트랜젝션 : 논리 작업 단위를 형성하는 DML 문 모음으로 구성된다., DDL문(한개),DCL문(한개)

1. INSERT 예제1

: 모든 컬럼에 INSERT할 때는 컬럼명을 쓰지 않아도 되지만 테이블 CREATE시

만들어진 순서대로 입력해야한다.

INSERT INTO dept

VALUES (50,'AAA','BBB')

* NULL 값을 갖는 행 삽입시 열목록에서 열을 생략(암시적)하는 방법과

NULL 키워드를 지정(명시적)하는 방법이 있다.

2. INSERT 예제2

INSERT INTO emp ( empno, ename, job, mgr, hiredate, sal, comm, deptno )

VALUES ( 7196, 'GREEN', 'SALESMAN', 7782, SYSDATE, 2000, NULL, 10 )

 

3. sql 파일로 만들어서 Argument를 받으면서 INSERT 실행하기.

* test.sql 파일

ACCEPT department_id PROMPT 'Please enter the department number: '

ACCEPT department_name PROMPT 'Please enter the department name: '

ACCEPT location PROMPT 'Please enter the location: '

INSERT INTO dept ( deptno, dname, loc )

VALUES (&department_id, '&department_name', '&location' )

 

SQL> @test = START test

Please enter the department number: 90

Please enter the department name: PAYROLL

Please enter the location: HOUSTON

 

1 row created

4. 다른 테이블의 row를 Copy하기(VALUES 절을 사용하지 않는다.)

INSERT INTO managers (id, name, salary, hiredate)

SELECT empno, ename, sal, hiredate

FROM emp

WHERE job = 'MANAGER'

 

5. UPDATE 예제1

UPDATE emp

SET deptno = 20,

sal = 2500,

comm = null

WHERE empno = 7782

 

6. UPDATE 예제2 (Multiple-Column Subquery를 사용한 예)

UPDATE emp

SET (job, deptno) = (SELECT job, deptno

FROM emp

WHERE empno = 7499)

WHERE empno = 7689

 

7. UPDATE 예제3 (다른 테이블에 있는 데이터를 SELECT해서 UPDATE하기)

UPDATE employee

SET deptno = (SELECT deptno FROM emp WHERE empno = 7788)

WHERE job = (SELECT job FROM emp WHERE empno = 7788)

 

8. DELETE 예제1 (조건에 맞는 데이터 지우기)

DELETE FROM dapartment

WHERE dname = 'DEVELOPMENT'

 

9. DELETE 예제2 (한 테이블의 전체 데이터 삭제)

DELETE FROM department

 

10. DELETE 규칙

* FROM은 옵션이므로 사용하지 않아도 된다.(예: DELETE department)

* Primary Key, Forien Key 관계가 설정되어 있는 데이터를 DELETE할때는 문제가 발생 할 수 있다.

 

11. COMMIT

* 변경된 데이터를 Fix시킨다.

* 이전상태의 데이터는 모두 잃게 된다.

* 모든 User가 결과를 볼수 있다.

* LOCK이 풀린다.

* 모든 SavePoint들이 clear된다.

* 자동 커밋 : DDL, DCL, 정상 종료시

12. ROLLBACK

* 변경된 데이터를 undo 시킨다.

* Transaction 전단계의 데이터로 돌아간다.

* Lock이 풀린다.

* 자동 롤백 : 비정상 종료, 시스템 장애

13. SAVEPOINT 예제

* SAVEPOINT : Transaction이 일어난 곳에 Marking을 할 수가 있다.

* 오라클은 자동적으로 눈에 안보이는 savepoint를 찍어 놓는다.

SQL> UPDATE.....

SQL> SAVEPOINT update_done ;

Savepoint created.

SQL> INSERT.....

SQL> ROLLBACK TO update_done ;

Rollback complete.

 

14. TABLE LOCK 예제 (DBA가 임으로 TABLE에 LOCK을 걸 수가 있다.)