쌍용교육센터 - 21일

개발자가 되고 싶어요 ㅣ 2024. 3. 18. 20:41

2024.03.18

인덱스(Index)

인덱스는 데이터 검색을 빨리 하기 위해 사용한다. 하지만 인덱스를 생성했다고 해서 데이터 검색이 무조건 빨라지는 것은 아니다. 데이터의 양이 별로 없거나 데이터 값이 몇 종류 안 되어 선택도가 높으면 인덱스가 없는 것이 더 빠를 수 있다.

선택도(selectivity)란 ‘1/서로 다른 값의 개수’를 말하는 것으로, 예를 들어 100개의 행을 가진 테이블에 값이(남,여) 두 가지라면 선택도가 높다고 할 수 있다.

테이블의 컬럼에 대한 제약조건을 설정할 때 Primary Key나 Unique로 설정하면 Oracle은 자동으로 이 컬럼에 대해 Unique Index를 설정한다. 데이터 값이 중복됨이 없이 유일한 값을 가지는 컬럼에 인덱스를 설정했을 때 이를 Unique Index라 한다.

1) 인덱스 만들기

-자동: 테이블 정의에 Primary Key 또는 Unique 제약 조건을 정의하면 고유 인덱스가 자동으로 생성된다.

-수동: 사용자가 열에 고유하지 않은 인덱스를 생성하여 행에 대한 액세스 시간을 줄일 수 있다.

CREATE (제약조건) INDEX index_name ON table_name (column_name[,column_name]...)
- 유일한 값을 가지는 컬럼에 인덱스 설정 : UNIQUE INDEX
CREATE UNIQUE INDEX danme_idx ON dept(dname);

- 유일한 값을 가지지 않는 컬럼에 인덱스 설정: NON UNIQUE INDEX
CREATE INDEX emp_ename_idx ON emp(ename);

2) 인덱스 생성이 필요한 경우

열에 광범위한 값이 포함된 경우
열에 null 값이 많이 포함된 경우
WHERE 절 또는 조인 조건에서 하나 이상의 열이 함께 자주 사용되는 경우
큰 테이블에서 대부분의 질의에 의해 검색되는 행이 2%~4% 미만인 경우

3) 인덱스를 생성하지 않아야 할 경우

테이블이 작은 경우
열이 질의의 조건으로 자주 사용되지 않는 경우
대부분의 질의가 테이블에 있는 행의 2%~4% 이상을 검색할 경우
테이블이 자주 갱신되는 경우
인덱스화된 열이 표현식의 일부로 참조되는 경우

4) 함수 기반 인덱스

함수 기반 인덱스는 표현식을 기반으로 하는 인덱스
인덱스 표현식은 테이블 열, 상수, SQL 함수 및 사용자가 정의한 함수로부터 생성된다.

동의어

동의어(객체의 다른 이름)를 생성하여 객체 액세스를 단순화한다.

동의어 생성
CREATE SYNONYM EMP20 FOR EMP20_VIEW;
SELECT * FROM EMP20;

동의어 삭제
DROP SYNONYM EMP20;

권한

관리자 계정과 사용자 계정

데이터베이스의 계정은 관리자 계정과 일반 사용자 계정으로 나뉜다. 오라클에서 관리자 계정은 SYS, SYSTEM이고 일반 사용자 계정은 관리자 계정을 이용해서 생성하거나 기본 계정의 잠금상태(LOCK)를 해제하여 사용하게 된다.

관리자 계정의 역할

데이터베이스의 생성과 관리를 담당하는 슈퍼유저(SUPER USER)계정이며 데이터베이스 객체의 생성, 변경, 삭제 등의 작업이 가능하다.

사용자 계정의 역할

사용자 계정은 데이터베이스에 접근하여 데이터를 조작(삽입, 삭제, 수정, 검색)하고 관리하는 일을 수행할 수 있는 계정이다.

데이터베이스 보안

시스템 보안
데이터 보안

시스템 권한 : 데이터베이스를 엑세스할 수 있다.

객체 권한 : 데이터베이스 객체의 내용을 조작할 수 있다.

시스템 권한

새 사용자 생성
사용자 제거
테이블 제거
테이블 백업

사용자 생성

CONN sys/1234(비밀번호) AS SYSDBA;

계정 생성
CREATE USER user
IDENTIFIED BY password;

접속권한부여 
GRANT RESOURCE,CONNECT,DBA TO user01;

*DBA: 특정 시스템 권한을 갖고 있는 롤

롤이란?

롤은 사용자에게 부여할 수 있는 관련 권한을 하나로 묶어 명명한 그룹으로서 롤을 사용하면 권한 취소 및 유지 관리를 쉽게 수행할 수 있다.

사용자는 여러 롤을 액세스할 수 있으며 동일한 롤이 여러 사용자에게 할당될 수도 있다.

롤 생성 및 권한 부여

롤 생성

CREATE ROLE manager;

롤에 권한을 부여

GRANT create table, create view
TO manager;

사용자에게 롤을 부여

GRANT manager TO 사용자1, 사용자2;

암호 변경

DBA는 사용자 계정을 생성하고 암호를 초기화한다.

ALTER USER username
IDENTIFIED BY newpassword;

사용자 삭제

DROP USER username;

객체 권한

객체 권한은 유저가 소유하고 있는 특정한 객체를 다른 사용자들이 액세스 하거나 조작할 수 있게 하기 위해서 생성한다.

객체 권한 테이블 뷰 시퀀스 프로시저

ALTER	O		O
DELETE	O	O
EXECUTE				O
INDEX	O	O
INSERT	O	O
REFERENCES	O	O
SELECT	O	O	O
UPDATE	O	O

객체 권한 취소

REVOKE select, insert
ON 테이블명
FROM username;

PL/SQL

‘Procedural Language Extension To Structured Query Language(SQL)’의 약자이다.
SQL을 확장한 순차적 처리 언어이다.
데이터베이스 질의어인 SQL과 일반 프로그래밍 언어의 특성을 결합한 언어이다. 즉 PL/SQL을 사용하면 조건문이나 반복문, 변수나 상수를 선언해서 사용할 수 있을 뿐만 아니라 SQL도 사용할 수 있다.

PL/SQL의 기본 구조

PL/SQL의 기본 단위는 **블록(BLOCK)**이다.

선언부(Declarative Part) : 사용할 변수나 상수를 선언하는 부분이다.(선언부에만 변수와 상수 선언 가능)
실행부(Executable Part) : 실제 처리할 로직을 담당하는 부분이다.
예외 처리부(Exception-Building Part) : 실행부에서 로직을 처리하던 중 발생할 수 있는 각종 오류들에 대해 처리하는 부분이다.

DECLARE <- 선언부

BEGIN <- 실행부

EXCEPTION WHEN THEN <- 예외처리부

END; <- 종료부

실행부만 명시한 상태

BEGIN
		//메세지 출력
    DBMS_OUTPUT.PUT_LINE('Hello World');
END;

변수 사용

DECLARE
-- 변수를 선언할 수 있는 선언부
message VARCHAR2(100);
BEGIN
-- 실행부에 사용할 변수는 선언부에서 미리 선언되어야 함
-- (:=) -> 대입연산자
message := 'Hello World!!';
DBMS_OUTPUT.PUT_LINE(message);
END;

조건문

DECLARE
    --변수 선언 후 초기화를 하지 않으면 null
    counter INTEGER;
BEGIN
    counter:=counter+1;
    IF counter IS NULL THEN
        DBMS_OUTPUT.PUT_LINE('result : counter is null');
    END IF;    
END;

반복문

DECLARE
    -- 변수 선언
    counter INTEGER;
    i INTEGER;
BEGIN
    FOR i IN 1..9 LOOP
        counter := 2*i;
        DBMS_OUTPUT.PUT_LINE('2 * ' || i || '=' || counter);
    END LOOP;
END;

예외 처리부

EXCEPTION WHEN 예외1 THEN 예외처리1
          WHEN 예외2 THEN 예외처리2
          WHEN OTHERS THEN 예외처리;

DECLARE
    --변수 선언
    counter INTEGER;
BEGIN
    --변수 초기화
    counter := 10;
    counter := counter/0; <-- 예외 발생
    EXCEPTION WHEN OTHERS THEN
        DBMS_OUTPUT.PUT_LINE('errors');
END;

변수와 상수

변수
emp_num1 NUMBER(9);
grade CHAR(2);
emp_num2 INTEGER := 1;

상수
nYear CONSTANT INTEGER :=30; (O)
nYear CONSTANT INTEGER; (X)

%TYPE : 참조할 테이블에 있는 컬럼의 데이터타입을 자동으로 가져온다.

변수명 테이블명.컬럼명%TYPE;
nSal emp.sal%TYPE;

%ROWTYPE : %TYPE은 하나의 값에 대해 적용되지만, %ROWTYPE은 하나 이상의 값에 대해 적용한다. 로우타입 변수를 선언해서 테이블에 있는 로우를 대입한다.

콜렉션

Varray

Varray는 Variable Array의 약자로 **고정 길이(Fixed Number)**를 가진 배열이다.

TYPE 타입명 IS {VARRAY | VARYING ARRAY}(크기) OF 요소데이터타입 [NOT NULL];

DECLARE
    TYPE VARRAY_TEST IS VARRAY(3) OF INTEGER;
    varray1 VARRAY_TEST; -- 위에서 선언한 VARRAY_TEST 타입 변수
BEGIN
    -- 배열 초기화
    VARRAY1 := VARRAY_TEST(10,20,30);
    -- 인덱스를 통해 읽어오기
    DBMS_OUTPUT.PUT_LINE(varray1(1));
    DBMS_OUTPUT.PUT_LINE(varray1(2));
    DBMS_OUTPUT.PUT_LINE(varray1(3));
END;

중첩 테이블

중첩 테이블은 Varray와 흡사하지만 중첩 테이블은 선언 시에 전체 크기를 명시할 필요가 없다.

TYPE 타입명 IS TABLE OF 요소데이터 타입 [NOT NULL];

DECLARE
    TYPE NESTED_TEST IS TABLE OF VARCHAR2(10);
    NESTED1 NESTED_TEST;
BEGIN
    NESTED1 := NESTED_TEST('A','B','C','D');
    
    DBMS_OUTPUT.PUT_LINE(NESTED1(1));
    DBMS_OUTPUT.PUT_LINE(NESTED1(2));
    DBMS_OUTPUT.PUT_LINE(NESTED1(3));
    DBMS_OUTPUT.PUT_LINE(NESTED1(4));
END;

Associative Array(Index-By Table)

Associative array(연관배열)는 index-by table이라고도 하며 연관배열이라고 부른다. 키와 값의 쌍으로 구성된 콜렉션으로, 하나의 키는 하나의 값과 연관되어 있다.

TYPE 타입명 IS TABLE OF 요소 데이터 타입 [NOT NULL]
	INDEX BY [PLS_INTEGER | BINARY_INTEGER | VARCHAR2(크기)]
	INDEX BY 키타입;

DECLARE
    --키는 PLS_INTEGER 형이며, 값은 NUMBER 형인 요소들로 구성
    TYPE ASSOC_ARRAY_NUM_TYPE IS TABLE OF NUMBER
        INDEX BY PLS_INTEGER;
    
    assoc1 ASSOC_ARRAY_NUM_TYPE;
BEGIN
    -- 키와 값 저장
    -- 키는 3, 값은 33
    assoc1(3) := 33;
    DBMS_OUTPUT.PUT_LINE(assoc1(3));
END;

레코드

테이블의 컬럼들이 서로 다른 유형의 데이터 타입으로 구성되듯이 레코드 역시 해당 필드(레코드에서는 요소란 말 대신 필드란 용어를 사용함)들이 각기 다른 데이터 타입을 가질 수 있다.

즉, 테이블에서 하나의 행과 같은 데이터이다.

TYPE 레코드이름 IS RECORD (필드1 데이터타입1, 필드2 데이터타입2 ㆍㆍㆍ);

레코드이름 테이블명%ROWTYPE;
레코드이름 커서명%ROWTYPE;

DECLARE
    --TYPE으로 선언한 레코드
    TYPE RECORD1 IS RECORd (DEPTNO NUMBER NOT NULL := 50,
                            DNAME VARCHAR2(14),
                            LOC VARCHAR2(13));
    --위에서 선언한 RECORD1을 받는 변수 선언
        rec1 RECORD1;
BEGIN
    --RECORD1 타입의 rec1의 dname 필드에 값 할당
    rec1.dname := 'RECORD';
    REC1.LOC := '서울';
    
    --rec1 레코드 값을 dept 테이블에 insert
    INSERT INTO DEPT VALUES REC1;
    COMMIT;
    
    //예외 처리
    EXCEPTION WHEN OTHERS THEN
    ROLLBACK;
END;

PL/SQL 문장과 커서

IF문

[처리 조건이 여러 개일 경우]
IF 조건 THEN
처리문1;
ELSIF 조건2 THEN
처리문2;
…
ELSE
처리문n;
END IF;

DECLARE
    grade char(1);
BEGIN
    --변수의 초기화
    grade := 'B';
    
    IF grade = 'A' THEN
    DBMS_OUTPUT.PUT_LINE('EXCELLENT');
    ELSIF GRADE = 'B' THEN
    DBMS_OUTPUT.PUT_LINE('GOOD');
    ELSIF GRADE = 'C' THEN
    DBMS_OUTPUT.PUT_LINE('FAIR');
    ELSIF GRADE = 'D' THEN
    DBMS_OUTPUT.PUT_LINE('POOR');
    END IF;
END;

CASE문

DECLARE
    GRADE CHAR(1);
BEGIN
    GRADE := 'C';

    CASE GRADE
    WHEN 'A' THEN DBMS_OUTPUT.PUT_LINE('EXCELLENT');
    WHEN 'B' THEN DBMS_OUTPUT.PUT_LINE('GOOD');
    WHEN 'C' THEN DBMS_OUTPUT.PUT_LINE('FAIR');
    WHEN 'D' THEN DBMS_OUTPUT.PUT_LINE('POOR');
    ELSE          DBMS_OUTPUT.PUT_LINE('NOT FOUND');
    END CASE;
END;

LOOP문

DECLARE
    --변수선언
    test_number INTEGER;
    result_num INTEGER;
BEGIN
    test_number:=1;
    
    loop
        result_num := 2*test_number;
        if result_num > 20 then
        exit; -- 블럭 종료
        else
            dbms_output.put_line(result_num);
        end if;
        test_number := test_number + 1;
    end loop;
    --loop 블럭을 빠져나오면 아래 코드를 실행함
    dbms_output.put_line('프로그램 끝');
END;

DECLARE
		--변수 선언
    TEST_NUM INTEGER;
    RESULT_NUM INTEGER;
BEGIN
    --변수 초기화
    TEST_NUM := 1;
    LOOP
        RESULT_NUM := 2*TEST_NUM;
        EXIT WHEN RESULT_NUM>20;
        DBMS_OUTPUT.PUT_LINE(RESULT_NUM);
        TEST_NUM := TEST_NUM+1;
    END LOOP;    
END;

While-Loop문

DECLARE
		--변수 선언
    TEST_NUM INTEGER;
    RESULT_NUM INTEGER;
BEGIN
		--변수 초기화
    TEST_NUM := 1;
    RESULT_NUM := 0;
    --WHILE 조건 LOOP
    WHILE RESULT_NUM < 20 LOOP
        RESULT_NUM := 2*TEST_NUM;
        TEST_NUM := TEST_NUM+1;
        DBMS_OUTPUT.PUT_LINE(RESULT_NUM);
    END LOOP;
END;

For..Loop문

DECLARE
		--변수 선언
    TEST_NUM INTEGER;
    RESULT_NUM INTEGER;
BEGIN
		--변수 초기화
    RESULT_NUM := 0;
    --FOR 변수 IN 시작..끝 LOOP
    FOR TEST_NUM IN 1..10 LOOP
        RESULT_NUM := TEST_NUM * 2;
        DBMS_OUTPUT.PUT_LINE(RESULT_NUM);
    END LOOP;
END;

DECLARE
		--변수 선언
    TEST_NUM INTEGER;
    RESULT_NUM INTEGER;
BEGIN
		--변수 초기화
    RESULT_NUM := 0;
    --FOR 변수 IN 시작..끝 LOOP
    FOR TEST_NUM IN REVERSE 1..10 LOOP
        RESULT_NUM := TEST_NUM * 2;
        DBMS_OUTPUT.PUT_LINE(RESULT_NUM);
    END LOOP;
END;

커서

쿼리에 의해 반환되는 결과는 메모리 상에 위치하게 되는데 PL/SQL에서는 바로 커서(CURSOR)를 사용하여 이 결과 집합에 접근할 수 있다.

[커서 선언 : 커서에 이름을 주고, 이 커서가 접근하려는 쿼리를 정의]
CURSOR 커서명 IS SELECT 문장;

[커서 열기(OPEN) : 커서로 정의된 쿼리를 실행하는 역할을 한다.]
OPEN 커서명;

[패치(FETCH) : 쿼리의 결과에 접근한다.]
FETCH 커서명 INTO 변수 ...;

[커서 닫기(CLOSE) : 패치 작업이 끝나면 사용된 커서를 닫는다.]
CLOSE 커서명;

DECLARE
    --커서 선언 : 커서에 이름을 주고, 이 커서가 접근하려는 쿼리를 정의
    CURSOR EMP_CSR IS
    SELECT EMPNO
    FROM EMP
    WHERE DEPTNO=10;
    
    EMP_NO EMP.EMPNO%TYPE;
BEGIN
    --커서 열기 : 커서로 정의된 쿼리를 실행하는 역할
    OPEN EMP_CSR;
    
    LOOP
        FETCH EMP_CSR INTO EMP_NO;
        --%NOTFOUND : 커서에서만 사용 가능한 속성
        --            더 이상 패치(할당)할 로우가 없음을 의미
        EXIT WHEN EMP_CSR%NOTFOUND;
        DBMS_OUTPUT.PUT_LINE(EMP_NO);
    END LOOP;        
    --커서 닫기 : 패치 작업이 끝나면 사용된 커서를 닫는다.
    CLOSE EMP_CSR;
END;

커서의 속성

속성	정의
%FOUND	PL/SQL 코드가 마지막으로 얻은 커서의 결과 SET에 레코드가 있다면 참
%NOTFOUND	%FOUND 연산자와 반대의 의미
%ROWCOUNT	커서에서 얻은 레코드의 수를 반
%ISOPEN	커서가 열렸고, 아직 닫히지 않은 상태라면 참

PL/SQL 서브프로그램

PL/SQL 서브프로그램은 파라미터와 고유의 이름을 가진 PL/SQL 블록을 말하며, 데이터베이스 객체로 존재한다. 즉 필요할 때마다 호출해서 사용할 수 있다는 의미이다. 이러한 서브프로그램에는 **내장 프로시저(Stored Procedure)**와 **함수(Function)**가 있다.

함수(Function)

CREATE OR REPLACE FUNCTION 함수명(파라미터1 데이터타입,
																	파라미터2 데이터타입,
																	…)
	RETURN 데이터타입 IS [AS]
	변수 선언…;
BEGIN
	처리내용…;
	RETURN 리턴값;
END;

첫번째 예시

CREATE OR REPLACE FUNCTION tax(p_value IN NUMBER)
    RETURN NUMBER
    IS
BEGIN
    RETURN p_value * 0.1;
END;

두번째 예시

CREATE OR REPLACE FUNCTION TAX2(P_SAL IN EMP.SAL%TYPE,
                                P_COMM EMP.COMM%TYPE)
RETURN NUMBER
IS
BEGIN
    RETURN (P_SAL + NVL(P_COMM,0)) * 0.1;
END;

SELECT EMPNO,ENAME,SAL,COMM,TAX2(SAL,COMM) AS TAX
FROM EMP;

세번째 예시

CREATE OR REPLACE FUNCTION TAX3(P_SAL IN EMP.SAL%TYPE,
                                P_COMM EMP.COMM%TYPE)
RETURN NUMBER
IS
    --변수 선언
    l_sum NUMBER;
    l_tax NUMBER;
BEGIN
    l_sum := p_sal + NVL(p_comm,0);
    
    IF l_sum < 1000 THEN
    l_tax := l_sum * 0.05;
    ELSIF l_sum <= 2000 THEN
    l_tax := l_sum * 0.1;
    ELSE
    l_tax := l_sum * 0.2;
    END IF;
        RETURN l_tax; 
END;

네번째 예시

CREATE OR REPLACE FUNCTION EMP_SALARIES(EMP_NO NUMBER)
    RETURN NUMBER
IS
--변수 선언
NSALARIES NUMBER(9);
BEGIN
    SELECT SAL
    --결과 행이 단일행일 경우 INTO를 이용해서 읽어온 값을 변수에 담을 수 있음
    INTO NSALARIES
    FROM EMP
    WHERE EMPNO=EMP_NO;
    
    RETURN NSALARIES;
END;

저작자표시

'IT 국비 교육' 카테고리의 다른 글

쌍용교육센터 - 23~25일 (0)	2024.03.22
쌍용교육센터 - 22일 (0)	2024.03.19
쌍용교육센터 - 20일 (2)	2024.03.15
쌍용교육센터 - 19일 (0)	2024.03.14
쌍용교육센터 - 18일 (0)	2024.03.13

백엔드 개발자 공부 기록