输入的参数不能同列名相同,编译不会出错,运行会报错。(procedure也有这个问题)
create or replace function fun_num(country varchar2)
return number
is
people number;
begin
select people into people from tb where country=country;
return people;
exception
when no_data_found then
return 0;
when others then
return -1;
end fun_num; 这个方法运行错误
运行正确的:
create or replace function fun_num(country2 varchar2)
return number
is
people number; //变量可以同列名相同
begin
select people into people from tb where country=country2;
return people;
exception //异常处理
when no_data_found then
return 0;
when others then
return -1;
end fun_num;
select fun_num('中国') from dual; //当tb中有两行中国的行时,会返回-1
修改:
create or replace function fun_num(country2 varchar2)
return number
is
people2 number;
begin
select people into people2 from tb where country=country2 and rownum<=1; //oracle没有top,但有一个隐藏序列rownum
return people2;
exception
when no_data_found then
return 0;
when others then
return -1;
end fun_num;
斐波那契
create or replace function fun_fi(n pls_integer)
return pls_integer
is
begin
if(n=1) then
return 1;
elsif(n=2) then //oracle杂种啊,这是elsif,不是elseif,搞死我了才发现
return 1;
else
return fun_fi(n-2)+fun_fi(n-1);
end if;
end;
pls_integer可以存储一个有符号的整形值,其精度范围和BINARY_INTEGER一样,是:-2^31~2^31。
PLS_INTEGER和NUMBER比较起来,其优点是:
1).占有较少的存储空间;
2).可以直接进行算术运算(在NUMBER上不能直接进行算术运算,如果要计算,NUMBER必须先被转换成二进制)。所以在进行算术的时候PLS_INTEGER比NUMBER和BINARY_INTEGER快一些。
ocm认证
阅读(1537) | 评论(0) | 转发(0) |