a表里有id,address
b表也同样有id,address我现在希望用id来找, 1,a表有address就返回a的address,没有就找b表function getAddress(ID IN VARCHAR2) return varchar2end;
求中间如何写
第二个问题是function和procedure结束时我看有时写 end; 有时候写 end [function name or procedure name];
没区别吧?
b表也同样有id,address我现在希望用id来找, 1,a表有address就返回a的address,没有就找b表function getAddress(ID IN VARCHAR2) return varchar2end;
求中间如何写
第二个问题是function和procedure结束时我看有时写 end; 有时候写 end [function name or procedure name];
没区别吧?
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;
试试
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;
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;
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;
没区别吧?
没区别吧?
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],这样可以增加 易读性,明白了事。
修剪下,增加明白性
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;
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