数据库PL/SQL脚本语言函数学习笔记

函数
[color=red]函数构建的语法规则如下：[/color]
create [or replace] function function_name [(parameter, …)]
return datatype is
variable declaration;
begin
statement;
…
[execption
statement;
…]
end;

函数的参数只有IN模式，只能够由调用环境传入
返回值可把结果传回到调用环境中
函数的返回类型不能够是PL/SQL数据类型，如table、record等，而只能是oracle建表时所支持的数据类型，例如number、date等
函数中也不能出现DML、DDL等语句


[color=red]示例(1)[/color]
--实现一个拼接字符串的函数，使两个字符串前后拼接成一个字符串，并返回
create or replace function f_str(
--参数类型(注意参数没有长度)
l_str1 varchar2,
l_str2 varchar2

)

--返回类型
return varchar2
is
l_result varchar2(20);

begin
--方法体
l_result := l_str1||l_str2;
return l_result;
end;
--测试
select f_str('aaa','ggg') from dual;
--测试
select f_str(e.ename, e.job) from emp e;

[color=red]示例(2)[/color]

--创建一个合并的函数题二：
表A数据如下：
FID  Field1
1    A
1    B
1    C
2    D
2    E
2    F
要求按如下格式显示：
FID  Field1
1    A,B,C
2    D,E,F 
如何做到?（wmsys.wm_concat）
--创建表AB
create table AB(
fid number(3),
field varchar2(3)

);
--插入数据用以测试
insert into AB values(1,'A');
insert into AB values(1,'B');
insert into AB values(1,'C');
insert into AB values(2,'D');
insert into AB values(2,'E');
insert into AB values(2,'F');


select * from AB;
--创建函数f_ab()用以连接多个字符,字符用'，'隔开
--传入此表的fid字段,使具有相同fid字段的记录的field字段连接在一起用‘，’隔开,并返回长字符串
create or replace function f_ab(
l_fid number

)
--返回类型
return varchar2
is
--定义返回值
l_result varchar2(20);
--创建一个游标用以接收具有相同fid字段的记录的field字段.
cursor c is select field from AB where fid = l_fid;

begin
--为返回值赋初值
l_result := '';
--用for循环遍历游标，用l_row接收各个元素
for l_row in c loop
--连接变量里的field字段
l_result := l_result||l_row.field||',';
--结束循环
end loop;
--返回数据(注意左边的‘，’)
return rtrim(l_result,',');
--函数结束
end;
--测试
select fid, f_ab(fid) from AB group by fid;

[color=red]示例(3)[/color]
--题一：表Ａ数据如下：FYear FNum
-- 2006 1
-- 2006 2
-- 2006 3
-- 2007 4
-- 2007 5
-- 2007 6
--按如下格式显示：年度? 2006 2007
-- 汇总　6 15

--创建表yearnum
create table yearnum(
FYear number(10) not null,
FNum number(10) not null
);

--向表中插入数据用以测试
insert into yearnum values(2006, 1);
insert into yearnum values(2006, 2);
insert into yearnum values(2006, 3);
insert into yearnum values(2007, 4);
insert into yearnum values(2007, 5);
insert into yearnum values(2007, 6);

--创建一个函数f_count()传入表的fyear字段,使具有相同的fyear字段的记录的fnum字段相加在一起并返回总值
create or replace function f_count(
l_year number

)
return number
is

l_sum number(10);
--创建以个游标用以存储相同fyear字段的记录的fnum字段.
cursor c is select y.fnum from yearnum y where y.fyear = l_year;
c_temp c%rowtype;


begin
l_sum := 0;
--打开游标
open c;
--循环开始
loop
--取出数据
fetch c into c_temp;
--到达游标结尾退出循环
exit when c%notfound;
--fnum字段值相加
l_sum := l_sum + c_temp.fnum;
--循环结束
end loop;
return l_sum;
--关闭游标
close c;

--函数结束
end;
--测试
select y.fyear 年度,f_count(fyear) 汇总 from yearnum y group by y.fyear;