创建一个函数 输入姓名 返回年薪(表里存的是月薪)
create function sp_fun2(spName varchar2) return
number is yearSal number(10,2);
begin
select sal*12 into yearSal from emp where name=spName;
return yearSal;
end;
/
调用函数
var a number; 定义一个变量
call sp_fun2('xiaom') into:a; 输入姓名 把返回值放入变量a里面;
创建包sp_package
声明包里面有个过程update_sal
声明包里面有个函数annual_income
create package sp_package is
procedure update_sal(spName varchar2,newSal number);
function annual_income(spName varchar2) return number;
end;
/
给包sp_package 实现包体
create package body sp_package is
procedure update_sal(spName varchar2,newSal number)
is
begin
update emp set sal=newSal where name=spName;
end;
function annual_income(spName varchar2)
return number is
annual_salary number;
begin
select sal into annual_salary from emp
where name=spName;
return annual_salary;
end;
end;
/
调用包
exec sp_package.update_sal('xiaom',899);