using merge statement in oracle pro*c program

step:

prepare...

execute...

 

not support directly exec sql or execute immediate.

 

 

following is the demo. remove some sensitive info.

#include

#include
#include
#include
#include

int main( int argc, char* argv[] )
{
 int i = 0;
    char v_sql_statement[40960];
    char v_effect_start_time[20] = {'\0'}; /* save log record input time           */
    char v_result_msg[256] = {'\0'};       /* save result message && sqlcode       */

 time_t rawtime;
 struct tm * timeinfo;

 char v_username[20];
 char v_password[20];
 char v_dbstring[20];
 char v_program_name[50];
 char v_start_time[20];
 char v_start_dt[20];
 char v_end_dt[20];

 strcpy( (char *) v_program_name, argv[1] ); /*""*/
 strcpy( (char *) v_start_dt, argv[2] );
 strcpy( (char *) v_end_dt, argv[3] );
 strcpy( (char *) v_username, argv[4] ); /*"ODS"*/
 strcpy( (char *) v_password, argv[5] ); /*"***"*/
 strcpy( (char *) v_dbstring, argv[6] ); /*"BAS_INTERNET"*/

 /* database initialization */
    EXEC SQL CONNECT :v_username IDENTIFIED BY :v_password using :v_dbstring;
    if ( 0!=sqlca.sqlcode && 1403!=sqlca.sqlcode )
    {
        printf( "EXEC SQL CONNECT %s IDENTIFIED BY %s using %s failed\n", v_username, v_password, v_dbstring );
        fflush( stdout );
        return -1;
    }

    /* insert running state */
    EXEC SQL SELECT to_char(sysdate,'YYYY-MM-DD HH24:MI:SS') INTO :v_effect_start_time FROM dual;
    EXEC SQL INSERT INTO etladmin.etl_log(program_name, start_time, end_time, status, effect_start_time) values(:v_program_name, to_date(:v_start_dt, 'YYYY-MM-DD HH24:MI:SS'), to_date(:v_end_dt, 'YYYY-MM-DD HH24:MI:SS'),'Running', to_date(:v_effect_start_time, 'YYYY-MM-DD HH24:MI:SS'));
    EXEC SQL COMMIT;
    EXEC SQL DELETE FROM ODS.bas_tab_calllog_call_ WHERE end_time>=TO_DATE(:v_start_dt,'YYYY-MM-DD HH24:MI:SS') AND end_time    /* bas_tab_calllog_call */
    sprintf( v_sql_statement,
             "MERGE INTO ODS.BAS_TAB__USER_LIST_ TARGET_T   "
       "USING (SELECT A.PROVINCE_ID,                         "
       "              A.PROVIDER_ID,                         "
       "              B.AREA_ID,                             "
       "              USER_ID,                               "
       "              USER_NBR,                              "
       "              USER_PWD,                              "
       "              REG_DATE,                              "
       "              UNREG_DATE,                            "
       "       0,                                      "
       "              REG_SRV_SALES,                         "
       "              UNREG_SRV_SALES,                       "
       "              REG_MODE,                              "
       "              UNREG_MODE                             "
       "         FROM STAGE.BAS_TAB__USER_LIST_ A, DW.BAS_DM_NUMBERSEG B "
       "        WHERE B.SEG_NUMBER(+) = SUBSTR(A.USER_NBR, 1, 7)) SOURCE_T       "
       "ON (TARGET_T.USER_NBR = SOURCE_T.USER_NBR)                               "
       "WHEN MATCHED THEN                                                        "
       "  UPDATE                                                                 "
       "     SET TARGET_T.REG_DATE        = SOURCE_T.REG_DATE,                   "
       "         TARGET_T.UNREG_DATE      = SOURCE_T.UNREG_DATE,                 "
       "         TARGET_T.USER_PWD        = SOURCE_T.USER_PWD,                   "
       "         TARGET_T.REG_SRV_SALES   = SOURCE_T.REG_SRV_SALES,              "
       "         TARGET_T.UNREG_SRV_SALES = SOURCE_T.UNREG_SRV_SALES,            "
       "         TARGET_T.REG_MODE        = SOURCE_T.REG_MODE,                   "
       "         TARGET_T.UNREG_MODE      = SOURCE_T.UNREG_MODE,                 "
       "     TARGET_T.CITY_ID      = SOURCE_T.AREA_ID                         "
       "WHEN NOT MATCHED THEN          "
       "  INSERT                       "
       "  VALUES                       "
       "    (SOURCE_T.PROVINCE_ID,     "
       "     SOURCE_T.PROVIDER_ID,     "
       "     SOURCE_T.AREA_ID,         "
       "     SOURCE_T.USER_ID,         "
       "     SOURCE_T.USER_NBR,        "
       "     SOURCE_T.USER_PWD,        "
       "     SOURCE_T.REG_DATE,        "
       "     SOURCE_T.UNREG_DATE,      "
       "   0,                         "
       "     SOURCE_T.REG_SRV_SALES,   "
       "     SOURCE_T.UNREG_SRV_SALES, "
       "     SOURCE_T.REG_MODE,        "
       "     SOURCE_T.UNREG_MODE)      " );

    EXEC SQL PREPARE insert_sql_statement FROM :v_sql_statement;
    if ( 0!=sqlca.sqlcode && 1403!=sqlca.sqlcode )
    {
        sprintf( v_result_msg, "PREPARE bas_tab__call:%s", sqlca.sqlerrm.sqlerrmc );
        EXEC SQL ROLLBACK;
        EXEC SQL UPDATE etladmin.etl_log
                 SET status='F', effect_end_time=sysdate, error_message=:v_result_msg
                 WHERE program_name=:v_program_name
                 AND start_time=to_date(:v_start_dt,'YYYY-MM-DD HH24:MI:SS')
                 AND end_time=to_date(:v_end_dt,'YYYY-MM-DD HH24:MI:SS')
                 AND effect_start_time=to_date(:v_effect_start_time,'YYYY-MM-DD HH24:MI:SS');
        EXEC SQL COMMIT RELEASE;
        return -1;
    }

    EXEC SQL EXECUTE insert_sql_statement;
 if ( 0!=sqlca.sqlcode && 1403!=sqlca.sqlcode )
    {
        sprintf( v_result_msg, "EXECUTE bas_tab__call:%s", sqlca.sqlerrm.sqlerrmc );
        EXEC SQL ROLLBACK;
        EXEC SQL UPDATE etladmin.etl_log
                 SET status='F', effect_end_time=sysdate, error_message=:v_result_msg
                 WHERE program_name=:v_program_name
                 AND start_time=to_date(:v_start_dt,'YYYY-MM-DD HH24:MI:SS')
                 AND end_time=to_date(:v_end_dt,'YYYY-MM-DD HH24:MI:SS')
                 AND effect_start_time=to_date(:v_effect_start_time,'YYYY-MM-DD HH24:MI:SS');
        EXEC SQL COMMIT RELEASE;
        return -1;
    }
    EXEC SQL COMMIT;
 EXEC SQL UPDATE etladmin.etl_log
             SET status='S', effect_end_time=sysdate, error_message='Sucess'
             WHERE program_name=:v_program_name
             AND start_time=to_date(:v_start_dt,'YYYY-MM-DD HH24:MI:SS')
             AND end_time=to_date(:v_end_dt,'YYYY-MM-DD HH24:MI:SS')
             AND effect_start_time=to_date(:v_effect_start_time,'YYYY-MM-DD HH24:MI:SS');

    EXEC SQL COMMIT RELEASE;

    return 0;
}