Procura por um padrão em um arquivo e move-o para os novos arquivos um por um

Question

Estou tentando pesquisar um determinado padrão em um arquivo e movê-lo para novos arquivos, um por um. Eu tentei sed -n 's/CREATE PROCEDURE/,/END ;/p' File É novamente imprimir o mesmo arquivo. O dado abaixo é o conteúdo do arquivo.

SET PATH "QSYS","QSYS2","JOES" ; 

CREATE PROCEDURE JOES.CANCELORDERLINE ( 
    IN ORDERID INTEGER , 
    IN ORDERLINEID INTEGER , 
    IN NEWORDLINESTATUSCODE VARCHAR(10) , 
    IN NAME VARCHAR(50) , 
    OUT O_RESULT CHAR(5) ) 
    LANGUAGE SQL 
    SPECIFIC JOES.CANCELORDERLINE 
    NOT DETERMINISTIC 
    MODIFIES SQL DATA 
    CALLED ON NULL INPUT 
    SET OPTION  ALWBLK = *ALLREAD , 
    ALWCPYDTA = *OPTIMIZE , 
    COMMIT = *NONE , 
    DECRESULT = (31, 31, 00) , 
    DFTRDBCOL = *NONE , 
    DYNDFTCOL = *NO , 
    DYNUSRPRF = *USER , 
    SRTSEQ = *HEX   
    BEGIN ATOMIC 

    DECLARE V_ORDLINENUMFORDSP VARCHAR ( 16 ) ; 

    DECLARE V_ORDLINENUMSTR VARCHAR ( 3 ) ; 

    DECLARE V_ORDLINENUM INT ; 

    DECLARE V_RESULT CHAR ( 5 ) DEFAULT '00000' ; 

    DECLARE SQLSTATE CHAR ( 5 ) DEFAULT '00000' ; 



DECLARE V_ORDLNSTATUSHISTID INT ; 

    DECLARE V_ORDERID INT DEFAULT 0 ; 

    DECLARE V_ORDERLINEID INT DEFAULT 0 ; 

    DECLARE V_NEWORDLINESTATUSCODE VARCHAR ( 10 ) DEFAULT '' ; 

DECLARE V_OLDSTATUS VARCHAR ( 100 ) DEFAULT '' ; 

    DECLARE EXIT HANDLER FOR SQLEXCEPTION 

        SET O_RESULT = SQLSTATE ; 

        SET O_RESULT = V_RESULT ; 


SET V_ORDERID = ORDERID ; 
SET V_ORDERLINEID = ORDERLINEID ; 
SET V_NEWORDLINESTATUSCODE = NEWORDLINESTATUSCODE ; 


/* Insert a new record for the new line OrdLineStatusHist*/ 
    SET V_OLDSTATUS = ( SELECT OLSH . ORDLNSTATUSTYPECD FROM JOES . ORDERLINESTATUSHIST OLSH WHERE OLSH . CLIENTORDERID = V_ORDERID AND OLSH . CLIENTORDERLINEID = V_ORDERLINEID AND OLSH . STATUSENDDATETIME IS NULL ) ; 

    IF ( ( V_OLDSTATUS IS NULL ) OR ( V_OLDSTATUS <> V_NEWORDLINESTATUSCODE ) ) THEN 


        IF ( EXISTS ( SELECT ORDLNSTATUSHISTID FROM JOES . ORDERLINESTATUSHIST WHERE CLIENTORDERID = V_ORDERID AND CLIENTORDERLINEID = V_ORDERLINEID ) ) 
        THEN 
            SET V_ORDLNSTATUSHISTID = ( SELECT MAX ( ORDLNSTATUSHISTID ) FROM JOES . ORDERLINESTATUSHIST WHERE CLIENTORDERID = V_ORDERID AND CLIENTORDERLINEID = V_ORDERLINEID ) ; 
        ELSE 
            SET V_ORDLNSTATUSHISTID = 0 ; 
        END IF ; 


        IF ( V_ORDLNSTATUSHISTID > 0 ) THEN 

        UPDATE  JOES . ORDERLINESTATUSHIST SET STATUSENDDATETIME = CURRENT TIMESTAMP , LASTUPDATENAME = NAME , UPDTIMESTAMP = CURRENT TIMESTAMP WHERE 
        CLIENTORDERID = V_ORDERID AND CLIENTORDERLINEID = V_ORDERLINEID AND ORDLNSTATUSHISTID = V_ORDLNSTATUSHISTID ; 

        END IF ; 

        SET V_ORDLNSTATUSHISTID = V_ORDLNSTATUSHISTID + 1 ; 

        INSERT INTO JOES . ORDERLINESTATUSHIST ( CLIENTORDERID , CLIENTORDERLINEID , ORDLNSTATUSHISTID , ORDLNSTATUSTYPECD , STATUSSTARTDATETIME , STATUSENDDATETIME , 
        CREATENAME , CREATETIMESTAMP ) VALUES ( V_ORDERID , V_ORDERLINEID , V_ORDLNSTATUSHISTID , V_NEWORDLINESTATUSCODE , CURRENT TIMESTAMP , CURRENT TIMESTAMP , NAME , CURRENT TIMESTAMP ) ; 

        UPDATE JOES . CLIENTORDERLINE SET 
            ORDLNSTATUSTYPECD = V_NEWORDLINESTATUSCODE , UPDTIMESTAMP = CURRENT TIMESTAMP WHERE CLIENTORDERID = V_ORDERID AND CLIENTORDERLINEID = V_ORDERLINEID ; 

    END IF ; 

END  ; 

SET PATH "QSYS","QSYS2","JOES" ; 

CREATE PROCEDURE JOES.CANCELTRANSFERNUMBER ( 
    IN TRANSFERNUMBER INTEGER , 
    OUT O_RETURNCODE CHAR(10) , 
    OUT O_RETURNMESSAGE CHAR(50) , 
    OUT O_RESULT CHAR(5) ) 
    LANGUAGE SQL 
    SPECIFIC JOES.CANCELTRANSFERNUMBER 
    NOT DETERMINISTIC 
    MODIFIES SQL DATA 
    CALLED ON NULL INPUT 
    SET OPTION  ALWBLK = *ALLREAD , 
    ALWCPYDTA = *OPTIMIZE , 
    COMMIT = *NONE , 
    DECRESULT = (31, 31, 00) , 
    DFTRDBCOL = *NONE , 
    DYNDFTCOL = *NO , 
    DYNUSRPRF = *USER , 
    SRTSEQ = *HEX   
    BEGIN 

DECLARE V_SYNONRTNCD CHAR ( 7 ) DEFAULT '' ; 
DECLARE V_RETURMESSAGE CHAR ( 50 ) DEFAULT 'SUCCESS' ; 
DECLARE V_RETURNCODE CHAR ( 10 ) DEFAULT '' ; 
DECLARE V_TRANSFERNUMBER INTEGER ; 
DECLARE V_CHARTRANSFERBATCHNUMBER CHAR ( 15 ) ; 
DECLARE V_TRANSFERBATCHNUMBER INTEGER ; 
DECLARE V_RESULT CHAR ( 5 ) DEFAULT '00000' ; 
DECLARE SQLSTATE CHAR ( 5 ) DEFAULT '00000' ; 

SET O_RESULT = SQLSTATE ; 
SET O_RESULT = V_RESULT ; 


SET V_TRANSFERNUMBER = TRANSFERNUMBER ; 

SET V_TRANSFERBATCHNUMBER = ( SELECT MAX ( TRANSFERBATCH ) FROM JOES . TRANSFERREQHEADER WHERE TRANSFERNUMBER = V_TRANSFERNUMBER ) ; 
SET V_CHARTRANSFERBATCHNUMBER = CHAR ( V_TRANSFERBATCHNUMBER ) ; 

CALL MWWPROD . BAERXFR ( V_SYNONRTNCD , V_CHARTRANSFERBATCHNUMBER , V_RETURNCODE , V_RETURMESSAGE ) ; 

DELETE FROM JOES . TRANSFERREQHEADER WHERE TRANSFERBATCH = V_TRANSFERBATCHNUMBER ; 
DELETE FROM JOES . TRANSFERREQLINE WHERE TRANSFERBATCH = V_TRANSFERBATCHNUMBER ; 

SET O_RETURNCODE = V_RETURNCODE ; 
SET O_RETURNMESSAGE = V_RETURMESSAGE ; 

END  ;

Estou tentando um script que me ajude a dividir o arquivo acima em arquivos separados. Cada arquivo deve começar com

SET PATH ......

e terminar com

END ;

Então a saída deve ser como

Conteúdo no Arquivo 1:

SET PATH "QSYS","QSYS2","JOES" ; 

CREATE PROCEDURE JOES.CANCELORDERLINE ( 
    IN ORDERID INTEGER , 
    IN ORDERLINEID INTEGER , 
    IN NEWORDLINESTATUSCODE VARCHAR(10) , 
    IN NAME VARCHAR(50) , 
    OUT O_RESULT CHAR(5) ) 
    LANGUAGE SQL 
    SPECIFIC JOES.CANCELORDERLINE 
    NOT DETERMINISTIC 
    MODIFIES SQL DATA 
    CALLED ON NULL INPUT 
    SET OPTION  ALWBLK = *ALLREAD , 
    ALWCPYDTA = *OPTIMIZE , 
    COMMIT = *NONE , 
    DECRESULT = (31, 31, 00) , 
    DFTRDBCOL = *NONE , 
    DYNDFTCOL = *NO , 
    DYNUSRPRF = *USER , 
    SRTSEQ = *HEX   
    BEGIN ATOMIC 

    DECLARE V_ORDLINENUMFORDSP VARCHAR ( 16 ) ; 

    DECLARE V_ORDLINENUMSTR VARCHAR ( 3 ) ; 

    DECLARE V_ORDLINENUM INT ; 

    DECLARE V_RESULT CHAR ( 5 ) DEFAULT '00000' ; 

    DECLARE SQLSTATE CHAR ( 5 ) DEFAULT '00000' ; 



DECLARE V_ORDLNSTATUSHISTID INT ; 

    DECLARE V_ORDERID INT DEFAULT 0 ; 

    DECLARE V_ORDERLINEID INT DEFAULT 0 ; 

    DECLARE V_NEWORDLINESTATUSCODE VARCHAR ( 10 ) DEFAULT '' ; 

DECLARE V_OLDSTATUS VARCHAR ( 100 ) DEFAULT '' ; 

    DECLARE EXIT HANDLER FOR SQLEXCEPTION 

        SET O_RESULT = SQLSTATE ; 

        SET O_RESULT = V_RESULT ; 


SET V_ORDERID = ORDERID ; 
SET V_ORDERLINEID = ORDERLINEID ; 
SET V_NEWORDLINESTATUSCODE = NEWORDLINESTATUSCODE ; 


/* Insert a new record for the new line OrdLineStatusHist*/ 
    SET V_OLDSTATUS = ( SELECT OLSH . ORDLNSTATUSTYPECD FROM JOES . ORDERLINESTATUSHIST OLSH WHERE OLSH . CLIENTORDERID = V_ORDERID AND OLSH . CLIENTORDERLINEID = V_ORDERLINEID AND OLSH . STATUSENDDATETIME IS NULL ) ; 

    IF ( ( V_OLDSTATUS IS NULL ) OR ( V_OLDSTATUS <> V_NEWORDLINESTATUSCODE ) ) THEN 


        IF ( EXISTS ( SELECT ORDLNSTATUSHISTID FROM JOES . ORDERLINESTATUSHIST WHERE CLIENTORDERID = V_ORDERID AND CLIENTORDERLINEID = V_ORDERLINEID ) ) 
        THEN 
            SET V_ORDLNSTATUSHISTID = ( SELECT MAX ( ORDLNSTATUSHISTID ) FROM JOES . ORDERLINESTATUSHIST WHERE CLIENTORDERID = V_ORDERID AND CLIENTORDERLINEID = V_ORDERLINEID ) ; 
        ELSE 
            SET V_ORDLNSTATUSHISTID = 0 ; 
        END IF ; 


        IF ( V_ORDLNSTATUSHISTID > 0 ) THEN 

        UPDATE  JOES . ORDERLINESTATUSHIST SET STATUSENDDATETIME = CURRENT TIMESTAMP , LASTUPDATENAME = NAME , UPDTIMESTAMP = CURRENT TIMESTAMP WHERE 
        CLIENTORDERID = V_ORDERID AND CLIENTORDERLINEID = V_ORDERLINEID AND ORDLNSTATUSHISTID = V_ORDLNSTATUSHISTID ; 

        END IF ; 

        SET V_ORDLNSTATUSHISTID = V_ORDLNSTATUSHISTID + 1 ; 

        INSERT INTO JOES . ORDERLINESTATUSHIST ( CLIENTORDERID , CLIENTORDERLINEID , ORDLNSTATUSHISTID , ORDLNSTATUSTYPECD , STATUSSTARTDATETIME , STATUSENDDATETIME , 
        CREATENAME , CREATETIMESTAMP ) VALUES ( V_ORDERID , V_ORDERLINEID , V_ORDLNSTATUSHISTID , V_NEWORDLINESTATUSCODE , CURRENT TIMESTAMP , CURRENT TIMESTAMP , NAME , CURRENT TIMESTAMP ) ; 

        UPDATE JOES . CLIENTORDERLINE SET 
            ORDLNSTATUSTYPECD = V_NEWORDLINESTATUSCODE , UPDTIMESTAMP = CURRENT TIMESTAMP WHERE CLIENTORDERID = V_ORDERID AND CLIENTORDERLINEID = V_ORDERLINEID ; 

    END IF ; 

END  ;

Conteúdo no Arquivo 2:

SET PATH "QSYS","QSYS2","JOES" ; 

CREATE PROCEDURE JOES.CANCELTRANSFERNUMBER ( 
    IN TRANSFERNUMBER INTEGER , 
    OUT O_RETURNCODE CHAR(10) , 
    OUT O_RETURNMESSAGE CHAR(50) , 
    OUT O_RESULT CHAR(5) ) 
    LANGUAGE SQL 
    SPECIFIC JOES.CANCELTRANSFERNUMBER 
    NOT DETERMINISTIC 
    MODIFIES SQL DATA 
    CALLED ON NULL INPUT 
    SET OPTION  ALWBLK = *ALLREAD , 
    ALWCPYDTA = *OPTIMIZE , 
    COMMIT = *NONE , 
    DECRESULT = (31, 31, 00) , 
    DFTRDBCOL = *NONE , 
    DYNDFTCOL = *NO , 
    DYNUSRPRF = *USER , 
    SRTSEQ = *HEX   
    BEGIN 

DECLARE V_SYNONRTNCD CHAR ( 7 ) DEFAULT '' ; 
DECLARE V_RETURMESSAGE CHAR ( 50 ) DEFAULT 'SUCCESS' ; 
DECLARE V_RETURNCODE CHAR ( 10 ) DEFAULT '' ; 
DECLARE V_TRANSFERNUMBER INTEGER ; 
DECLARE V_CHARTRANSFERBATCHNUMBER CHAR ( 15 ) ; 
DECLARE V_TRANSFERBATCHNUMBER INTEGER ; 
DECLARE V_RESULT CHAR ( 5 ) DEFAULT '00000' ; 
DECLARE SQLSTATE CHAR ( 5 ) DEFAULT '00000' ; 

SET O_RESULT = SQLSTATE ; 
SET O_RESULT = V_RESULT ; 


SET V_TRANSFERNUMBER = TRANSFERNUMBER ; 

SET V_TRANSFERBATCHNUMBER = ( SELECT MAX ( TRANSFERBATCH ) FROM JOES . TRANSFERREQHEADER WHERE TRANSFERNUMBER = V_TRANSFERNUMBER ) ; 
SET V_CHARTRANSFERBATCHNUMBER = CHAR ( V_TRANSFERBATCHNUMBER ) ; 

CALL MWWPROD . BAERXFR ( V_SYNONRTNCD , V_CHARTRANSFERBATCHNUMBER , V_RETURNCODE , V_RETURMESSAGE ) ; 

DELETE FROM JOES . TRANSFERREQHEADER WHERE TRANSFERBATCH = V_TRANSFERBATCHNUMBER ; 
DELETE FROM JOES . TRANSFERREQLINE WHERE TRANSFERBATCH = V_TRANSFERBATCHNUMBER ; 

SET O_RETURNCODE = V_RETURNCODE ; 
SET O_RETURNMESSAGE = V_RETURMESSAGE ; 

END  ;

O que pode ser feito para conseguir isso? Estou impressionado aqui.

    

Answer 1

A solução que estou oferecendo inicia cada arquivo com 'SET PATH'. Ele não verifica 'END' no final da seção (mas você não explicou o que deveria acontecer se um 'SET PATH' subsequente ocorresse sem um 'END' anterior, então estou assumindo que isso não é uma tarefa difícil. exigência).

csplit -kz source.sql '/^SET PATH /' '{*}'

O comando csplit divide o arquivo source.sql com a expressão regular que corresponde à string 'SET PATH' ancorada no início da linha. Há vários sinalizadores controlando os nomes dos arquivos de saída, mas, por padrão, a saída de sua amostra é colocada nos arquivos xx00 e xx01 .