2022.04.28. 목요일 이공계 전문기술 연수사업 24일차

오늘 배운 것

뷰
실제로는 존재하지 않는 가상의 테이블을 말하여 빈번하게 사용하는 복잡한 sql을 만들어 둠으로써
편리하게 사용할 수 있음 또, 사용자마다 접근할 수 있는 컬럼을 제한할 수 있음

sqlplus
사용자명 입력: system
비밀번호 입력:
마지막 성공한 로그인 시간: 월 4월  25 2022 10:32:42 +09:00

다음에 접속됨:
Oracle Database 21c Express Edition Release 21.0.0.0.0 - Production
Version 21.3.0.0.0

SQL> create user c##hong identified by hong;

사용자가 생성되었습니다.

SQL> grant connect, resource, (dba) to c##hong;

권한이 부여되었습니다.

ex) c##sist가 자신의 테이블인 emp중에서 사원번호,사원이름,부서번호,주소,전화번호만 조회할 수 있는 뷰를 생성하여 
c##hong한테 권한을 부여
뷰 이름 : vw_emp
권한부여 : grant select on vw_emp to c##hong;
조회: select * from c##sist.vw_emp; 가능, select * from c##sist.book; 불가능
권한제거 : revoke select on vw_emp from c##hong;

뷰의 수정
create or replace view 뷰이름 as select문;
만약에 동일한 이름의 뷰가 있으면 수정해주고 없으면 새로 만들어줌

ex)대한민국에 거주하는 고객들로 구성된 뷰를 만들고 조회해보자
create view vw_customer as select * from customer where address like '%대한민국%';

ex)vw_customer는 주소가 대한민국인 고객을 보여줌. 이 뷰를 주소가 영국인 고객으로 변경
create or replace view vw_customer as select * from customer where address like '%영국%';

ex)고객이 주문한 내역에 대하여 주문번호,고객이름,도서명,주문가격,주문일을 조회하는 뷰를 생성
생성된 뷰를 통해 김연아 고객이 주문한 도서명,주문가격을 조회
SQL> create view vw_orders as select orderid,name,bookname,saleprice,orderdate from orders, customer where orders.custid=customer.custid;

뷰가 생성되었습니다.

SQL> select * from vw_orders where name='김연아';

   ORDERID NAME       BOOKNAME              SALEPRICE ORDERDAT
---------- ---------- -------------------- ---------- --------
        12 김연아     축구의 이해                8000 22/04/27
        10 김연아     케틀밸 스윙 접근하기      52000 22/04/27
         6 김연아     야구의 추억                6000 22/04/27
         2 김연아     축구의 이해               16000 22/04/27
         
SQL> alter table orders drop column bookname;
SQL> select * from vw_orders where name='김연아';
select * from vw_orders where name='김연아'
              *
1행에 오류:
ORA-04063: view "C##SIST.VW_ORDERS"에 오류가 있습니다


SQL> create view vw_orders as select orderid,name,bookname,saleprice,orderdate from orders, customer,book  where orders.custid=customer.custid and book.bookid=orders.bookid;
create view vw_orders as select orderid,name,bookname,saleprice,orderdate from orders, customer,book  where orders.custid=customer.custid and book.bookid=orders.bookid
            *
1행에 오류:
ORA-00955: 기존의 객체가 이름을 사용하고 있습니다.


SQL> create view vw_orders as select orderid,name,bookname,saleprice,orderdate from orders, customer,book b where orders.custid=customer.custid and b.bookid=orders.bookid;
create view vw_orders as select orderid,name,bookname,saleprice,orderdate from orders, customer,book b where orders.custid=customer.custid and b.bookid=orders.bookid
            *
1행에 오류:
ORA-00955: 기존의 객체가 이름을 사용하고 있습니다.


SQL> drop view vw_orders;

뷰가 삭제되었습니다.

SQL> create view vw_orders as select orderid,name,bookname,saleprice,orderdate from orders, customer,book b where orders.custid=customer.custid and b.bookid=orders.bookid;

뷰가 생성되었습니다.

SQL> select * from vw_orders where name='김연아';

   ORDERID NAME       BOOKNAME              SALEPRICE ORDERDAT
---------- ---------- -------------------- ---------- --------
        12 김연아     축구의 이해                8000 22/04/27
         2 김연아     축구의 이해               16000 22/04/27
         6 김연아     야구의 추억                6000 22/04/27
        10 김연아     케틀밸 스윙 접근하기      52000 22/04/27

sysdate는 년,월,일 뿐만아니라
시,분,초에 대한 정보도 함꼐 갖고있음
오늘 날짜이기는 하지만 도서를 추가할 때 시,분,초와 현재 조회할 떄 시,분,초가 달라서 레코드가 조회되지 않음

Q.뷰의 정의?
실제로 존재하지 않는 가상의 논리적인 테이블을 말하며 복잡한 sql을 미리 뷰를 만들어 둠으로써
쉽게 조회할 수 있고 사용자별 접근제한을 위하여 뷰를 사용함
뷰를 통해 추가,수정,삭제가 가능하며 with read only 옵션을 통해 읽기 전용의 뷰를 생성할 수 있고
with check option을 설정하여 조건에 맞는 레코드를 추가,수정할 수 있도록 뷰를 생성할 수 있음

------------------------------------------------------------------------------------------
시스템 뷰
오라클이 제공하는 뷰를 말함(데이터 사전이라고도 함)
user_objects 사용자가 만든 모든 객체의 정보를 갖고 있음
user_tables 사용자가 만든 모든 테이블의 정보를 갖고 있음
user_contraints 사용자가 만든 모든 제약의 정보를 갖고 있음

user_tables의 table_name으로 여태 만든 테이블을 조회할 수 있음
select table_name from user_tables;

SQL> select object_name from user_objects;

OBJECT_NAME
--------------------------------------------------------------------------------
STUDENT
SYS_C008315
MYBOOK
SYS_C008394
VW_BOOK
VW_EMP_20
VW_EMP
VW_ORDERS
SYS_C008319
ORDERS
CUSTOMER

OBJECT_NAME
--------------------------------------------------------------------------------
SYS_C008317
BOOK
SYS_C008320
DEPT
SYS_C008323
EMP
SYS_C008324
MEMBER
BOARD
SYS_C008357
SYS_C008358

OBJECT_NAME
--------------------------------------------------------------------------------
NEWBOOK
SYS_C008393
TEST
CUSTOMER100

26 행이 선택되었습니다.
데이터베이스에서 object란 객체 즉, 여태 만든 테이블,뷰,제약,인덱스 등을 뜻함

constraints - 제약의 이름,제약의 종류,어떤 테이블의 어떤 제약인지,제약의 상태(비활성화유무)
select owner,constraint_name,constraint_type,table_name,status from user_constraints;
select constraint_name,constraint_type,table_name,status from user_constraints;

CONSTRAINT_NAME
--------------------------------------------------------------------------------
CO
--
TABLE_NAME
--------------------------------------------------------------------------------
STATUS
----------------
SYS_C008325
R
EMP
ENABLED

emp테이블의 참조키에 대한 제약이 활성화되어있다는 뜻.
즉,emp테이블의 부서번호는 반드시 dept테이블의 부서번호를 참조해야 함

SQL> insert into emp(eno,ename,dno) values(2001,'신짱구',60);
insert into emp(eno,ename,dno) values(2001,'신짱구',60)
*
1행에 오류:
ORA-02291: 무결성 제약조건(C##SIST.SYS_C008325)이 위배되었습니다- 부모 키가 없습니다
=>참조 키에대한 제약이 활성화되어있기 때문에 부모테이블에 참조되는 키의 값이 없는 레코드는 추가할 수 없음

------------------------------------------------------------------------------------------------------------
제약의 비활성화
alter table 테이블명 disable constraint 제약명;

ex)emp테이블의 참조키에 대한 제약을 비활성화하고 dept에 존재하지 않은 부서번호로 레코드를 추가
SQL> alter table emp disable constraint SYS_C008325;

테이블이 변경되었습니다.

SQL> insert into emp(eno,ename,dno) values(2001,'신짱구',60);

1 개의 행이 만들어졌습니다.

//값으로써 테이블 이름은 대문자로 되어있기 때문에 대문자로 where절에 넣어줘야함(값으로써 테이블 이름의 제약이름을 검색할 때)
select constraint_name,constraint_type,table_name,status from user_constraints where table_name='EMP';

---------------------------------------------------------------------------------------------------------------
제약의 활성화
alter table 테이블이름 enable constraint 제약명;
제약을 활성화하려면 제약의 속성을 만족하는 값만 있을 때 활성화가 가능함!
제약의 속성을 만족하지 않는 레코드를 삭제한 후 제약을 활성화해야 함

SQL> alter table emp enable constraint SYS_C008325;

테이블이 변경되었습니다.

SQL> insert into emp(eno,ename,dno) values(2000,'dldldl',50);
insert into emp(eno,ename,dno) values(2000,'dldldl',50)
*
1행에 오류:
ORA-02291: 무결성 제약조건(C##SIST.SYS_C008325)이 위배되었습니다- 부모 키가
없습니다

----------------------------------------------------------------------------------------------------------------
인덱스
도서의 맨 뒤에 색인표를 만들어서 책 내용을 찾기 쉽도록 하는 것처럼
테이블의 특정 컬럼에 인덱스를 만들어 검색 속도를 향상시킬 수 있게하는 것
레코드의 수가 많으면 인덱스가 유용함
레코드의 수가 별로 없으면 인덱스가 유용하지 않음
변경이 잦은 테이블 레코드의 인덱스 생성은 성능 저하의 요인
빈번하지 않은 요소에 대해서 인덱스를 만들어야 검색 속도의 향상을 기대할 수 있음


인덱스의 생성
create index 인덱스이름 on 테이블이름(컬럼);

ex)사원 이름에 대하여 인덱스를 생성
SQL> create index idx_ename on emp(ename);

인덱스가 생성되었습니다.
=>데이터 양이 많을 때 사원명으로 검색할 때 검색속도의 향상을 기대할 수 있음
우리가 갖고 있는 데이터 양으로는 검색속도 향상을 기대할 수 없음

검색에 자주 사용하는 두 개 이상의 칼럼으로 인덱스를 생성할 수 있음
create index 인덱스이름 on 테이블이름(컬럼1,컬럼2);

ex)book 테이블의 출판사와 가격을 대상으로 인덱스를 생성
SQL> create index idx_book on book(publisher,price);

인덱스가 생성되었습니다.

인덱스를 만들었는데 그 인덱스가 적용된 레코드에 새로운 값이 추가되거나 수정 또는 삭제가 되었다면
인덱스를 재구성해줘야함.
인덱스가 구성된 테이블에 레코드의 수정,추가,삭제 작업이 있었다면
오히려 인덱스가 성능 저하의 원인이 될 수도 있음!

---------------------------------------------------------------------------------------
인덱스의 재구성
alter index 인덱스이름 rebuild;

ex)고객의 이름에 대하여 검색속도 향상을 위하여 인덱스를 생성
그 다음 고객이름으로 검색, 생성한 인덱스 삭제
SQL> create index idx_name on customer(name);

인덱스가 생성되었습니다.

SQL> select name from customer;

NAME
----------
박지성
김연아
장미란
추신수
손흥민
김연어

6 행이 선택되었습니다.

SQL> drop index idx_name;

인덱스가 삭제되었습니다.

테이블 생성 시에 pk로 설정한 컬럼은 자동으로 인덱스가 생성됨!(pk로 검색할 일이 많아서)
select index_name,table_name from user_indexes;
INDEX_NAME      TABLE_NAME
--------------- ----------
SYS_C008315     STUDENT
SYS_C008317     CUSTOMER
SYS_C008319     BOOK
SYS_C008320     ORDERS
SYS_C008323     DEPT
SYS_C008324     EMP
SYS_C008357     BOARD
SYS_C008358     MEMBER
SYS_C008393     NEWBOOK
SYS_C008394     MYBOOK
IDX_ENAME       EMP

INDEX_NAME      TABLE_NAME
--------------- ----------
IDX_BOOK        BOOK

-------------------------------------------------------------------------------------
데이터베이스 프로그래밍
프로그래밍이란? 프로그램을 설계하고 소스코드를 작성하여 디버깅하는 과정
데이터베이스 프로그래밍이란? dbms에 데이터를 정의하고 저장된 데이터를 변경하는 프로그램을 작성하는 과정
일반 프로그래밍과는 데이터베이스 언어인 sql을 포함한다는 점이 다음
sql/sql+자바 //결국 자바를 잘하라는 소리

sql 전용 프로그램
오라클에서는 pl/sql언어를 사용해서 sql 전용 프로그램을 사용할 수 있음

---------------------------------------------------------------------------------------
pl/sql
오라클 전용 db프로그래밍 언어

프로시저 : 자바에서 메소드처럼 생각. 어떤 sql을 실행하는 동작을 미리 만들어놓은 것
트리거 : 어떤 테이블에서 insert,update,delete가 일어났을 때 연쇄하여 동작해야할 일이 있을 때 사용
사용자 정의 함수 : select 절에서 사용할 수 있는 함수

---------------------------------------------------------------------------------------
프로시저
생성 : create or replace procedure 프로시저이름 (변수이름 모드 자료형, ... ) as 지역변수의 선언 begin 프로시저가 해야 할 명령어(들) end;
프로시저의 모드: on(레코드를 읽거나 추가하기 위해서),out(출력하기 위해서)

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;

생성한 프로시저를 통해 레코드 추가하기
exec insertBook(12,'즐거운 오라클','쌍용미디어',35000);
SQL> exec insertBook(12,'즐거운 오라클','쌍용미디어',35000);

PL/SQL 처리가 정상적으로 완료되었습니다.

ex)고객번호,고객명,주소,전화번호를 매개변수로 전달받아 새로운 고객을 등록하는 
프로시저를 만들고 호출해보자
SQL> create or replace procedure insertCustomer(
  2  p_custid in number,
  3  p_name in varchar2,
  4  p_address in varchar2,
  5  p_phone in varchar2
  6  )
  7  as
  8  begin
  9  insert into customer(custid,name,address,phone)
 10  values(p_custid,p_name,p_address,p_phone);
 11  end;
 12  /

프로시저가 생성되었습니다.

SQL> exec insertCustomer(7,'이이이','대한민국 서울','010-1234-5678');

PL/SQL 처리가 정상적으로 완료되었습니다.

ex)부서번호와 부서위치를 매개변수로 전달받아 해당 부서의 위치를 변경하는 프로시저를 만들고 호출
create or replace procedure updateDloc(
p_dno number,
p_dloc varchar2
)
as
begin
update dept set dloc=p_dloc where dno=p_dno;
end;
/
SQL> create or replace procedure updateDloc(
  2  p_dno number,
  3  p_dloc varchar2
  4  )
  5  as
  6  begin
  7  update dept set dloc=p_dloc where dno=p_dno;
  8  end;
  9  /

프로시저가 생성되었습니다.

SQL> exec updateDloc(10,'구로');

PL/SQL 처리가 정상적으로 완료되었습니다.

SQL> select * from dept;

  DNO DNAME      DLOC
----- ---------- ----------
   10 총무팀     구로
   20 기획팀     판교
   30 개발1팀    서교동
   40 개발2팀    판교

//begin 절에 오는 명령어에는 문자 자료형이어도 홑따옴표 안 씀!!!

ex)고객번호를 매개변수로 전달받아 해당 고객의 정보를 삭제하는 프로시저를 만들고 호출
create or replace procedure deleteCustomer(
p_custid number
)
as
begin
delete customer where custid=p_custid;
end;
/

SQL> create or replace procedure deleteCustomer(
  2  p_custid number
  3  )
  4  as
  5  begin
  6  delete customer where custid=p_custid;
  7  end;
  8  /

프로시저가 생성되었습니다.

SQL> exec deleteCustomer(7);

PL/SQL 처리가 정상적으로 완료되었습니다.

SQL> select * from customer;

CUSTID NAME       ADDRESS         PHONE
------ ---------- --------------- ---------------
     1 박지성     영국 맨체스타   000-5000-0001
     2 김연아     영국 토트넘     000-6000-0001
     3 장미란     대한민국 서울   000-7000-0001
     4 추신수     미국 뉴욕       000-8000-0001
     5 손흥민     대한민국 서울   010-1234-5678
     6 김연어     대한민국 인천   000-9000-0001

6 행이 선택되었습니다.

프로시저 만드는 것도 ddl임!
프로시저로 삭제한 작업을 취소(rollback)이 먹혀서 7번 소비자가 다시 생성될 수 있음 (이게 ddl 특인가..?)
exec 도 되고 call도 됨.

ex)부서번호를 매개변수로 전달받아 해당 부서의 위치를 출력하는 프로시저 생성
create or replace procedure printDloc(
p_dno number
)
as
p_dloc varchar2(20);
begin
select dloc into p_dloc from dept where dno=p_dno;
dbms_output.put_line(p_dloc);
end;
/
SQL> exec printDloc(40);

PL/SQL 처리가 정상적으로 완료되었습니다.
=>프로시저가 동작하였지만 화면에 결과가 출력되지 않았음
dbms_output.put_line으로 출력하려면 다음의 명령어를 먼저 실행해야 함
set serveroutput on;

SQL> set serveroutput on;
SQL> exec printDloc(40);
판교

PL/SQL 처리가 정상적으로 완료되었습니다.

ex)고객번호를 매개변수로 전달 받아 해당 고객의 총 주문금액을 출력하는 프로시저를 만들고 호출
create or replace procedure printSum(
p_custid number
)
as
p_sumprice number;
begin 
select sum(saleprice) into p_sumprice from orders where custid=p_custid;
dbms_output.put_line(p_sumprice);
end;
/

//출력과 반환은 다른가..?
//지역변수 필요한 경우와 필요하지 않은 경우?
//프로시저 안에서 출력은 못하나?

ex)고객번호를 매개변수로 전달받아 해당 고객의 총 주문금액을 반환하는 프로시저를 생성
create or replace procedure getSum(
p_custid in number,
p_sum out number
)
as
begin
select sum(saleprice) into p_sum from orders where custid=p_custid;
end;
/
=>프로시저 안에서 출력하는 것이 아니기때문에
프로시저 호출한 결과를 변수에 저장하고 그 변수의 내용을 출력해야 함
이것을 수행하기 위한 또 다른 pl/sql이 필요함

declare
변수선언
begin
명령어
end;
/

----------------------------------------------------------------------
getSum의 결과를 호출하는 pl/sql 생성

declare
result number;
begin
getSum(5,result);
dbms_output.put_line(result);
end;
/

------------------------------------------------------------------------------
cursor
프로시저 안에서 select한 행의 수가 여러 건일 때 cursor사용함
select name from customer where custid=1; =>이건 행의 수가 1건
select name from customer =>이건 행의 수가 여러 건이어서 cursor를 사용해야 함!

ex)부서위치를 매개변수로 전달받아 그 위치에 근무하는 모든 사원 이름을 출력하는 프로시저를 생성하고 호출
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;
dbms_output.put_line(p_ename);
end loop;
close c;
end;
/

SQL> exec printEmp('판교');
김도연
김한희
이유리
이진주
이창희
김소윤
이희승
조태헌
황예은

ex)관리자 이름을 매개변수로 전달받아 해당 관리자의 부하직원의 이름을 출력하는 프로시저를 만들고 호출
create or replace procedure printEmp2(
m_ename varchar2
)
as
e_ename varchar2(20);
cursor c is select e.ename from emp e, emp m where e.mgr=m.eno and m.ename=m_ename;
begin
open c;
loop
fetch c into e_ename;
exit when c%NOTFOUND;
dbms_output.put_line(e_ename);
end loop;
close c;
end;
/

SQL> exec printEmp2('박정수');
김도연
김민혁
김소윤
고주희

PL/SQL 처리가 정상적으로 완료되었습니다.

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

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

시점 : before,after가 올 수 있음
명령어 : insert,update,delete가 올 수 있음
:new : 현재 명령어가 실행된 새로운 레코드를 의미하는 키워드(만약 update가 일어났다면 update되고 난 후의 레코드)
:old : 현재 명령어가 실행되기 전의 레코드를 의미하는 키워드(만약 update가 일어났다면 update되기 전의 레코드!,만약 delete가 일어났다면 삭제되기 전 그 레코드를 의미함)

실습을 위하여 dept테이블을 복사해서 dept_back테이블 생성
create table dept_back as select * from dept;
 
ex)dept에 insert가 일어나면 자동으로 dept_back에도 insert 트리거 생성
create or replace trigger trig_dept_insert
after insert on dept for each row
declare
begin
insert into dept_back values (:new.dno,:new.dname,:new.dloc);
end;
/

SQL> insert into dept values(50,'개발3팀','강남');

1 개의 행이 만들어졌습니다.

SQL> select * from dept_back;

  DNO DNAME      DLOC
----- ---------- ----------
   10 총무팀     구로
   20 기획팀     판교
   30 개발1팀    서교동
   40 개발2팀    판교
   50 개발3팀    강남
   
ex)dept테이블에 삭제가 일어나면 dept_back에서도 삭제되도록 트리거를 생성한 후 호출
create or replace trigger trig_dept_delete
after delete on dept for each row
declare
begin
delete dept_back where dno =:old.dno;
end;
/
SQL> delete dept where dno=50;

1 행이 삭제되었습니다.

SQL> select * from dept_back;

  DNO DNAME      DLOC
----- ---------- ----------
   10 총무팀     구로
   20 기획팀     판교
   30 개발1팀    서교동
   40 개발2팀    판교

 

오늘 헷갈린 것

• 트리거는 똑같은 것을 실행한다기보다는 어떤 테이블에 한 sql이 실행되면 다른 테이블에도 이벤트리스너처럼 행동할 수 있다는 뜻이다. (dept에 insert되면 dept_back에는 delete되게 트리거 생성할 수 있음)
• 트리거의 :new,:old 다시 생각하기
• top3book sql내가 다시 한 번 해보기
• 어떤 문자열을 입력받아 도서명에 그 문자열이 포함된다면 '쌍용'으로 바꿔주는 프로시저 생성 후 호출 <- 이거 해결하기 !!!
• equals 메소드 오버라이딩 할 때 두 개의 관문!
• rollback 먹히는 것과 안 먹히는 것
• sysdate에는 시 분 초의 정보도 함께 있다는 것을 잊지 말기 

오늘 한 생각

졸린데 밤에 잠이 오지는 않는 기이한 현상..

앞으로 할 수 있으면 캡쳐해서 잘라서 올리자.. 난 이런걸 못해서 큰일이다 ㅠ