ICT블로그

※ Oracle PL/SQL Programming 6판 기준입니다. (O'REILLY)

#Creating and Editing Source Code (22p)

What is unique about PL/SQL is the fact the the source code for stored programs must be loaded into the database before it can be compiled and executed.

--> PL/SQL은 다른 프로그램(C 등)과 달리, 데이터 베이스 안에서 compile되고 구동되어야 하기 때문에 일반 java program 등과 달리 version 관리 등에 고민해야 할 부분들이 발생한다.

#Starting Up SQL*Plus (24p)

3 ways to connect to a database with SQL*Plus

(1)

>sqlplus

Enter user-name : system

Enter password : ******

connect to (...)

SQL>

(2)

> sqlplus system/******

※do not recommend because of possibility of the password being leaked 

--> 어깨너머로 훔쳐 볼 수 도 있고, UNIX등 multi user 환경에서는 실제로 OS user가 수행한 명령어를 확인할 수 있다. 

(3)
> sqlplus /nolog

SQL> connect system/******

SQL> Connected.

--> nolog 구문을 이용하여 접속은 후에 맺는 식으로 패스워드 유출을 막을 수 있음.

#Running A PL/SQL Program (27p)

예제1)

begin

  dbms_output.put_line('Hey look, ma!');

end;

/

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

PL/SQL 프로시저가 성공적으로 완료되었습니다.

예제2)

set serveroutput on

begin

  dbms_output.put_line('Hey look, ma!');

end;

/         

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

PL/SQL 프로시저가 성공적으로 완료되었습니다.

Hey look, ma!

※기억해야 할 점

1. set serveroutput on 옵션을 켜야 PL/SQL 결과가 출력된다.

2. /(slash)를 반드시 입력해야 한다.

**    /(slash)의 역할 및 특징  **

To tell SQL*Plus that you're done entering a PL/SQL statement, you must usually include a trailing slash

1. sql*plus 상에서 / 는 '가장 최근에 입력된 문장을 수행하라'는 명령어이다. (PL/SQL, SQL 상관없이)

2. The slash is a command unique to SQL*Plus; it is not part of the PL/SQL language, nor is it part of SQL.

3. It must appear on a line by itself; no other commands can be included on the line.

4. Oracle 9i 이전의 버전은 / 앞, 뒤의 공백유무에 따라 수행이 안될수도 있다. (9i 이후버전은 앞뒤 공백 모두 상관 없다.)

예제3) BEGIN, END, / (slash) 입력없이 수행하기

execute dbms_output.put_line('Hey look, Ma!')

exec dbms_output.put_line('Hey look, Ma!!!!')

EXECUTE란 command를 통해 Begin, end, /를 모두 생략가능하다. 

1) semicolon (;)은 붙여도 되고 안붙여도 된다.

2) execute의 축약형으로 exec를 사용가능하다.

※ DECODE 함수의 특징

- 프로그래밍 언어의 IF문을 SQL, PL/SQL 내에서 구현하기 위해 만들어진 오라클 함수.

- FROM 절을 제외한 나머지 절 전체(SELECT, GROUP BY, WHERE 등등)에 사용가능. --> 동적으로 FROM절의 TABLE을 선택할 수는 없다는 의미.

- 중복된 DECODE사용이 가능

- 단독으로 쓰이기보단 SIGN, LEAST, GREATEST 등의 함수, SUM, MAX, MIN 등의 GROUP 함수와 많이 쓰인다.

- DECODE는 최소 3개의 인수만 받을 수 있으며, 이 경우 내부적으로 NULL을 출력하는 ELSE로 처리한다.

따라서 아래 i)번과 ii)번은 같다.

 예) 

i) IF A = B THEN

   RETURN 'T';

END IF;

==> DECODE(A,B,'T')

ii) IF A = B THEN

    RETURN 'T';

ELSE

    NULL

END IF;

==> DECODE(A,B,'T',NULL)

- 단순 상수뿐만 아니라 컬럼 간의 비교에도 유용하게 쓰일 수 있다.

예제) decode를 이용한 암호 생성

select to_char(sysdate,'DD')||decode(to_char(sysdate,'DAY'),'월요일','01','화요일','11','수요일','21','목요일','31','금요일','41','토요일','51','일요일','61') SEC_KEY  from dual;

예제) decode가 sign과 함께 쓰이는 경우

   ==> decode는 부등호를 이용한 대소비교를 할 수 없기 때문에, 필요한 경우 sign, least 등의 함수로 대소비교 처리가 가능하게끔 해준다.

 select lec_id,lec_time,lec_point,

        decode(sign(lec_time-lec_point),0,'일반과목',1,'실험과목',-1,'기타과목') as ty from lecture;

select lec_id, lec_time, lec_point, decode(sign(lec_time-lec_point),1,'실험과목','일반과목') from lecture;

select lec_id,lec_time,lec_point, decode(least(lec_time,lec_point),lec_time,'일반과목','실험과목') from lecture;

-- 중첩된 DECODE의 사용 (중첩된 IF)

-- 예제: DECODE(A,B,DECODE(A,C,'T','F'),'F')

desc lecture;

select lec_id, lec_time, lec_point from lecture;

select lec_id, lec_time, lec_point, decode(lec_time,lec_point,decode(lec_time,3,'중요과목')) from lecture;

-- 연습문제 Q/A : 04-2

질문: TEMP의 행을 3개씩 묶어 하나의 번호를 부여하고, 해당 ROWNUM을 3으로 나눈 나머지가 1, 2, 0 인 경우로 나누어 컬럼의 위치를 정해주는 SQL을 만들어라.

select rownum, ceil(rownum/3) "no+", decode(mod(rownum,3),1,'A',2,'B',0,'C') as G, emp_id, emp_name from temp;

select ceil(rownum/3) COL1, decode(mod(rownum,3),1,emp_id) COL2, decode(mod(rownum,3),1,emp_name) COL3, decode(mod(rownum,3),2,emp_id) COL4

, decode(mod(rownum,3),2,emp_name) COL5, decode(mod(rownum,3),0,emp_id) COL7, decode(mod(rownum,3),0,emp_name) COL8

from temp;

-- 연습문제 Q/A : 04-3

(발전형) TEMP의 자료를 이용하여 한 행에 사번, 성명을 3명씩 (총 6개 컬럼) 보여주는 SQL을 작성하라.

select emp_id, emp_name from temp;

select ceil(rownum/3) COL1,

        decode(mod(rownum,3),1,emp_id) COL2,

        decode(mod(rownum,3),1,emp_name) COL3, 

        decode(mod(rownum,3),2,emp_id) COL4, 

        decode(mod(rownum,3),2,emp_name) COL5, 

        decode(mod(rownum,3),0,emp_id) COL7, 

        decode(mod(rownum,3),0,emp_name) COL8

from temp;

==>

select ceil(rownum/3) COL1,

        max(decode(mod(rownum,3),1,emp_id)) COL2,

        max(decode(mod(rownum,3),1,emp_name)) COL3, 

        max(decode(mod(rownum,3),2,emp_id)) COL4, 

        max(decode(mod(rownum,3),2,emp_name)) COL5, 

        max(decode(mod(rownum,3),0,emp_id)) COL7, 

        max(decode(mod(rownum,3),0,emp_name)) COL8

from temp

where emp_id>0

group by ceil(rownum/3);

- group by 절을 이용해 3명씩 그룹을 지어준다.

1-1 ROWNUM

※ rownum의 속성에 대하여

-- rownum에 대해 알아 두어야 할 사실

첫째. 정의: rownum은 where절 (where절 없으면 from절)에 의해 추출된 DATA SET에 row 단위로 붙는 순번이다.

둘째. where절에 rownum을 조건으로 줄 때에는 항상 다른 조건을 모두 만족시킨 결과에 대해 조건이 반영된다.

셋째, order by 절을 사용할 때에는 우선 rownum이 부여되고 난 후 해당 결과 집합에 대해 sort를 하게 된다.

   (순서) rownum --> order by

① 단순 조회 시

 select rownum, emp_id, emp_name

  from temp

 where lev = '수습';

1 20000101 이태백

2 20000102 김설악

3 20000203 최오대

4 20000334 박지리

5 20000305 정북악

6 20006106 유도봉

7 20000407 윤주왕

8 20000308 강월악

9 20000119 장금강

10 20000210 나한라

--> 별도 정렬 없이 temp 자료 중에서 "lev=수습"인 값을 보여줌

② 단순 조회 + 조건 추가 (where emp_id >0) 

 select rownum, emp_id, emp_name

  from temp

 where emp_id >0

 and lev = '수습';

1 20000101 이태백

2 20000102 김설악

3 20000119 장금강

4 20000203 최오대

5 20000210 나한라

6 20000305 정북악

7 20000308 강월악

8 20000334 박지리

9 20000407 윤주왕

10 20006106 유도봉

where emp_id >0 조건이 추가되면서, 별도의 order by 절 없이도 emp_id 순으로 ascending 하여 자료가 출력됨.

③ rownum을 이용한 필터링 (and rownum < 5)

 select rownum, emp_id, emp_name

  from temp

 where emp_id >0

 and lev = '수습'

 and rownum < 5;

1 20000101 이태백

2 20000102 김설악

3 20000119 장금강

4 20000203 최오대

②번에서 출력된 자료에 and rownum < 5 라는 조건을 통해 emp_id 순으로 상위 4개 값만 출력(필터링)된 것을 알 수 있다.

!!주의!! rownum 조건을 이용한 필터링 시에는 <, <= 두 개 연산자 외에는 결과 값이 출력되지 않는다

*예외적으로 1과 비교할 때는 =(같다)로 비교가능하다.

④ rownum을 이용한 필터링 + order by 구문 추가

 select rownum, emp_id, emp_name

  from temp

 where emp_id >0

 and lev = '수습'

 and rownum < 5

 order by emp_name;

2 20000102 김설악

1 20000101 이태백

3 20000119 장금강

4 20000203 최오대

③번에 order by emp_name 절을 추가하였다. 이때 첫번째, ③번의 결과집합은 그대로 가져감. 둘째, ③번에서 확인했던 rownum은 바뀌지 않음을 알 수 있다.

즉, 'and rownum < 5' 구문으로 필터링 시 각 레코드에 대해 rownum 번호가 부여되며, 이후 order by를 이용한 sort가 이뤄졌단 사실을 알 수 있다.

연습문제 Q/A : 04-1 ROWNUM의 이용

 --> TEMP 테이블의 자료를 이용하여 SELECT 결과를 3개행씩 묶어 하나의 번호를 부여하는 SQL을 만들어보자. ROWNUM, 부여된번호, EMP_ID, EMP_NAME을 보여주면 된다.

나의답변)

 select rownum, trunc((rownum-1)/3)+1 as "부여된번호", emp_id, emp_name

 from temp;

모범답안)

 select rownum, ceil(rownum/3) as "부여된번호", emp_id, emp_name from temp;

* ceil : 지정된 값 이상의 가장 작은 정수를 리턴하는 함수

1-2 ROWID