D+49 -Sequence.삭제,Index.생성,검색,삭제,결합인덱스,함수인덱스,권한.유저만들기,유저삭제,접속권한,테이블권한,role부여,동의어.생성,삭제,View.장점,생성,조회,삭제
학원 일기
Table
SEQUENCE
시퀀스 삭제
- 질의 : DROP SEQUENCE 시퀀스명;
DROP SEQUENCE DEPT_TEMP_SEQ;
이런 메세지가 뜨면 성공이다.
INDEX
조회속도 향상을 위해 컬럼에 인덱스를 생성함 컬럼에 인덱스를 지정해서 생성하면 된다. 예를 들어 어떤 사이트에서 이름검색이 검색이 너무 느려 DB에 INDEX를 생성해서 속도를 올릴수 있다.
INDEX가 필요한 상황
- 쿼리 문을 작성할 때 WHERE절에 특정 컬럼이 많이 등장할 경우 인덱스를 생성하면 좋다. 처음부터 만들기 보다는 필요하다고 느낄때 만들어도 된다.
- 행이 10만건 이상이 생기면 INDEX를 만들어 주는 것이 성능에 더 좋다. 그런데 10만건 보다 데이터가 적으면 만드는 것이 INDEX공간을 차지하기에 성능이 더 안 좋을 수 있다.
- 조인에 공통컬럼으로 사용될 경우에는 그 컬럼은 INDEX를 해주는 것이 좋다.
INDEX 생성하기
- 질의 : CREATE INDEX 인덱스 이름 ON 테이블(컬럼);
CREATE INDEX IX_EMPLOYEE_ENAME ON EMPLOYEE(ENAME);
여기서 INDEX를 확인할 수 있다. 우리가 만든 INDEX가 생긴것을 확인 할 수 있다. 이러면 INDEX만 차지하는 공간을 만든뒤 여기에 INDEX를 저장한다.
INDEX 검색하기
테이블에 어떤 INDEX가 있는지를 명령어로 확인이 가능하다.
- 질의 : SELECT *
FROM USER_IND_COLUMNS
WHERE TABLE_NAME IN ('테이블1','테이블2');
USER_IND_COLUMNS은 SQL에서 제공하는 테이블로 INDEX기록을 지닌다.
SELECT *
FROM USER_IND_COLUMNS
WHERE TABLE_NAME IN ('EMPLOYEE','DEPARTMENT');EMPLOYEE와 DEPARTMENT에 어떤 인덱스가 있는지 확인 할 수 있다.
다음과같이 어떤 테이블에 어떤 INDEX가 걸려 있는 지를 알 수 있다.
INDEX 삭제하기
- 질의 : DROP INDEX 인덱스명;
CREATE INDEX IX_DEOARTMENT_DNAME ON DEPARTMENT(DNAME);
이런 메세지가 뜨면 성공한 것이다.
결합 INDEX
컬럼 여러개를 동시에 인덱스 1개로 생성 할 수 있다.
예를 들어
SELECT * FROM DEPARTMENT
WHERE DNAME = 'SALES'
AND LOC = 'NEW YORK'위 두 컬럼에 대한 인덱스를 만들려면 원래는 각 컬럼에 대한 2개의 INDEX를 만들어야 했다.
하지만 결합 INDEX를 사용하면 하나의 INDEX문으로 두개의 컬럼을 INDEX시킬 수 있다.
- 질의 : CREATE INDEX 인덱스명 ON 테이블명(컬럼, 컬럼2, ...);
CREATE INDEX IX_DEPARTMENT_DNAME_LOC ON DEPARTMENT(DNAME, LOC);결합인덱스는 WHERE절에서 AND 로 두개의 컬럼이 나올때 사용하면 된다.
함수 INDEX
SQL함수 또는 산술식에 인덱스를 걸 수 있다.
다음과 같은 쿼리문이 있다고 하자.
SELECT * FROM EMPLOYEE
WHERE SALARY = SALARY * 12;여기서 SALARY * 12을 인덱스로 만들어보자
CREATE INDEX IX_EMPLOYEE_ANNSAL ON EMPLOYEE(SALARY * 12);이렇게 해주면 INDEX가 완성된다.
권한
유저 만들기
기본 관리자
ORACLE DB의 기본 관리자는 SYSTEM이다. 계정생성, 권한주기 등 거의 대부분의 작업을 수행한다.
유저 만들기
유저를 만들고 암호를 지정해보자.
- 질의 : CREATE USER 유저이름 IDENTIFIED BY 패스워드;
CREATE USER usertest01 IDENTIFIED BY pass1;반드시 관리자 계정으로 만들어야한다.
다음과 같은 메세지가 뜨면 성공이다.
유저 삭제
- 질의 : DROP USER 유저명;
DROP USER usertest01;
이 메세지가 뜨면 삭제가 완료된다. 단 삭제전 접속을 해제해야한다.
권한 부여
GRANT CREATE SESSION 접속권한 주기
위에서 만든 유저에 접속권한을 부여해보자.
- 질의 : GRANT CREATE SESSION TO 유저명;
GRANT CREATE SESSION TO usertest01; -- 접속권한 GREATE SESSION
다음과 같은 메세지가 뜨면 접속권한이 주어진 것이다.
GRANT CREATE TABLE 테이블 생성 권한 주기
위에서 만든 유저에 테이블 생성 권한을 부여해보자.
- 질의 : GRANT CREATE TABLE TO 유저명;
GRANT CREATE TABLE TO usertest01; -- TABLE 만드는 권한 주기다음과 같은 메세지가 뜨면 테이블 생성 권한이 주어진 것이다.
GRANT UNLIMITED TABLESPACE 공간사용 권한 주기
위에서 만든 유저에 공간사용 권한을 부여해보자.
- 질의 : GRANT UNLIMITED TABLESPACE TO 유저명;
GGRANT UNLIMITED TABLESPACE TO usertest01;다음과 같은 메세지가 뜨면 공간 사용 권한이 주어진 것이다.
ROLE
이렇게 권한들을 하나 하나 부여하면 너무 비효율적이다. 그래서 한번에 여러권한을 줄 수 있게 만든 기능이 있는데 이게 ROLE이다. 이걸 부여하면 관련된 권한들을 모두 한번에 부여가 가능하다.
ROLE 종류
- 접속 권한들의 집합 : CONNECT
- 테이블,인덱스,함수등을 사용할수 있는 권한집합 : RESOURCE
- View 생성 권한 : CREATE VIEW 권한
ROEL 부여하기
- 질의 : GRANT CONNECT, RESOURCE, CREATE VIEW TO usertest01;
GRANT CONNECT, RESOURCE, CREATE VIEW TO usertest01;
이 메세지가 뜨면 role부여가 된 것이다.
동의어
다른 계정의 테이블이나 인덱스를 사용하려면 "FROM 계정.테이블" 이렇게 불러와야한다. 하지만 너무 길기 때문에 별명을 붙여서 별명으로만 사용을 할 수 있다.
관리자 계정으로 테이블을 생성해서 다른 계정으로 테이블을 조회해보자.
관리자 계정에서 테이블 생성
SAMPLETBL이라는 이름의 테이블을 만들었다.
다른 계정에서 조회하기
다른 계정에서 테이블을 조회하려면 권한부여를 먼저 해줘야한다.
조회권한 및 동의어 생성 권한 부여
- 질의 : GRANT SELECT ON 테이블 TO 유저;
GRANT CREATE SYNONYM TO 유저;
GRANT SELECT ON SAMPLETBL TO scott; -- 조회권한
GRANT CREATE SYNONYM TO scott; -- 동의어 생성 권한
SCOTT계정에서 관리자 계정의 테이블 조회가 가능하다.
동의어 생성하기
- 질의 : CREATE SYNONYM 바꿀새이름 FOR 권한.테이블명;
CREATE SYNONYM SAMPLETBL FOR SYSTEM.SAMPLETBL;-- 동의어(별명) 생성
SELECT * FROM SAMPLETBL; -- 동의어 조회
새로 만든 이름으로도 조회가 가능해진다.
동의어 삭제
- 질의 : DROP SYNONYM 동의어;
DROP SYNONYM SAMPLETBL;
삭제되었다.
VIEW
VIEW란
보안을 유지하기 위해 1개 이상의 테이블이나 다른 뷰를 이용해서 생성하는 가상의 테이블이다.
예를 들어 사원 테이블에 급여는 다른 사람이 봐서는 안된다. 이때 VIEW를 이용하여 가상의 테이블을 만들어 작업을 하면 SALARY를 노출시키지 않고 작업을 할 수 있다.
VIEW 생성하기
VIEW 만드는 방법
- 질의 : CREATE OR REPLACE VIEW 뷰이름(보여주고싶은 컬럼들 별명)
AS
SELECT 보여주고싶은 컬럼들
FROM 가져올 테이블
WHERE 보여줄 VIEW만 조건
CREATE OR REPLACE VIEW VW_EMP_JOB(ENO, ENAME, DNO, JOB)
AS
SELECT ENO, ENAME, DNO, JOB
FROM EMPLOYEE
WHERE JOB LIKE 'SALESMAN%'급여 컬럼을 없애고 직무가 SALESMAN인 행만 보여주자.
이 메세지가 뜨면 성공이고 뷰파일에 위치된다.
VIEW 조회하기
- 질의 : SELECT * FROM 뷰이름;
SELECT * FROM VW_EMP_JOB;
또 어떤 테이블에 view가 있는지를 알 수 있는 방법이 있다.
SELECT * FROM USER_VIEWSuser_views 테이블에 view 데이터들이 저장된다.
VIEW에 INSERT 금지시키기
참고로 INSERT도 가능하다. 단 WITH READ ONLY를 사용하면 INSERT는 부가능하다.
CREATE OR REPLACE VIEW VW_EMP_JOB_READ
AS
SELECT ENO, ENAME, DNO, JOB
FROM EMPLOYEE
WHERE JOB LIKE '%MANAGER%' WITH READ ONLY;이런식으로 하면 INSERT가 불가하다.
VIEW 그룹함수 만들기
CREATE OR REPLACE VIEW VW_EMP_SALARY
AS
SELECT DNO, SUM(SALARY) AS SAL_SUM, ROUND(AVG(SALARY)) AS SAL_AVG
FROM EMPLOYEE
GROUP BY DNO;
SQL 그룹함수처럼 VIEW도 똑같이 그룹함수를 만들면된다.
VIEW 삭제
- 질의 : DROP VIEW 뷰이름;
DROP VIEW VW_EMP_SALARY;이 메세지가 있으면 삭제가 잘 된다.
Prosedure
주로 중급자들이 하고 난이도가 굉장히 높아서 내가 당장 할 일은 없지만 침고만 하자.
처리 속도가 빠르고 제어문, 변수들이 있다.
-- 26_Procedure.sql
-- DB 프로그래밍 분야 : sql 숙련자들이 코딩함
-- 중급 개발자가 주로 개발함
-- 난이도 높음
-- 소스 : Oracle DB 내에 저장됨
-- 장점 : 1) 처리속도 빠름
-- 특징 : 1) 제어문(조건문/반복문), 변수/상수
-- 프로시저 정의 (3단계)
-- 1) 선언부 : 변수 정의 , 변수값 받기(매개변수)
-- 2) 실행부(BEGIN) : 본격적인 코딩
-- 3) 예외처리(EXCEPTION) : 예외 블럭 처리
-- 4) 종료 : END , 프로시저 끝
-- 1) 화면 출력 모드 ON
SET SERVEROUTPUT ON;
-- 예제 1) 매개변수 2개의 합을 출력
-- in : 매개변수의 의미
-- 사용법) CREATE OR REPLACE PROCEDURE 프로시저명(
-- 매개변수 in 자료형,
-- ...
-- )
-- IS
-- 변수 자료형(자리수); -- 선언부
-- BEGIN
-- 실제 코딩
-- END;
-- /
-- 화면출력 함수 : dbms_output.put_line('문자열')
CREATE OR REPLACE PROCEDURE pro_exam01
(
-- 매개변수 부분 : 함수 매개변수와 비슷
p_num1 in NUMBER,
p_num2 in NUMBER
)
IS
-- 선언부 : 변수정의
v_sum NUMBER(10);
BEGIN
-- 실행블럭 : 본격 코딩
-- 합 계산 : v_sum := p_num1 + p_num2
-- := : 대입연산자
v_sum := p_num1 + p_num2;
-- db 출력 : 1줄 출력
-- || : 문자열 붙이기 연산자
-- 사용법) dbms_output.put_line(변수)
dbms_output.put_line('총합은 : ' || v_sum);
END;
/
-- 프로시저 실행 방법
-- 사용법) CALL 프로시저명(매개변수, 매개변수2,...);
CALL pro_exam01(1,2);
-- 예제 2) 입력받은 사원번호에 해당하는 월급 출력하기 : 사원테이블
-- 입력값 : 7788
CREATE OR REPLACE PROCEDURE pro_exam02
(
p_num1 in NUMBER -- 매개변수 (7788)
)
IS
v_sal NUMBER(10); -- 결과 저장 변수
BEGIN
-- 입력받은 사원번호에(ENO) 해당하는 월급(SALARY) 출력
-- 사용법) SELECT 컬럼명 INTO 변수 : 컬럼값 -> 변수에 저장됨
SELECT SALARY INTO v_sal
FROM EMPLOYEE
WHERE ENO = p_num1;
dbms_output.put_line('월급은 : ' || v_sal);
END;
/
-- 프로시저 실행 방법
CALL pro_exam02(7788);
-- 예제 3) 조건문 : IF/ELSIF/ELSE/END IF;
CREATE OR REPLACE PROCEDURE pro_exam03
(
p_num1 in NUMBER -- 매개변수
)
IS
BEGIN
-- 입력값에 따라 짝수/홀수 인지 출력하세요 : MOD(값, 나누기) => 결과:나머지
-- =(등호) : 비교연산자 , 같다
IF MOD(p_num1, 2) = 0 THEN
dbms_output.put_line('짝수');
ELSE
dbms_output.put_line('홀수');
END IF;
END;
/
-- 프로시저 실행 방법
CALL pro_exam03(2);
-- 예제 4) 반복문 : 커서(cursor)
-- 커서(cursor) : select 문의 결과로 여러건을 가지고 있는 변수
CREATE OR REPLACE PROCEDURE pro_exam04
(
p_num1 in NUMBER -- 매개변수
)
IS
-- 선언부
-- 커서 정의 : 배열과 비슷, select 문의 결과
-- 사용법) CURSOR 커서변수명 IS SELECT ~ 문
CURSOR emp_cursor IS
SELECT ENAME, SALARY, DNO
FROM EMPLOYEE
WHERE DNO = p_num1; -- 부서번호(매개변수)
BEGIN
-- FOR문 : 반복문
-- 자바의 향상된 FOR 문 과 비슷 : 예) for(자료형 변수 : 배열){}
-- 특징 : 증감식 없음, 데이터 끝에 도달하면 반복문 종료
FOR emp_record IN emp_cursor LOOP
-- 반복문 실행부분
-- emp_record : 커서의 1행만 차례대로 들어감
-- 차례로 화면에 출력하기
DBMS_OUTPUT.PUT_LINE(emp_record.ename || ' ' || emp_record.salary
|| ' ' || emp_record.dno);
END LOOP;
END;
/
-- 프로시저 실행 방법
CALL pro_exam04(20);
-- 예제) 부서번호를 매개변수로 받아서 위치를 출력하는 함수 정의
-- 사용법) CREATE OR REPLACE FUNCTION 평션명(매개변수 in 자료형)
-- RETURN 리턴값자료형
-- 예제 5) 평션 만들기
-- 예제) 부서번호를(DNO) 매개변수로 받아서 부서위치를(LOC) 출력하는 함수 정의
-- 사용법) CREATE OR REPLACE FUNCTION 평션명(매개변수 in 자료형)
-- RETURN 리턴값자료형
-- 예약어 : 테이블명.컬럼명%type => 테이블명에 해당하는 컬럼의 자료형을 가져옴
-- 예) DEPARTMENT.DNO%TYPE => DEPARTMENT 테이블의 DNO 컬럼의 자료형 : NUMBER
CREATE OR REPLACE FUNCTION fn_exam05
(
p_num1 in DEPARTMENT.DNO%type -- 매개변수 자료형 (NUMBER)
)
RETURN DEPARTMENT.LOC%TYPE
IS
-- 선언부
v_loc DEPARTMENT.LOC%TYPE; -- 부서 테이블의 LOC 자료형 참고
BEGIN
-- 부서번호를(DNO) 매개변수로 받아 위치를(LOC) 조회하는 SQL문
SELECT LOC INTO v_loc
FROM DEPARTMENT
WHERE DNO = p_num1; -- 부서번호 매개변수
RETURN v_loc;
END;
/
-- 함수 실행방법 :
SELECT fn_exam05(10) FROM DUAL;