oracle 存储过程学习实例

//版权所有，转载请注明出处-----

CREATE OR REPLACE PROCEDURE p_num_exception(iorder_id in varchar2,isuborder_id in varchar2)  IS

  v_v1 number;
  v_c1 varchar(30);
  v_c2 varchar(30);
  v_out varchar(500);
  v_cur_sql varchar2(2000);
  type  curr is ref cursor;
  cur curr;
  V_REC uop_crm2.tf_r_mphonecode_use%rowtype;
BEGIN
  BEGIN

    DECLARE
   
 

    begin
        --  CURSOR CUR IS
     v_cur_sql :=  'select *
        from uop_crm2.tf_r_mphonecode_use b
       where b.serial_number in
             (select a.attr_value
                from uop_crm2.tf_b_weg_trade_item a
               where a.suborder in
                     (select c.suborder
                     from uop_crm2.tf_b_weg_trade c
                     where c.order_id = iorder_id ';
                --     )
              --);
                       --and c.suborder = isuborder_id
      if isuborder_id <> null and isuborder_id <> ''  then
           v_cur_sql := v_cur_sql || ' and c.suborder = isuborder_id ))';
      else
         v_cur_sql := v_cur_sql || '))';     
      end if;
      
      OPEN CUR FOR v_cur_sql;
     -- FOR V_REC IN CUR LOOP,不用打开和关闭着，直接使用
     loop
        FETCH CUR INTO V_REC;
        EXIT WHEN CUR%NOTFOUND;
        if v_rec.code_state_code = 4 then
           v_out :=v_rec.serial_number||'已开卡';
        else
          --非在用，检查空闲表
          select count(1) into v_v1 from uop_crm2.tf_r_mphonecode_idle t where t.serial_number = v_rec.serial_number;
          if v_v1 > 0 then
            v_out := v_rec.serial_number||'在空闲表中';
          else
            --检查TF_B_TRADE表是否存在0 Y订单，
            select t.subscribe_state,t.next_deal_tag into v_c1,v_c2 from uop_crm2.TF_B_TRADE t where t.serial_number = v_rec.serial_number;
            if v_c1 = '0' and v_c2 = 'Y' then
                  v_out := '子订单号:'||isuborder_id||',号码:'||v_rec.serial_number;
            else
                --TF_B_TRADE不存在0Y订单，检查TF_BH_TRADE 表是否存在500订单
                select tt.trade_type_code,tt.cancel_tag into v_v1,v_c1 from(select t.trade_type_code,t.cancel_tag  from uop_crm2.TF_Bh_TRADE t where t.serial_number = v_rec.serial_number order by t.accept_date desc)tt where rownum = 1;
                if v_v1 = 500 then
                      if v_c1 = '0' then
                         v_out := v_rec.serial_number||'状态为竣工';
                    --else
                       --If 一条1，一条2 ，返回某某号码开户返销，需要预开

                    end if;
                else
                    v_out := v_rec.serial_number||'没有预开';
                end if;
            end if;
          end if;
        end if;

      END LOOP;
      CLOSE cur;
    END;

  END;
dbms_output.put_line(v_out);

EXCEPTION
  WHEN OTHERS THEN
      DBMS_OUTPUT.put_line('sqlcode : ' || sqlcode);
      DBMS_OUTPUT.put_line('sqlerrm : ' || sqlerrm);
    rollback;
END p_num_exception;