DB /day20 / 23.09.19(화) / (핀테크) Spring 및 Ai 기반 핀테크 프로젝트 구축

10_VIEW_2.SQL

-- 10_VIEW_2.SQL
/*
# ROWNUM : 조회된 값에 번호를 부여할 때 사용
*/

SELECT ROWNUM, EMPNO, ENAME, HIREDATE
FROM EMP;

SELECT ROWNUM, EMPNO, ENAME, HIREDATE
FROM EMP
ORDER BY HIREDATE;

-- 입사일을 기준으로 오름차순 정렬한 VIEW 생성
CREATE OR REPLACE VIEW HIREDATE_VIEW
AS
SELECT EMPNO, ENAME, HIREDATE
FROM EMP
ORDER BY HIREDATE;

SELECT * FROM HIREDATE_VIEW;

-- 입사일이 빠른 사람 순서로 5명 조회
SELECT ROWNUM, EMPNO, ENAME, HIREDATE
FROM HIREDATE_VIEW
WHERE ROWNUM<=5;

/*
# 인라인 뷰 : 메인쿼리의 SELECT 문의 FROM 절 내부에 사용된 서브쿼리
*/
-- 내부에 있는 서브쿼리가 먼저 진행
SELECT ROWNUM, EMPNO, ENAME, HIREDATE
FROM(SELECT EMPNO, ENAME, HIREDATE
     FROM EMP
     ORDER BY HIREDATE)
WHERE ROWNUM <= 5;

-- #1 QUIZ
-- 각 부서별 최대 급여와 최소 급여를 출력하는 'SAL_MAX_MIN' VIEW 를 생성하세요
DROP VIEW SAL_MAX_MIN;
CREATE OR REPLACE VIEW SAL_MAX_MIN
AS
SELECT D.DNAME "부서명", MAX(E.SAL) "최대 급여" , MIN(E.SAL) "최소 급여"
FROM EMP_COPY E, DEPT_COPY D
WHERE E.DEPTNO = D.DEPTNO
GROUP BY D.DNAME;
SELECT * FROM SAL_MAX_MIN;

-- 인라인 뷰를 사용해서 급여를 많이 많는 순서대로 7명을 출력하세요
CREATE VIEW EMP_SAL_DESC
AS
SELECT *
FROM EMP
ORDER BY SAL DESC;

SELECT ROWNUM, ENAME, SAL
FROM EMP_SAL_DESC
WHERE ROWNUM <= 7;

------------------------------------
SELECT ROWNUM, ENAME, SAL
FROM (SELECT * 
      FROM EMP
      ORDER BY SAL DESC)
WHERE ROWNUM<=7;

---

11_SEQUENCE.SQL

-- 11_SEQUENCE.SQL

/*
# 시퀀스 ( SEQUENCE )
- 테이블 내의 유일한 숫자를 자동으로 생성하는 자동 번호 생성기
- 행을 구분하는 기본키가 유일한 값을 갖도록 태이블 내의 유일한 숫자를 자동으로 생성 가능
CREATE SEQUENCE 시퀀스명
       START WITH            -> 시퀀스 번호의 시작값 지정
       INTREMENT BY          -> 연속적인 시퀀스 번호의 증가치
       MAXVALUE | NOMAXVALUE -> 최댓값 지정
       MINVALUE | NOMINVALUE -> 최솟값 지정
       CYCLE    | NOCYCLE    -> 시퀀스 최댓값 도달 시 시작값에서 다시 시작 여부
       CACHE    | NOCACHE    -> 메모리상의 시퀀스 값 관리
       
- CURRVAL : 현재값 반환
- NEXTVAL : 현재 시퀀스 값의 다음값 반환
- 어떠한 컬럼 내의 값으로 쓰임 / 순차적
*/
-- 시퀀스 객체 삭제 및 생성
DROP SEQUENCE TEST_SEQ;
CREATE SEQUENCE TEST_SEQ
START WITH 1
INCREMENT BY 1;

-- NEXTVAL 로 새로운 값을 생성
SELECT TEST_SEQ.NEXTVAL
FROM DUAL;

-- 시퀀스 객체의 현재 값
SELECT TEST_SEQ.CURRVAL
FROM DUAL;

-- 주의사항 한 번 값을 설정하고나면 이전 값으로 돌아갈 수 없음
-- 처음부터 다시 쓰고 싶다면 삭제 후 다시 써야 함
-- 즉, 모든 레코드가 다시 INSERT 되어야 함
-- 주로 웹사이트의 공지사항 등의 번호 부여에 사용됨


DROP TABLE EMP01 PURGE;
CREATE TABLE EMP01(
EMPNO NUMBER(4) CONSTRAINT PK_SEQ_EMPNO PRIMARY KEY,
ENAME VARCHAR2(10),
HIREDATE DATE
);
DESC EMP01;

-- EMP01 테이블의 EMPNO 컬럼 시퀀스 객체 생성
CREATE SEQUENCE EMP01_EMPNO_SEQ
START WITH 1 
INCREMENT BY 1
MAXVALUE 1000;

-- 데이터 추가
INSERT INTO EMP01 VALUES(EMP01_EMPNO_SEQ.NEXTVAL, 'MAN_A', SYSDATE);
INSERT INTO EMP01 VALUES(EMP01_EMPNO_SEQ.NEXTVAL, 'MAN_B', SYSDATE);

SELECT * FROM EMP01;

DROP SEQUENCE EMP01_EMPNO_SEQ;

/*
# 시퀀스 수정
- ALTER SEQUENCE 는 START WITH 절 빼고, CREATE SEQUENCE 와 구조가 동일
  START WITH 옵션은 변경할 수 없고, 다시 시작하려면 시퀀스를 삭제하고 다시 생성해야 함
*/
-- 연습용 테이블
CREATE TABLE SEQTEST(
SNO NUMBER(2) PRIMARY KEY
);

DESC SEQTEST;

-- SEQTEST 테이블 SNO 컬럼에 적용할 시퀀스 객체 생성
DROP SEQUENCE SEQTEST_SEQ;
CREATE SEQUENCE SEQTEST_SEQ
START WITH 1
INCREMENT BY 1
MAXVALUE 3;

-- 데이터 추가
INSERT INTO SEQTEST VALUES(SEQTEST_SEQ.NEXTVAL);
INSERT INTO SEQTEST VALUES(SEQTEST_SEQ.NEXTVAL);
INSERT INTO SEQTEST VALUES(SEQTEST_SEQ.NEXTVAL);
-- 더 추가하려 하면 에러나옴 
-- 이유 : MAXVALUE = 3 이기 때문
-- 해결방법 : 최댓값 수정 -> ALTER 
SELECT * FROM SEQTEST;

-- 시퀀스 최댓값 수정
ALTER SEQUENCE SEQTEST_SEQ MAXVALUE 10;

DROP TABLE SEQTEST PURGE;
DROP SEQUENCE SEQTEST_SEQ; -- 시퀀스는 휴지통으로 이동하지 않음


-- #1 QUIZ
-- 부서정보를 가지는 테이블을 생성하세요 : 부서번호, 부서명, 지역
-- > 부서번호는 SEQUENCE 를 사용해서 적용합니다(초기값 10, 10씩 증가)
DROP TABLE DEPT_QUIZ PURGE;
CREATE TABLE DEPT_QUIZ(
DEPTNO NUMBER(10) PRIMARY KEY,
DNAME VARCHAR2(10),
LOC VARCHAR2(10)
);

SELECT * FROM DEPT_QUIZ;
DROP SEQUENCE DEPT_QUIZ_SEQ;
CREATE SEQUENCE DEPT_QUIZ_SEQ
START WITH 10
INCREMENT BY 10;

INSERT INTO DEPT_QUIZ VALUES(DEPT_QUIZ_SEQ.NEXTVAL, 'D1','P1');
INSERT INTO DEPT_QUIZ VALUES(DEPT_QUIZ_SEQ.NEXTVAL, 'D2','P2');
INSERT INTO DEPT_QUIZ VALUES(DEPT_QUIZ_SEQ.NEXTVAL, 'D3','P3');
INSERT INTO DEPT_QUIZ VALUES(DEPT_QUIZ_SEQ.NEXTVAL, 'D4','P4');
INSERT INTO DEPT_QUIZ VALUES(DEPT_QUIZ_SEQ.NEXTVAL, 'D5','P5');

SELECT * FROM DEPT_QUIZ;

-- 제약조건 확인
SELECT CONSTRAINT_NAME, CONSTRAINT_TYPE, TABLE_NAME
FROM USER_CONSTRAINTS
WHERE TABLE_NAME IN('DEPT_QUIZ');

---

12_시스템권한.SQL

-- 12_시스템권한.SQL

/*
# 데이터베이스 보안을 위한 권한
- 시스템 권한 / 객체 권한

# 시스템 권한
- 사용자 생성과 제거, DB 접근 및 여러가지 객체를 생성할 수 있는 권한 등으로
  주로 DBA에 의해 부여됨
-- DBA : Database Administration

# 객체 권한
- 테이블, 뷰 등의 객체
*/

/*
# 사용자 생성
- 사용자 계정을 생성하기 위해서는 시스템 권한을 가진 system 으로 접속해야 함

CREATE USER '사용자 이름'
INDENTIFIED BY '사용자 암호'
[ WITH ADMIN OPTION ];
*/

-- SYSTEM 계정 연결

-- TEST 계정 생성
CREATE USER TEST IDENTIFIED BY test1234;

-- 생성된 계정 목록 확인
SELECT * FROM ALL_USERS;

/*
# GRANT
- 사용자에게 시스템 권한을 부여할 때 사용하는 명령어
GRANT PRIVILEGE_NAME, .....
TO USER_NAME
*/
-- SYSTEM 계정 연결
-- CREATE SESSION : 데이터베이스에 접속할 수 있는 권한
GRANT CREATE SESSION TO TEST;

/*
# WITH ADMIN OPTION : 해당 권한을 다른 사용자에게 옮길 수 있음
- 사용자에게 시스템 권한을 WITH ADMIN OPTION 과 함께 부여하면, 
  시스템 권한을 다른 사용자에게도 부여할 수 있음
*/

-- SYSTEM

-- USER01 계정 생성
CREATE USER USER01 IDENTIFIED BY user01;

-- USER02 계정 생성
CREATE USER USER02 IDENTIFIED BY user02;

-- USER02 계정에 연결 권한 부여
GRANT CREATE SESSION TO USER02 WITH ADMIN OPTION;

-- USER02 계정 연결
CONN USER02/user02;

-- USER02 계정으로 USER01 계정에 연결권한 부여
GRANT CREATE SESSION TO USER01;



/*
# 객체 권한 : 특정 객체의 조작을 할 수 있는 권한
- 객체의 소유자는 개체에 대한 모든 권한을 가짐
GRANT PRIVILEAGE_NAME [COLUMN_NAME] | ALL -> 권한
ON OBJECT_NAME | ROLE_NAME | PUBLIC       -> 객체 선택
TO USER_NAME;                             -> 사용자
*/

-- USER01 계정 연결
CONN USER01/user01;

-- SCOTT 게정의 EMP 테이블 조회 -- 사용할 수 없음 -- SYSTEM 에선 가능
SELECT * FROM SCOTT.EMP;
-- - EMP 테이블은 SCOTT 계정의 소유의 테이블 -> 접근 불가


-- SCOTT 계정 연결
CONN SCOTT/tiger;

-- SCOTT 소유의 EMP 테이블을 조회할 수 있는 권한을 USER01 계정에 부여
GRANT SELECT ON EMP TO USER01;


-- USER01 계정 연결 후 조회
-- 자신이 소유한 객체가 아닌 경우에는 그 객체를 소유한 사용자명(스키마)를 반드시 지정해야 한다
-- > 스키마(SCHEMA) : 객체를 소유한 사용자명
SELECT * FROM SCOTT.EMP; -- 조회 가능해짐

/*
# 사용자에게 부여된 권한 조회
- 자신에게 부여된 사용자 권한 정보 조회
>>
SELECT * FROM USER_TAB_PRIVS_RECD;

- 다른 사용자에게 부여한 권한 정보 조회
SELECT * FROM USER_TAB_PRIVS_MADE;
*/
SELECT * FROM USER_TAB_PRIVS_RECD;
SELECT * FROM USER_TAB_PRIVS_MADE;


/*
# REVOKE
- 사용자에게 부여한 객체 권한을 데이터베이스 관리자나 소유자로부터 철회 시 사용
REVOKE PRIVILEAGE_NAME | ALL               -> 철회하는 객체 권한
ON OBJECT_NAME                            -> 객체 지정
FROM USER_NAME | ROLE_NAME | PUBLIC;      -> 부여한 사용자명
*/

-- SCOTT 연결

-- USER01 사용자에게서 EMP 테이블에 대한 SELECT 권한 철회
REVOKE SELECT ON EMP FROM USER01;
-- 권한이 철회되어 EMP 테이블 SELECT 불가

------------------------------------------------------------------

/*
# 롤 ( ROLE )
- 사용자에게 보다 효율적으로 권한을 부여할 수 있도록 여러개의 권한을 묶어 놓은 것

- CONNECT 롤
  > 사용자가 데이터베이스에 접속 가능하도록 하는 시스템 권한을 묶어 놓은 것
  
- RESOURCE 롤
  > 사용자가 객체를 생성할 수 있도록 하는 권한을 묶어 놓은 것
  
- DBA 롤
  > 사용자들이 소유한 데이터베이스 객체를 관리하고
*/


-- SYSTEM 계정 연결
-- USER03 계정 생성
CREATE USER USER03 IDENTIFIED BY user03;

-- USER03 계정에 CONNECT, RESOURCE 권한 부여
GRANT CONNECT, RESOURCE TO USER03;

-- USER03 계정 연결

-- #1 QUIZ
-- DBTEST_A 계정을 생성하고, 기본 2개의 롤 권한( CONNECT, RESOURCE ) 을 부여합니다
-- > 1. 계정 생성
--   2. 생성된 계정에 권한 부여
--   3. ALTER USER 계정명 DEFAULT TABLESPACE USERS; -> 데이터베이스 저장되는 공간 지정
--   4. ALTER USER 계정명 QUOTA UNLIMITED ON USERS; -> TABLESPACE 용량지정
CREATE USER DBTEST_A IDENTIFIED BY a1;
GRANT CONNECT, RESOURCE TO DBTEST_A;
ALTER USER DBTEST_A DEFAULT TABLESPACE USERS;
ALTER USER DBTEST_A QUOTA UNLIMITED ON USERS;

SELECT * FROM ALL_USERS;
-- DBTEST_A 계정에 회원 정보를 관리하는 MEMBER 테이블 생성
-- > SEQ        - 회원수    : 시퀀스 적용
--   ID(30)     - 회원 ID   : 중복 X, NULL 값 사용 불가 PK
--   NAME(30)   - 회원 이름 : NULL 값 사용 불가, NN
--   AGE(3)     - 회원 나이
--   HEIGHT     - 회원 키   : 전체 10자리, 소수점 2번째 짜리까지 가능
--   LOGTIME    - 생성일자
DROP SEQUENCE MEMBER_SEQ;
CREATE SEQUENCE MEMBER_SEQ
START WITH 1
INCREMENT BY 1
NOCYCLE
NOCACHE;

DROP TABLE MEMBER PURGE;
CREATE TABLE MEMBER(
SEQ NUMBER(2) CONSTRAINT NN_MEMBER_SEQ NOT NULL,
ID VARCHAR2(30) CONSTRAINT PK_MEMBER_ID PRIMARY KEY,
NAME VARCHAR2(30) CONSTRAINT NN_MEMBER_NAME NOT NULL,
AGE NUMBER(3),
HEIGHT NUMBER(10,2),
LOGTIME DATE
);

-- MEMBER 테이블에 데이터 추가, 삭제
INSERT INTO MEMBER VALUES(MEMBER_SEQ.NEXTVAL,'ID1','MAN_A',22,123.4,SYSDATE);
INSERT INTO MEMBER VALUES(MEMBER_SEQ.NEXTVAL,'ID2','MAN_B',22,123.4,SYSDATE);
INSERT INTO MEMBER VALUES(MEMBER_SEQ.NEXTVAL,'ID3','MAN_C',22,123.4,SYSDATE);

SELECT * FROM MEMBER;
DELETE FROM MEMBER
WHERE SEQ = 3;

SELECT CONSTRAINT_NAME, CONSTRAINT_TYPE, TABLE_NAME
FROM USER_CONSTRAINTS
WHERE TABLE_NAME IN('MEMBER');
-- MEMBER 테이블 삭제
DROP TABLE MEMBER PURGE;
-- DBTEST_A 계정 삭제
DROP USER DBTEST_A CASCADE;


SELECT * FROM ALL_USERS;

---

13_PL_SQL.SQL

-- 13_PL_SQL.SQL

/*
# PL/SQL ( Oracle Procedural Language Extension to SQL )
- SQL 문장에서 변수 정의, 조건 처리(IF), 반복 처리(LOOP) 등을 지원하며,
  오라클 자체에 내장되어 있는 절차적 언어로서 SQL 문의 단점을 보완
  
DECLARE ~ BEGIN ~ EXCEPTION ~ END 순서
> 선언부 ( DECLARE SECTION ) : PL/SQL 에서 사용되는 변수, 상수를 선언

> 실행부 ( EXECUTABLE SECTION ) : 절차적 형식으로 SQL 문을 실행할 수 있도록 제어문, 반복문 등을 기술하는 부분
  >> BEGIN 으로 시작
  
> 예외처리 ( EXCEPTION SECTION )
- 예외 : PL/SQL 문이 실행되는 중 발생하는 에러

# PL/SQL 프로그램 작성
- PL/SQL 블록 내에서 한 문장이 종료될 때 마다 세미콜론(;)DMF TKDYD
- END 뒤에 ; 을 사용해서 하나의 블록이 끝났다는 것을 알려줌

- 한 문장 끝날 때 ; 가장 마지막 END 필수 << 끝남을 표시
*/

-- 오라클에서 제공해주는 프로시저를 사용하여 출력해 주는 내용을 화면에 보여주도록 설정
SET SERVEROUTPUT ON;

-- 메시지 출력
-- > 화면 출력을 위해서 PUT_LINE 프로시저를 이용
-- 오라클에서 제공해주는 프로시저로 DBMS_OUTPUT 팩키지에 있음

/
BEGIN 
    DBMS_OUTPUT.PUT_LINE('HI ↱^-^;;');
END;
/


/*
# 변수 선언
- 변수를 선언할 때에는 변수명 다음에 자료형을 기술
*/
-- 변수 선언
VEMPNO NUMBER(4);
VENAME VARCHAR2(10);
-- 변수 값 지정
VEMPNO := 7890;
VENAME := 'TEST';

-- 변수 선언하고 출력
/
DECLARE 
    VEMPNO NUMBER(4);
    VENAME VARCHAR2(10);
BEGIN
    VEMPNO := 7890;
    VENAME := 'TEST';
    DBMS_OUTPUT.PUT_LINE('사번 / 이름');
    DBMS_OUTPUT.PUT_LINE(VEMPNO || ' / ' || VENAME);
END;
/

-- 해당 컬럼에 맞춰서 만들 수 있음

-- 레퍼런스
-- 이전에 선언된 다른 변수 또는 데이터베이스 컬럼에 맞춰 변수 선언
-- 프로시저는 테이블을 원활하게 다루기 위해 존재
-- %TYPE 속성 사용
-- REMPNO, RENAME 변수 EMP 테이블의 해당 컬럼 자료형과 크기를 그대로 참조
REMPNO EMP.EMPNO%TYPE;    
RENAME EMP.ENAME%TYPE;    


/*
# PL/SQL SELECT
- 테이블의 행에서 질의된 값을 변수에 할당시키기 위해 SELECT 문장을 사용
- PL/SQL 의 SELECT 문은 INTO 절이 필요한데, INTO 절에는 데이터를 저장하는 변수를 작성

- SELECT 절에 있는 컬럼은 INTO 절에 있는 변수와 1:1 대응을 하기 때문에
  갯수, 데이터형, 길이가 일치해야 함

SELECT SELECT_LIST
INTO VARIABLE_NAME
FROM TABLE_NAME
WHERE CONDITION;
*/
-- SMITH 사원의 사번, 이름 조회
/
DECLARE
    VEMPNO EMP.EMPNO%TYPE;
    VENAME EMP.ENAME%TYPE;
BEGIN
    DBMS_OUTPUT.PUT_LINE('사번 / 이름');
    DBMS_OUTPUT.PUT_LINE('-----------');
    
    SELECT EMPNO, ENAME
    INTO VEMPNO, VENAME
    FROM EMP
    WHERE ENAME = 'SMITH';
    
    DBMS_OUTPUT.PUT_LINE(VEMPNO || ' / ' || VENAME);
END;
/


/*
# ROWTYPE 레퍼런스 변수
- ROW(행) 단위로 참조하는 자료형으로 만들어진 변수
- 특정 테이블의 컬럼의 갯수와 데이터 형식을 모르더라도 지정할 수 있음
*/


/
DECLARE
    REMP EMP%ROWTYPE;
BEGIN
    SELECT * INTO REMP
    FROM EMP
    WHERE ENAME = 'SMITH';
    
--  DBMS_OUTPUT.PUT_LINE(REMP); ERROR // 컬럼값을 각각 작성
    DBMS_OUTPUT.PUT_LINE('사원번호 : '|| REMP.EMPNO );
    DBMS_OUTPUT.PUT_LINE('사원이름 : '|| REMP.ENAME );
    DBMS_OUTPUT.PUT_LINE('사원급여 : '|| REMP.SAL );
END;
/

/*
# IF - THEN - END IF
IF CONDITION THEN -> 조건문
STATEMENTS;       -> 조건문이 참이면 실행
END IF
*/

SELECT * FROM DEPT;
-- 10	ACCOUNTING	NEW YORK
-- 20	RESEARCH	DALLAS
-- 30	SALES	CHICAGO
-- 40	OPERATIONS	BOSTON
-- 50	DB	KOR
SELECT * FROM EMP;
-- 7369 7499 7521 7566 7654 7698 7782 7839 7844 7900 7902 7934

-- 부서번호를 사용해서 부서명 출력

/
DECLARE
    VEMPNO EMP.EMPNO%TYPE;
    VENAME EMP.ENAME%TYPE;
    VDEPTNO EMP.DEPTNO%TYPE;
    VDNAME DEPT.DNAME%TYPE;
    
BEGIN
    SELECT EMPNO, ENAME, DEPTNO
    INTO VEMPNO, VENAME, VDEPTNO
    FROM EMP
    WHERE EMPNO = 7902;
    
    IF(VDEPTNO = 10) THEN
        VDNAME := 'ACCOUNTING';
    END  IF;
    IF(VDEPTNO = 20) THEN
        VDNAME := 'RESEARCH';
    END  IF;
    IF(VDEPTNO = 30) THEN
        VDNAME := 'SALES';
    END  IF;
    IF(VDEPTNO = 40) THEN
        VDNAME := 'OPERATIONS';
    END  IF;
    
    DBMS_OUTPUT.PUT_LINE('사원번호 / 사원이름 / 부서명');
    DBMS_OUTPUT.PUT_LINE(VEMPNO || ' / ' || VENAME || ' / ' || VDNAME);
END ;
/

/*
# IF THEN ELSIF ~ ELSE ~ END IF
*/

-- 부서번호를 사용해서 부서명 출력

/
DECLARE
    VEMP EMP%ROWTYPE;
    VDNAME DEPT.DNAME%TYPE;
BEGIN
    SELECT *
    INTO VEMP
    FROM EMP
    WHERE EMPNO = 7499;
    
    IF(VEMP.DEPTNO = 10) THEN
        VDNAME := 'ACCOUNTING';
    ELSIF(VEMP.DEPTNO = 20) THEN
        VDNAME := 'RESEARCH';
    ELSIF(VEMP.DEPTNO = 30) THEN
        VDNAME := 'SALES';
    ELSIF(VEMP.DEPTNO = 40) THEN
        VDNAME := 'OPERATIONS';
    END  IF;
    
    DBMS_OUTPUT.PUT_LINE('사원번호 / 사원이름 / 부서명');
    DBMS_OUTPUT.PUT_LINE(VEMP.EMPNO || ' / ' || VEMP.ENAME || ' / ' || VDNAME);
END ;
/