Oracle 정리

DDL(정의어)

테이블의 컬럼 자료형 수정(데이터 유지한 상태로 변경하는 방법)

ALTER TABLE <테이블명> MODIFY <컬럼명> <자료형>

 

테이블의 컬럼 삭제

ALTER TABLE <테이블명> DROP COLUMN <컬럼명>

 

테이블의 컬럼 추가

ALTER TABLE <테이블명> ADD <컬럼명> <자료형>

원하는 위치에 컬럼 추가하는 방법(!?)

 

오라클 예약어를 테이블명, 혹은 컬럼명 등으로 사용하는 방법 - ""로 감싸주면 된다

 

컬럼명 변경

ALTER TABLE <테이블명> RENAME COLUMN <변경해야할 컬럼명> TO <새로운 컬럼명>

 

DML(조작어)

INSERT 명령 규칙

INSERT INTO <테이블명> VALUES <값목록>

 

모든 필드 값 입력하기

INSERT INTO <테이블명> VALUES ('값1', '값2', '값3', '값4', '값5')

 

원하는 필드만, 원라는 순서대로 입력하기

INSERT INTO <테이블명(컬럼1, 컬럼2)> VALUES('값1', '값2')

나머지는 NULL 값 들어감.

 

UPDATE 명령 규칙

UPDATE <테이블명> SET <컬럼명> = '<값>' WHERE <조건> = '<필드명>'

 

DELETE 명령 규칙

DELETE [FROM] <테이블명> WHERE <컬럼명> = '<필드명>'

 

SELECT 구문

SELECT, FROM, WHERE, GROUP BY, HAVING, ORDER BY

 

SELECT <컬럼명> (as) <별칭> FROM <테이블명>

** as는 생략 가능하다

 

SELECT <컬럼명> (as) <"별칭"> FROM <테이블명>

** "" 는 대소문자를 구분해줌.

 

SELECT <컬럼명> FROM <테이블명> WHERE <컬럼명> IS NULL OR IS NOT NULL

 

- SELECT * FROM NOTICE WHERE HIT = 0 OR HIT = 1 OR HIT = 2

- SELECT * FROM NOTICE WHERE  0 <= HIT AND HIT <= 2

- SELECT * FROM NOTICE WHERE HIT BETWEEN 0 AND 2

 

(다른 쿼리 동일한 결과)

 

- SELECT * FROM NOTICE WHERE HIT = 0 OR HIT = 2 OR HIT = 7

- SELECT * FROM NOTICE WHERE HIT (NOT) IN (0, 2, 7)

(다른 쿼리 동일한 결과)

 

Q.회원중에서 박씨성을 조회하시오.

SELECT * FROM MEMBER WHERE NAME  (NOT) LIKE = '박%'

 

Q.회원중에서 박씨성이고 외자인 회원을 조회하시오.

SELECT * FROM MEMBER WHERE NAME LIKE = '박_'

 

WHERE 절 구문

SELECT <컬럼명> FROM <테이블명> WHERE <조건> 

 

SELECT <컬럼명> FROM <테이블명> WHERE

 

ROWNUM BETWEEN 1 AND 5

**ROWNUM은 행 번호

 

SELECT <컬럼명> FROM <테이블명> WHERE ROWNUM BETWEEN 6 AND 10

** ROWNUM을 1이 아닌 수 부터 적용하면 데이터가 출력이 되지 않는다. ROWNUM은 결과 테이블이 만들어질때 결과 테이블을 기준으로 생성되기 때문이다.

 

SELECT * FROM (SELECT ROWNUM NUM, EMPLOYEES.* FROM EMPLOYEES) WHERE  NUM BETWEEN 1 AND 5

 

DISTINCT 중복 제거

SELECT DISTINCT <컬럼명> FROM <테이블명>

 

서브쿼리

SELECT <컬럼명> FROM (SELECT <테이블명> FROM <테이블명> WHERE <조건>) WHERE <조건>

 

SELECT <컬럼명> FROM <테이블명> WHERE <조건> (SELECT <컬럼명> FROM <테이블명> WHERE <조건>)

 

JOIN 구문

INNER JOIN, OUTER JOIN, SELF JOIN, CROSS JOIN

 

INNER JOIN - 서로 관계가 있는 레코드만 합치는 조인

 

OUTER JOIN - 참조키를 기준으로 일치하지 않는 행도 포함시키는 조인

** LEFT JOIN, RIGHT JOIN, FULL JOIN

 

SELF JOIN - 같은 테이블 안에 참조관계에 있는 컬럼을 조인

** employee_id와 manager_id

 

- INNER JOIN

 

SELECT <컬럼명> FROM <테이블A> INNER

 

JOIN <테이블B> ON <A.컬럼a> = <B.컬럼a>

 

SELECT <컬럼명> FROM <테이블A> LEFT/RIGHT/FULL OUTER JOIN <테이블B> ON <A.컬럼a> = <B.컬럼a>

 

- LEFT/RIGHT OUTER JOIN

 

SELECT <컬럼명> FROM <테이블A> LEFT OUTER JOIN <테이블B> ON <A.컬럼a> = <B.컬럼a>

** 테이블A의 아우터들을 포함시키고 나머지는 NULL값으로 채운다

 

SELECT <컬럼명> FROM <테이블A> RIGHT OUTER JOIN <테이블B> ON <A.컬럼a> = <B.컬럼a>

** 테이블B의 아우터들을 포함시키고 나머지는 NULL값으로 채운다

 

- SELF JOIN

 

SELECT M.*, B.NAME (AS) BOSS_NAME

 

FROM MEMBER M, LEFT OUTER JOIN MEMBER B ON B.ID = M.BOSS_ID

 

SELECT E.*, M.FIRST_NAME ||' '|| LAST_NANE (AS) MANAGER_NAME FROM EMPLOYEES E, LEFT OUTER JOIN EMPLOYEES M ON E.EMPLOYEE_ID = M.MANAGER_ID

 

 

오라클 JOIN

 

- ANSI INNER JOIN

 

SELECT N.ID, N.TITLE, N.NAME FROM MEMBER M INNER JOIN NOTICE N ON M.ID = N.WRITER_ID WHERE M.ID = 'newlec'

 

- ORACLE INNER JOIN

 

SELECT N.ID, N.TITLE, N.NAME FROM MEMBER M, NOTICE N WHERE M.ID = N.WRITER_ID AND M.ID = 'newlec'

 

- ANSI OUTER JOIN

 

SELECT N.*, M.NAME WRITER_NAME FROM NOTICE N LEFT OUTER JOIN MEMBER M ON M.ID = N.WRITER_ID WHERE M.ID = 'newlec'

 

- ORACLE OUTER JOIN

 

SELECT N.*, M.NAME WRITER_NAME FROM NOTICE N, MEMBER M WHERE N.WRITER_ID = M.ID(+)

 

 

UNION 구문

 

SELECT ID, NAME, FROM MEMBER UNION ID, WRITER_ID, NAME FROM NOTICE

 

 

VIEW

 

CREATE VIEW <뷰이름> AS SELECT <컬럼명> FROM <테이블명> WHERE <조건>

 

 

- CREATE VIEW

 

     -> 뷰(하나 이상의 기본 테이블로부터 유도되는 이름을 갖는 가상 테이블)를 정의

 

     -> SELECT문의 결과로써 뷰를 생성

 

CREATE VIEW 뷰명[(속성명[, 속성명, ...])]

AS SELECT문;

 

    -> 고객 테이블에서 '주소'가 '포천시'인 고객들의 '이름'과 '전화번호'를 '포천 고객'이라는 뷰로 정의하는 SQL문

 

CREATE VIEW 포천고객(이름, 전화번호)

AS SELECT 이름,전화번호

FROM 고객

WHERE 주소 = '포천시';

 

 

PROCEDURE(프로시저)

 

CREATE [OR REPLACE] PROCEDURE <프로시저

 

명(파라미터 컬럼명 IN/OUT/INOUT 자료형)>

[지역번수 선언]

BEGIN

    프로시저 BODY;

END;

 

- OR REPLACE : 동일한 프로시저 이름이 이미 존재하는 경우 기존의 프로시저를 대체

 

- 파라미터

 

    -> IN : 호출 프로그램이 프로시저에게 값을 전달할 때 사용

 

    -> OUT : 프로시저가 호출 프로그램에게 값을 전달할 때 사용

 

    -> INOUT : 호출 프로그램이 프로시저에게 값을 전달하고, 프로시저 실행 후 호출 프로그램에게 값을 반환할 때 지정

 

    -> 매개변수명 : 호출 프로그램으로부터 전달받은 값을 저장할 변수의 이름을 지정

 

 

    -> 자료형 : 변수의 자료형을 지정

 

- 프로시저 BODY

 

    -> 프로시저의 코드를 기록

 

    -> BEGIN과 END 사이에 적어도 하나의 SQL 문이 존재해야 함

 

Q. 사원번호를 입력받아 해당 사원의 지급방식을 S로 변경하는 프로시저

 

CREATE OR REPLACE PROCEDURE emp_change_s(i_사원번호 IN INT)

IS

BEGIN

    UPDATE 급여 SET 지급방식 = 'S' WHERE 사원번호 = i_사원번호;

    EXCEPTION

        WHEN PROGRAM_ERROR THEN

            ROLLBACK;

    COMMIT;

END;

 

 

프로시저 실행

 

EXECUTE 프로시저명;

EXEC 프로시저명;

CALL 프로시저명;

- 위 3가지 명령어 중 하나를 사용

 

- 위에 정의한 emp_change_s 프로시저를 사원번호 32를 인수로 하여 실행

 

EXECUTE emp_change_s(32);

EXEC emp_change_s(32);

CALL emp_change_s(32);

 

 

TRIGGER(트리거)

 

트리거(Trigger)란 영어로 방아쇠라는 뜻인데, 방아쇠를 당기면 그로 인해 총기 내부에서 알아서 일련의 작업을 실행하고 총알이 날아갑니다. 이처럼 데이터베이스에서도 트리거(Trigger)는 특정 테이블에 INSERT, DELETE, UPDATE 같은 DML 문이 수행되었을 때, 데이터베이스에서 자동으로 동작하도록

 

작성된 프로그램입니다. 즉! 사용자가 직접 호출하는 것이 아니라, 데이터베이스에서 자동적으로 호출하는 것이 가장 큰 특징입니다.

 

트리거의 생성

 

CREATE [OR REPLACE] TRIGGER 트리거명 [동작시기 옵션][동작 옵션] ON 테이블명

REFERENCING [NEW | OLD] AS 테이블명

FOR EACH ROW

[WHEN 조건식]

BEGIN

    트리거 BODY;

END;   

- 동작 시기 옵션 : 트리거가 실행될 때를 지정

 

    -> AFTER : 테이블이 변경된 후

 

    -> BEFORE : 테이블이 변경되기 전

 

- 동작 옵션 : 트리거가 실행되게 할 작업의 종류를 지정

 

    -> INSERT : 새로운 튜플을 삽입할 때

 

    -> DELETE : 튜플을 삭제할 때

 

    -> UPDATE : 튜플을 수정할 때

 

- NEW | OLD : 트리거가 적용될 테이블의 별칭을 지정

 

    -> NEW : 추가되거나 수정에 참여할 튜플들의 집합(테이블)을 의미

 

    -> OLD : 수정되거나 삭제 전 대상이 되는 튜플들의 집합(테이블)을 의미

 

- FOR EACH ROW : 각 튜플마다 트리거를 적용한다는 의미

 

- WHEN 조건식 : 트리거를 적용할 튜플의 조건을 지정

 

- 학생 테이블에 새로운 튜플이 삽입될 때 튜플에 학년 정보가 누락되었으면 학년 필드에 신입생을 치환하는 트리거를 학년 정보_tri라는 이름으로 정의

 

 

CREATE TRIGGER 학생정보_tri BEFORE INSERT ON 학생

REFERENCING NEW AS new_table

FOR EACH ROW

WHEN (new_table.학년 IS NULL)

BEGIN

    :new_table.학년 := '신입생';

END; 

 

 

사용자 정의 함수 생성

CREATE [OR REPLACE] FUNCTION 사용자 정의 함수명(파라미터)

[지역변수 선언]

BEGIN

    사용자 정의 함수 BODY;

    RETURN 반환;

END;

 

- 프로시저와 유사하며 파라미터에의 구성요소는 IN, 매개변수명, 자료형이 있음

 

 

- RETURN 반환값 : 반환할 값이나 반환할 값이 저장된 변수를 호출 프로그램으로 돌려줌

 

- i_성별코드를 입력받아 1이면 남자, 2면 여자를 반환하는 사용자 정의 함수를 Get_S_성별로 정의

 

CREATE FUNCTION Get_S_성별(i_성별코드 IN INT)

RETURN VARCHAR2

IS

BEGIN

    IF (i_성별코드 = 1) THEN

        RETURN '남자';

    ELSE

        RETURN '여자';

    END IF;

END;

 

사용자 정의 함수 실행

SELECT 사용자 정의 함수명 FROM 테이블명;

INSERT INTO 테이블명(속성명) VALUES (사용자 정의 함수명);

 

DELETE FROM 테이블명 WHERE 속성명 = 사용자 정의 함수명;

UPDATE 테이블명 SET 속성명 = 사용자 정의 함수명;

 

기타 내용 정리

VARCHAR(50) 괄호 안의 숫자는 BYTE 크기를 의미

CHAR ([size [BYTE | CHAR])]

size BYTE가 Default

 

오라클에서 '+' 연산자는 숫자를 위한 연산자

 

문자를 더하는 연산자는 '||'

 

트랜잭션 - 업무의 실행단위/논리 명령단위