oracle函数 function
在oracle查询中通常有一些状态不是本条数据直接查询的,而是通过其他表格数据计算而来的,这个时候使用function 函数去计算就比较方便,例如下面的state值,通过station_no 字段去别的表查询以确定state的值,scott.GetStationState中的scott是数据库用户名
create or replace function GetStationState(station_no in varchar2)
return varchar2
is
state varchar2(16);
begin
select count(*) into state from (select radar_no as no from ac_radar r where r.state ='1' and r.basestation_no=station_no
union
select nvr_no as no from ac_nvr n where n.basestation_no=station_no and n.nvr_state='1'
union
select camera_no as no from ac_photoelectricity r where r.state ='1' and r.basestation_no=station_no);
IF state > 0 THEN
state := '1';
ELSE
state := '0';
END IF;
RETURN state;
end;
select t.ID,t.STATION_NO, scott.GetStationState(t.station_no) as state,t.LONGITUDE,t.LATITUDE,t.NAME,t.UPDATE_TIME,t.CREATE_TIME,t.DEL_FLAG,t.REMARK,t.REMARK1,t.REMARK2
from AC_BASESTATION t where t.station_no='1'