--인덱스
--데이터베이스 테이블에 대한 검색성능의 속도를 높여주는 도구
--주로 where절/ order by 절에 사용되는 컬럼에 인덱스를 생성하면 검색성능 좋아짐
-- 단, 인덱스 관리를 위해 저장공간이 추가로 필요 (DB의 10%정도)
-- CREATE INDEX 이름 on 테이블명 (컬럼 , ...)
-- books 테이블의 bookname컬럼에 인덱스 생성
create index ix_book on books(bookname);
-- books 테이블의 publisher, price컬럼에 인덱스 생성
create index ix_book2 on books(PUBLISHER, PRICE);
-- 생성한 인덱스 ix_book 조회
select * from USER_INDEXES where TABLE_NAME = 'BOOKS';
--컬럼별 인덱스 정보 확인
select * from ALL_IND_COLUMNS where TABLE_NAME = 'BOOKS';
-- 생성한 인덱스를 재생성하기
alter index ix_book rebuild ;
-- 생성한 인덱스 ix_book 삭제
drop index ix_book2 ;
-- vip인 고객의 이름, 나이, 등급을 우수고객이라는 이름의 뷰로 생성
create view 우수고객
as
select name, age, grade
from "Customers" ; -- view는 보안이 강화됨.
drop view 우수고객;
create or replace view 우수고객
as
select USERID, name, age, grade from "Customers"
where grade = 'vip';
-- 제품테이블에서 제조업체별 제품수로 구성된 뷰를 '업체별제품수'라는 이름으로 생성 후,
-- 업체별 제품수 뷰의 모든 내용을 검색.
-- 먼저 검색 후 뷰 생성
select maker 제조업체, count(PRODID) 제품수
from "Products"
group by maker;
-- -->
create or replace view 업체별제품수
as
select maker 제조업체, count(PRODID) 제품수
from "Products"
group by maker;
select * from 업체별제품수;
-- '우수고객' 뷰에서 나이가 20이상인 고객 조회
select * from 우수고객
where age>20;
-- '업체별제품수'뷰에서 제품수가 3이상인 업체 조회
select * from 업체별제품수
where 제품수>=3;
-- 질의문으로 테이블과 뷰 구분
-- user_objects 라는 데이터 딕셔너리 이용
select OBJECT_NAME, OBJECT_TYPE from USER_OBJECTS
where OBJECT_NAME='우수고객'; -- 오브젝트 타입이 뷰임을 알수 있음
select *
from USER_VIEWS;
select *
from USER_tables;
-- ------
create or replace view emp (empid,fname,lname,email,phone,hdate,jobid,sal,comm,emgrid,deptid) --컬럼명 바꾸기
as
select * from EMPLOYEES;
select * from emp;
create or replace view dept (deptid, dname, dmgrid, locid)
as
select * from DEPARTMENTS;
select * from dept;
-- 사원 테이블과 부서 테이블을 join해서 'empdept' 뷰 만들기
select * from EMPLOYEES inner join DEPARTMENTS D on EMPLOYEES.DEPARTMENT_ID = D.DEPARTMENT_ID;
create or replace view empdept
as
select empid, fname, lname, email, phone, hdate, jobid, sal, comm, e.emgrid, d.deptid, dname, dmgrid, locid
from emp e inner join dept d
on e.deptid= d.deptid;
프로시듀어
-- PL/SQL
-- 데이터베이스 응용프로그램 개발에 사용하는 SQL 전용언어
-- SQL문으로 처리하기 어려운 문제를 해결해 줌
-- 기존의 SQL문으로는 반복/비교/오류 처리 불가능
-- SQL문 자체를 본안상의 이유로 캡슐화하기 어려움
-- Oracle : PL/SQL
-- Microsoft : T-SQL
-- create procedure/function 명령으로 생성
-- 생성된 프로시져나 함수는 execute 명령으로 실행함
-- 오류 발생시 show errors 명령으로 확인
-- 익명 프로시져 : declare 문으로 시작
-- 저장 프로시져 : create procedure 문으로 시작
-- 사용자 정의 함수 : create function 문으로 시작
-- PL/SQL 의 구조 : 선언부/실행부/예외처리부
-- 익명 프로시저 : 프로시저 이름 없음
-- dbmsoutput 활성화 (Ctrl+f8),
-- 저장 프로시져
-- PL/SQL 블록 외부에 선언해서 데이터베이스에 저장
-- PL/SQL 블록을 별도로 작성하므로 프로그램이 단순-모듈화
-- 서버 부하도 줄이고, 네트워크 트래픽도 감소-효율성
-- PL/SQL, 트리거, 각종 응용프로그램등에서 호출가능-재사용성
-- 프로그램 관리가 용이 - 유지보수성
-- CREATE OR REPLACE PROCEDURE 이름(IN/OUT/INOUT 매개변수목록)
-- IS
-- BEGIN
-- END;
-- 매개변수 : 입력/출력/겸용 전용 매개변수를 정의
-- 매개변수 유형을 정의하지 않으면 기본적으로 IN으로 설정
-- OUT 매개변수는 프로시져 내에서 코드를 실행한 후 결과를
-- 매개변수에 저장해서 프로시져 외부에서 참조할 수 있게 함
-- 매개변수 기본값 지정
-- 프로시져 실행시 반드시 매개변수의 갯수에 맞춰
-- 값을 전달해서 실행해야 함
-- 만약, 매개변수를 하나라도 누락하면 오류발생
-- 하지만, 매개변수에 기본값을 지정하면
-- 프로시져 실행시 해당 매개변수는 값을 전달 X
BEGIN --PL/SQL 블럭 시작 (실행부)
DBMS_OUTPUT.PUT_LINE('hello, world!!');
end; --PL/SQL 블럭 끝 의미
declare --선언부(변수, 상수 정의)
msg varchar(100);
vName varchar(10);
vAge number;
begin --실행부
msg := 'hello, world, again!!'; --변수에 값 대입
vName := '송혜교';
vAge := 35;
DBMS_OUTPUT.PUT_LINE(msg);
-- DBMS_OUTPUT.PUT_LINE(vName);
-- DBMS_OUTPUT.PUT_LINE(vAge);
DBMS_OUTPUT.PUT_LINE(vName || '/' || vAge);
end;
-- 테이블 조회 익명 프로시져
-- 1번 도서를 조회해서 결과표시 (단, 결과는 반드시 1행만 존재해야함)
declare
vbkname varchar(100);
begin -- select 컬럼명 into 변수명
select bookname
into vbkname
from BOOKS
where bookid = 1;
DBMS_OUTPUT.PUT_LINE(vbkname);
end;
-- 도서 총 가격, 평균가격, 최대/최소 가격을 출력하는 익명 프로시져 작성
declare --선언부(변수, 상수 정의)
sumprice number;
avgprice number(10,1);
maxprice number;
minprice number;
begin --실행부
select sum(price), avg(price),
max(price), min(price)
into sumprice, avgprice, maxprice, minprice
from BOOKS;
DBMS_OUTPUT.PUT_LINE(sumprice ||',' || avgprice ||',' || maxprice ||',' || minprice);
end;
-- 도서번호를 하나 입력받아 그 번호에 해당하는 도서 제목과 출판사 출력
set serveroutput on
accept bookid prompt '도서번호를 입력하세요'
declare -- vbookid varchar(10) := '&bookid';
vbookid varchar(10) := '&bookid';
vbkname varchar(100) ;
vpublish varchar(100) ;
begin
select bookname, publisher
into vbkname, vpublish
from BOOKS where bookid = vbookid;
dbms_output.put_line(vbkname ||','|| vpublish );
end; -- developer에서 실행
-- PL/SQL에서 select 문 사용시 주의사항
-- 검색된 행의 수가 '하나'여야 한다는 것
-- 검색 결과는 into 절을 이용해서 변수에 저장해야 함
-- order by 절은 사용불가
-- 변수에 저장된 값은 dbms_output.put_line()을
-- 이용해서 출력 가능
-- 검색된 결과가 작거나(no_data_found)
-- 많으면(too_many_rows) 오류 발생!
-- 프로시져 procedure
-- 익명 프로시져는 선언부에 정의하고 실행부에서 호출되어 실행
-- 실행할때 마다 컴파일되어야 하므로 속도가 느림
-- 다른 PL/SQL에서 호출해서 사용 불가 : 코드 재사용 불가
-- 익명 프로시져의 주 용도는 코드 실행 후 테스트 목적
-- 한번 실행하고나면 메모리에서 없어지는 휘발성 코드
-- 실행한 코드를 데이터베이스 내에 남겨
-- 재사용을 가능하게 하려면 함수나 프로시져를 작성
-- 사용자 정의 함수
-- 오라클은 내장함수를 통해 다양한 기능을 제공
-- 사용자가 임의로 코드를 작성해서 함수를 만들수 있음
-- 함수는 매개변수(입력값)를 받아 뭔가를 처리하고
-- 그 결과를 반환하는 데이터베이스 객체
-- create or replace function [-] (매개변수,...)
-- return 데이터유형;
-- is
-- 함수 몸체 블록
create or replace function sayhello
return varchar(100)
is
begin
REturn ('hello, world');
end;
select sayhello from dual;
begin sayhello
end;
--3개의 정수를 입력받아 더하고, 그 결과를 반환하는 함수
-- addplus(x,y,z)
create or replace function addplus(x number, y number, z number)
return number
is begin
return (x+y+z);
end;
select addplus(10,15,25) from dual;
--도서번호 입력시 상세 도서정보를 출력하는 함수 작성
-- showbookinfo(bookid)
create or replace function showbookinfo(bkid number)
return varchar
is
bkname varchar(100);
pubname varchar(100);
price number;
begin
select BOOKNAME, PUBLISHER, price
into bkname, pubname, price
from BOOKS where bookid=bkid;
return (bkname||','||pubname||','||price);
end;
select showbookinfo(1) from dual;
--함수실행방법 2
variable info varchar(100); --1회용 변수 선언
exec : info := showbookinfo(1);
print info;
--메모리상 저장되어, 실행시 빠르게 호출
create or replace procedure sayhello2
is
begin
DBMS_OUTPUT.PUT_LINE('hello, world!!');
end;
select SAYHELLO2() from dual;
begin sayhello2();
end;
--회원정보(아이디,비번,이름,이메일) 를 입력하는 프로시져 생성
create or replace procedure newmember(
puserid in varchar,
ppasswd in member.passwd%type,
pname in member.name%type,
pemail in member.EMAIL%type
)
is
begin
insert into member (userid, passwd, email, name)
values (puserid, ppasswd, pname, pemail);
DBMS_OUTPUT.PUT_LINE('입력완료!!');
end;
begin NEWMEMBER('abc', '123', 'xyz', '987');
end;
-- 커서 cursor
-- 이전 예제에서 select 문의 실행결과를 하나만 처리했었음
-- 한편, 오라클에서는 select 문의 실행결과로 복수행이
-- 반환되는 경우, cursor로 처리하도록 지원
-- 커서는 질의결과로 얻어진 복수행 집합 중
-- 각 행의 위치를 의미 : 결과집합에서 하나의 행을 읽어옴
-- 커서에는 암시적implicit/명시적explicit 커서가 존재
-- 일반적으로 암시적 커서는 단일행을 결과로 반환
-- 여러 행을 결과로 반환하는 SQL문에는 명시적 커서가 사용
-- cursor [이름] open ~ fetch ~ close 형식으로 커서를 정의
-- 정의된 커서는 for ~ loop ~ end loop 문으로 처리 : 결과출력
-- 커서의 속성을 제공하는 특수한 명령 지원 : %rowcount, %notfound, %isopen
-- 부서번호에 따라 사원들의 성, 직책을 조회하는 프로시져
--커서정의 : select 문을 실행한 후 결과를 저장해 둠 (복수행처리시)
create or replace procedure show_deptid( pdeptid emp.deptid%type )
is
cursor cs_dept(pdeptno emp.deptid%type)
is select lname, JOBID from EMP
where DEPTID=pdeptno;
begin
-- 커서를 호출하여 rs변수에 저장해두고
-- 반복처리를 통해 행별로 값을 읽어 결과 출력
for rs in cs_dept(pdeptid)
loop
DBMS_OUTPUT.PUT_LINE(rs.LNAME|| ','||rs.JOBID);
end loop;
end;
begin show_deptid(50);
end;
is
cursor cs_dept(pdeptno)
select distinct DEPARTMENT_ID
from EMPLOYEES;
create table dada ( elec varchar(100),
dress varchar(100),
music varchar(100));
insert into dada values (120, 160, 80);
select distinct dada from