0616 Oracle SQL 5일차

외래키(foreign key)

references

create table employee(
eno number,
ename varchar2(10),
deptno number references dept(deptno)
);
이렇게 테이블을 짜면 dept라는 테이블의 deptno 외의
값이 들어가면 무결성 제약조건에 위배되어 
insert를 할 수 없다.

dept를 부모 테이블 deptno를 부모키
employee를 자식 테이블 
imployee의 deptno를 자식키 혹은 외래키라고 부른다.
이때 부모키는 꼭 기본키 혹은 고유키여야한다. 
(primary key 혹은 unique)

자식 레코드의 자식키에 사용된 부모키는
무결성 제약조건에 위배되어 삭제할 수 없다.
원만하게 삭제해주기 위해선 자식키를 생성할 때
on delete set null 를 붙여준다.
(부모키 해당 부분 삭제시 null로 설정)
on delete cascade
(부모키 해당 부분 삭제시 같이 삭제)
on delete 세팅을 하지 않고 부모키를 지우려면
drop table dept cascade constraints; 
를 통해 삭제할 수 있다.
(자식이 참고하는 부모키가 있어도 테이블을 지우겠다는 뜻)

create table employee(
eno number,
ename varchar2(10),
deptno number references dept(deptno) on delete set null
);

[제약조건]
primary key
not null
unique
check
forign key
default

dml - select, insert, update, delete
(테이블 내의 정보 삽입 수정등)
ddl  - create,alter,drop,rename,turncate (ddl은 무조건 자동커밋된다.)
(테이블 생성 정의 등)
dcl - grant,revoke (권한 부여 회수)


PLSQL : Procedural Languege SQL
절차 지향적 언어로 구성된 SQL 

시작시

set severoutput on

declare -선언
x number; -x 변수 선언
begin -시작

x := 100; -x변수에 100을 넣는다
dbms_output.put('값='||x); -- java의 print.
dbms_output.put_line('값='||x); -값= + x 출력
dbms_output.put('값='||x); --마지막은 _line으로 끝내야하므로
출력되지 않는다.
end; -끝


sql 내의 메소드를 사용할 수 있다.

declare
  x number;
  y number;
  z number;
  name varchar2(10);
  addr varchar2(10);
begin
  x := 10;
  y :=20;
  z := x+y;
  DBMS_OUTPUT.PUT_LINE('결과:'||z||'입니다');
  DBMS_OUTPUT.PUT('결과:'||(x+y)||'입니다'||chr(10));
chr(10): 엔터의 아스키 코드값.
  DBMS_OUTPUT.PUT_LINE('몫:'||round(12/7)||'입니다');
  DBMS_OUTPUT.PUT_LINE('몫:'||floor(12/7)||'입니다');
  DBMS_OUTPUT.PUT_LINE('나머지:'||mod(12,7)||'입니다');
  DBMS_OUTPUT.PUT(1);
  DBMS_OUTPUT.PUT_LINE(2);
  name := '김연아';
  addr := '서울';
  dbms_output.put_line('이름은 '||name||', 주소는 '||addr||'입니다.');
end;
 
테이블 호출해서 정보 추출
declare
    vname varchar2(12);
    vposition varchar2(12);
    vsalary number; --테이블 담을 변수
begin
-- select~ into는 조건이 하나만 있는 행이어야 한다
    select name,position,salary  --테이블 칼럼
    into vname,vposition,vsalary
    from company --테이블 명
    where sabun=3; -- 위치 선정 
    DBMS_OUTPUT.put_line(vname||','||vposition||','||vsalary);
end;

PLSQL로 레코드 추가하기

/
declare
    vno number;
    vname varchar2(10) :='슬기';
    vaddr varchar2(10) := '부산';
begin
    vno :=6;
    insert into customer
    values(vno,vname,vaddr);
    
end;
/
PLSQL로 입력값을 받아 레코드 추가하기
&~ : 입력 명령어

declare
    vno number := '&no';
    vname varchar2(10) :='&name';
    vaddr varchar2(10) := '&addr';
begin   
    insert into customer
    values(vno,vname,vaddr);
    
end;

축약(변수 선언 declare 파트 없이)
begin
  update consumer
  set c_name='&name',c_price='&price'
  where code='&code';
end;

PLSQL의 조건문
if 조건 then
처리할 문장1;
elsif 조건2 then
처리할 문장2;
else
처리할 문장3;
end if;

declare
  x number:=2;
  result varchar2(10);
begin
  if x=1 then
    result :='one';
  elsif x=2 then
    result :='two';
  elsif x=3 then
    result := 'three';
  else
    result :='그 밖의 수';
  end if;
  dbms_output.put_line('result: '||result); -- result: two
end;  

응용

declare
  vsabun number :='&sabun';
  vname varchar2(10);
  vdeptno number;
  vdept varchar2(10);
begin
  select name,deptno
  into vname,vdeptno
  from company
  where sabun=vsabun;
   
  if vdeptno=10 then
    vdept :='인사부';
  elsif vdeptno=20 then
    vdept :='개발부';
  elsif vdeptno=30 then
    vdept :='홍보부';
  else  
    vdept :='없는 부서';
end if;
dbms_output.put_line('사번 : '||vsabun||' 이름 : '||vname||' deptno : '||
    vdeptno||' 부서명 : '||vdept);
end;