declare
d Number;
begin
select DepartId into d from MMT_USERS M INNER JOIN CNT_Employee C on M.EMP_ID = C.ID where M.NAME = 'bht ';
if d = 0 THEN
select * from 表A;
else
select * from 表B;
end if;
end;
在oracle这样把查询到的一个ID结果赋值给声明的变量d,然后用d来做判断条件进行表的显示。 有点问题,请大家帮忙修正一下,谢谢!!!
d Number;
begin
select DepartId into d from MMT_USERS M INNER JOIN CNT_Employee C on M.EMP_ID = C.ID where M.NAME = 'bht ';
if d = 0 THEN
select * from 表A;
else
select * from 表B;
end if;
end;
在oracle这样把查询到的一个ID结果赋值给声明的变量d,然后用d来做判断条件进行表的显示。 有点问题,请大家帮忙修正一下,谢谢!!!
与
select * from 表A;
在Oracle过程里面里面不允许这么用,以及一楼指的select S into A from table1 不能保证select值是唯一,有可能会发生问题
目的是利用declare @d int
set @d = select DepartId int d from MMT_USERS M INNER JOIN CNT_Employee C on M.EMP_ID = C.ID where M.NAME ='bht'
上句话把查询出来的结果赋值给变量@d
然后根据变量@d的值做条件进行判断,如果等于0的话查询表A,不等于0的话查询表B
if @d = 0
SELECT DW.MC QX, DW_1.MC AS XZ, JSZW.ZWMC, count(ZCSJ.id) as rs
FROM ZCSJ
INNER JOIN JSZW ON ZCSJ.XRJSZW = JSZW.ID
INNER JOIN DW ON ZCSJ.QX = DW.ID
INNER JOIN DW DW_1 ON ZCSJ.XZ = DW_1.ID
where to_char(ZCSJ.ZSYXQ, 'yyyy-MM-dd') >= '[?riqi|2008-8-1?]'
and to_char(ZCSJ.QDSJ, 'yyyy-MM-dd') <= '[?riqi|2008-8-1?]'
and ZCSJ.QXZCYY is null
GROUP By DW.MC, DW_1.MC, JSZW.ZWMC;
else
SELECT DW.MC QX, DW_1.MC AS XZ, JSZW.ZWMC, count(ZCSJ.id) as rs
FROM ZCSJ
INNER JOIN JSZW ON ZCSJ.XRJSZW = JSZW.ID
INNER JOIN DW ON ZCSJ.QX = DW.ID
INNER JOIN DW DW_1 ON ZCSJ.XZ = DW_1.ID
where to_char(ZCSJ.ZSYXQ, 'yyyy-MM-dd') >= '[?riqi|2008-8-1?]'
and to_char(ZCSJ.QDSJ, 'yyyy-MM-dd') <= '[?riqi|2008-8-1?]'
and ZCSJ.QXZCYY is null
and ((DW_1.ID in (select D.ID
from MMT_USERS M
INNER JOIN CNT_Employee C on M.EMP_ID = C.ID
INNER JOIN DW D on C.DepartId = D.ID
where M.NAME = '[?name|bht?]')) or
(DW.ID in (select D.ID
from MMT_USERS M
INNER JOIN CNT_Employee C on M.EMP_ID = C.ID
INNER JOIN DW D on C.DepartId = D.ID
where M.NAME = '[?name|bht?]')))
GROUP By DW.MC, DW_1.MC, JSZW.ZWMC;
现在把数据库换成oracle了 所以我想问一下,按照以前的要求在oracle中应该怎样修改这个SQL语句!!!!
TYPE cursortype is ref CURSOR;
cur1 cursortype;
open cur1 for yousql_statement
也就是:
if d=0 then
open cur1 for sql1
else
open cur1 for sql2
end if;
然后再fetch cur1 into 变量值,再给你下面的过程用