oracle 自定义函数里有个nvl() 请问是做什么用的呀
create or replace function fn_ColumnIsExist(stablename varchar2,scolumn varchar2)
return int
as
vcount int;
begin
select count(*) into vcount from user_tab_columns u where u.COLUMN_NAME=upper(scolumn) and u.TABLE_NAME=upper(stablename);
if(nvl(vcount,0)>0)then
return 1;
end if;
return 0;
end;顺便解释下count(*)在这里有什么作用谢谢啦
create or replace function fn_ColumnIsExist(stablename varchar2,scolumn varchar2)
return int
as
vcount int;
begin
select count(*) into vcount from user_tab_columns u where u.COLUMN_NAME=upper(scolumn) and u.TABLE_NAME=upper(stablename);
if(nvl(vcount,0)>0)then
return 1;
end if;
return 0;
end;顺便解释下count(*)在这里有什么作用谢谢啦
count(*) 不就是取记录条数? 难道还有其他作用??
nvl(x,value):如果x为空,那么就返回value.
如果x不为空,就返回x.count(*)统计根据你的条件:
u.COLUMN_NAME=upper(scolumn) and u.TABLE_NAME=upper(stablename)
返回的结果的条数,
不要nvl也可以
select count(*) into vcount
vcount要么为0要么大于0,不会为空的
--不只有个nvl(),还有个nvl2()SQL*Plus: Release 8.0.6.0.0 - Production on 星期二 4月 12 11:22:36 2011(c) Copyright 1999 Oracle Corporation. All rights reserved.
Connected to:
Oracle Database 10g Release 10.1.0.2.0 - ProductionSQL> select nvl('123','为空') from dual ;NVL('123','为空')
-------------------------
123SQL> select nvl('','为空') from dual ;NVL('','为空')
-------------------
为空SQL> select nvl(null,'为空') from dual ;NVL(NULL,'为空')
--------------------
为空SQL> select nvl('','为空') from dual ;NVL('','为空')
-------------------
为空SQL> select nvl2(null,'不为空','为空') from dual ;NVL2(NULL,'不为空','为空')
---------------------------------
为空SQL> select nvl2('123','不为空','为空') from dual ;NVL2('123','不为空','为空')
----------------------------
不为空SQL>
count(*)返回结果的条数!
if a=true then
d=b;
else
d=c;
end if;