[Day 24] Database 0428 - 내용 정리

🌞권한부여

grant select on 테이블명 to 사용자계정;

==> select 할 수 있는 권한을 사용자에게 부여

🌞권한제거

revoke select on 테이블명 from 사용자계정;

==> 사용자로부터 테이블에 대한 select 권한을 제거

🌞뷰의 수정

<기본문법>

create or replace view 뷰이름 as select ~~

==> 만약에 동일한 이름의 뷰가 있으면 수정해주고, 없으면 만들어준다.

예시) vw_customer는 주소가 '대한민국'인 고객을 보여준다. 이 뷰를 주소가 '영국'인 고객으로 변경해보자. 고객번호, 이름, 주소를 조회하도록 한다.

create or replace view vw_customer as
select custid, name, address from customer
where address like '%영국%';

🌞뷰의 삭제

<기본문법>

drop view 뷰이름

🌞시스템 뷰

• 오라클이 제공하는 뷰를 말한다.
• 데이터 사전이라고도 부른다.

🌈user_objects

• 사용자가 만든 모든 객체의 정보를 갖고있다.

예시)

select object_name from user_objects;

🌈user_tables

• 사용자가 만든 모든 테이블의 정보를 갖고있다.

예시)

select table_name from user_tables;

🌈user_constraints

• 사용자가 만든 모든 제약의 정보를 갖고있다.

이름 
----------------------------------------------------------------------------
OWNER                       
CONSTRAINT_NAME            //제약명
CONSTRAINT_TYPE            //제약의종류
TABLE_NAME                 //테이블이름
STATUS					   //제약의 상태(때로는 제약을 활성화, 비활성화 시킬수 있다.)

예시)

SYS_C008322 	R		EMP		ENABLED

==> emp 테이블에 참조키에 대한 제약이 활성화된 상태이다.
즉, emp 테이블의 부서번호는 반드시 dept 테이블의 부서번호를 참조해야 한다.

🍁제약의 비활성화

<기본문법>

alter table 테이블명 disable constraint 제약명;

🍁제약의 활성화

<기본문법>

alter table 테이블명 enable constraint 제약명;

==> 제약에 대한 만족하지 않는 데이터가 있으면 제약을 활성화할 수 없다.

🌞인덱스

• 도서의 맨 뒤에 색인표를 만들어 책 내용을 찾기 쉽도록 하는 것 처럼 테이블의 특정 컬럼에 인덱스를 만들어 검색 속도를 향상 시킬 수 있다.
• 레코드의 수가 많을 때에 인덱스가 유용하다.
• 인덱스를 만들어 두고 레코드의 값의 변경이 있다면 오히려 인덱스가 성능저하의 원인이 된다.

🌈인덱스의 생성

<기본문법>

create index 인덱스명 on 테이블명(컬럼명);

예시) 사원 이름에 대하여 인덱스를 생성

create index idx_ename on emp(ename);

🌈검색에 자주 사용하는 두 개이상의 컬럼으로 인덱스 생성

<기본문법>

create index 인덱스명 on 테이블명(컬럼1, 컬럼2);

• 인덱스가 구성된 테이블에 레코드의 추가, 수정, 삭제 작업이 있었다면 오히려 인덱스가 성능 저하의 원인이 될 수 있다. 이 때는 인덱스를 '재구성' 해주어야 한다.

🌈인덱스의 재구성

<기본문법>

alter index 인덱스명 rebuild;

• 테이블 생성 시에 pk로 설정한 컬럼은 자동으로 인덱스가 생성된다.

🌞PL/SQL

• 오라클 전용 DB 프로그래밍 언어

<종류>

• 종류에는 프로시저, 트리거, function 등이 있다.

🌈procedure (프로시저)

• 자바의 메소드처럼 빈번하게 사용하는 sql 명령어를 프로시저에 만들어두면 쉽게 사용할 수 있다.

🍁프로시저의 생성

<기본문법>

create or replace procedure 프로시저이름(변수이름 모드 자료형, ) --> 모드 in(입력용), out(출력용)
as
	지역변수 선언
begin
	프로시저가 해야할 명령어(들)
end;
/

예시) book 테이블에 새로운 레코드를 추가하는 프로시저 생성

create or replace procedure
insertBook(
	p_bookid in number,
	p_bookname in varchar2,
	p_publisher in varchar2,
	p_price in number
)
as
begin
	insert into book(bookid, bookname, publisher, price) 
	values(p_bookid, p_bookname, p_publisher, p_price);
end;
/

• 오류발생할 경우 오류를 확인하는 명령어

show errors;

예시) 고객번호, 고객명, 주소, 전화를 매개변수로 전달받아 새로운 고객을 등록하는 프로시저를 만들고, 호출해보자.

create or replace procedure
insertCustomer(
	p_custid in number,
	p_name in varchar2,
	p_address in varchar2,
	p_phone in varchar2
)
as
begin
	insert into customer(custid, name, address, phone)
	values(p_custid, p_name, p_address, p_phone);
end;
/

🍁cursor의 사용

• 프로시저 안에서 select 한 행의 수가 여러 건일 때는 cursor를 사용해야 한다.

예시) 부서위치를 매개변수로 전달받아 그 위치에 근무하는 모든 사원이름을 출력하는 프로시저를 생성하고 호출

create or replace procedure
printEmp(
		p_dloc varchar2
)
as
	p_ename varchar2(20);
	cursor c is select ename from emp where dno in (select dno from dept where dloc = p_dloc);
begin
	open c; //커저를 동작
	loop //루프를 돈다.
		fetch c into p_ename; //커저에 있는 것을 하나씩 뽑아온다.
		exit when c%NOTFOUND; // 더이상 뽑아올 레코드가 없으면 exit한다.
		dbms_output.put_line(p_ename); //뽑아올 사원이름을 출력
	end loop; //루프를 닫아준다.
	close c; //커저를 닫아준다.
end;
/

🌈trigger (트리거)

• SQL에서의 이벤트 처리를 위한 것으로써 어떤 테이블에서 이벤트(insert, update, delete)가 일어날 때에 자동으로 동작하는 프로시저를 말한다.

🍁트리거 만드는 방법

create or replace trigger 트리거이름
시점 명령어 on 테이블명 for each row
declare
	변수선언
begin
	트리거가 해야할 명령어(들)
end;
/ 

• 시점 : before, after가 올 수 있다.
• 명령어 : insert, update, delete가 올 수 있다.
• :new : 명령어가 실행된 새로운 레코드를 의미한다. 만약, insert가 일어났다면 insert된 레코드를 의미, 만약, update가 되었다면 update된 레코드를 의미
• :old : 명령어가 실행되기 전에 레코드를 의미한다. 만약, update가 되었다면 update되기 전의 레코드를 의미. 만약, delete가 되었다면 delete 되기 전의 레코드를 의미

🌈function

• select 절에서 사용할 수 있는 함수이다.