📄 ieptests.out
字号:
ij> --table used for exportcreate table ex_emp(id int , name char(7) , skills varchar(200), salary decimal(10,2)) ;0 rows inserted/updated/deletedij> --table used for importcreate table imp_emp(id int , name char(7), skills varchar(200), salary decimal(10,2)) ;0 rows inserted/updated/deletedij> --After an export from ex_emp and import to imp_emp both tables should have --same data.--double delimter cases with default character delimter "--field seperator character inside a double delimited string as first lineinsert into ex_emp values(99, 'smith' , 'tennis"p,l,ayer"', 190.55) ;1 row inserted/updated/deletedij> -- Perform Export:call SYSCS_UTIL.SYSCS_EXPORT_TABLE (null, 'EX_EMP' , 'extinout/emp.dat' , null, null, null) ;0 rows inserted/updated/deletedij> -- Perform Importcall SYSCS_UTIL.SYSCS_IMPORT_TABLE (null, 'IMP_EMP' , 'extinout/emp.dat' , null, null, null, 0) ;0 rows inserted/updated/deletedij> insert into ex_emp values(100, 'smith' , 'tennis"player"', 190.55) ;1 row inserted/updated/deletedij> insert into ex_emp values(101, 'smith' , 'tennis"player', 190.55) ;1 row inserted/updated/deletedij> insert into ex_emp values(102, 'smith' , '"tennis"player', 190.55) ;1 row inserted/updated/deletedij> insert into ex_emp values(103, 'smith' , '"tennis"player"', 190.55) ;1 row inserted/updated/deletedij> insert into ex_emp values(104, 'smith' , '"tennis"""""""""""""""""""""""""""""""""""""player"', null) ;1 row inserted/updated/deletedij> --empty stringinsert into ex_emp values(105, 'smith' , '""', 190.55) ;1 row inserted/updated/deletedij> --just delimeter inside insert into ex_emp values(106, 'smith' , '"""""""""""""""""""', 190.55);1 row inserted/updated/deletedij> --null valueinsert into ex_emp values(107, 'smith"' , null, 190.55) ;1 row inserted/updated/deletedij> --all values are nullsinsert into ex_emp values(108, null , null, null) ;1 row inserted/updated/deletedij> -- Perform Export:call SYSCS_UTIL.SYSCS_EXPORT_TABLE (null, 'EX_EMP' , 'extinout/emp.dat' , null, null, null) ;0 rows inserted/updated/deletedij> -- Perform Importcall SYSCS_UTIL.SYSCS_IMPORT_TABLE (null, 'IMP_EMP' , 'extinout/emp.dat' , null, null, null, 0) ;0 rows inserted/updated/deletedij> select * from ex_emp;ID |NAME |SKILLS |SALARY ------------------------------------------------------------------------------------------------------------------------------------------------------------------99 |smith |tennis"p,l,ayer" |190.55 100 |smith |tennis"player" |190.55 101 |smith |tennis"player |190.55 102 |smith |"tennis"player |190.55 103 |smith |"tennis"player" |190.55 104 |smith |"tennis"""""""""""""""""""""""""""""""""""""player" |NULL 105 |smith |"" |190.55 106 |smith |""""""""""""""""""" |190.55 107 |smith" |NULL |190.55 108 |NULL |NULL |NULL ij> select * from imp_emp;ID |NAME |SKILLS |SALARY ------------------------------------------------------------------------------------------------------------------------------------------------------------------99 |smith |tennis"p,l,ayer" |190.55 99 |smith |tennis"p,l,ayer" |190.55 100 |smith |tennis"player" |190.55 101 |smith |tennis"player |190.55 102 |smith |"tennis"player |190.55 103 |smith |"tennis"player" |190.55 104 |smith |"tennis"""""""""""""""""""""""""""""""""""""player" |NULL 105 |smith |"" |190.55 106 |smith |""""""""""""""""""" |190.55 107 |smith" |NULL |190.55 108 |NULL |NULL |NULL ij> --checking queryselect count(*) from imp_emp, ex_emp where ex_emp.id = imp_emp.id and (ex_emp.skills=imp_emp.skills or (ex_emp.skills is NULL and imp_emp.skills is NULL));1 -----------11 ij> delete from imp_emp where id < 105;7 rows inserted/updated/deletedij> --export from ex_emp using the a query only rows that got deleted in imp_emp call SYSCS_UTIL.SYSCS_EXPORT_QUERY('select * from ex_emp where id < 105', 'extinout/emp.dat' , null, null, null) ;0 rows inserted/updated/deletedij> call SYSCS_UTIL.SYSCS_IMPORT_TABLE (null, 'IMP_EMP' , 'extinout/emp.dat' , null, null, null, 0) ;0 rows inserted/updated/deletedij> --checking queryselect count(*) from imp_emp, ex_emp where ex_emp.id = imp_emp.id and (ex_emp.skills=imp_emp.skills or (ex_emp.skills is NULL and imp_emp.skills is NULL));1 -----------10 ij> --export the columns in different column order than in the table.call SYSCS_UTIL.SYSCS_EXPORT_QUERY('select name , salary , skills, id from ex_emp where id < 105', 'extinout/emp.dat' , null, null, null) ;0 rows inserted/updated/deletedij> -- import them in to a with order different than in the table;call SYSCS_UTIL.SYSCS_IMPORT_DATA(null, 'IMP_EMP' ,'NAME, SALARY, SKILLS, ID', null, 'extinout/emp.dat', null, null, null, 1) ;0 rows inserted/updated/deletedij> --check queryselect count(*) from imp_emp, ex_emp where ex_emp.id = imp_emp.id and (ex_emp.skills=imp_emp.skills or (ex_emp.skills is NULL and imp_emp.skills is NULL));1 -----------6 ij> -- do import replace into the table with table order but using column indexescall SYSCS_UTIL.SYSCS_IMPORT_DATA(null, 'IMP_EMP' ,null, '4, 1, 3, 2', 'extinout/emp.dat', null, null, null, 1) ;0 rows inserted/updated/deletedij> --check queryselect count(*) from imp_emp, ex_emp where ex_emp.id = imp_emp.id and (ex_emp.skills=imp_emp.skills or (ex_emp.skills is NULL and imp_emp.skills is NULL));1 -----------6 ij> --replace using insert column names and column indexescall SYSCS_UTIL.SYSCS_IMPORT_DATA(null, 'IMP_EMP' ,'SALARY, ID, SKILLS, NAME', '2, 4, 3, 1', 'extinout/emp.dat', null, null, null, 1) ;0 rows inserted/updated/deletedij> --check queryselect count(*) from imp_emp, ex_emp where ex_emp.id = imp_emp.id and (ex_emp.skills=imp_emp.skills or (ex_emp.skills is NULL and imp_emp.skills is NULL));1 -----------6 ij> ---testing with different delimiters-- single quote(') as character delimitercall SYSCS_UTIL.SYSCS_EXPORT_TABLE (null, 'EX_EMP' , 'extinout/emp.dat' , null, '''', null) ;0 rows inserted/updated/deletedij> call SYSCS_UTIL.SYSCS_IMPORT_TABLE (null, 'IMP_EMP' , 'extinout/emp.dat' , null, '''', null, 1) ;0 rows inserted/updated/deletedij> select * from imp_emp ;ID |NAME |SKILLS |SALARY ------------------------------------------------------------------------------------------------------------------------------------------------------------------99 |smith |tennis"p,l,ayer" |190.55 100 |smith |tennis"player" |190.55 101 |smith |tennis"player |190.55 102 |smith |"tennis"player |190.55 103 |smith |"tennis"player" |190.55 104 |smith |"tennis"""""""""""""""""""""""""""""""""""""player" |NULL 105 |smith |"" |190.55 106 |smith |""""""""""""""""""" |190.55 107 |smith" |NULL |190.55 108 |NULL |NULL |NULL ij> -- single quote(') as column delimitercall SYSCS_UTIL.SYSCS_EXPORT_TABLE (null, 'EX_EMP' , 'extinout/emp.dat' , '''',null, null) ;0 rows inserted/updated/deletedij> delete from imp_emp ;10 rows inserted/updated/deletedij> call SYSCS_UTIL.SYSCS_IMPORT_TABLE (null, 'IMP_EMP' , 'extinout/emp.dat' , '''', null, null, 0) ;0 rows inserted/updated/deletedij> select * from imp_emp;ID |NAME |SKILLS |SALARY ------------------------------------------------------------------------------------------------------------------------------------------------------------------99 |smith |tennis"p,l,ayer" |190.55 100 |smith |tennis"player" |190.55 101 |smith |tennis"player |190.55 102 |smith |"tennis"player |190.55 103 |smith |"tennis"player" |190.55 104 |smith |"tennis"""""""""""""""""""""""""""""""""""""player" |NULL 105 |smith |"" |190.55 106 |smith |""""""""""""""""""" |190.55 107 |smith" |NULL |190.55 108 |NULL |NULL |NULL ij> call SYSCS_UTIL.SYSCS_EXPORT_TABLE (null, 'EX_EMP' , 'extinout/emp.dat' , '*', '%', null) ;0 rows inserted/updated/deletedij> call SYSCS_UTIL.SYSCS_IMPORT_TABLE (null, 'EX_EMP' , 'extinout/emp.dat' , '*', '%', null, 1) ;0 rows inserted/updated/deletedij> select * from imp_emp ;ID |NAME |SKILLS |SALARY ------------------------------------------------------------------------------------------------------------------------------------------------------------------99 |smith |tennis"p,l,ayer" |190.55 100 |smith |tennis"player" |190.55 101 |smith |tennis"player |190.55 102 |smith |"tennis"player |190.55 103 |smith |"tennis"player" |190.55 104 |smith |"tennis"""""""""""""""""""""""""""""""""""""player" |NULL 105 |smith |"" |190.55 106 |smith |""""""""""""""""""" |190.55 107 |smith" |NULL |190.55 108 |NULL |NULL |NULL ij> --cases for identity columns--create table emp1(id int generated always as identity (start with 100), name char(7), -- skills varchar(200), salary decimal(10,2),skills varchar(200));--check import export with real and double that can not be explictitly--casted from VARCHAR type .create table noncast(c1 double , c2 real ) ;0 rows inserted/updated/deletedij> insert into noncast values(1.5 , 6.7 ) ;1 row inserted/updated/deletedij> insert into noncast values(2.5 , 8.999) ;1 row inserted/updated/deletedij> call SYSCS_UTIL.SYSCS_EXPORT_TABLE ('APP' , 'NONCAST' , 'extinout/noncast.dat' , null , null , null) ;0 rows inserted/updated/deletedij> call SYSCS_UTIL.SYSCS_IMPORT_TABLE (null, 'NONCAST' , 'extinout/noncast.dat' , null , null , null , 0) ;0 rows inserted/updated/deletedij> call SYSCS_UTIL.SYSCS_IMPORT_DATA(null, 'NONCAST', 'C2 , C1' , '2, 1' , 'extinout/noncast.dat' , null , null , null , 0) ;0 rows inserted/updated/deletedij> select * from noncast ;C1 |C2 ------------------------------------1.5 |6.7 2.5 |8.999 1.5 |6.7 2.5 |8.999 1.5 |6.7 2.5 |8.999 ij> --check import/export of time types⌨️ 快捷键说明
复制代码
Ctrl + C
搜索代码
Ctrl + F
全屏模式
F11
切换主题
Ctrl + Shift + D
显示快捷键
?
增大字号
Ctrl + =
减小字号
Ctrl + -