INSERT

描述

该语句用于添加一个或多个记录到表中。

语法

INSERT [hint_options] { single_table_insert | multi_table_insert }

single_table_insert:
    { INTO insert_table_clause opt_nologging '(' column_list ')' values_clause [{ RETURNING | RETURN } returning_exprs [into_clause]]
    | INTO insert_table_clause opt_nologging '(' ')' values_clause [{ RETURNING | RETURN } returning_exprs [into_clause]]
    | INTO insert_table_clause opt_nologging values_clause [{ RETURNING | RETURN } returning_exprs [into_clause]]
    }

opt_nologging: 
    { NOLOGGING | /*EMPTY*/ }

returning_exprs:
    projection_col_name [,projection_col_name ...]
 
insert_into_clause: 
    { INTO into_var_list | BULK COLLECT INTO into_var_list}

into_var_list:
    { USER_VARIABLE | ref_name } [, { USER_VARIABLE | ref_name }...]

values_clause:
    VALUES ({ expr | DEFAULT } [, { expr | DEFAULT } ]...  )

multi_table_insert:
    { ALL { insert_into_clause [ values_clause ] [error_logging_clause] }
    | conditional_insert_clause
    } subquery

conditional_insert_clause:
    [ ALL | FIRST ]
    WHEN condition
    THEN insert_into_clause
    [ values_clause ]
    [ error_logging_clause ]
    [ insert_into_clause [ values_clause ] [ error_logging_clause ] ]...
    [ WHEN condition
        THEN insert_into_clause
        [ values_clause ]
        [ error_logging_clause ]
        [ insert_into_clause [ values_clause ] [ error_logging_clause ] ]...
    ]...
    [ ELSE insert_into_clause
        [ values_clause ]
        [ error_logging_clause ]
        [ insert_into_clause [ values_clause ] [ error_logging_clause ] ]...
    ]

error_logging_clause:
    LOG ERRORS [ INTO [schema.] table_name ] [ (simple_expression) ] [ REJECT LIMIT { integer | UNLIMITED } ]

参数解释

示例

示例表及数据基于以下定义：

obclient> CREATE TABLE tbl1(col1 INT PRIMARY KEY, col2 INT,col3 INT);
Query OK, 0 rows affected

• 单表插入：向表 tbl1 中插入一行数据。

  obclient> INSERT INTO tbl1 VALUES(1,2,3);
  Query OK, 1 row affected
  
  obclient> SELECT * FROM tbl1;
  +------+------+------+
  | COL1 | COL2 | COL3 |
  +------+------+------+
  |    1 |    2 |    3 |
  +------+------+------+
  1 row in set
  

• 单表插入：直接向子查询中插入数据。

  obclient>INSERT INTO (SELECT * FROM tbl1) VALUES(1,2,3);
  Query OK, 1 row affected
  
  obclient> SELECT * FROM tbl1;
  +----+------+------+
  | COL1 | COL2   | COL3   |
  +----+------+------+
  |  1 |    2 |    3 |
  +----+------+------+
  1 row in set
  

• 单表插入：包含 RETURNING 子句。

  obclient> INSERT INTO tbl1 VALUES(1,2,3) RETURNING col1;
  +------+
  | COL1 |
  +------+
  |    1 |
  +------+
  1 row in set
  
  obclient> SELECT * FROM tbl1;
  +------+------+------+
  | COL1 | COL2 | COL3 |
  +------+------+------+
  |    1 |    2 |    3 |
  +------+------+------+
  1 row in set
  

• 普通的多表插入：当表 tbl3 中有至少一行数据时，向表 tbl1 插入一行数据 (1,1,1)，向表 tbl2 插入一行数据 (2,2,2)。

  obclient> INSERT ALL INTO tbl1 VALUES(1,1,1) 
                                      INTO tbl2 VALUES(2,2,2) 
                  SELECT * FROM tbl3 WHERE ROWNUM< 2;
  Query OK, 2 rows affected
  Records: 2  Duplicates: 0  Warnings: 0
  
  obclient> SELECT * FROM tbl1;
  +------+------+------+
  | COL1 | COL2 | COL3 |
  +------+------+------+
  |    1 |    1 |    1 |
  +------+------+------+
  1 row in set
  
  obclient> SELECT * FROM tbl2;
  +------+------+------+
  | COL1 | COL2 | COL3 |
  +------+------+------+
  |    2 |    2 |    2 |
  +------+------+------+
  1 row in set
  

• 带条件的多表插入：使用 INSERT ALL，当表 tbl 中 col2 的值大于 1 时，向表 tbl1 中插入数据 (1,1,1)；当表 tbl 中 col3 的值大于 1 时，向表 tbl2 中插入数据 (2,2,2)；如果都不满足，则向表 tbl1 中插入数据 (3,3,3)。

  obclient> INSERT INTO tbl VALUES(1,2,3);
  Query OK, 1 row affected
  
  obclient>INSERT ALL
                WHEN col2 > 1 THEN INTO tbl1 VALUES(1,1,1) 
                WHEN col3 > 1 THEN INTO tbl2 VALUES(2,2,2) 
                ELSE INTO tbl1 VALUES(3,3,3) SELECT col2,col3 FROM tbl;
  Query OK, 2 rows affected
  Records: 2  Duplicates: 0  Warnings: 0
  
  obclient> SELECT * FROM tbl1;
  +----+------+------+
  | COL1 | COL2   | COL3   |
  +----+------+------+
  |  1 |    1 |    1 |
  +----+------+------+
  1 row in set
  
  obclient> SELECT * FROM tbl2;
  +----+------+------+
  | COL1 | COL2   | COL3   |
  +----+------+------+
  |  2 |    2 |    2 |
  +----+------+------+
  1 row in set
  

• 带条件的多表插入：使用 INSERT FIRST，当表 tbl 中 col2 的值大于 1 时，向表 tbl1 中插入数据 (1,1,1)，向表 tbl2 中插入数据 (4,4,4)；当表 tbl 中 col3 的值大于 1 时，向表 tbl2 中插入数据 (2,2,2)；如果都不满足，则向表 tbl1 中插入数据 (3,3,3)。

  obclient>INSERT INTO tbl VALUES(1,2,3);
  Query OK, 1 row affected
  
  obclient>INSERT ALL 
                WHEN col2 > 1 THEN INTO tbl1 VALUES(1,1,1) INTO tbl2 VALUES(4,4,4)
                WHEN col3 > 1 THEN INTO tbl2 VALUES(2,2,2) 
                ELSE INTO tbl1 VALUES(3,3,3) SELECT col2,col3 FROM tbl;
  Query OK, 3 rows affected
  Records: 3  Duplicates: 0  Warnings: 0
  
  obclient> SELECT * FROM tbl1;
  +----+------+------+
  | COL1 | COL2   | COL3   |
  +----+------+------+
  |  1 |    1 |    1 |
  +----+------+------+
  1 row in set
  
  obclient> SELECT * FROM tbl2;
  +------+------+------+
  | COL1   | COL2   | COL3   |
  +------+------+------+
  |    4 |    4 |    4 |
  |    2 |    2 |    2 |
  +------+------+------+
  2 rows in set