PL/SQL Procedure, Function 연습

 문제 1) 숫자를 파라미터로 받아서 파라미터를 단수로 하여 구구단을 출력하는 Procedure작성

 create or replace procedure proc1 (num in number)
 is
 begin
    for i in 1..9 loop
            dbms_output.put_line(num||'x'||i||'='||(num*i));
    end loop;
 end proc1;
 /

SQL> exec proc1(5);

.....................................................................................................................................................................................

문제 2) 파라미터로 사번을 받아서 사원의 이름을 출력하는 Procedure 작성

 create or replace procedure proc2 (emp_no in emp.empno%TYPE)
 is
 e_name emp.ename%TYPE;
 begin
 select ename into e_name from emp where empno=emp_no;
 dbms_output.put_line(e_name||'('||emp_no||')');
 end proc2;
 /

.....................................................................................................................................................................................

 문제 3) 파라미터 없는 Procedure 를 선언하고 실행하면 전 사원의 번호, 이름을 출력한다.

create or replace procedure proc3
is
 empcsr SYS_REFCURSOR;
 emprow emp%ROWTYPE;
begin
 open empcsr for select * from emp;
 loop
  fetch empcsr into emprow;
  exit when empcsr%NOTFOUND;
  dbms_output.put_line(emprow.empno||'--'||emprow.ename);
 end loop;
end proc3;
/

.....................................................................................................................................................................................

문제 4) IN, OUT 파라미터를 이용하여 사번을 전달하여 사원이름을 출력하는 Procedure
(IN, OUT 파라미터, 바인드 변수를 사용하고,  print명령으로 OUT파라미터 출력)

create or replace procedure proc4
(emp_no IN emp.empno%TYPE, e_name OUT emp.ename%TYPE)
is
begin
 select ename into e_name from emp where empno=emp_no;
end proc4;
/
SQL> var ename varchar2(20);  --출력파라미터로 등록될 바인드변수 선언
SQL> exec proc4(7369, :ename); --IN, OUT 파라미터를 사용하여 프로시저 호출
SQL> print ename;    --출력파라미터에 값이 저장되어 있는지 테스트함.

.....................................................................................................................................................................................

문제 5) 사번을 파라미터로 받아서 사번, 이름을 출력하는 Procedure 작성

create or replace procedure proc5
(emp_no IN OUT emp.empno%TYPE, e_name OUT emp.ename%TYPE)
is
begin
 select empno, ename into emp_no, e_name from emp where empno=emp_no;
end proc5;
/

SQL> var empno number;
SQL> exec :empno := 7369;
SQL> var ename varchar2(20);
SQL> exec proc5(:empno, :ename);
SQL> print :empno;
SQL> print :ename;

.....................................................................................................................................................................................

문제 6) 사번입력받고, 소속부서의 최고, 최저연봉 차액을 OUT파라미터로 출력하는 Procedure

create or replace procedure proc6
(emp_no IN emp.empno%TYPE, diff OUT number)
is
 dept_no emp.deptno%TYPE;
begin
 select deptno into dept_no from emp where empno=emp_no;
 select max(sal)-min(sal) into diff from emp where deptno=dept_no;
end proc6;
/

SQL> var diff number;
SQL> exec proc6(7369, :diff);
SQL> print :diff;

.....................................................................................................................................................................................

문제 7) 문제 6에서 다루었던 내용을 Function으로 작성해 보세요.
(OUT 파라미터 대신 RETURN 문장 사용)

create or replace function func1
(emp_no IN emp.empno%TYPE)
RETURN number
is
 dept_no emp.deptno%TYPE;
 diff emp.sal%TYPE;
begin
 select deptno into dept_no from emp where empno=emp_no;
 select max(sal)-min(sal) into diff from emp where deptno=dept_no;
 RETURN diff;
end func1;
/

SQL>  select func1(7369) from dual;

.....................................................................................................................................................................................

숙제 : 문제 3에서 작성했던 프로시저의 내용을 변경하여 SYS_REFCURSOR를 OUT 파라미터로 설정하여
JSP 에서 ResultSet를 다룰 수 있게 하고,  JSP가 호출되면 모든 사원의 정보가 출력되도록 작성해 보세요.