12/22 수업자료

drop table member;

create table member(
  id varchar(20)  primary key,
  name varchar(20) not null,
  email varchar(50) not null unique
);

insert into member values('hkd','홍길동', 'hkd@email.com');
insert into member values('lss','이순신', 'lss@lss.com');
insert into member values('hkt','홍길돈', 'hkd@email.com'); --입력불가

create table orderTest(
  no char(10) primary key,
  id varchar(20) references member(id),
  gId char(8)
);

insert into orderTest values('2016122210', 'hkd','E0000001');
insert into orderTest values('2016122211', 'www','E0000001');--에러

delete from member where id='hkd'; --에러. 주문테이블에 hkd이 남아 있으므로.

create table goodsTest
(
  gId char(8) primary key,
  name varchar(20)
);
insert into goodsTest values('E0000001','노트7');
insert into goodsTest values('E0000002','아이폰7');

select * from goodsTest;

-- 테이블 생성후 fk 추가하기
alter table orderTest
add constraint orderTest_gId_fk foreign key (gId)
references goodsTest(gId);

-- 제약조건 검색하기
select constraint_name
from user_constraints
where table_name='ORDERTEST';

alter table orderTest
drop constraint orderTest_gId_fk;

 

 

-- 출력기능 활성화
set serveroutput on;
--변수 선언
declare
vno employees.employee_id%type;
vname employees.first_name%type;
vdepartmentId employees.department_id%type;
vdname varchar(20); -- 부서명
begin
  select employee_id, first_name, department_id
  into vno, vname, vdepartmentId
  from employees
  where employee_id=&id; -- &id는 parameter역할.
 
  vdname:=case vdepartmentId
              when 10 then '관리부'
              when 20 then '영업부'
              when 30 then '구매부'
              when 40 then '인사부'
              else '기타'
          end;  
  dbms_output.put_line(vno||','||vname||','||vdname);
end;

declare
no1 number :=0;
begin
  loop
    DBMS_OUTPUT.PUT_LINE(no1); -- no1값 출력
    no1:=no1+1;
    exit when no1>5; -- loop 탈출조건.5보다 크면 종료.
  end loop;
end;

 

 

declare
no1 number:=0;
begin
  while no1<6 loop
    DBMS_OUTPUT.PUT_LINE(no1); -- no1값 출력
    no1:=no1+1;
  end loop;
end;

 

 

declare
no1 number:=0;
begin
  for no1 in 0..5 loop
    DBMS_OUTPUT.PUT_LINE(no1);
  end loop;
end;

 

 

declare
  v_empid employees.employee_id%type;
  v_fname employees.first_name%type;
  cursor c1 -- 커서 선언
  is
    select employee_id, first_name
    from employees
    where department_id=30;
begin
  open c1; -- 커서오픈
  loop
    -- 커서를 이용해서 한 행을 읽어서 변수에 저장.
    fetch c1 into v_empid,v_fname;
    -- 탈출조건. 데이터가 더이상 없는경우.
    exit when c1%NOTFOUND;
    DBMS_OUTPUT.PUT_LINE(v_empid||','||v_fname);
  end loop;
  close c1; -- 커서닫기
end;