Oracle 7

 

A테이블을 B가 참조하여 정보를 입력할떄 B의 테이블에 A를 참조한 자료가 있는경우 A의 참조자료는 삭제가 되지 않는다. B자료는 자식자료이므로 삭제가 가능하다.  만일 B가 A자료중 참조하지않는 자료가있다면 그 자료는 삭제가 가능하다.

이럴떄 삭제를 하고싶을때 제약조건의 비활성화를 하는 방법이있다.

(기존에 외부키제약조건 추가문에 on delete casecade를 붙여주면 됨)->참조되는 테이블A를 지우면 B에 A를참조했던 테이블의 자료까지 삭제해줌

alter table t2 add constraint t2_deptno_fk foreign key(deptno) references t1(deptno) on delete cascade

A의 자료를 삭제하되 참조되고있는 B테이블의 자료까지 삭제하지 않고 자료는 그대로 두고싶은 경우, A를 참조했떤 레코드에는 null값을 준다.

alter table t2 add constraint t2_deptno_fk foreign key(deptno) references t1(deptno) on delete set null;

 

뷰만들기

create view 뷰이름

as select first_name, last_name, salary from employees;  ->뷰 생성

select * from 뷰이름;  ->뷰확인

create or replace view 뷰이름 ->뷰 업데이트

권한부여하기 ->Grant /revoke

system/manager 로 접속해서

ex)테이블 생성해도되~>Grant create view to 권한부여받은사람 명령어 입력

scott에게 뷰생성권한을 주었으므로 삭제나 수정역시  scott이 가능함

뷰는 실질적인 정보가 아닌 문서에 대한 정보이다. (user_views로 확인)

값을 insert할때 create view했을떄 주었던 조건과 다른 자료를 입력하게 되면 입력은 되지만 자료는 보여지지 않는다. 따라서 뷰를 생성할떄 with check option을 주어서 조건에 위배되는 값은 입력되지 못하게 할 수 있다.

 

뷰이름:5v50

50번 부서이름, first_name,salst_name,max salary, min salary, avg salary

create or replace view vdept
as select department_name 부서명, avg(salary) 평균연봉,min(salary) 최소연봉,max(salary) 최고연봉
from employees, departments
where employees.department_id=departments.department_id
group by  department_name
/

Q.과제1>

뷰이름:v_info

이름,이메일,입사일연봉,manager의 이름, 부서명

select e.first_name name,e.email email,e.hire_date hiredate,e.salary salary,d.department_name dname,m.first_name manager
from employees e, departments d,employees m
where e.department_id=d.department_id
and e.manager_id =m.employee_id
/

뷰중에 자료입력삭제없이 읽기만 가능하게 하는 구문->create view문에서 with read only를 써서 사용

시퀀스

create sequence seq;

자료입력시 값을 입력할때마다 증가 count시켜줌

select seq.currval from dual  ->현재 seq에 입력되어있는 자료의 개수

select seq.nextval from dual   ->다음 seq에 입력될 자료번호

 

declare

변수 형식선언 초기값

변수:=

begin dbms_output.put_line('출력하고자 하는 내용');

...

end;

ex)

declare
myname varchar2(20):='hong gil dong';
begin dbms_output.put_line('my name is:'|| myname);
myname:='Steven';
dbms_output.put_line('my name is:'||myname);
end;

변수의 타입을 복사하는것 -.

vempno emp.empno%type                  (emp에 empno의 타입이 vempno에 복사됨)

set server output on    

 

반복문

declare
begin
   for i in 1..100 loop
      if(mod(i,2))=0  then
 dbms_output.put_line(i);      //값을 저장하고 싶으면 insert into p1 values(i);
      end if;
   end loop;
end;
/

declare
vempno emp.empno%type;
vename emp.ename%type;
vdeptno emp.deptno%type;
vdname dept.dname%type :=null;
begin
    select empno,ename,deptno
    into vempno,vename,vdeptno
    from emp
    where empno=7788;
        dbms_output.put_line(vempno||vename||vdeptno);
end;
/

 

declare
vempno emp.empno%type;
vename emp.ename%type;
vdeptno emp.deptno%type;
vdname dept.dname%type :=null;
begin
    select empno,ename,deptno
    into vempno,vename,vdeptno
    from emp
    where empno=7788;
if vdeptno=10 then
vdname:='ACCOUNT';
end if;
if vdeptno=20 then
vdname:='RESEARCH';
end if;
if vdeptno=30 then
vdname:='SALES';
end if;
if vdeptno=40 then
vname:='OPERATIONS';
end if;
    dbms_output.put_line(' empno  ename  deptno  vdname');
   dbms_output.put_line('--------------------------------------');
dbms_output.put_line(vempno||'  '||vename||'  '||vdeptno||'  '||vdname);
end;
/

 

sal<1000    1000미만

>=1000 and <2000   2000미만

<=2000 and <3000   30000미만

>=3000   3000이상

7900사원의 정보는 다음과 같습니다.

empno, ename,job,sal result

============================

결과:

declare
vempno emp.empno%type;
vename emp.ename%type;
vjob emp.job%type;
vsal emp.sal%type;
vresult varchar(10);
begin
select empno,ename,job,sal into vempno,vename,vjob,vsal
from emp where empno=7900;
if vsal<1000 then
vresult:='1000미만';
end if;
if vsal between 1000 and 2000 then
vresult:='2000미만';
end if;
if vsal between 2000 and 3000 then
vresult:='3000미만';
end if;
if vsal >=3000 then
vresult:='3000이상';
end if;
dbms_output.put_line('7900번 사원의 정보는 다음과 같습니다.');
dbms_output.put_line('empno  ename  job  sal  result');
dbms_output.put_line(vempno||'  '||vename||'  '||vjob||'  '||vsal||'  '||vresult);
end;
/

과제2>테이블 만들기

이름  주민번호

aaa   80010101

 

출력결과 :

이름 주민번호  나이  성별

=========================                                ->plsql로

 

declare
v ex%rowtype;
vgender varchar2(5);
vage varchar2(3);
begin
select name,jumin,trunc((sysdate-to_date(substr(jumin,1,6),'rrmmdd'))/365),
substr(jumin,8,1) into v.name,v.jumin,vage,vgender from ex;
if vgender='1' then
vgender:='남';
else
vgender:='여';
end if;
dbms_output.put_line('이름    주민번호    성별    나이');
dbms_output.put_line(v.name||'  '||v.jumin||'  '||vgender||'  '||vage);
end;
/

조건1   <3000  :초급기술자     >=3000 and <8000  :중급기술자    >=8000 and <=12000 : 고급기술자

>=12000 ; 초대박기술자    -비고

조건2

연봉..commission_pct 자료가 있으면 적용출력(salary=salary+comm) 아니면 그냥 salary만 출력  -연봉

조건3  연봉을 가장 많이 받는 사람의 자료로 한정~!

select * from employees where salary=(select max(salary) from employees)

과제3)employees테이블 plsql을 이용해서 출력

이름(first_name||last_name) 이메일(email) job_title(jobs)-직책  salary  연봉 (salary)   부서(department_name)  비고

 

declare
emp employees%rowtype;
job_title varchar2(10);
dept_title varchar2(10);
result varchar2(20);
begin
select * into emp from employees where salary=(select max(salary) from employees);
select job_title into job_title from employees,jobs where jobs.job_id=employees.job_id
and salary=(select max(salary) from employees);
select department_name into dept_title from employees,departments where departments.department_id=employees.department_id
and salary=(select max(salary) from employees);
if emp.salary<3000 then
result:='초급기술자';
elsif emp.salary<8000 then
result:='중급기술자';
elsif emp.salary<=12000 then
result:='고급기술자';
else
result:='초대박기술자';
end if;
if emp.commission_pct is not null then
emp.salary:=emp.salary+emp.salary*emp.commission_pct;
end if;
dbms_output.put_line('        이름        이메일   직책      연봉        부서      결과');
dbms_output.put_line(emp.first_name||emp.last_name||'  '||emp.email||'  '||job_title||'   '||emp.salary||'   '||dept_title||'   '|| result);
end;
/

-->!!   salary*12+salary*nvl(commission_pct,0)   (if절이 아닌 nvl을 이용해 쿼리문으로 사용해도 된다!!)

여러개로 조인된 테이블을 rowtype으로 지정해줄 수 없으므로 declare부분에 따로 변수로 받아주어야 한다!!

>>>

declare
begin
select result.* from (select first_name,email,job_title,salary*12+nvl(commission_pct,0) ansal,
department_name from employees join jobs from employees join jobs on employees.job_id=
jobs.job_id join departments on employees.departmnet_id=departments.department_id order by
ansal desc)result where rownum=1

 

>>연봉범위는 case문으로 작성해줘도된다!(decode는 안됨-딱떨어지는 값이 아니므로)

 

 

nvl과 디코드는 중요~~~~~~~~~~~~~~~~~~~

주의! type은 하나의 자료만 받을 수 있다!

이를 해결해 주기 위해 아래와 같이 쓴다 (타입을 선언하고 값을 넣어줌)

declare
 type ename_table_type is table of emp.ename%type
 index by binary_integer;

 type job_table_type is table of emp.job%type
 index by binary_integer;

 ename_table ename_table_type;
 job_table job_table_type;
 i binary_integer:=0;

begin
 for k in (select ename,job from emp) loop
  i:=i+1;
  ename_table(i):=k.ename;
  job_table(i):=k.job;
 end loop;
 for j in 1..i loop
  dbms_output.put_line(rpad(ename_table(i),12)||'/' ||rpad(job_table(j),9));
 end loop;
end;
/

Q.구구단만들기

declare
begin
 for i in 1..9 loop
  for j in 1..9 loop
   dbms_output.put_line(i||'  x  '||j||'  '||i*j);
  end loop;
 end loop;
end;
/

 

Q.별그리기

declare
i number:=1;
j number:=1;
result varchar2(30):=null;
begin
 while i<=4 loop
  while j<=i loop
   result :=result||'*';
  j:=j+1;
  end loop;
   dbms_output.put_line(result);
 i:=i+1;
 end loop;
end;
/

부서를 입력해서 부서에 대한 총급여를 출력하는 프로시저