Question

oracle函数设计

有表A，两个字段：loginname char(12),game_name, char(4).
写一个函数，fun_get_vip(loginname in varchar,game_name varchar)
当loginname和game_name在a中有则返回1，没有则返回0，其他情况返回1
求具体语句

Answer 1

Create Function fun_get_vip(P_Loginname in varchar2,P_Gamename in char,P_A varchar2) return number is
begin
if instr(P_A,P_Loginname) <> 0 and instr(P_A,P_Loginname) <> 0 then
return 1;
else
return 0;
end if;

end;

Answer 2

create or replace function fun_get_vip
(loginname in varchar,game_name in varchar)
return varchar
is
vn_num number;
vn_have varchar2(1);
begin
select count(*) into vn_num from A where loginname=loginname and game_name=game_name;
if vn_num='0' then
vn_have:='0';
else
vn_have :='1';
end if;
return vn_have;
end;

Answer 3

CREATE OR REPLACE FUNCTION fun_get_vip(loginname in varchar,game_name varchar)
RETURN INT IS
v_Count INT;
BEGIN
SELECT
COUNT(1) INTO v_Count
FROM
a
WHERE
a.loginname = loginname
AND a.game_name = game_name;
-- 有则返回1，没有则返回0
IF v_Count > 0 THEN
RETURN 1;
ELSE
RETURN 0;
END IF;
END fun_get_vip;
/

没测试过， 不知道行不行...

Answer 4

引用yehaver2010的回答：
Create Function fun_get_vip(P_Loginname in varchar2,P_Gamename in char,P_A varchar2) return number is
begin
if instr(P_A,P_Loginname) <> 0 and instr(P_A,P_Loginname) <> 0 then
return 1;
else
return 0;
end if;

end;

and instr(P_A,P_Loginname) 应该是 and instr(P_A,P_Gamename)吧