在插入数据时选择值

当您在表格中插入行时，还可以同时从插入的行中选择值。

关于本任务

当您在表格中插入一行或多行新数据时，还可以检索包含以下值的行：

自动生成的列（如ROWID或标识列）的价值
列的任何默认值
插入行的所有值，不指定单个列名
通过多行插入操作插入的所有值
在插入前触发器中修改的值

过程

要从插入的行中选择值：

在SELECT语句的FROM子句中指定INSERT语句。

插入目标表的行将生成一个结果表，其列可在查询的SELECT列表中引用。结果表的列受目标表定义的列、约束和触发器的影响：

结果表中包括 Db2 生成的标识列、ROWID列或行更改时间戳列的值。
在 Db2 生成结果表之前，它会强制执行任何影响插入操作的约束（即检查约束、唯一索引约束和参照完整性约束）。
结果表中包括插入操作激活的“之前”触发器所导致的任何变化。 “之后”触发器不会影响结果表中的数值。

示例

除了使用 Db2 示例表的例子之外，本主题中的示例还使用EMPSAMP表，该表的定义如下：

CREATE TABLE EMPSAMP
  (EMPNO     INTEGER GENERATED ALWAYS AS IDENTITY,
   NAME      CHAR(30),
   SALARY    DECIMAL(10,2),
   DEPTNO    SMALLINT,
   LEVEL     CHAR(30),
   HIRETYPE  VARCHAR(30) NOT NULL WITH DEFAULT 'New Hire',
   HIREDATE  DATE NOT NULL WITH DEFAULT);

示例1：检索生成的列值

假设您需要在EMPSAMP表中为新员工插入一行。要获取生成的 EMPNO、HIRETYPE 和 HIREDATE 列的值，请使用以下 SELECT FROM INSERT 语句：

SELECT EMPNO, HIRETYPE, HIREDATE
  FROM FINAL TABLE (INSERT INTO EMPSAMP (NAME, SALARY, DEPTNO, LEVEL)
                    VALUES('Mary Smith', 35000.00, 11, 'Associate'));

SELECT语句返回 Db2 为EMPNO列生成的身份值、HIRETYPE列的默认值“New Hire”以及HIREDATE列的当前日期特殊寄存器值。

建议：使用 SELECT FROM INSERT 语句将一行插入父表中，并检索由 Db2 生成的主键值（ROWID 或标识列）。在另一个INSERT语句中，将生成的值指定为从属表中外键的值。

示例2：检索触发器更新的值

假设在EMPSAMP表上创建了一个插入前触发器，以使所有新入职的准员工工资增加5000美元。触发器有以下定义：

CREATE TRIGGER NEW_ASSOC
  NO CASCADE BEFORE INSERT ON EMPSAMP
  REFERENCING NEW AS NEWSALARY
  FOR EACH ROW MODE DB2SQL
    WHEN (NEWSALARY.LEVEL = 'ASSOCIATE')
    BEGIN ATOMIC
      SET NEWSALARY.SALARY = NEWSALARY.SALARY + 5000.00;
    END;

在SELECT语句的FROM子句中，INSERT语句将新员工插入EMPSAMP表中：

SELECT NAME, SALARY
  FROM FINAL TABLE (INSERT INTO EMPSAMP (NAME, SALARY, LEVEL)
                    VALUES('Mary Smith', 35000.00, 'Associate'));

SELECT语句返回玛丽·史密斯的工资为 40000.00 ，而不是INSERT语句中明确指定的初始工资 35000.00。

插入单行时，选择值：

当您在表格中插入新行时，您可以从SELECT FROM INSERT语句的结果表中检索任何列。当您在应用程序中嵌入此语句时，您可以通过使用 SELECT... 陈述的形式。

示例4：检索插入到结构中的行中的所有值。

您可以检索插入到结构中的行中的所有值。例如，在以下语句中：empstruct是一个主机变量结构，它使用EMPSAMP表中每列的变量进行声明。

EXEC SQL SELECT * INTO :empstruct
  FROM FINAL TABLE (INSERT INTO EMPSAMP (NAME, SALARY, DEPTNO, LEVEL)
                    VALUES('Mary Smith', 35000.00, 11, 'Associate'));

示例4：在视图中插入数据时选择值

如果INSERT语句引用了带有搜索条件的视图，则该视图必须使用WITH CASCADED CHECK OPTION选项进行定义。当您将数据插入视图时，SELECT FROM INSERT语句的结果表仅包含满足视图定义的行。

因为视图 V1 定义了 WITH CASCADED CHECK OPTION 选项，所以您可以在 INSERT 语句中引用 V1 ：

CREATE VIEW V1 AS
  SELECT C1, I1 FROM T1 WHERE I1 > 10
  WITH CASCADED CHECK OPTION;

SELECT C1 FROM
  FINAL TABLE (INSERT INTO V1 (I1) VALUES(12));

值12满足视图定义的搜索条件，结果表包含插入行中 C1 的值。

如果使用的值不符合视图定义的搜索条件，插入操作将失败， Db2 将返回错误。

示例5：插入多行时，选择行ID值

在应用程序中，要从插入的多行中检索值，请声明一个游标，以便将INSERT语句置于游标的SELECT语句的FROM子句中。

要查看插入员工照片和简历表格中的 ROWID 列的值，可以声明以下游标：

EXEC SQL DECLARE CS1 CURSOR FOR
  SELECT EMP_ROWID
  FROM FINAL TABLE (INSERT INTO DSN8C10.EMP_PHOTO_RESUME (EMPNO)
                    SELECT EMPNO FROM DSN8C10.EMP);

示例6：使用FETCH FIRST子句

要只查看员工照片和简历表中插入的前五行，请使用FETCH FIRST子句：

EXEC SQL DECLARE CS2 CURSOR FOR
  SELECT EMP_ROWID
  FROM FINAL TABLE (INSERT INTO DSN8C10.EMP_PHOTO_RESUME (EMPNO)
                    SELECT EMPNO FROM DSN8C10.EMP)
  FETCH FIRST 5 ROWS ONLY;

示例7：使用INPUT SEQUENCE子句

要按插入顺序检索行，请使用INPUT SEQUENCE子句：

EXEC SQL DECLARE CS3 CURSOR FOR
  SELECT EMP_ROWID
  FROM FINAL TABLE (INSERT INTO DSN8C10.EMP_PHOTO_RESUME (EMPNO)
                    VALUES(:hva_empno)
                    FOR 5 ROWS)
  ORDER BY INPUT SEQUENCE;

只有在SELECT语句的FROM子句中包含INSERT语句时，才能指定INPUT SEQUENCE子句。在这个例子中，行是从员工编号数组中插入的。

示例8：插入具有多个编码CCSID的行

假设您想用EBCDIC表中的值填充ASCII表，然后查看ASCII表中的选定值。您可以使用以下光标选择EBCDIC列，填充ASCII表，然后检索ASCII值：

EXEC SQL DECLARE CS4 CURSOR FOR
  SELECT C1, C2
  FROM FINAL TABLE (INSERT INTO ASCII_TABLE
                    SELECT * FROM EBCDIC_TABLE);

示例9：插入数据时选择附加列

您可以使用 INCLUDE 子句在结果表中引入新列，但不能在目标表中添加列。

假设您需要将部门编号数据插入项目表中。假设您想检索部门编号和每个部门的相应经理编号。因为 MGRNO 不是项目表中的列，所以可以使用 INCLUDE 子句将经理编号包含在结果中，但不包含在插入操作中。下面的 SELECT FROM INSERT 语句执行插入操作并检索数据。

DECLARE CS1 CURSOR FOR
 SELECT manager_num, projname FROM FINAL TABLE
   (INSERT INTO PROJ (DEPTNO) INCLUDE(manager_num CHAR(6))
      SELECT DEPTNO, MGRNO FROM DEPT);

示例10：插入多行时光标的显示结果

在应用程序中，当您向表中插入多行数据时，需要声明游标，以便将INSERT语句置于游标的SELECT语句的FROM子句中。游标的计算结果在游标打开处理期间确定。结果表可能会或可能不会受到应用程序中其他进程的影响。

当您声明一个可滚动光标时，如果光标说明的 FROM 子句中包含 INSERT 语句，则必须使用 INSENSITIVE 关键字声明光标。结果表在打开光标处理期间生成，不反映任何未来的更改。您不能使用SENSITIVE DYNAMIC或SENSITIVE STATIC关键字来声明光标。

当您声明光标不可滚动时，任何搜索更新或删除都不会影响光标的结果表。结果表的行是在光标打开处理期间确定的。

例如，假设您的应用程序声明了一个游标，打开游标，执行了一次获取操作，更新了表，然后获取了额外的行：

EXEC SQL DECLARE CS1 CURSOR FOR
  SELECT SALARY
  FROM FINAL TABLE (INSERT INTO EMPSAMP (NAME, SALARY, LEVEL)
                    SELECT NAME, INCOME, BAND FROM OLD_EMPLOYEE);
EXEC SQL OPEN CS1;
EXEC SQL FETCH CS1 INTO :hv_salary;
/* print fetch result */
...
EXEC SQL UPDATE EMPSAMP SET SALARY = SALARY + 500;
while (SQLCODE == 0) {
  EXEC SQL FETCH CS1 INTO :hv_salary;
  /* print fetch result */
  ...
}

更新后出现的抓取操作将返回光标打开时生成的行。如果您使用简单的 SELECT（FROM 子句中没有 INSERT 语句），根据 Db2 使用的访问路径，获取操作可能会返回更新的值。

例11：WITH HOLD的效果

当您使用 WITH HOLD 选项声明游标并打开游标时，所有行都会插入到目标表中。 WITH HOLD选项对游标定义的SELECT FROM INSERT语句没有影响。在应用程序提交后，您可以继续检索所有插入的行。

假设 Db2 示例应用程序中的员工表有五行。您的应用程序显示一个“暂停”光标，打开光标，获取两行数据，执行提交操作，然后成功获取第三行数据：

EXEC SQL DECLARE CS2 CURSOR WITH HOLD FOR
  SELECT EMP_ROWID
  FROM FINAL TABLE (INSERT INTO DSN8C10.EMP_PHOTO_RESUME (EMPNO)
                    SELECT EMPNO FROM DSN8C10.EMP);
EXEC SQL OPEN CS2;                                /* Inserts 5 rows */
EXEC SQL FETCH CS2 INTO :hv_rowid;   /* Retrieves ROWID for 1st row */
EXEC SQL FETCH CS2 INTO :hv_rowid;   /* Retrieves ROWID for 2nd row */
EXEC SQL COMMIT;                                  /* Commits 5 rows */
EXEC SQL FETCH CS2 INTO :hv_rowid;   /* Retrieves ROWID for 3rd row */

示例12：SAVEPOINT和ROLLBACK的影响

保存点是指恢复单元内的时间点，关系数据库的更改可以回滚到此时间点。您可以使用SAVEPOINT语句设置一个保存点。

在打开光标之前设置一个保存点，然后回滚到该保存点，所有插入操作都会取消。

假设您的应用程序声明了一个游标，设置了一个保存点，打开游标，设置了另一个保存点，回滚到第二个保存点，然后回滚到第一个保存点：

EXEC SQL DECLARE CS3 CURSOR FOR
  SELECT EMP_ROWID
  FROM FINAL TABLE (INSERT INTO DSN8C10.EMP_PHOTO_RESUME (EMPNO)
                    SELECT EMPNO FROM DSN8C10.EMP);
EXEC SQL SAVEPOINT A ON ROLLBACK RETAIN CURSORS;       /* Sets 1st savepoint */ 
EXEC SQL OPEN CS3;
EXEC SQL SAVEPOINT B ON ROLLBACK RETAIN CURSORS;       /* Sets 2nd savepoint */
...
EXEC SQL ROLLBACK TO SAVEPOINT B;  /* Rows still in DSN8C10.EMP_PHOTO_RESUME */ 
...
EXEC SQL ROLLBACK TO SAVEPOINT A;            /* All inserted rows are undone */

示例13：SELECT INTO处理过程中的错误

在应用程序中，当您使用 SELECT FROM INSERT 语句向表中插入一行或多行时，插入操作的结果表可能会受到影响，也可能不会受到影响，具体取决于应用程序处理过程中错误发生的位置。

如果在 SELECT INTO 语句中插入处理或选择处理失败，则不会向目标表插入任何行，也不会从插入操作的结果表中返回任何行。例如，假设 Db2 示例应用程序的员工表有一行，且SALARY列的值为 9999000.00。

EXEC SQL SELECT EMPNO INTO :hv_empno
  FROM FINAL TABLE (INSERT INTO EMPSAMP (NAME, SALARY)
                    SELECT FIRSTNAME || MIDINIT || LASTNAME,
                           SALARY + 10000.00 
                    FROM DSN8C10.EMP)

添加 10000.00 会导致小数点溢出，并且没有行插入EMPSAMP表中。

示例14：OPEN光标处理过程中的错误

如果在光标处理过程中插入任何一行失败，则之前所有成功的插入操作都将取消。插入的结果表为空。

示例15：FETCH处理过程中的错误

如果在插入操作的结果表中检索行时FETCH语句失败，则应用程序会收到负SQLCODE，但结果表仍包含在OPEN游标处理期间确定的原始行数。此时，您可以撤销所有插入。

假设结果表包含100行数据，当从游标中获取第90行时返回负值的SQLCODE：

EXEC SQL DECLARE CS1 CURSOR FOR
  SELECT EMPNO
  FROM FINAL TABLE (INSERT INTO EMPSAMP (NAME, SALARY)
                    SELECT FIRSTNAME || MIDINIT || LASTNAME, SALARY + 10000.00 
                      FROM DSN8C10.EMP);
EXEC SQL OPEN CS1;                                 /* Inserts 100 rows */
while (SQLCODE == 0) 
  EXEC SQL FETCH CS1 INTO :hv_empno;
if (SQLCODE == -904)           /* If SQLCODE is -904, undo all inserts */
  EXEC SQL ROLLBACK;
else                                           /* Else, commit inserts */
  EXEC SQL COMMIT;