動的SQLサンプル1

  SAMPLE01



  
CREATE or REPLACE PROCEDURE SAMPLE01
        (
         PM_BLOCKSHO    IN      NUMBER
        ,PM_DATE    IN      DATE
        )
        AS

/*================================================
   変数
================================================*/
    source_cursor        integer;
    destination_cursor    integer;
    rows_processed        integer;

    WK_DATE            DATE;
    WK_SELECT    VARCHAR2(1024);            -- select 文
    WK_INSERT    VARCHAR2(1024);            -- insert 文

    売上通常        NUMBER(10,0);        -- (E) 1
    売上通常消費税        NUMBER(10,0);        -- (F) 1
    売上中間請求        NUMBER(10,0);        -- (A) 2
    売上中間請求消費税    NUMBER(10,0);        -- (B) 2
    売上最終請求        NUMBER(10,0);        -- (I) 3
    売上最終請求消費税    NUMBER(10,0);        -- (J) 3
    売上値引        NUMBER(10,0);        -- (C) 4
    売上値引消費税        NUMBER(10,0);        -- (D) 4
    売上修正        NUMBER(10,0);        -- (G) 5
    売上修正消費税        NUMBER(10,0);        -- (H) 5

    ITU ̄,小o°AT        NUMBER(4,0);
    取引先o°AT        NUMBER(5,0);
    取引先支店o°AT        NUMBER(2,0);
    ?°A区分            NUMBER(1,0);
    金額            NUMBER(10,0);
    消費税            NUMBER(10,0);

    SVITU ̄,小o°AT        NUMBER(4,0);
    SV取引先o°AT        NUMBER(5,0);
    SV取引先支店o°AT    NUMBER(2,0);

    事業部            NUMBER(2,0);
    部            NUMBER(1,0);
    課            NUMBER(1,0);
    前月請求額        NUMBER(10,0);
    入金金額        NUMBER(10,0);

    最新請求年月日        DATE;

/*===========================================================================
   カーソル
===========================================================================*/

/*===========================================================================
   ローカル関数
===========================================================================*/
/*=======================================================
   最新請求締年月日取得
   IN           PM_DATE         : 締年月日
   RETURN       WK_DATE         : 最新請求締年月日
=====================================================*/
FUNCTION GET_DATE_SIME (PM_DATE IN DATE) RETURN DATE
        AS
        WK_DATE         DATE;
BEGIN
        WK_DATE         := ADD_MONTHS(PM_DATE,-1);
        WK_DATE         := WK_DATE + 1;
        RETURN          WK_DATE;
END GET_DATE_SIME;

/*=======================================================
   請求明細テーブル名取得
   IN        PM_DATE         : 締年月日
   RETURN    WK_TBLNAME      : 目的テーブル名
=====================================================*/
FUNCTION GET_SEIDT_NAME (PM_DATE IN DATE) RETURN VARCHAR2
    AS
    WK_DATE         DATE;
        WK_DAY          VARCHAR2(30);
        WK_TBLNAME    VARCHAR2(30);
BEGIN
        WK_DATE := LAST_DAY(PM_DATE);
        if WK_DATE = PM_DATE then
                WK_TBLNAME := 'KF_SEIDT99';
        else
                WK_DAY := TO_CHAR( PM_DATE, 'DD' );
                WK_TBLNAME := 'KF_SEIDT' || WK_DAY;
        end if;
    RETURN    WK_TBLNAME;
dbms_output.put_line( WK_TBLNAME );
END GET_SEIDT_NAME;


/*=======================================================
   長い文字列出力
=====================================================*/
PROCEDURE PUT_LSTR (PM_STR IN VARCHAR2)
        AS
BEGIN
    DBMS_OUTPUT.PUT_LINE(substr(PM_STR,1,80));
    DBMS_OUTPUT.PUT_LINE(substr(PM_STR,81,80));
    DBMS_OUTPUT.PUT_LINE(substr(PM_STR,161,80));
    DBMS_OUTPUT.PUT_LINE(substr(PM_STR,241,80));
    DBMS_OUTPUT.PUT_LINE(substr(PM_STR,321,80));
    DBMS_OUTPUT.PUT_LINE(substr(PM_STR,401,80));
    DBMS_OUTPUT.PUT_LINE(substr(PM_STR,481,80));
END PUT_LSTR;

/*=======================================================
   請求ファイルの前レコード当月請求額を取得
=====================================================*/
FUNCTION GET_BACK_SEIKYU
    (
    PM_BLOCKSHO IN NUMBER
    ,PM_TORI IN NUMBER
    ,PM_TORI_ST IN NUMBER
    )
    RETURN NUMBER
        AS
    WK_KINGAKU    NUMBER(10,0);
        CURSOR KF_SEI_cur is 
                select
                        KIN_TOSEI
                from
                        KF_SEI
        where
            CD_BLOCKSHO = PM_BLOCKSHO and
            CD_TORI = PM_TORI and
            CD_TORI_ST = PM_TORI_ST
        order by 
            YMD_SIME_SEI DESC;

BEGIN
    BEGIN
            OPEN    KF_SEI_cur;
            FETCH    KF_SEI_cur    INTO    WK_KINGAKU;
        if KF_SEI_cur%NOTFOUND then
            WK_KINGAKU := 0;
        end if;

    EXCEPTION
            WHEN OTHERS THEN
            WK_KINGAKU := 0;

    END;    

    RETURN WK_KINGAKU;
END GET_BACK_SEIKYU;

/*=======================================================
   回収Fより入金額を取得
=====================================================*/
FUNCTION GET_KAISYU
    (
    PM_BLOCKSHO IN NUMBER
    ,PM_TORI IN NUMBER
    ,PM_TORI_ST IN NUMBER
    )
    RETURN NUMBER
        AS
    WK_KINGAKU1    NUMBER(10,0);
    WK_KINGAKU2    NUMBER(10,0);
    WK_NYUKIN    NUMBER(10,0);
BEGIN

    WK_KINGAKU1 := 0;
    WK_KINGAKU2 := 0;

    select
        nvl(sum(KIN_KAI),0)
    into
        WK_KINGAKU1
    from
        KF_TOKU_KAI
    where
        CD_BLOCKSHO = PM_BLOCKSHO and
        CD_TORI    = PM_TORI and
        CD_TORI_ST = PM_TORI_ST and
        NO_KIN = 1 and
        NO_SYU not in (23,26) and
        KIN_KAI is not NULL;

    select
        nvl(sum(KIN_KAI),0)
    into
        WK_KINGAKU2
    from
        KF_TOKU_KAI
    where
        CD_BLOCKSHO = PM_BLOCKSHO and
        CD_TORI    = PM_TORI and
        CD_TORI_ST = PM_TORI_ST and
        NO_KIN = 2 and
        NO_SYU not in (23,26) and
        YMD_KAI between WK_DATE and PM_DATE and
        KIN_KAI is not NULL;

    WK_NYUKIN := WK_KINGAKU1 - WK_KINGAKU2;

    RETURN WK_NYUKIN;

END GET_KAISYU;

/*=====================================================
   取引サブMより部門コードを取得して
   最新請求締年月日を更新し、
   請求締日変更ファイルより該当レコードを削除する
=====================================================*/
PROCEDURE GET_TORISUB
        (
         PM_BLOCKSHO    IN      NUMBER
        ,PM_TORI        IN      NUMBER
        ,PM_TORI_ST    IN    NUMBER
        ,PM_DATE    IN    DATE
        ,PM_JIGYO    IN OUT  NUMBER
        ,PM_BUMON    IN OUT  NUMBER
        ,PM_KA        IN OUT  NUMBER
        )
        AS

    WK_KBN_JITA    NUMBER(1,0);
    WK_SIME        DATE;
BEGIN
    BEGIN
        select                    -- 取引サブMより部門コードを取得
            CD_JIGYO,
            CD_BUMON,
            CD_KA,
            YMD_SEI_NEW
        into
            PM_JIGYO,
            PM_BUMON,
            PM_KA,
            最新請求年月日
        from
            MM_TORISUB
        where
            CD_TORI = PM_TORI and
            CD_TORI_ST = PM_TORI_ST;

        if    最新請求年月日 is NULL then
                WK_DATE         := ADD_MONTHS(PM_DATE,-1);
                WK_DATE         := WK_DATE + 1;
        else
                WK_DATE         := 最新請求年月日 + 1;
        end if;

--        update
--            MM_TORISUB
--        set
--            YMD_SEI_NEW = PM_DATE
--        where
--            CD_TORI = PM_TORI and
--            CD_TORI_ST = PM_TORI_ST;

        select
            KBN_JITA
        into    
            WK_KBN_JITA
        from
            MM_TORI
        where
            CD_TORI    = PM_TORI and
            CD_TORI_ST = PM_TORI_ST;

        if    WK_KBN_JITA = 0 then
                        BEGIN
                select
                    YMD_HSIME_SEI
                into
                    WK_SIME
                from
                    KF_HSIME_SEI
                where
                    CD_BLOCKSHO    = PM_BLOCKSHO and
                    CD_TORI        = PM_TORI and
                    CD_TORI_ST    = PM_TORI_ST;
            EXCEPTION
                WHEN NO_DATA_FOUND THEN NULL;
            END;
            if SQL%FOUND then
                if WK_SIME = PM_DATE then
                    delete
                    from
                        KF_HSIME_SEI
                    where
                        CD_BLOCKSHO    = PM_BLOCKSHO and
                        CD_TORI        = PM_TORI and
                        CD_TORI_ST    = PM_TORI_ST;
                end if;
            end if;
        end if;

        EXCEPTION
                WHEN NO_DATA_FOUND THEN
                        PM_JIGYO := NULL;             
                        PM_BUMON := NULL;             
                        PM_KA := NULL;             
                WK_DATE         := ADD_MONTHS(PM_DATE,-1);
                WK_DATE         := WK_DATE + 1;
                WHEN OTHERS THEN
                        PM_JIGYO := NULL;             
                        PM_BUMON := NULL;             
                        PM_KA := NULL;             
                WK_DATE         := ADD_MONTHS(PM_DATE,-1);
                WK_DATE         := WK_DATE + 1;
        END;

END GET_TORISUB;

/*===========================================================================
   単純サブルーチン
===========================================================================*/
/*=====================================================
   ワーク変数の初期化
=====================================================*/
PROCEDURE ZERO_CLEAR
    AS
BEGIN

    売上通常        := 0;
    売上通常消費税        := 0;
    売上中間請求        := 0;
    売上中間請求消費税    := 0;    
    売上最終請求        := 0;
    売上最終請求消費税    := 0;
    売上値引        := 0;
    売上値引消費税        := 0;
    売上修正        := 0;
    売上修正消費税        := 0;

END ZERO_CLEAR;

/*=====================================================
   データの登録
=====================================================*/
PROCEDURE INSERT_DATA
    AS
BEGIN

    dbms_sql.bind_variable(destination_cursor, 'CD_BLOCKSHO', PM_BLOCKSHO);
    GET_TORISUB(PM_BLOCKSHO,SV取引先o°AT,SV取引先支店o°AT,PM_DATE,事業部,部,課);
    dbms_sql.bind_variable(destination_cursor, 'CD_JIGYO', 事業部);
    dbms_sql.bind_variable(destination_cursor, 'CD_BUMON', 部);
    dbms_sql.bind_variable(destination_cursor, 'CD_KA', 課);
    dbms_sql.bind_variable(destination_cursor, 'CD_TORI', SV取引先o°AT);
    dbms_sql.bind_variable(destination_cursor, 'CD_TORI_ST', SV取引先支店o°AT);
    dbms_sql.bind_variable(destination_cursor, 'YMD_FROM_SEI', WK_DATE);
    dbms_sql.bind_variable(destination_cursor, 'YMD_SIME_SEI', PM_DATE);
    前月請求額 := GET_BACK_SEIKYU(SVITU ̄,小o°AT,SV取引先o°AT,SV取引先支店o°AT);
    dbms_sql.bind_variable(destination_cursor, 'KIN_ZENSEI', 前月請求額);
    入金金額 := GET_KAISYU(SVITU ̄,小o°AT,SV取引先o°AT,SV取引先支店o°AT);
    dbms_sql.bind_variable(destination_cursor, 'KIN_NYUKIN', 入金金額);
    dbms_sql.bind_variable(destination_cursor, 'KIN_ZENSYU', 売上修正+売上修正消費税);
    dbms_sql.bind_variable(destination_cursor, 'KIN_URIAGE', 売上通常+売上最終請求+売上値引);
    dbms_sql.bind_variable(destination_cursor, 'KIN_TYUKAN', 売上中間請求);
    dbms_sql.bind_variable(destination_cursor, 'ZEI_URIAGE', 売上中間請求消費税
        +売上値引消費税
        +売上通常消費税
        +売上最終請求消費税);
    dbms_sql.bind_variable(destination_cursor, 'KIN_TOSEI', 前月請求額
        -入金金額
        +売上通常+売上最終請求+売上値引
        +売上中間請求
        +売上中間請求消費税+売上値引消費税+売上通常消費税+売上最終請求消費税);

--    dbms_sql.bind_variable(destination_cursor, 'YMD_UPD', sysdate);

    rows_processed := dbms_sql.execute(destination_cursor);

END INSERT_DATA;

/*===========================================================================
   主処理
===========================================================================*/
BEGIN

/*=====================================================
   初期処理
=====================================================*/
--    WK_DATE    := GET_DATE_SIME(PM_DATE);            -- 今回の締年月日よりfrom年月日を取得


/*=====================================================
   SELECT文作成(請求明細**読込用)
   金額と数量をサマリ
=====================================================*/


    WK_SELECT    :=
    'select 
        CD_BLOCKSHO,
        CD_TORI,
        CD_TORI_ST,
        sum(KIN_URIAGE),
        sum(ZEI_URIAGE),
        KBN_SORT
    from ' || GET_SEIDT_NAME(PM_DATE) || 
    ' group by
        CD_BLOCKSHO,
        CD_TORI,
        CD_TORI_ST,
        KBN_SORT';
/*
    'select 
        CD_BLOCKSHO,
        CD_TORI,
        CD_TORI_ST,
        sum(KIN_URIAGE),
        sum(ZEI_URIAGE),
        KBN_SORT
    from ' || GET_SEIDT_NAME(PM_DATE) || 
    ' where
        CD_BLOCKSHO = :PM_BLOCKSHO and
        YMD_URI_KJ between :WK_DATE and :PM_DATE
    group by
        CD_BLOCKSHO,
        CD_TORI,
        CD_TORI_ST,
        KBN_SORT';

*/

/*=====================================================
   入力定義
=====================================================*/
    source_cursor := dbms_sql.open_cursor;
    dbms_sql.parse(source_cursor, WK_SELECT, 2 );
    dbms_sql.define_column(source_cursor, 1, ITU ̄,小o°AT);
    dbms_sql.define_column(source_cursor, 2, 取引先o°AT);
    dbms_sql.define_column(source_cursor, 3, 取引先支店o°AT);
    dbms_sql.define_column(source_cursor, 4, 金額);
    dbms_sql.define_column(source_cursor, 5, 消費税);
    dbms_sql.define_column(source_cursor, 6, ?°A区分);

--    dbms_sql.bind_variable(source_cursor, 'PM_BLOCKSHO', PM_BLOCKSHO);
--    dbms_sql.bind_variable( source_cursor, 'WK_DATE', WK_DATE );
--    dbms_sql.bind_variable( source_cursor, 'PM_DATE', PM_DATE );

    rows_processed := dbms_sql.execute(source_cursor);


/*=====================================================
   出力定義(請求F)
=====================================================*/
    destination_cursor := dbms_sql.open_cursor;
    WK_INSERT    :=
    'insert into KF_SEI (
        CD_BLOCKSHO,
        CD_JIGYO,
        CD_BUMON,
        CD_KA,
        CD_TORI,
        CD_TORI_ST,
        YMD_FROM_SEI,
        YMD_SIME_SEI,
        KIN_ZENSEI,
        KIN_NYUKIN,
        KIN_ZENSYU,
        KIN_URIAGE,
        KIN_TYUKAN,
        ZEI_URIAGE,
        KIN_TOSEI
--        YMD_UPD
        )
     values (
        :CD_BLOCKSHO,
        :CD_JIGYO,
        :CD_BUMON,
        :CD_KA,
        :CD_TORI,
        :CD_TORI_ST,
        :YMD_FROM_SEI,
        :YMD_SIME_SEI,
        :KIN_ZENSEI,
        :KIN_NYUKIN,
        :KIN_ZENSYU,
        :KIN_URIAGE,
        :KIN_TYUKAN,
        :ZEI_URIAGE,
        :KIN_TOSEI
--        :YMD_UPD
        )';

    dbms_sql.parse( destination_cursor, WK_INSERT, 2 );

    SVITU ̄,小o°AT        := -1;
    ZERO_CLEAR;


/*=====================================================
   入出力
=====================================================*/
    loop

        if dbms_sql.fetch_rows(source_cursor) > 0 then
            dbms_sql.column_value(source_cursor, 1, ITU ̄,小o°AT);
            dbms_sql.column_value(source_cursor, 2, 取引先o°AT);
            dbms_sql.column_value(source_cursor, 3, 取引先支店o°AT);
            dbms_sql.column_value(source_cursor, 4, 金額);
            dbms_sql.column_value(source_cursor, 5, 消費税);
            dbms_sql.column_value(source_cursor, 6, ?°A区分);
            DBMS_OUTPUT.PUT_LINE(
                '取引先o°AT:' || TO_CHAR(取引先o°AT) ||
                '  取引先支店o°AT:' || TO_CHAR(取引先支店o°AT) ||
                '  ?°A区分:' || TO_CHAR(?°A区分) ||
                '  金額:' || TO_CHAR(金額) ||
                '  消費税:' || TO_CHAR(消費税)
            );


            if SVITU ̄,小o°AT = -1 then
                NULL;
            else
                if    SVITU ̄,小o°AT != ITU ̄,小o°AT or
                    SV取引先o°AT != 取引先o°AT or
                    SV取引先支店o°AT != 取引先支店o°AT then

                    BEGIN

                        INSERT_DATA;

                    EXCEPTION
                        WHEN DUP_VAL_ON_INDEX THEN
                            delete from KF_SEI where
                                CD_BLOCKSHO = PM_BLOCKSHO and
                                CD_JIGYO = 事業部 and
                                CD_BUMON = 部 and
                                CD_KA = 課 and
                                CD_TORI = SV取引先o°AT and
                                CD_TORI_ST = SV取引先支店o°AT and
                                YMD_SIME_SEI = PM_DATE;

                                INSERT_DATA;
                        WHEN OTHERS THEN

                            NULL;

                    END;

                    ZERO_CLEAR;

                end if;

            end if;

            if    ?°A区分 = 1 then
                売上通常        := 金額;
                売上通常消費税        := 消費税;
            elsif    ?°A区分 = 2 then
                売上中間請求        := 金額;
                売上中間請求消費税    := 消費税;    
            elsif    ?°A区分 = 3 then
                売上最終請求        := 金額;
                売上最終請求消費税    := 消費税;
            elsif    ?°A区分 = 4 then
                売上値引        := 金額;
                売上値引消費税        := 消費税;
            elsif    ?°A区分 = 5 then
                売上修正        := 金額;
                売上修正消費税        := 消費税;
            end if;

            SVITU ̄,小o°AT        := ITU ̄,小o°AT;
            SV取引先o°AT        := 取引先o°AT;
            SV取引先支店o°AT    := 取引先支店o°AT;

        else

            if SVITU ̄,小o°AT = -1 then
                NULL;
            else
                BEGIN
                    INSERT_DATA;

                EXCEPTION
                    WHEN DUP_VAL_ON_INDEX THEN
                        delete from KF_SEI where
                            CD_BLOCKSHO = PM_BLOCKSHO and
                            CD_JIGYO = 事業部 and
                            CD_BUMON = 部 and
                            CD_KA = 課 and
                            CD_TORI = SV取引先o°AT and
                            CD_TORI_ST = SV取引先支店o°AT and
                            YMD_SIME_SEI = PM_DATE;

                            INSERT_DATA;

                    WHEN OTHERS THEN
                        NULL;
                END;
            end if;

            exit;

        end if;

    end loop;


/*=====================================================
   終了処理
=====================================================*/
    commit;
    dbms_sql.close_cursor(source_cursor);
    dbms_sql.close_cursor(destination_cursor);
dbms_output.put_line( 'NEC7' );

/*=====================================================
   例外処理
=====================================================*/

EXCEPTION
    WHEN OTHERS THEN
        if dbms_sql.is_open(source_cursor) then
            dbms_sql.close_cursor(source_cursor);
        end if;
        if dbms_sql.is_open(destination_cursor) then
            dbms_sql.close_cursor(destination_cursor);
        end if;


END SAMPLE01;
/
  










  infoboard   管理者用   
このエントリーをはてなブックマークに追加





フリーフォントWEBサービス
SQLの窓WEBサービス

SQLの窓フリーソフト

素材

一般WEBツールリンク

SQLの窓

フリーソフト

JSライブラリ