gethc1.sql

基于SQL/PLUS的脚本

----*************************************************************************
----*****版 本 ：1.0.0
----*****功 能 ：获得指定焊材材汇表。
----*****编制者：黄刚
----*****时 间 ：2005-03-04
----***************************************************************************
create or replace PROCEDURE cappdb2.CAPPDB2_GetHC
       (pUnitContentID IN NUMBER,mCAPPFileID IN NUMBER,mFileName IN VARCHAR2,mCreatetime IN DATE,mModifytime IN DATE,mField2 IN VARCHAR2)
as
        v_Sequen   varchar2(4000):=null;      ----序号
	v_Name     varchar2(4000):=null;      ----材料名称
	v_Standard varchar2(4000):=null;      ----材料规格
	v_Sign     varchar2(4000):=null;      ----材料牌号
	v_Tech     varchar2(4000):=null;      ----技术条件
	v_Ration   varchar2(4000):=null;      ----消耗定额
	v_Shop     varchar2(4000):=null;      ----车间
	v_Ident    varchar2(4000):=null;      ----标准件标识
	v_From     varchar2(4000):=null;      ----来源，(在综合工艺材汇中存在。)
	v_Unit     varchar2(4000):=null;      ----单位，(在综合工艺材汇中存在。)
	v_UnitContentID number(11,0);	
       	v_m varchar2(200):=null;
        v_b boolean:=false;
	v_CAPPFileID number(11,0); 
	v_FileName   varchar2(200):=null;
	v_Createtime date:=null;
	v_Modifytime date:=null;	
	mStr varchar2 (4000);
        mStrLeaf	varchar2(4000);
        mStrDel      varchar2(4000);
        m    number(4,0);
        n    number(4,0);
	m_nCount     number(4,0):=1;

     type content_rec is record(
	mFileID number(11,0),
	mSTRCONTENT varchar2(4000)
      );
      type content_rec_type is ref cursor
      return content_rec;
      content_refcur content_rec_type;
      mcontent content_rec;

    cursor csr is select distinct recnum from cappdb2.cprptyrecsetcontents
	where UNITCONTENTID = pUnitContentID order by recnum asc;
	
    mRECNUM cappdb2.cprptyrecsetcontents.RECNUM%TYPE;

   
BEGIN
    OPEN csr;
    FETCH csr INTO mRECNUM;    
    WHILE csr%FOUND LOOP

      OPEN content_refcur for
	     SELECT FIELDID,strcontent  FROM cappdb2.cprptyrecsetcontents
	    WHERE RECNUM = mRECNUM and UNITCONTENTID = pUnitContentID;

	FETCH content_refcur INTO mcontent;
          
                        v_Sequen:=null;
                        v_Name:=null;
			v_Standard:=null;
			v_Sign:=null;
			v_Tech:=null;
			v_Ration:=null;
			v_Shop:=null;
			v_Ident:=null;
			v_From:=null;
			v_Unit:=null;
			v_UnitContentID:= trim(to_char(pUnitContentID));
			v_CAPPFileID:= mCAPPFileID;
			v_FileName:= trim(mFileName);
			v_Createtime:= mCreatetime;
			v_Modifytime:= mModifytime;
			

	 while content_refcur%found loop
                         m:=0;
			 n:=0;
			 
                        if instr(mcontent.mstrcontent,'<THFONT%',1)>0 or instr(mcontent.mstrcontent,'<%',1) >0 or
                           instr(mcontent.mstrcontent,'<THALIGN%',1) > 0 then
                            
      			    if instr(mcontent.mstrcontent,'<THFONT%',1) >0 then
	 			m:= instr(mcontent.mstrcontent,'<THFONT%',1);
	 			n:= instr(mcontent.mstrcontent,'%THFONT>',1);
	 			mStrDel:=substr(mcontent.mstrcontent,m, n - m + 8 );	
         			mStrLeaf := replace(mcontent.mstrcontent,mStrDel);
			    else
				mStrleaf := mcontent.mstrcontent;
	 		    end if;

       			    if instr(mStrLeaf,'<%',1) >0 then
	 
	 			m:= instr(mStrLeaf,'<%',1) ;
	 			n:= instr(mStrLeaf,'%>',1) ;
         			mStrDel:=substr(mStrLeaf,m, n - m +2);
	 			mStrLeaf := replace(mStrLeaf,mStrDel);
			    end if;

       			    if instr(mStrLeaf,'<THALIGN%',1) >0 then
	 			m:= instr(mStrLeaf,'<THALIGN%',1);
	 			n:= instr(mStrLeaf,'%THALIGN>',1) ;
         			mStrDel:=substr(mStrLeaf,m, n-m +9);
	 			mStrLeaf := replace(mStrLeaf,mStrDel);
			    end if;

                        else
                             mStrLeaf:=mcontent.mstrcontent;
   			end if;
			

		      v_m:= trim(to_char(mcontent.mFileID));
		
		if v_m = '7' then
		        v_Sequen:=mStrLeaf ;			
		elsif v_m = '8' then
			v_Standard:=mStrLeaf ; 			
		elsif v_m = '45' then
			v_Name:=mStrLeaf ; 			
		elsif v_m = '56' then
			v_Tech:=mStrLeaf ;			
		elsif v_m = '44' then
			v_Ration:=mStrLeaf ;			
		elsif v_m = '45' then
			v_Shop:=mStrLeaf ;
		elsif v_m = '46' then
			v_Sign:=mStrLeaf ;
		elsif v_m = '57' then
			v_Ident:=mStrLeaf ;
		elsif v_m = '68' then
			v_From:=mStrLeaf ;
		elsif v_m = '59' then
			v_Unit:=mStrLeaf ;
		else
			null;		
		end if;	

	        FETCH content_refcur INTO mcontent;
         end loop;
        CLOSE content_refcur; 		
	
        insert into CAPPDB2.t_HCMTable(M_SEQUEN,M_NAME,M_STANDARD,M_SIGN,M_TECH,M_SHOP,M_IDENT,M_RATION,
                             M_UNITID,M_CAPPFileName,M_FileCreatetime,M_FileModifytime,M_Version,M_Field2,M_COUNT)
	values(v_Sequen,v_Name,v_Standard,v_Sign,v_Tech,v_shop,v_Ident,v_Ration,
              pUnitContentID,v_FileName,v_Createtime,v_Modifytime,1,mField2,m_nCount);

        commit;

	m_nCount := m_ncount + 1;
	FETCH csr INTO mRECNUM;  	
	
    END LOOP;
    CLOSE csr;
END;
/