ABAP–原生SQL接口API(ADBC)的使用详解编程语言

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
 
以上代码都是从sap帮助文档摘抄。
相关连接：IT虾米网

原创文章，作者：ItWorker，如若转载，请注明出处：https://blog.ytso.com/20097.html