sqlserver的存储过程改成oracle的

Question

一直用sqlserver oracle还没来得及看 现在非常着急要把下面这个存储过程改成oracle的  哪位高手帮忙改下 谢谢了create procedure pr_dddev@devid varchar(36),@devno varchar(36),@chktype int,@devname varchar(50)
as
declare @cardfunction varchar(50),@card_number varchar(36),@card_status varchar(36)declare @card cursorbegininsert into lm_kqdev values(@devid,@devno,0,@chktype,2,@devname)
insert into lm_kqhttp values(@devid,'11111111','11111111','11111111')update fk_equipment set state=1 where code=@devid
begin 
    set @card=cursor  for select cardnum,cardfunction,state from fk_card where SUBSTRING(cardfunction ,2,1)=1         open @card             fetch next from @card into @card_number,@cardfunction,@card_status
  
               while(@@FETCH_STATUS = 0)
               begin
  
    
                                
                                if(@card_status=1)
                                begin
            
                                      delete from lm_kqhttpredcard where cardid=@card_number and devid=@devid
                                      
                                      insert into lm_kqhttpredcard(cardid,flag,downflag,devid) select @card_number,0,2,devid from lm_kqdev where devid=@devid
                          
                                      update lm_kqhttpblackcard set flag=1,downflag=2 where cardid=@card_number and devid=@devid
                                end
                                if(@card_status!=1)
                                begin
            
                                      delete from lm_kqhttpblackcard where cardid=@card_number and devid=@devid
                                      
                                      insert into lm_kqhttpblackcard(cardid,flag,downflag,devid) select @card_number,0,2,devid from lm_kqdev where devid=@devid
                                   
                                      update lm_kqhttpredcard set flag=1,downflag=2 where cardid=@card_number and devid=@devid
                                end
       
  
  
  
                         fetch next from @card into @card_number,@cardfunction,@card_status
                      end
            
            
         close @card      
end
  end

Answer 1

--大概如下  未测试
create procedure pr_dddev(devid varchar2,devno varchar2,chktype number,devname varchar2)
as
cardfunction varchar2(50);
card_number varchar2(36);
card_status varchar2(36);
card  sys_refcursor;begininsert into lm_kqdev values(devid,devno,0,chktype,2,devname);
insert into lm_kqhttp values(devid,'11111111','11111111','11111111');update fk_equipment set state=1 where code=devid;
open card for select cardnum,cardfunction,state from fk_card where SUBSTRING(cardfunction ,2,1)=1;      loop          fetch card into card_number,cardfunction,card_status;
  
          exit when card%notfound;
                if card_status=1 then
                        delete from lm_kqhttpredcard where cardid=card_number and devid=devid;
                                      
                        insert into lm_kqhttpredcard(cardid,flag,downflag,devid) select card_number,0,2,devid from lm_kqdev where devid=devid;
                          
                        update lm_kqhttpblackcard set flag=1,downflag=2 where cardid=card_number and devid=devid;
                else                       delete from lm_kqhttpblackcard where cardid=card_number and devid=devid;
                                      
                        insert into lm_kqhttpblackcard(cardid,flag,downflag,devid) select card_number,0,2,devid from lm_kqdev where devid=devid;
                                   
                        update lm_kqhttpredcard set flag=1,downflag=2 where cardid=card_number and devid=devid;
       
  
               end if;
            
     end loop;
close card;      
end;

Answer 2

CREATE OR REPLACE PROCEDURE pr_dddev(v_devid varchar2,v_devno varchar2,n_chktype number,v_devname varchar2)
IS
  
  CURSOR cur_card IS
  SELECT cardnum,cardfunction,state 
  FROM fk_card 
  WHERE SUBSTRING(cardfunction ,2,1)=1;
  
  v_cardfunction VARCHAR2(50);
  v_card_number VARCHAR2(36);
  v_card_status VARCHAR2(36);  
BEGIN
     INSERT INTO lm_kqdev 
     VALUES(v_devid,v_devno,0,n_chktype,2,v_devname);
     
     INSERT INTO lm_kqhttp 
     VALUES(v_devid,'11111111','11111111','11111111');
     
     UPDATE fk_equipment 
     SET state=1 
     where code=v_devid;
     
     BEGIN
          OPEN cur_card;
               FETCH cur_card INTO v_cardfunction,v_card_number,v_card_status;
               LOOP
                   EXIT WHEN cur_card%NOTFOUND;
                        IF v_card_status = 1 THEN
                        
                            DELETE FROM lm_kqhttpredcard 
                            WHERE cardid=v_card_number 
                            AND devid=v_devid;
                                      
                            INSERT INTO lm_kqhttpredcard(cardid,flag,downflag,devid) 
                            SELECT v_card_number,0,2,v_devid 
                            FROM lm_kqdev 
                            WHERE devid=v_devid;
                          
                            UPDATE lm_kqhttpblackcard 
                            SET flag=1,downflag=2 
                            WHERE cardid=v_card_number and devid=v_devid;   
                                                 
                        ELSE
                        
                            DELETE FROM lm_kqhttpblackcard 
                            WHERE cardid=v_card_number 
                            AND devid=v_devid;
                                      
                            INSERT INTO lm_kqhttpblackcard(cardid,flag,downflag,devid) 
                            SELECT v_card_number,0,2,v_devid 
                            FROM lm_kqdev WHERE devid=v_devid;
                                   
                            UPDATE lm_kqhttpredcard 
                            SET flag=1,downflag=2 
                            WHERE cardid=v_card_number 
                            AND devid=v_devid;                        END IF;
               END LOOP;
          CLOSE cur_card;
     END;END;未测试~

Answer 3

FETCH cur_card INTO v_cardfunction,v_card_number,v_card_status;
               LOOP
位置要调换一下汗

Answer 4

--sql server和oracle中的存储过程，游标还是有区别的，虽然都是基于SQL标准，
--lz最好将需求写出来，好实现oracle版本的相同于sql server过程功能的过程
create or replace procedure pr_dddev(
       devid varchar2,
       devno varchar2,
       chktype number,
       devname varchar2)
  as
  declare 
          cardfunction varchar2(50);
          card_number varchar2(36);
          card_status varchar2(36);
          card sys_refcursor;
  begin
       insert into lm_kqdev values(devid,devno,0,chktype,2,devname);
       insert into lm_kqhttp values(devid,'11111111','11111111','11111111');
       update fk_equipment set state=1 where code=devid;
       begin 
         open card for select cardnum,cardfunction,state from fk_card where substr(cardfunction,2,1)=1;
         loop
           fetch card into card_number,cardfunction,card_status;
             exit when card%notfound;
               if card_status=1 then
                 delete from lm_kqhttpredcard where cardid=card_number and devid=devid;               
                 insert into lm_kqhttpredcard(cardid,flag,downflag,devid) 
                        select card_number,0,2,devid from lm_kqdev where devid=devid;
                 update lm_kqhttpblackcard set flag=1,downflag=2 where cardid=card_number and devid=devid;
               else
                  delete from lm_kqhttpblackcard where cardid=card_number and devid=devid;              
                  insert into lm_kqhttpblackcard(cardid,flag,downflag,devid) 
                         select card_number,0,2,devid from lm_kqdev where devid=devid;               
                  update lm_kqhttpredcard set flag=1,downflag=2 where cardid=card_number and devid=devid;
               end if;   
         end loop;
         close card;      
       end;
  end;
end pr_dddev;