-- Create table
create table T_TEST1
(
id NUMBER not null,
name NVARCHAR2(20),
createdate DATE,
code NVARCHAR2(20)
);
-- Create table
create table T_TEST2
(
id NUMBER not null,
name NVARCHAR2(20),
createdate DATE
);
--给T_TEST1 生成10000条数据
--正向循环
DECLARE
X NUMBER; --声明变量
BEGIN
X := 1; --给初值
FOR X IN 1 .. 10000 LOOP
INSERT INTO T_TEST1
(ID, NAME, CREATEDATE)
VALUES
(GET_TABLE_PK_SEQUENCES('T_TEST2'), 'CHENLS', SYSDATE);
END LOOP;
END;
--给T_TEST2 生成1000条数据
--正向循环
DECLARE
X NUMBER; --声明变量
BEGIN
X := 1; --给初值
FOR X IN 1 .. 10000 LOOP
INSERT INTO T_TEST2
(ID, NAME, CREATEDATE)
VALUES
(GET_TABLE_PK_SEQUENCES('T_TEST2'), 'CHENLS', SYSDATE);
END LOOP;
END;
--插入单据数据
INSERT INTO T_TEST1
(ID, NAME, CREATEDATE)
VALUES
(GET_TABLE_PK_SEQUENCES('T_TEST2'), 'CHENLS', SYSDATE);
--插入单据数据
INSERT INTO T_TEST1
(ID, NAME)
VALUES
(GET_TABLE_PK_SEQUENCES('T_TEST2'), 'CHENLS');
--插入单据数据
INSERT INTO T_TEST1
VALUES
(GET_TABLE_PK_SEQUENCES('T_TEST2'), 'CHENLS', SYSDATE);
--根据查询结果插入数据
INSERT INTO T_TEST1
SELECT * FROM T_TEST2;
--根据查询结果插入数据
INSERT INTO T_TEST1
(ID, NAME)
SELECT ID, 'test2' FROM T_TEST2;
--根据查询结果插入数据
INSERT INTO T_TEST1
(ID, NAME)
SELECT GET_TABLE_PK_SEQUENCES('T_TEST1'), 'test2intotes1' FROM T_TEST2;
----------------------------------------------------------------------------------------
UPDATE T_TEST1 SET NAME ='1' WHERE ID='1';
--更新前先查询一下需要更新的数据
SELECT T_TEST1.ID,to_date('2018-12-12','yyyy-mm-dd') CREATEDATE
FROM T_TEST1, T_TEST2
WHERE T_TEST1.NAME = T_TEST2.NAME
-- GROUP BY T_TEST1.ID, T_TEST2.CREATEDATE
;
--参考 rkAfter_updateBO_OA_ZCGL_KC_C
--参考 rkAfter_updateBO_OA_ZCGL_KC_C
UPDATE T_TEST1
SET (NAME, CREATEDATE) =
(SELECT ID, CREATEDATE
FROM (SELECT T_TEST1.ID, to_date('2018-12-12','yyyy-mm-dd') CREATEDATE
FROM T_TEST1, T_TEST2
WHERE T_TEST1.NAME = T_TEST2.NAME
GROUP BY T_TEST1.ID, T_TEST2.CREATEDATE) B
WHERE T_TEST1.ID = B.ID)
WHERE EXISTS (SELECT ID, CREATEDATE
FROM (SELECT T_TEST1.ID, to_date('2018-12-12','yyyy-mm-dd') CREATEDATE
FROM T_TEST1, T_TEST2
WHERE T_TEST1.NAME = T_TEST2.NAME
GROUP BY T_TEST1.ID, T_TEST2.CREATEDATE) B
WHERE T_TEST1.ID = B.ID);