ABAP--原生SQL接口API(ADBC)的使用-优快云博客

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.

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 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,
msgTYPE c LENGTH 30,
keyTYPE 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.

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 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 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,
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 = 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( ).