oracle function 写法

ladofwind 2010-06-25 12:46:52
a表里有id,address
b表也同样有id,address

我现在希望用id来找, 1,a表有address就返回a的address,没有就找b表

function getAddress(ID IN VARCHAR2) return varchar2

end;
求中间如何写


第二个问题是function和procedure结束时我看有时写 end; 有时候写 end [function name or procedure name];
没区别吧?

...全文
596 12 打赏 收藏 举报
写回复
12 条回复
切换为时间正序
当前发帖距今超过3年,不再开放新的回复
发表回复
心中的彩虹 2010-06-25
create or replace function getAddress(v_ID IN VARCHAR2) return varchar2
as
v_address varchar2(100);
begin
select address into v_address from a where a.id=v_ID;
if v_address is nulll then
select address into v_address from b where b.id=v_ID;
end if;
return v_address;
end;
--多写了个v v_v_address
  • 打赏
  • 举报
回复
心中的彩虹 2010-06-25

修剪下,增加明白性
create or replace function getAddress(v_ID IN VARCHAR2) return varchar2
as
v_address varchar2(100);
begin
select address into v_address from a where a.id=v_ID;
if v_v_address is nulll then
select address into v_address from b where b.id=v_ID;
end if;
return v_address;
end;

  • 打赏
  • 举报
回复
心中的彩虹 2010-06-25
[Quote=引用楼主 ladofwind 的回复:]
a表里有id,address
b表也同样有id,address

我现在希望用id来找, 1,a表有address就返回a的address,没有就找b表

function getAddress(ID IN VARCHAR2) return varchar2

end;
求中间如何写


第二个问题是function和procedure结束时我看有时写 end; 有时候写 ……
[/Quote]


1:
create or replace function getAddress(ID IN VARCHAR2) return varchar2
as
v_address varchar2(100);
begin
select address into v_address from a where a.id=id;
if v_v_address is nulll then
select address into v_address from b where b.id=id;
end if;
return v_address;
end;
2:
第二个问题是function和procedure结束时我看有时写 end; 有时候写 end [function name or procedure name];
没区别吧?
没什么区别 单独的过程或者函数都一样 ,没什么区别
不过在包体中最好还是
end [function name or procedure name],这样可以增加 易读性,明白了事。
  • 打赏
  • 举报
回复
ladofwind 2010-06-25
有没人回答第二个问题
  • 打赏
  • 举报
回复
ladofwind 2010-06-25
function和procedure结束时我看有时写 end; 有时候写 end [function name or procedure name];
没区别吧?
  • 打赏
  • 举报
回复
kkdtfyyg 2010-06-25
个人认为JAVA3344520的更完整,当A表和B都没有时,程序得以续继执行;在IIHERO的源程序中,创建函数CREATE OR REPLACE 语句中的 IS 关键字是否可以写成 AS ?
  • 打赏
  • 举报
回复
ladofwind 2010-06-25
第二个问题是function和procedure结束时我看有时写 end; 有时候写 end [function name or procedure name];
没区别吧?
  • 打赏
  • 举报
回复
mg_chen 2010-06-25
2楼代码有问题,如果找不到不能用is null 来判断,会直接触发异常,应该如下

Create Or Replace Function Getaddress(In_Id In Int) Return Varchar2 Is
Result integer;
Begin
Begin
Select Address Into Result From a Where Id = Id;
Exception
When No_Data_Found Then
Begin
Select Address Into Result From b Where Id = Id;
Exception
When No_Data_Found Then
Result := -1;
When Others Then
Raise;
End;
When Others Then
Raise;
End;

Return(Result);
End Getaddress;
  • 打赏
  • 举报
回复
luocb1980 2010-06-25
function getAddress(ID IN VARCHAR2) return varchar2 IS

v_a_address a.address%type;
cursor c_a is
select address from a where id = ID;

begin

if not c_a%isopen then
open c_a;
fetch c_a
into v_a_address;
if c_a%found then
DBMS_OUTPUT.put_LINE('Address is ' || v_a_address);
else
select address into v_a_address from b where id = ID;
DBMS_OUTPUT.put_LINE('Address is ' || v_a_address);
end if;
end if;
if c_a%isopen then
close c_a;
end if;

end getAddress;
  • 打赏
  • 举报
回复
iihero 2010-06-25
create or replace function getAddress(var_id IN VARCHAR2) return varchar2
as
v_addr varchar2(32);
begin
select address into v_addr from a where id = var_id;
if (v_addr is null) then
select address into v_addr from b where id = var_id;
end if;
return v_addr;
end;
  • 打赏
  • 举报
回复
iqlife 2010-06-25
function getAddress(ID IN VARCHAR2) 
return varchar2 is
v_result varchar2(50);
as
select address
into v_result
from a
where id=ID;

if v_result is null then
select address
into v_result
from b
where id=ID;
end if;

return v_result;

exception
when NO_DATA_FOUND then
v_result := -1;
when others then
DBMS_OUTPUT.PUT_LINE('在FN_GET_TAX_GB过程中出错!'||v_result);
end;

试试
  • 打赏
  • 举报
回复
ladofwind 2010-06-25
有高手指点吗?
  • 打赏
  • 举报
回复
相关推荐
发帖
Oracle
加入

1.6w+

社区成员

Oracle开发相关技术讨论
社区管理员
  • 开发
  • Lucifer三思而后行
  • 卖水果的net
申请成为版主
帖子事件
创建了帖子
2010-06-25 12:46
社区公告
暂无公告