영어청취와 사업아이디어, 컴퓨터무료강의가 있는 곳

PL/SQL(Procedure Language/SQL)
PL/SQL은 SQL을 확장한 프로그램 언어이다. 이는 데이터 조작 및 조회문장을 포함한
블록구조를 가지며 절차적인 단위로 시행되어진다.

이론적인 것보다는 실제 예제를 먼저 다루면
EMP테이블의 복사본인 EMP_BACK 테이블로 이해를 해보자.

사원(EMP)테이블과 급여등급(SALGRADE)테이블을 이용하여 아래와 같은 결과를 포함하는

EMP_GRADE 테이블을 생성하시오.

          ....
         14개 행이 선택되었습니다.

위와 같은 결과를 보여주는 EMP_GRADE 테이블을 SELECT문을 이용해서 생성하려면

생성방법
CREATE TABLE EMP_GRADE
AS
SELECT문

위와 같이 EMP테이블과 SALGRADE간의 NONEQUL JOIN을 이용해서 결과값이
나오는지 확인한 후 위의 생성방법의 형식에 따라서 구해주면 된다.

GROUP BY 와 HAVING

사원 테이블에서 부서별 급여의 합계가 9000을 넘는 경우를 조사할 경우

서브쿼리(SUBQUERY)

여러 번의 쿼리문을 수행해서 얻을 수 있는 결과를 하나의 중첩된 쿼리문으로 얻을 수
있도록 한다.
서브쿼리문은 부분 실행이 가능하므로 원하는 값이 나오는지
확인한 후 WHERE절 이하의 표현식 연산자와 비교해보고 검색하는 것이 좋을 것이다.

형식

SELECT 선택열들

FROM 테이블

WHERE 표현식 연산자

        (SELECT 선택열 FROM 테이블) ;

예 1) 사원 테이블에서 SCOTT보다 많은 급여를 받는 사원의 이름과 JOB, 급여는?

예 2) 사원 테이블에서 부서별 최소 급여를 받는 사원의 이름, 급여, 부서번호를 검색?

GET

외부파일의 내용을 버퍼로 읽음

형식
GET 파일

ED

버퍼에 저장되는 내용을 수정하기 위해 지정된 편집기를 실행하여 버퍼의 내용을

읽거나 외부파일의 내용을 수정하기 위해 불러옴

형식

ED

ED 파일

START, RUN
외부파일 실행하기

예제에 사용된 APPEND.SQL

실행결과

자주 사용하다보면 의외로 유용한 부분이 많으리라 생각합니다.
이상..SQLPLUS에 대해서 마칩니다.

SPOOL 명령

사용방법

SQL>SPOOL 파일명

SQL처리

SQL>SPOOL OFF

저장된 파일 보기

SAVE 명령

버퍼의 내용을 외부 파일에 저장함.

사용방법

SQL>SAVE 파일명 [REPACE | APPEND]

여기서 REPLACE는 위의 파일명과 똑 같은 파일이 기존에 존재하면 교체하고

APPEND는 기존의 파일이 존재하면 기존의 파일 뒤에 추가한다.

SAVE에서 REPLACE 이용

   
   원래 생성된 파일
  

   RELACE를 수행하고 난 후 파일    

SAVE에서 APPEND 이용

원래 생성된 파일

APPEND 후 변경된 파일

EXIT 명령

SQLPLUS 사용을 종료한다.

사용방법

SQL>EXIT

정리하자면 SPOOL은 SQL문과 그 실행결과까지를 저장하지만 SAVE는 단지 SQL문만을

저장함을 알 수 있습니다.

다음에는 GET, ED, START, … 에 대해서 알아보도록 하지요.
아참.. 위에서 SQL>cl buff; 이라는 부분이 있는데 이것은 버퍼내용을 비우라는 의미입니다.

조인(JOIN)

하나 이상의 테이블을 연결하여 데이터를 검색하는 방법

형식

SELECT 테이블1.칼럼1, 테이블1.칼럼2, …, [테이블2.칼럼1, 테이블2.칼럼2, …]

FROM 테이블1, 테이블2

WHERE 테이블1.공통칼럼=테이블2.공통칼럼 ;

1. Equi-JOIN(동등 조인) or INNER JOIN(내부 조인)

일반적으로 JOIN하면 INNER JOIN을 지칭할 정도로 많이 사용한다.

예제) 급여가 2000 이상인 사원에 대해 사원 번호, 사원이름, 업무, 부서명, 위치를 검색

SELECT a.EMPNO, a.ENAME, a.JOB, a.SAL, b.DNAME, b.LOC

FROM EMP a, DEPT b

WHERE a.DEPTNO=b.DEPTNO AND a.SAL>=2000;

SELECT a.EMPNO, a.ENAME, a.JOB, a.SAL, b.DNAME, b.LOC

FROM EMP a INNER JOIN DEPT b

ON a.DEPTNO=b.DEPTNO AND a.SAL>=2000;

결과

2. Non-Equi JOIN(비동등 조인)
테이블의 어떤 칼럼도 조인할 테이블의 칼럼과 일치하지 않을 때 사용.

따라서 조인 조건 대신 BETWEEN  AND, IS NULL, IS NOT NULL, IN, NOT IN

같은 연산자를 사용

예제) 사원 테이블(EMP) 내의 급여가 급여등급테이블(SALGRADE)의 하한값과 상한값 사이에 포함되는 경우 사원번호, 사원이름, 사원급여, 사원등급이 표시되도록 하시오.

SELECT a.EMPNO 사원번호, a.ENAME 사원이름, a.SAL 사원급여,
b.GRADE 사원등급

FROM EMP a, SALGRADE b

WHERE a.SAL BETWEEN b.LOSAL AND b.HISAL ;

결과

3. SELF JOIN(자기 조인)

일반적인 조인은 서로 다른 테이블을 이용하여 처리하였으나 SELF JOIN은 자기 자신의 테이블을 별칭을 이용해서 조인하여 원하는 결과값을 검색하는 조인의 한 유형이다.

예제) 사원번호, 사원이름, 관리자이름, 관리자번호가 나타나도록 검색하시오.

SELECT a.EMPNO 사원번호, a.ENAME 사원이름, b.ENAME 관리자이름,

b.EMPNO 관리자번호

FROM EMP a, EMP b

WHERE a.MGR=b.EMPNO;

결과

4. OUTER JOIN(외부 조인)

동등조인과 비동등 조인의 경우에 조인 조건이 만족하는 데이터만 검색이 되므로

위의 검색결과를 참고하면 알 수 있듯이 DEPT 테이블에 있는 DEPTNO=40에 해당하는

OPERATIONS 부서는 절대로 검색되지 않는다.

따라서 이러한 문제를 해결하기 위해서 OUTER JOIN을 사용한다.

유형

LEFT OUT JOIN, RIGHT OUTER JOIN, FULL OUTER JOIN이 있는데 FULL OUTER JOIN은 LEFT, RIGHT JOIN을 합한 형태이다.

(+) 기호를 사용하기도 하는데 (+) 기호는 일치되지 않는 행이 존재하지 않은 쪽에 붙인다.

SELECT a.EMPNO, a.ENAME, b.DNAME

FROM EMP a, DEPT b

WHERE a.DEPTNO (+)= b.DEPTNO;

SELECT a.EMPNO, a.ENAME, b.DNAME

FROM EMP a RIGHT OUTER JOIN  DEPT b

ON a.DEPTNO = b.DEPTNO;

결과

참고로 FULL OUTER JOIN은 (+)기호를 사용해서 구할 수 없고 FULL OUTER JOIN 형식으로만 가능하다.

SELECT a.EMPNO, a.ENAME, b.DNAME

FROM EMP a, DEPT b

WHERE a.DEPTNO(+)= b.DEPTNO(+); -> 에러 발생

SELECT a.EMPNO, a.ENAME, b.DNAME

FROM EMP a FULL OUTER JOIN  DEPT b

ON a.DEPTNO = b.DEPTNO;

5. Cartesian Product(카티션 곱) or Cross JOIN(크로스 조인)

쿼리문

SELECT EMPNO, ENAME, DNAME

FROM DEPT

CROSS JOIN EMP;

결과

6. Natural JOIN(자연 조인)

자연 조인은 Equi-JOIN과 동일하지만 기술하는 방법이 조금 다른데,

JOIN 조건을 표시하지 않고 FROM 뒤의 테이블 내에서 동일한 이름을

갖는 칼럼들을 찾아서 동등한 조건으로 JOIN해줌.

쿼리문

SELECT EMPNO, ENAME, DNAME

FROM DEPT

NATURAL JOIN EMP;

결과

이상으로 JOIN(조인)에 대해서 간략히 알아보았습니다.
그럼 이만..

TO_CHAR()

TO_CHAR(NUMBER, ‘포맷형식’)

예) EMP 테이블에서 SAL 값을 표시하면 앞에 화폐 표시가 되지 않는데.

아래와 같이 쿼리를 작성하여 실행하면

SELECT ENAME, TO_CHAR(SAL, 'L99,999.0') FROM EMP;

다음과 같이 된다.

CASE

CASE 함수는 SQL 문장 내에서 IF … THEN … ELSE … END 와 같은 제어문을 쓸 수

있도록 해준다.

형식 

CASE  표현식1 WHEN 비교 표현식 1 THEN 리턴값1

                       WHEN 비교 표현식 2 THEN 리턴값2

                       WHEN 비교 표현식 3 THEN 리턴값3

                        ELSE  리턴값4

END 별칭

예) EMP 테이블에서 JOB에 대해 영어로 되어 있는 명칭을 JOB이 ANALYST, CLEAK은 한글로 나머지는 ‘기타’로 하고 ENAME, JOB를 기본으로 처리 결과를 출력하도록 하시요.

SELECT ENAME, JOB,

       CASE JOB WHEN 'ANALYST' THEN '분석가'

                WHEN 'CLERK'   THEN '점원'

                ELSE '기타 직종' 

END "직종"

FROM EMP;

ROUND() 반올림 함수

SELECT ROUND(45.275,1) FROM DUAL;
--결과 : 45.3

  1    2    3    4   .   5   6   7
 -4  -3   -2  -1   0   1   2   3
ROUND 함수는 양수 자리의 반올림은 그 자리에서 소수점 아래의 반올림은
그 뒷자리에서 반올림을 한다.

여기서 FROM 다음 DUAL이라는 테이블이 궁금할 것 같은데요.
DUAL 테이블은 조회를 하기 위한 형식적인 테이블임.

TRUNC() 함수
SELECT TRUNC(45.245,2) FROM DUAL;
-- 결과 : 45.24

SIGN() 함수
양수 1, 음수 -1, 0은 0을 반환한다.
SELECT SIGN(100), SIGN(0), SIGN(-100) FROM DUAL;
결과 : 순서대로 1 0 -1

SUBSTR()
문자 추출함수
SELECT SUBSTR('ABCDEFG', 1,3) FROM DUAL; 결과 : ABC
SELECT SUBSTR('ABCDEFG', 3,2) FROM DUAL; 결과 : CD
SELECT SUBSTR('ABCDEFG', -3,2) FROM DUAL; 결과 : EF
SELECT SUBSTR('ABCDEFG', -3) FROM DUAL; 결과 : EFG
SELECT SUBSTR('ABCDEFG', -5) FROM DUAL; 결과 : CDEFG

INSTR()
문자의 위치값을 반환
SELECT INSTR('ABCDEFG', 'C') FROM DUAL; 결과 : 3

RTRIM(), LTRIM()
좌, 우측에서 특정문자를 제거한다.
SELECT RTRIM('ABBBBB', 'B') FROM DUAL; 결과 : A
SELECT RTRIM('A     ', ' ') FROM DUAL; 결과 : A
SELECT LTRIM('BBBABB', 'B') FROM DUAL; 결과 : ABB

변환 함수
TO_CHAR(숫자나 날짜를 문자로 변환)

MM : 달수(10)
MON : 3문자  달이름(MAR)
MONTH : 달의 완벽한 이름(MARCH)
DD : 달의 날짜수(22)
D: 주의 일수(2)
DY: 3문자 요일이름(MON)
DAY: 월의 완벽한 이름(MONTH)
YYYY: 4문자 년도(2007)
YY : 2문자 년도(07)
RM : 로마식 달수(IX)

SELECT SYSDATE, TO_CHAR(SYSDATE,'D') FROM DUAL;  결과 : 5
SELECT SYSDATE, TO_CHAR(SYSDATE,'DY') FROM DUAL; 결과 : THU
SELECT SYSDATE, TO_CHAR(SYSDATE,'RM') FROM DUAL; 결과 : IV
SELECT SYSDATE, TO_CHAR(SYSDATE,'YYYY') FROM DUAL; 결과 : 2008
SELECT SYSDATE, TO_CHAR(SYSDATE,'YY') FROM DUAL; 결과 : 08
SELECT SYSDATE, TO_CHAR(SYSDATE,'DD') FROM DUAL; 결과 : 17
SELECT SYSDATE, TO_CHAR(SYSDATE,'YY DD') FROM DUAL; 결과 : 08 17

시간 형식 요소
HH, HH12 : 12시간
HH24 : 24시간
MI:분
SS : 초
SSSSS: 초의 자리수
AM, PM : 오전, 오후
A.M., P.M. : 오전, 오후

SELECT  TO_CHAR(SYSDATE,'HH24:MI:SSSS') FROM DUAL; 결과 : 14:26:1717
SELECT  TO_CHAR(SYSDATE,'AMHH:MI') FROM DUAL; 결과 : PM02:26
SELECT  TO_CHAR(SYSDATE,'AM HH:MI') FROM DUAL; 결과 : PM 02:26
SELECT  TO_CHAR(SYSDATE,'P.M. HH:MI') FROM DUAL; 결과 : P.M. 02:26

SP : 서수
TH : 서수
SELECT SYSDATE, TO_CHAR(SYSDATE, 'DD') FROM DUAL; 결과 : 17
SELECT SYSDATE, TO_CHAR(SYSDATE, 'DDSP') FROM DUAL; 결과 : SEVENTEEN
SELECT SYSDATE, TO_CHAR(SYSDATE, 'DDTH') FROM DUAL; 결과 : 17TH
SELECT SYSDATE, TO_CHAR(SYSDATE, 'DDSPTH') FROM DUAL; 결과 : SEVENTEEN

REPLACE()
주어진 문자열을 특정 문자열로 교체한다.
SELECT * FROM EMP;