数据库要从oracle变为mysql,现在就剩下把下面oracle函数转变为mysql的工作了.因为对函数不熟,来请教下各位.
// 想将下面的转为mysql的,求各位帮帮忙,非常感谢!!在线等,我先去恶补下SQL的函数知识,希望等会能有好心人的回答~~
create or replace function getFiberStatus(in_stationid in varchar2, -- 局站标识
in_id in varchar2, -- 地标标识
in_segmentid in varchar2, -- 光缆段标识
in_fiberid in varchar2) -- 光纤标识
return integer is
v_count integer := 0;
begin
-- 是否被光纤连接占用
select count(*)
into v_count
from fams_fiberconnection
where decode(stationid, in_stationid, 1, 0) = 1
and decode(id, in_id, 1, 0) = 1
and ((asegmentid = in_segmentid and afiberid = in_fiberid) or
(zsegmentid = in_segmentid and zfiberid = in_fiberid));
if v_count > 0 then
return 1;
end if; -- 是否被分光器占用
select count(*)
into v_count
from fams_splitter
where decode(stationid, in_stationid, 1, 0) = 1
and decode(id, in_id, 1, 0) = 1
and segmentid = in_segmentid
and fiberid = in_fiberid;
if v_count > 0 then
return 1;
end if; -- 是否被分光器连接占用
select count(*)
into v_count
from fams_splitter s, fams_splitterconnection sc
where decode(stationid, in_stationid, 1, 0) = 1
and decode(id, in_id, 1, 0) = 1
and s.splitterid = sc.splitterid
and sc.segmentid = in_segmentid
and sc.fiberid = in_fiberid;
if v_count > 0 then
return 1;
end if; -- 是否被光开关连接占用
select count(*)
into v_count
from fams_rtu r, fams_oswconnection oc
where r.stationid = in_stationid
and r.rtuid = oc.rtuid
and oc.segmentid = in_segmentid
and oc.fiberid = in_fiberid;
if v_count > 0 then
return 1;
end if; -- 其它需要补充,否则未被占用状态
return 0;
end getFiberStatus;
// 想将下面的转为mysql的,求各位帮帮忙,非常感谢!!在线等,我先去恶补下SQL的函数知识,希望等会能有好心人的回答~~
create or replace function getFiberStatus(in_stationid in varchar2, -- 局站标识
in_id in varchar2, -- 地标标识
in_segmentid in varchar2, -- 光缆段标识
in_fiberid in varchar2) -- 光纤标识
return integer is
v_count integer := 0;
begin
-- 是否被光纤连接占用
select count(*)
into v_count
from fams_fiberconnection
where decode(stationid, in_stationid, 1, 0) = 1
and decode(id, in_id, 1, 0) = 1
and ((asegmentid = in_segmentid and afiberid = in_fiberid) or
(zsegmentid = in_segmentid and zfiberid = in_fiberid));
if v_count > 0 then
return 1;
end if; -- 是否被分光器占用
select count(*)
into v_count
from fams_splitter
where decode(stationid, in_stationid, 1, 0) = 1
and decode(id, in_id, 1, 0) = 1
and segmentid = in_segmentid
and fiberid = in_fiberid;
if v_count > 0 then
return 1;
end if; -- 是否被分光器连接占用
select count(*)
into v_count
from fams_splitter s, fams_splitterconnection sc
where decode(stationid, in_stationid, 1, 0) = 1
and decode(id, in_id, 1, 0) = 1
and s.splitterid = sc.splitterid
and sc.segmentid = in_segmentid
and sc.fiberid = in_fiberid;
if v_count > 0 then
return 1;
end if; -- 是否被光开关连接占用
select count(*)
into v_count
from fams_rtu r, fams_oswconnection oc
where r.stationid = in_stationid
and r.rtuid = oc.rtuid
and oc.segmentid = in_segmentid
and oc.fiberid = in_fiberid;
if v_count > 0 then
return 1;
end if; -- 其它需要补充,否则未被占用状态
return 0;
end getFiberStatus;
改成这样可以吗? : where select if(stationid = in_stationid, 1, 0) = 1
where decode(stationid, in_stationid, 1, 0) = 1
--上面的意思就是下面的意思,怎么搞了这么复杂(就是stationid值为in_stationid的值是成立,否则不成立)
where stationid = in_stationid
搞定了已经.上面那个是我百度到mysql的if语句可以替换oracle的decode,没想太多就用if改了..
最后也是用的where stationid = in_stationid
搞定了已经.上面那个是我百度到mysql的if语句可以替换oracle的decode,没想太多就用if改了..
最后也是用的where stationid = in_stationid
-- 我也顺便学习下,下面是百度后的结果,不知道对不对..CREATE FUNCTION getFiberStatus(in_stationid VARCHAR(10), -- 局站标识
in_id VARCHAR(10), -- 地标标识
in_segmentid VARCHAR(10), -- 光缆段标识
in_fiberid VARCHAR(10)) -- 光纤标识
RETURNS INTEGER
DETERMINISTIC
CONTAINS SQL
BEGIN
-- 初始化
DECLARE v_count INTEGER DEFAULT 0; -- 是否被光纤连接占用
SELECT COUNT(*)
INTO v_count
FROM fams_fiberconnection
WHERE stationid = in_stationid
AND id = in_id
AND ((asegmentid = in_segmentid AND afiberid = in_fiberid) OR
(zsegmentid = in_segmentid AND zfiberid = in_fiberid));
IF v_count > 0 THEN
RETURN 1;
END IF; -- 是否被分光器占用
SELECT COUNT(*)
INTO v_count
FROM fams_splitter
WHERE stationid = in_stationid
AND id = in_id
AND segmentid = in_segmentid
AND fiberid = in_fiberid;
IF v_count > 0 THEN
RETURN 1;
END IF; -- 是否被分光器连接占用
SELECT COUNT(*)
INTO v_count
FROM fams_splitter s, fams_splitterconnection sc
WHERE stationid = in_stationid
AND id = in_id
AND s.splitterid = sc.splitterid
AND sc.segmentid = in_segmentid
AND sc.fiberid = in_fiberid;
IF v_count > 0 THEN
RETURN 1;
END IF; -- 是否被光开关连接占用
SELECT COUNT(*)
INTO v_count
FROM fams_rtu r, fams_oswconnection oc
WHERE r.stationid = in_stationid
AND r.rtuid = oc.rtuid
AND oc.segmentid = in_segmentid
AND oc.fiberid = in_fiberid;
IF v_count > 0 THEN
RETURN 1;
END IF; -- 其它需要补充,否则未被占用状态
RETURN 0;
END;