-- table type => 일차원 배열
create or replace procedure table_test(v_deptno in emp.deptno%TYPE)
is
type empno_table is table of emp.empno%TYPE
index by binary_integer;
type ename_table is table of emp.ename%TYPE
index by binary_integer;
type sal_table is table of emp.sal%TYPE
index by binary_integer;
empno_tab empno_table;
ename_tab ename_table;
sal_tab sal_table;
i binary_integer := 0;
begin
dbms_output.enable;
for emp_list in ( select empno, ename, sal from emp where deptno = v_deptno) loop
i := i + 1;
empno_tab(i) := emp_list.empno;
ename_tab(i) := emp_list.ename;
sal_tab(i) := emp_list.sal;
end loop;
for cnt in 1..i loop
dbms_output.put_line('사원 번호 : '|| empno_tab(cnt));
dbms_output.put_line('사원 이름 : '|| ename_tab(cnt));
dbms_output.put_line('사원 급여 : '|| sal_tab(cnt));
end loop;
end;
-- record type => 구조체, c의 struct?
create or replace procedure record_test ( p_empno in emp.empno%TYPE)
is
type emp_record is record
(v_empno emp.empno%TYPE, v_ename emp.ename%TYPE, v_hiredate emp.hiredate%TYPE);
emp_rec emp_record;
begin
dbms_output.enable;
select empno, ename, hiredate
into emp_rec.v_empno, emp_rec.v_ename, emp_rec.v_hiredate
from emp where empno = p_empno;
dbms_output.put_line(' 사원 번호 : '|| emp_rec.v_empno);
dbms_output.put_line(' 사원 번호 : '|| emp_rec.v_ename);
dbms_output.put_line(' 입 사 일 : '|| emp_rec.v_hiredate);
end;
-- %rowtype type => table의 * 구조체
create or replace procedure table_test
is
i binary_integer := 0;
type dept_table_type is table of dept%rowtype
index by binary_integer;
dept_table dept_table_type;
begin
for dept_list in ( select * from dept ) loop
i := i + 1;
dept_table(i).deptno := dept_list.deptno;
dept_table(i).dname := dept_list.dname;
dept_table(i).loc := dept_list.loc;
end loop;
for cnt in 1..i loop
dbms_output.put_line(' 부서 번호 : '||dept_table(cnt).deptno ||
' 부서 이름 : '||dept_table(cnt).dname ||
' 부서 위치 : '||dept_table(cnt).loc);
end loop;
end;
--insert test
create or replace procedure insert_test
( v_empno in emp.empno%type,
v_ename in emp.ename%type,
v_deptno in emp.deptno%type)
is
begin
insert into emp(empno,ename,hiredate,deptno)
values(v_empno,v_ename,sysdate,v_deptno);
end;
--update test
create or replace procedure update_test
(v_empno in emp.empno%type,
v_rate in number)
is
v_emp emp%rowtype;
begin
dbms_output.enable;
update emp set sal = sal+(sal*(v_rate/100))
where empno = v_empno;
dbms_output.put_line('데이터 수정 성공 ');
select empno, ename, sal
into v_emp.empno, v_emp.ename, v_emp.sal
from emp
where empno = v_empno;
dbms_output.put_line(' **** 수 정 확 인 ****');
dbms_output.put_line('사원번호 : '||v_emp.empno);
dbms_output.put_line('사원이름 : '||v_emp.ename);
dbms_output.put_line('급여 : '||v_emp.sal);
end;
--delete test
create or replace procedure delete_test
(p_empno in emp.empno%type)
is
type del_record is record
( v_empno emp.empno%type,
v_ename emp.ename%type,
v_hiredate emp.hiredate%type);
v_emp del_record;
begin
dbms_output.enable;
select empno, ename, hiredate
into v_emp.v_empno, v_emp.v_ename, v_emp.v_hiredate
from emp
where empno = p_empno;
dbms_output.put_line('사원번호 : '||v_emp.v_empno);
dbms_output.put_line('사원이름 : '||v_emp.v_ename);
dbms_output.put_line('입사일 : '||v_emp.v_hiredate);
delete from emp where empno = p_empno;
dbms_output.put_line('데이터 삭제 성공 ');
end;
-- for loop test
declare
type ename_table is table of emp.ename%type
index by binary_integer;
type sal_table is table of emp.sal%type
index by binary_integer;
ename_tab ename_table;
sal_tab sal_table;
i binary_integer := 0;
begin
dbms_output.enable;
for emp_list in (select ename, sal from emp where deptno = 10 ) loop
i := i + 1;
ename_tab(i) := emp_list.ename;
sal_tab(i) := emp_list.sal;
end loop;
for cnt in 1..i loop
dbms_output.put_line('employee_name : '|| ename_tab(cnt));
dbms_output.put_line('employee salary : '|| sal_tab(cnt));
end loop;
end;
--while loop test
declare
v_cnt number(3) := 100;
begin
dbms_output.enable;
loop
insert into emp(empno,ename, hiredate)
values(v_cnt,'test'||to_char(v_cnt), sysdate);
v_cnt := v_cnt +1;
exit when v_cnt > 110;
end loop;
dbms_output.put_line(v_cnt-100|| ' 개의 데이터가 입력되었습니다.');
end;
-- if statement
create or replace procedure dept_search
( p_empno in emp.empno%type)
is
v_deptno emp.deptno%type;
begin
dbms_output.enable;
select deptno into v_deptno from emp where empno = p_empno;
if v_deptno <= 7000 then
dbms_output.put_line('accounting 부서 사원 입니다.');
elsif v_deptno < 7900 then
dbms_output.put_line('research 부서 사원 입니다.');
else
dbms_output.put_line(' 부서가 없네요..');
end if;
end;
-- 임시적인 커서
create or replace procedure impoicit_cursor(p_empno emp.empno%type)
is
v_loc dept.loc%type;
v_update_row number;
begin
select loc into v_loc from dept where deptno = p_empno;
if sql%notfound then
dbms_output.put_line('Not Found!');
elsif sql%found then
dbms_output.put_line(sql%rowcount|| ' row found.');
elsif sql%isopen then
dbms_output.put_line('Cursor is open.');
end if;
end;
-- 명시적인 커서
create or replace procedure ExpCursor_test
(v_deptno dept.deptno%type)
is
cursor dept_avg is
select b.dname, count(a.empno) cnt, round(avg(a.sal),3) salary
from emp a, dept b
where a.deptno = b.deptno
and b.deptno = v_deptno
group by b.dname;
v_dname dept.dname%type;
emp_cnt number;
sal_avg number;
begin
open dept_avg;
fetch dept_avg into v_dname, emp_cnt,sal_avg;
dbms_output.put_line('department name : '||v_dname);
dbms_output.put_line('count employees : '||emp_cnt);
dbms_output.put_line('Salary Average : '||sal_avg);
close dept_avg;
exception
when others then
dbms_output.put_line(sqlerrm||'에러 발생 ');
end;
create or replace procedure forcursor_test
is
cursor dept_sum is select dname, count(a.empno) cnt, sum(a.sal) salary
from emp a, dept b
where a.deptno = b.deptno
group by b.dname;
begin
for emp_list in dept_sum loop
dbms_output.put_line('department name : '|| emp_list.dname);
dbms_output.put_line('employees : '|| emp_list.cnt);
dbms_output.put_line('total salary :'|| emp_list.salary);
end loop;
exception
when others then
dbms_output.put_line(sqlerrm||'에러 발생 ');
end;