前言
很多的经典SQL练习题，在SAP系统中没有，这里参考一些SQL练习题，整理出一版简版ABAP版本SQL练习。文末程序可直接复制粘贴到系统中，执行生成练习表及数据。
| 人员编号 | 姓名 | 出生日期 | 性别 |
|---|---|---|---|
| 1 | 赵雷 | 19900101 | 男 |
| 2 | 钱电 | 19901221 | 男 |
| 3 | 孙风 | 19900520 | 男 |
| 4 | 李云 | 19900806 | 男 |
| 5 | 周梅 | 19911201 | 女 |
| 6 | 吴兰 | 19920301 | 女 |
| 7 | 郑竹 | 19890701 | 女 |
| 8 | 王菊 | 19900120 | 女 |
| 9 | 张三 | 19900520 | 男 |
| 10 | 李四 | 19901220 | 女 |
| 11 | 王五 | 19900120 | 男 |
| 课程ID | 课程 | 开始日期 | 结束日期 | 讲师人员编号 |
|---|---|---|---|---|
| 1 | 语文 | 2021.03.01 | 2021.03.31 | 10 |
| 2 | 数学 | 2021.04.01 | 2021.04.30 | 9 |
| 3 | 英语 | 2021.03.01 | 2021.03.01 | 11 |
| 人员编号 | 课程ID | 得分 |
|---|---|---|
| 1 | 1 | 80 |
| 1 | 2 | 90 |
| 1 | 3 | 99 |
| 2 | 1 | 70 |
| 2 | 2 | 60 |
| 2 | 3 | 80 |
| 3 | 1 | 80 |
| 3 | 2 | 80 |
| 3 | 3 | 80 |
| 4 | 1 | 50 |
| 4 | 2 | 30 |
| 4 | 3 | 20 |
| 5 | 1 | 76 |
| 5 | 2 | 87 |
| 6 | 1 | 31 |
| 6 | 3 | 34 |
| 7 | 2 | 89 |
| 7 | 3 | 98 |
-
查询" 01 “课程比” 02 "课程成绩高的人员的信息及课程分数
SELECT u~*,
s1~score AS score_01,
s2~score AS score_02
FROM ylc_train_user AS u
JOIN ylc_train_score AS s1 ON u~pernr = s1~pernr AND s1~c_id = '01'
JOIN ylc_train_score AS s2 ON u~pernr = s2~pernr AND s2~c_id = '02'
INTO TABLE @DATA(lt_sql_01)
WHERE s1~score > s2~score.
*+-------+--------+--------------------+------+----------+----------+
*| PERNR | NAME | 出生日期 | 性别 | score_01 | score_02 |
*+-------+--------+--------------------+------+----------+----------+
*| 02 | 钱电 | 1990-12-21 | 男 | 70.0 | 60.0 |
*| 04 | 李云 | 1990-08-06 | 男 | 50.0 | 30.0 |
*+------+--------+---------------------+------+----------+----------+
- 查询平均成绩大于等于 60 分的人员的人员编号和人员姓名和平均成绩
SELECT u~pernr, name,AVG( score ) AS avg_score
FROM ylc_train_user AS u
JOIN ylc_train_score AS s ON u~pernr = s~pernr
INTO TABLE @DATA(lt_sql_02)
GROUP BY u~pernr , name
HAVING AVG( score ) > 60.
*+------+--------+-----------+
*| PERNR | NAME | avg_score |
*+------+--------+-----------+
*| 01 | 赵雷 | 89.66667 |
*| 02 | 钱电 | 70.00000 |
*| 03 | 孙风 | 80.00000 |
*| 05 | 周梅 | 81.50000 |
*| 07 | 郑竹 | 93.50000 |
*+------+--------+-----------+- 查询存在成绩的人员信息
SELECT *
FROM ylc_train_user
INTO TABLE @DATA(lt_sql_03)
WHERE pernr IN
( SELECT pernr FROM ylc_train_score WHERE score <> 0 ).
*+-------+-------+------------+------+
*| PERNR | NAME | 出生日期 | 性别 |
*+-------+-------+------------+------+
*| 01 | 赵雷 | 1990-01-01 | 男 |
*| 02 | 钱电 | 1990-12-21 | 男 |
*| 03 | 孙风 | 1990-05-20 | 男 |
*| 04 | 李云 | 1990-08-06 | 男 |
*| 05 | 周梅 | 1991-12-01 | 女 |
*| 06 | 吴兰 | 1992-03-01 | 女 |
*| 07 | 郑竹 | 1989-07-01 | 女 |
*+-------+-------+------------+------+- 查询所有人员的编号、姓名、选课总数、所有课程的总成绩(没成绩的显示为 空 )
SELECT u~pernr, u~name, COUNT( DISTINCT s~c_id ) AS count, SUM( score ) AS sumscore
FROM ylc_train_user AS u LEFT JOIN ylc_train_score AS s
ON u~pernr = s~pernr
INTO TABLE @DATA(lt_sql_04)
GROUP BY u~pernr , u~name.
*+-------+--------+--------------+-----------+
*| PERNR | NAME | 选课总数 | 总成绩 |
*+-------+--------+--------------+-----------+
*| 01 | 赵雷 | 3 | 269.0 |
*| 02 | 钱电 | 3 | 210.0 |
*| 03 | 孙风 | 3 | 240.0 |
*| 04 | 李云 | 3 | 100.0 |
*| 05 | 周梅 | 2 | 163.0 |
*| 06 | 吴兰 | 2 | 65.0 |
*| 07 | 郑竹 | 2 | 187.0 |
*| 08 | 王菊 | 0 | |
*+-------+--------+--------------+-----------+- 查询有参与过2021年「张」姓教师的课程的人员信息
SELECT u1~*
FROM ylc_train_course AS c
JOIN ylc_train_user AS u ON u~pernr = c~pernr
JOIN ylc_train_score AS s ON s~c_id = c~c_id
JOIN ylc_train_user AS u1 ON u1~pernr = s~pernr
INTO TABLE @DATA(lt_sql_05)
WHERE u~name LIKE '张%'
AND c~c_bdate >= '20210101'
AND c~c_edate <= '20211231'.
*+-------+--------+-------------------+
*| PERNR | NAME | 出生日期 | 性别 |
*+-------+--------+-------------------+
*| 01 | 赵雷 | 1990-01-01 | 男 |
*| 02 | 钱电 | 1990-12-21 | 男 |
*| 03 | 孙风 | 1990-05-20 | 男 |
*| 04 | 李云 | 1990-08-06 | 男 |
*| 05 | 周梅 | 1991-12-01 | 女 |
*| 07 | 郑竹 | 1989-07-01 | 女 |
*+-------+--------+-------------------+- 查询至少有一门课与编号为" 01 "的人员所学相同的人员的信息
SELECT *
FROM ylc_train_user
INTO TABLE @DATA(lt_sql_06)
WHERE pernr IN
(
SELECT DISTINCT pernr
FROM ylc_train_score
WHERE c_id IN
(
SELECT c_id
FROM ylc_train_score
WHERE pernr = '01'
)
)
.
*+-------+--------+-------------------+
*| PERNR | NAME | 出生日期 | 性别 |
*+-------+--------+-------------------+
*| 01 | 赵雷 | 1990-01-01 | 男 |
*| 02 | 钱电 | 1990-12-21 | 男 |
*| 03 | 孙风 | 1990-05-20 | 男 |
*| 04 | 李云 | 1990-08-06 | 男 |
*| 05 | 周梅 | 1991-12-01 | 女 |
*| 06 | 吴兰 | 1992-03-01 | 女 |
*| 07 | 郑竹 | 1989-07-01 | 女 |
*+-------+--------+-------------------+- 显示所有人员的所有课程的成绩以及平均成绩
SELECT pernr,
SUM( CASE WHEN c_id = '01' THEN score ELSE 0 END ) AS score_01,
SUM( CASE WHEN c_id = '02' THEN score ELSE 0 END ) AS score_02,
SUM( CASE WHEN c_id = '03' THEN score ELSE 0 END ) AS score_03,
AVG( score ) AS score
INTO TABLE @DATA(lt_sql_07)
FROM ylc_train_score AS s
GROUP BY pernr.
*+-------+----------+----------+----------+------------+
*| PERNR | score_01 | score_02 | score_03 | avg(score) |
*+-------+----------+----------+----------+------------+
*| 07 | 0 | 89.0 | 98.0 | 93.50000 |
*| 01 | 80.0 | 90.0 | 99.0 | 89.66667 |
*| 05 | 76.0 | 87.0 | 0 | 81.50000 |
*| 03 | 80.0 | 80.0 | 80.0 | 80.00000 |
*| 02 | 70.0 | 60.0 | 80.0 | 70.00000 |
*| 04 | 50.0 | 30.0 | 20.0 | 33.33333 |
*| 06 | 31.0 | 0 | 34.0 | 32.50000 |
*+-------+----------+----------+----------+------------+- 查询 1990 年出生的人员名单
SELECT *
FROM ylc_train_user
WHERE substring( gbdat , 1 , 4 ) = '1990'
INTO TABLE @DATA(lt_sql_08).
*+-------+--------+------------+------+
*| PERNR | NAME | 出生日期 | 性别 |
*+-------+--------+------------+------+
*| 01 | 赵雷 | 1990-01-01 | 男 |
*| 02 | 钱电 | 1990-12-21 | 男 |
*| 03 | 孙风 | 1990-05-20 | 男 |
*| 04 | 李云 | 1990-08-06 | 男 |
*| 08 | 王菊 | 1990-01-20 | 女 |
*+-------+--------+------------+------+参考SQL练习地址：https://blog.csdn.net/paul0127/article/details/82529216
SQL练习表及数据生成程序：
*&---------------------------------------------------------------------*
*& Report YSQL_GENERATE_DEMO
*&---------------------------------------------------------------------*
*& Transaction code :
*& Program Name : YSQL_GENERATE_DEMO
*& Created on :
*& Functional Consultant :
*& Developer :SilentFirework
*&---------------------------------------------------------------------*
*& Purpose: 自动生成练习数据
*&---------------------------------------------------------------------*
*& Change Record (new entries to the bottom)
*& Date Developer Transport Descriptions
*& ========== ======== =========== =================================*
*&---------------------------------------------------------------------*
REPORT ysql_generate_demo.
*&---------------------------------------------------------------------*
* Type Definition
*&---------------------------------------------------------------------*
TYPES: BEGIN OF ty_ydemo_tr_user,"人员信息表
mandt TYPE mandt,
pernr TYPE pernr_d,
name TYPE emnam,
gbdat TYPE gbdat,
gesch TYPE gesch,
END OF ty_ydemo_tr_user.
TYPES: BEGIN OF ty_ydemo_tr_course,"课程信息表
mandt TYPE mandt,
c_id TYPE char10,
c_name TYPE text80,
c_bdate TYPE bapi_bkk_dte_validfrom,
c_edate TYPE bapi_bkk_dte_validto,
pernr TYPE pernr_d,
END OF ty_ydemo_tr_course.
TYPES: BEGIN OF ty_ydemo_tr_score,"成绩信息表
mandt TYPE mandt,
pernr TYPE pernr_d,
c_id TYPE char10,
score TYPE hrpdv_sem_score,
END OF ty_ydemo_tr_score.
*&---------------------------------------------------------------------*
* Data Definition
*&---------------------------------------------------------------------*
DATA: gr_dtel TYPE RANGE OF rollname, "系统已存在数据元素
gr_tabl TYPE RANGE OF tabname. "系统已存在表
DATA: gt_act_object TYPE TABLE OF dwinactiv."成功创建的对象-激活时使用
CONSTANTS: c_ydemo_tr_user TYPE tabname VALUE 'YDEMO_TR_USER',
c_ydemo_tr_score TYPE tabname VALUE 'YDEMO_TR_SCORE',
c_ydemo_tr_course TYPE tabname VALUE 'YDEMO_TR_COURSE',
c_yde_c_id TYPE rollname VALUE 'YDE_C_ID',
c_yde_c_name TYPE rollname VALUE 'YDE_C_NAME',
c_yde_t_id TYPE rollname VALUE 'YDE_T_ID'.
*&---------------------------------------------------------------------*
* Selection Screen definition
*&---------------------------------------------------------------------*
SELECTION-SCREEN: BEGIN OF BLOCK b1 WITH FRAME.
SELECTION-SCREEN: BEGIN OF LINE.
PARAMETERS p_node TYPE char1 RADIOBUTTON GROUP rg1 DEFAULT 'X'."校验
SELECTION-SCREEN COMMENT 6(20) p_nodet FOR FIELD p_node.
SELECTION-SCREEN: END OF LINE.
SELECTION-SCREEN: BEGIN OF LINE.
PARAMETERS p_ovri TYPE char1 RADIOBUTTON GROUP rg1."是否覆盖
SELECTION-SCREEN COMMENT 6(20) p_ovrit FOR FIELD p_ovri.
SELECTION-SCREEN: END OF LINE.
SELECTION-SCREEN: BEGIN OF LINE.
PARAMETERS p_jump TYPE char1 RADIOBUTTON GROUP rg1."跳过
SELECTION-SCREEN COMMENT 6(20) p_jumpt FOR FIELD p_jump.
SELECTION-SCREEN: END OF LINE.
SELECTION-SCREEN: END OF BLOCK b1.
*Activate the selection screen button
*&---------------------------------------------------------------------*
* Initialization
*&---------------------------------------------------------------------*
INITIALIZATION.
p_nodet = '直接执行'.
p_ovrit = '覆盖已有对象'.
p_jumpt = '跳过已有对象'.
AT SELECTION-SCREEN.
PERFORM check_exist.
*&---------------------------------------------------------------------*
* start of selection
*&---------------------------------------------------------------------*
START-OF-SELECTION.
CLEAR: gt_act_object.
PERFORM create_element.
PERFORM create_table.
PERFORM activate_object.
PERFORM generate_data.
*&---------------------------------------------------------------------*
*& Form CHECK_EXIST
*&---------------------------------------------------------------------*
* 校验表及数据元素是否已存在
*----------------------------------------------------------------------*
* --> p1 text
* <-- p2 text
*----------------------------------------------------------------------*
FORM check_exist .
DATA: lv_tabname TYPE tabname.
DEFINE mcr_check_tab.
SELECT SINGLE tabname
FROM dd02l
INTO lv_tabname
WHERE tabname = &1.
IF sy-subrc = 0 AND p_ovri IS INITIAL AND p_jump IS INITIAL.
MESSAGE &1 && '表已存在' TYPE 'E' .
ELSEIF sy-subrc = 0.
APPEND VALUE #( sign = 'I' option = 'EQ' low = &1 ) TO gr_tabl.
ENDIF.
END-OF-DEFINITION.
DEFINE mcr_check_element.
SELECT COUNT(*)
FROM dd04l
UP TO 1 ROWS
WHERE rollname = &1 .
IF sy-dbcnt <> 0 AND p_ovri IS INITIAL AND p_jump IS INITIAL.
MESSAGE &1 && '数据元素已存在' TYPE 'E' .
ELSEIF sy-dbcnt <> 0.
APPEND VALUE #( sign = 'I' option = 'EQ' low = &1 ) TO gr_dtel.
ENDIF.
END-OF-DEFINITION.
"表是否已存在
mcr_check_tab: c_ydemo_tr_user,
c_ydemo_tr_score,
c_ydemo_tr_course.
"数据元素是否已存在
mcr_check_element: c_yde_c_id,
c_yde_c_name,
c_yde_t_id.
ENDFORM.
*&---------------------------------------------------------------------*
*& Form CREATE_ELEMENT
*&---------------------------------------------------------------------*
* 创建数据元素
*----------------------------------------------------------------------*
* --> p1 text
* <-- p2 text
*----------------------------------------------------------------------*
FORM create_element .
DATA: lt_dd04v TYPE TABLE OF dd04v,
ls_dd04v TYPE dd04v.
DATA: lv_object TYPE string,
lv_object_class TYPE string,
lv_package TYPE devclass.
DATA: lv_message TYPE string.
DATA lv_rc TYPE sy-subrc.
*&------1. 数据元素对应参数赋值
lt_dd04v = VALUE #( ( rollname = c_yde_c_id ddlanguage = sy-langu domname = 'CHAR10'
ddtext = '课程ID' reptext = '课程ID' scrtext_l = '课程ID'
scrtext_m = '课程ID' scrtext_s = '课程ID' dtelmaster = sy-langu
headlen = 20 scrlen1 = 10 scrlen2 = 20 scrlen3 = 40 )
( rollname = c_yde_c_name ddlanguage = sy-langu domname = 'TEXT80'
ddtext = '课程名称' reptext = '课程名称' scrtext_l = '课程名称'
scrtext_m = '课程名称' scrtext_s = '课程名称' dtelmaster = sy-langu
headlen = 20 scrlen1 = 10 scrlen2 = 20 scrlen3 = 40 )
( rollname = c_yde_t_id ddlanguage = sy-langu domname = 'PERSNO'
ddtext = '培训讲师ID' reptext = '培训讲师ID' scrtext_l = '培训讲师ID'
scrtext_m = '培训讲师ID' scrtext_s = '培训讲师ID' dtelmaster = sy-langu
headlen = 20 scrlen1 = 10 scrlen2 = 20 scrlen3 = 40 )
).
*&------2. 特殊处理
IF p_jump = 'X' AND gr_dtel IS NOT INITIAL."是否跳过已存在对象
DELETE lt_dd04v WHERE rollname IN gr_dtel.
ENDIF.
*&------3. 写入数据字典
LOOP AT lt_dd04v INTO ls_dd04v.
"为开发对象分配包
PERFORM assign_pakage USING 'DTEL' ls_dd04v-rollname CHANGING lv_rc lv_message.
IF lv_rc <> 0.
WRITE:/ '数据元素：' , ls_dd04v-rollname , '分配包失败:' , lv_message.
ELSE.
"将数据元素写入数据字段
CALL FUNCTION 'DDIF_DTEL_PUT'
EXPORTING
name = ls_dd04v-rollname
dd04v_wa = ls_dd04v
EXCEPTIONS
dtel_not_found = 1
name_inconsistent = 2
dtel_inconsistent = 3
put_failure = 4
put_refused = 5
OTHERS = 6.
IF sy-subrc <> 0.
MESSAGE ID sy-msgid TYPE 'E' NUMBER sy-msgno
WITH sy-msgv1 sy-msgv2 sy-msgv3 sy-msgv4 INTO lv_message.
WRITE:/ '数据元素：' , ls_dd04v-rollname , '创建失败:' , lv_message.
ELSE.
WRITE:/ '数据元素：' , ls_dd04v-rollname , '创建成功'.
APPEND VALUE #( object = 'DTEL' obj_name = ls_dd04v-rollname uname = sy-uname ) TO gt_act_object.
ENDIF.
ENDIF.
ENDLOOP.
ENDFORM.
*&---------------------------------------------------------------------*
*& Form CREATE_TABLE
*&---------------------------------------------------------------------*
* 创建数据库表
*----------------------------------------------------------------------*
* --> p1 text
* <-- p2 text
*----------------------------------------------------------------------*
FORM create_table.
DATA ls_dd02v TYPE dd02v. "
DATA ls_dd09l TYPE dd09l. "
DATA ls_dd03p TYPE dd03p. "
DATA lt_dd03p TYPE rsos_t_dd03p. "
"表定义
ls_dd02v = VALUE #( tabname = c_ydemo_tr_course ddlanguage = sy-langu tabclass = 'TRANSP'
clidep = 'X' ddtext = '课程信息表' contflag = 'A' ).
"技术信息
ls_dd09l = VALUE #( tabname = c_ydemo_tr_course tabart = 'APPL0' bufallow = 'N'
roworcolst = 'C' tabkat = '0' ).
"表字段
lt_dd03p = VALUE #( ( tabname = c_ydemo_tr_course fieldname = 'MANDT' position = '1'
keyflag = 'X' rollname = 'MANDT' notnull = 'X' comptype = 'E' )
( tabname = c_ydemo_tr_course fieldname = 'C_ID' position = '2'
keyflag = 'X' rollname = 'YDE_C_ID' notnull = 'X' comptype = 'E' )
( tabname = c_ydemo_tr_course fieldname = 'C_NAME' position = '3'
keyflag = ' ' rollname = 'YDE_C_NAME' notnull = ' ' comptype = 'E' )
( tabname = c_ydemo_tr_course fieldname = 'C_BDATE' position = '4'
keyflag = ' ' rollname = 'BAPI_BKK_DTE_VALIDFROM' notnull = ' ' comptype = 'E' )
( tabname = c_ydemo_tr_course fieldname = 'C_EDATE' position = '5'
keyflag = ' ' rollname = 'BAPI_BKK_DTE_VALIDTO' notnull = ' ' comptype = 'E' )
( tabname = c_ydemo_tr_course fieldname = 'PERNR' position = '6'
keyflag = ' ' rollname = 'YDE_T_ID' notnull = ' ' comptype = 'E' )
).
"是否跳过已存在对象
IF p_jump <> 'X' OR ls_dd02v-tabname NOT IN gr_tabl.
PERFORM create_table_sigle USING ls_dd02v ls_dd09l lt_dd03p.
ENDIF.
ls_dd02v = VALUE #( tabname = c_ydemo_tr_score ddlanguage = sy-langu tabclass = 'TRANSP'
clidep = 'X' ddtext = '成绩信息表' contflag = 'A' ).
ls_dd09l = VALUE #( tabname = c_ydemo_tr_score tabart = 'APPL1' bufallow = 'N'
roworcolst = 'C' tabkat = '0' ).
lt_dd03p = VALUE #( ( tabname = c_ydemo_tr_score fieldname = 'MANDT' position = '1'
keyflag = 'X' rollname = 'MANDT' notnull = 'X' comptype = 'E' )
( tabname = c_ydemo_tr_score fieldname = 'PERNR' position = '2'
keyflag = 'X' rollname = 'PERNR_D' notnull = 'X' comptype = 'E' )
( tabname = c_ydemo_tr_score fieldname = 'C_ID' position = '3'
keyflag = 'X' rollname = 'YDE_C_ID' notnull = 'X' comptype = 'E' )
( tabname = c_ydemo_tr_score fieldname = 'SCORE' position = '4'
keyflag = ' ' rollname = 'HRPDV_SEM_SCORE' notnull = ' ' comptype = 'E' )
).
IF p_jump <> 'X' OR ls_dd02v-tabname NOT IN gr_tabl.
PERFORM create_table_sigle USING ls_dd02v ls_dd09l lt_dd03p.
ENDIF.
ls_dd02v = VALUE #( tabname = c_ydemo_tr_user ddlanguage = sy-langu tabclass = 'TRANSP'
clidep = 'X' ddtext = '人员信息表' contflag = 'A' ).
ls_dd09l = VALUE #( tabname = c_ydemo_tr_user tabart = 'APPL0' bufallow = 'N'
roworcolst = 'C' tabkat = '0' ).
lt_dd03p = VALUE #( ( tabname = c_ydemo_tr_user fieldname = 'MANDT' position = '1'
keyflag = 'X' rollname = 'MANDT' notnull = 'X' comptype = 'E' )
( tabname = c_ydemo_tr_user fieldname = 'PERNR' position = '2'
keyflag = 'X' rollname = 'PERNR_D' notnull = 'X' comptype = 'E' )
( tabname = c_ydemo_tr_user fieldname = 'NAME' position = '3'
keyflag = ' ' rollname = 'EMNAM' notnull = ' ' comptype = 'E' )
( tabname = c_ydemo_tr_user fieldname = 'GBDAT' position = '4'
keyflag = ' ' rollname = 'GBDAT' notnull = ' ' comptype = 'E' )
( tabname = c_ydemo_tr_user fieldname = 'GESCH' position = '5'
keyflag = ' ' rollname = 'GESCH' notnull = ' ' comptype = 'E' )
).
IF p_jump <> 'X' OR ls_dd02v-tabname NOT IN gr_tabl.
PERFORM create_table_sigle USING ls_dd02v ls_dd09l lt_dd03p.
ENDIF.
COMMIT WORK AND WAIT.
ENDFORM.
*&---------------------------------------------------------------------*
*& Form GENERATE_DATA
*&---------------------------------------------------------------------*
* text
*----------------------------------------------------------------------*
* --> p1 text
* <-- p2 text
*----------------------------------------------------------------------*
FORM generate_data .
DATA: lt_ydemo_tr_user TYPE TABLE OF ty_ydemo_tr_user,
lt_ydemo_tr_score TYPE TABLE OF ty_ydemo_tr_score,
lt_ydemo_tr_course TYPE TABLE OF ty_ydemo_tr_course.
lt_ydemo_tr_user = VALUE #(
( pernr = 01 name = '赵雷' gbdat = '19900101' gesch = '男' )
( pernr = 02 name = '钱电' gbdat = '19901221' gesch = '男' )
( pernr = 03 name = '孙风' gbdat = '19900520' gesch = '男' )
( pernr = 04 name = '李云' gbdat = '19900806' gesch = '男' )
( pernr = 05 name = '周梅' gbdat = '19911201' gesch = '女' )
( pernr = 06 name = '吴兰' gbdat = '19920301' gesch = '女' )
( pernr = 07 name = '郑竹' gbdat = '19890701' gesch = '女' )
( pernr = 08 name = '王菊' gbdat = '19900120' gesch = '女' )
( pernr = 09 name = '张三' gbdat = '19900520' gesch = '男' )
( pernr = 10 name = '李四' gbdat = '19901220' gesch = '女' )
( pernr = 11 name = '王五' gbdat = '19900120' gesch = '男' )
).
lt_ydemo_tr_score = VALUE #(
( pernr = 01 c_id = '01' score = 80 )
( pernr = 01 c_id = '02' score = 90 )
( pernr = 01 c_id = '03' score = 99 )
( pernr = 02 c_id = '01' score = 70 )
( pernr = 02 c_id = '02' score = 60 )
( pernr = 02 c_id = '03' score = 80 )
( pernr = 03 c_id = '01' score = 80 )
( pernr = 03 c_id = '02' score = 80 )
( pernr = 03 c_id = '03' score = 80 )
( pernr = 04 c_id = '01' score = 50 )
( pernr = 04 c_id = '02' score = 30 )
( pernr = 04 c_id = '03' score = 20 )
( pernr = 05 c_id = '01' score = 76 )
( pernr = 05 c_id = '02' score = 87 )
( pernr = 06 c_id = '01' score = 31 )
( pernr = 06 c_id = '03' score = 34 )
( pernr = 07 c_id = '02' score = 89 )
( pernr = 07 c_id = '03' score = 98 )
).
lt_ydemo_tr_course = VALUE #(
( c_id = '01' c_name = '语文' c_bdate = '20210301'
c_edate = '20210331' pernr = 10 )
( c_id = '02' c_name = '数学' c_bdate = '20210401'
c_edate = '20210430' pernr = 09 )
( c_id = '03' c_name = '英语' c_bdate = '20210301'
c_edate = '20210301' pernr = 11 )
).
CLEAR:gr_tabl, gr_dtel.
PERFORM check_exist.
IF c_ydemo_tr_user IN gr_tabl AND c_ydemo_tr_user IN gr_tabl
AND c_ydemo_tr_course IN gr_tabl AND gr_tabl IS NOT INITIAL.
SELECT COUNT( * )
FROM dd02l
WHERE tabname IN gr_tabl
AND as4local = 'A'
AND as4vers = 0000.
IF sy-dbcnt <> 3."存在表未激活
WRITE:/ '数据生成：数据表未激活，demo数据无法生成'.
ELSE.
MODIFY (c_ydemo_tr_user) FROM TABLE lt_ydemo_tr_user.
MODIFY (c_ydemo_tr_score) FROM TABLE lt_ydemo_tr_score.
MODIFY (c_ydemo_tr_course) FROM TABLE lt_ydemo_tr_course.
IF sy-subrc = 0.
COMMIT WORK AND WAIT.
WRITE:/ '数据生成：demo数据更新成功'.
ELSE.
ROLLBACK WORK.
WRITE:/ '数据生成：demo数据更新失败'.
ENDIF.
ENDIF.
ELSE.
WRITE:/ '数据生成：数据表未创建成功，demo数据无法生成'.
ENDIF.
ENDFORM.
*&---------------------------------------------------------------------*
*& Form ACTIVATE_OBJECT
*&---------------------------------------------------------------------*
* 激活对象
*----------------------------------------------------------------------*
* --> p1 text
* <-- p2 text
*----------------------------------------------------------------------*
FORM activate_object .
DATA: lv_popup TYPE abap_bool,
lv_ddic TYPE abap_bool.
CHECK gt_act_object IS NOT INITIAL.
lv_popup = abap_true.
lv_ddic = abap_true.
"弹窗激活数据字典
CALL FUNCTION 'RS_WORKING_OBJECTS_ACTIVATE'
EXPORTING
activate_ddic_objects = lv_ddic
with_popup = lv_popup
TABLES
objects = gt_act_object
EXCEPTIONS
excecution_error = 1
cancelled = 2
insert_into_corr_error = 3
OTHERS = 4.
IF sy-subrc <> 0.
WRITE:/ '对象激活：激活失败或取消激活'.
ELSE.
WRITE:/ '对象激活：激活成功'.
ENDIF.
ENDFORM.
*&---------------------------------------------------------------------*
*& Form CREATE_TABLE_SIGLE
*&---------------------------------------------------------------------*
* 创建数据库表
*----------------------------------------------------------------------*
* -->P_LS_DD02V text
* -->P_LS_DD09L text
* -->P_LT_DD03P text
*----------------------------------------------------------------------*
FORM create_table_sigle USING ps_dd02v TYPE dd02v
ps_dd09l TYPE dd09l
pt_dd03p TYPE rsos_t_dd03p.
DATA lv_message TYPE string.
DATA lv_rc TYPE sy-subrc.
"为开发对象分配包
PERFORM assign_pakage USING 'TABL' ps_dd02v-tabname CHANGING lv_rc lv_message.
IF lv_rc <> 0.
WRITE:/ '数据库表：' , ps_dd02v-tabname , '分配包失败:' , lv_message.
ELSE.
CALL FUNCTION 'DDIF_TABL_PUT'
EXPORTING
name = ps_dd02v-tabname
dd02v_wa = ps_dd02v
dd09l_wa = ps_dd09l
TABLES
dd03p_tab = pt_dd03p
EXCEPTIONS
tabl_not_found = 1
name_inconsistent = 2
tabl_inconsistent = 3
put_failure = 4
put_refused = 5
OTHERS = 6.
IF sy-subrc <> 0.
MESSAGE ID sy-msgid TYPE 'E' NUMBER sy-msgno
WITH sy-msgv1 sy-msgv2 sy-msgv3 sy-msgv4 INTO lv_message.
WRITE:/ '数据库表：' , ps_dd02v-tabname , '创建失败：', lv_message.
ELSE.
WRITE:/ '数据库表：' , ps_dd02v-tabname , '创建成功'.
APPEND VALUE #( object = 'TABL' obj_name = ps_dd02v-tabname uname = sy-uname ) TO gt_act_object.
ENDIF.
ENDIF.
ENDFORM.
*&---------------------------------------------------------------------*
*& Form ASSIGN_PAKAGE
*&---------------------------------------------------------------------*
* 为开发对象分配包
*----------------------------------------------------------------------*
* -->p_object text
* -->pv_obj_name text
* <--pv_rc text
*----------------------------------------------------------------------*
FORM assign_pakage USING VALUE(p_object) TYPE char4
pv_obj_name
CHANGING pv_rc TYPE sy-subrc
pv_message TYPE string.
DATA: lv_object TYPE string,
lv_object_class TYPE string,
lv_package TYPE devclass.
DATA lv_message TYPE string.
lv_object_class = 'DICT'.
CONCATENATE p_object pv_obj_name INTO lv_object.
lv_package = '$TMP'."local
CALL FUNCTION 'RS_CORR_INSERT'
EXPORTING
object = lv_object
object_class = lv_object_class
devclass = lv_package
master_language = sy-langu
global_lock = abap_true
author = sy-uname
mode = 'I'
suppress_dialog = abap_true
EXCEPTIONS
cancelled = 1
permission_failure = 2
unknown_objectclass = 3
OTHERS = 4.
pv_rc = sy-subrc.
IF sy-subrc <> 0.
MESSAGE ID sy-msgid TYPE 'E' NUMBER sy-msgno
WITH sy-msgv1 sy-msgv2 sy-msgv3 sy-msgv4 INTO pv_message.
ENDIF.
ENDFORM.
824
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
