SQL view 생성 및 프로시저

⏰ 2024.10.30 (D+15)

1. VIEW 생성 및 삭제방법

중요 🔖]
- VIEW는 하나 또는 그 이상의 테이블로부터 생성된 가상의 테이블이다.
- 물리적으로 존재하지 않는 테이블
- 데이타베이스의 선택적인 내용을 보여줄 수 있기때문에 데이타베이스에 대한 액세스 제한 가능
- 복잡한 질의어를 통해 얻을 수 있는 결과를 간단할 질의어를 써 구할 수 있다.

참고사항 💡]
하나의 테이블로 만든 VIEW에서는 DML(INSERT,DELETE,UPDATE)문장을 수행 할 수 있지만
여러 테이블로 만든 VIEW(PK와 FK로 조인한)에서는 DML문(INSERT)을 수행 할 수 없다.
단, UPDATE와 DELETE는 가능.

VIEW의 장점 ➕]
(1) 보안성 - 중요한 컬럼을 숨 길수 잇어 보안을 유지 할 수 있다.
(2) 편의성 - 여러 개의 테이블 사용을 위해 복잡한 조인이 사용되었다 하더라도 사용자는 이를 몰라도 된다.
(3) 간결성 - 뷰를 사용하면 APPLICATION을 개발할 때 복잡한 쿼리문을 숨길 수 있어 소스가 간결해 진다.

조건식 ⭐]

VIEW 생성 구문 형식
CREATE [ OR REPLACE] VIEW VIEW명[(컬럼별칭1,컬럼별칭2...)]
별칭 부여시 "" 로 감싸줘라
AS
SELECT 구문
[WITH READ ONLY] --뷰를 읽기전용으로 만들때

📌 VIEW에 들어갈 데이터 조회

SELECT
EMPNO,
ENAME,
JOB,
HIREDATE,
DEPTNO
FROM EMP;

⚙️ 뷰 생성 예시 ①
CREATE OR REPLACE VIEW EMP_VIEW("사원번호","사원명","직무","입사일","부서코드")
AS
SELECT 텍스트
EMPNO,
ENAME,
JOB,
HIREDATE,
DEPTNO
FROM EMP
WITH READ ONLY;

💡 emp_view 뷰 JOB 컬럼 검색
SELECT * FROM emp_view WHERE JOB ='SALESMAN';

⚙️ 뷰 생성 예시 ②
CREATE OR REPLACE VIEW EMP_DEPT_VIEW
AS
SELECT E.*,DNAME, LOC
FROM EMP E
JOIN DEPT D ON E.DEPTNO=D.DEPTNO ORDER BY ENAME
WITH READ ONLY;

⚙️ 뷰 수정
OR REPLACE 사용 : 이미 존재한다면 다시 생성

💡 뷰 검색
SELECT VIEW_NAME,TEXT FROM USER_VIEWS;

SELECT * FROM EMP_DEPT_VIEW;

🗑️ 뷰 삭제
DROP VIEW VIEW명

DROP VIEW EMP_DEPT_VIEW;

2. INDEX 생성 및 삭제 방법

중요 🔖]
- 행의 검색 속도를 향상 시킬 수 있는 객체
- 인덱스를 명시적(CREATE INDEX) 또는 자동적으로(PRIMARY KEY,UNIQUE KEY)로 생성 할 수 있다.
- 컬럼에 대하 인덱스가 없으면 한 테이블 전체를 검색. 즉 인덱스는 쿼리의 성능을 향상 시키는 것이 목적
- INSERT/UPDATE/DELETE 가 많은 컬럼에 대해서 INDEX를 되도록이면 설정하지 말아라
- 인덱스가 많은 것이 항상 좋은 것은 아니다
- 왜냐하면 인덱스를 가진 테이블에 대한 DML작업 은 인덱스도 갱신되어야 함을 의미하기 때문

❓ 어느 컬럼에 인덱스를 설정하는가?
1) WHERE조건이나 조인 조건에서 자주 사용되는 컬럼
2) 광범위한 값을 포함하는 컬럼
3) 많은 NULL값을 포함하는 컬럼
※테이블에 자료의 양이 적거나 자주 갱신 되는 테이블은 오히려 인덱스를 걸지 말아라

필수 사항 ⭐]
※인덱스는 수정 불가 수정시에는 삭제 후 다시 생성

⚙️ 인덱스 생성
CREATE INDEX IDX_EMP ON EMP(DEPTNO, SAL, COMM);

🗑️ 인덱스 삭제
DROP INDEX IDX_EMP;

❗SERVEROUT ON 설정
SET SERVEROUT ON

❗SERVEROUT 설정값 확인
SHOW SERVEROUT

프로시저 예시1 📖]

BEGIN
dbms_output.put_line('HELLO WROLD');
END;
/

프로시저 예시2 📖]

DECLARE
NUM NUMBER;
BEGIN
NUM :=100;
DBMS_OUTPUT.PUT_LINE('NUM의 값:' || NUM);
:NUM := NUM;
END;
/

➖ 변수 선언
VAR NUM NUMBER
PRINT NUM
SELECT :NUM FROM DUAL;

MOD 함수를 사용한 예시 📖]

SELECT MOD(100,2) FROM DUAL;
SELECT MOD(99,2) FROM DUAL;
❌SELECT MOD(20%2) FROM DUAL; 소수점 출력 불가

➖ 변수 선언
VAR NUM NUMBER
VAR PRINT RTVAL

➖ 변수에 값 대입
PRINT RTVAL:=100;

❗SERVEROUT ON 설정
SET SERVEROUT ON

DECLARE
NUM NUMBER;
BEGIN
NUM := 10;
IF MOD(NUM,2) = 0 THEN
DBMS_OUTPUT.PUT_LINE('짝수');
:RTVAL := NUM || '은 짝수';
ELSE
DBMS_OUTPUT.PUT_LINE('홀수');
:RTVAL := NUM || '은 홀수';
END IF;

END;
/

❗ 결과 값 확인하기
PRINT PTVAL
SELECT :RTVAL FROM DUAL;

SYSTEM 계정으로 설정 ⚙️]

1. SCOTT계정 비밀번호 변경 → SCOTT으로 변경
   ALTER USER SCOTT IDENTIFIED BY SCOTT;
   GRANT SELECT ON SCOTT.EMP TO USER02;

2. SCOTT계정 잠그기
   ALTER USER SCOTT ACCOUNT LOCK;

3. SCOTT계정 접속여부 확인
   CONN SCOTT/SCOTT@LOCALHOST/XEPDB1

4. SCOTT계정 잠금 풀기
   ALTER USER SCOTT ACCOUNT UNLOCK;

문제 📙]

➖ 입력창 선언부
ACCEPT KOR PROMPT '국어 점수 입력?'
ACCEPT ENG PROMPT '영어 점수 입력?'
ACCEPT MATH PROMPT '수학 점수 입력?'

➖ 변수 선언부
DECLARE
KOR NUMBER(3) := &KOR;
ENG NUMBER(3) := Ŋ
MATH NUMBER(3);--MATH는 실행부에서 초기화 하여라

➖총합을 저장하는 변수와 평균을 저장하는 변수 선언하여라
SUM NUMBER :=0;
AVG NUMBER;
--SUM 및 AVG는 사용하면 에러.

BEGIN
MATH := &MATH;
SUM := KOR + ENG + MATH;
AVG := SUM / 3;
DBMS_OUTPUT.PUT_LINE('============점수 출력============');
➖ 문) 90점 이상이면 A학점 출력
IF AVG >= 90 THEN
DBMSOUTPUT.PUT_LINE('A학점');
ELSIF AVG >= 80 THEN
DBMSOUTPUT.PUT_LINE('B학점');
ELSIF AVG >= 70 THEN
DBMSOUTPUT.PUT_LINE('C학점');
ELSIF AVG >= 60 THEN
DBMSOUTPUT.PUT_LINE('D학점');
ELSE
DBMS_OUTPUT.PUT_LINE('F학점');
END IF;
DBMS_OUTPUT.PUT_LINE('총점:' || SUM || ',평균:' || AVG_);
END;
/

문제 📙]

❗SERVEROUT ON 설정
SET SERVEROUT ON

➖창을 뜨게 하는 명령문
ACCEPT NUM PROMPT 'PLEASE INPUT NUMBER?'

DECLARE
NUM NUMBER := #

BEGIN
/
문) 입력받은 숫자가 3과 5의 공배수이면 '3과 5의 공배수'
3의 배수이면 '3의 배수'
5의 배수이면 '5의 배수'
3과 5의 배수가 아니면 '3과 5의 배수가 아니다'를 출력하여라
/

💡방법 ① ]
IF MOD(&NUM,15)=0 THEN
DBMS_OUTPUT.PUT_LINE('multiple of 3 and 5');
ELSIF MOD(&NUM,5)=0 THEN
DBMS_OUTPUT.PUT_LINE('multiple of 5');
ELSIF MOD(&NUM,3)=0 THEN
DBMS_OUTPUT.PUT_LINE('multiple of 3');
ELSE
DBMS_OUTPUT.PUT_LINE('Not multiple of 3 and 5');
END IF;

💡방법 ② ]
IF MOD(NUM,3)=0 AND MOD(NUM,5)=0 THEN
DBMS_OUTPUT.PUT_LINE('3과 5의 공배수');
ELSIF MOD(NUM,3)=0 THEN
DBMS_OUTPUT.PUT_LINE('3의 배수');
ELSIF MOD(NUM,5)=0 THEN
DBMS_OUTPUT.PUT_LINE('5의 배수');
ELSE
DBMS_OUTPUT.PUT_LINE('3과 5의 배수가 아니다');
END IF;

END;
/