夜雨

Appearance

SAP Native SQL 深度开发指南 ​

一、Native SQL 核心特性与优缺点 ​

优势亮点 ​

  1. ‌直接访问数据库‌：绕过ABAP字典层，执行原生DDL/DML（如创建索引）‌
  2. ‌数据库特性支持‌：调用Oracle分析函数、SQL Server窗口函数等专有功能‌:ml-citation
  3. ‌跨系统操作‌：通过DB Link访问非SAP系统数据库表‌
  4. ‌性能优化‌：处理千万级数据时减少中间层消耗‌
  5. ‌动态SQL支持‌：运行时动态构建复杂查询语句‌

主要缺陷 ​

  1. ‌数据库依赖‌：语法随数据库类型变化（Oracle/SQL Server差异）‌
  2. ‌安全风险‌：开放DDL操作权限，存在SQL注入漏洞‌
  3. ‌维护困难‌：代码无法自动适应SAP版本升级‌:ml-citation
  4. ‌缓冲失效‌：无法使用ABAP字典表缓冲机制‌
  5. ‌调试复杂‌：错误日志依赖数据库返回信息‌

二、完整语法体系 ​

基础执行结构 ​

abap
EXEC SQL [PERFORMING callback_form].
  <Native SQL Statement>
ENDEXEC. ‌

‌动态参数示例‌：

abap
DATA: lv_empno TYPE n LENGTH 4 VALUE '1001'.

EXEC SQL.
  SELECT ename, job 
  INTO :ls_emp 
  FROM scott.emp 
  WHERE empno = :lv_empno
ENDEXEC. ‌

多数据库适配语法 ​

abap
" Oracle分页语法
EXEC SQL.
  SELECT * FROM (
    SELECT t.*, ROWNUM rn 
    FROM (SELECT * FROM zlarge_tab ORDER BY key) t 
    WHERE ROWNUM <= :lv_end
  ) WHERE rn >= :lv_start
ENDEXEC.

" SQL Server分页语法
EXEC SQL.
  SELECT * FROM zlarge_tab 
  ORDER BY key 
  OFFSET :lv_start ROWS 
  FETCH NEXT :lv_page_size ROWS ONLY
ENDEXEC. ‌

三、关键开发场景 ​

1. 内表数据交互 ​

abap
TYPES: BEGIN OF ty_emp,
         empid TYPE n,
         name  TYPE string,
       END OF ty_emp.

DATA: lt_emp TYPE TABLE OF ty_emp.

EXEC SQL.
  SELECT employee_id, last_name 
  INTO :lt_emp 
  FROM employees 
  WHERE department_id = 100
ENDEXEC. ‌

2. 跨表关联查询 ​

abap
EXEC SQL.
  SELECT a.mandt, b.external_data 
  FROM sap_tab a 
  INNER JOIN external_schema.external_tab b 
    ON a.keycol = b.keycol 
  WHERE a.bukrs = :lv_company
ENDEXEC. ‌

3. 字符串处理 ​

abap
" Oracle字符串截取
EXEC SQL.
  SELECT SUBSTR(address, 1, 20) 
  INTO :lv_short_addr 
  FROM zaddress 
  WHERE id = :lv_id
ENDEXEC.

" SQL Server字符串处理
EXEC SQL.
  SELECT LEFT(address, 20) 
  INTO :lv_short_addr 
  FROM zaddress 
  WHERE id = :lv_id
ENDEXEC. ‌

4. 数据库链接(DB Link) ​

abap
" 创建DB Link（需DBA权限）
EXEC SQL.
  CREATE DATABASE LINK remote_db 
  CONNECT TO remote_user IDENTIFIED BY password 
  USING 'remote_tns'
ENDEXEC.

" 跨库查询
EXEC SQL.
  SELECT local.col1, remote.col2 
  FROM local_table local 
  JOIN remote_table@remote_db remote 
    ON local.key = remote.key
ENDEXEC. ‌

四、执行控制与调试 ​

事务管理 ​

abap
EXEC SQL. 
  BEGIN TRANSACTION 
ENDEXEC.

EXEC SQL.
  UPDATE zaccount 
  SET balance = balance - :lv_amount 
  WHERE accno = :lv_acc_from
ENDEXEC.

EXEC SQL.
  UPDATE zaccount 
  SET balance = balance + :lv_amount 
  WHERE accno = :lv_acc_to
ENDEXEC.

IF sy-subrc = 0.
  EXEC SQL. COMMIT WORK ENDEXEC.
ELSE.
  EXEC SQL. ROLLBACK WORK ENDEXEC.
ENDIF. ‌

错误捕获 ​

abap
TRY.
  EXEC SQL.
    INSERT INTO zlog_table 
    VALUES (sysdate, :lv_user, :lv_action)
  ENDEXEC.
  
  IF sy-subrc <> 0.
    RAISE EXCEPTION TYPE cx_sy_native_sql_error.
  ENDIF.
  
CATCH cx_sy_native_sql_error INTO DATA(lx_error).
  MESSAGE ID lx_error->sql_error 
  TYPE 'E' NUMBER lx_error->sql_error_code. 
ENDTRY. ‌

五、开发规范与安全 ​

安全编码原则 ​

  1. ‌输入验证‌：对动态参数进行白名单过滤
    abap
    IF lv_input CN '0123456789'.
  RAISE DATA_ERROR.
ENDIF.
  2. ‌权限控制‌：限制DDL执行权限至特定程序‌
  3. ‌连接管理‌：显式关闭数据库连接
    abap
    EXEC SQL. DISCONNECT :lv_conn_name ENDEXEC. ‌

性能优化 ​

  1. ‌批量操作‌：使用数组插入代替逐行插入
    abap
    EXEC SQL.
  INSERT ALL
    INTO ztable VALUES (:lt_data-field1, :lt_data-field2)
    INTO ztable VALUES (:lt_data-field1, :lt_data-field2)
  SELECT 1 FROM DUAL
ENDEXEC. ‌
  2. ‌索引策略‌：在频繁查询字段创建索引
    abap
    EXEC SQL.
  CREATE INDEX zidx_field ON ztable (field1, field2)
ENDEXEC. ‌

六、典型案例分析 ​

案例1：跨系统数据同步 ​

abap
" 源系统连接
EXEC SQL.
  CONNECT TO 'SRC_DB' AS src_conn
ENDEXEC.

" 目标系统连接
EXEC SQL.
  CONNECT TO 'DEST_DB' AS dest_conn
ENDEXEC.

" 数据迁移
EXEC SQL AT src_conn.
  SELECT col1, col2 
  INTO :lt_data 
  FROM source_table
ENDEXEC.

EXEC SQL AT dest_conn.
  INSERT INTO dest_table 
  VALUES :lt_data 
  COMMIT
ENDEXEC. ‌

案例2：复杂报表生成 ​

abap
" 使用Oracle分析函数
EXEC SQL.
  SELECT deptno, 
         SUM(sal) OVER (PARTITION BY deptno) AS dept_total,
         RANK() OVER (ORDER BY sal DESC) AS salary_rank
  INTO :lt_report 
  FROM emp 
  WHERE hiredate > '2020-01-01'
ENDEXEC. ‌

‌注意‌：生产环境执行前必须进行SQL执行计划分析‌:ml-citation

Copyright © 2023-present YeYu