使用abap向数据库直接写入数据

最新推荐文章于 2025-10-21 08:54:29 发布

原创最新推荐文章于 2025-10-21 08:54:29 发布 · 1.1w 阅读

9 ·

CC 4.0 BY-SA版权

SAP ABAP 专栏收录该内容

3 篇文章

订阅专栏

本文介绍了如何使用Native SQL进行数据库连接、选择、确定及关闭等操作，并详细展示了通过Native SQL执行过程调用、打开游标读取数据的示例。

Starting the Connection

EXEC SQL.
CONNECT TO dbs [AS con]
ENDEXEC.
连接到DBS并将其置为当前连接，其后所有的SQL命令都通过其执行。如果到DBS的连接已经存在，复用此连接，否则新建连接。

DBS可以是个字符串或host variable（变量值是表DBCON列CON_NAME内容），DBS信息必须在表DBCON内已维护。

使用AS选项，为连接分配一个名称。CON可是字符串或字符型host variable。便可通过此名称使用连接。

Choosing the Connection
EXEC SQL.
SET CONNECTION{con|DEFAULT}
ENDEXEC.
设置当前连接。可以是DBCON列CON_NAME值，或建连接用的con

DEFAULT：到当前SAP系统的central database system

Determining the Connection
EXEC SQL.
GET CONNECTION:con
ENDEXEC.
获得连接名称，如果连接有名称，此名称分配给con.如果没有名称，DBCON列CON_NAME值分配给con，如果当前连接是到central database of the ABAP-based SAP system，con ＝"DEFAULT".
Closing the Connection
EXEC SQL.
DISCONNECT con
ENDEXEC.
如果con 不是当前连接，没有影响，如果是当前连接，同时将the standard connection to the central database of the ABAP-based SAPsystem 设为当前连接。

连接有名称，必须使用名称，如果没有名称才可以使用DBCON列CON_NAME值。不能使用"DEFAULT"

例，打开一个连接，将SCARR内容导入。

*数据库连接

PARAMETERS dbs TYPE dbcon-con_name.
DATA carrid_wa TYPE scarr-carrid.
*数据库系统类型
DATA dbtype TYPE dbcon_dbms.
SELECT SINGLE dbms
       FROM dbcon
       INTO dbtype
       WHERE con_name = dbs.

*判断数据库类型
IF dbtype = 'ORA'.
TRY.
      EXEC SQL.
        CONNECTTO :dbs
      ENDEXEC.

*建立连接异常
    IF sy-subrc<> 0.
        RAISE EXCEPTION TYPEcx_sy_native_sql_error.
      ENDIF.

*执行SQL
      EXEC SQL.
        OPEN dbcur FOR   SELECT carrid FROM scarr
      ENDEXEC.
      DO.
        EXEC SQL.
          FETCH NEXT dbcur INTO:carrid_wa
        ENDEXEC.
        IF sy-subrc <> 0.
          EXIT.
        ELSE.
          WRITE / carrid_wa.
        ENDIF.
      ENDDO.

*关闭游标
      EXEC SQL.
        CLOSEdbcur
      ENDEXEC.

*关闭连接
      EXEC SQL.
        DISCONNECT :dbs
      ENDEXEC.

*异常处理
CATCH cx_sy_native_sql_error.
MESSAGE `Error in Native SQL.` TYPE'I'.
ENDTRY.
ENDIF.
In native SQL, similar statements for reading data using a database cursoras in Open SQL can be specified.

EXEC SQL.
OPEN dbcur FORSELECT ...
ENDEXEC.
打开游标dbcur. For dbcur, a flatcharacter-type host variable can be specifried.
EXEC SQL.
FETCH NEXT dbcur INTO ...
ENDEXEC.
读取游标数据。如果没到数据，sy-subrc ＝4.

sy-dbcnt＝已读过的条数。

EXEC SQL.
CLOSE dbcur
ENDEXEC.
关闭游标

执行过程

EXEC SQL.
EXECUTE PROCEDURE proc ( IN    p_in1   IN    p_in2 ...,
                           OUT   p_out1  OUT   p_out2 ...,
                           INOUT p_inout1 INOUTp_inout2 ... )
ENDEXEC.

In database systems, you can define procedures as so-called "storedprocedures". Since the syntax for calling such procedures and thepertinent parameter transfer for various database systems can vary widely, auniform command exists in Native SQL.

The statement EXECUTE PROCEDURE calls a procedure proc storedin the database. For all formal parameters of the procedure, you must specifythe actual parameters, separated by commas. You must specify IN, OUT or INOUT before every actual parameter, in order toindicate whether the parameter is an input, output, or input/output parameter.You can use literals or Host Variables labeled by a colon(:)for the actual parameters.

Example

This example defines a selfuncprocedure using database specific SQL-Statements (Informix). It also calls theprocedure using the SAP-specific Native-SQL-Statement EXECUTEPROCEDURE in a LOOP-loop bymeans of a Selection Table, and deletes thethe procedure using an SQL-Statement. In the case shown here, the procedure isa function whose return value output in EXECUTEPROCEDURE is copied to the hostvariable name.

DATA scarr_carrid TYPE scarr-carrid.
SELECT-OPTIONS s_carrid FOR scarr_carrid NO INTERVALS.
DATA s_carrid_wa LIKE LINE OF s_carrid.

DATA name TYPE c LENGTH 20.

TRY.
    EXEC SQL.
      CREATE FUNCTION selfunc( inputCHAR(3) )
        RETURNING char(20);
        DEFINE output char(20);
        SELECT carrname
               INTO output
               FROM scarr
               WHERE mandt = '000' AND
                     carrid = input;
        RETURN output;
        END FUNCTION;
    ENDEXEC.
    LOOP AT s_carrid INTO s_carrid_wa
                     WHERE sign = 'I' ANDoption = 'EQ'.
      TRY.
        EXEC SQL.
            EXECUTE PROCEDURE selfunc(IN :s_carrid_wa-low,
                                      OUT :name )
          ENDEXEC.
          WRITE: / s_carrid_wa-low, name.
        CATCH cx_sy_native_sql_error.
          MESSAGE `Error in procedureexecution` TYPE 'I'.
      ENDTRY.
    ENDLOOP.
    EXEC SQL.
      DROP FUNCTION selfunc;
    ENDEXEC.
CATCH cx_sy_native_sql_error.
    MESSAGE `Error in procedure handling`TYPE 'I'.
ENDTRY.