pl/sql 存储函数

create or replace function get_sysdate
return date
is
v_date date;

begin
v_date := sysdate;
return v_date;
end;

select get_sysdate from dual;

–定义2数相加函数
create or replace function tsum(v_num1 number,v_num2 number)
return number
is
v_sum number;
begin
v_sum := v_num1 +v_num2;
return v_sum;
end;

begin
dbms_output.put_line(tsum(4,8));
end;

—查询某课程总分数
create or replace function tscore(v_sid varchar2)不能定长
return number
is
v_sum number(18,1) :=0;
cursor score_cursor is select score from SC where sid = v_sid;
begin
for c in score_cursor loop
v_sum := v_sum + c.score;
end loop;
return v_sum;
end;

show errors;

–in型参数不可改变，out型参数可改变
–查询总额和人数
create or replace function tscore2(v_sid varchar2,v_count out number )
return number(18,1)
is
v_sum number(18,1) :=0;
cursor score_cursor is select score from SC where sid = v_sid;
begin
v_count :=0;
for c in score_cursor loop
v_sum := v_sum + c.score;
v_count := v_count +1;
end loop;
return v_sum;
end;

—输出语句
declare
v_num number;
begin
dbms_output.put_line(tscore2(‘01’,v_num));
dbms_output.put_line(v_num);
end;