oracle sql 서브쿼리,function,no_data_found,too_many_rows,others

ed fn_deptInfo

create or replace function fn_deptInfo

(v_name emp.ename%type)

return varchar2

is

v_no number;

v_dname varchar2(50);

v_loc varchar2(50);

v_total varchar2(100);

begin

select d.deptno, d.dname, d.loc into v_no, v_dname, v_loc from 

(select deptno from emp where ename=v_name) e,dept d 

where d.deptno=e.deptno;

v_total := '부서번호:'||v_no ||'부서명:'||v_dname||'장소:'||v_loc;

return v_total;

end;

/

------------------------------------------------------------------------------------------

ed empno_return

create or replace function empno_return

(v_empno in emp.empno%type)

return number

is

v_deptno emp.deptno%type;

v_sal emp.sal%type;

v_dname dept.dname%type;

v_ename emp.ename%type;

begin

select sal,deptno,ename into v_sal,v_deptno,v_ename from emp

where empno=v_empno;

select dname into v_dname from dept

where deptno=v_deptno;

dbms_output.put_line('성    명:'||v_ename);

dbms_output.put_line('부서번호:'||TO_CHAR(v_deptno));

dbms_output.put_line('부 서 명:'||v_dname);

dbms_output.put_line('급    여:'||TO_CHAR(v_sal,'999,999'));

return v_empno;

exception

when NO_DATA_FOUND THEN

dbms_output.put_line('입력한 manager가 없습니다.');

when TOO_MANY_ROWS THEN

dbms_output.put_line('자료가 2건 이상입니다.');

when OTHERS THEN

dbms_output.put_line('기타 에러입니다.');

end;

/