不行,没有这种写法,用函数返回一个布尔值,来作语句条件,经测试行不通. SQL> select * from aa;ID FID -- --- 1 0 2 1 3 1 4 2 5 3 6 4 6 57 rows selectedSQL> SQL> CREATE OR REPLACE FUNCTION isRoomLeft( 2 p_id in varchar2) 3 RETURN boolean 4 IS 5 6 begin 7 if p_id='1' then 8 return true; 9 else 10 return false; 11 end if; 12 end; 13 /Function createdSQL> select * from aa where isRoomLeft(id);select * from aa where isRoomLeft(id)ORA-00920: 无效的关系运算符在函数中销为修改这种形式吧: CREATE OR REPLACE FUNCTION isRoomLeft( rid in RoomBookInfo.roomid%type) RETURN number IS
begin if ... then --true return 1; else return 0; end if; end; / 在过程中: v_sql := 'select * from Room where isRoomLeft(ROOMID)=1';
--判断房间是否为空 CREATE OR REPLACE FUNCTION isRoomLeft( startTime IN varchar2, endTime IN varchar2, rid in varchar2) RETURN number IS result number :=1; sTime Date; eTime Date; begin sTime :=to_date(''||startTime ||'','yyyy-mm-dd'); eTime := to_date(''|| endTime ||'','yyyy-mm-dd'); while sTime <= eTime loop ..... sTime := sTime +1; loop; return result; END; /我的这个函数为什么不对呢
begin sTime :=to_date(startTime ,'yyyy-mm-dd'); eTime := to_date( endTime,'yyyy-mm-dd'); while sTime <= eTime loop .....
我现在也没有找到原因!
SQL> select * from aa;ID FID
-- ---
1 0
2 1
3 1
4 2
5 3
6 4
6 57 rows selectedSQL>
SQL> CREATE OR REPLACE FUNCTION isRoomLeft(
2 p_id in varchar2)
3 RETURN boolean
4 IS
5
6 begin
7 if p_id='1' then
8 return true;
9 else
10 return false;
11 end if;
12 end;
13 /Function createdSQL> select * from aa where isRoomLeft(id);select * from aa where isRoomLeft(id)ORA-00920: 无效的关系运算符在函数中销为修改这种形式吧:
CREATE OR REPLACE FUNCTION isRoomLeft(
rid in RoomBookInfo.roomid%type)
RETURN number
IS
begin
if ... then --true
return 1;
else
return 0;
end if;
end;
/
在过程中:
v_sql := 'select * from Room where isRoomLeft(ROOMID)=1';
CREATE OR REPLACE FUNCTION isRoomLeft(
startTime IN varchar2,
endTime IN varchar2,
rid in varchar2)
RETURN number
IS
result number :=1;
sTime Date;
eTime Date;
begin
sTime :=to_date(''||startTime ||'','yyyy-mm-dd');
eTime := to_date(''|| endTime ||'','yyyy-mm-dd');
while sTime <= eTime loop
.....
sTime := sTime +1;
loop;
return result;
END;
/我的这个函数为什么不对呢
sTime :=to_date(startTime ,'yyyy-mm-dd');
eTime := to_date( endTime,'yyyy-mm-dd');
while sTime <= eTime loop
.....