1. 함수란?
일반적인 프로그래밍 언어에서 사용되는 함수와 같이 복잡한 프로그래밍이 가능하도록 지원 해준다. 함수는 RETURN문에 의해서 특정 값을 돌려주는 기능도 한다.
2. 함수 생성법
CREATE FUNCTION 함수이름 (파라미터)
RETURN 데이터형식
AS
변수 선언 부분
BEGIN
이 부분에 PL/SQL 프로그램 코딩
RETURN 변수;
END 함수이름;
3. 함수의 정의와 프로시저 정의의 차이점
- 프로시저의 파라미터와 달리 IN, OUT, IN OUT 을 사용할 수 없다.
- 함수는 RETURN 문으로 반환할 값의 데이터 형식을 지정하고, 본문 안에서는 RETURN 문으로 하나의 값을 반환해야 한다.
- 프로시저는 별도의 반환하는 구문이 없으며, 필요하다면 여러개의 OUT 파라미터를 사용해야 한다.
- 프로시저는 EXECUTE 로 호출하지만, 함수는 EXECUTE 뿐 아니라, SELECT 문장 안에서도 호출 한다.
1. 스토어드 프로시저란?
오라클에서 제공되는 프로그래밍 기능.
쿼리문의 집합으로 어떠한 동작을 일괄 처리 하기 위한 용도로 사용.
자주 사용되는 일반적인 쿼리를 사용하는 것보다 모듈화 시켜서 필요할 때마다 호출.
2. 스토어드 프로시저의 정의 형식
CREATE OR REPLACE PROCEDURE 프로시저_이름 AS 변수_선언_부분
BEGIN
PL/SQL 프로그래밍 코딩 부분
END 프로시저_이름
3. 스토어드 프로시저의 실행
EXECUTE 프로시저_이름();
4. 스토어드 프로시저의 수정과 삭제
수정 : CREATE OR REPLACE PROCEDURE 문을 다시 사용
삭제 : DROP PROCEDURE
5. 파라미터의 사용
입력 - 입력_파라미터_이름 IN 데이터_형식 := 디폴트 값
실행 - EXECUTE 스토어드_프로시저_이름(전달 값);
출력 - 출력_파라미터_이름 OUT 데이터 형식
입출력 - 입력_파라미터_이름 IN OUT 데이터_형식 := 디폴트 값
6. 스토어드 프로시저의 특징
- 오라클의 성능을 향상 시킬 수 있다.
- 유지 관리가 간편하다
- 예외 처리 및 모듈식 프로그램이 가능하다.
- 네트워크 전송량의 감소
1. 개념
검색을 빠르게 하기 위해 책의 찾아보기 부분과 같이 미리 정리를 해놓는 기술
2. 장점
-검색의 속도를 높일 수 있다.
-쿼리의 부하가 줄어들어 시스템 전체 성능이 향상된다.
3. 단점
-인덱스가 DB 공간을 차지하여 추가적인 공간이 필요
-처음 인덱스를 생성하는데 시간이 필요
-변경 작업(insert, update, delete)이 자주 일어날 경우 오히려 성능이 나빠질 수 있음
4. 정의법
CREATE TEBLE 테이블명
(
userID CHAR(8) NOT NULL PRIMARY KEY, --기본키를 지정하면 인덱스 자동 생성
)
테이블 생성 시 제약 조건에 Primary Key 또는 Unique를 사용하면 자동으로 인덱스 생성
5. 인덱스 제거법
DROP INDEX 인덱스이름;
6. 결론
-인덱스는 열 단위에서 생성된다.
-WHERE 절에서 사용되는 열에 인덱스를 생성해야 한다
-WHERE 절에 사용되더라도 자주 사용해야 가치가 있다
-데이터의 중복도가 높은 열은 인덱스를 만들어도 별 효과가 없다.
-JOIN 에 자주 사용되는 열에는 인덱스를 생성해주는 것이 좋다.
-인덱스는 읽기에만 성능 향상 시키기에 데이터 변경이 자주 일어나는 DB에는 좋지 않다.
- 사용하지 않는 인덱스는 제거하여 용량과 부하를 줄일 수 있다.
- 뷰(view)란?
1. 보여주고 싶은 데이터만 보여줌으로 보안에 도움이 됨
2. 복잡한 쿼리를 단순화 시킬 수 있다.
CREATE OR REPLACE VIEW v_userbuyTBL --뷰 테이블 명
AS
SELECT U.userID, U.userName, B.prodName, U.addr, U.mobile1 || U.mobile2 AS "연락처"
FROM userTBL U
INNER JOIN buyTBL B
ON U.userID = B.userID ;
--위의 쿼리문을 아래와 같이 단순화 가능
SELECT * FROM v_userbuyTBL WHERE userName = '김범수';
3. 뷰 활용
DROP VIEW v_userbuyTBL; --뷰 삭제
UPDATE v_userTBL SET addr = '부산' WHERE userID='JKW'; --뷰 수정
INSERT INTO v_userTBL(userID, userName, addr) VALUES('KBM','김병만','충북'); --뷰 입력
1. 기본키 설정
CREATE TABLE userTBL
( userID CHAR(8) NOT NULL PRIMARY KEY, --userTBL의 기본키
userName NVARCHAR2(10) NOT NULL,
birthYear NUMBER(4) NOT NULL
);
CREATE TABLE buyTBL
( idNum NUMBER(8) NOT NULL PRIMARY KEY, --buyTBL 의 기본키
userID CHAR(8) NOT NULL,
prodName NCHAR(6) NOT NULL
);
2. 외래키 설정
CREATE TABLE buyTBL
( idNum NUMBER(8) NOT NULL PRIMARY KEY,
userID CHAR(8) NOT NULL,
, FOREIGN KEY(userID) REFERENCES userTBL(userID) --buyTBL 의 외래키 지정
);
또는
ALTER TABLE buyTBL
ADD CONSTRAINT FK_userTBL_buyTBL --외래키 이름 지정
FOREIGN KEY (userID) --현재 테이블 외래키
REFERENCES userTBL (userID) -- 연결할 기본 테이블 기본키
ON DELETE CASCADE ; --기본 테이블 삭제시 현재 테이블도 삭제
3. UNIQUE 제약 조건 -중복되지 않은 유일한 값이지만 NULL 허용
CREATE TABLE userTBL
( userID CHAR(8) NOT NULL ,
userName NVARCHAR2(10) NOT NULL,
birthYear NUMBER(4) NOT NULL,
, CONSTRAINT AK_email UNIQUE (email) --이메일은 유일해야 하지만 null일 수도 있다
);
또는
ALTER TABLE USERTBL
ADD CONSTRAINT AK_EMAIL UNIQUE (EMAIL);
4. CHECK 제약 조건 - 입력되는 데이터 점검 기능
ALTER TABLE userTbl
ADD CONSTRAINT CK_height
CHECK (height >= 0) ; -- 키는 0이상이어야 함.
ALTER TABLE userTbl
ADD CONSTRAINT CK_mobile1
CHECK (mobile1 IN ('010','011','016','017','018','019')); -- 휴대폰 국번 체크
5. DEFAULT 정의 - 값 입력이 없으면 기본으로 입력되는 기본 값
CREATE TABLE userTBL
( userID CHAR(8) NOT NULL PRIMARY KEY ,
userName NVARCHAR2(10) NOT NULL ,
birthYear NUMBER(4) DEFAULT -1 NOT NULL , --기본 -1 입력
addr NCHAR(2) DEFAULT '서울' NOT NULL , --기본 서울 입력
mobile1 CHAR(3) NULL,
mobile2 CHAR(8) NULL,
height NUMBER(3) DEFAULT 170 NULL, --기본 170 입력
mDate DATE NULL
);
또는
ALTER TABLE userTBL
MODIFY birthYear DEFAULT -1; --기본 -1 입력
ALTER TABLE userTBL
MODIFY addr DEFAULT '서울'; --기본 서울 입력
ALTER TABLE userTBL
MODIFY height DEFAULT 170; --기본 170 입력
1. ROLLUP() - 총합 또는 중간 합계가 필요할 때 사용
//이름과 id별로 합계와 총합을 조회
SELECT id, name SUM(a * b) AS "비용" FROM buyTBL
GROUP BY ROLLUP (name, id)
2. GROUPING_ID() - 함수의 결과가 0이면 데이터, 1이면 합계를 위해 추가된 열
SELECT id, name SUM(a * b) AS "비용", GROUPING_ID(name) AS "추가행 여부"
FROM buyTBL
GROUP BY ROLLUP (name, id)
3. WITH절 비재귀적 CTE 구조
WITH abc(id, total)
AS
( SELECT id, SUM(a * b) FROM buyTBL GROUP BY userID )
SELECT * FROM abc ORDER BY total DESC;
4. WITH절 재귀적 CTE 구조
WITH empCTE(empName, mgrName, dept, empLevel)
AS
(
(SELECT emp, manager, department, 0 FROM empTBL WHERE manager = "없음")
UNION ALL
(SELECT empTbl.emp, emmpTbl.manager, empTbl.department, empCTE.empLevenl + 1
FROM empTbl INNER JOIN empCTE ON empTbl.manager = empCTE.empName)
)
SELECT * FROM empCTE ORDER BY dept, empLevel;
1. 중복제거
//주소가 겹치는 사람은 1개씩만 출력
SELECT DISTINCT addr FROM userTBL;
2. ROWNUM
//결과값의 5개만 출력
SELECT * FROM
(SELECT id, data FROM emp ORDER BY date ASC) WHERE ROWNUM <= 5;
3. SAMPLE(퍼센트)
//전체 결과 값 중 5%의 갯수만큼 출력
SELECT id, data FROM emp WHERE SAMPLE(5);
4. 테이블 복사
CREATE TEBLE 새로운 테이블명 AS (SELECT 복사할 열 FROM 기존 테이블명)
5. GROUP BY 절 - 그룹으로 묶어주는 역할
SELECT userID, amount FROM buyTBL ORDER BY userID; //userID로 그룹화
6. 집계함수
SUM() 합계
AVG() 평균
MAX() 최대값
COUNT() 행의 갯수
COUNT(DISTINCT) 행의 갯수(중복은 1개만 인정)
STDEV() 표준편차
VARIANCE() 분산
CAST() 데이터 변환 함수
7. HAVING 절 - 집계 함수시 조건절
//총 구매액이 1000원 이상인 값만 출력
SELECT userID AS "사용자", SUM(a * b) AS "총 구매액" FROM buyTBL
GROUP BY userID
HAVING SUM(a * b) > 1000
