D34 OBJECT , PLSQL
20241111 D33
OBJECT
OBJECT 객체
- 데이터베이스를 이루는 논리적인 구조물.
OBJECT의 종류- TABLE, USER, VIEW, SEQUENCE, INDEX, PACKAGE, TRIGGER ...
VIEW (뷰)
- SELECT 문을 저장해 둘 수 있는 객체 = 서브쿼리 (인라인뷰) 를 저장 할 수 잇는 객체
- 자주 사용되는 SELECT문을 VIEW에 저장, 재사용성 보안성이 확보된다.
-- '한국' 에서 근무하는 사원들의 사번, 이름, 부서명, 급여, 연봉, 근무국가명, 직급명을 조회 SELECT EMP_ID, EMP_NAME, DEPT_TITLE, SALARY*12 연봉, NATIONAL_NAME, JOB_NAME FROM EMPLOYEE E, DEPARTMENT D, NATION N, JOB J, LOCATION L WHERE E.DEPT_CODE = D.DEPT_ID AND E.JOB_CODE = J.JOB_CODE AND D.LOCATION_ID = L.LOCAL_CODE AND L.NATIONAL_CODE = N.NATIONAL_CODE AND NATIONAL_NAME = '한국';
1. VIEW 생성 방법
[표현법] CREATE VIEW 뷰이름 AS 서브쿼리; --기본형 CREATE OR REPLACE 옵션 -> 뷰 생성할 떄 기존에 중복된 이름의 뷰가 있다면 그 이름의 뷰를 REPLAEC한다 만약, 중복된 이름의 뷰가 없다면 새로 CREATE한다.
CREATE VIEW VW_EMPLOYEE
AS SELECT EMP_ID, EMP_NAME, DEPT_TITLE, SALARY*12 연봉, NATIONAL_NAME, JOB_NAME
FROM EMPLOYEE E, DEPARTMENT D, NATION N, JOB J, LOCATION L
WHERE E.DEPT_CODE = D.DEPT_ID
AND E.JOB_CODE = J.JOB_CODE
AND D.LOCATION_ID = L.LOCAL_CODE
AND L.NATIONAL_CODE = N.NATIONAL_CODE
AND NATIONAL_NAME = '한국'; --관리자 계정으로 권한 부여후 생성
-- 관리자 계정으로 접속해서 VIEW 생성 권한 추가
-- GRANT CREATE VIEW TO C##KH; 관리자 계정에서 계정 생성 명령문
SELECT * FROM VW_EMPLOYEE; --저장한 테이블 확인
-- CREATE OR REPLACE
-- 해당 객체가 이미 존재할 경우, 기존 객체를 삭제하고 새로운 객체를 생성하는 기능
CREATE OR REPLACE VIEW VW_EMPLOYEE --OR REPL
AS SELECT EMP_ID, EMP_NAME, DEPT_TITLE,SALARY, SALARY*12 연봉,BONUS, NATIONAL_NAME, JOB_NAME
FROM EMPLOYEE E, DEPARTMENT D, NATION N, JOB J, LOCATION L
WHERE E.DEPT_CODE = D.DEPT_ID
AND E.JOB_CODE = J.JOB_CODE
AND D.LOCATION_ID = L.LOCAL_CODE
AND L.NATIONAL_CODE = N.NATIONAL_CODE ;
--1)
SELECT * FROM VW_EMPLOYEE
WHERE NATIONAL_NAME = '한국';
--위아래 1) = 2)쿼리문은 같은 내용이다
--2)
SELECT *
FROM CREATE OR REPLACE VIEW VW_EMPLOYEE --OR REPLACE
AS SELECT EMP_ID, EMP_NAME, DEPT_TITLE,SALARY, SALARY*12 연봉,BONUS, NATIONAL_NAME, JOB_NAME
FROM EMPLOYEE E, DEPARTMENT D, NATION N, JOB J, LOCATION L
WHERE E.DEPT_CODE = D.DEPT_ID
AND E.JOB_CODE = J.JOB_CODE
AND D.LOCATION_ID = L.LOCAL_CODE
AND L.NATIONAL_CODE = N.NATIONAL_CODE
WHERE NATIONAL_NAME = '한국' ;뷰는 논리적인 가상테이블러 실질적인 데이터를 보관하고 있지 않다
- 내부적으로 SUBQUERY를 TEXT형태로 보관, 해당 쿼리문을 호출하여 조회결과를 만들어준다
-- VIEW 전용 데이터 딕셔너리
(뷰(View)에 대한 메타데이터를 저장하고 관리하는 시스템을 의미)
--작명방식예(CMS 사용을 위한 작명)
-- USER_TABLES
-- USER_CONSTRINTS
SELECT * FROM USER_VIEWS; --VIEW 모든 정보 조회
SELECT * FROM USER_TABLES; --TABLE 모든 정보 조회뷰 컬럼에 별칭 부여 규칙
서브쿼리에 SELECT절에 함수, 산술연산식을 사용한 경우 반드시 별칭 지정
-- 사원의 사번, 이름, 직급명, 성별, 근무년수를 조회할수 있는 VIEW 정의
CREATE VIEW VW_EMP_JOB
AS SELECT EMP_ID, EMP_NAME, JOB_NAME,
DECODE(SUBSTR(EMP_NO,8, 1), 1, '남', 2, '여', 3, '남', 4, '여') 성별,
EXTRACT(YEAR FROM SYSDATE) - EXTRACT(YEAR FROM HIRE_DATE) 근무년수,
FROM EMPLOYEE
JOIN JOB USING(JOB_CODE);
-- VIEW 만의 별칭 부여 방법.
CREATE OR REPLACE VIEW VW_EMP_JOB (사번, 사원명, 직급명, 성별, 근무년수) -- 모든 칼럼에 별칭부여
AS SELECT EMP_ID, EMP_NAME, JOB_NAME,
DECODE(SUBSTR(EMP_NO,8, 1), 1, '남', 2, '여', 3, '남', 4, '여') ,
EXTRACT(YEAR FROM SYSDATE) - EXTRACT(YEAR FROM HIRE_DATE) ,
FROM EMPLOYEE
JOIN JOB USING(JOB_CODE);
-- VIEW 삭제하기
DROP VIEW VW_EMP_JOB;
SELECT * FROM VW_EMP_JOB; --삭제확인 뷰 조회
-- VIEW에 DML 사용하기.
-- VIEW를 통해 DML을 실행시 , 실제 테이블에 DML에 적용 된다.
CREATE VIEW VW_JOB
AS SELECT * FROM JOB;
SELECT * FROM JOB; --확인
SELECT * FROM VW_JOB; --확인
-- VW_JOB 테이블에 JOB_CODE 가 J8, 인턴 계급을 추가
INSERT INTO VW_JOB VALUES ('J8', '인턴');
-- VW_JOB뷰에 J8인 행의 JOB_NAME을 '알바'로 변경
UPDATE VW_JOB
SET JOB_NAME = '알바'
WHERE JOB_CODE = 'J8';
--J8인 행 삭제
DELETE FROM VW_JOB
WHERE JOB_CODE = 'J8';
-- VIEW 를 DML(조작) 하면 실제 데이터를 본과하고 있는 테이블에도 영향이 간다.VIEW 를 통해 DML이 가능한 경우
- 서브쿼리 , GROUP BY 등을 이용한 SELECT문이 아닌경우 .
VIEW를 통해 DML이 불가능한 경우
1) 뷰에 정의되지 않은 칼럼을 조각하는 경우
2) 뷰에 정의되어 있지 않은 칼럼중 NOT NULL 제약조건이 지정된 경우
3) 산술연산식 또는 함수를 통하여 정의 되어 있는 경우
4) 그룹함수나 GROUP BY 절이 포함된 경우
5) DISTINCT 구문이 포함된 경우
6) JOIN을 이용한 경우
-- 1) 1) 뷰에 정의되지 않은 칼럼을 조작하는 상황.
CREATE OR REPLACE VIEW VW_JOB
AS SELECT JOB_NAME FROM JOB;
SELECT * FROM VW_JOB;
INSERT INTO VW_JOB VALUES ( 'J8', '인턴'); --에러 too many values
INSERT INTO VW_JOB(JOB_CODE, JOB_NAME) VALUES ( 'J8', '인턴'); --에러 "JOB_CODE": 부적합한 식별자
-- 2) 뷰에 정의 되어 있지 않은 칼럼 중 NOT NULL제약조건이 추가된 경우
INSERT INTO VW_JOB(JOB_NAME) VALUES ('인턴');
-- NULL을 ("C##KH"."JOB"."JOB_CODE") 안에 삽입할 수 없습니다
UPDATE VW_JOB
SET JOB_NAME = '알바'
WHERE JOB_NAME = '사원'; -- 업데이트 가능
-- 3) 산술연산식 또는 함수를 통하여 정의 되어 있는 경우
CREATE OR REPLACE VIEW VW_EMP_SAL
AS SELECT EMP_ID, EMP_NAME, SALARY * 12 연봉
FROM EMPLOYEE;
SELECT * FROM VW_EMP_SAL; -- 생성확인
INSERT INTO VW_EMP_SAL VALUES (400, '임세윤' , 30000000); --에러
-가상 열은 사용할 수 없습니다 "virtual column not allowed here"
UPDATE VW_EMP_SAL
SET EMP_NAME = '임세윤'
WHERE EMP_ID = 900; -- 존재하는 컬럼만 가지고 업데이트 실행
ROLLBACK;
-- 4) 그룹함수나 GROUP BY 절이 사용된 경우
CREATE OR REPLACE VIEW VW_GROUPDEPT
AS SELECT DEPT_CODE, SUM(SALARY) 합계, FLOOR(AVG(SALARY)) 평균급여
FROM EMPLOYEE
GROUP BY DEPT_CODE;
SELECT * FROM VW_GROUPDEPT;
INSERT INTO VW_GROUPDEPT VALUES('D5', 12000000, 4000000); -- 에러
--가상 열은 사용할 수 없습니다 virtual column not allowed here
-- INSERT, UPDATE, DELETE 모두 불가
-- 5) DISTINCT 구문이 포함된 경우
CREATE OR REPLACE VIEW VW_DT_JOB
AS SELECT DISTINCT JOB_CODE
FROM EMPLOYEE;
SELECT * FROM VW_DT_JOB;
--JOB코드로 생성한 뷰는 문제가 되지 않지만
--EMPLOYEE로 생성된 뷰는 에러 발생
INSERT INTO VW_DT_JOB VALUES('J8'); --에러
--뷰에 대한 데이터 조작이 부적합합니다 data manipulation operation not legal on this view
-- 6) JOIN을 이용한 경우
CREATE OR REPLACE VIEW VW_JOINEMP
AS SELECT EMP_ID, EMP_NAME, DEPT_TITLE
FROM EMPLOYEE
JOIN DEPARTMENT ON DEPT_CODE = DEPT_ID;
SELECT * FROM VW_JOINEMP; --생성완료
INSERT INTO VW_JOINEMP VALUES (300, '조세오', '총무부');
--한번에 2개의 테이블에 데이터 추가 불가능
UPDATE VW_JOINEMP
SET EMP_NAME = '조세오'
WHERE EMP_ID = 900; -- 1개의 테이블만 수정하면서, VIEW가 가지고 있는 칼럼만 이용하는 경우
UPDATE VW_JOINEMP
SET DEPT_TITLE = '행정부'
WHERE EMP_ID = 900; -- 과거에 가능하지 않았던 쿼리문
--900번 사원의 DEPT_CODE를 가져와서 DEPARTMENT DEPT_ID와 일치하는 행을 찾음VIEW 사용가능한 옵션들
- OR REPLACE
- FORCE / NOFORCE 옵션
- 실제 테이블이 없어도 VIEW를 먼저 생성할수 있게 해주는 옵션.
- WITH CHECK OPTION
- SELECT문의 WHERE절에서 사용한 칼럼을 "수정불가"하게끔 막는 옵션
- WITH READ ONLY 옵션
- WITH READ ONLY 옵션
- VIEW에 대한 조작이 불가능하게 하는 옵션
2 . FORCE / NOFORCE 옵션
CREATE OR REPLACE FORCE VIEW V_FORCETEST AS SELECT WORD FROM NOTING; -- SELECT * FROM NOTING; -- 에러발생 SELECT * FROM V_FORCETEST; -- 오류발생 -- CREATE TABLE NOTING ( WORD VARCHAR2(5) ); -- --DROP TABLE NOTING;3. WITH CHECK OPTION
CREATE OR REPLACE VIEW VW_CHECKOPTION AS SELECT EMP_ID, EMP_NAME, SALARY, DEPT_CODE FROM EMPLOYEE WHERE DEPT_CODE = 'D5' WITH CHECK OPTION; -- SELECT * FROM VW_CHECKOPTION; -- UPDATE VW_CHECKOPTION SET DEPT_CODE = 'D6'; --WHERE DEPT_CODE = 'D5' -- UPDATE VW_CHECKOPTION SET SALARY = 10000000 WHERE EMP_ID = 215; -- 업데이트 완료 가능하다4. WITH READ ONLY (DML 불가)
CREATE OR REPLACE VIEW V_READ AS SELECT EMP_ID, EMP_NAME, SALARY, DEPT_CODE FROM EMPLOYEE WHERE DEPT_CODE = 'D5' WITH READ ONLY; -- UPDATE V_READ SET SALARY = 10000000 WHERE EMP_ID = 215; -읽기전용 VIEW에서는 SELECT만 가능.
시퀀스 SEQUENCE
SEQUENCE (시퀀스)
- 자도으로 번호를 입력시켜주는 객체(자동 번호 부여)
- 정수값을 순차적으로 발생시켜준다.
예시) 회원번호, 사번, 게시글 번호에 값을 자동으로 넣어주고 싶을 때 사용.1. 시퀸스 객체 생성 구문
[표현법] CREATE SEQUENCE 시퀸스명 START WITH 시작숫자 -> 시작값 설정(DIFAULT 1) INCREMENT BY 증가값 -> 시퀸스 증가 때 증가값 설정( DEFAULT 1) MAXVALUE 최대값 MINVALUE 최소값 CYCLE / NOCYCLE -> 값의 순환(1~N회전) 여부. CACHE 바이트크기/NOCACHE -> 캐시메모리 사용 여부(기본값 20BYTE) (시퀸스) 캐시 메모리
- 시퀸스로부터 미리 발생될 값들을 생성해서 저장해두는 저장공간.
- 매번 호출되어 새로운 번호의 생성보다 캐시메모리 저장공간에 미리 생성된 값을 사용하게 되면 속도가 빠르다.
- 단, 세션이 끊기고 나서 재접속 후 기존에 생성된 값들은 초기화 된다.
CREATE SEQUENCE SEQ_TEST; -- SELECT * FROM USER_SEQUENCES; --- 모든내용확인 -- CREATE SEQUENCE SEQ_EMPNO START WITH 300 INCREMENT BY 5 MAXVALUE 310 NOCYCLE --생략가능 NOCACHE; --생략가능2. 시퀸스 사용 구문
시퀸스명.CURRVAL : 현재 시퀸스의 값(마짐가으로 발생된 NECTVAL값) 시퀸스명.NEXTVAL : 현재 시퀸스의 값을 INCREMTENT_BY만큼 증가시키고, 그 증가된 시퀸스 값 == CURRVAL + INVAREMENT_BY 증가값 단, 시퀸스 생성 후 첫 NEXTVAL 은 START WITH 으로 지정한 값으로 발생 , 시퀸스 생성 후 1번 이상 NEXTVAL 수행후 CURRVAL 값을 얻어 올수 있다 -- SELECT SEQ_EMPNO.CURRVAL FROM DUAL; -- 에러)시퀀스 SEQ_EMPNO.CURRVAL은 이 세션에서는 정의 되어 있지 않습니다 -- SELECT SEQ_EMPNO.NEXTVAL FROM DUAL; -- 넥스트발 실행후 '300' SELECT SEQ_EMPNO.CURRVAL FROM DUAL; --실행 이후 첫 값 '300' --CURRVAL은 마지막으로 수행된 NEXTVAL의 값을 저장, -- SELECT SEQ_EMPNO.NEXTVAL FROM DUAL; --'305' 두번째 실행 INCREMENT BY 5 SELECT SEQ_EMPNO.CURRVAL FROM DUAL; -- '305' 항상 마지막 실행된 NEXTVAL 값 -- SELECT SEQ_EMPNO.CURRVAL FROM DUAL; -- '305' 여러번 실행해도 기준값은 그대로 -- SELECT SEQ_EMPNO.NEXTVAL FROM DUAL; -- '310' -- SELECT SEQ_EMPNO.NEXTVAL FROM DUAL; -- 에러) 최대값 초과3. 시퀀스 변경
[표현법] ALTER SEQUENCE 시퀀스명 INCREMENT BY 증가값 MAXVALUE 최대값 MINVALUE 최소값 CYCLE / NOCYCLE CACHE 바이트크기/ NOCACHE -> **START WITH은 변경이 불가. -- ALTER SEQUENCE SEQ_EMPNO INCREMENT BY 10 MAXVALUE 400 CACHE 40; -- SELECT SEQ_EMPNO.CURRVAL FROM DUAL; --'310' 마지막으로 출렸했던 값그대로 -- SELECT SEQ_EMPNO.NEXTVAL FROM DUAL; --'320' 변경값을 적용한 값 -- SELECT SEQ_EMPNO.CURRVAL FROM DUAL; --에러) 계정접속 해제(세션종료)후 재접속 했을때 -- SELECT SEQ_EMPNO.CURRVAL FROM DUAL; -- 첫시작시 세션 재접속시는 값이 저장되어 있지 않다 -- CURRVAL 캐시메모리에 저장되기 떄문에 SESSION 종료기 값이 없어진다. SELECT SEQ_EMPNO.NEXTVAL FROM DUAL; -- NEXTVAL값을 문제 없이 출력 -- SELECT * FROM USER_SEQUENCES; 모든내용 확인 DROP SEQUENCE SEQ_EMPNO; 테이블 삭제연습문제
--매번 새로운 사번이 발생되는 시퀀스 생성(SEQ_EID) --시퀀스를 통해 발생하는 시작값은 300 증가값 1 최대값 400 설정 CREATE SEQUENCE SEQ_EID START WITH 300 MAXVALUE 400 ; -- -- 시퀀스를 활용 EMPLOYEE 테이블에 새로운 행을 추가 -- 값은 자유롭게 작성. --1번 INSERT INTO EMPLOYEE(EMP_ID, EMP_NAME, EMP_NO, JOB_CODE, SAL_LEVEL, HIRE_DATE) VALUES( SEQ_EID.NEXTVAL, '윤휘명', '870131-1231231','J2', 'S3', SYSDATE); -- SELECT SEQ_EID.NEXTVAL FROM DUAL; --2번 INSERT INTO EMPLOYEE(EMP_ID, EMP_NAME, EMP_NO, JOB_CODE, SAL_LEVEL, HIRE_DATE) VALUES( SEQ_EID.CURRVAL, '윤휘명', '870131-1231231','J2', 'S3', SYSDATE);
INDEX
'목차'의 개념을 추상화
INDEX
- 데이터를 빠르게 검색하는 객체로 데이터의 정렬/탐색과 등 DBMS의 성능 향상의 목적
- 테이블의 찾고자하는 정보(칼럼값)을 통해 인덱스를 만들면, 인덱스로 지정된 칼럼은 별도의 저장공간에 칼럼값+위치정보가 저장 이때, 검색의 편리성을 위해 컬럼값 기준 오름차순 정렬로 데이터를 보관.
- INDEX는 데이터를 빠르게 조회하기 위해 데이터에 대한 정보와 데이터를 찾기 위한 위치정보를 함께 저장하는 객체이다
INDEX는 일반적으로 B*TREE 방식으로 구현되어 있다.
B*TREE : 루트, 브랜치, 리프 노드가 존재 (뿌리 > 줄기 > 잎)
루트노드에서 리프노드까지 항상 균등한 (BALNECD)깊이를 가진 형태의 자료구조
- 루트와 브랜치는 "값의 범위"만을 저장
- 리프노드에 "실체 인덱스의 키값"과 해당하는
- 테이블의 칼럼에 접근할수 있는 "위치주소(ROWID)"를 보관하고
- 이 값들은 "키값 기준 오름차순 정렬"되어있다.
- 인덱스는 강제로 사용하게 할 수 도 있지만, 일반적으로 DBMS에 의해 자동 호출 사용
- INDEX는 SELECT, JOIN 문들이 실행 될때 JOIN"조건절"이나 WHERE"조건절"에 인덱스로
지정된 칼럼이 사용 될 경우 DBMS에 의해 사용 될 수 있다.[표현법] CREATE INDEX 인덱스명 ON 테이블명 (...컬럼명); 자동생성 : PRIMARY KEY, UNIQUE 제약조건 추가시 INDEX 자동 생성. -- SELECT * FROM USER_MOCK_DATA; SELECT COUNT(*) FROM USER_MOCK_DATA; -- SELECT * FROM USER_MOCK_DATA WHERE ID = 22222; --FULL SCAN -- SELECT * FROM USER_MOCK_DATA WHERE EMAIL = 'niacobassi65@shareasale.com'; --FULL -- SELECT * FROM USER_MOCK_DATA WHERE GENDER = 'MALE'; -- SELECT * FROM USER_MOCK_DATA WHERE FIRST_NAME LIKE 'R%'; ---------인덱스 정렬 확인 작업 모두 적용되지 않아 FULL SCAN 중----- SELECT * FROM USER_INDEXES; ----- 저장된 인덱스 방식이 없다 -- -- 제약조건 추가 ALTER TABLE USER_MOCK_DATA ADD CONSTRAINT PK_ID PRIMARY KEY(ID); -- PK기본키 생성 ALTER TABLE USER_MOCK_DATA ADD CONSTRAINT UQ_EMAIL UNIQUE(EMAIL); -- SELECT * FROM USER_INDEXES; --모든 정보 확인 -- SELECT * FROM USER_MOCK_DATA WHERE ID = 22222; --UNIQUE SCAN 사용 COST 137 -> 2 사용 -- SELECT * FROM USER_MOCK_DATA WHERE EMAIL = 'niacobassi65@shareasale.com'; --UNIQUE SCAN 사용 COST 137 -> 2 사용 -- --일반칼럼에 인덱스 추가 CREATE INDEX USER_MOCK_DATA_CENDER ON USER_MOCK_DATA(GENDER); CREATE INDEX DATA_FIRST_NAME ON USER_MOCK_DATA(FIRST_NAME); -- SELECT * FROM USER_MOCK_DATA WHERE GENDER = 'MALE'; --FULL SCAN -- SELECT * FROM USER_MOCK_DATA WHERE FIRST_NAME LIKE 'R%'; --FULL SCAN --인덱스를 생성했다고 해서 항상 사용하는건 아니다
인덱스를 효율적으로 사용하기 위해서?
- 데이터의 분포도가 높고,
- 조건절에서 자주 활용되며,
- 중복값이 적은 칼럼을 인덱스로 만드는 것이 좋다
1) 조건절에 자주 등장하는 칼럼
2) 항상 =(동등비교) 연산자로 비교되는 칼럼
3) 중복되는 데이터가 최소한이 칼럼
4) ORDER BY 절에 자주 사용되는 칼럼
5) JOIN 조건으로 자주 사용되는 칼럼
인덱스의 장점
1) SELECT, JOIN시 인덱스 칼럼을 사용하면 빠르게 연산이 가능.
2) 인덱스 컬럼 기준 ORDER BY 연산을 사용 할 필요가 없다
3) 인덱스 칼럼을 토애 최소(MIN) 최대(MAX) 값을 찾을때 연산속도가 매우 빠르다.
인덱스의 단점
1) 인덱스가 많을 수로 저장공간을 많이 차지한다.
2) 인덱스를 생성하고 사용하지 않을 수도 있다.
3) DML에 취약. INSERT, UPDATE, DELETE등 데이터의 추가 수정 삭제 하는 경우
인덱스를 다시 정렬해야 하므로 성능이 좋지 못하다.
SELECT ROWID, ID FROM USER_MOCK_DATA; -- ROWID 확인 해보기.
PLSQL
<PL / SQL>
- PROCEDURE LANGUAGE EXTENSTOIN TO SQL
- 오라클에 내장된 절차적 언어
- SQL문장 내에서 변수 정의 , 조건처리(IF) , 반복처리(FOR) , 예외처리등을 지원하여 SQL의 단점을 보완
PL / SQL 구조
-[선언부(DECLARE SECTION)] DECLARE 로 시작, 변수나 상수를 선언 / 초기화 하는 부분. -실행부 (EXCUTABLE SECTION) BEGIN으로 시작, SQL문 또는 제어문 등의 로직을 기술 하는 부분. -예외처리부 (EXCEPTION SECTION) EXCEPTION 으로 시작, 예외 발생시 해결하기 위한 구문을 기술하는 부분.--서버 아웃풋 옵션 키기 SET SERVEROUTPUT ON; BEGIN DBMS_OUTPUT.PUT_LINE('HELLO ORACLE'); END;
1. DECLARE 선언부
변수 및 상수 선언 하는 공간 일반타입 변수, 레어런스 변수, ROW타입 변수 3가지가 존재1_1) 일반타입 변수 선언 및 초기화
변수명 [CONSTANT] 자료형 [: =값];DECLARE EID NUMBER; ENAME VARCHAR2(20); PI CONSTANT NUMBER := 3.14; BEGIN EID := 800; ENAME := '&이름'; DBMS_OUTPUT.PUT_LINE(EID || ENAME || PI); END; /1_2) 레퍼런스 타입 변수 선언 및 초기화
어떤 테이블의 어떤 칼럼의 데이터 타입을 참조 할 것인지 기술
변수명 테이블명.컬럼명%TYPE;DECLARE EID EMPLOYEE.EMP_ID%TYPE; ENAME EMPLOYEE.EMP_NAME%TYPE; SAL EMPLOYEE.SALARY%TYPE; BEGIN --사번이 200번인 사원의 사번 이름 연봉 대입 SELECT EMP_ID, EMP_NAME, SALARY*12 SAL INTO EID, ENAME, SAL FROM EMPLOYEE WHERE EMP_ID = &사번 ; -- DBMS_OUTPUT.PUT_LINE('EID : ' || EID || ' ENAME : ' || ENAME || ' SAL : ' ||SAL); END;레퍼런스 타입 변수로
- EID, ENAME, JCODE, DTITLE을 선언
- 각 변수는
EMPLOYEE(EMP_ID, EMP_NAME, JOB_CODE, SALARY), DEPARTMENT(DEPT_TITLE)을 참조사용자가 입력한 사번인 사원의 사번, 사원명, 직급코드, 급여, 부서명을 조회 후 변수에 담아서 출력
DECLARE EID EMPLOYEE.EMP_ID%TYPE; ENAME EMPLOYEE.EMP_NAME%TYPE; JCODE EMPLOYEE.JOB_CODE%TYPE; SALARY EMPLOYEE.SALARY; DTITLE DEPARTMENT.DEPT_TITLE%TYPE; BEGIN SELECT EMP_ID, EMP_NAME, JOB_CODE, SALARY, DEPT_TITLE INTO EID, ENAME, JCODE, SALARY, DTITLE FROM EMPLOYEE JOIN DEPARTMENT ON DEPT_CODE = DEPT_ID WHERE EMP_ID = &사번; DBMS_OUTPUT.PUT_LINE('EID : ' || EID || ' ENAME : ' || ENAME || ' JCODE : ' || JCODE || ' SALARY : ' || SAL || ' DTITLE : ' || DTITLE ); END; /1_3) ROW타입 변수 선언
- 테이블의 한 행에 대한 모든 칼럼값을 한꺼번에 담을 수 있는 변수.
변수명 테이블명 %ROWTYPE;DECLARE E EMPLOYEE%ROWTYPE; BEGIN SELECT * INTO E FROM EMPLOYEE WHERE EMP_ID = &사번; DBMS_OUTPUT.PUT_LINE ('사원명 : ' || E.EMP_NAME || ' 급여 : ' || E.SALARY); END; /
2. BEGIN 실행부
<조건문> 1) IF 조건식 THEN 실행내용 END IF; --사번을 입력받은 후 해당 사번의 사번 이름 급여 보너스율을 출력 -- 단 보너스를 받지 않은 사원은 보너스를 출력전 '보너스를 지급받지 않은 사원입니다' 를 출력 DECLARE E EMPLOYEE%ROWTYPE; BEGIN SELECT * INTO E FROM EMPLOYEE WHERE EMP_ID = &사번; DBMS_OUTPUT.PUT_LINE(' 사번 : ' || E.EMP_ID || ' 이름 : ' || E.EMP_NAME || ' 급여 : ' || E.SALARY); IF E.BONUS IS NULL THEN DBMS_OUTPUT.PUT_LINE('보너스를 지급받지 않는 사원입니다'); END IF; DBMS_OUTPUT.PUT_LINE (' 보너스 : ' || NVL(E.BONUS, 0) *100 || '%'); END; / -- 2) IF - ELSE 문 --IF 조건식 THEN 실행내용 ELSE 실행내용 END IF; DECLARE E EMPLOYEE%ROWTYPE; BEGIN SELECT * INTO E FROM EMPLOYEE WHERE EMP_ID = &사번; DBMS_OUTPUT.PUT_LINE(' 사번 : ' || E.EMP_ID || ' 이름 : ' || E.EMP_NAME || ' 급여 : ' || E.SALARY); IF E.BONUS IS NULL THEN DBMS_OUTPUT.PUT_LINE('보너스를 지급받지 않는 사원입니다'); ELSE DBMS_OUTPUT.PUT_LINE (' 보너스 : ' || NVL(E.BONUS, 0) *100 || '%'); END IF; END; /--실습문제--
- 래퍼런스 타입 변수 (EID, ENAME, DTITLE, NCODE)
- 참조할 칼럼( EMP_ID, EMP_NAME, DEPT_TITLE, NATIONAL_CODE)
- 일반타입 변수 (TEAM VARCHAR2(20))
- 사용자가 입력한 사번의 사번, 이름 부서명 근무국가코드를 조회후 변수에 대입한다.
- NCODE에 대입된 값이
KO일 경우 TEAM변수에 '한국팀'을 대입
KO가 아닌결우 TERM변수에 '해외팀'을 대입- 조회된 사원의 사번 이름 부서 TEAM을 출력
DECLARE TEAM VARCHAR2(10); EID EMPLOYEE.EMP_ID%TYPE; ENAME EMPLOYEE.EMP_NAME%TYPE; DTITLE DEPARTMENT.DEPT_TITLE%TYPE; NCODE LOCATION.NATIONAL_CODE%TYPE; BEGIN SELECT EMP_ID, EMP_NAME,DEPT_TITLE,NATIONAL_CODE INTO EID,ENAME,DTITLE,NCODE FROM EMPLOYEE,DEPARTMENT,LOCATION WHERE DEPT_ID = DEPT_CODE AND LOCATION_ID = LOCAL_CODE AND EMP_ID = &사번; -- DBMS_OUTPUT.PUT_LINE('EID : ' || EID || ' ENAME : ' || ENAME || ' DTITLE : ' || DTITLE || ' NCODE : ' || NCODE ); IF NCODE = 'KO' THEN TEAM := '한국팀'; ELSE TEAM := '해외팀'; END IF; -- DBMS_OUTPUT.PUT_LINE('TEAM : ' || TEAM ); DBMS_OUTPUT.PUT_LINE('EID : ' || EID || ' ENAME : ' || ENAME || ' DTITLE : ' || DTITLE || ' NCODE : ' || NCODE || 'TEAM : ' || TEAM ); END; / 3) IF 조건식1 THEN 실행내용1 ELSIF 조건식2 THEN 실행내용2 ELSIF 실행내용3 END IF; -- 다음 조건을 만족하는 쿼리문 작성 -- 급여가 500만원 이상이면 고급 -- 300만원 이상이면 중급 -- 그외 초급 --"해당사원의 급여 등급은 xx입니다." DECLARE SAL EMPLOYEE.SALARY%TYPE; GRADE VARCHAR2(6) ; BEGIN --내가 조회한 사원의 급여, 급여등급을 출력 SELECT SALARY INTO SAL FROM EMPLOYEE -- JOIN SAL_GRADE USING(SAL_LEVEL) WHERE EMP_ID = &EMP_ID; IF SAL >= '5000000' THEN GRADE := '고급'; ELSIF SAL >= '3000000' THEN GRADE := '중급'; ELSIF SAL >= '1000000' THEN GRADE := '초급'; END IF ; -- 급여가 500만원 이상이면 고급 -- 300만원 이상이면 중급 -- 그외 초급 --"해당사원의 급여 등급은 xx입니다." DBMS_OUTPUT.PUT_LINE('해당사원의 급여 등급은 ' || SAL || ' 이며 급여 등급은 ' || GRADE || '입니다'); END; /반복문
1) BASIC LOOP문 LOOP 반복적으로 실행할 구문; -- *반복을 빠져나가술 있는 구문. (1) IF 조건문 THEN EXIT; END IF; (2) EXIT WHEN 조건식; END LOOP; -- -- 1부터 5까지 증가하는 값을 출력하는 반복문 DECLARE I NUMBER := 1; BEGIN LOOP DBMS_OUTPUT.PUT_LINE(I); I := I + 1; -- 증감식 -- 1) IF 활용한 조건식 IF I = 6 THEN EXIT; END IF; -- 2) EXIT WHEN 활용한 조건식 EXIT WHEN I = 6; END LOOP; END; / 2) FOR LOOP문 --[REVERSE] 역순으로 할때 FOR 변수 IN [REVERSE]초기값 .. 최종값 LOOP 반복적으로 수행할 구문; END LOOP; --1 부터 15까지 증가값 출력문 BEGIN FOR I IN 1 .. 15 LOOP DBMS_OUTPUT.PUT_LINE(I); END LOOP; END; / -- 15부터 1까지의 감소값 출력문 BEGIN FOR I IN REVERSE 1 .. 15 --역순 LOOP DBMS_OUTPUT.PUT_LINE(I); END LOOP; END; / -- CREATE TABLE TEST_PL(NO NUMBER PRIMARY KEY,TDATE DATE); CREATE SEQUENCE SEQ_TNO; BEGIN FOR I IN 1..10000 LOOP INSERT INTO TEST_PL VALUES( SEQ_TNO.NEXTVAL, SYSDATE); END LOOP; END; / -- SELECT * FROM TEST_PL ORDER BY TNO; -- -- FOR LOOP 서브쿼리문 -- BEGIN FOR EMP IN ( SELECT EMP_ID, EMP_NAME, JOB_NAME FROM EMPLOYEE JOIN JOB USING (JOB_CODE) ) LOOP DBMS_OUTPUT.PUT_LINE( EMP.EMP_ID || ' , ' || EMP.EMP_NAME || ' , ' || EMP.JOB_NAME ); END LOOP; END; / -- -- 중첩 반복문 -- 외부 / 내부 반복문 -- 외부 반복문에서는 EMPLOYEE 테이블에서 사원의 사번과 , 부서코드를 조회 -- 내부 반복문에서는 현재 행의 부서코드 정보를 통해, DEPARTMENT 테이블에서 부서이름을 조회 BEGIN FOR OUTER IN (SELECT EMP_ID, DEPT_CODE FROM EMPLOYEE) LOOP FOR INNER IN (SELECT DEPT_TITLE FROM DEPARTMENT WHERE OUTER.DEPT_CODE = DEPT_ID) LOOP DBMS_OUTPUT.PUT_LINE(OUTER.EMP_ID || ' , ' || INNER.DEPT_TITLE); END LOOP; END LOOP; END; / -- -- 3) WHILE LOOP 문 WHILE 반복문이 수행될 조건 LOOP 반복적으로 실행시킬 구문. END LOOP; -- -- 1~5까지 증가하는 반복문 DECLARE I NUMBER := 1; BEGIN WHILE 1 < 6 LOOP DBMS_OUTPUT.PUT_LINE(I); I := I + 1; --증감식 END LOOP; END; /
실습문제 풀이
--실습문제 1 : 사원의 연봉을 구하는 PL/SQL 블럭 작성
보너스가 있는 사원은 보너스도 포함하여 계산
--출력예시 : xx사원의 연봉은 XX입니다ㅓ
DECLARE
EID EMPLOYEE.EMP_ID%TYPE;
ENAME EMPLOYEE.EMP_NAME%TYPE;
SAL EMPLOYEE.SALARY%TYPE;
BNS EMPLOYEE.BONUS%TYPE;
YSAL NUMBER; -- 연봉
BEGIN
-- 사원 정보 조회
SELECT
EMP_NAME, SALARY, BONUS
INTO
ENAME, SAL, BNS
FROM
EMPLOYEE
WHERE
EMP_ID = &사번;
-- 연봉 계산 (12개월 급여 + 보너스)
YSAL := (SAL + SAL * NVL(BNS, 0)) *12;
DBMS_OUTPUT.PUT_LINE(ENAME || ' 사원의 연봉은 ' || YSAL || '입니다.');
END;
/
--실습문제 2 : 구구단 짝수단 출력
--2_1) FOR LOOP 으로 작성
BEGIN
FOR I IN 2..9 LOOP
IF MOD(I, 2) = 0 THEN -- 짝수 단인지 확인
DBMS_OUTPUT.PUT_LINE(I || '단 : ');
FOR J IN 1..9 LOOP
DBMS_OUTPUT.PUT_LINE(I || ' x ' || J || ' = ' || ( I * J ));
END LOOP;
END IF;
END LOOP;
END;
/
--2_2) WHILE LOOP 으로 작성
DECLARE
I NUMBER := 2;
BEGIN
WHILE i <= 9 LOOP
IF MOD(i, 2) = 0 THEN
DBMS_OUTPUT.PUT_LINE( I || '단 : ');
DECLARE
J NUMBER := 1;
BEGIN
WHILE J <= 9 LOOP
DBMS_OUTPUT.PUT_LINE(I || ' x ' || J || ' = ' || ( I * J ));
J := J + 1; -- j 증가
END LOOP;
END;
END IF;
I := I + 1; -- i 증가
END LOOP;
END;
/ PROCEDURE
- PL/SQL "저장"해서 이용하는 객체
- 저장된 PL/SQL 문을 필요 할 때마다 호출하여 사용 가능.
프로시져 생성방법 CREATE PROCEDURE 프로시저명 [(매개변수)] IS PL/SQL문 프로시져 실행방법 EXEC 프로시져명[(매개변수)]-- EMPLOYEE 테이블을 복사한 COPY테이블 생성 CREATE TABLE PRO_TEST AS SELECT * FROM EMPLOYEE; -- -- 프로시져 생성 CREATE PROCEDURE DEL_DATA IS --DECLARE 구문의 대체 BEGIN DELETE FROM PRO_TEST; COMMIT; END; / EXEC DEL_DATA; SELECT * FROM PRO_TEST; -- 데이터값 확인 아무것도 없다 -- -- 프로시져에 매개변수 추가하기 -- IN : 프로시져에 필요한 값을 받는 변수(자바에서의 매개변수 개념과 동일) -- OUT : 프로시져 호출 후 값을 반환 받는 변수(결과값->매개변수로) -- CREATE OR REPLACE PROCEDURE PRO_SELECT_EMP(V_EMP_ID IN EMPLOYEE.EMP_ID%TYPE, -- V_EMP_NAME OUT EMPLOYEE.EMP_NAME%TYPE, -- V_SALARY OUT EMPLOYEE.SALARY%TYPE, -- V_BONUS OUT EMPLOYEE.BONUS%TYPE) IS BEGIN SELECT EMP_NAME, SALARY, BONUS INTO V_EMP_NAME, V_SALARY, V_BONUS FROM EMPLOYEE WHERE EMP_ID = V_EMP_ID; END; / -- -- 매개변수가 있는 프로시져 실행하기 -- VAR EMP_NAME VARCHAR2 (20); VAR SALARY NUMBER; VAR BONUS NUMBER; -- EXEC PRO_SELECT_EMP( 200, :EMP_NAME, :SALARY, :BONUS ) ; -- IN사용한 경우는 값을 바로 리터럴 하고 -- OUT를 사용한 매개변수의 경우 직접 리터럴 해주고 :뒤에 컬럼명을 입력해준다 -- PRINT EMP_NAME; --내용확인 방법 PRINT 명령어 PRINT SALARY; PRINT BONUS;
※프로시져 장점
- 처리속도가 빠르다.
- 대용량 데이터 처리시 효율이 좋다
※프로시져 단점(알맞는 곳에 잘 사용해야한다?)
- 관리적 측면에서 소스코드를 형상관리하지 힘들다.
- DB자원을 직접 사용하기 때문 DB에 부하를 준다.
FUNCTION
프로시져와 유사하지만 실행결과를 반환 받을 수 있다
-- FUNCTION 생성방법 -- CREATE FUNCTION 함수명(매개변수) RETURN 자료형 IS 변수 선언부 BEGIN 실행부 END; /CREATE OR REPLACE FUNCTION MYFUNC(V_STR VARCHAR2) RETURN NUMBER IS RESULT NUMBER; BEGIN RESULT := LENGTH(V_STR) -1; RETURN RESULT; END; / -- SELECT MYFUNC ('밍경밍') FROM DUAL; --내가 만든함수가 사용되어 데이터 출력 -- -- EMP_ID 값을 전달 받아 연봉을 계산하여 출력해주는 함수 만들기. CREATE OR REPLACE FUNCTION CALC_SALARY(V_EMP_ID EMPLOYEE.EMP_ID%TYPE) RETURN NUMBER IS E EMPLOYEE%ROWTYPE; RESULT NUMBER; BEGIN SELECT * INTO E FROM EMPLOYEE WHERE EMP_ID = V_EMP_ID; -- RESULT := (E.SALARY * NVL(E.BONUS, 0) + E.SALARY) *12; RETURN RESULT; END; / -- SELECT EMP_ID, EMP_NAME, CALC_SALARY( EMP_ID) FROM EMPLOYEE;
TRIGGER(트리거)
내가 지정한 트리거로 지정한 테이블에 INSERTM UPDATE, DELETE 문이 실행될 떄
자동으로 매번 실행 할 내용을 미리 정의 할 수 있는 객체트리거의 종류
SQL 실행 시기에 따른 분류
1). BEFORE TRIGGER
내가 지정한 테이블에 DML 이벤트(INTERT, UPDATE, DELETE)가 발생 되기 전에 실행
2). AFTER TRIGGER
DML이벤트가 발생 된 후에 실행SQL문에 의해 영향을 받는 각 행에 따른 분류
1). STATEMENT TRIGGER
이벤트가 발생한 SQL문에 대해 한번만 실행하는 트리거
2). ROW TRIGGER
SQL문이 실행 할 떄 마다 반복적으로 실행되는 트리거사용가능 옵션
1). :OLD -> BEFORE 트리거에서 사용 가능한 예약어 (이전값)
2). :NEW -> AFRER 트리고에서 사용가능한 예약어 (새로운값)트리거 생성구문 [표현법] CREATE TRIGGER 트리거명 BEFORE | AFTER INSERT | DELETE | UPDATE ON 테이블명 [FOR EACH ROW] -- 매행 실행을 위한 명령어 PL/SQL 문; --EMPLOYEE 테이블에 새로운 행 추가될때 마다 환영메세지를 자동출력하는 트리거정의 CREATE OR REPLACE TRIGGER TRG_01 AFTER INSERT ON EMPLOYEE FOR EACH ROW --이 명령문이 있어야 컴파일이 가능 했다. BEGIN DBMS_OUTPUT.PUT_LINE( :NEW.EMP_NAME || '님 환영합니다.'); END; / INSERT INTO EMPLOYEE (EMP_ID, EMP_NAME, EMP_NO, JOB_CODE, SAL_LEVEL) VALUES(SEQ_EID.NEXTVAL, '김개똥', '1234', 'J1', 'S1'); -- 첫번째 샘플 트리거 생성 -- DELETE FROM TB_PRODUCT WHERE PCODE IN (200, 205, 210); COMMIT; -- -- 1. 상품에 대한 데이터 보관할 테이블(TB_PRODUCT) CREATE TABLE TB_PRODUCT( PCODE NUMBER PRIMARY KEY, -- 상품번호 PNAME VARCHAR2(30) NOT NULL, -- 상품명 BRAND VARCHAR2(30) NOT NULL, -- 브랜드명 PRICE NUMBER, -- 가격 STOCK NUMBER DEFAULT 0 -- 재고수량 ); -- -- 상품번호 중복 안되게끔 매번 새로운 번호를 발생시키는 시퀀스 생성(SEQ_PCODE) CREATE SEQUENCE SEQ_PCODE START WITH 200 INCREMENT BY 5 NOCACHE; -- -- 샘플데이터 추가하기 INSERT INTO TB_PRODUCT VALUES(SEQ_PCODE.NEXTVAL, '갤럭시Z플립4','삼성',1350000,DEFAULT); INSERT INTO TB_PRODUCT VALUES(SEQ_PCODE.NEXTVAL, '갤럭시S10','삼성',1350000,10); INSERT INTO TB_PRODUCT VALUES(SEQ_PCODE.NEXTVAL, '아이폰13','애플',1500000,20); SELECT * FROM TB_PRODUCT; COMMIT; -- -- 2. 상품 입출고 상세 이력 테이블(TB_PRODETAIL) -- 어떤 상품이 어떤 날짜에 몇개가 입고 또는 출고가 되었는지에 대한 데이터를 기록하는 테이블. CREATE TABLE TB_PRODETAIL( DCODE NUMBER PRIMARY KEY, -- 이력번호 PCODE NUMBER REFERENCES TB_PRODUCT, -- 상품번호 PDATE DATE NOT NULL, -- 상품입출고일 AMOUNT NUMBER NOT NULL, -- 입출고 수량 STATUS CHAR(6) CHECK(STATUS IN ('입고','출고')) -- 상태(입고 , 출고) ); -- 이력번호로 매번 새로운 번호 발생시켜서 들어갈수 있게 도와주는 시퀸스(SEQ_DCODE) CREATE SEQUENCE SEQ_DCODE NOCACHE; -- SELECT * FROM TB_PRODETAIL; --확인명령문 -- 215번 상품을 오늘 날짜로 10개 입고 INSERT INTO TB_PRODETAIL VALUES (SEQ_DCODE.NEXTVAL, 215, SYSDATE, 10, '입고'); -- UPDATE TB_PRODUCT SET STOCK = STOCK + 10 WHERE PCODE = 215; -- COMMIT;TB_PRODETAIL 테이블에 INSERT 이벤트 발생 후 ,
TB_PRODUCT 테이블에 재고수량이 자동 UPDATE 되도록 트리거를 정의.
- INSERTE된 행의 상태값이 입고인 경우와 출고인 경우 나눠서 코드가 실행되어야 한다.
CREATE OR REPLACE TRIGGER TRG_02 AFTER INSERT ON TB_PRODETAIL FOR EACH ROW BEGIN --상품이 입고된 경우 -> 재고수량 증가 IF :NEW.STATUS = '입고' THEN UPDATE TB_PRODUCT SET STOCK = STOCK + :NEW.AMOUNT WHERE PCODE = :NEW.PCODE; ELSE UPDATE TB_PRODUCT SET STOCK = STOCK - :NEW.AMOUNT WHERE PCODE = :NEW.PCODE; END IF; --상품이 출고된 경우 -> 재고수량 감소 END; / -- SELECT * FROM TB_PRODUCT; -- -- 오늘날짜로 가가 220 번 상품 7개 출고 215번 상품 100입고 INSERT INTO TB_PRODETAIL VALUES(SEQ_DCODE.NEXTVAL, 220, SYSDATE, 7, '출고'); INSERT INTO TB_PRODETAIL VALUES(SEQ_DCODE.NEXTVAL, 215, SYSDATE, 100, '입고'); -- 입고 출고를 완료 하는 구문.
※트리거의 장점
1). DB의 관라의 자동화
2). 데이터 추가, 수정, 삭제시 데이터의 자동관리로 DB의 무결성 유지.※트리거 단점
1). 관리적 측면에서 형상 관리가 불가능하기 때문에 관리가 불편하다.
2). 트리거의 남용은 예상하지 못한 상황이 발생 할 수 있다
3). 트리거로 지정된 테이블에 DML작업의 빈번 할 경우 성능상 좋지 못하다
