TYPE-POOLS:ADBC.

DATA LV_SQL TYPE STRING.
DATA LV_FROM TYPE STRING.
DATA LV_WHERE TYPE STRING.
DATA LV_WHERE2 TYPE STRING.
DATA LV_WHERE3 TYPE STRING.
DATA : V_ROWS TYPE I.
DATA : LT_META   TYPE ADBC_RS_METADATA_DESCR_TAB,
       LT_COLUMN TYPE ADBC_COLUMN_TAB,
       LS_META   LIKE LINE OF LT_META,
       LS_COLUMN LIKE LINE OF LT_COLUMN.

*Define variables for the connection
DATA: LR_DBCONN     TYPE REF TO CL_SQL_CONNECTION,
      LV_CON        TYPE DBCON_NAME VALUE 'HDB',
      LR_SQL_ENV    TYPE REF TO CL_SQL_STATEMENT,
      LR_SQL_RESULT TYPE REF TO CL_SQL_RESULT_SET,
      LR_SQL_EXC    TYPE REF TO CX_SQL_EXCEPTION,           "#EC NEEDED
      LR_PAR_EXC    TYPE REF TO CX_PARAMETER_INVALID,       "#EC NEEDED
      DREF          TYPE REF TO DATA,
      TAB_REF       TYPE REF TO DATA,
      GT_COMPONENTS TYPE CL_ABAP_STRUCTDESCR=>COMPONENT_TABLE,
      GS_COMPONENTS LIKE LINE OF GT_COMPONENTS.
FIELD-SYMBOLS : <<SPAN style="COLOR: rgb(0,0,255)">TABLE> TYPE ANY TABLE,
                   TYPE ANY.

*Get connection
LR_DBCONN = CL_SQL_CONNECTION=>GET_CONNECTION( CON_NAME = LV_CON ).
CREATE OBJECT LR_SQL_ENV
  EXPORTING
    CON_REF = LR_DBCONN.

LV_SQL = 'SELECT * FROM ZLYTEST01'.


LR_SQL_RESULT = LR_SQL_ENV->EXECUTE_QUERY( LV_SQL ).
LT_META = LR_SQL_RESULT->GET_METADATA( ).

LOOP AT LT_META INTO LS_META.
  LS_COLUMN = LS_META-COLUMN_NAME.
  APPEND LS_COLUMN TO LT_COLUMN.
  GS_COMPONENTS-NAME = LS_META-COLUMN_NAME.
  GS_COMPONENTS-TYPE ?= CL_ABAP_DATADESCR=>DESCRIBE_BY_DATA( P_DATA = LS_META-DATA_TYPE ).
  APPEND GS_COMPONENTS TO GT_COMPONENTS.
ENDLOOP.

DATA LR_TABLEDESCR TYPE REF TO CL_ABAP_TABLEDESCR.

LR_TABLEDESCR = CL_ABAP_TABLEDESCR=>CREATE(
P_LINE_TYPE  = CL_ABAP_STRUCTDESCR=>CREATE( P_COMPONENTS = GT_COMPONENTS ) ).

CREATE DATA TAB_REF TYPE HANDLE LR_TABLEDESCR.

*  get REFERENCE OF LT_DATA INTO tab_ref.
LR_SQL_RESULT->SET_PARAM_TABLE(
  EXPORTING
    ITAB_REF             =  TAB_REF   " Reference to Output Variable
    CORRESPONDING_FIELDS = LT_COLUMN ).

**Execute the query
LR_SQL_RESULT->NEXT_PACKAGE( ).
V_ROWS = LR_SQL_RESULT->NEXT( ).

ASSIGN TAB_REF->* TO <<SPAN style="COLOR: rgb(0,0,255)">TABLE>.

ABAP--原生SQL接口API(ADBC)的使用

转载自：http://blog.csdn.net/sap_jack/article/details/4562943

ADBC(ABAP Database Connectivity)

sap为大家提供原生SQL(Native SQL)接口API，该接口主要由四个类组成：
CL_SQL_STATEMENT - Execution of SQL Statements
CL_SQL_PREPARED_STATEMENT - Prepared SQL Statements
CL_SQL_CONNECTION - Administration of Database Connections
CX_SQL_EXCEPTION - Exception Class
 
原生SQL接口API可以在ABAP里执行动态的原生SQL操作，解决用户一些非常特殊的操作需求。
 
样例一：数据定义语言DDL(Create,Drop,Grant,Revoke) 和数据操纵语言DML(Update,Insert,Delete)操作
REPORT demo_adbc_ddl_dml.


PARAMETERS  p_name TYPE c LENGTH 10 DEFAULT 'mytab'.
SELECTION-SCREEN SKIP.
PARAMETERS: p_create RADIOBUTTON GROUP grp,
            p_insert RADIOBUTTON GROUP grp,
            p_select RADIOBUTTON GROUP grp,
            p_drop   RADIOBUTTON GROUP grp.
SELECTION-SCREEN SKIP.
PARAMETERS  p_key TYPE i DEFAULT 1.


CLASS adbc DEFINITION.
  PUBLIC SECTION.
    CLASS-METHODS main.
  PRIVATE SECTION.
    CLASS-DATA: dbname TYPE string,
                sql TYPE REF TO cl_sql_statement,
                wa1 TYPE c LENGTH 10,
                wa2 TYPE c LENGTH 10,
                err TYPE REF TO cx_sql_exception .
    CLASS-METHODS: create RAISING cx_sql_exception,
                   insert RAISING cx_sql_exception,
                   select RAISING cx_sql_exception,
                   drop   RAISING cx_sql_exception.
ENDCLASS.


CLASS adbc IMPLEMENTATION.
  METHOD main.
    dbname = 'ABAP_DOCU_DEMO_' && p_name.
    TRY.
        CREATE OBJECT sql.
        IF p_create = 'X'.
          create( ).
          MESSAGE 'Create was successful' TYPE 'S'.
        ELSEIF p_insert = 'X'.
          insert( ).
          MESSAGE 'Insert was successful' TYPE 'S'.
        ELSEIF p_select = 'X'.
          select( ).
          MESSAGE 'Select was successful' TYPE 'S'.
        ELSEIF p_drop   = 'X'.
          drop( ).
          MESSAGE 'Drop was successful' TYPE 'S'.
        ENDIF.
      CATCH cx_sql_exception INTO err.
        MESSAGE err TYPE 'I' DISPLAY LIKE 'E'.
    ENDTRY.
  ENDMETHOD.
  METHOD create.
    sql->execute_ddl(
      `CREATE TABLE ` && dbname   &&
      `( val1 char(10) NOT NULL,` &&
      `  val2 char(10) NOT NULL,` &&
      `  PRIMARY KEY (val1) )` ).
  ENDMETHOD.
  METHOD insert.
    DO 100 TIMES.
      wa1 = sy-index.
      wa2 = sy-index ** 2.
      sql->execute_update(
       `INSERT INTO ` && dbname && ` ` &&
       `VALUES ('` && wa1 && `','` && wa2 && `')` ).
    ENDDO.
  ENDMETHOD.
  METHOD select.
    DATA: result TYPE REF TO cl_sql_result_set,
          msg    TYPE c LENGTH 30,
          key    TYPE c LENGTH 10,
          dref   TYPE REF TO data,
          rc     TYPE i.
    key = p_key.
    result = sql->execute_query(
      `SELECT val1, val2 ` &&
      `FROM ` && dbname && ` ` &&
      `WHERE val1 = ` && `'` && key && `'` ).
    GET REFERENCE OF wa1 INTO dref.
    result->set_param( dref ).
    GET REFERENCE OF wa2 INTO dref.
    result->set_param( dref ).
    rc = result->next( ).
    IF rc > 0.
      WRITE: 'Result:' TO msg,
             wa1 TO msg+10,
             wa2 TO msg+20.
    ELSE.
      msg = 'No entry found'.
    ENDIF.
    result->close( ).
    MESSAGE msg TYPE 'I'.
  ENDMETHOD.
  METHOD drop.
    sql->execute_ddl(
     `DROP TABLE ` && dbname ).
  ENDMETHOD.
ENDCLASS.


START-OF-SELECTION.
  adbc=>main( ).


样例二、参数绑定样例
 
REPORT demo_adbc_ddl_dml_binding.


PARAMETERS  p_name TYPE c LENGTH 10 DEFAULT 'mytab'.
SELECTION-SCREEN SKIP.
PARAMETERS: p_create RADIOBUTTON GROUP grp,
            p_insert RADIOBUTTON GROUP grp,
            p_select RADIOBUTTON GROUP grp,
            p_drop   RADIOBUTTON GROUP grp.
SELECTION-SCREEN SKIP.
PARAMETERS  p_key TYPE i DEFAULT 1.


CLASS adbc DEFINITION.
  PUBLIC SECTION.
    CLASS-METHODS main.
  PRIVATE SECTION.
    CLASS-DATA: dbname TYPE string,
                sql TYPE REF TO cl_sql_statement,
                wa1 TYPE c LENGTH 10,
                wa2 TYPE c LENGTH 10,
                err TYPE REF TO cx_sql_exception .
    CLASS-METHODS: create RAISING cx_sql_exception,
                   insert RAISING cx_sql_exception,
                   select RAISING cx_sql_exception,
                   drop   RAISING cx_sql_exception.
ENDCLASS.


CLASS adbc IMPLEMENTATION.
  METHOD main.
    dbname = 'ABAP_DOCU_DEMO_' && p_name.
    TRY.
        CREATE OBJECT sql.
        IF p_create = 'X'.
          create( ).
          MESSAGE 'Create was successful' TYPE 'S'.
        ELSEIF p_insert = 'X'.
          insert( ).
          MESSAGE 'Insert was successful' TYPE 'S'.
        ELSEIF p_select = 'X'.
          select( ).
          MESSAGE 'Select was successful' TYPE 'S'.
        ELSEIF p_drop   = 'X'.
          drop( ).
          MESSAGE 'Drop was successful' TYPE 'S'.
        ENDIF.
      CATCH cx_sql_exception INTO err.
        MESSAGE err TYPE 'I' DISPLAY LIKE 'E'.
    ENDTRY.
  ENDMETHOD.
  METHOD create.
    sql->execute_ddl(
      `CREATE TABLE ` && dbname   &&
      `( val1 char(10) NOT NULL,` &&
      `  val2 char(10) NOT NULL,` &&
      `  PRIMARY KEY (val1) )` ).
  ENDMETHOD.
  METHOD insert.
    DATA dref TYPE REF TO data.
    DO 100 TIMES.
      GET REFERENCE OF wa1 INTO dref.
      sql->set_param( dref ).
      GET REFERENCE OF wa2 INTO dref.
      sql->set_param( dref ).
      wa1 = sy-index.
      wa2 = sy-index ** 2.
      sql->execute_update(
       `INSERT INTO ` && dbname && ` VALUES (?,?)` ).
    ENDDO.
  ENDMETHOD.
  METHOD select.
    DATA: result TYPE REF TO cl_sql_result_set,
          msg    TYPE c LENGTH 30,
          key    TYPE c LENGTH 10,
          dref   TYPE REF TO data,
          rc     TYPE i.
    key = p_key.
    GET REFERENCE OF key INTO dref.
    sql->set_param( dref ).
    result = sql->execute_query(
      `SELECT val1, val2 ` &&
      `FROM ` && dbname && ` ` &&
      `WHERE val1 = ?` ).
    GET REFERENCE OF wa1 INTO dref.
    result->set_param( dref ).
    GET REFERENCE OF wa2 INTO dref.
    result->set_param( dref ).
    rc = result->next( ).
    IF rc > 0.
      WRITE: 'Result:' TO msg,
             wa1 TO msg+10,
             wa2 TO msg+20.
    ELSE.
      msg = 'No entry found'.
    ENDIF.
    result->close( ).
    MESSAGE msg TYPE 'I'.
  ENDMETHOD.
  METHOD drop.
    sql->execute_ddl(
     `DROP TABLE ` && dbname ).
  ENDMETHOD.
ENDCLASS.


START-OF-SELECTION.
  adbc=>main( ). 
 
样例三、存储过程调用
REPORT demo_adbc_stored_procedure.


PARAMETERS incprice TYPE sflight-price.


CLASS demo DEFINITION.
  PUBLIC SECTION.
    CLASS-METHODS main.
ENDCLASS.


CLASS demo IMPLEMENTATION.
  METHOD main.
    DATA:  sql     TYPE REF TO cl_sql_statement,
           err     TYPE REF TO cx_sql_exception,
           dref    TYPE REF TO data.
    CREATE OBJECT sql.
    TRY.
       sql->execute_ddl(
         `CREATE OR REPLACE PROCEDURE increase_price (x IN NUMBER) IS `
          && `BEGIN `
          && `UPDATE sflight SET price = price + x`
          && `               WHERE mandt = '` && sy-mandt && `'; `
          && `END;` ).
        GET REFERENCE OF incprice INTO dref.
        sql->set_param( data_ref = dref
                        inout    = cl_sql_statement=>c_param_in ).
        sql->execute_procedure( proc_name = 'increase_price' ).
      CATCH cx_sql_exception INTO err.
        MESSAGE err TYPE 'I' DISPLAY LIKE 'E'.
    ENDTRY.
  ENDMETHOD.
ENDCLASS.


INITIALIZATION.
  IF sy-dbsys <> 'ORACLE'.
    MESSAGE 'Example is only for Oracle SQL' TYPE 'I' DISPLAY LIKE 'E'.
    LEAVE PROGRAM.
  ENDIF.


START-OF-SELECTION.
  demo=>main( ).
 
样例四、动态查询样例，参见sap程序ADBC_QUERY