1、在oracle中,对于一些比较复杂的场景,用sql不好处理的时候,我们一般会使用函数或者存储过程进行处理。记得一点能用sql处理的尽量不用函数和存储过程处理。
2、创建函数
基本结构如下,可参照sf_score_pm的案例。
create or replace function 函数名称(参数 in 参数类型...)
return 返回类型
is
参数列表
begin
...
exception
...
end;
create or replace function sf_score_pm(
p_in_stuid in varchar2,--学号
p_in_courseid in varchar2 --课程ID
)
return number
is
ls_pm number:=0;
ls_score number:=0;
begin
--获取该学生的成绩
select t.score into ls_score from score t
where t.stuid = p_in_stuid
and t.courseid = p_in_courseid;
--获取成绩比该学生高的人数
select count(1) into ls_pm from score t
where t.courseid = p_in_courseid
and t.score>ls_score;
--得到该学生的成绩排名
ls_pm:=ls_pm+1;
return ls_pm;
exception
when no_data_found then
dbms_output.put_line('该学生的课程:'||p_in_courseid|| '的成绩在成绩表中找不到');
end;
创建函数案例2
根据入参time(日期对象相减获取),格式话计算出天时分秒
CREATE OR REPLACE
FUNCTION fun_getFormatTime (time NUMBER)
RETURN VARCHAR2 IS time1 VARCHAR2 (32) ;
BEGIN
select to_char(extract(day from numtodsinterval(time,'day')))||'天'||
to_char(extract(hour from numtodsinterval(time,'day')))||'小时'||
to_char(extract(minute from numtodsinterval(time,'day')))||'分钟'||
to_char(round(to_number (extract(second from numtodsinterval(time,'day')))))||'秒' INTO time1
from dual;
if (time > 0) then
return time1 ;
else
return '' ;
end if; END ;
调用函数
SELECT fun_getFormatTime(0.000789) AS"总额" FROM DUAL;
注意项:
(1)、如果end if;和end;是最后两行的话,需要放到一行去。否则会报编译错误。
3、创建存储过程
在存储过程中,与函数不通,没有return的用法,需要返回结果则要在参数配置out参数
案例:
create or replace procedure get_all
(
v_id in varchar2,
v_vid out varchar2
) is
vsal varchar2(100);
begin
select vid into vsal from ics_record where id=v_id;
v_vid := vsal;
end get_all;
4、函数和存储过程在plsql进行测试
在需要的函数或者存储过程上右键测试,弹出如下,输入入参,执行
5、以上均为简单案例,仅供初学者学习!学海无涯苦作舟!继续前行!