oracle 存储过程入门

 

1、创建存储过程

create or replace procedure test(var_name_1 in type,var_name_2 out type) as

--声明变量(变量名 变量类型)

begin

--存储过程的执行体

end test;

2、变量赋值

变量名 := 值;

E.g：

create or replace procedure test(workDate in Date) is

x number(4,2);

 begin

 x := 1;

end test;

3、判断语句:

if 比较式 then begin end; end if;

E.g

create or replace procedure test(x in number) is

begin

        if x >0 then

         begin

        x := 0 - x;

        end;

    end if;

    if x = 0 then

       begin

        x: = 1;

    end;

    end if;

end test;

 

4、For 循环

For ... in ... LOOP

--执行语句

end LOOP;

(1)循环遍历游标

create or replace procedure test() as

Cursor cursor is select name from student; name varchar(20);

begin

for name in cursor LOOP

begin

 dbms_output.putline(name); 

end;

end LOOP;

end test;

 

第一次写oracle存储过程，在此记录，以当查看，代码如下：

------------------------------------------------------

create or replace procedure p_data_send is
       Cursor sendList is
       SELECT e.state, s.privilege, s.timeOut,e.equipId ,s.sendInterval  FROM t_equip e, t_data_send s
              WHERE e.equipId=s.equipId AND s.prevId=0 AND s.state=1 AND s.timeOut< sysdate;
       v_equipState t_equip.state%type;
       v_privilege  t_data_send.privilege%type;
       v_timeOut    t_data_send.timeout%type;
       v_equipId    t_equip.equipid%type;
       v_sendInterval t_data_send.sendinterval%type;
       i           number;
begin
  open sendList;
  for i in (select count(1) from t_equip e, t_data_send s WHERE e.equipId=s.equipId AND s.prevId=0 AND s.state=1 AND s.timeOut< sysdate) loop
    begin
      fetch sendList into v_equipState,
                        v_privilege,
                        v_timeOut,
                        v_equipId,
                        v_sendInterval;
      if v_equipState = 0 then
         UPDATE t_data_send s SET s.state=0,s.privilege=(v_privilege+20),
            s.timeout=sysdate+ v_sendInterval/24/60/60 WHERE s.equipid=v_equipId;
      else
         DELETE   FROM t_data_send s   WHERE s.equipid=v_equipId;
      end if;
      Dbms_Output.put_line(v_equipId);
      commit;    
      exit when sendList%notfound;
 
   
      if sendList%isopen then
         close sendList;
      end if;
    end;
  end loop;
end p_data_send;

--------------------------------------------------------

create or replace procedure p_online is
  v_intervalVale number;
  v_equipmentIDValue varchar2(16);
  i           number;
  totalNum    number;
 
  Cursor onlineList is
   select (ROUND(TO_NUMBER(sysdate - e.updateTime) * 24 * 60 * 60)-1800),e.equipId from t_equip e;
begin
  select count(1) into totalNum from t_equip;
  open onlineList;
  for i in 1..totalNum loop
    begin
       fetch onlineList into v_intervalVale,v_equipmentIDValue;
       if v_intervalVale<0 then
           update t_equip e SET e.st_online = '1' where e.equipId = v_equipmentIDValue;
       else
           update t_equip e SET e.st_online = '0' where e.equipId = v_equipmentIDValue;
       end if;   
       commit;    
       exit when onlineList%notfound;
 
    end;
  end loop;
  close onlineList;
end p_online;

-------------------------------------------------------------