存储过程小记

 简单示例一：

create or replace procedure testp(isal in number)--输入参数不能有长度限制
is
xname varchar2(10); --变量要有长度限制
CURSOR cur_1 is select ename from emp where sal>isal;--游标定义
begin
DBMS_OUTPUT.put_line('HELLO WORLD');
 FOR rec in cur_1 loop--迭代游标
  begin
   xname := rec.ename;
   DBMS_OUTPUT.put_line('name:'||xname);
  end;
end loop;
end;

示例二：

create or replace procedure p_form_submit_task(arg_pid in VARCHAR2,arg_begin_date in date,arg_end_date in date,arg_type in varchar2)
is
a_state varchar2(20);
   CURSOR cur_1 is
   select FORM_ID fid,PERIOD_TYPE pt,SUBMIT_DATE_LIMIT sdl,BEGIN_DECLARE_DATE bdd,
       BIZ_DATE_BEGIN bdb,BIZ_DATE_END bde from FORM_SUBMIT_TASK_RULE
       where RULE_PROP1=arg_type and BIZ_DATE_BEGIN>=arg_begin_date and BIZ_DATE_END<=arg_end_date;
begin
   FOR rec in cur_1 loop
   begin
   --先判断是否有相应记录
   select STATE into a_state from FORM_SUBMIT_TASK where PID=arg_pid and FORM_ID=rec.fid and BIZ_DATE_BEGIN=rec.bdb and BIZ_DATE_END=rec.bde;
     --如果有记录且为未提交记录，则更新记录
     if a_state ='0' then
       begin
       update FORM_SUBMIT_TASK set ENTERPRISE_TYPE=arg_type,PERIOD_TYPE=rec.pt,SUBMIT_DATE_LIMIT=rec.sdl
       where PID=arg_pid and FORM_ID=rec.fid and BIZ_DATE_BEGIN=rec.bdb and BIZ_DATE_END=rec.bde;
      -- DBMS_OUTPUT.put_line('UPDATE``````');
       end;
     end if;
  exception
  --没有记录就直接插入记录
  when NO_DATA_FOUND then
    insert into FORM_SUBMIT_TASK(TASK_ID,TASK_TYPE,FORM_ID,PID,ENTERPRISE_TYPE,PERIOD_TYPE,SUBMIT_DATE_LIMIT,BEGIN_DECLARE_DATE,BIZ_DATE_BEGIN,BIZ_DATE_END,STATE,OVERDUE_DAYS)
          values(sys_guid(),'CWBB',rec.fid,arg_pid,arg_type,rec.pt,rec.sdl,rec.bdd,rec.bdb,rec.bde,'0',0);
          --DBMS_OUTPUT.put_line('INSERT``````');
  end;
end loop;
COMMIT;
end;