UIUX _국비과정 0613 [오라클SQL 시퀸스 ~ 확장된 SQL 구문]

 

🍀 시퀀스

-- 시퀀스(sequence)
-- 숫자를 자동으로 중복되지 않게 만들어 주는 객체
-- 테이블의 기본키(primary key)가 정의된 컬럼의 값으로 사용
-- 시퀀스명.nextval

create sequence 시퀀스명
start with 1 -- 몇 부터 시작할지 (1부터 하려면 생략 가능)
increment by 1 -- 얼마나 증가할지
maxvalue 10의 27승 -- 최대
minvalue 10의 -27승 -- 최소

 

시퀀스 생성

create sequence emp01_seq
start with 10
increment by 10
maxvalue 10000;

-- 시퀀스 객체의 구조를 확인
-- user_xxx 로 딕셔너리에 접근
desc user_sequences;

select SEQUENCE_NAME, MIN_VALUE, MAX_VALUE, INCREMENT_BY
from user_sequences;

select emp01_seq.nextval
from dual;
-- 실행할 때마다 값이 증가한다

drop table emp01;

create table emp01(
empno number(4) primary key,
ename varchar2(10),
hiredate date
);

insert into emp01
values (emp01_seq.nextval, 'hong', sysdate);

delete from emp01
where empno = 90;

select * from emp01;

새로 데이터가 만들어질 때마다 empno가 증가한다

-- 증가하는 sequence에 별칭 부여
insert into emp01
values ('eno' || emp01_seq.nextval, 'hong', sysdate);

 

 

🍀 인덱스

-- 인덱스(index)
-- 데이터의 조회 속도를 증가 시킨다.
-- 컬럼에 생성하는 객체
-- primary key 제약조건이 정의된 컬럼은 기본적으로 index 객체가 생성 되어있다

-- 조건 : 전체의 3~4% 내외 데이터 검색이 이뤄질 때 효과적이다
-- 인덱스 객체를 만드는 것도 용량을 차지하기 때문에 인덱스 객체가 필요한지 잘 판단하는 것이 중요
-- 실제로는 primary key를 더 많이 사용

drop table emp01;

create table emp01
as
select * from emp;

-- 자기 것을 복제
-- insert into emp01
-- select * from emp01;

select count(*) from emp01;

insert into emp01(empno, ename)
values (1111, 'SYG');

-- 인덱스 객체가 생성되기 전과 후 데이터 찾는 속도 비교
set timing on;

select distinct empno, ename from emp01
where ename = 'SYG';

-- 인덱스 객체 생성
create index idx_emp01_ename
on emp01 (ename);

desc user_ind_columns;

select INDEX_NAME, TABLE_NAME, COLUMN_NAME
from user_ind_columns
where table_name in ('EMP01');

-- 인덱스 삭제
drop index idx_emp01_ename;

데이터를 5만건 넣고 조회하는 속도를 비교해보았다.

 

 

🍀 데이터 베이스 권한

system 계정

-- 데이터 베이스 권한
-- 최고 권한 DBA (system 계정)

-- 계정 생성
-- DCL(Data Control Language)
-- grant : 사용자에게 권한 부여 
-- revoke : 사용자에게 권한 취소

-- 데이터 베이스의 권한을 부여하기 위해 새로운 개정 생성
create user user01 identified by 1234;

-- 권한
-- 시스템 권한
-- 객체 권한

-- 권한 부여 명령어

-- 세션 만들기 권한
grant create session
to user01;

-- 테이블 만들기 권한
grant create table
to user01;

-- 다른 계정의 객체를 사용하고 싶을 때 권한 위임
-- 객체에 따라 권한 목록이 다름

-- 권한 회수
revoke create table
from user01;

 

scott 계정에서 user01 계정으로 권한 부여, 회수

scott 계정

-- 객체 권한
grant select
on emp
to user01;

-- 권한 회수
revoke select
on emp
from user01;

 

user01 계정

create table emp(
empno number(4)
);

drop table emp;

-- 누구로부터 권한을 부여받았는지 명시해줘야 한다 (스키마 붙이기)
select * from scott.emp;

-- 삭제 권한은 없기 때문에 실행되지 않음
-- delete from scott.emp;

desc user_tab_privs_recd;

select * from user_tab_privs_recd;

 

 

role 권한

system 계정

-- role 권한
-- 오라클베이스에 만들어져 있다
-- connect
-- resouce
-- dba

create user user02 identified by 1234;

-- 만들어진 roll을 사용하여 모든 권한 부여
grant connect, resource
to user02;

 

user02 계정

create table test (
empno number(4)
);

테이블이 잘 만들어진다.

 

role 을 직접 만들어서 사용하기

system 계정

-- role 만들어서 사용
create role mrole;

grant create session, create table, create view
to mrole;

create user user03 identified b용y 1234;

grant mrole
to user03;

 

user03 계정

create table test (
empno number(4)
);

테이블이 잘 만들어진다

 

객체권한을 담는 role

system 계정

-- 객체권한을 담는 role
create role mrole2;

-- 권한 주기
grant mrole2
to user03;

revoke mrole2
from user03;

-- role 삭제
-- revoke는 role을 특정사용자로부터 권한 회수, drop은 role을 아예 삭제하는 것이다
drop role mrole2;

 

scott 계정

-- role에 권한 부여
grant select
on emp
to mrole2;

 

user03

create table test (
empno number(4)
);

select * from scott.emp;

 

 

🍀 확장된 SQL 구문

-- PL/SQL (확장된 sql 구문)
-- 변수, 반복문, 조건, 비교문
-- 실행문 마지막에 반드시 세미콜론을 붙인다.

[declare] 
변수
begin

변수의 초기화, 반목문, 조건문, sql구문, 출력함수()

[exception]

end;
/

 

 

변수의 초기화

-- 문장 출력하기
set serveroutput on;

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

-- 변수, 상수 선언

declare
vempno number(4); -- 변수의 선언
vename varchar2(10);
vjob varchar2(10) := 'sales'; -- 선언 및 초기화

begin
vempno := 7788; -- 변수의 초기화
vename := 'HONG';

-- 출력함수
dbms_output.put_line(vempno || ' : ' || vename);
end;
/

-- pl/sql구문
-- select 구문 작성 시 반드시 into절과 where절을 함께 작성해야 한다.
-- where은 조건절이 온다.




declare
	  -- 스칼라 방식
	  -- 타입과 크기를 직접 명시하는 방식
    -- vempno number(4); 
    -- vename varchar2(10);
    
    -- 레퍼런스
     vempno emp.empno%type; -- number(4)
     vename emp.ename%type; -- varchar2(10)
     
begin
    select empno, ename into vempno, vename
    from emp
    where ename = 'SCOTT';
    
    dbms_output.put_line(vempno || ' : ' || vename);
end;
/

select empno,ename
from emp;

 

rowtype 타입으로 변수선언

declare
    -- rowtype 타입
    -- 테이블에 컬럼명을 변수로 사용하자
    remp emp%rowtype;
    
begin
    select * into remp
    from emp
    where ename = 'SCOTT';
    
    dbms_output.put_line(remp.empno || ' : ' || remp.ename);
    dbms_output.put_line(remp.job || ' : ' || remp.sal);
    dbms_output.put_line(remp.mgr || ' : ' || remp.hiredate);
    dbms_output.put_line(remp.comm || ' : ' || remp.deptno);
    
end;
/

-- 부서테이블 20번 부서정보를 rowtype를 적용하여 출력하세요


select * from dept;

declare
    rdept dept%rowtype;
    
begin
    select * into rdept
    from dept
    where deptno = 20;
    
    dbms_output.put_line(rdept.deptno || ' : ' || rdept.dname || ' : ' || rdept.loc);
  
end;
/

 

 

🍀 조건문

 

if 문

-- 조건문
-- 하나의 레코드만 조회되게 만들어야 한다

-- pl/sql 구분의 if문은 3가지
-- if문
-- if else 문
-- 다중 if문



declare
    vempno number(4);
    vename varchar2(20);
    vdeptno emp.deptno%type;
    vdname varchar2(10);
begin
    select empno, ename, deptno into vempno, vename, vdeptno
    from emp
    where empno = '7788';
    
    if(vdeptno = 10) then
    vdname := 'AAA';
    end if;
    
    if(vdeptno = 10) then
    vdname := 'BBB';
    end if;
    
    if(vdeptno = 10) then
    vdname := 'CCC';
    end if;
    
    if(vdeptno = 10) then
    vdname := 'DDD';
    end if;
    
    dbms_output.put_line(vempno || ' : ' || vename);
    dbms_output.put_line(vdeptno || ' : ' || vename);
end;
/

-- 사원의 연봉을 출력하는 프로그램

declare
    vemp emp%rowtype;
    annsal number(7,2);
    
begin
   select * into vemp
   from emp
   where ename = 'SCOTT';
   
   if (vemp.comm is null) then
   vemp.comm := 0;
   end if;
   
   annsal := vemp.sal * 12 + vemp.comm;
   
   dbms_output.put_line('사 번' || ' : ' || '이 름' || ' : ' || '연 봉');
   dbms_output.put_line(vemp.empno || ' : ' || vemp.ename || ' : ' || annsal);
end;
/

 

if else문, 다중 if문

-- if else 문

declare
    vemp emp%rowtype;
    annsal number(7,2);
    
begin
   select * into vemp
   from emp
   where ename = 'SCOTT';
   
   if(vemp.comm is null) then
   annsal := vemp.sal * 12;
   else 
   annsal := vemp.sal * 12 + vemp.comm;
   end if;
   
   dbms_output.put_line('사 번' || ' : ' || '이 름' || ' : ' || '연 봉');
   dbms_output.put_line(vemp.empno || ' : ' || vemp.ename || ' : ' || annsal);
end;
/



-- 다중 if문

declare
    vempno number(4);
    vename varchar2(20);
    vdeptno emp.deptno%type;
    vdname varchar2(10);
begin
    select empno, ename, deptno into vempno, vename, vdeptno
    from emp
    where empno = '7788';
    
    if(vdeptno = 10) then
    vdname := 'AAA';
    elsif(vdeptno = 20) then
    vdname := 'BBB';
    elsif(vdeptno = 30) then
    vdname := 'CCC';
    elsif(vdeptno = 40) then
    vdname := 'DDD';
    end if;
    
    dbms_output.put_line(vempno || ' : ' || vename);
    dbms_output.put_line(vdeptno || ' : ' || vename);
end;
/