pacbody_regdateclass.sql

图书管理系统

      o_err_msg                    OUT  VARCHAR2
      )

   IS countdate          number;
     BEGIN  o_retcode := '0';
    select count(*) into countdate
           from OPDATEClASSRULE
          where (rulename=i_rulename) ;
          if(countdate=0)
          then
          o_retcode := '0';
    else
          o_retcode := '2';
          end if;
     EXCEPTION
      WHEN NO_DATA_FOUND
      THEN
         o_retcode := '1';
         RETURN;
      WHEN OTHERS
      THEN
         o_retcode := '-1';
         RETURN;

  END PROC_OMPS_ADDUNREGQRYDATECLASS;
  
  
  PROCEDURE PROC_OMPS_DELQRYDATECLASS1(
      i_rulename                   IN   VARCHAR2,
      i_rulecode                   IN   VARCHAR2,
      i_startdate                  IN   VARCHAR2,
      i_maker                      IN   VARCHAR2,
    
   -- i_info                       IN   VARCHAR2,

      o_retCode                    OUT  VARCHAR2,
      o_err_msg                    OUT  VARCHAR2,
      o_dateclass_ref_detail            OUT  ref_detail
      )

   IS
     BEGIN  o_retcode := '0';

   if(i_maker='所有人')
    then

      OPEN  o_dateclass_ref_detail  FOR
         SELECT a.ruleid,a.rulecode,a.rulename,
           a.describes,a.startdate,a.enddate,a.remark1,b.name,c.name
           FROM OPDATEClASSRULE a  join ctp_user b on a.maker1=b.ID full join ctp_user c on a.maker2=c.ID
		   WHERE
           (a.state1 =2) and (upper(a.rulename) like '%'||upper(i_rulename)||'%') AND
           (upper(a.rulecode) like '%'||upper(i_rulecode)||'%')   AND
           (a.startdate like '%'||i_startdate||'%')

        ORDER BY  a.rulecode  desc;
else
      OPEN  o_dateclass_ref_detail  FOR
         SELECT a.ruleid,a.rulecode,a.rulename,a.describes,a.startdate,a.enddate,a.remark1,b.name,c.name
           FROM OPDATEClASSRULE a join ctp_user b on b.ID=a.maker1 left join ctp_user c on c.ID=a.maker2
		   WHERE (a.state1 = 2) and (upper(a.rulename) like '%'||upper(i_rulename)||'%') AND
           (upper(a.rulecode) like '%'||upper(i_rulecode)||'%')   AND
           (a.startdate like '%'||i_startdate||'%') AND
           (a.maker1 like '%'||i_maker||'%' or a.maker2 like '%'||i_maker||'%' )
        ORDER BY  a.rulecode  desc
          ;
          end if;


     EXCEPTION
      WHEN NO_DATA_FOUND
      THEN
         o_retcode := '1';
         RETURN;
      WHEN OTHERS
      THEN
         o_retcode := '-1';
         RETURN;

  END PROC_OMPS_DELQRYDATECLASS1;
  
  PROCEDURE   PROC_OMPS_WAITDELDATE(
      i_ruleid                    IN   VARCHAR2,
      i_maker1                    IN   VARCHAR2,
   -- i_info                       IN   VARCHAR2,

      o_retCode                    OUT  VARCHAR2,
      o_err_msg                    OUT  VARCHAR2
      )

   IS  in_last_editor_time   varchar2(20) ;

      BEGIN  o_retCode := '0';
        in_last_editor_time := TO_CHAR(CURRENT_TIMESTAMP,'YYYY-MM-DD HH24:MI:SS');

            UPDATE OPDATEClASSRULE a
            SET a.maker1 = i_maker1,
                a.last_editor_time=in_last_editor_time,
                a.maker2='',
                a.last_check_time='',
                a.state1 = '3',
                a.state2 = '3'
          WHERE (a.ruleid = i_ruleid);

          UPDATE OPDATEClASS b
            SET b.state = '3'
          WHERE (b.ruleid = i_ruleid) and (b.state='2');
           commit;
     EXCEPTION
      WHEN OTHERS
      THEN
         o_retcode := '-1';
         RETURN;

  END  PROC_OMPS_WAITDELDATE;
  
  PROCEDURE PROC_OMPS_DELDATECLASS1(
     i_ruleid                    IN   VARCHAR2,
   -- i_info                       IN   VARCHAR2,

      o_retCode                    OUT  VARCHAR2,
      o_err_msg                    OUT  VARCHAR2
      )

   IS   
     BEGIN  o_retcode := '0';
     delete
           FROM OPDATEClASS a
          WHERE (a.ruleid = i_ruleid) ;

      delete
         from opdateclassrule b
           where (b.ruleid=i_ruleid);

     EXCEPTION
      WHEN OTHERS
      THEN
         o_retcode := '-1';
         RETURN;

  END PROC_OMPS_DELDATECLASS1;
  
  PROCEDURE PROC_OMPS_DELDATECLASS2(
     i_ruleid                    IN   VARCHAR2,
     i_maker1                    IN   VARCHAR2,
   -- i_info                       IN   VARCHAR2,

      o_retCode                    OUT  VARCHAR2,
      o_err_msg                    OUT  VARCHAR2
      )

   IS    in_last_check_time   varchar2(20) ;
        countdate      number;
     BEGIN  o_retcode := '0';
      in_last_check_time := TO_CHAR(CURRENT_TIMESTAMP,'YYYY-MM-DD HH24:MI:SS');
      delete
           FROM OPDATEClASS a
          WHERE (a.ruleid = i_ruleid) and (a.state='3');
          
      update OPDATEClASS b
            SET b.state = '2'
          WHERE (b.ruleid = i_ruleid) and (b.state='1');

    
             UPDATE OPDATEClASSRULE c
            SET c.state1 = '2',
                c.maker2 = i_maker1,
                c.last_check_time=in_last_check_time,
                c.state2 = '2'
          WHERE (c.ruleid = i_ruleid);
          commit;
      select count(*) into countdate
           from OPDATEClASS
          where (ruleid=i_ruleid);
          if(countdate=0)
      then
           delete
           FROM OPDATEClASSRULE
          WHERE (ruleid = i_ruleid);
         end if;

     EXCEPTION
      WHEN OTHERS
      THEN
         o_retcode := '-1';
         RETURN;

  END PROC_OMPS_DELDATECLASS2;
  
      PROCEDURE PROC_OMPS_UPDATEREGDATE1(                              --新建日期规则类型
      i_ruleid                  IN   VARCHAR2,              --规则类型ID号
      i_datestr1                   IN   VARCHAR2,
      i_datestr2                  IN   VARCHAR2,
      i_datestr3                   IN   VARCHAR2,
      i_startdate                 IN   VARCHAR2,
      i_overdate                  IN   VARCHAR2,
      i_remark1                      IN   VARCHAR2,
      i_maker1                     IN   VARCHAR2,
      i_state1                     IN   VARCHAR2,
--      i_info                       IN   VARCHAR2,
      o_retCode                    OUT  VARCHAR2,
      o_err_msg                    OUT  VARCHAR2
      )

   IS          i          NUMBER (10);
             in_last_editor_time   varchar2(20) ;
             countdate          number;
                r       VARCHAR2(5000);
              t       t_table;

      BEGIN  o_retCode := '0';
 in_last_editor_time := TO_CHAR(CURRENT_TIMESTAMP,'YYYY-MM-DD HH24:MI:SS');
           
     if( (i_datestr1<>'1') or (i_datestr2<>'2') or(i_datestr3<>'3'))
        then  delete
           from   OPDATEClASS O
           where  (O.ruleid=i_ruleid);
      
      		
     if(i_datestr2<>'2')
      
        then
        r := str2table(i_datestr2
						  ,t);
						

			FOR i IN t.FIRST .. t.LAST
			LOOP			
            INSERT INTO OPDATEClASS

          (id,opdate,state,ruleid)
           VALUES
       (OPDATE.NEXTVAL,t(i),2,i_ruleid);

          	END LOOP;

	end if;

      if(i_datestr1<>'1')
   then r := str2table(i_datestr1
						  ,t);			

			FOR i IN t.FIRST .. t.LAST
			LOOP			
            INSERT INTO OPDATEClASS

          (id,opdate,state,ruleid)
           VALUES
       (OPDATE.NEXTVAL,t(i),'1',i_ruleid);

          	END LOOP;
    end if;
   if(i_datestr3<>'3')

    then
    r :='';
      r := str2table(i_datestr3
						  ,t);			

			FOR i IN t.FIRST .. t.LAST
			LOOP			
            INSERT INTO OPDATEClASS

          (id,opdate,state,ruleid)
           VALUES
       (OPDATE.NEXTVAL,t(i),'3',i_ruleid);

          	END LOOP;
       end if;   	
          	
          
  
          	
end if;          	
          	
       update OPDATEClASSRULE a
            SET a.remark1 = i_remark1,
                a.startdate=i_startdate,
                a.enddate=i_overdate,
                a.maker1=i_maker1,
                a.maker2='',
                a.last_editor_time=in_last_editor_time,
                a.last_check_time='',
                a.state1=4
          WHERE (a.ruleid = i_ruleid) ;	
            commit;
     EXCEPTION
		   WHEN OTHERS THEN
            o_err_msg := SQLCODE||SQLERRM;
			o_retCode := '-1';
            ROLLBACK;
         RETURN;

  END  PROC_OMPS_UPDATEREGDATE1;
  
  
  PROCEDURE PROC_OMPS_UPDATEREMARK1(                              --新建日期规则类型
      i_ruleid                  IN   VARCHAR2,              --规则类型ID号
      i_remark1                      IN   VARCHAR2,
     
--      i_info                       IN   VARCHAR2,
      o_retCode                    OUT  VARCHAR2,
      o_err_msg                    OUT  VARCHAR2
      )

   IS         

      BEGIN  o_retCode := '0';
 
       update OPDATEClASSRULE a
            SET a.remark1 = i_remark1
                
          WHERE (a.ruleid = i_ruleid) ;	
            commit;
     EXCEPTION
		   WHEN OTHERS THEN
            o_err_msg := SQLCODE||SQLERRM;
			o_retCode := '-1';
            ROLLBACK;
         RETURN;

  END  PROC_OMPS_UPDATEREMARK1;
  
  	FUNCTION str2table(i_str VARCHAR2,
							 t     OUT t_table) RETURN VARCHAR2 IS
		ret_cod INTEGER;
		e_no_required_param EXCEPTION;
		i          INTEGER;
		j          INTEGER;
		item       VARCHAR2(100);
		data_begin BOOLEAN;
		len        INTEGER;
		c          VARCHAR2(1);
		log_info   LONG;
		one        VARCHAR2(100);
	BEGIN
		ret_cod := '0';
		IF i_str IS NULL THEN
			RAISE e_no_required_param;
		END IF;
		data_begin := FALSE;
		i          := 0;
		j          := 0;
		len        := length(i_str);
		LOOP
			i := i + 1;
			IF i > len THEN
				IF NOT (item IS NULL) THEN
					j := j + 1;
					t(j) := item;
					log_info := log_info || 'add last element:' || t(j) || chr(10);
				ELSE
					IF NOT (one IS NULL) THEN
						j := j + 1;
						t(j) := one;
						log_info := log_info || 'add last element:' || t(j) ||
										chr(10);
					END IF;
				END IF;
				EXIT;
			END IF;
			c := substr(i_str
						  ,i
						  ,1);
			IF (c = '$') OR (c = '|') THEN
				data_begin := TRUE;
				IF NOT (item IS NULL) THEN
					j := j + 1;
					t(j) := item;
					log_info := log_info || 'add element:' || t(j) || chr(10);
				END IF;
				item := '';
			ELSE
				IF data_begin THEN
					item := item || c;
				ELSE
					one := one || c;
				END IF;
			END IF;
		END LOOP;
		log_info := log_info || 't.first=' || to_char(t.FIRST) || chr(10);
		log_info := log_info || 't.last=' || to_char(t.LAST) || chr(10);
		IF NOT t.FIRST IS NULL THEN
			FOR i IN t.FIRST .. t.LAST
			LOOP
				log_info := log_info || 't[' || i || ']=' || t(i) || chr(10);
			END LOOP;
		END IF;

		RETURN ret_cod;
	EXCEPTION
		WHEN e_no_required_param THEN
			ret_cod := 'e_no_required_param for str2table';
			RETURN ret_cod;
		WHEN OTHERS THEN
			ret_cod := 'e_unknown_error in str2table';
			RETURN ret_cod;
	END;


  END;
/



-- End of DDL Script for Package Body ORALDBA.OMPS_PAGE_REGDATACLASS