Oracle SQL: ALTER
인덱스를 만드는 방법
create index 인덱스이름 on 테이블명(컬럼명)
인덱스를 재구성
alter index 인덱스이름 rebuild
인덱스 삭제
drop index 인덱스이름
create index idx_customer_name on customer(name);
select * from customer where name=’윤석열’;
update customer set name = ‘윤자바’ where custid = 6; 이와 같이 인덱스를 만들어둔 데이터에 수정이 있으면 오히려 검색속도를 떨어트릴 수 있따.
alter index index_customer_name rebuild;
select * from customer where name = ‘윤자바’; 인덱스가 효과를 발휘하려면 데이터의 수정이 빈번하지 않고 또, 인덱스를 설정한 컬럼의 같은 값의 종류가 많지 않아야 의미가 있다.
select custid, (select address from customer cs where cs.custid=od.custid) address, sum(saleprice) total from orders od group by od.custid;
=>주소를 출력하기 위하여 select절에 상관서브쿼리 이용
select sum(saleprice) total from orders od where exists (select * from customer cs where custid<=3 and cs.custid = od.custid);
select sum(saleprcie) from orders where custid <=3;
뷰
장점 복잡한 검색을 뷰를 이용하면 간편하게 사용할 수 있다. 보안유지상 사용자별로 접근권한을 다르게 설정할 수 있다.
사용자별로 접근권한을 다르게 설정하여 관리하는 것이 번거로울 수 있다. 뷰의 종류에 따라 with read only, with check option 등의 설정 및 관리가 필요하다.
create view highorders as select b.bookid, bookname, name, publisher, saleprice from book b, customer c, orders o where b.bookid = o.bookid and c.custid = o.custid and saleprice >=20000;
뷰 삭제
drop view 뷰이름
dop view highorders;
select bookname, name from highorders;
create or replace view highorders as select b.bookid, bookname, name, publisher, saleprice from book b, customer c, orders o where b.bookid = o.bookid and c.custid = o.custid and saleprice >=30000;
select ename from emp where mgr is null; select ename from emp where mgr is not null;
select ename, dname from emp e, dept d where e.dno = d.dno;
select ename, (select dname from dept d where e.dno = d.dno) dname from emp e;
select ename from empe, dept d where e.dno = d.dno and dloc=’서교동’; select ename from emp e, (select * from dept where dloc = ‘서교동’) d where e.dno = d.dno; select ename from emp e where dno in (select dno from dept where dloc=’서교동’); select ename from emp e exists (select dno from dept d where dloc=’서교동’ and d.dno=e.dno);
select enmae from emp where salary >(select avg(salary) from emp);
select ename from emp e1 where salary > (select avg(salary) from emp e2 where e1.dno = e2.dno);
프로시저
프로시저 만들기
create procedure 프로시저이름(
변수이름 모드 자료형,
변수이름 모드 자료형.
..
)
as
지역변수(들)
begin
프로시저가 해야할 sql명령어들
end;
모드의 종류 in 입력용 out 출력용
연습) 도서번호, 도서명, 출판사, 가격을 매개변수로 전달받아 도서를 추가하는 프로시저를 만들고 프로시저를 호출하여 insert를 수행해봅시다.
create procedure insertBook( myBookID in book.bookid%TYPE, myBookName in book.bookname%TYPE, myPublisher in book.publisher%TYPE, myPrice in book.price %TYPE ) as begin insert into book values(myBookID, myBookName, myPublisher, myPrice); end; /
프로시저 호출
exec 프로시저이름(값1,값2..)
exec insertBook(16,’재미있는 pl/sql’,’쌍용미디어’,30000);
create procedure updatePrice( myBookID in book.bookid%TYPE, myPrice in book.price%TYPE ) as begin update book set price = myPrice where bookid = myBookID; end; /
exec updatePrice(11,20000);
create or replace
없으면 생성하고 있으면 변경해라
create or replace procedure deleteBook( myBookID in book.bookid%TYPE ) as begin delete from book where bookid = myBookID; end; /
exec deleteBook(16);
** PL/SQL에서도 제어문을 사용할 수 있다. 조건에 따라 실행할 SQL을 선택하도록 할 수 있다.**
연습) 도서번호, 도서명, 출판사, 가격을 매개변수로 전달받아 해당도서가 이미 있다면 도서의 가격을 수정하고 그렇지 않다면 도서를 추가하는 프로시저를 만든다
create or replace procedure BookInsertOrUpdate( myBookID book.bookid%TYPE, myBookName book.bookname%TYPE, myPublisher book.publisher%TYPE, myPrice book.price%TYPE ) as mycount number; begin select count(*) into mycount from book where bookname = myBookName; if mycount!=0 then update book set price = myPrice where bookname = myBookName; else insert into book values(myBookID, myBookName, myPublisher, myPrice); end if; end; /
exec BookInsertOrUpdate(13,’재미있는 오라클’,’쌍용미디어’,10000); exec BookInsertOrUpdate(17,’즐거운 jsp’,’쌍용미디어’,35000);
반환값이 있는 프로시저 만들기
create procedure 프로시저이름
(
변수명 out 자료형
)
is
begin
select 컬럼명 into 변수명 from ~~;
end;
/
==> 검색한 결과를 변수에 담아서 반환.
반환값이 있는 프로시저 호출하기
(변수를 선언하고 프로시저를 호출하고 반환값을 확인(출력)하기위한 프로그램이 필요하다) PL/SQL 구문이 필요!
set serveroutput on; <-화면에 결과를 출력하기 위해서 필요한 설정
PL/SQL의 기본 구조 declare 변수명 자료형; begin 프로시저호출(변수명); <- 프로시저에서 전달하는 변수에 결과를 담아줌 dbms_output.put_line(변수명) <- console에 출력하는 명령 end;
도서의 평균가격을 반환하는 프로시저
create or replace procedure AveragePrice( averageVal out number ) as begin select avg(price) into averageVal from book where price is not null; end; /
set serveroutput on; -> cmd창을 닫기 전까지 한번만 동작시켜두면 된다.
declare result number; begin AveragePrice(result); dbms_output.put_line(‘책값 평균’||result); end; /
고객명을 매개변수로 전달받아 그 고객이 주문한 총주문건ㄱ수, 총 주문액을 반환 프로시저 만들자
create or replace procedure getCustomerOrders( cname in customer.name%TYPE, cnt out number, tot out number ) as begin select count(*), sum(saleprice) into cnt,tot from orders where custid= (select custid from customer where name = cname); end; /
declare cnt number; tot number; begin getCustomerOrders(‘박지성’,cnt,tot); dbms_output.put_line(‘총구매건수:’||cnt||’,총구매금액:’||tot); end; /
반환값이 있는 프로시저 호출하는 또다른 방법
variable 변수명 자료형;
exec 프로시저이름(:변수명)
print 변수명
variable cnt number; variable tot number; exec getCustomerOrders(‘박지성’,:cnt,:tot); print cnt; print tot;
연습) 고객번호, 이름, 주소, 전화번호를 매개변수로 전달받아 insert수행
create or replace procedure InsertCustomer( cid in customer.custid%TYPE, cname in customer.name%TYPE, caddr in customer.address%TYPE, cphone in customer.phone%TYPE ) as begin insert into customer values(cid, cname, caddr, cphone); end; /
exec insertCustomer(1,’방탄소년단’,’미국’,’010-1234-4567’);
연습2) 수정 프로시저
create or replace procedure UpdateCustomer( cid in customer.custid%TYPE, cname in customer.name%TYPE, caddr in customer.address%TYPE, cphone in customer.phone%TYPE ) as begin update customer set name=cname, address=caddr, phone=cphone where custid=cid; end; /
exec UpdateCustomer(17,’방탄’,’미국’,’010-1234-4567’);
연습3) 삭제 프로시저
create or replace procedure DeleteCustomer( cid in customer.custid%TYPE ) as begin delete from customer where custid = cid; end; /
exec DeleteCustomer(17);
연습4) 조회 프로시저
create or replace procedure findCustomer( cid in customer.custid%TYPE, cname out customer.name%TYPE, cphone out customer.phone%TYPE, caddr out customer.address%TYPE ) as begin select name, phone, address into cname, cphone, caddr from customer where custid = cid; end; /
declare name customer.name%type; phone customer.phone%type; addr customer.address%type; begin findCustomer(1,name,phone,addr); dbms_output.put_line(name||phone||addr); end; /
커서를 사용하는 프로시저
cursor의 사용 프로시저안에서 select한 결과가 여러건일때 사용
** cursor를 사용하는 방법**
cursor 정의
create procedure 프로시저이름(
매개변수(들)
)
as
변수1 자료형;
변수2 자료형;
...
cursor 커서이름 is select 컬럼1, 컬럼2 from ~;
begin
open 커서이름 커서에 정의된 select문 실행
loop 반복문 수행
fetch 커서이름 into 변수1, 변수2 ... 커서의 내용을 한행씩 변수에 담기
exit when 커서이름%NOTFOUND 커서에 더이상 읽어올 레코드가 없다면 반복문을 탈출
end loop;
close 커서이름;
end;
/
orders 테이블로부터 총판매이익을 계산하여 출력하는 프로시저를 만들고 호출해봅시다. 판매금액이 30000원 이상이면 10프로 이익이고 그렇지않으면 5프로 이익입니다.
모든 주문에 대하여 이익금을 계산하고 그 이익금에 총합을 누적하여 출력
select saleprice from orders;
set serveroutput on; create or replace procedure Interest as cursor InterestCursor is select saleprice from orders; price number; total number; begin total :=0; open InterestCursor; loop fetch InterestCursor into price; exit when InterestCursor%NOTFOUND; if price >=30000 then total := total + price0.1; else total := total + price0.05; end if; end loop; close InterestCursor; dbms_output.put_line(‘전체이익금액=’||total); end; /
exec Interest
연습
create or replace procedure printCustomer( pname in customer.name%TYPE ) as pbookname book.bookname%type; pprice book.price%type; psaleprice orders.saleprice%type; porderdate orders.orderdate%type; cursor c is select bookname, price, saleprice, orderdate from customer c, book b, orders o where c.custid = o.custid and b.bookid = o.bookid and name = pname; begin open c; loop fetch c into pbookname, pprice, psaleprice, porderdate; exit when c%NOTFOUND; dbms_output.put_line(pbookname||’ ‘||pprice||’ ‘||psaleprice||’ ‘||porderdate); end loop; close c; end; /
select bookname, price, saleprice, orderdate from customer c, book b, orders o where c.custid = o.custid and b.bookid = o.bookid and name = ‘박지성’;
exec printCustomer(‘박지성’);
create view salecount as select * from (select (select bookname from book b where b.bookid = o.bookid) bookname,count() from orders o where to_char(orderdate,’yyyy/mm/dd’)=to_char(sysdate,’yyyy/mm/dd’) group by bookid order by count() desc) where rownum<=3;
select (select bookname from book b where b.bookid = o.bookid) bookname,count() from orders o where to_char(orderdate,’yyyy/mm/dd’)=to_char(sysdate,’yyyy/mm/dd’) group by bookid order by count() desc;
create view today_top3 as select * from(select bookname, count(saleprice) cnt from book b, orders o where b.bookid = o.bookid and to_char(sysdate,’yyyy/mm/dd’)=to_char(orderdate,’yyyy/mm/dd’) group by bookname order by count(saleprice) desc) where rownum <=3;
select bookname, count(saleprice) cnt from book b, orders o where b.bookid = o.bookid and to_char(sysdate,’yyyy/mm/dd’)=to_char(orderdate,’yyyy/mm/dd’) group by bookname order by count(saleprice) desc;
PL/SQL 종류
- procedure 자바의 메소드처럼 만들어두고 호출하여 insert, update, delete, select 할 수 있다. 매개변수가 있을수도, 없을수도 잇고 리턴값이 있을수도 없을 수도 있고
-trigger sql명령어의 이벤트처리를 위한 거승로써 어떤 A테이블에 insert, update, delete가 발생되면 연관있는 B테이블에 연쇄하여 insert, update, delete를 자동으로 하게 만드는 것
-function 프로시저와 마찬가지로 사용되는데 이것은 select절에 사용할 용도로 만든다. 반드시 반환값이 있도록 만들어야 한다.
select count(saleprice) from orders; 위의 sql에서 사용한 count처럼 select절에 사용할 함수를 만들때에는 fuction으로 만든다.
프로시저 만들기
create or replace procedure 프로시저이름 ( 매개변수1[모드] 자료형,
) as 지역변수명1 자료형; begin 프로시저가 해야할 명령어 end; /
** 모드에는 in, out이 있다. in은 프로시저를 실행하기에 필요한 값을 읽어들이기 위한 입력용, out은 프로시저 수행한 결과를 반환하기 위한 출력용 모드를 생략하면 기본은 in이다.**
begin과 end 사이에 프로시저가 해야할 명령어들을 써준다.
pl/sql에서 대입연산자는 := 이렇게 표현한다.
새로운 부서를 등록하는 프로시저를 생성 create or replace procedure insert_dept( p_dno dept.dno%type, p_dname dept.dname%type, p_dloc dept.dloc%type ) as begin insert into dept(dno,dname,dloc) values(p_dno,p_dname,p_dloc); end; / exec insert_dept(50,’개발3팀’,’제주’);
create or replace procedure delet_dept( p_dno dept.dno%type ) as begin delete dept where dno = p_dno; end; /
set serveroutput on; create or replace procedure print_dloc( p_dno in dept.dno%type ) as p_dloc dept.dloc%type; begin select dloc into p_dloc from dept where dno = p_dno; dbms_output.put_line(p_dloc); end; /
create or replace procedure get_dloc( p_dno in dept.dno%type, p_dloc out dept.dloc%type //반환하니까 ) as begin select dloc into p_dloc from dept where dno = p_dno; end; /
declare result dept.dloc%type; //반환한거 받아올 변수 선언 begin get_dloc(10,result); dbms_output.put_line(result); end; /
cursor의 사용 select하나 행의 수가 여러줄일때 cursor사용
create or replace procedure print_dname( p_dloc int dept.dloc%type ) as p_dname dept.dname%type; cursor c is select dname from dept where dloc = p_dloc; begin open c; loop fetch c into p_dname; exit when c%NOTFOUND; dbms_output.put_line(p_dname); end loop; close c; end; / —
트리거
:sql에서의 이벤트처리를 위한 것으로써 어떠한 테이블에 이벤트(insert, update,delete)가 일어날때에 자동으로 동작하는 프로시저를 말한다.
트리거 만드는 방법
create or replace trigger 트리거이름 시점 명령어 on 테이블이름 for each row declare 변수명1 자료형 begin 트리거가 해야할 명령어 end; / ** 시점에는 before, after가 올 수 있다**
명령어에는 insert, update, delete가 올수잇따
:new
:old
create or replace trigger trg_afterInsertDept after insert on dept for each row declare begin insert into dept_back values(:new.dno,:new.dname,:new.dloc); end; /
insert into dept values(50, ‘개발3팀’, ‘제주’);
create or replace trigger trg_afterupdateDept after update on dept for each row declare begin update dept_back set dname=:new.dname,dloc=:new.dloc where dno=:new.dno; end; / update dept set dloc=’서울’ where dno=50;
create or replace trigger trg_afterdeleteDept after delete on dept for each row declare begin delete dept_back where dno=:old.dno; end; / delete dept where dno=50;
create table 상품( 상품번호 number primary key, 상품명 varchar2(30), 가격 number, 재고수량 number );
create table 입고( 입고번호 number primary key, 상품번호 number references 상품(상품번호), 입고수량 number, 입고일 date );
insert into 상품 values(100,’색종이’,500,0); insert into 상품 values(200,’삼각자’,700,0); insert into 상품 values(300,’딱풀’,600,0);
create or replace trigger trg_insert입고 after insert on 입고 for each row declare begin update 상품 set 재고수량=재고수량 + :new.입고수량 where 상품번호=:new.상품번호; end; /
insert into 입고 values(1,100,50,sysdate); insert into 입고 values(4,100,100,sysdate); drop trigger trg_delete입고;
create or replace trigger trg_afterDelete입고 after delete on 입고 for each row declare begin update 상품 set 재고수량= 재고수량 - :old.입고수량 where 상품번호=:old.상품번호; end; / delete from 입고 where 입고번호=4;
insert into 입고 values(1,100,10,sysdate); insert into 입고 values(2,200,10,sysdate); insert into 입고 values(3,300,50,sysdate);
create or replace trigger trg_update입고 after update on 입고 for each row declare cnt 입고.입고수량%type; cnt := :old.입고수량 - :new.입고수량; begin update 상품 set 재고수량= 재고수량 - cnt where 상품번호=:new.상품번호; end; /
create or replace trigger trg_update입고 after update on 입고 for each row declare begin update 상품 set 재고수량= 재고수량 - (:old.입고수량 - :new.입고수량) where 상품번호=:new.상품번호; end; /
insert into 입고 values(4,300,100,sysdate); update 입고 set
function 만드는 방법
create or replace fuction 함수명(매개변수명 자료형)
판매금액을 매개변수로 전달받아 이익금을 반환하는 사용자저으이 함수
create or replace function profit(saleprice number) return number is result number; begin if saleprice >= 30000 then result :=saleprice0.1; else result :=saleprice0.5; end if; return result; end; /
select bookname, price, saleprice, profit(saleprice) 이익금 from book b, orders o where b.bookid = o.bookid and to_char(sysdate,’yyyy/mm/dd’)=to_char(orderdate,’yyyy/mm/dd’);
create or replace function profit(saleprice number) return number is result number; begin if saleprice >= 30000 then result :=saleprice0.1; else result :=saleprice0.5; end if; return result; end; /
create or replace function Grade(p_custid number) return varchar2 is result varchar2(30); tot number; begin select sum(saleprice) into tot from orders where custid = p_custid; if tot >=20000 then result :=’우수’; else result :=’보통’; end if; return result; end; /
** 고객의 이름을 고객의 등급을 출력해보자
select name, Grade(custid) grade from customer;
select sum(saleprice) from orders where custid = 1 group by custid ;
select name, (select sum(saleprice) from orders o where o.custid = c.custid) sum , grade(custid) grade from customer c;
create or replace function Domestic(p_addr varchar2) return varchar2 is result varchar2(30); begin if p_addr like ‘대한민국%’ then result:=’국내거주’; else result:=’국외거주’; end if; return result; end; /
select name, Domestic(address) domestic from customer;
select name, Grade(custid), Domestic(address) from customer;
select Domestic(address), sum(saleprice) from customer c, orders o where c.custid = o.custid group by Domestic(address);
select bookname, price from book where publisher = ‘이상미디어’;
select publisher, sum(saleprice) from book b, orders o where b.bookid = o.bookid group by publisher;
select publisher, bookname, price, (select avg(price) from book b3 where b1.publisher=b3.publisher) avg from book b1 where price > (select avg(price) from book b2 where b1.publisher=b2.publisher) order by publisher;
select * from () avg where b.bookid = avg.bookid;
select avg(price) from book group by publisher; select name from customer;