这个sql 怎么转成oracle的语法啊。

Question

declare @c1 int
select @c1=count(*) from aa
if(@c1 >0)
begin
  select @c1 from bb
end

Answer 1

试试
declare 
c1 int
select count(*) into :c1 from aa
if(c1 >0)
begin
  dbms_output.putline(:c1);
end

Answer 2

--如果是sqlplus下 先执行 set serveroutput on
declare 
c1 int;
begin 
select count(*) into c1 from aa;
if(c1 >0)
dbms_output.put_line(c1);
end if;
end;

Answer 3

declare 
c1 int
select count(*) into :c1 from(
SELECT *
FROM TP_YPWC A LEFT JOIN
( SELECT ZYK_YPWCGYS_GYSXZ,ZYK_YPWCGYS_QYGYSBM,MIN(ZYK_YPWCGYS_RWSJ) ZYK_YPWCGYS_RWSJ 
FROM TP_ZYK_YPWCGYS GROUP BY ZYK_YPWCGYS_QYGYSBM,ZYK_YPWCGYS_GYSXZ) B 
ON A.YPWC_GYSBM = B.ZYK_YPWCGYS_QYGYSBM
AND A.YPWC_FPRQ >= B.ZYK_YPWCGYS_RWSJ
WHERE B.ZYK_YPWCGYS_QYGYSBM IS NULL  and   create_at>to_date('2010-03-01','yyyy-MM-dd ')
)
if(c1 >0)
begin
  insert into T_INS_warnData
       (wdid,WdDType,WdZType,WdUserId,WdNumber,WdDate,Wdflag)
   select INSWARNDATA.NEXTVAL,24,'FZYKCG',user_code,:c1,sysdate,0 from mv_role_document_business where business_id=24
end
好像是不行啊。

Answer 4

declare
  c1 int;
begin
  select count(*)
    into :c1
    from (SELECT *
            FROM TP_YPWC A
            LEFT JOIN (SELECT ZYK_YPWCGYS_GYSXZ,
                             ZYK_YPWCGYS_QYGYSBM,
                             MIN(ZYK_YPWCGYS_RWSJ) ZYK_YPWCGYS_RWSJ
                        FROM TP_ZYK_YPWCGYS
                       GROUP BY ZYK_YPWCGYS_QYGYSBM, ZYK_YPWCGYS_GYSXZ) B
              ON A.YPWC_GYSBM = B.ZYK_YPWCGYS_QYGYSBM
             AND A.YPWC_FPRQ >= B.ZYK_YPWCGYS_RWSJ
           WHERE B.ZYK_YPWCGYS_QYGYSBM IS NULL
             and create_at > to_date('2010-03-01', 'yyyy-MM-dd '));             
             if (c1 > 0) 
               insert into
     T_INS_warnData(wdid,
                        WdDType,
                        WdZType,
                        WdUserId,
                        WdNumber,
                        WdDate,
                        Wdflag)
          select INSWARNDATA.NEXTVAL,
                 24,
                 'FZYKCG',
                 user_code,
                 :c1,
                 sysdate,
                 0
            from mv_role_document_business
           where business_id = 24;end if;
end;
不行。

Answer 5

--把你报错的具体信息贴出来
declare
  c1 int;
begin
  select count(*)
    into c1   --这里不要:
    from (SELECT *
            FROM TP_YPWC A
            LEFT JOIN (SELECT ZYK_YPWCGYS_GYSXZ,
                             ZYK_YPWCGYS_QYGYSBM,
                             MIN(ZYK_YPWCGYS_RWSJ) ZYK_YPWCGYS_RWSJ
                        FROM TP_ZYK_YPWCGYS
                       GROUP BY ZYK_YPWCGYS_QYGYSBM, ZYK_YPWCGYS_GYSXZ) B
              ON A.YPWC_GYSBM = B.ZYK_YPWCGYS_QYGYSBM
             AND A.YPWC_FPRQ >= B.ZYK_YPWCGYS_RWSJ
           WHERE B.ZYK_YPWCGYS_QYGYSBM IS NULL
             and create_at > to_date('2010-03-01', 'yyyy-MM-dd '));             
 if (c1 > 0) 
   insert into
     T_INS_warnData(wdid,
                        WdDType,
                        WdZType,
                        WdUserId,
                        WdNumber,
                        WdDate,
                        Wdflag)
          select INSWARNDATA.NEXTVAL,
                 24,
                 'FZYKCG',
                 user_code,
                 :c1,
                 sysdate,
                 0
            from mv_role_document_business
           where business_id = 24;end if;
end;

Answer 6

select INSWARNDATA.NEXTVAL,
                 24,
                 'FZYKCG',
                 user_code,
                 :c1,  --这里的:也不要
                 sysdate,
                 0
            from mv_role_document_business
           where business_id = 24

Answer 7

:xx  是绑定变量哈。。
pl/sql中 应该是 into吧。

Answer 8

declare 
c1 int
select count(*) into :c1 from aa
if(c1 >0)
begin
  dbms_output.putline(:c1);
end；

Answer 9

---这样
declare
  c1 int;
begin
SELECT count(a.*) into c1
            FROM TP_YPWC A
            LEFT JOIN (SELECT ZYK_YPWCGYS_GYSXZ,
                             ZYK_YPWCGYS_QYGYSBM,
                             MIN(ZYK_YPWCGYS_RWSJ) ZYK_YPWCGYS_RWSJ
                        FROM TP_ZYK_YPWCGYS
                       GROUP BY ZYK_YPWCGYS_QYGYSBM, ZYK_YPWCGYS_GYSXZ) B
              ON A.YPWC_GYSBM = B.ZYK_YPWCGYS_QYGYSBM
             AND A.YPWC_FPRQ >= B.ZYK_YPWCGYS_RWSJ
           WHERE B.ZYK_YPWCGYS_QYGYSBM IS NULL
             and create_at > to_date('2010-03-01', 'yyyy-MM-dd ');             
if c1 > 0 then 
          insert into T_INS_warnData(wdid,WdDType,WdZType,WdUserId,WdNumber,WdDate,Wdflag)
          select INSWARNDATA.NEXTVAL,24,'FZYKCG',user_code,c1,sysdate,0 from mv_role_document_business
          where business_id = 24;end if;
end;