PL/SQL 함수,프로시져 실습

함수

 

 

-함수생성

CREATE OR REPLACE FUNCTION
FC_update_salary(v_empno in NUMBER)
return number
is
v_sal emp3.salary%type;
begin
update emp3 set salary=salary*1.1 where employee_id=v_empno;
commit;
select salary into v_sal from emp3 where employee_id=v_empno;
return v_sal;
end;
/

 

- 변수선언

var salary number

 

- 함수실행

EXECUTE :salary := FC_update_salary(9999);

 

- 변수확인

print salary

 

 

 

 

프로시져

 

 

- 프로시져 생성

create or replace procedure emp_infom (p_empno in emp3.employee_id%type)
is
 v_empno emp3.employee_id%type;
 v_ename emp3.first_name%type;
 v_sal emp3.salary%type;
begin
 dbms_output.enable;
 select employee_id,first_name,salary
 into v_empno,v_ename,v_sal
 from emp3
 where employee_id=p_empno;

 dbms_output.put_line('사원번호:'||v_empno);
 dbms_output.put_line('사원이름:'||v_ename);
 dbms_output.put_line('사원급여:'||v_sal);
end;
/

 

- 변수선언

set serveroutput on;

 

- 프로시져실행
execute emp_infom(9999);

 

 

실습

 

계정 scott/tiger  테이블 salgrade

grade가 1등급인 항목에 대해 losal을 120% 인상해주는 procedure와 function생성 (출력문사용 각 컬럼 출력)

 

 

 

1)프로시져

 

create or replace procedure sal_grade
is
v_grade salgrade.grade%type;
v_losal salgrade.losal%type;
v_hisal salgrade.hisal%type;
begin

dbms_output.enable;

update salgrade set losal=losal*1.2 where grade=1;
commit;
select grade,losal,hisal into v_grade,v_losal,v_hisal from salgrade where grade=1;

dbms_output.put_line('봉급등급:'||v_grade);
dbms_output.put_line('낮은봉급:'||v_losal);
dbms_output.put_line('높은봉급:'||v_hisal);
end;
/

set serveroutput on

 

execute sal_grade

 

 

2)함수

 

create or replace function sal_grade2
return number

is
v_grade salgrade.grade%type;
v_losal salgrade.losal%type;
v_hisal salgrade.hisal%type;

begin
dbms_output.enable;
update salgrade set losal=losal*1.2 where grade=1;
commit;
select grade,losal,hisal into v_grade,v_losal,v_hisal from salgrade where grade=1;

dbms_output.put_line('봉급등급:'||v_grade);
dbms_output.put_line('낮은봉급:'||v_losal);
dbms_output.put_line('높은봉급:'||v_hisal);
return v_losal;
end;
/

var losal number

 

execute:losal:=sal_grade2

 

print losal