1. 编写带有一个输入参数和一个输出参数的存储过程,输入参数为教师的编号类型,输 出参数与 tm 表的 workdays 类型一致,存储过程的功能是,根据输入参数教师编号的 值,计算出该教师为其参与的所有项目的工作总天数;
create or replace procedure sumworkday(ftno char)
as
sum_workday number;
begin
select sum(workdays) into sum_workday
from tm
where tno=ftno;
if sum_workday is null then
dbms_output.put_line('不存在的');
rollback;
return;
end if;
dbms_output.put_line(sum_workday);
end sumworkday;
/
2. 编写代码测试存储过程;
exec sumworkday('t001');
exec sumworkday('t002');
exec sumworkday('t234');
3. 编写一个函数,计算某个教师负责的经费总数;
create or replace function sumfund (ftno varchar2)
return number
as
sum_fund number;
begin
select sum(pfund) into sum_fund from myproject where tno=ftno;
return sum_fund;
end;
/
4. 编写代码测试函数;
var sum_fund