*42일차 (index / PL'SQL / 조건문 / 반복문)

학원/강의

*42일차 (index / PL'SQL / 조건문 / 반복문)

pringspring 2022. 3. 26. 21:56

@INDEX

색인
sql명령의 처리속도 향상을 위해 table의 컬럼에 대해 생성하는 색인 객체
key-value형태.
key : 컬럼값 , value : 주소값 보관

*장점

검색속도 빨라짐
시스템 부하 감소
전체적인 성능향상

*단점

추가 저장공간필요
인덱스 생성/수정/삭제시 별도의 작업시간 소요
데이터 생성/수정/삭제가 빈번하다면 인덱스로 인해 성능저하 야기

**어떤 컬럼에 대해 인덱스를 만드는가?

1.선택도가 좋은 컬럼

→ 선택도 : 고유한 값을 많이 가지는 것

ex)

-- employee.emp_id 중복X -> 선택도가 아주 좋다.
-- employee.emp_no 중복X -> 선택도가 아주 좋다.
-- employee.emp_name 중복O -> 선택도가 좋다.
-- employee.dept_code 중복O -> 선택도 보통
-- employee.gender -> 선택도 나쁨

2.where절에 자주 사용되는 컬럼 선택

3.join컬럼 선택

4.한번 입력된 데이터가 변경이 자주 없는 컬럼

→중복값 많은 컬럼, null값이 많은 컬럼은 되도록 지양

--index 조회

--pk,uq제약조건이 걸린 컬럼은 자동으로 인덱스 생성

select * from user_indexes;
select * from user_ind_columns;

--pk,uq로 생성된 인덱스는 제약조건명과 인덱스명이 같다.

--제약조건명이 잘 지으면(테이블명,컬럼명 포함) 인덱스정보 확인시 유용

select * from user_constraints where constraint_type in ('P', 'U');
select 
    * 
from
    user_indexes ui join user_ind_columns uic
        using(index_name);

--실행계획(f10) 확인

select * from employee where job_code = 'J1'; -- job_code 인덱스 없음
select * from employee where emp_id = '203'; -- emp_id 인덱스 있음.
select * from employee where dept_code = 'D5';
select * from employee where emp_no = '070910-4653546';

select * from employee where emp_name = '송종기';

--인덱스 생성

create index idx_employee_emp_name on employee(emp_name);

-- 인덱스 사용시 주의사항
-- 인덱스 사용여부는 optimizer(최적화처리기)가 결정을 하지만, 다음 경우는 index를 사용하지 않는다.
-- 1. 인덱스 컬럼을 변형해 조회하는 경우 where substr(emp_no, 8, 1) = '1'
-- 2. null비교하는 경우 where emp_name is null
-- 3. not비교하는 경우 where emp_name != '송종기'
-- 4. 인덱스컬럼과 자료형이 다른 경우 where emp_id = 201

select * from employee where substr(emp_no, 8, 1) = '1';
select * from employee where emp_id = 201;

-- index 삭제
-- drop index 인덱스명

-- pk, uq제약조건으로 생성된 인덱스는 직접 삭제가 불가하다. 제약조건을 제거하면, 자동으로 삭제처리된다.

@PL/SQL

Procedural Language extension to SQL
기존 sql에 절차적 언어 방식을 추가해서 변수선언,조건처리,반복처리 지원하는 문법

*pl/sql 유형

익명 블럭 : 매번실행
프로시저 : pl/sql구문을 저장해서 호출해 재사용 가능
함수 : pl/sql구문을 저장해서 호출해 재사용 가능 (반드시 하나의 리턴값 가짐)

*익명블럭 구조

declare

--변수선언부(선택)

begin

--실행부(필수)

exception

--예외처리부(선택)

end;

→ sql문은 ;(세미콜론)으로 각sql문을 구분

→ pl/sql에서는 블럭안에 여러개의 sql문이 올수있다. /가 익명블럭의 종료를 의미함.

--서버콘솔출력

--기본값이 off이므로 매번 session생성시마다 1회 실행할 것

set serveroutput ON;

begin
    dbms_output.put_line('hello world');
end;
/


declare
    v_emp_id char(3); -- 변수 선언
begin
    select 
        emp_id
    into
        v_emp_id
    from 
        employee
    where 
        emp_name = '정동일';
        
    dbms_output.put_line('emp_id : ' || v_emp_id);    
exception 
    when no_data_found then dbms_output.put_line('찾으시는 사원이 없습니다.');
end;
/

--익명블럭

declare 변수
변수명 [constant] 자료형 [not null] [:=값];

declare
    name varchar2(100);
    num number := 10 * 20;
    KKK constant number := 333;
begin
    name := '김사랑';
--    KKK := KKK * 100; -- ORA-06550: 줄 7, 열5:PLS-00363: 'KKK' 식은 피할당자로 사용될 수 없습니다.
    
    dbms_output.put_line(name);
    dbms_output.put_line(num);
    dbms_output.put_line(KKK);
end;
/

--변수의 자료형

1.기본자료형

varchar2 , char , clob
number, binary_integer , pls_integer
date
boolean (true,false,null)

2.복합자료형

record
cursor
collection (varray(배열) , nested_table(List), associative array(Map))

--변수 유형

스칼라변수(값)
참조변수(테이블,컬럼타입)

--참조변수1 %type

--변수자료형을 직접 선언하지않고, (다른테이블).(특정 컬럼)을 참조

--부서명도 함께 출력

declare
    v_emp_name employee.emp_name%type;
    v_phone employee.phone%type;
    v_dept_title department.dept_title%type;
begin
    select
        emp_name, phone, d.dept_title
    into 
        v_emp_name, v_phone, v_dept_title
    from
        employee e left join department d
            on e.dept_code = d.dept_id
    where
        emp_id = '&사번'; -- 사번 임시변수에 사용자입력값 받아서 조회
    
    
    dbms_output.put_line('이름 : '|| v_emp_name);
    dbms_output.put_line('전화번호 : '|| v_phone);
    dbms_output.put_line('부서명 : '|| v_dept_title);
end;
/

desc employee;

--참조변수2 %rowtype

--테이블의 모든 컬럼을 참조하는 타입

declare
    emp_row employee%rowtype;
begin
    select
        *
    into
        emp_row
    from
        employee
    where
        emp_id = '&사번';

    dbms_output.put_line('사원명 : ' || emp_row.emp_name);
    dbms_output.put_line('이메일 : ' || emp_row.email);

end;
/

--참조변수3 . record

--원하는 컬럼만 갖고있는 record자료형을 만들고 사용

declare
    type my_emp_type is record(
        emp_name employee.emp_name%type,
        dept_title department.dept_title%type
    );

    erow my_emp_type;
begin
    select
        e.emp_name, d.dept_title
    into
        erow
    from
        employee e left join department d
            on e.dept_code = d.dept_id
    where
        emp_id = '&사번';
        
    dbms_output.put_line('이름 : ' || erow.emp_name);
    dbms_output.put_line('부서 : ' || erow.dept_title);

end;
/

--사원명,직급명을 처리할 수 있는 레코드 선언, 사번을 통해 조회

declare
    type emp_type is record(
        emp_name employee.emp_name%type,
        job_name job.job_name%type
    );
    erow emp_type;
begin
    select
        emp_name,
        (select job_name from job where job_code = e.job_code) job_name
    into 
        erow
    from 
        employee e
    where
        emp_id = '&사번';


    dbms_output.put_line('사원명 : ' || erow.emp_name);
    dbms_output.put_line('직급 : ' || erow.job_name);

end;
/

--begin절에 DML사용하기

--익명블럭안에서 트랜잭션 처리까지 완료해야 한다.

select * from tb_member;
desc tb_member;

begin
    insert into 
        tb_member
    values (
        'sinsa', '신사임당', '1234', 'sinsa@gmail.com', 'F', 1000, default
    );
    -- 트랜잭션처리까지 완료하기
    commit;
end;
/

-- ex_employee의 마지막 번호를 조회한후, +1 사번을 부여해서 다음 정보를 insert하세요.
-- 김테리 880808-2345678 taeri@gmail.com 01012341234 null J4 S3 3500000 null 200 오늘 null N

select * from ex_employee;

declare
    v_emp_id employee.emp_id%type;
begin
    -- 1. 마지막 번호 조회
    select 
        max(emp_id)
    into
        v_emp_id
    from
        ex_employee;
            
--    dbms_output.put_line(v_emp_id);
    
    -- 2. insert
    insert into
        ex_employee
    values (
        v_emp_id + 1, '김테리', '880808-2345678', 'taeri@gmail.com', '01012341234', null, 'J4', 'S3', '3500000' ,null, '200', sysdate, null, 'N'
    );
    
    -- 2.1 트랜잭션처리
    commit;
end;
/

@조건문

if , else if , if else

if 조건식 then

실행구문

else if;

if 조건식 then

참일때 실행코드

else

거짓일때 실행코드

end if;

if 조건식1 then

실행코드1

elsif 조건식2 then

실행코드2

elsif 조건식3 then

실행코드3

. . . .

end if;

declare
    n number := &숫자;
begin
    dbms_output.put_line(n);
    
--    if mod(n, 2) = 0 then
--        dbms_output.put_line('짝수를 입력하셨습니다.');
--    else 
--        dbms_output.put_line('홀수를 입력하셨습니다.');
--    end if;
    
    if n > 0 then
        dbms_output.put_line('양수입니다.');
    elsif n = 0 then
        dbms_output.put_line('0입니다.');
    else
        dbms_output.put_line('음수입니다.');
    end if;
    
    
    dbms_output.put_line('끝');
end;
/

--case문

/*
문법1
    case 표현식
        when 값1 then
            실행코드1;
        when 값2 then
            실행코드2;
        ...
        else
            기본실행코드;
    end case;

문법2

    case
        when 조건식1 then 실행코드1;
        when 조건식2 then 실행코드2;
        when 조건식3 then 실행코드3;
        else 기본실행코드;
    end case;

*/
-- 가위(1) 바위(2) 보(3)
declare
    n number := &가위바위보123;
begin
    case (n)
        when 1 then dbms_output.put_line('가위');
        when 2 then dbms_output.put_line('바위');
        when 3 then dbms_output.put_line('보');
        else dbms_output.put_line('잘못 입력하셨습니다.');
    end case;
end;
/

declare
    com number := trunc(dbms_random.value(1, 4)); -- 1.0보다 크거나 같고, 4.0보다 작은 실수 반환
    n number := &가위바위보123;
begin
    dbms_output.put_line(n || ' ' || com);
    case 
        when n = 1 then dbms_output.put_line('가위를 냈습니다.');
        when n = 2 then dbms_output.put_line('바위를 냈습니다.');
        when n = 3 then dbms_output.put_line('보를 냈습니다.');
        else dbms_output.put_line('잘못 입력하셨습니다.'); return;
    end case;
    
    -- 결과평가 
    case
        when com = n then dbms_output.put_line('> 비겼습니다.');
        when ((n = 1 and com = 3) or (n = 2 and com = 1) or (n = 3 and com = 2)) then dbms_output.put_line('> 당신이 이겼습니다.');
        else dbms_output.put_line('> 당신이 졌습니다.');
    end case;
    
end;
/

-- 사번을 입력받고, 관리자에 대한 성과급을 지급하려한다.
-- 관리하는 사원이 5명이상은 급여의 15% 지급 : '성과급은 ??원입니다.'
-- 관리하는 사원이 5명미만은 급여의 10% 지급 : '성과급은 ??원입니다.'
-- 관리하는 사원이 없는 경우는 '대상자가 아닙니다.'

declare
    salary employee.salary%type;
    num number; -- 부하직원수
begin
    -- 1. 사번을 가지고 부하직원수, 급여 조회
    select
        (select count(*) from employee where manager_id = e.emp_id), 
        salary
    into
        num, salary
    from
        employee e
    where
        emp_id = '&사번';
        
    dbms_output.put_line(num || ', ' || salary);

    -- 2. 성과금 평가
    if num >= 5 then
        dbms_output.put_line('성과금 : ' || salary * 0.15 || '원');
    elsif num > 0 then
        dbms_output.put_line('성과금 : ' || salary * 0.1 || '원');
    else
        dbms_output.put_line('성과금 대상자가 아닙니다');
    end if;
end;
/

@반복문

loop , while loop , for in loop

--loop 무한반복 + 탈출문 exit

declare
    n number := 1;
begin

    loop
        dbms_output.put_line(n);
        n := n + 1;
        
        -- 탈출조건(필수)
        exit when n > 5;
        
    end loop;

end;
/

--while(조건식) loop

declare
    n number := 1;
begin
    while n <= 5 loop
        dbms_output.put_line(n);
        n := n + 1;
    end loop;
end;
/

for loop : 증감변수 별도 선언 불필요
증감변수 범위만큼 반복후 자동종료
for 증감변수 in 시작값 . . 종료값
증감처리 +1 / 변경불가 / reverse 사용가능

declare 
    dan number := &구구단수;
begin
    for n in 1..9 loop
        dbms_output.put_line(dan || ' * ' || n || ' = ' || (dan * n));
    end loop;
end;
/

--2~9단까지 출력

begin
    for dan in 2..9 loop
        for n in 1..9 loop
            dbms_output.put_line(dan || ' * ' || n || ' = ' || (dan * n));
        end loop;
        dbms_output.new_line;
    end loop;
end;
/

dbms_output.new_line; -- 개행만 하고 싶은 경우

--사원정보 출력

--select문의 조회결과가 1행씩 처리

declare
    erow employee%rowtype;
begin
    for n in 200..223 loop
--        dbms_output.put_line(n);
        select 
            *
        into 
            erow
        from
            employee
        where
            emp_id = n;
        
        dbms_output.put_line(erow.emp_id || '   ' || erow.emp_name || '     ' || erow.email);
    end loop;
end;
/