一:有输入输出参数的存储过程
根据员工号,查询并返回该员工的年薪,姓名,奖金
create or replace function fun_get_annuaSal_by_empno2(
p_empno emp.empno%type,
p_ename out emp.ename%type,
p_com out emp.comm%type
)
return number
as
p_sal_emp.sal%type;
begin
select ename,sal,nvl(comm,0) into p_ename,p_sal,p_comm from emp where empno=p_empno;
return 12*p_sal + p_comm;
end;
调用存储过程
declare
p_annualSal number(10,2);
p_ename emp.ename%type;ss
p_comm emp.comm%type;
begin
p_annualSal := fun_get_annualSal_by_empno2(7499,p_ename,p_com);
dbms_output.put_line('员工姓名为:'||p_ename||',奖金为:
'||p_comm||',年薪为:'||p_annualSal);
end;