ABAP--原生SQL接口API(ADBC)的使用ABAP--原生SQL接口API(ADBC)的使用
sap为大家提供原生SQL(Native SQL)接口API，该接口主要由四个类组成：
sql server拼接字符串函数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.PARAMETERSp_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.PARAMETERSp_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 RAISIN
G 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,msgTYPE c LENGTH 30,keyTYPE c LENGTH 10,dref TYPE REF TO data,rc TYPE i.key = sult = sql->execute_query(`SELECT val1, val2 ` &&`FROM ` && dbname && ` ` &&`WHERE val1 = ` && `'` && key && `'` ).GET REFERENCE OF wa1 sult->set_param( dref ).GET REFERENCE OF wa2 sult->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'.sult->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.PARAMETERSp_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-SCR
EEN SKIP.PARAMETERSp_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,msgTYPE c LENGTH 30,keyTYPE 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 sult->set_param( dref ).GET REFERENCE OF wa2 sult->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'.sult->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, drefTYPE 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 = drefinout= 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

版权声明：本站内容均来自互联网，仅供演示用，请勿用于商业和其他非法用途。如果侵犯了您的权益请与我们联系QQ:729038198，我们将在24小时内删除。