Oracle_DML汇总sql的一些写法

-- 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);