2009 02 14 오라클 주말강의

Constraint (재약 조건 )

Select constraint_name,constraint_type,table_name from user_constraints;

1. 유니크 재약 조건 ( c2 , 중복체크 )
Create table t4 (c1 number , c2 number  , constraint t4_uq unique(c2));
insert into t4 values (1 ,null);
insert into t4 values (2 ,null);
insert into t4 values(4,3);
insert into t4 values(4,3);


2. Pk 재약조건(idx , 중복 배제 )
Create table t5(c1 number , c2 number , constraint t4_pk primary key(c2));

insert into t4 values(1,1);
insert into t4 values(2,1);

3.  Fk 재약조건(무결성 검사 , dept table과의 관계가 있나없나)
Create table t6(c1 number , c2 number , constraint t6_fk foreign key(t2) reference dept(deptno));

insert into t6(1  ,31);

4.check 재약조건  ( 해당 조건을 체크후 입력)
Create table t7(c1 number , c2 number , constraint t7_ck check(0<c2 and c2<10));






drop table orders
/
drop table menu
/
create table menu(
 mid number(2),
 name varchar2(30),
 price number(6),
 constraint menu_pk primary key(mid)
)
/
create table orders(
 id number(7),
 mid number(2),
 su number(3), 
 constraint orders_pk primary key(id),
 constraint orders_fk foreign key(mid)
 references menu(mid) on delete cascade
)
/
insert into menu values(1,'자장',4000)
/
insert into menu values(2,'짱뽕',4500)
/
insert into orders values(1,1,2)
/
insert into orders values(2,2,1)
/
insert into orders values(3,2,2)
/
select id,name,su*price "t_price"
 from orders natural join menu
/


drop table menu cascade constraint == 참조되는 테이블이 있어도 삭제 .

purge recyclebin; //쓰레기통 비우기.



scott 에게 권한 주기 (SQL> grant create view  , create synonym to scott;)



view 생성 Create view aemp as select * From emp;


create view semp as select ename , deptno from emp;
grant select on semp to public;  // 누구나 볼수있게


create view semp as select ename , deptno from emp with check option;


view 다중 삭제
 Select 'drop view '|| tname||';' from tab where tabtype='VIEW';

'DROPVIEW'||TNAME||';'
-----------------------------------------
drop view AEMP;
drop view EMPSMALL;
drop view EMPS2;

복사 후 붙혀 넣기.





index 생성

1.create index emp2_sal_ix on emp2(sal);
2. alter table emp2 modify constraint emp2_pk   (pk 생성시 인덱스 자동생성).

동의어생성 : create synonym e for employees;
    1. select * from employees;
    2. select * from e;





Sequences   (PK의 기본값으로 쓰임..)

1.nextval (next value)

뜻 : 다음번호
범위 : 전체 범위

2.currval(current value)

뜻 : 넣은 값
범위 : 세션 범위

 

예제) 
Create sequence s2
   increment by 2
   start with 4
   maxvalue 7
   minvalue 3
   cycle
   nocache;

실행 결과 )4 , 6 ,3, 5, 7, 3, 5 , 7