[오라클] 예제 문제

-- 7-1번 문제임 위는 7번이고, 이를 합해서 만든 것!
create or replace procedure p_insert02(v_empno testemp.empno%type,
                                       v_ename testemp.ename%type,
                                       v_sal testemp.sal%type,
                                       v_deptno testemp.deptno%type)
is 
    v_sumsal testemp.sumsal%type;
begin
    insert into testemp
    values(v_empno,v_ename,v_sal,v_deptno,null,null);
    update testemp
    set sumsal=(select sum(sal)
                from testemp
                where deptno=v_deptno)
    where deptno=v_deptno;
    select sum(sal)
    into v_sumsal
    from testemp
    where deptno=v_deptno;
    if v_deptno=10 then
        v_sumsal:=0;
    end if;
    if v_sumsal >= 10000 then
        dbms_output.put_line('10000 over!');
    end if;
    commit;
exception
    when others then
        dbms_output.put_line(sqlcode||sqlerrm);
        rollback;
end;
/
select * from testemp;
exec p_insert02(9999,'PARK',100,30);


drop table testemp;
create table testemp as 
select empno, ename, sal, deptno from emp;

alter table testemp add grade number(1);
alter table testemp add sumsal number(10);
select *from testemp;


--문제 8번
create or replace procedure p_delete01(v_empno testemp.empno%type)
is 
    v_deptno testemp.deptno%type;
    v_sumsal testemp.sumsal%type;
    invalid_sumsal exception; --1. 선언부
begin
    select deptno
    into v_deptno
    from testemp
    where empno=v_empno;
    delete from testemp 
    where empno=v_empno;
    
    select sum(sal)
    into v_sumsal --조건을 처리하기 위함
    from testemp
    where deptno=v_deptno;
    if v_deptno=10 then
        v_sumsal:=0;
    end if;
    if v_sumsal >= 10000 then
        raise invalid_sumsal; -- 2. 실행부
    end if;
    update testemp
    set sumsal=v_sumsal
    where deptno=v_deptno;
    commit;
exception
    when invalid_sumsal then --3.exception
        dbms_output.put_line('10000 over!');
        rollback;
     when others then
        dbms_output.put_line(sqlcode||sqlerrm);
        rollback;
end;
/

select * from testemp;
exec p_delete01(9998);
exec p_insert02(9998,'KIM',2000,20);

create or replace procedure p_sum02
is
    invalid_sumsal exception; --1.1 사용자정의 exception
    v_deptno testemp.deptno%type;
    v_sumsal testemp.sumsal%type;
    cursor testemp_cur is  --2.1 선언
        select deptno,sum(sal)
        from testemp
        group by deptno;
begin
    open testemp_cur; --2.2 커서 오픈
    loop 
        fetch testemp_cur into v_deptno,v_sumsal; --2.3 커서 fetch(읽기)
        exit when testemp_cur%notfound;
        if v_sumsal <=10 then
            raise invalid_sumsal; --1.2
        end if;
        update testemp
        set sumsal=v_sumsal
        where deptno = v_deptno;
    end loop;
    close testemp_cur; --2.4 커서 닫기 
    commit;
exception 
   when invalid_sumsal then --1.3
        dbms_output.put_line(v_deptno||' ' ||TO_CHAR(v_sumsal));
        rollback;
    when too_many_rows then -- predefined-exception
        dbms_output.put_line('Try again!!');
        rollback;
    when others then
        dbms_output.put_line(sqlcode||sqlerrm);
        rollback;
end;
/

select * from testemp;
exec p_sum02;
exec p_insert02(9997,'LEE',5,40);


drop table testemp;
create table testemp
as select empno, ename, sal, deptno
    from emp;
create table testdept
as select deptno,dname
    from dept;

--과제 1번
desc user_constraints
select constraint_name,constraint_type,
table_name,search_condition,r_constraint_name
from user_constraints
where table_name in ('TESTDEPT','TESTEMP');

alter table testdept
add constraint pk_testdept_deptno primary key(deptno);

--과제 2번
alter table testemp
add constraint pk_testemp_empno primary key(empno);
--과제 3번
alter table testemp
add constraint fk_testemp_deptno 
foreign key(deptno) references testdept(deptno);

-- 4번 
alter table testemp
add enumber char(10)  --키 생성 동시에 컬럼 생성
constraint uk_testemp_enumber unique;

--5번(오라클에선 not null을 제약조건으로 본다)
--not null도 ck (체크)제약 조건 중 하나 이다.
alter table testemp
modify ename varchar2(10) constraint nn_ename not null;
--constraint ck_testemp_ename not null과 같이 제약조건을 줘도 됨.

--6번 (오라클에서 default 는 제약조건이 아니다.)
alter table testdept
add dcount number(3) default 0;


--7번 
alter table testemp
modify enumber char(14);

--8번
alter table testemp
add egender char(1) default 'm' not null
constraint ck_testemp_egender check(egender in ('m','f'));


select constraint_name,constraint_type,
table_name,search_condition,r_constraint_name
from user_constraints
where table_name in ('TESTDEPT','TESTEMP');

desc user_tab_columns
select table_name,column_name,data_default
from user_tab_columns
where table_name in ('TESTDEPT','TESTEMP');

-- 9번
alter table testemp
drop constraint SYS_C007227;
alter table testemp
add constraint ck_testemp_egender2
check(egender is not null);

--10번
drop view v_testdept;
create view v_testdept
as select * 
    from testdept
    where dcount=0;
select * from v_testdept;
--grant create view to scott; 를 sys_user에서 실행시켜주어 권한을 줘야 위에 create가 실행됨
select * from testemp;

--11번
--부서의 인원이 10명 초과이면, 사용자 정의 exception incalid_dcout을 일으키고
--'10 over!'에러메세지와 부서번호,인원수를 출력하고 모든 작업을 취소하라.
create or replace procedure p_dcount01
is
    cursor dept_cur is
        select deptno,count(*)
        from testemp
        group by deptno;
    v_deptno testemp.deptno%type;
    v_dcount testdept.dcount%type;
    invalid_dcount exception;
begin
    open dept_cur;
    loop
        fetch dept_cur into v_deptno,v_dcount;
        exit when dept_cur%notfound;
        if v_dcount >10 then --테스트 하려면 5로 해보면 됨
            raise invalid_dcount;
        end if;
        update testdept
        set dcount = v_dcount
        where deptno=v_deptno;
    end loop;
    close dept_cur;
    commit;
exception
    when invalid_dcount then
        dbms_output.put_line
        ('10 over! '||v_deptno||' ' ||v_dcount);
        rollback;
    when others then
        dbms_output.put_line(sqlcode||sqlerrm);
        rollback;
end;
/
exec p_dcount01;
select * from testdept;
update testdept
set dcount =0;
commit;--테스트 전 트랜젝션 처리를 완료해주어야함.

--12 번
create or replace procedure p_insert10(v_empno testemp.empno%type,
v_ename testemp.ename%type,v_sal testemp.sal%type,
v_deptno testemp.deptno%type,v_enumber testemp.enumber%type,
v_egender testemp.egender%type)
is 
begin
    insert into testemp
    values(v_empno,v_ename,v_sal,v_deptno,v_enumber,v_egender);
    update testdept
    set dcount = dcount+1
    where deptno= v_deptno;
    commit;
exception
    when others then
        dbms_output.put_line(sqlcode||sqlerrm);
        rollback;
end;
/
select * from testemp;
select * from testdept;
exec p_insert10(9999,'HAEUN',1000,10,'9999-9999','f');


--15번 
create or replace trigger t_insert10 
before insert on testemp for each row
begin
    update testdept
    set dcount = dcount+1
    where deptno = :new.deptno; --외부 변수는 꼭 앞에 :(콜론)을 붙여야한다.

exception
    when others then
        dbms_output.put_line(sqlcode||sqlerrm);
        rollback;
end;
/

select * from testemp;
select * from testdept;
insert into testemp
values(9998,'KIM',900,10,'9999-9998','f');
commit;


--13번
create or replace procedure p_delete10(v_empno testemp.empno%type)
is 
    v_deptno testemp.deptno%type;
begin
    select deptno 
    into v_deptno
    from testemp
    where empno = v_empno;
    
    delete from testemp
    where empno = v_empno;
    update testdept
    set dcount = dcount-1
    where deptno = v_deptno;
    commit;
exception
    when others then
        dbms_output.put_line(sqlcode||sqlerrm);
        rollback;
end;
/

select * from testemp;
select * from testdept;
exec p_delete10(9999);

--16번
create or replace trigger t_delete10
before delete on testemp for each row
begin
    update testdept
    set dcount= dcount-1
    where deptno = :old.deptno; --외부변수 콜론이 들어감
exception
    when others then
        dbms_output.put_line(sqlcode||sqlerrm);
        rollback;
end;
/
select * from testdept;
select * from testemp;
delete from testemp
where empno=9998;
commit;

desc user_source
select line,text
from user_source
where name='T_DELETE10';
desc user_objects
select object_name,object_type
from user_objects
where object_type='TRIGGER';
desc user_triggers
select * from user_triggers;