pl/sql编程（二）

创建一个函数 输入姓名 返回年薪（表里存的是月薪）

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);