存储过程实战（一）

存储过程实战（一）

其中P_JXXT_XXXXX为存储过程名称，

CREATE OR REPLACE 
procedure P_JXXT_XXXXX(
ret out varchar2,
v_in_userid in varchar2,--??????id
v_in_stuid in varchar2,--??????id
v_in_stustatus in varchar2,--????????????
v_in_oraclassid in varchar2,--?????????id
v_in_curclassid in varchar2,--???????????????id
v_in_remark in varchar2,--?????
v_in_statetime in varchar2,--????????????
v_in_remark1 in varchar2,--??????????????????
v_in_filename in varchar2,--????????
v_in_priviewname in varchar2,--??????????????
v_in_reentrytime in varchar2--????????????
)  is
vlog_id varchar2(8);
v_in_statetime_ varchar2(20);
v_stu_type varchar2(2);
begin
        v_in_statetime_ :=  v_in_statetime;
       if v_in_statetime is null then
         v_in_statetime_ := to_char(sysdate,'yyyy-mm-dd');
       end if;
       update T_STUDENT set STU_status = v_in_stustatus , class_id = v_in_curclassid where STU_ID  = v_in_stuid;
       select STU_LOG_SEQ.Nextval into vlog_id from dual;
       insert into stu_log (log_id,user_id,stu_id,log_time,operate_type,oragin_classid,current_classid,remark,state_time,remark1,file_path,reentry_time,file_preview)
       values (vlog_id,v_in_userid,v_in_stuid,to_char(sysdate,'yyyy-mm-dd hh24:mi:ss'),v_in_stustatus,v_in_oraclassid,v_in_curclassid,v_in_remark,v_in_statetime_,v_in_remark1,v_in_filename,v_in_reentrytime,v_in_priviewname);
       update student_cycle set end_day = to_char(to_date(v_in_statetime_,'yyyy-mm-dd')-1,'yyyy-mm-dd'),/*remark =  v_in_remark ,*/OPERATE_ID = v_in_userid
       where stu_id = v_in_stuid and to_char(sysdate,'yyyy-mm-dd') between START_DAY and END_DAY and class_id=v_in_oraclassid;
       select a.stu_type into v_stu_type from  T_STUDENT A where a.stu_id=v_in_stuid;
       if (v_stu_type='S') then
       insert into student_cycle(cycle_id, stu_id,class_id,stu_status,start_day,end_day, remark,operate_id,log_id)
       values(jzxt_seq_student_cycle.nextval,v_in_stuid,v_in_curclassid,v_in_stustatus,v_in_statetime_,'2030-01-01',v_in_remark,v_in_userid,vlog_id);
       end if;
       ret := sql%rowcount;
       COMMIT;
EXCEPTION WHEN OTHERS THEN
  BEGIN
  ROLLBACK;
  ret := -1;
  END;
end P_JXXT_XXXXX;

ret out varchar2 

这句是输出结果，后边跟着输出数据类型

v_in_stuid in varchar2

,这句是传入参数，后边跟着传入数据类型

vlog_id varchar2(8)

是定义变量

if v_in_statetime is null then
         v_in_statetime_ := to_char(sysdate,'yyyy-mm-dd');
       end if;

这跟java if… else效果一样，
不同的是函数运算不加括号，结束夹end if收尾

select STU_LOG_SEQ.Nextval into vlog_id from dual;

查询语句结果的赋值vlog_id 是上面定义好的变量
其它增删改语句跟平时写语句一样不变，

EXCEPTION WHEN OTHERS THEN
  BEGIN
  ROLLBACK;

看到exception应该不陌生，这句话就是说遇到异常回滚

ret := sql%rowcount;

这句是 给ret赋值 跟java中 a=1;意思一样