目录
函数
其实oracle有个很方便的地方在于,它有很多已经定义好的函数可以直接调用,如系统函数和数学函数。
自定义函数
【通用结构】定义函数
create or replace function 函数名(参数1 输入类型 数据类型,.....)
return 返回值类型
is
变量 数据类型:=初始值;
begin
函数体;
return 返回值;
end;
【通用结构】调用函数
select 函数名() from dual;
【例题1】简单函数。输出"hello world"。(无参)
create or replace function getmessage
return char
is
begin
return 'hello world';
end;
【运行】调用函数
select getmessage() from dual;
【输出】hello world
【例题2】简单函数。传入参数输出“hello”或者“world”。(有参)
create or replace function getmessage(x in int)
return char
is
n varchar(10):='hello';
m varchar(10):='world';
begin
if x>5 then
return n;
else
return m;
end if;
end;
【运行】select getmessage(10) from dual;
【输出】hello
【例题3】函数实例。该函数能实现:查询某门课程所有学生成绩的平均值。
create or replace function getavg(v_cno score.cno%type)
return char
is
v_grade score.grade%type;
begin
select avg(grade) into v_grade from score where cno=v_cno;
return v_grade;
end;
【运行】select getavg('A001') from dual;
【输出】63
【例题4】综合实例。使用游标、增加异常处理完成【例题3】
create or replace function getavg(v_cno score.cno%type)
return char
is
err_no_data exception;
cursor grade_cur is select grade from score where cno=v_cno;
v_grade score.grade%type;
v_sum score.grade%type:=0; --如果这个没有赋值,就会return null
n int:=0;
begin
open grade_cur;
loop
fetch grade_cur into v_grade;
exit when grade_cur%notfound;
v_sum:=v_sum+v_grade;
n:=n+1;
end loop;
if n=0 then
raise err_no_data;
else
return v_sum/n;
end if;
exception
when err_no_data then return 'nodata';
close grade_cur;
end;
【运行】select getavg('A001') from dual;
【输出】63
数学函数
【例子】功能写在注释里了,这些都是可能常用的。
select sno,substr(sno,3,2),sname from student; --从sno第三个开始保留两个字符,其余的减掉
select avg(grade) from score where cno=upper('c001'); --score表中所有grade的平均值--表中的数据都是区分大小写的
select chr('78'),ASCII('A') from dual --asi为78对应的字符和字符A对应的asii
select length(sno) from student --student中每一行(每个人)的sno学号的长度
select reverse(sno) from student; --学号颠倒
select replace('hello,china','china','beijing') from dual;--第一个参数是需要替换的句子,替换china为beijing,输出被替换后的句子
select * from course where cno=upper('a001'); --upper大小写。
系统函数
【例子】我选择第一个为例子,其余不试了
select sysdate from dual; --输出时间
【注释】可以更改时间或者格式:首选项-数据库-第一项 更改