11.3Native SQL
实现Native SQL,1.使用ADBC classes;2.静态嵌入Native SQL。
Native SQL缺点:
1.创建的表没注册到R/3系统,在SAP字典中查找不到(se11),只能有Native SQL访问到;
2.没有自动控制MANDT,client字段需要手动处理;
3.动态执行的SQL存在安全隐患;
4.如果是只支持特定数据库的SQL语句,当底层数据库变化后,SQL语句就不再有效;
Native SQL优点:
1.可以根据数据库,使用特定高效的SQL语句;
2.可以访问到不在ABAP系统中的数据,例如:数据库系统表等;
1. ABAP Database Connectivity
使用ADBC,没有自动client handling,操作时,需要明确指定mandt域值。
类:
CL_SQL_STATEMENT:执行SQL,只能相同参数执行一次,不能执行COMMIT,ROLLBACK;
方法:EXECUTE_DDL
执行CREATE,DROP,ALTER等操作;
方法:EXECUTE_UPDATE
执行INSERT,UPDATE,DELETE等操作;
方法:EXECUTE_QUERY
执行查询操作。返回result_set,对应类型CL_SQL_RESULT_SET;
方法:EXECUTE_PROCEDURE
执行procedure;
方法:SET_PARAM
设置参数,每个占位符?都需要执行一次;
方法:SET_PARAM_STRUCT
设置结构,结构中字段数必须和占位符数匹配,只执行一次;
方法:SET_PARAM_TABLE
设置table数据,table结构字段必须和占位符数匹配,只执行一次,只在INSERT,UPDATE,DELETE批量执行操作;
类:
CL_SQL_RESULT_SET:查询返回结果集;
方法:SET_PARAM
对单独行,列数据进行读取;
方法:SET_PARAM_STRUCT
读取行数据;
方法:SET_PARAM_TABLE
读取多行数据;
类:
CL_SQL_PREPARED_STATEMENT
执行SQL,可以传入不同参数,多次执行;
类:
CL_SQL_CONNECTION
连接其他数据库;
方法:GET_CONNECTION
获取数据表DBCAN中配置数据库链接;
方法:COMMIT
提交事务;
方法:ROLLBACK
回滚事务;
类:
CX_SQL_EXCEPTION
SQL异常类;
Attribute | Meaning |
DB_ERROR | "X", if an SQL statement was not executed by DBMS. SQL_CODE and SQL_MESSAGE contain further information in this case. |
DBOBJECT_EXISTS | "X", if you want to create a database object that already exists. DB_ERROR is also "X" in this case. |
DBOBJECT_NOT_EXISTS | "X", if you want to access a database object that does not exist. DB_ERROR is also "X" in this case. |
DUPLICATE_KEY | "X", if a DML statement would violate a unique table key. DB_ERROR is also "X" in this case. |
INTERNAL_ERROR | Internal error code from DBMS. For further troubleshooting, see the log and trace files. |
INVALID_CURSOR | "X", if you want to use an invalid or closed database cursor. |
SQL_CODE | Database-specific error code, if DB_ERROR is "X". |
SQL_MESSAGE | Database-specific error code, if DB_ERROR is "X". |
示例:
"Native SQL
CLASS c_native_sql DEFINITION.
PUBLIC SECTION.
METHODS:constructor.
METHODS:m_create_tab IMPORTING iv_tabname TYPE string RAISING cx_sql_exception.
METHODS:m_insert IMPORTING iv_tabname TYPE string RAISING cx_sql_exception.
METHODS:m_select IMPORTING iv_tabname TYPE string RAISING cx_sql_exception.
METHODS:m_drop_tab IMPORTING iv_tabname TYPE string RAISING cx_sql_exception.
"创建执行procedure
METHODS:m_creat_proc.
METHODS:m_excute_proc.
"使用预定义sql
METHODS:m_prepared_sql.
PRIVATE SECTION.
DATA:lo_sql_statement TYPE REF TO cl_sql_statement.
DATA:lo_sql_prepared_statement TYPE REF TO cl_sql_prepared_statement.
DATA:lo_sql_exception TYPE REF TO cx_sql_exception.
TYPES:BEGIN OF s_tab,
id TYPE N LENGTH 8,
name TYPE C LENGTH 10,
age TYPE I,
address TYPE C LENGTH 20,
zcount TYPE f,
END OF s_tab.
DATA:lt_tab TYPE TABLE OF s_tab.
DATA:ls_tab LIKE LINE OF lt_tab.
ENDCLASS.
"类实现
CLASS c_native_sql IMPLEMENTATION.
METHOD constructor.
"实例化
CREATE OBJECT lo_sql_statement.
ls_tab-id = 3.
ls_tab-name = 'lili'.
ls_tab-age = 10.
ls_tab-address = '四川'.
ls_tab-zcount = '2023'.
APPEND ls_tab TO lt_tab.
ls_tab-id = 4.
ls_tab-name = 'lucy'.
ls_tab-age = 22.
ls_tab-address = 'jack'.
ls_tab-zcount = '1203.33'.
APPEND ls_tab TO lt_tab.
ENDMETHOD.
"创建表
METHOD m_create_tab.
DATA:lv_statement TYPE string.
"对应数据类型?
"nvarchar,string,date,time,fltp,f不支持?
"不知道支持哪些数据类型???
"创建表
lv_statement = `CREATE TABLE `
&& iv_tabname
&& `( id char(8) NOT NULL,`
&& ` name varchar(10) NOT NULL,`
&& ` age int,`
&& ` address varchar(20),`
&& ` zcount float,`
* && ` zdesc string,`
* && ` creat_date date,`
* && ` creat_time time,`
&& ` PRIMARY KEY (id) )`.
lo_sql_statement->execute_ddl( lv_statement ).
ENDMETHOD.
"插入语句
METHOD m_insert.
DATA:lv_statement TYPE string.
DATA:lv_rows TYPE I.
DATA:lv_count TYPE I.
DATA:lv_ref TYPE REF TO DATA.
"循环插入
* LOOP AT lt_tab INTO ls_tab.
* CLEAR lv_statement.
* "插入语句
* lv_statement = `INSERT INTO ` && iv_tabname
* && ` VALUES ('` && ls_tab-id && `','`
* && ls_tab-name && `',`
* && ls_tab-age && `,'`
* && ls_tab-address && `','`
* && ls_tab-zcount && `')`.
* lv_rows = lo_sql_statement->execute_update( lv_statement ).
* lv_count = lv_count + lv_rows.
* ENDLOOP.
* WRITE:/ lv_count.
"绑定参数方式插入
CLEAR lv_statement.
"插入语句
lv_statement = `INSERT INTO ` && iv_tabname
&& ` VALUES (?,?,?,?,?)`.
"设置插入参数
GET REFERENCE OF lt_tab INTO lv_ref.
lo_sql_statement->set_param_table( lv_ref ).
lv_rows = lo_sql_statement->execute_update( lv_statement ).
ENDMETHOD.
"查询语句
METHOD m_select.
DATA:lo_result TYPE REF TO cl_sql_result_set.
DATA:lv_statement TYPE string.
DATA:lt_table TYPE TABLE OF s_tab.
DATA:ls_table LIKE LINE OF lt_table.
DATA:lv_ref TYPE REF TO DATA.
DATA:lv_val TYPE I.
lv_statement = `SELECT COUNT(*) AS counts FROM ` && iv_tabname.
"执行查询
lo_result = lo_sql_statement->execute_query( lv_statement ).
GET REFERENCE OF lv_val INTO lv_ref.
lo_result->set_param( EXPORTING data_ref = lv_ref ).
"返回单笔记录
lo_result->next( ).
WRITE:/ lv_val.
"执行查询
lv_statement = `SELECT * FROM ` && iv_tabname.
lo_result = lo_sql_statement->execute_query( lv_statement ).
GET REFERENCE OF lt_table INTO lv_ref.
lo_result->set_param_table( EXPORTING itab_ref = lv_ref ).
"返回多笔记录
lo_result->next_package( ).
LOOP AT lt_table INTO ls_table.
WRITE:/ ls_table-name,ls_table-age,ls_table-address,ls_table-zcount.
ENDLOOP.
"参数绑定使用占位符
DATA:lv_address TYPE C LENGTH 20 VALUE '重庆'.
DATA:lv_age TYPE I VALUE 20.
CLEAR lt_table[].
lv_statement = `SELECT * FROM ` && iv_tabname
&& ` WHERE address = ? AND age > ?`.
"绑定参数,每个?占位符
GET REFERENCE OF lv_address INTO lv_ref.
lo_sql_statement->set_param( lv_ref ).
GET REFERENCE OF lv_age INTO lv_ref.
lo_sql_statement->set_param( lv_ref ).
"查询结果
lo_result = lo_sql_statement->execute_query( lv_statement ).
GET REFERENCE OF lt_table INTO lv_ref.
lo_result->set_param_table( EXPORTING itab_ref = lv_ref ).
lo_result->next_package( ).
LOOP AT lt_table INTO ls_table.
WRITE:/ ls_table-name,ls_table-age,ls_table-address,ls_table-zcount.
ENDLOOP.
ENDMETHOD.
METHOD m_drop_tab.
DATA:lv_statement TYPE string.
"删除表
lv_statement = `DROP TABLE ` && iv_tabname.
lo_sql_statement->execute_ddl( lv_statement ).
ENDMETHOD.
"创建procedure
METHOD m_creat_proc.
DATA:lv_statement TYPE string.
DATA:lv_inc TYPE P LENGTH 7 DECIMALS 2.
DATA:lv_ref TYPE REF TO DATA.
lv_statement = `CREATE PROCEDURE `
&& `ZTOM_TEST_PROC(IN inc DECIMAL(15,2)) AS BEGIN `
&& `UPDATE sflight SET price = price + :inc;`
&& `END`.
GET REFERENCE OF lv_inc INTO lv_ref.
lo_sql_statement->set_param(
data_ref = lv_ref
inout = cl_sql_statement=>c_param_in ).
lo_sql_statement->execute_ddl( lv_statement ).
ENDMETHOD.
"执行procedure
METHOD m_excute_proc.
DATA:lv_rows TYPE I.
lv_rows = lo_sql_statement->execute_procedure( proc_name = 'ZTOM_TEST_PROC' ).
ENDMETHOD.
"执行预定义SQL
METHOD m_prepared_sql.
DATA:lv_statement TYPE string.
DATA:lt_scarr TYPE TABLE OF scarr.
DATA:ls_scarr LIKE LINE OF lt_scarr.
DATA:lt_carrid TYPE TABLE OF scarr-carrid.
DATA:lv_carrid TYPE scarr-carrid.
DATA:lv_ref TYPE REF TO DATA.
"结果
DATA:lo_result TYPE REF TO cl_sql_result_set.
lv_carrid = 'AA'.
APPEND lv_carrid TO lt_carrid.
lv_carrid = 'AB'.
APPEND lv_carrid TO lt_carrid.
lv_statement = 'SELECT * FROM SCARR WHERE CARRID = ?'.
CREATE OBJECT lo_sql_prepared_statement
EXPORTING
statement = lv_statement.
"获取参数
GET REFERENCE OF lv_carrid INTO lv_ref.
lo_sql_prepared_statement->set_param( lv_ref ).
LOOP AT lt_carrid INTO lv_carrid.
lo_result = lo_sql_prepared_statement->execute_query( ).
GET REFERENCE OF lt_scarr INTO lv_ref.
lo_result->set_param_table( EXPORTING itab_ref = lv_ref ).
lo_result->next_package( ).
LOOP AT lt_scarr INTO ls_scarr.
WRITE:/ ls_scarr-carrid,ls_scarr-carrname,ls_scarr-url.
ENDLOOP.
ENDLOOP.
ENDMETHOD.
ENDCLASS.
2.Static Embedding Native SQL
静态嵌入Native SQL语句。
语法:
EXEC SQL.
ENDEXEC.
示例:
"嵌入sql
FORM f_embeded_sql.
DATA:lo_exception TYPE REF TO cx_root.
DATA: p_carrid TYPE spfli-carrid VALUE 'AA',
p_connid TYPE spfli-connid VALUE '0017'.
TYPES: BEGIN OF s_wa,
cityfrom TYPE spfli-cityfrom,
cityto TYPE spfli-cityto,
END OF s_wa.
DATA:lt_wa TYPE TABLE OF s_wa.
DATA:ls_wa LIKE LINE OF lt_wa.
DATA:lt_spfli TYPE TABLE OF spfli.
DATA:ls_spfli LIKE LINE OF lt_spfli.
DATA:lv_carrid TYPE spfli-carrid.
DATA:lv_connid TYPE spfli-connid.
TRY.
"执行sql,使用:使用本地或全局变量
EXEC SQL.
SELECT cityfrom, cityto
INTO STRUCTURE :ls_wa
FROM spfli
WHERE mandt = :sy-mandt
AND carrid = :p_carrid
AND connid = :p_connid
ENDEXEC.
WRITE:/ ls_wa-cityfrom,ls_wa-cityto.
"使用cursor
"游标
DATA:lv_c1 TYPE cursor.
EXEC SQL.
OPEN lv_c1 FOR SELECT * FROM spfli
ENDEXEC.
DO.
EXEC SQL.
FETCH NEXT lv_c1 INTO :lv_carrid, :lv_connid
ENDEXEC.
IF sy-subrc <> 0.
EXIT.
ELSE.
WRITE:/ lv_carrid,lv_connid.
ENDIF.
ENDDO.
"关闭游标
EXEC SQL.
CLOSE lv_c1
ENDEXEC.
"创建procedure
DATA:incprice TYPE P LENGTH 8 DECIMALS 2 VALUE '-10.00'.
"创建procedure
EXEC SQL.
CREATE OR REPLACE PROCEDURE update_price (x IN NUMBER) IS
BEGIN
UPDATE sflight SET price = price + x WHERE carrid = 'AA' AND connid = '0017';
END;
ENDEXEC.
"执行procedure
EXEC SQL.
EXECUTE PROCEDURE update_price ( IN :incprice )
ENDEXEC.
"删除procedure
EXEC SQL.
DROP PROCEDURE update_price
ENDEXEC.
"创建function,有问题
DATA:lv_scarrname TYPE C LENGTH 20.
DATA:lv_scarrid TYPE C LENGTH 3 VALUE 'AA'.
"创建function
EXEC SQL.
CREATE FUNCTION func1( input CHAR(3) )
RETURNING char(20);
DEFINE output char(20);
SELECT carrname
INTO output
FROM scarr
WHERE carrid = input;
RETURN output;
END FUNCTION;
ENDEXEC.
"执行function
EXEC SQL.
EXECUTE PROCEDURE func1( IN :lv_scarrid, OUT :lv_scarrname )
ENDEXEC.
WRITE:/ lv_scarrname.
"删除function
EXEC SQL.
DROP FUNCTION func1
ENDEXEC.
CATCH cx_root INTO lo_exception.
DATA:lv_string TYPE string.
lv_string = lo_exception->get_text( ).
MESSAGE lv_string TYPE 'E'.
ENDTRY.
ENDFORM.
3.Object Service
使用面向对象框架,处理关系数据库。
1.Persistence service,持久层服务
2.Transaction service,传输层服务
3.Query service,查询层服务
持久层类:
创建持久记录,示例:DEMO_CREATE_PERSISTENT。
CL_persistent:创建持久层类型,示例持久类:CL_SPFLI_PERSISTENT。
CA_persistent:类代理,示例:CA_APFLI_PERSISTENT。
CB_persistent:所有actor的父类,例如:CL_OS_CA_COMMON
类方法:
CREATE_PERSISTENT:创建持久对象。不会判断是否持久对象已经创建,使用GET_PERSISTENT判断是否对象已经创建;
GET_PERSISTENT:获取已经存在持久对象;
DELETE_PERSISTENT:删除持久对象;
CREATE_TRANSIENT:生成持久对象的临时对象;
GET_TRANSIENT:获取持久对象的临时对象;
持久类代理接口:
IF_OS_FACTORY:
接口方法:
IF_OS_FACTORY~CREATE_PERSISTENT:创建持久对象。
IF_OS_FACTORY~CREATE_PERSISTENT_BY_KEY:创建持久对象,通过GUID key;
IF_OS_FACTORY~REFRESH_PERSISTENT:强制系统将持久对象的当前属性从数据库加载到ABAP程序中;
IF_OS_FACTORY~DELETE_PERSISTENT:删除持久对象;
IF_OS_FACTORY~CREATE_TRANSIENT;
IF_OS_FACTORY~CREATE_TRANSIENT_BY_KEY;
IF_OS_FACTORY~RELEASE:从持久性服务的管理中完全删除由类代理管理的对象;
IF_OS_CA_PERSISTENCY:
接口方法:
IF_OS_CA_PERSISTENCY~GET_PERSISTENT_BY_OID;
IF_OS_CA_PERSISTENCY~GET_PERSISTENT_BY_KEY;
IF_OS_CA_PERSISTENCY~GET_PERSISTENT_BY_OID_TAB;
IF_OS_CA_PERSISTENCY~GET_PERSISTENT_BY_KEY_TAB;
IF_OS_CA_PERSISTENCY~GET_PERSISTENT_BY_QUERY;
IF_OS_CA_INSTANCE:
接口方法:
IF_OS_CA_INSTANCE~GET_STATUS:获取托管对象的管理状态,状态代码Type Group: OSCON,结构OSTATUS_xxx;
IF_OS_CA_INSTANCE~GET_NOT_LOADED;
IF_OS_CA_INSTANCE~GET_CREATED;
IF_OS_CA_INSTANCE~GET_LOADED;
IF_OS_CA_INSTANCE~GET_CHANGED;
IF_OS_CA_INSTANCE~GET_DELETED;
IF_OS_CA_INSTANCE~TRANSIENT;
状态代码:
OSCON_OSTATUS_NOT_LOADED | 0 | Representative object for a persistent object, data not yet loaded from the database. |
OSCON_OSTATUS_NEW | 1 | The object is persistent and is newly created. |
OSCON_OSTATUS_LOADED | 2 | The object is persistent and has been loaded completely. |
OSCON_OSTATUS_CHANGED | 3 | The object is persistent and has been changed. |
OSCON_OSTATUS_DELETED | 4 | The object is persistent and has been deleted. |
OSCON_OSTATUS_TRANSIENT | 10 | The object is transient. |
OSCON_OSTATUS_LOADING | 12 | The object is being loaded at this moment. |
持久类管理接口:
IF_OS_STATE
接口方法:
IF_OS_STATE~INIT;
IF_OS_STATE~INVALIDATE;
IF_OS_STATE~HANDLE_EXCEPTION;
IF_OS_STATE~GET and IF_OS_STATE~SET;
传输层类:
用来更新数据库记录,示例:DEMO_TRANSACTION_SERVICE。使用系统类CL_OS_SYSTEM,调用GET_TRANSACTION_MANAGER方法,
使用接口IF_OS_TRANSACTION_MANAGER and IF_OS_TRANSACTION,访问事务管理器和事务。
CL_OS_SYSTEM:
类方法:
INIT_AND_SET_MODES:初始化对象服务,创建服务对象,只执行一次;
指定可能的模式:
OSCON_DMODE_DEFAULT, OSCON_DMODE_UPDATE_TASK, OSCON_DMODE_UPDATE_TASK_SYNC and OSCON_DMODE_LOCAL for local updates
OSCON_DMODE_DIRECT for saving directly;
GET_TRANSACTION_MANAGER:返回事务管理器,接口类型:IF_OS_QUERY_MANAGER;
接口IF_OS_QUERY_MANAGER方法:
IF_OS_TRANSACTION_MANAGER~CREATE_TRANSACTION:返回事务接口类型:IF_OS_TRANSACTION;
IF_OS_TRANSACTION_MANAGER~GET_CURRENT_TRANSACTION:返回当前事务;
IF_OS_TRANSACTION_MANAGER~GET_TOP_TRANSACTION:返回最高等级事务;
接口IF_OS_TRANSACTION方法:
IF_OS_TRANSACTION~START:执行事务,没有其他事务,等级变成top;
IF_OS_TRANSACTION~END:停止事务;
IF_OS_TRANSACTION~UNDO;
IF_OS_TRANSACTION~END_AND_CHAIN;
IF_OS_TRANSACTION~UNDO_AND_CHAIN;
IF_OS_TRANSACTION~REGISTER_CHECK_AGENT;
IF_OS_TRANSACTION~GET_STATUS;
IF_OS_TRANSACTION~SET_MODE_UNDO_RELEVANT;
IF_OS_TRANSACTION~SET_MODE_UPDATE;
IF_OS_TRANSACTION~GET_MODES;
IF_OS_TRANSACTION~SAVE_REQUESTED;
IF_OS_TRANSACTION~SAVE_PREPARED;
IF_OS_TRANSACTION~FINISHED;
查询层类:
通过逻辑表达式查询持久层对象,示例:DEMO_QUERY_SERVICE。通过CL_OS_SYSTEM类方法GET_QUERY_MANAGER获取查询管理器。IF_OS_QUERY_MANAGER, IF_OS_QUERY, and IF_OS_QUERY_FACTORY等接口操作查询管理器。
CL_OS_SYSTEM类方法:GET_QUERY_MANAGER返回查询管理器,类型IF_OS_QUERY_MANAGER;
IF_OS_QUERY_MANAGER:
接口方法:
IF_OS_QUERY_MANAGER~CREATE_QUERY;
IF_OS_QUERY:
接口方法:
IF_OS_QUERY~GET_EXPR_FACTORY,返回IF_OS_QUERY_EXPR_FACTORY类型;
IF_OS_QUERY~SET_FILTER_EXPR,设置筛选条件逻辑表达式;
IF_OS_QUERY~SET_PARAMETERS_EXPR,设置筛选条件参数列表;
IF_OS_QUERY~SET_ORDERING_EXPR,设置排序规则;
IF_OS_QUERY~PARSE,创建筛选条件,排序等内表显示;
IF_OS_QUERY_EXPR_FACTORY:
接口方法:
IF_OS_QUERY_EXPR_FACTORY~CREATE_OPERATOR_EXPR:创建比较条件,参数:I_ATTR, I_OPERATOR and I_ATTR2.。
IF_OS_QUERY_EXPR_FACTORY~CREATE_LIKE_EXPR:创建like条件,参数:I_ATTR, I_PATTERN,I_NOT;
IF_OS_QUERY_EXPR_FACTORY~CREATE_ISNULL_EXPR:创建是否为空条件,参数:I_ATTR,I_NOT(是否为空);
IF_OS_QUERY_EXPR_FACTORY~CREATE_REF_EXPR:创建是否ref相等,参数:I_ATTR,ref使用参数I_IDX指定;
IF_OS_QUERY_EXPR_FACTORY~CREATE_NOT_EXPR:创建not条件;
IF_OS_QUERY_EXPR_FACTORY~CREATE_AND_EXPR:创建AND条件;
IF_OS_QUERY_EXPR_FACTORY~CREATE_OR_EXPR:创建OR条件;
IF_OS_QUERY_EXPR_FACTORY~CREATE_PARAMETERS_EXPR:参数列表,返回IF_OS_QUERY_PARAMETERS_EXPR类型;
IF_OS_QUERY_EXPR_FACTORY~CREATE_ORDERING_EXPR:排序列表,返回
IF_OS_QUERY_ORDERING_EXPR类型;