제어문

INSERT

 

create or replace procedure insert_test_jyh
(v_empno in emp2.empno%type,
v_ename in emp2.ename%type,
v_deptno in emp2.deptno%type)
is
begin
dbms_output.enable;
insert into emp2(empno,ename,deptno)
values(v_empno,v_ename,v_deptno);
dbms_output.put_line('사원번호:'||v_empno);
dbms_output.put_line('사원이름:'||v_ename);
dbms_output.put_line('사원부서:'||v_deptno);
dbms_output.put_line('데이터 입력 성공');
end;
/

 

set serveroutput on

execute insert_test_jyh(2000,'yong',20);

 

 

UPDATE

 

create or replace procedure update_test_jyh
(v_empno in emp2.empno%type,
v_rate in number)
is
v_emp emp2%rowtype;
begin
dbms_output.enable;
update emp2 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 emp2 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;
/

 

set serveroutput on

execute update_test_jyh(7111,-10);

 

DELETE

 

create or replace procedure delete_test_jyh(p_empno in emp2.empno%type)
is
type del_record is record
(v_empno emp2.empno%type,
v_ename emp2.ename%type,
v_hiredate emp2.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 emp2 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 emp2 where empno=p_empno;
dbms_output.put_line('데이터삭제성공');
end;
/

 

set serveroutput on

execute delete_test_jyh(7111);

 

 

FOR LOOP

 

declare
type ename_table is table of emp2.ename%type
index by binary_integer;
type sal_table is table of emp2.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 emp2 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('사원이름:'||ename_tab(cnt));
dbms_output.put_line('사원급여:'||sal_tab(cnt));
end loop;
end;
/

 

 

LOOP WHILE

 

declare
v_cnt number(3):=100;
begin
dbms_output.enable;
loop
insert into emp2(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 THEN

 

create or replace procedure dept_search_jyh(p_empno in emp2.empno%type)
is
v_deptno emp2.deptno%type;
begin
dbms_output.enable;
select deptno into v_deptno from emp2 where empno=p_empno;
if v_deptno<=10 then
dbms_output.put_line('ACCOUNTING 부서 사원입니다.');
elsif v_deptno<=20 then
dbms_output.put_line('RESEARCH 부서 사원입니다.');
else
dbms_output.put_line('부서가 없습니다.');
end if;
end;
/