오라클 정리

1. SQL 장단점

장점 : 비절차적 언어(문자열로 원하는 결과 획득)

단점 : 반복 조건, 여러개의 문장을 동시 수행 불가

2. DML

데이터를 제어(데이터 조작언어)

- SELECT : 데이터 검색

SELECT 컬럼1, 컬럼2(컬럼리스트)

FROM 테이블명

[

WHERE (컬럼명, 연산자, 데이터값)

GROUP BY 컬럼명

HAVING 조건절

ORDER BY 컬럼 (ASC/DESC)

]

JOIN (테이블과 테이블의 연결

- INNER JOIN 일반적으로 가장 많이 사용)

단점

연산에 맞는 데이터 가져옴

NULL값일경우 연산처리 불가

NULL값일경우 데이터 호출 불가

EQUI JOIN(=), 자연 JOIN, JOIN~USING

NON EQUI JOIN(-아닌 다른 연산자 사용시)

-OUTER JOIN (NULL값을 포함해서 값가져옴)

LEFT OUTER JOIN

WHERE A.컬럼=B.컬럼(+)

RIGHT OUTER JOIN

WHERE A.컬럼(+)=B.컬럼

SUBQUERY(쿼리와 쿼리의 연결)

쿼리문장 여러개를 한개의 쿼리로 처리

메인쿼리 (서브쿼리)

서브쿼리에서 실행된 결과값을 메인쿼리가 받아서 실행

1) 단일행

2) 다중행

IN(포함되어있는 모든것)

ANY(결과값중 가장 작은값)

ALL(결과값중 가장 큰값)

내장함수

종류	함수	내용
문자함수	UPPER()	대문자로 변환
	LOWER()	소문자로 변환
	SUBSTR()	문자 분해
	TRIM()	원하는 좌우 데이터 삭제
숫자함수	ROUND()	반올림
	TRUNC()	버림
	MOD()	나머지
날짜함수	SYSDATE	날자와 시간까지 저장, 시스템의 시간을 읽어옴
	MONTHS_BETWEEN	기간(퇴직금, 호봉)
기타	NVL()	NULL값을 다른값으로 변환
변환함수	TO_CHAR()	숫자, 날짜를 문자열로 변환
집합함수	COUNT()	ROW의 개수
	MAX()	가장 큰값
	SUM()	합을 구함
	AVG()	평균을 구함

연산자

종류	연산자	내용
산술연산자	+	덧셈
	-	뺄셈
	*	곱셈
	/	나눗셈(정수/정수 = 실수)
논리연산자	and	A이면서 B인경우(양쪽 조건이 참)
	or	A이거나 B인경우(어느 한쪽이 참)
부정	=!	논리부정, ~가 아님
	<>
	not
NULL	is not null	NULL값이 아님
	is null	NULL값임
기간,사이	BETWEEN ~ AND	A부터 B까지의 기간
비교 연산자	=	같음
	!=	같지 않음
	>	A가 큼
	<	B가 큼
	>=	A가 크거나 같음
	<=	B가 크거나 같음
LIKE	%, _	유자 문자열 검색

- INSERT : 데이터 추가

INSERT INTO 테이블명 VALUES(...)

- UPDATE : 데이터 수정

UPDATE 테이블명 SET 컬럼명= 값

- DELETE : 데이터 삭제

DELETE FROM 테이블명 WHERE 조건절

3. DDL (TABLE, VIEW, SEQUENCE, PROCEDURE, FUNCTION, PACKAGE)

제약조건

제약조건	내용
PRIMARY KEY	기본키 (UNIQUE + NOT NULL)
UNIQUE	중복이 없이 데이터 입력(유일키)
NOT NULL	NULL을 허용하지 않음
CHECK	지정된 값만 입력
DEFAULT	값이 없는 경우 지정된 값 처리
FOREIGN KEY	참조키(외래키)

- CREATE : 생성

TABLE 생성시(컬럼레벨,table레벨)

1) 직접 생성

                  데이터형

                       - 문자 : CHAR,VARCHAR2,CLOB

                       - 숫자 : NUMBER

                       - 바이너리 : BFILE,BLOB

                       - 날짜 : DATE,TIMESTAMP

                   제약조건 : 데이터의 무결점

                       - NOT NULL

                       - UNIQUE 

                       - PRIMARY KEY (NOT NULL+UNIQUE)

                       - FOREIGN KEY

                       - CHECK

                       - DEFAULT

2) 복사해서 생성(서브쿼리)

CREATE TABLE table_name 

AS

SELECT * FROM EMP;

- DROP : 삭제

DROP : 삭제할 때 ( TRUNCATE )

- ALTER

1)column단위

column추가 ADD

ALTER TABLE table_name ADD 컬럼명 데이터형

column삭제 DROP

ALTER TABLE table_name DROP column 컬럼명

column수정 MODIFY

ALTER TABLE table_name MODIFY(컬럼명 데이터형)

단.. ADD,MODIFY 의 경우 NOT NULL(제약조건을 수정,추가)

- SEQUENCE 생성(자동증가번호)

START WITH : 시작숫자

INCREMENT BY : 증가

NOCACHE : 메모리에 저장값

NOCYCLE : 계속 증가값

- VIEW 

한개이상의 테이블을 근거한 가상테이블

= 복합한 쿼리를 단순한 쿼리를 만드는 과정

= 데이터 보호(보안)

= 검색시에 많이 사용되는 부분

= 단순뷰(테이블 한개) : SELECT,UPDATE,DELETE

= 복합뷰(테이블 한개 이상) : SELECT 

형식)

CREATE VIEW view_name 

AS

서브쿼리

4. DCL

GRANT : 권한 부여

REVOKE : 권한 해제

5. TCL

정상 처리 : commit

비정상 처리 : rollback

6. PL/SQL (기능처리를 위한 문법사항)

쿼리문장을 반복,조건에 따라 쿼리문장 변경(제어문)

형식

선언부: 변수선언

DECLARE

vempno NUMBER(4);

vename VARCHAR2(10);

구현부: 쿼리문장 제어

BEGIN

쿼리문장

END;

/ -> 문장의 종료

예외처리부 : 실행과정중 에러발생시 에러 처리

ex)

DECLARE

vempno NUMBER(4);

vename VARCHAR2(20);

BEGIN

SELECT empno, ename INTO vempno, vename FROM emp WHERE empno=7788;

dbms_output.put_line('****실행결과****');

dbms_output.put_line('사번 :'||vempno);

dbms_output.put_line('이름 :'||vename);

END;

/

오라클 명령시

SET SERVEROUPPUT ON

@test

변수 대입법

-변수 직접 대입

ex)

DECLARE

vempno NUMBER(4);

vename VARCHAR2(20);

BEGIN

SELECT empno, ename INTO vempno, vename FROM emp 

WHERE empno=7788;

dbms_output.put_line('****실행결과****');

dbms_output.put_line('사번 :'||vempno);

dbms_output.put_line('이름 :'||vename);

END;

/

-테이블에 존재하는 컬럼의 실제 데이터형 대입

vempno NUMBER(4);

vempno emp.empno%TYPE;

vename emp.ename%TYPE;

ex)

DECLARE

vename emp.ename%TYPE;

vjob emp.job%TYPE;

vsal emp.sal%TYPE;

BEGIN

SELECT ename, job, sal INTO vename, vjob, vsal

FROM emp

WHERE empno=7788;

dbms_output.put_line('****결과****');

dbms_output.put_line('이름 :'||vename);

dbms_output.put_line('직위 :'||vjob);

dbms_output.put_line('급여 :'||vsal);

END;

/

-ROW단위로 대입(C언어의 구조체)

vemp emp$ROWTYPE

emp 테이블이 가지고 있는 모든 데이터형 첨부

vemp(empno, ename...)

ex)

DECLARE

vemp emp%ROWTYPE;

BEGIN

SELECT * INTO vemp FROM emp WHERE empno=7788;

dbms_output.put_line('****결과****');

dbms_output.put_line('이름 :'||vemp.ename);

dbms_output.put_line('직위 :'||vemp.job);

dbms_output.put_line('급여 :'||vemp.deptno);

END;

/

-입력값 받아오기

ACCEPT 받을 변수 PROMPT '입력 안내 '

DECLARE 에서 넣을 변수 NUMBER(4) := &입력받은 변수;

:= &로 입력받은 변수의 주소값을 넘김

정수값만 가능

조건문

단일 IF(IF-THEN-END IF)

DECLARE

vempno emp.empno%TYPE;

vename emp.ename%TYPE;

vdeptno emp.deptno%TYPE;

vdname VARCHAR2(20) := NULL;

BEGIN

SELECT empno, ename, deptno INTO vempno, vename, vdeptno FROM emp WHERE ename='KING';

IF(vdeptno=10) THEN

vdname:='영업부';

END IF;

IF(vdeptno=20) THEN

vdname:='기획부';

END IF;

IF(vdeptno=30) THEN

vdname:='총무부';

END IF;

IF(vdeptno=40) THEN

vdname:='자재부';

END IF;

dbms_output.put_line('****결과값****');

dbms_output.put_line('사번 : '||vempno);

dbms_output.put_line('이름 : '||vename);

dbms_output.put_line('부서번호 : '||vdeptno);

dbms_output.put_line('부서명 : '||vdname);

END;

/

다중 IF(IF-THEN-ELSEIF-END IF)

DECLARE

vempno emp.empno%TYPE;

vename emp.ename%TYPE;

vdeptno emp.deptno%TYPE;

vdname VARCHAR2(20) := NULL;

BEGIN

SELECT empno, ename, deptno INTO vempno, vename, vdeptno FROM emp WHERE ename='KING';

IF(vdeptno=10) THEN

vdname:='영업부';

ELSIF(vdeptno=20) THEN

vdname:='기획부';

ELSIF(vdeptno=30) THEN

vdname:='총무부';

ELSIF(vdeptno=40) THEN

vdname:='자재부';

END IF;

dbms_output.put_line('****결과값****');

dbms_output.put_line('사번 : '||vempno);

dbms_output.put_line('이름 : '||vename);

dbms_output.put_line('부서번호 : '||vdeptno);

dbms_output.put_line('부서명 : '||vdname);

END;

/

선택 IF~ELSE(IF-THEN-ELSE-END IF)

DECLARE

vempno emp.empno%TYPE;

vename emp.ename%TYPE;

vcomm emp.comm%TYPE;

BEGIN

SELECT empno, ename, comm INTO vempno, vename, vcomm FROM emp WHERE ename='WARD';

IF(vcomm>0) THEN

dbms_output.put_line(vename||'의 커미션은 '||vcomm||'입니다.');

ELSE

dbms_output.put_line(vename||'은 커미션이 없습니다.');

END IF;

END;

/

반복문

FOR var_name(변수) IN lower..upper LOOP

statement

END LOOP;

ex)

FOR i IN 1..9 LOOP 

statement(실행문장)

END LOOP;