6-2

5/23

 

2) insert ~~ select ~~ 구문

   : select 결과를 다른 테이블에 insert하는 구문

 

3) 트리거 (82p)

 

select * from student; 

1)원본 테이블
create table student(
 sno varchar2(3) ,
 sname nvarchar2(10),
 year varchar2(10),
 dept nvarchar2(10) ,
 constraint   student_sno_pk   primary  key(sno));
 

--삭제하기
 2)지워진 테이블을 보관할 테이블
 create table delStudent(
 sno varchar2(3) ,
 sname nvarchar2(10),
 year varchar2(10),
 dept nvarchar2(10) ,
 delStudent date, --삭제한 날짜
 constraint   delStudent_sno_pk   primary  key(sno));

3)트리거 생성 테이블
create or replace trigger trg_delStudent --만들거나 덮어씌우거나
after delete --삭제 후에 작동하게 지정
on student --트리거를 부착할 테이블
for each row --각 행마다 적용됨
begin
insert into delStudent 
values (:old.sno, :old.sname, :old.year, :old.dept, sysdate()); -- :old 테이블의 내용을 백업 테이블에 삽입
end;

delete from student 
where sno='600';

select * from student; --600 없음
select * from delStudent; --존재

2. update하기

 

2)

 create table upStudent(
 sno varchar2(3) ,
 sname nvarchar2(10),
 year varchar2(10),
 dept nvarchar2(10) ,
 upStudent date,
 constraint   upStudent_sno_pk   primary  key(sno));

 

3)  

create or replace trigger  trg_upStudent
after update
on student
for each row
begin
insert into upStudent 
values (:old.sno, :old.sname, :old.year, :old.dept, sysdate());
end;

update student set sname='둘리'
where sno='700';

select * from student;
select * from upStudent;

select sysdate from dual;  --22/05/23 수정한 날짜만 띄워보기

select sysdate+10 from dual; --22/06/02 연산이 가능함

 

insert into student(sno,sname,year,dept)
values('100','나연묵','4','컴퓨터');
insert into student(sno,sname,year,dept)
values('200','이찬영','3','전기');
insert into student(sno,sname,year,dept)
values('300','정기태','1','컴퓨터');
insert into student(sno,sname,year,dept)
values('400','송병호','4','컴퓨터');
insert into student(sno,sname,year,dept)
values('500','박종화','2','산공');
--여기까지 pk

insert into student (sno,sname,year,dept)
values('600','하늘이','4','만화');
insert into student (sno,sname,year,dept)
values('700','영심이','4','만화');

 

 

4) 데이터베이스 모델링 111p

 

5) 중복빼고 여러개의 컬럼을 조인해서 사용하기

select s.*, c.cno, cname, credit, grade, midterm, final 

from student s, enrol e, course c
where s.sno=e.sno and c.cno=e.cno;

 

--뷰로 만들어보기
create view v_join3
as
select s.*, c.cno, cname, credit, grade, midterm, final from student s, enrol e, course c
where s.sno=e.sno and c.cno=e.cno;

select * from v_join3;

 

create table course(
 cno varchar2(5) primary key,
 cname nvarchar2(10),
 credit varchar2(3),
 dept nvarchar2(10),
 prname nvarchar2(10));


create table enrol(
 sno varchar2(3) ,
 cno varchar2(5) ,
 grade varchar2(3),
 midterm int,
 final int ,
 constraint enrol_sno_fk foreign key(sno) references student(sno),
 constraint enrol_cno_fk foreign key(cno) references course(cno),
 constraint enrol_combo_pk primary key(sno, cno));


insert into student(sno,sname,year,dept)
values('100','나연묵','4','컴퓨터');
insert into student(sno,sname,year,dept)
values('200','이찬영','3','전기');
insert into student(sno,sname,year,dept)
values('300','정기태','1','컴퓨터');
insert into student(sno,sname,year,dept)
values('400','송병호','4','컴퓨터');
insert into student(sno,sname,year,dept)
values('500','박종화','2','산공');


insert into course(cno,cname,credit,dept,prname)
values('C123','프로그래밍','3','컴퓨터','김성기');
insert into course(cno,cname,credit,dept,prname)
values('C312','자료구조','3','컴퓨터','황수찬');
insert into course(cno,cname,credit,dept,prname)
values('C324','화일처리','3','컴퓨터','이규철');
insert into course(cno,cname,credit,dept,prname)
values('C413','데이터베이스','3','컴퓨터','이성호');
insert into course(cno,cname,credit,dept,prname)
values('E412','반도체','3','전자','홍봉희');


insert into enrol(sno,cno,grade,midterm,final)
values('100','C413','A',90,95);
insert into enrol(sno,cno,grade,midterm,final)
values('100','E412','A',95,95);
insert into enrol(sno,cno,grade,midterm,final)
values('200','C123','B',85,80);
insert into enrol(sno,cno,grade,midterm,final)
values('300','C312','A',90,95);
insert into enrol(sno,cno,grade,midterm,final)
values('300','C324','C',75,75);
insert into enrol(sno,cno,grade,midterm,final)
values('300','C413','A',95,90);
insert into enrol(sno,cno,grade,midterm,final)
values('400','C312','A',90,95);
insert into enrol(sno,cno,grade,midterm,final)
values('400','C324','A',95,90);
insert into enrol(sno,cno,grade,midterm,final)
values('400','C413','B',80,85);
insert into enrol(sno,cno,grade,midterm,final)
values('400','E412','C',65,75);
insert into enrol(sno,cno,grade,midterm,final)
values('500','C312','B',85,80);
insert into enrol(sno,cno,grade,midterm,final)
values('400','C123','A',90,90);