Oracle PL/SQL문 강좌(5)

[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을 걸 수가 있다.)