Oracle 기본정리 - Declare, Procedure 문제

Oracle 기본정리 - Declare, Procedure 문제

--문제1
--exec proc3(7788);
--레퍼런스
--사원번호:
--사원명:
--입사일:
---급여:
 
--desc emp;
 
 
create or replace procedure proc3(eno emp.empno%type)
is
    vempno emp.empno%type;
    vename emp.ename%type;
    vhiredate emp.hiredate%type;
    vsal emp.sal%type;
begin
    select empno,ename,hiredate,sal into vempno,vename,vhiredate,vsal
    from emp
    where empno=eno;
       
  
    dbms_output.put_line('값이 있습니다.');
    dbms_output.put_line('vempno : '|| vempno);
    dbms_output.put_line('vename : ' || vename);
    dbms_output.put_line('vhiredate : ' || vhiredate);
    dbms_output.put_line('vsal : '|| vsal);
  
    exception       --no data에 대한 예외 잡기
    when no_data_found then dbms_output.put_line('값이 없습니다!');
end;
 
exec proc3(7782);
 
--------------------------------------------------------------------
--프로시저 계산
create or replace procedure proc4(a number,b number)
is
    begin
        dbms_output.put_line(a/b);
    exception when ZERO_DIVIDE then  dbms_output.put_line('0으로 나눌수 없습니다.');
    end;
   
execute proc4(10,2);
execute proc4(10,0);
   
----------------------------------------------------------------------
--레퍼런스로 배열 받기
create or replace procedure proc5(a number)
is
    emp_rec emp%rowtype;
    begin
        select * into emp_rec
        from emp
        where empno=a;
       
        dbms_output.put_line(emp_rec.empno);
        dbms_output.put_line(emp_rec.ename);
        dbms_output.put_line(emp_rec.sal);
        exception when NO_DATA_FOUND then dbms_output.put_line('데이터가 없습니다.');
    end;
 
execute proc5(7781);
 
---------------------------------------------------
--프로시저 배열로 선언하기
create or replace procedure proc6(a number)
is
    TYPE emp_rec_type IS RECORD(empno emp.empno%TYPE
                                ,sal emp.sal%TYPE
                                ,hiredate emp.hiredate%type);
    v_emp_rec emp_rec_type;
    begin
        select empno,sal,hiredate into v_emp_rec
        from emp
        where empno=a;
       
        dbms_output.put_line(v_emp_rec.empno);
        dbms_output.put_line(v_emp_rec.sal);
        dbms_output.put_line(v_emp_rec.hiredate);
       
    end;
 
execute proc6(7782);
 
------------------------------------------------------------------------
--문제1
--empno ename job sal deptno
--rec 타입 만들기
 
create or replace procedure proc7(a number)
is
    TYPE emp_REC_TYPE is RECORD(empno emp.empno%TYPE
                                ,ename emp.ename%TYPE
                                ,job emp.job%TYPE
                                ,sal emp.sal%TYPE
                                ,deptno emp.deptno%TYPE
                                );
    v_emp_rec emp_REC_TYPE;
    begin
        select empno,ename,job,sal,deptno into v_emp_rec
        from emp
        where empno=a;
       
        dbms_output.put_line(v_emp_rec.empno);
        dbms_output.put_line(v_emp_rec.ename);
        dbms_output.put_line(v_emp_rec.job);
        dbms_output.put_line(v_emp_rec.sal);
        dbms_output.put_line(v_emp_rec.deptno);
       
       
        exception when NO_DATA_FOUND then dbms_output.put_line('데이터가 없습니다.');
    end;
 
exec proc7(7781);