oracle sql procedure

set serveroutput on

create or replace procedure proc_deptSelect

is

cursor dept_select is

select deptno,dname from dept;

begin

for dept_list in dept_select loop

dbms_output.put_line(dept_list.deptno ||',' ||dept_list.dname);

end loop;

end;

/

@proc_deptSelect

execute proc_deptSelect;

emp테이블에서 아래와같이 출력

사원번호(사원명):급여/입사일(2015-02-23)

create or replace procedure proc_empSelect

is

cursor emp_select is

select empno,ename,sal,to_char(hiredate,'YYYY-MM-DD') hiredate from emp;

begin

for emp_list in emp_select loop

dbms_output.put_line(emp_select.empno||'('|| emp_select.ename||') :'|| emp_select.sal||'/'||emp_select.hiredate);

end loop;

end;

/

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

부서명,사원수,급여합계 를 sql만들기

create or replace procedure proc_all

is

cursor empdept_select is

select d.dname dname, count(e.empno) cnt, sum(e.sal) salary

from emp e,dept d

where e.deptno=d.deptno

group by d.dname;

begin

for empdept_list in empdept_select loop

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

dbms_output.put_line('사원수  :'||empdept_list.cnt);

dbms_output.put_line('급여합계:'||empdept_list.salary);

dbms_output.put_line('======================');

end loop;

end;

/

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

emp 테이블의 내용을 이용해 bonus2(보너스=급여*0.5)테이블에 insert

create or replace procedure proc_insert

is

cursor emp_select is

select empno,(sal*0.5) salary ,'2015-10-07' gdate from emp;

begin

for emp_insert in emp_select loop

insert into bonus2 values(emp_insert.empno,emp_insert.salary,'2015-10-07'); 

end loop;

end;

/